16 Aralık 2013 Pazartesi

Kullanışlı bir Ado.Net Data Access Layer.


 Merhaba,
Eğer projenizde Ado.Net kullanacaksanız, yada Ado.Net kullanmanız gereken yerler olacaksa yazmış olduğum bu sınıfı çok seveceksiniz.



public class DAL {
 

       public static DataTable executeProc(string spName, params SqlParameter[] prm) {
           SqlConnection cnn = new SqlConnection(getCnn);
           SqlCommand cmd = new SqlCommand();
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = spName;
           cmd.Connection = cnn;
           if (prm != null) {
               foreach (SqlParameter pr in prm) {
                   if (pr.Value == null) {
                       pr.Value = DBNull.Value;
                   }
                   cmd.Parameters.Add(pr);
               }
           }
           DataTable dt = new DataTable();
           SqlDataAdapter da = new SqlDataAdapter(cmd);
           da.Fill(dt);
           return dt;
       }
      

       public static  List<T> executeProc<T> ( string spName, bool? isException=false,params SqlParameter[] prm) {
           DataTable dt = executeProc(spName, prm);
           List<T> returnList = new List<T> ();
           T obj;
           foreach (DataRow dr in dt.Rows) {
               obj = (T)Activator.CreateInstance(typeof(T));
               foreach (DataColumn dc in dt.Columns) {
                   if (dr[dc].Equals(DBNull.Value))  dr[dc] = null;
                   try {
                       obj.GetType().GetProperty(dc.ColumnName).SetValue(obj, dr[dc], null);
                   } catch  {
                       if (isException==true) {
                           throw;
                       } else {
                           HttpContext.Current.Server.ClearError();
                           break;
                       }
                   }
               }
               returnList.Add(obj);
           }
           return returnList;
 
       }
 
       public static void executeNonQuery(string spName, params SqlParameter[] prm) {
           SqlConnection cnn = new SqlConnection(getCnn);
           SqlCommand cmd = new SqlCommand();
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = spName;
           cmd.Connection = cnn;
           if (prm != null) {
               foreach (SqlParameter pr in prm) {
                   if (pr.Value == null) {
                       pr.Value = DBNull.Value;
                   }
                   cmd.Parameters.Add(pr);
               }
           }
           cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
       }
       public static object executeScalarValue(string spName, params SqlParameter[] prm) {
           SqlConnection cnn = new SqlConnection(getCnn);
           SqlCommand cmd = new SqlCommand();
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = spName;
           cmd.Connection = cnn;
           if (prm != null) {
               for (int i = 0; i < prm.Length; i++) {
                   if (prm[i].Value != null) {
                       cmd.Parameters.Add(prm[i]);
                   }
               }
           }
           cnn.Open();
           object obj = cmd.ExecuteScalar();
           cnn.Close();
           return obj;
       }
       public static bool executeSql(string sqlString, params SqlParameter[] prm) {
           SqlConnection cnn = new SqlConnection(getCnn);
           SqlCommand cmd = new SqlCommand();
           try {
               cmd.CommandType = CommandType.Text;
               cmd.Connection = cnn;
               if (prm != null) {
                   for (int i = 0; i < prm.Length; i++) {
                       if (prm[i].Value != null) {
                           cmd.Parameters.Add(prm[i]);
                       }
                   }
               }
               cnn.Open();
               cmd.ExecuteNonQuery();
               cnn.Close();
               return true;
           } catch {
               if (cnn.State == ConnectionState.Open) {
                   cnn.Close();
               }
               return false;
           }
       }
       public static string getCnn = ConfigurationManager.AppSettings["Cnn"].ToString();
    }