SqlHelper2 

  1 public abstract class DBHelper
  2     {
  3         public static string connectionString = @"DATA SOURCE=.\sqldata;UID=sa;PWD=bkin123;DATABASE=bkintest";//ConfigurationManager.ConnectionStrings["database"].ConnectionString;
  4         public DBHelper()
  5         {
  6            //connectionString = @"DATA SOURCE=(local);UID=sa;PWD=sa;DATABASE=db";
  7         }
  8 
  9         #region 不带参数
 10         //执行不带参数数据记录的增、删、改操作，并返回执行后的结果值  
 11         public static int ExecuteCommand(string safeSql)
 12         {
 13             using (SqlConnection connection = new SqlConnection(connectionString))
 14             {
 15                 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
 16                 {
 17                     try
 18                     {
 19                         connection.Open();
 20                         int result = cmd.ExecuteNonQuery();
 21                         return result;
 22                     }
 23                     catch (System.Data.SqlClient.SqlException e)
 24                     {
 25                         connection.Close();
 26                         throw e;
 27                     }
 28                 }
 29             }
 30         }
 31 
 32         //返回执行不带参数SQL语句后中结果集的第一行第一列
 33         public static object GetScalar(string safeSql)
 34         {
 35             using (SqlConnection connection = new SqlConnection(connectionString))
 36             {
 37                 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
 38                 {
 39                     try
 40                     {
 41                         connection.Open();
 42                         object result = cmd.ExecuteScalar();
 43                         return result;
 44                     }
 45                     catch (System.Data.SqlClient.SqlException e)
 46                     {
 47                         connection.Close();
 48                         throw e;
 49                     }
 50                 }
 51             }
 52         }
 53 
 54         //返回执行不带参数SQL语句后的结果数据表  
 55         public static DataTable GetDataTable(string safeSql)
 56         {
 57             using (SqlConnection connection = new SqlConnection(connectionString))
 58             {
 59                 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
 60                 {
 61                     try
 62                     {
 63                         connection.Open();
 64                         DataSet ds = new DataSet();
 65                         SqlDataAdapter da = new SqlDataAdapter(cmd);
 66                         da.Fill(ds);
 67                         return ds.Tables[0];
 68                     }
 69                     catch (System.Data.SqlClient.SqlException e)
 70                     {
 71                         connection.Close();
 72                         throw e;
 73                     }
 74                 }
 75             }
 76         }
 77         #endregion
 78 
 79         #region 带参数
 80         //执行带参数数据记录的增、删、改操作，返回执行后的结果值  
 81         public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
 82         {
 83             using (SqlConnection connection = new SqlConnection(connectionString))
 84             {
 85                 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
 86                 {
 87                     try
 88                     {
 89                         connection.Open();
 90                         cmd.Parameters.AddRange(values);
 91                         return cmd.ExecuteNonQuery();
 92                     }
 93                     catch (System.Data.SqlClient.SqlException e)
 94                     {
 95                         connection.Close();
 96                         throw e;
 97                     }
 98                 }
 99             }
100         }
101 
102         //返回执行带参数SQL语句后中结果集的第一行第一列
103         public static object GetScalar(string safeSql, params SqlParameter[] values)
104         {
105             using (SqlConnection connection = new SqlConnection(connectionString))
106             {
107                 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
108                 {
109                     try
110                     {
111                         connection.Open();
112                         cmd.Parameters.AddRange(values);
113                         object result = cmd.ExecuteScalar();
114                         return result;
115                     }
116                     catch (System.Data.SqlClient.SqlException e)
117                     {
118                         connection.Close();
119                         throw e;
120                     }
121                 }
122             }
123         }
124 
125         //返回执行带参数SQL语句后的结果数据表  
126         public static DataTable GetDataTable(string safeSql, params SqlParameter[] values)
127         {
128             using (SqlConnection connection = new SqlConnection(connectionString))
129             {
130                 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
131                 {
132                     try
133                     {
134                         connection.Open();
135                         DataSet ds = new DataSet();
136                         cmd.Parameters.AddRange(values);
137                         SqlDataAdapter da = new SqlDataAdapter(cmd);
138                         da.Fill(ds);
139                         return ds.Tables[0];
140                     }
141                     catch (System.Data.SqlClient.SqlException e)
142                     {
143                         connection.Close();
144                         throw e;
145                     }
146                 }
147             }
148         }
149         #endregion
150     }

 

posted on 2017-12-29 16:18  bkin的笔记  阅读(244)  评论(0)    收藏  举报