SQLHelper

  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 }