1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Data.SqlClient;
6 using System.Data;
7 using System.Configuration;
8
9 public class SqlHelper
10 {
11 public static readonly string cOnString= ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;
12 //增删改
13 public static bool ExeNonQuery(string sql, CommandType type, params SqlParameter[] lists)
14 {
15 bool bFlag = false;
16 using (SqlConnection con = new SqlConnection(conString))
17 {
18 SqlCommand cmd = new SqlCommand();
19 cmd.COnnection= con;
20 cmd.CommandText = sql;
21 cmd.CommandType = type;
22 if (lists != null)
23 {
24 foreach (SqlParameter p in lists)
25 {
26 cmd.Parameters.Add(p);
27 }
28 }
29 try
30 {
31 if (con.State == ConnectionState.Closed)
32 {
33 con.Open();
34 }
35 int result = cmd.ExecuteNonQuery();
36 if (result > 0)
37 {
38 bFlag = true;
39 }
40
41 }
42 catch { ;}
43 }
44 return bFlag;
45 }
46
47 //查．读
48 public static SqlDataReader ExeDataReader(string sql, CommandType type, params SqlParameter[] lists)
49 {
50 SqlConnection con = new SqlConnection(conString);
51 SqlCommand cmd = new SqlCommand();
52 cmd.COnnection= con;
53 cmd.CommandText = sql;
54 cmd.CommandType = type;
55
56 if (con.State == ConnectionState.Closed)
57 {
58 con.Open();
59 }
60
61 if (lists != null)
62 {
63 foreach (SqlParameter p in lists)
64 {
65 cmd.Parameters.Add(p);
66 }
67 }
68
69 SqlDataReader reader = cmd.ExecuteReader();
70
71 return reader;
72 }
73
74 //返回单个值
75 public static object GetScalar(string sql, CommandType type, params SqlParameter[] lists)
76 {
77 object returnValue = null;
78 using (SqlConnection con = new SqlConnection(conString))
79 {
80 SqlCommand cmd = new SqlCommand();
81 cmd.COnnection= con;
82 cmd.CommandText = sql;
83 cmd.CommandType = type;
84 if (lists != null)
85 {
86 foreach (SqlParameter p in lists)
87 {
88 cmd.Parameters.Add(p);
89 }
90 }
91 try
92 {
93 if (con.State == ConnectionState.Closed)
94 {
95 con.Open();
96 }
97 returnValue = cmd.ExecuteScalar();
98
99 }
100 catch { ; }
101 }
102 return returnValue;
103 }
104
105 //事务
106 public static bool ExeNonQueryTran(List list)
107 {
108 bool flag = true;
109 SqlTransaction tran = null;
110 using (SqlConnection con = new SqlConnection(conString))
111 {
112 try
113 {
114 if (con.State == ConnectionState.Closed)
115 {
116 con.Open();
117 tran = con.BeginTransaction();
118 foreach (SqlCommand com in list)
119 {
120 com.COnnection= con;
121 com.Transaction = tran;
122 com.ExecuteNonQuery();
123 }
124 tran.Commit();
125 }
126 }
127 catch (Exception ex)
128 {
129 Console.Write(ex.Message);
130 tran.Rollback();
131 flag = false;
132 }
133 }
134 return flag;
135 }
136 //返回DataTable
137 public static DataTable GetTable(string sql)
138 {
139 SqlConnection cOnn= new SqlConnection(conString);
140 SqlDataAdapter da = new SqlDataAdapter(sql, conn);
141 DataTable table = new DataTable();
142 da.Fill(table);
143 return table;
144 }
145 ///
146 /// 调用带参数的存储过程，返回dataTable
147 ///
148 /// 存储过程的名称
149 /// 一页几行
150 /// 当前页
151 /// 表名
152 /// dataTable
153 public static DataTable Proc_Table(string proc, int rows, int page, string tabName)
154 {
155 SqlConnection cOnn= new SqlConnection(conString);
156 SqlCommand cmd = new SqlCommand(proc, conn);
157 //指定调用存储过程
158 cmd.CommandType = CommandType.StoredProcedure;
159 cmd.Parameters.Add("@rows", rows);
160 cmd.Parameters.Add("@page", page);
161 cmd.Parameters.Add("@tabName", tabName);
162 SqlDataAdapter apt = new SqlDataAdapter(cmd);
163 DataTable dt = new DataTable();
164 apt.Fill(dt);
165 return dt;
166 }
167
168 //调用带参数的存储过程返回datatable
169 public static DataTable GetTablebyproc(string proc, int pageRow, int pagSize, string tabName)
170 {
171 SqlConnection cOnn= new SqlConnection(conString);
172 SqlCommand cmd = new SqlCommand(proc,conn);
173 cmd.CommandType = CommandType.StoredProcedure;
174 cmd.Parameters.Add("@rows", pageRow);
175 cmd.Parameters.Add("@pagesize", pagSize);
176 cmd.Parameters.Add("@tablename", tabName);
177 SqlDataAdapter apt = new SqlDataAdapter(cmd);
178 DataTable table = new DataTable();
179 apt.Fill(table);
180 return table;
181
182 }
183 public static DataTable GetDataByPager(string tbname, string fieldkey, int pagecurrent, int pagesize, string fieldshow, string fieldorder, string wherestring, ref int pagecount)
184 {
185 SqlParameter[] parameters = {
186 new SqlParameter("@tbname", SqlDbType.VarChar, 100),
187 new SqlParameter("@FieldKey", SqlDbType.VarChar, 100),
188 new SqlParameter("@PageCurrent", SqlDbType.Int),
189 new SqlParameter("@PageSize", SqlDbType.Int),
190 new SqlParameter("@FieldShow", SqlDbType.VarChar, 200),
191 new SqlParameter("@FieldOrder", SqlDbType.VarChar, 200),
192 new SqlParameter("@WhereString", SqlDbType.VarChar, 500),
193 new SqlParameter("@RecordCount", SqlDbType.Int),
194 };
195 parameters[0].Value = tbname;
196 parameters[1].Value = fieldkey;
197 parameters[2].Value = pagecurrent;
198 parameters[3].Value = pagesize;
199 parameters[4].Value = fieldshow;
200 parameters[5].Value = fieldorder;
201 parameters[6].Value = wherestring;
202 parameters[7].Direction = ParameterDirection.Output;
203 DataTable dt = ExecuteQuery("sp_get_data", parameters).Tables[0];
204 pagecount = Convert.ToInt32(parameters[7].Value);
205 return dt;
206 }
207 ///
208 /// 执行有参数的查询类存储过程
209 ///
210 /// 存储过程名
211 /// 存储过程的参数数组
212 /// 查询得到的结果集
213 public static DataSet ExecuteQuery(string pstrStoreProcedure, SqlParameter[] pParms)
214 {
215
216
217 DataSet dsResult = new DataSet();
218 SqlDataAdapter sda = new SqlDataAdapter();
219 SqlConnection con = new SqlConnection(conString);
220 SqlCommand cmd;
221 int intCounter;
222 try
223 {
224 if (con.State != ConnectionState.Open)
225 con.Open();
226 cmd = new SqlCommand();
227 cmd.COnnection= con;
228 cmd.CommandType = CommandType.StoredProcedure;
229 cmd.CommandText = pstrStoreProcedure;
230 if (pParms != null)
231 {
232 for (intCounter = 0; intCounter 0
(((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value ==
京公网安备 11010802041100号