你的位置:首页 > 软件开发 > ASP.net > C#实现较为实用的SQLhelper

C#实现较为实用的SQLhelper

发布时间:2016-10-05 21:01:17
第一次写博客,想不到写什么好b( ̄▽ ̄)d ,考虑的半天决定从sqlhelper开始,sqlhelper对程序员来说就像helloworld一样,很简单却又很重要,helloworld代表着程序员萌新第一次写代码,而sqlhelper则是初次接触数据库(不知道这种说法对不对)。  ...

  第一次写博客,想不到写什么好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(get='_blank'>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     /// 查询数据,返回DataSet110     /// </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       try128       {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       try172       {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       try215       {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     /// 查询数据,返回DataTable245     /// </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     /// 执行存储过程查询数据,返回DataSet259     /// </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         {319           parm = new SqlParameter(kv.Key, DBNull.Value);320         }321         else322         {323           Type t = kv.Value.GetType();324           parm = new SqlParameter(kv.Key, NetToSql(kv.Value.GetType()));325           parm.Value = kv.Value;326         }327 328         parms[index++] = parm;329       }330       return parms;331     }332 333 334     /// <summary>335     /// .net类型转换为Sql类型336     /// </summary>337     /// <param name="t">.net类型</param>338     /// <returns>Sql类型</returns>339     public static SqlDbType NetToSql(Type t)340     {341       SqlDbType dbType = SqlDbType.Variant;342       switch (t.Name)343       {344         case "Int16":345           dbType = SqlDbType.SmallInt;346           break;347         case "Int32":348           dbType = SqlDbType.Int;349           break;350         case "Int64":351           dbType = SqlDbType.BigInt;352           break;353         case "Single":354           dbType = SqlDbType.Real;355           break;356         case "Decimal":357           dbType = SqlDbType.Decimal;358           break;359 360         case "Byte[]":361           dbType = SqlDbType.VarBinary;362           break;363         case "Boolean":364           dbType = SqlDbType.Bit;365           break;366         case "String":367           dbType = SqlDbType.NVarChar;368           break;369         case "Char[]":370           dbType = SqlDbType.Char;371           break;372         case "DateTime":373           dbType = SqlDbType.DateTime;374           break;375         case "DateTime2":376           dbType = SqlDbType.DateTime2;377           break;378         case "DateTimeOffset":379           dbType = SqlDbType.DateTimeOffset;380           break;381         case "TimeSpan":382           dbType = SqlDbType.Time;383           break;384         case "Guid":385           dbType = SqlDbType.UniqueIdentifier;386           break;387         case "":388           dbType = SqlDbType.389           break;390         case "Object":391           dbType = SqlDbType.Variant;392           break;393       }394       return dbType;395     }396 397   }

原标题:C#实现较为实用的SQLhelper

关键词:C#

C#
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

可能感兴趣文章

我的浏览记录