你的位置:首页 > ASP.net教程

[ASP.net教程]C#实现较为实用的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(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   }

  可以直接这样调用:

1 IDictionary<string, object> values = new Dictionary<string, object>();2 values.Add("@UserName", UserName);      3 values.Add("@PassWord", passWord);4 object Scalar = SQLHelper.QueryScalar(SQLHelper.BestNet, SQLHelper.UserInfoCURD, CommandType.StoredProcedure, values);  

 




泰国旅游报价到泰国旅游团购去泰国旅游攻略泰国六日游行程安排参考去泰国旅游跟团行程番禺百万葵园冬季景色怎么样?百万葵园冬天有什么特色? 过年三亚旅游怎么样?三亚有哪些好玩的地方? 广州百万葵园现在多少钱?番禺百万葵园门票最新价格? 2015三亚春节好玩的地方?春节三亚自由行攻略? 仓桥直街 遍地风流是绍兴 大亚湾小桂旅游攻略?小桂景点好玩吗? 惠州大亚湾旅游吃在哪里?大亚湾哪里有烧烤? 惠州大亚湾旅游度假去哪玩好?大亚湾有没有漂流水上乐园的地方? 东部华侨城水公园是温水还是冷水?深圳东部华侨城水公园介绍? 东部华侨城水公园好玩吗?深圳东部华侨城水公园怎么样? 阳山小桂林漂流价格?小桂林竹筏漂流几个人的? 和平漂流位置?河源和平漂流在哪里? 广州香格里拉大酒店推出“香传秋韵”系列月饼[二] 秦始皇陵兵马俑在哪?怎么去? 珠海石景山观光索道费用?石景山观光索道多少钱一个人? 石景山公园缆车费是往返的吗?珠海石景山公园门票包含缆车吗? MJ01-0603SGC Datasheet MJ01-0603SGC Datasheet MJ01-0603SYC Datasheet MJ01-0603SYC Datasheet MJ01-0603T2GC Datasheet MJ01-0603T2GC Datasheet 成都大熊猫基地门票 成都大熊猫基地门票 成都大熊猫基地门票 龙门石窟在哪 龙门石窟在哪 龙门石窟在哪 广州长隆水上乐园门票 广州长隆水上乐园门票 广州长隆水上乐园门票