.Net平台下的SqlHelper

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117

class SqlHelper     {         //通过ConfigurationManager可以读到App.config中的配置信息         private static string conStr = ConfigurationManager.ConnectionStrings["dbConStr"].ConnectionString;           /// <summary>         /// 将null转换成数据库中的DBNull         /// </summary>         /// <param name="value"></param>         /// <returns></returns>         public static object ToDBValue(object value)         {             if (value == null)                 value = DBNull.Value;             return value;         }           /// <summary>         /// 从数据库中获得DBNull数据转换成null         /// </summary>         /// <param name="value"></param>         /// <returns></returns>         public static object FromDBValue(object value)         {             if (value == DBNull.Value)                 value = null;             return value;         }           /// <summary>         /// 执行不查询sql语句         /// </summary>         /// <param name="sql">要执行的sql语句</param>         /// <returns>执行完sql语句后，数据库受影响的行数</returns>         public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)         {             using (SqlConnection sqlCon = new SqlConnection(conStr))             {                 sqlCon.Open();                 using (SqlCommand sqlCmd = sqlCon.CreateCommand())                 {                     sqlCmd.CommandText = sql;                     //foreach (SqlParameter param in parameters)                     //{                     //    sqlCmd.Parameters.Add(param);                     //}                     sqlCmd.Parameters.AddRange(parameters);                     return sqlCmd.ExecuteNonQuery();                 }             }         }           /// <summary>         /// 执行查询         /// </summary>         /// <param name="sql">要执行的sql语句</param>         /// <returns>返回查询结果中的第一行第一列</returns>         public static object ExecuteScaler(string sql,params SqlParameter[] parameters)         {             using (SqlConnection sqlCon = new SqlConnection(conStr))             {                 sqlCon.Open();                 using (SqlCommand sqlCmd = sqlCon.CreateCommand())                 {                     sqlCmd.CommandText = sql;                     sqlCmd.Parameters.AddRange(parameters);                     return sqlCmd.ExecuteScalar();                 }             }         }           /// <summary>         /// 执行离线数据集查询         /// </summary>         /// <param name="sql">要执行的sql语句</param>         /// <returns>返回离线数据集</returns>         public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)         {             using (SqlConnection sqlCon = new SqlConnection(conStr))             {                 sqlCon.Open();                 using (SqlCommand sqlCmd = sqlCon.CreateCommand())                 {                     sqlCmd.CommandText = sql;                     sqlCmd.Parameters.AddRange(parameters);                     SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);                     DataSet dataSet = new DataSet();                     sqlAdapter.Fill(dataSet);                     return dataSet.Tables[0];                 }             }         }           /// <summary>         /// 执行存储过程         /// </summary>         /// <param name="storedProc"></param>         /// <param name="parameters"></param>         /// <returns></returns>         public static int ExecuteStoredProc(string storedProc,params SqlParameter[] parameters)         {             using (SqlConnection conn = new SqlConnection(conStr))             {                 using (SqlCommand sqlCmd = new SqlCommand(storedProc, conn))                 {                     //sql命令为存储过程                     sqlCmd.CommandType = CommandType.StoredProcedure;                     //添加参数                     sqlCmd.Parameters.AddRange(parameters);                     //晚打开，早关闭                     conn.Open();                     return sqlCmd.ExecuteNonQuery();                 }             }           }     }