public class dalc
{
SqlConnection conn = new SqlConnection();
//
SqlConnection conn;
public dalc()
{
conn.ConnectionString = @"YourConnectionString";
//
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
}
/// <summary>
/// Get list by query as a string
/// </summary>
/// <typeparam name="T">any class or object</typeparam>
/// <param name="str">query</param>
/// <returns>list of data</returns>
public async
Task<List<T>> selectbyqueryList<T>(string str)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = str.ToString();
SqlDataReader dr;
try
{
await conn.OpenAsync();
dr = await cmd.ExecuteReaderAsync();
List<T> lst = new List<T>();
while (await
dr.ReadAsync())
{
T item =
GetItem<T>(dr);
lst.Add(item);
}
return lst;
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
cmd.Parameters.Clear();
cmd.Dispose();
//
conn.Dispose();
}
}
/// <summary>
/// Get list by calling store procedure
with parameters
/// </summary>
/// <typeparam name="T">any class or object</typeparam>
/// <param name="Query">name of store procedure</param>
/// <param name="para">parameters</param>
/// <returns></returns>
public async
Task<List<T>> GetList<T>(string Query,
SqlParameter[] para)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = conn;
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Parameters.AddRange(para);
cmd.CommandText = Query.ToString();
SqlDataReader dr;
try
{
await conn.OpenAsync();
dr = await cmd.ExecuteReaderAsync();
List<T> lst = new List<T>();
while (await
dr.ReadAsync())
{
T item =
GetItem<T>(dr);
lst.Add(item);
}
return lst;
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
cmd.Parameters.Clear();
cmd.Dispose();
//
conn.Dispose();
}
}
/// <summary>
/// Get list by calling query with parameters
/// </summary>
/// <typeparam name="T">any class or object</typeparam>
/// <param name="Query">parameterize query</param>
/// <param name="para">parameters</param>
/// <returns></returns>
public async
Task<List<T>> GetList_Text<T>(string Query,
SqlParameter[] para)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(para);
cmd.CommandText = Query.ToString();
SqlDataReader dr;
try
{
await conn.OpenAsync();
dr = await cmd.ExecuteReaderAsync();
List<T> lst = new List<T>();
if (dr.HasRows)
{
while (await dr.ReadAsync())
{
T item = GetItem<T>(dr);
lst.Add(item);
}
}
return lst;
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
cmd.Parameters.Clear();
cmd.Dispose();
//
conn.Dispose();
}
}
/// <summary>
/// Get dictionary data from query
/// </summary>
/// <param name="qry">query</param>
/// <returns>list of dictionary</returns>
public async
Task<List<Dictionary<string, object>>>
GetQryData(string qry)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = qry;
SqlDataReader dr;
try
{
await conn.OpenAsync();
dr = await cmd.ExecuteReaderAsync();
//
conn.Close();
List<Dictionary<string, object>> lst = new List<Dictionary<string, object>>();
while (await
dr.ReadAsync())
{
Dictionary<string, object> obj = new
Dictionary<string, object>();
for (int i = 0; i < dr.FieldCount; i++)
{
obj[dr.GetName(i)] =
dr[i].GetType().Name == "DBNull" ? null : dr[i];
}
lst.Add(obj);
}
return lst;
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
cmd.Parameters.Clear();
cmd.Dispose();
}
}
public T GetItem<T>(SqlDataReader dr)
{
Type temp = typeof(T);
T obj =
Activator.CreateInstance<T>();
for (int i = 0; i
< dr.FieldCount; i++)
{
foreach (PropertyInfo pro in temp.GetProperties())
{
if (pro.Name.ToLower() ==
dr.GetName(i).ToLower())
{
if (!string.IsNullOrEmpty(Convert.ToString(dr[i])))
{
if (pro.PropertyType.Name == "String")
pro.SetValue(obj, Convert.ToString(dr[i]));
else if (pro.PropertyType.Name == "Byte[]" && string.IsNullOrEmpty(Convert.ToString(dr[i])))
pro.SetValue(obj, new byte[0]);
else
pro.SetValue(obj, dr[i]);
}
break;
}
}
}
return obj;
}
}
