1 using System; 2 using System.Collections.Generic; 3 using System.Data.SqlClient; 4 using System.Data; 5 using System.Configuration; 6 using System.Reflection; 7 using System.Collections; 8 namespace DataHelp 9 { 10 #region ADO.NET 访问数据库辅助类 +SqlHelp 11 //Author:兵兵 +SqlHelp 12 public class SqlHelp 13 { 14 /// 15 /// DB连接字符串 16 /// 17 public static readonly string DB= ConfigurationManager.ConnectionStrings["DB"].ConnectionString; 18 19 20 #region ExecuteDataReader +ExecuteDataReader(string cmdText, List parameters,string connString) 21 /// 22 /// ExecuteDataReader(执行有参存储过程) 23 /// 24 /// 存储过程名称 25 /// 参数列表 26 /// 连接字符串 27 /// SqlDataReader对象 28 public static SqlDataReader ExecuteDataReader(string cmdText, List parameters, string connString) 29 { 30 SqlConnection conn = new SqlConnection(connString); 31 SqlCommand cmd = new SqlCommand(); 32 CommandBuilder(cmdText, cmd, conn, parameters); 33 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 34 cmd.Parameters.Clear(); 35 return reader; 36 37 } 38 #endregion 39 40 #region ExecuteDataReader +ExecuteDataReader(string cmdText,string connString) 41 /// 42 /// ExecuteDataReader(执行无参存储过程) 43 /// 44 /// 存储过程 45 /// 连接字符串 46 /// SqlDataReader对象 47 public static SqlDataReader ExecuteDataReader(string cmdText, string connString) 48 { 49 50 SqlConnection conn = new SqlConnection(connString); 51 SqlCommand cmd = new SqlCommand(); 52 CommandBuilder(cmdText, cmd, conn); 53 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 54 cmd.Parameters.Clear(); 55 return reader; 56 57 } 58 #endregion 59 60 #region ExecuteNonQuery +ExecuteNonQuery(string cmdText, List parameters, string connString) 61 /// 62 /// ExecuteNonQuery(执行有参存储过程) 63 /// 64 /// 存储过程名称 65 /// 参数列表 66 /// 连接字符串 67 /// 数据库受影响的行数 68 public static int ExecuteNonQuery(string cmdText, List parameters, string connString) 69 { 70 using (SqlConnection conn = new SqlConnection(connString)) 71 { 72 SqlCommand cmd = new SqlCommand(); 73 CommandBuilder(cmdText, cmd, conn, parameters); 74 int result = cmd.ExecuteNonQuery(); 75 cmd.Parameters.Clear(); 76 return result; 77 } 78 } 79 #endregion 80 81 #region ExecuteNonQuery +ExecuteNonQuery(string cmdText, string connString) 82 /// 83 /// ExecuteNonQuery(执行无参存储过程) 84 /// 85 /// 存储过程名称 86 /// 连接字符串 87 /// 数据库受影响的行数 88 public static int ExecuteNonQuery(string cmdText, string connString) 89 { 90 91 using (SqlConnection conn = new SqlConnection(connString)) 92 { 93 SqlCommand cmd = new SqlCommand(); 94 CommandBuilder(cmdText, cmd, conn); 95 int result = cmd.ExecuteNonQuery(); 96 cmd.Parameters.Clear(); 97 return result; 98 } 99 100 101 }102 #endregion103 104 #region ExecuteScalar +ExecuteScalar(string cmdText, List parameters, string connString)105 /// 106 /// ExecuteScalar(执行有参存储过程)107 /// 108 /// 存储过程名称109 /// 参数列表110 /// 连接字符串111 /// object 112 public static object ExecuteScalar(string cmdText, List parameters, string connString)113 {114 115 using (SqlConnection conn = new SqlConnection(connString))116 {117 SqlCommand cmd = new SqlCommand();118 CommandBuilder(cmdText, cmd, conn, parameters);119 object o = cmd.ExecuteScalar();120 cmd.Parameters.Clear();121 return o;122 }123 124 125 }126 #endregion127 128 #region ExecuteScalar +ExecuteScalar(string cmdText, string connString)129 /// 130 /// ExecuteScalar(执行无参存储过程)131 /// 132 /// 存储过程名称133 /// 连接字符串134 /// object 135 public static object ExecuteScalar(string cmdText, string connString)136 {137 138 using (SqlConnection conn = new SqlConnection(connString))139 {140 SqlCommand cmd = new SqlCommand();141 CommandBuilder(cmdText, cmd, conn);142 object o = cmd.ExecuteScalar();143 cmd.Parameters.Clear();144 return o;145 }146 147 148 }149 #endregion150 151 #region ExecuteDataTable +ExecuteDataTable(string cmdText, List parameters, string connString)152 /// 153 /// ExecuteDataTable(用适配器执行有参存储过程)154 /// 155 /// 存储过程名称156 /// 参数列表157 /// 连接字符串158 /// DataTable 159 public static DataTable ExecuteDataTable(string cmdText, List parameters, string connString)160 {161 162 using (SqlConnection conn = new SqlConnection(connString))163 {164 SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);165 //命令类型为存储过程166 da.DeleteCommand.CommandType = CommandType.StoredProcedure;167 da.SelectCommand.Parameters.AddRange(parameters.ToArray());168 DataTable dt = new DataTable();169 da.Fill(dt);170 return dt;171 }172 173 174 }175 #endregion176 177 #region ExecuteDataTable +ExecuteDataTable(string cmdText, string connString)178 /// 179 /// ExecuteDataTable(用适配器执行无参存储过程)180 /// 181 /// 存储过程名称182 /// 连接字符串183 /// DataTable 184 public static DataTable ExecuteDataTable(string cmdText, string connString)185 {186 187 using (SqlConnection conn = new SqlConnection(connString))188 {189 SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);190 //命令类型为存储过程191 da.DeleteCommand.CommandType = CommandType.StoredProcedure;192 DataTable dt = new DataTable();193 da.Fill(dt);194 return dt;195 }196 }197 #endregion198 199 #region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, List parameters, string connString)200 /// 201 /// ExecuteDataTableProc(执行有参存储过程)202 /// 203 /// 存储过程名称204 /// 参数列表205 /// 连接字符串206 /// DataTable 207 public static DataTable ExecuteDataTableProc(string cmdText, List parameters, string connString)208 /// 209 {210 211 using (SqlConnection conn = new SqlConnection(connString))212 {213 SqlCommand cmd = new SqlCommand();214 CommandBuilder(cmdText, cmd, conn, parameters);215 SqlDataAdapter adapter = new SqlDataAdapter(cmd);216 DataTable dt = new DataTable();217 adapter.Fill(dt);218 cmd.Parameters.Clear();219 return dt;220 221 }222 223 }224 #endregion225 226 #region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, string connString)227 /// 228 /// ExecuteDataTableProc(执行无参存储过程)229 /// 230 /// 参数列表231 /// 连接字符串232 /// DataTable 233 public static DataTable ExecuteDataTableProc(string cmdText, string connString)234 {235 236 using (SqlConnection conn = new SqlConnection(connString))237 {238 SqlCommand cmd = new SqlCommand();239 CommandBuilder(cmdText, cmd, conn);240 SqlDataAdapter adapter = new SqlDataAdapter(cmd);241 DataTable dt = new DataTable();242 adapter.Fill(dt);243 cmd.Parameters.Clear();244 return dt;245 246 }247 248 }249 #endregion250 251 #region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List parameters)252 /// 253 /// 准备命令对象(执行有参存储过程)254 /// 255 /// 存储过程名称256 /// 命令对象257 /// 连接对象258 /// 参数列表259 private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List parameters)260 {261 262 if (conn.State == System.Data.ConnectionState.Closed)263 conn.Open();264 cmd.Connection = conn;265 cmd.CommandText = cmdText;266 cmd.CommandType = System.Data.CommandType.StoredProcedure;267 if (parameters.Count > 0)268 cmd.Parameters.AddRange(parameters.ToArray());269 270 }271 #endregion272 273 #region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)274 /// 275 /// 准备命令对象(执行无参存储过程)276 /// 277 /// 存储过程名称278 /// 命令对象279 /// 连接对象280 private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)281 {282 283 if (conn.State == System.Data.ConnectionState.Closed)284 conn.Open();285 cmd.Connection = conn;286 cmd.CommandText = cmdText;287 cmd.CommandType = System.Data.CommandType.StoredProcedure;288 289 }290 #endregion291 292 #region 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)293 /// 294 /// 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)295 /// 296 /// 所有数据的表格297 /// 表名298 public static int BulkInsert(DataTable dt, string tableName, string connStr)299 {300 int result = -1;301 if (string.IsNullOrEmpty(tableName))302 throw new Exception("请指定你要插入的表名");303 var count = dt.Rows.Count;304 if (count == 0)305 return result;306 SqlTransaction sqlBulkTran = null;307 try308 {309 using (SqlConnection conn = new SqlConnection(connStr))310 {311 if (conn.State == System.Data.ConnectionState.Closed)312 conn.Open();313 sqlBulkTran = conn.BeginTransaction();314 using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlBulkTran))315 {316 copy.DestinationTableName = tableName;//指定目标表317 copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中318 if (sqlBulkTran != null)319 {320 sqlBulkTran.Commit();321 }322 result = 1;323 }324 325 }326 327 }328 catch (Exception)329 {330 if (sqlBulkTran != null)331 {332 sqlBulkTran.Rollback();333 }334 }335 finally336 {337 sqlBulkTran = null;338 }339 340 return result;341 }342 #endregion343 344 345 }346 #endregion347 }348 #region list 扩展方法 Author:高兵兵349 public static class IListUtil350 {351 /// 352 /// 将集合类转换成DataTable 353 /// 354 /// 集合355 /// 356 public static DataTable AsDataTable (this IList list)357 {358 DataTable result = new DataTable();359 if (list.Count > 0)360 {361 PropertyInfo[] propertys = typeof(T).GetProperties();362 foreach (PropertyInfo pi in propertys)363 {364 result.Columns.Add(pi.Name, pi.PropertyType);365 }366 367 for (int i = 0; i < list.Count; i++)368 {369 ArrayList tempList = new ArrayList();370 foreach (var item in propertys)371 {372 object obj = item.GetValue(list[i], null);373 tempList.Add(obj);374 }375 376 object[] array = tempList.ToArray();377 result.LoadDataRow(array, true);378 }379 }380 return result;381 }382 383 384 }385 #endregion