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 }
浙公网安备 33010602011771号