首页 > 代码库 > SqlHelper,JSonHelper类
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 = http://www.mamicode.com/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 =http://www.mamicode.com/ 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 try113 {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 //返回DataTable137 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 //调用带参数的存储过程返回datatable146 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 =http://www.mamicode.com/ tbname;173 parameters[1].Value =http://www.mamicode.com/ fieldkey;174 parameters[2].Value =http://www.mamicode.com/ pagecurrent;175 parameters[3].Value =http://www.mamicode.com/ pagesize;176 parameters[4].Value =http://www.mamicode.com/ fieldshow;177 parameters[5].Value =http://www.mamicode.com/ fieldorder;178 parameters[6].Value =http://www.mamicode.com/ 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 try200 {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 finally224 {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 = http://www.mamicode.com/tableName;>258 // parameters[1].Value = http://www.mamicode.com/getFields;>259 // parameters[2].Value = http://www.mamicode.com/orderName;>260 // parameters[3].Value = http://www.mamicode.com/pageSize;>261 // parameters[4].Value = http://www.mamicode.com/pageIndex;>262 // parameters[5].Value = http://www.mamicode.com/isGetCount ? 1 : 0;>263 // parameters[6].Value = http://www.mamicode.com/orderType ? 1 : 0;>264 // parameters[7].Value = http://www.mamicode.com/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.StoredProcedure290 };291 foreach (SqlParameter parameter in parameters)292 {293 if (parameter != null)294 {295 if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value =http://www.mamicode.com/= null))296 {297 parameter.Value =http://www.mamicode.com/ 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 try323 {324 connection.Open();325 num2 = command.ExecuteNonQuery();326 }327 catch (SqlException exception)328 {329 connection.Close();330 throw exception;331 }332 finally333 {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 else114 {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 else125 {126 JsonString.Append("}, ");127 }128 }129 JsonString.Append("]");130 JsonString.Append("}");131 return JsonString.ToString().Replace("\n", "");132 }133 else134 {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 }
SqlHelper,JSonHelper类
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。