SqlHelper,JSonHelper类 





  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<SqlCommand> 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    //调用带参数的存储过程返回datatable
146     public static DataTable GetTablebyproc(string proc, int pageRow, int pagSize, string tabName)
147     {
148         SqlConnection conn = new SqlConnection(conString);
149         SqlCommand cmd = new SqlCommand(proc,conn);
150         cmd.CommandType = CommandType.StoredProcedure;
151         cmd.Parameters.Add("@rows", pageRow);
152         cmd.Parameters.Add("@pagesize", pagSize);
153         cmd.Parameters.Add("@tablename", tabName);
154         SqlDataAdapter apt = new SqlDataAdapter(cmd);
155         DataTable table = new DataTable();
156         apt.Fill(table);
157         return table;
158 
159     }
160     public static DataTable GetDataByPager(string tbname, string fieldkey, int pagecurrent, int pagesize, string fieldshow, string fieldorder, string wherestring, ref int pagecount)
161     {
162         SqlParameter[] parameters = {
163                 new SqlParameter("@tbname",   SqlDbType.VarChar, 100),
164                 new SqlParameter("@FieldKey", SqlDbType.VarChar, 100),
165                 new SqlParameter("@PageCurrent", SqlDbType.Int),
166                 new SqlParameter("@PageSize", SqlDbType.Int),
167                 new SqlParameter("@FieldShow", SqlDbType.VarChar, 200),
168                 new SqlParameter("@FieldOrder", SqlDbType.VarChar, 200),
169                 new SqlParameter("@WhereString", SqlDbType.VarChar, 500),
170                 new SqlParameter("@RecordCount", SqlDbType.Int),
171             };
172         parameters[0].Value = tbname;
173         parameters[1].Value = fieldkey;
174         parameters[2].Value = pagecurrent;
175         parameters[3].Value = pagesize;
176         parameters[4].Value = fieldshow;
177         parameters[5].Value = fieldorder;
178         parameters[6].Value = wherestring;
179         parameters[7].Direction = ParameterDirection.Output;
180         DataTable dt = ExecuteQuery("sp_get_data", parameters).Tables[0];
181         pagecount = Convert.ToInt32(parameters[7].Value);
182         return dt;
183     }
184     /// <summary>
185     /// 执行有参数的查询类存储过程
186     /// </summary>
187     /// <param name="pstrStoreProcedure">存储过程名</param>
188     /// <param name="pParms">存储过程的参数数组</param>
189     /// <returns>查询得到的结果集</returns>
190     public static DataSet ExecuteQuery(string pstrStoreProcedure, SqlParameter[] pParms)
191     {
192 
193 
194         DataSet dsResult = new DataSet();
195         SqlDataAdapter sda = new SqlDataAdapter();
196         SqlConnection con = new SqlConnection(conString);
197         SqlCommand cmd;
198         int intCounter;
199         try
200         {
201             if (con.State != ConnectionState.Open)
202                 con.Open();
203             cmd = new SqlCommand();
204             cmd.Connection = con;
205             cmd.CommandType = CommandType.StoredProcedure;
206             cmd.CommandText = pstrStoreProcedure;
207             if (pParms != null)
208             {
209                 for (intCounter = 0; intCounter < pParms.GetLength(0); intCounter++)
210                 {
211                     cmd.Parameters.Add(pParms[intCounter]);
212                 }
213             }
214             sda.SelectCommand = cmd;
215             sda.Fill(dsResult);
216 
217 
218         }
219         catch (SqlException ex)
220         {
221             throw new Exception(ex.Message);
222         }
223         finally
224         {
225             //清空关闭操作
226             sda.Dispose();
227             con.Close();
228             con.Dispose();
229 
230         }
231         return dsResult;
232     }
233     /// <summary>
234     /// 此分页存储过程直没修改 大家可以用自己的
235     /// </summary>
236     /// <param name="tableName">表名</param>
237     /// <param name="getFields">需要返回的列</param>
238     /// <param name="orderName">排序的字段名</param>
239     /// <param name="pageSize">页尺寸</param>
240     /// <param name="pageIndex">页码</param>
241     /// <param name="isGetCount">返回记录总数,非 0 值则返回</param>
242     /// <param name="orderType">设置排序类型,0表示升序非0降序</param>
243     /// <param name="strWhere"></param>
244     /// <returns></returns>
245     //public static DataSet GetList(string tableName, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)
246     //{
247     //    SqlParameter[] parameters = {
248     //            new SqlParameter("@tblName", SqlDbType.VarChar, 255),
249     //            new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
250     //            new SqlParameter("@fldName", SqlDbType.VarChar, 255),
251     //          new SqlParameter("@PageSize", SqlDbType.Int),
252     //       new SqlParameter("@PageIndex", SqlDbType.Int),
253     //        new SqlParameter("@doCount", SqlDbType.Bit),
254     //            new SqlParameter("@OrderType", SqlDbType.Bit),
255     //            new SqlParameter("@strWhere", SqlDbType.VarChar, 1500)            
256     //                             };
257     //    parameters[0].Value = tableName;
258     //    parameters[1].Value = getFields;
259     //    parameters[2].Value = orderName;
260     //    parameters[3].Value = pageSize;
261     //    parameters[4].Value = pageIndex;
262     //    parameters[5].Value = isGetCount ? 1 : 0;
263     //    parameters[6].Value = orderType ? 1 : 0;
264     //    parameters[7].Value = strWhere;
265     //    return SqlHelper.RunProcedure("pro_pageList", parameters, "ds");
266     //}
267     //public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
268     //{
269     //    using (SqlConnection connection = new SqlConnection(conString))
270     //    {
271     //        DataSet dataSet = new DataSet();
272     //        connection.Open();
273     //        new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName);
274     //        connection.Close();
275     //        return dataSet;
276     //    }
277     //}
278     /// <summary>
279     /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
280     /// </summary>
281     /// <param name="connection">数据库连接</param>
282     /// <param name="storedProcName">存储过程名</param>
283     /// <param name="parameters">存储过程参数</param>
284     /// <returns>SqlCommand</returns>
285     private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
286     {
287         SqlCommand command = new SqlCommand(storedProcName, connection)
288         {
289             CommandType = CommandType.StoredProcedure
290         };
291         foreach (SqlParameter parameter in parameters)
292         {
293             if (parameter != null)
294             {
295                 if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
296                 {
297                     parameter.Value = DBNull.Value;
298                 }
299                 command.Parameters.Add(parameter);
300             }
301         }
302         return command;
303     }
304     //根据表名和主键id来进行删除
305     public static int DelData(string tabName, string ID)
306     {
307         if (ID != string.Empty && ID != "0")
308         {
309             string sql = string.Format("delete from {0}  WHERE (ID IN ({1}))", tabName, ID);
310             int delNum = ExecuteSql(sql);
311             return delNum;
312         }
313         return 0;
314     }
315     //增删改返回执行条数
316     public static int ExecuteSql(string SQLString)
317     {
318         int num2;
319         using (SqlConnection connection = new SqlConnection(conString))
320         {
321             SqlCommand command = new SqlCommand(SQLString, connection);
322             try
323             {
324                 connection.Open();
325                 num2 = command.ExecuteNonQuery();
326             }
327             catch (SqlException exception)
328             {
329                 connection.Close();
330                 throw exception;
331             }
332             finally
333             {
334                 if (command != null)
335                 {
336                     command.Dispose();
337                 }
338             }
339         }
340         return num2;
341     }
342 }






 


  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Text;
  6 using System.Web.Script.Serialization;
  7 using System.Data;
  8 
  9     public class JSonHelper
 10     {
 11 
 12         public static string CreateJson(DataTable table)
 13         {
 14             string jsname = "total";
 15             StringBuilder json = new StringBuilder("{\""+jsname+"\":[");
 16             if (table.Rows.Count > 0)
 17             {
 18                 foreach (DataRow row in table.Rows)
 19                 {
 20                     json.Append("{");
 21                     foreach (DataColumn column in table.Columns)
 22                     {
 23                         json.Append("\""+column.ColumnName+"\":\""+row[column.ColumnName].ToString()+"\",");
 24                     }
 25                     json.Remove(json.Length - 1, 1);
 26                     json.Append("},");
 27                 }
 28                 json.Remove(json.Length - 1, 1);
 29             }
 30             json.Append("]}");
 31             return json.ToString();
 32         }
 33         public static string CreateJsons(DataTable dt, bool flag, int count)
 34         {
 35             StringBuilder json = new StringBuilder();
 36             if (dt.Rows.Count > 0)
 37             {
 38                 json.Append("{");
 39                 if (flag)
 40                 {
 41                     json.Append("\"total\":");
 42                     json.Append(count);
 43                     json.Append(",");
 44                 }
 45                 json.Append("\"rows\":[");
 46                 foreach (DataRow row in dt.Rows)
 47                 {
 48                     json.Append("{");
 49                     foreach (DataColumn column in dt.Columns)
 50                     {
 51                         json.Append("\"JSON_"+column.ColumnName.ToLower()+"\":\""+row[column.ColumnName].ToString()+"\",");
 52                     }
 53                     json.Remove(json.Length - 1, 1);
 54                     json.Append("},");
 55                 }
 56                 json.Remove(json.Length - 1, 1);
 57             }
 58             json.Append("]}");
 59             return json.ToString();
 60         }
 61 
 62 
 63         public static string CreateJsonParameters(DataTable dt, bool displayCount, int totalcount)
 64         {
 65             StringBuilder JsonString = new StringBuilder();
 66             //Exception Handling        
 67             if (dt != null)
 68             {
 69                 JsonString.Append("{ ");
 70                 if (displayCount)
 71                 {
 72                     JsonString.Append("\"total\":");
 73                     JsonString.Append(totalcount);
 74                     JsonString.Append(",");
 75                 }
 76                 JsonString.Append("\"rows\":[ ");
 77                 for (int i = 0; i < dt.Rows.Count; i++)
 78                 {
 79                     JsonString.Append("{ ");
 80                     for (int j = 0; j < dt.Columns.Count; j++)
 81                     {
 82                         if (j < dt.Columns.Count - 1)
 83                         {
 84                             //if (dt.Rows[i][j] == DBNull.Value) continue;
 85                             if (dt.Columns[j].DataType == typeof(bool))
 86                             {
 87                                 JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" +
 88                                                   dt.Rows[i][j].ToString().ToLower() + ",");
 89                             }
 90                             else if (dt.Columns[j].DataType == typeof(string))
 91                             {
 92                                 JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
 93                                                   dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\",");
 94                             }
 95                             else
 96                             {
 97                                 JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\",");
 98                             }
 99                         }
100                         else if (j == dt.Columns.Count - 1)
101                         {
102                             //if (dt.Rows[i][j] == DBNull.Value) continue;
103                             if (dt.Columns[j].DataType == typeof(bool))
104                             {
105                                 JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" +
106                                                   dt.Rows[i][j].ToString().ToLower());
107                             }
108                             else if (dt.Columns[j].DataType == typeof(string))
109                             {
110                                 JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
111                                                   dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\"");
112                             }
113                             else
114                             {
115                                 JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\"");
116                             }
117                         }
118                     }
119                     /*end Of String*/
120                     if (i == dt.Rows.Count - 1)
121                     {
122                         JsonString.Append("} ");
123                     }
124                     else
125                     {
126                         JsonString.Append("}, ");
127                     }
128                 }
129                 JsonString.Append("]");
130                 JsonString.Append("}");
131                 return JsonString.ToString().Replace("\n", "");
132             }
133             else
134             {
135                 return null;
136             }
137         }
138         public static string DataTableToJson(DataTable table, string name)
139         {
140             StringBuilder Json = new StringBuilder("{\""+name+"\":[");
141             if (table.Rows.Count > 0)
142             {
143                 foreach (DataRow row in table.Rows)
144                 {
145                     Json.Append("{");
146                     foreach (DataColumn cloumn in table.Columns)
147                     {
148                         Json.Append("\""+cloumn.ColumnName+"\":\""+row[cloumn.ColumnName].ToString()+"\",");
149                     }
150                     Json.Remove(Json.Length - 1, 1);
151                     Json.Append("},");
152                 }
153                 Json.Remove(Json.Length - 1, 1);
154             }
155             Json.Append("]}");
156             return Json.ToString();
157         }
158     }




 







		

		
posted @ 
2015-01-23 14:53 
Bodyjiang 
阅读(560) 
评论(0) 
编辑 
收藏 
举报