SQLHelper
2015-04-29 16:52 糯米粥 阅读(231) 评论(0) 编辑 收藏 举报![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SqlClient; 6 using System.Data; 7 8 namespace LWSR.FBU.DAL 9 { 10 public class SQLHelper 11 { 12 //连接字符串 13 static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 14 /// <summary> 15 /// 返回受影响的行数 16 /// </summary> 17 /// <param name="comText"></param> 18 /// <param name="param"></param> 19 /// <returns></returns> 20 public static int ExecuteNonQuery(string comText, params SqlParameter[] param) 21 { 22 using (SqlConnection conn = new SqlConnection(connStr)) 23 { 24 using (SqlCommand cmd = new SqlCommand(comText, conn)) 25 { 26 if (param != null && param.Length != 0) 27 { 28 cmd.Parameters.AddRange(param); 29 } 30 if (conn.State == ConnectionState.Closed) 31 { 32 conn.Open(); 33 } 34 return cmd.ExecuteNonQuery(); 35 } 36 } 37 } 38 39 /// <summary> 40 /// model是静态类型的调用方法 41 /// </summary> 42 /// <param name="comText"></param> 43 /// <param name="param"></param> 44 public static void zhixing(string comText, params SqlParameter[] param) 45 { 46 using (SqlConnection conn = new SqlConnection(connStr)) 47 { 48 using (SqlCommand cmd = new SqlCommand(comText, conn)) 49 { 50 if (param != null && param.Length != 0) 51 { 52 cmd.Parameters.AddRange(param); 53 } 54 if (conn.State == ConnectionState.Closed) 55 { 56 conn.Open(); 57 } 58 cmd.ExecuteNonQuery(); 59 } 60 } 61 } 62 /// <summary> 63 /// 返回一个数据集 64 /// </summary> 65 /// <param name="sqlStr">sql语句</param> 66 /// <returns></returns> 67 public static DataSet dataSet(string sqlStr) 68 { 69 SqlConnection conn = new SqlConnection(connStr); 70 conn.Open(); 71 DataSet ds = new DataSet(); 72 try 73 { 74 SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn); 75 da.Fill(ds); 76 } 77 catch (Exception e) 78 { 79 throw new Exception(e.Message); 80 } 81 finally 82 { 83 conn.Close(); 84 } 85 return ds; 86 } 87 /// <summary> 88 /// 返回数据对象 89 /// </summary> 90 /// <param name="comText"></param> 91 /// <param name="param"></param> 92 /// <returns></returns> 93 public static object ExecuteScalar(string comText, params SqlParameter[] param) 94 { 95 using (SqlConnection conn = new SqlConnection(connStr)) 96 { 97 using (SqlCommand cmd = new SqlCommand(comText, conn)) 98 { 99 if (param != null && param.Length != 0) 100 { 101 cmd.Parameters.AddRange(param); 102 } 103 if (conn.State == ConnectionState.Closed) 104 { 105 conn.Open(); 106 } 107 object obj = cmd.ExecuteScalar(); 108 cmd.Parameters.Clear(); 109 return obj; 110 } 111 } 112 } 113 /// <summary> 114 /// 返回table 115 /// </summary> 116 /// <param name="cmdText"></param> 117 /// <param name="param"></param> 118 /// <returns></returns> 119 public static DataTable Adapter(string cmdText, params SqlParameter[] param) 120 { 121 DataTable dt = new DataTable(); 122 using (SqlDataAdapter sda = new SqlDataAdapter(cmdText, connStr)) 123 { 124 if (param != null && param.Length != 0) 125 { 126 127 if (param[0] != null) 128 sda.SelectCommand.Parameters.AddRange(param); 129 } 130 sda.Fill(dt); 131 sda.SelectCommand.Parameters.Clear(); 132 133 } 134 return dt; 135 } 136 /// <summary> 137 /// 向前读取记录 138 /// </summary> 139 /// <param name="cmdText"></param> 140 /// <param name="param"></param> 141 /// <returns></returns> 142 public static SqlDataReader ExectueReader(string cmdText, params SqlParameter[] param) 143 { 144 SqlConnection conn = new SqlConnection(connStr); 145 //using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 146 //{ 147 SqlCommand cmd = new SqlCommand(cmdText, conn); 148 if (param != null && param.Length != 0) 149 { 150 cmd.Parameters.AddRange(param); 151 } 152 if (conn.State == ConnectionState.Closed) 153 { 154 conn.Open(); 155 } 156 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 157 //} 158 } 159 160 /// <summary> 161 /// 读取存储过程 162 /// </summary> 163 /// <param name="cmdText"></param> 164 /// <param name="type"></param> 165 /// <param name="param"></param> 166 /// <returns></returns> 167 public static DataTable GetPro(string cmdText, CommandType type, params SqlParameter[] param) 168 { 169 DataTable dt = new DataTable(); 170 using (SqlDataAdapter sda = new SqlDataAdapter(cmdText, connStr)) 171 { 172 new SqlCommand().CommandType = CommandType.StoredProcedure; 173 if (param != null && param.Length != 0) 174 { 175 sda.SelectCommand.Parameters.AddRange(param); 176 } 177 sda.Fill(dt); 178 } 179 return dt; 180 } 181 /// <summary> 182 /// 批量插入数据 183 /// </summary> 184 /// <param name="table">目标数据</param> 185 /// <param name="tableName">表名称</param> 186 /// <param name="mapping"></param> 187 public static void SqlBulkCopyInsert(DataTable table, string tableName, List<SqlBulkCopyColumnMapping> mapping) 188 { 189 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr)) 190 { 191 bulkCopy.DestinationTableName = tableName; 192 if (mapping != null && mapping.Count != 0) 193 { 194 foreach (SqlBulkCopyColumnMapping item in mapping) 195 { 196 bulkCopy.ColumnMappings.Add(item.SourceColumn, item.DestinationColumn); 197 } 198 } 199 200 201 202 bulkCopy.WriteToServer(table); 203 } 204 } 205 206 public static int BatchInert(string connectionString, string desTable, DataTable dt, int batchSize = 500) 207 { 208 using (var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction) 209 { 210 BulkCopyTimeout = 300, 211 NotifyAfter = dt.Rows.Count, 212 BatchSize = batchSize, 213 DestinationTableName = desTable 214 }) 215 { 216 foreach (DataColumn column in dt.Columns) 217 sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName); 218 sbc.WriteToServer(dt); 219 } 220 221 return dt.Rows.Count; 222 } 223 } 224 }