1 2 3 这周在工作室开始与美工部分合作开发网站,自己写了一个简单的sql参数化查询类 4 5 using System; 6 using System.Collections.Generic; 7 using System.Data; 8 using System.Data.SqlClient; 9 using System.Linq; 10 using System.Web; 11 12 namespace bilibili 13 { 14 public static class sqlHelperWithParameter 15 { 16 //public static readonly String conString =ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ToString(); 17 //static string str = @"server=(localdb)\v11.0;Integrated Security=SSPI;database=bilibili";//windows连接字符串 18 static string str = System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString(); 19 /// <summary> 20 /// 连接数据库 21 /// </summary> 22 /// <returns>返回SqlConnection对象</returns> 23 public static SqlConnection GetConnection() 24 { 25 //conn、ConnectionString在web.config 26 //string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString(); 27 ///string str = ConfigurationManager.ConnectionStrings["conn"].ConnectionString; 28 SqlConnection myConn = new SqlConnection(str); 29 return myConn; 30 } 31 /// <summary> 32 /// 查询第一行第一列数据(无参)(返回的是什么类型就转换成什么类型) 33 /// </summary> 34 /// <param name="sql"></param> 35 /// <returns>object</returns> 36 public static object GetScalar(string safeSql) 37 { 38 SqlConnection myConn = GetConnection(); 39 try 40 { 41 myConn.Open(); 42 SqlCommand cmd = new SqlCommand(safeSql, myConn); 43 return cmd.ExecuteScalar(); 44 } 45 catch (Exception ex) 46 { 47 throw new Exception(ex.Message); 48 } 49 finally 50 { 51 myConn.Close(); 52 } 53 } 54 /// <summary> 55 /// 查询第一行第一列数据(有参)(返回的是什么类型就转换成什么类型) 56 /// </summary> 57 /// <param name="sql"></param> 58 /// <param name="values"></param> 59 /// <returns>object</returns> 60 public static object GetScalar(string safeSql, params SqlParameter[] values) 61 { 62 SqlConnection myConn = GetConnection();//连接数据库 63 try 64 { 65 myConn.Open(); 66 SqlCommand cmd = new SqlCommand(safeSql, myConn); 67 cmd.Parameters.AddRange(values); 68 return cmd.ExecuteScalar(); 69 } 70 catch (Exception ex) 71 { 72 throw new Exception(ex.Message); 73 } 74 finally 75 { 76 myConn.Close(); 77 } 78 } 79 /// <summary> 80 /// 执行一个无参增删查改存储过程 81 /// </summary> 82 /// <param name="procName"></param> 83 /// <returns>返回受影响的行数</returns> 84 public static int ExcuteProc(string procName) 85 { 86 SqlConnection myConn = GetConnection(); 87 try 88 { 89 myConn.Open(); 90 SqlCommand cmd = new SqlCommand(procName, myConn); 91 return cmd.ExecuteNonQuery(); 92 } 93 catch (Exception ex) 94 { 95 throw new Exception(ex.Message); 96 } 97 finally 98 { 99 myConn.Close(); 100 } 101 } 102 /// <summary> 103 /// 执行一个有参的增删查改过程 104 /// </summary> 105 /// <param name="procName"></param> 106 /// <param name="values"></param> 107 /// <returns></returns> 108 public static int ExcuteProc(string procName, params SqlParameter[] values) 109 { 110 SqlConnection myConn = GetConnection(); 111 try 112 { 113 myConn.Open(); 114 SqlCommand cmd = new SqlCommand(procName, myConn); 115 cmd.Parameters.AddRange(values); 116 return cmd.ExecuteNonQuery(); 117 } 118 catch (Exception ex) 119 { 120 throw new Exception(ex.Message); 121 } 122 finally 123 { 124 myConn.Close(); 125 } 126 } 127 /// <summary> 128 /// 返回一个dataTable(无参数) 129 /// </summary> 130 /// <param name="safeSql"></param> 131 /// <returns>dataTable</returns> 132 public static DataTable GetDataSet(string safeSql) 133 { 134 SqlConnection myConn = GetConnection(); 135 DataSet ds = new DataSet(); 136 try 137 { 138 myConn.Open(); 139 SqlCommand cmd = new SqlCommand(safeSql, myConn); 140 SqlDataAdapter da = new SqlDataAdapter(cmd); 141 da.Fill(ds); 142 return ds.Tables[0]; 143 } 144 catch (Exception ex) 145 { 146 throw new Exception(ex.Message); 147 } 148 finally 149 { 150 myConn.Close(); 151 } 152 } 153 /// <summary> 154 /// 返回一个Datatable(有参) 155 /// </summary> 156 /// <param name="safeSql"></param> 157 /// <param name="values"></param> 158 /// <returns>dataTable</returns> 159 public static DataTable GetDataSet(string safeSql, params SqlParameter[] values) 160 { 161 SqlConnection myConn = GetConnection(); 162 DataSet ds = new DataSet(); 163 try 164 { 165 myConn.Open(); 166 SqlCommand cmd = new SqlCommand(safeSql, myConn); 167 cmd.Parameters.AddRange(values); 168 SqlDataAdapter da = new SqlDataAdapter(cmd); 169 da.Fill(ds); 170 return ds.Tables[0]; 171 } 172 catch (Exception ex) 173 { 174 throw new Exception(ex.Message); 175 } 176 finally 177 { 178 myConn.Close(); 179 } 180 } 181 /// <summary> 182 /// 返回一个sqlDataReader(注意要关闭) 183 /// </summary> 184 /// <param name="safeSql"></param> 185 /// <returns>SqlDataReader</returns> 186 public static SqlDataReader GetReader(string safeSql) 187 { 188 SqlConnection myConn = GetConnection(); 189 try 190 { 191 myConn.Open(); 192 SqlCommand cmd = new SqlCommand(safeSql, myConn); 193 SqlDataReader reader = cmd.ExecuteReader(); 194 return reader; 195 } 196 catch (Exception ex) 197 { 198 throw new Exception(ex.Message); 199 } 200 finally 201 { 202 myConn.Close(); 203 } 204 } 205 /// <summary> 206 /// 返回一个sqlDataReader(注意要关闭) 207 /// </summary> 208 /// <param name="safeSql"></param> 209 /// <param name="values"></param> 210 /// <returns>sqlDataReader</returns> 211 public static SqlDataReader GetReader(string safeSql,params SqlParameter[] values) 212 { 213 SqlConnection myConn = GetConnection(); 214 try 215 { 216 myConn.Open(); 217 SqlCommand cmd = new SqlCommand(safeSql, myConn); 218 cmd.Parameters.AddRange(values); 219 SqlDataReader reader = cmd.ExecuteReader(); 220 return reader; 221 } 222 catch (Exception ex) 223 { 224 throw new Exception(ex.Message); 225 } 226 finally 227 { 228 myConn.Close(); 229 } 230 } 231 } 232 } 233 234 调用例子: 235 236 /// <summary> 237 /// 验证用户是否存在 238 /// </summary> 239 /// <returns></returns> 240 public object DetermineWhetherAuserExists() 241 { 242 string sqlStr = "select * from [admin] where adminName=@name1"; 243 SqlParameter[] parameter = new SqlParameter[] 244 { 245 new SqlParameter("@name1",SqlDbType.VarChar){Value=_adminName} 246 }; 247 return sqlHelperWithParameter.GetScalar(sqlStr, parameter); 248 }