客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
第一次写博客，想不到写什么好b（￣▽￣）d ，考虑的半天决定从sqlhelper开始，sqlhelper对程序员来说就像helloworld一样，很简单却又很重要，helloworld代表着程序员萌新第一次写代码，而sqlhelper则是初次接触数据库（不知道这种说法对不对）。 好了不废话了，下面直接上代码（无话可说了）： 1 public class SQLHelper 2 { 3 // 超时时间 4 private static int Timeout = 1000; 5 // 数据库名称 6 public const String BestNet = "BestNet"; 7 //存储过程名称 8 public const String UserInfoCURD = "UserInfoCURD"; 9 // 数据库连接字符串 10 private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>(); 11 12 /// <summary> 13 /// SQLServer操作类(静态构造函数) 14 /// </summary> 15 static SQLHelper() 16 { 17 ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings; 18 foreach (ConnectionStringSettings config in configs) 19 { 20 ConnStrs.Add(config.Name, config.ConnectionString); 21 } 22 } 23 24 /// <summary> 25 /// 获取数据库连接 26 /// </summary> 27 /// <param name="database">数据库(配置文件内connectionStrings的name)</param> 28 /// <returns>数据库连接</returns> 29 private static SqlConnection GetConnection(string database) 30 { 31 if (string.IsNullOrEmpty(database)) 32 { 33 throw new Exception("未设置参数：database"); 34 } 35 if (!ConnStrs.ContainsKey(database)) 36 { 37 throw new Exception("未找到数据库：" + database); 38 } 39 return new SqlConnection(ConnStrs[database]); 40 } 41 42 /// <summary> 43 /// 获取SqlCommand 44 /// </summary> 45 /// <param name="conn">SqlConnection</param> 46 /// <param name="transaction">SqlTransaction</param> 47 /// <param name="cmdType">CommandType</param> 48 /// <param name="sql">SQL</param> 49 /// <param name="parms">SqlParameter数组</param> 50 /// <returns></returns> 51 private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms) 52 { 53 SqlCommand cmd = new SqlCommand(sql, conn); 54 cmd.CommandType = cmdType; 55 cmd.CommandTimeout = Timeout; 56 if (transaction != null) 57 cmd.Transaction = transaction; 58 if (parms != null && parms.Length != 0) 59 cmd.Parameters.AddRange(parms); 60 return cmd; 61 } 62 63 /// <summary> 64 /// 查询数据，返回DataTable 65 /// </summary> 66 /// <param name="database">数据库</param> 67 /// <param name="sql">SQL语句或存储过程名</param> 68 /// <param name="parms">参数</param> 69 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 70 /// <returns>DataTable</returns> 71 public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType) 72 { 73 if (string.IsNullOrEmpty(database)) 74 { 75 throw new Exception("未设置参数：database"); 76 } 77 if (string.IsNullOrEmpty(sql)) 78 { 79 throw new Exception("未设置参数：sql"); 80 } 81 82 try 83 { 84 using (SqlConnection conn = GetConnection(database)) 85 { 86 conn.Open(); 87 88 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms)) 89 { 90 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 91 { 92 DataTable dt = new DataTable(); 93 da.Fill(dt); 94 return dt; 95 } 96 } 97 } 98 } 99 catch (SqlException ex) 100 { 101 System.Text.StringBuilder log = new System.Text.StringBuilder(); 102 log.Append("查询数据出错："); 103 log.Append(ex); 104 throw new Exception(log.ToString()); 105 } 106 } 107 108 /// <summary> 109 /// 查询数据，返回DataSet 110 /// </summary> 111 /// <param name="database">数据库</param> 112 /// <param name="sql">SQL语句或存储过程名</param> 113 /// <param name="parms">参数</param> 114 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 115 /// <returns>DataSet</returns> 116 public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType) 117 { 118 if (string.IsNullOrEmpty(database)) 119 { 120 throw new Exception("未设置参数：database"); 121 } 122 if (string.IsNullOrEmpty(sql)) 123 { 124 throw new Exception("未设置参数：sql"); 125 } 126 127 try 128 { 129 using (SqlConnection conn = GetConnection(database)) 130 { 131 conn.Open(); 132 133 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms)) 134 { 135 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 136 { 137 DataSet ds = new DataSet(); 138 da.Fill(ds); 139 return ds; 140 } 141 } 142 } 143 } 144 catch (SqlException ex) 145 { 146 System.Text.StringBuilder log = new System.Text.StringBuilder(); 147 log.Append("查询数据出错："); 148 log.Append(ex); 149 throw new Exception(log.ToString()); 150 } 151 } 152 153 /// <summary> 154 /// 执行命令获取唯一值(第一行第一列) 155 /// </summary> 156 /// <param name="database">数据库</param> 157 /// <param name="sql">SQL语句或存储过程名</param> 158 /// <param name="parms">参数</param> 159 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 160 /// <returns>获取值</returns> 161 public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType) 162 { 163 if (string.IsNullOrEmpty(database)) 164 { 165 throw new Exception("未设置参数：database"); 166 } 167 if (string.IsNullOrEmpty(sql)) 168 { 169 throw new Exception("未设置参数：sql"); 170 } 171 try 172 { 173 using (SqlConnection conn = GetConnection(database)) 174 { 175 conn.Open(); 176 177 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms)) 178 { 179 return cmd.ExecuteScalar(); 180 } 181 } 182 } 183 catch (SqlException ex) 184 { 185 System.Text.StringBuilder log = new System.Text.StringBuilder(); 186 log.Append("处理出错："); 187 log.Append(ex); 188 throw new Exception(log.ToString()); 189 } 190 } 191 192 /// <summary> 193 /// 执行命令更新数据 194 /// </summary> 195 /// <param name="database">数据库</param> 196 /// <param name="sql">SQL语句或存储过程名</param> 197 /// <param name="parms">参数</param> 198 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 199 /// <returns>更新的行数</returns> 200 public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType) 201 { 202 if (string.IsNullOrEmpty(database)) 203 { 204 throw new Exception("未设置参数：database"); 205 } 206 if (string.IsNullOrEmpty(sql)) 207 { 208 throw new Exception("未设置参数：sql"); 209 } 210 211 //返回(增删改)的更新行数 212 int count = 0; 213 214 try 215 { 216 using (SqlConnection conn = GetConnection(database)) 217 { 218 conn.Open(); 219 220 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms)) 221 { 222 if (cmdType == CommandType.StoredProcedure) 223 cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue; 224 225 count = cmd.ExecuteNonQuery(); 226 227 if (count <= 0) 228 if (cmdType == CommandType.StoredProcedure) 229 count = (int)cmd.Parameters["@RETURN_VALUE"].Value; 230 } 231 } 232 } 233 catch (SqlException ex) 234 { 235 System.Text.StringBuilder log = new System.Text.StringBuilder(); 236 log.Append("处理出错："); 237 log.Append(ex); 238 throw new Exception(log.ToString()); 239 } 240 return count; 241 } 242 243 /// <summary> 244 /// 查询数据，返回DataTable 245 /// </summary> 246 /// <param name="database">数据库</param> 247 /// <param name="sql">SQL语句或存储过程名</param> 248 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 249 /// <param name="values">参数</param> 250 /// <returns>DataTable</returns> 251 public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary<string, object> values) 252 { 253 SqlParameter[] parms = DicToParams(values); 254 return QueryDataTable(database, sql, parms, cmdType); 255 } 256 257 /// <summary> 258 /// 执行存储过程查询数据，返回DataSet 259 /// </summary> 260 /// <param name="database">数据库</param> 261 /// <param name="sql">SQL语句或存储过程名</param> 262 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 263 /// <param name="values">参数 264 /// <returns>DataSet</returns> 265 public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary<string, object> values) 266 { 267 SqlParameter[] parms = DicToParams(values); 268 return QueryDataSet(database, sql, parms, cmdType); 269 } 270 271 /// <summary> 272 /// 执行命令获取唯一值(第一行第一列) 273 /// </summary> 274 /// <param name="database">数据库</param> 275 /// <param name="sql">SQL语句或存储过程名</param> 276 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 277 /// <param name="values">参数</param> 278 /// <returns>唯一值</returns> 279 public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary<string, object> values) 280 { 281 SqlParameter[] parms = DicToParams(values); 282 return QueryScalar(database, sql, parms, cmdType); 283 } 284 285 /// <summary> 286 /// 执行命令更新数据 287 /// </summary> 288 /// <param name="database">数据库</param> 289 /// <param name="sql">SQL语句或存储过程名</param> 290 /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 291 /// <param name="values">参数</param> 292 /// <returns>更新的行数</returns> 293 public static int Execute(string database, string sql, CommandType cmdType, IDictionary<string, object> values) 294 { 295 SqlParameter[] parms = DicToParams(values); 296 return Execute(database, sql, parms, cmdType); 297 } 298 299 /// <summary> 300 /// 创建参数 301 /// </summary> 302 /// <param name="name">参数名</param> 303 /// <param name="type">参数类型</param> 304 /// <param name="size">参数大小</param> 305 /// <param name="direction">参数方向(输入/输出)</param> 306 /// <param name="value">参数值</param> 307 /// <returns>新参数对象</returns> 308 public static SqlParameter[] DicToParams(IDictionary<string, object> values) 309 { 310 if (values == null) return null; 311 312 SqlParameter[] parms = new SqlParameter[values.Count]; 313 int index = 0; 314 foreach (KeyValuePair<string, object> kv in values) 315 { 316 SqlParameter parm = null; 317 if (kv.Value == null) 318 全部评论
专题导读
热门推荐
热门话题
 阅读排行榜
|
请发表评论