“造轮运动”之 ORM框架系列（一）~谈谈我在实际业务中的增删改查

  1     public static class SqlServerHelper
  2     {
  3         //数据库连接字符串，从配置文件的配置字段中读取
  4         private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
  5 
  6         /// <summary>
  7         /// 创建新的数据库连接
  8         /// </summary>
  9         public static SqlConnection CreateSqlConnection()
 10         {
 11             SqlConnection conn = new SqlConnection(connStr);
 12 
 13             conn.Open();   //打开数据库连接
 14 
 15             return conn;
 16         }
 17 
 18         /// <summary>
 19         /// 执行无结果返回的sql语句（共用同一个连接）
 20         /// </summary>
 21         /// <param name="sqlConn"></param>
 22         /// <param name="sqlStr"></param>
 23         /// <param name="sqlParamters"></param>
 24         /// <returns>返回受影响的数据总条数</returns>
 25         public static long ExecuteNoQuery(SqlConnection sqlConn, string sqlStr, params SqlParameter[] sqlParamters)
 26         {
 27             using (SqlCommand cmd = sqlConn.CreateCommand())
 28             {
 29                 cmd.CommandText = sqlStr;
 30                 cmd.Parameters.AddRange(sqlParamters);
 31 
 32                 long sum = cmd.ExecuteNonQuery();
 33 
 34                 return sum;
 35             }
 36         }
 37 
 38         /// <summary>
 39         /// 执行无结果返回的sql语句（不共用一个连接）
 40         /// </summary>
 41         /// <param name="sqlStr"></param>
 42         /// <param name="sqlParamters"></param>
 43         /// <returns>返回受影响的数据总条数</returns>
 44         public static long ExecuteNoQuery(string sqlStr, params SqlParameter[] sqlParamters)
 45         {
 46             using (SqlConnection sqlConn = CreateSqlConnection())
 47             using (SqlCommand cmd = sqlConn.CreateCommand())
 48             {
 49                 cmd.CommandText = sqlStr;
 50                 cmd.Parameters.AddRange(sqlParamters);
 51 
 52                 long sum = cmd.ExecuteNonQuery();
 53 
 54                 return sum;
 55             }
 56         }
 57 
 58         /// <summary>
 59         /// 执行只有一行一列返回数据的sql语句（共用同一个连接）
 60         /// </summary>
 61         /// <param name="sqlConn"></param>
 62         /// <param name="sqlStr"></param>
 63         /// <param name="sqlParamters"></param>
 64         /// <returns>返回结果的第一行第一列数据</returns>
 65         public static object ExecuteScalar(SqlConnection sqlConn, string sqlStr, params SqlParameter[] sqlParamters)
 66         {
 67             using (SqlCommand cmd = sqlConn.CreateCommand())
 68             {
 69                 cmd.CommandText = sqlStr;
 70                 cmd.Parameters.AddRange(sqlParamters);
 71 
 72                 object res = cmd.ExecuteScalar();
 73 
 74                 return res;
 75             }
 76         }
 77 
 78         /// <summary>
 79         /// 执行只有一行一列返回数据的sql语句（不共用同一个连接）
 80         /// </summary>
 81         /// <param name="sqlStr"></param>
 82         /// <param name="sqlParamters"></param>
 83         /// <returns>返回结果的第一行第一列数据</returns>
 84         public static object ExecuteScalar(string sqlStr, params SqlParameter[] sqlParamters)
 85         {
 86             using (SqlConnection sqlConn = CreateSqlConnection())
 87             using (SqlCommand cmd = sqlConn.CreateCommand())
 88             {
 89                 cmd.CommandText = sqlStr;
 90                 cmd.Parameters.AddRange(sqlParamters);
 91 
 92                 object res = cmd.ExecuteScalar();
 93 
 94                 return res;
 95             }
 96         }
 97 
 98         /// <summary>
 99         /// 执行有查询结果的sql语句（共用同一个连接）
100         /// </summary>
101         /// <param name="sqlConn"></param>
102         /// <param name="sqlStr"></param>
103         /// <param name="sqlParamters"></param>
104         /// <returns></returns>
105         public static DataTable ExcuteQuery(SqlConnection sqlConn, string sqlStr, params SqlParameter[] sqlParamters)
106         {
107             using (SqlCommand cmd = sqlConn.CreateCommand())
108             {
109                 cmd.CommandText = sqlStr;
110 
111                 cmd.Parameters.AddRange(sqlParamters);
112 
113                 //把sql语句的执行结果填充到SqlDataAdapter中
114                 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
115 
116                 DataTable dt = new DataTable();
117                 adapter.Fill(dt);    //将执行结果填充到dt对象中
118 
119                 return dt;
120             }
121         }
122 
123         /// <summary>
124         /// 执行有查询结果的sql语句（共用同一个连接）
125         /// </summary>
126         /// <param name="sqlStr"></param>
127         /// <param name="sqlParamters"></param>
128         /// <returns></returns>
129         public static DataTable ExcuteQuery(string sqlStr, params SqlParameter[] sqlParamters)
130         {
131             using (SqlConnection sqlConn = CreateSqlConnection())
132             using (SqlCommand cmd = sqlConn.CreateCommand())
133             {
134                 cmd.CommandText = sqlStr;
135 
136                 cmd.Parameters.AddRange(sqlParamters);
137 
138                 //把sql语句的执行结果填充到SqlDataAdapter中
139                 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
140 
141                 DataTable dt = new DataTable();
142                 adapter.Fill(dt);    //将执行结果填充到dt对象中
143 
144                 return dt;
145             }
146         }
147 
148         /// <summary>
149         /// 批量插入数据到数据库
150         /// </summary>
151         /// <param name="insertTable"></param>
152         /// <param name="dataTableName"></param>
153         public static void BatchInsert(DataTable insertTable, string dataTableName)
154         {
155             using (SqlBulkCopy sbc = new SqlBulkCopy(connStr))
156             {
157                 sbc.DestinationTableName = dataTableName;
158 
159                 for (int i = 0; i < insertTable.Columns.Count; i++ )
160                 {
161                     sbc.ColumnMappings.Add(insertTable.Columns[i].ColumnName, insertTable.Columns[i].ColumnName);
162                 }
163 
164                 sbc.WriteToServer(insertTable);
165             }
166         }
167 
168     }