1 //webconfig配置文件 2 <connectionStrings> 3 <add name="ConnectionString" connectionString="Data Source=.;pwd=123456;Initial Catalog=gjfj;User ID=sa" providerName="System.Data.SqlClient"/> 4 </connectionStrings> 5 <appSettings> 6 <add key="ConnectionString" value="server=.;database=gjfj;uid=sa;pwd=123456;"/> 7 </appSettings> 8 9 10 //DBHelper.cs 11 public static class DBHelper 12 { 13 //数据库连接属性,从config配置文件中获取连接字符串connectionString 14 private static string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();//数据库连接字符串 15 16 private static SqlConnection connection; 17 public static SqlConnection Connection 18 { 19 20 21 get 22 { 23 string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();//数据库连接字符串 24 // string connectionString = " server=.;database=ezwell;uid=sa;pwd=123456;";//数据库连接字符串 25 26 if (connection == null) 27 { 28 using (connection = new SqlConnection(connectionString)) 29 { 30 connection.Open(); 31 } 32 } 33 else if (connection.State == System.Data.ConnectionState.Closed) 34 { 35 connection.Open(); 36 } 37 else if (connection.State == System.Data.ConnectionState.Broken) 38 { 39 connection.Close(); 40 connection.Open(); 41 } 42 return connection; 43 } 44 } 45 /// <summary> 46 /// 关闭数据库连接 47 /// </summary> 48 public static void Close() 49 { 50 ///判断连接是否已经创建 51 if (connection != null) 52 { 53 ///判断连接的状态是否打开 54 if (connection.State == ConnectionState.Open) 55 { 56 connection.Close(); 57 } 58 } 59 } 60 61 /// <summary> 62 /// 释放资源 63 /// </summary> 64 65 public static void Dispose() 66 { 67 // 确认连接是否已经关闭 68 if (connection != null) 69 { 70 connection.Dispose(); 71 connection = null; 72 } 73 } 74 75 /// <summary> 76 /// 执行无参SQL语句，并返回执行记录数 77 /// </summary> 78 /// <param name="safeSql">sql字符串</param> 79 /// <returns>受影响的行数</returns> 80 public static int ExecuteCommand(string safeSql) 81 { 82 SqlConnection sqlConn = new SqlConnection(connectionString); 83 sqlConn.Open(); 84 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 85 int result = cmd.ExecuteNonQuery(); 86 sqlConn.Close(); 87 return result; 88 } 89 /// <summary> 90 /// 执行有参SQL语句，并返回执行记录数 91 /// </summary> 92 /// <param name="safeSql">sql字符串</param> 93 /// <param name="values">参数值</param> 94 /// <returns>受影响的行数</returns> 95 public static int ExecuteCommand(string sql, params SqlParameter[] values) 96 { 97 SqlConnection sqlConn = new SqlConnection(connectionString); 98 sqlConn.Open(); 99 SqlCommand cmd = new SqlCommand(sql, sqlConn); 100 cmd.Parameters.AddRange(values); 101 int result = cmd.ExecuteNonQuery(); 102 sqlConn.Close(); 103 return result; 104 } 105 /// <summary> 106 /// 执行无参存储过程，并返回受影响的行数。 107 /// </summary> 108 /// <param name="safeSql">存储过程名</param> 109 /// <returns>受影响的行数</returns> 110 public static int ExecuteProcCommand(string safeSql) 111 { 112 SqlConnection sqlConn = new SqlConnection(connectionString); 113 sqlConn.Open(); 114 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 115 cmd.CommandType = CommandType.StoredProcedure; 116 int result = cmd.ExecuteNonQuery(); 117 sqlConn.Close(); 118 return result; 119 } 120 /// <summary> 121 /// 执行带参存储过程，并返回受影响的行数。 122 /// </summary> 123 /// <param name="safeSql">存储过程名</param> 124 /// <param name="values">存储过程参数值</param> 125 /// <returns>受影响的行数</returns> 126 public static int ExecuteProcCommand(string safeSql, params SqlParameter[] values) 127 { 128 SqlConnection sqlConn = new SqlConnection(connectionString); 129 sqlConn.Open(); 130 131 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 132 cmd.CommandType = CommandType.StoredProcedure; 133 cmd.Parameters.AddRange(values); 134 int result = cmd.ExecuteNonQuery(); 135 sqlConn.Close(); 136 return result; 137 } 138 /// <summary> 139 /// 执行无参SQL语句，并返回首行首列数据。 140 /// </summary> 141 /// <param name="safeSql">sql字符串</param> 142 /// <returns>首行首列数据</returns> 143 public static int ExecuteGetScalar(string safeSql) 144 { 145 SqlConnection sqlConn = new SqlConnection(connectionString); 146 sqlConn.Open(); 147 148 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 149 int result = Convert.ToInt32(cmd.ExecuteScalar()); 150 sqlConn.Close(); 151 return result; 152 } 153 /// <summary> 154 /// 执行有参SQL语句，并返回首行首列数据。 155 /// </summary> 156 /// <param name="safeSql">sql字符串</param> 157 /// <param name="values">参数值</param> 158 /// <returns>首行首列数据</returns> 159 public static int ExecuteGetScalar(string sql, params SqlParameter[] values) 160 { 161 SqlConnection sqlConn = new SqlConnection(connectionString); 162 sqlConn.Open(); 163 SqlCommand cmd = new SqlCommand(sql, sqlConn); 164 cmd.Parameters.AddRange(values); 165 int result = Convert.ToInt32(cmd.ExecuteScalar()); 166 sqlConn.Close(); 167 return result; 168 } 169 /// <summary> 170 /// 执行无参存储过程，并返回首行首列数据。 171 /// </summary> 172 /// <param name="safeSql">存储过程名</param> 173 /// <returns>首行首列数据</returns> 174 public static int ExecuteProcGetScalar(string safeSql) 175 { 176 SqlConnection sqlConn = new SqlConnection(connectionString); 177 sqlConn.Open(); 178 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 179 cmd.CommandType = CommandType.StoredProcedure; 180 int result = Convert.ToInt32(cmd.ExecuteScalar()); 181 sqlConn.Close(); 182 return result; 183 } 184 /// <summary> 185 /// 执行有参存储过程，并返回首行首列数据。 186 /// </summary> 187 /// <param name="safeSql">存储过程名</param> 188 /// <param name="values">参数值</param> 189 /// <returns>首行首列数据</returns> 190 public static int ExecuteProcGetScalar(string sql, params SqlParameter[] values) 191 { 192 SqlConnection sqlConn = new SqlConnection(connectionString); 193 194 sqlConn.Open(); 195 SqlCommand cmd = new SqlCommand(sql, sqlConn); 196 cmd.CommandType = CommandType.StoredProcedure; 197 cmd.Parameters.AddRange(values); 198 int result = Convert.ToInt32(cmd.ExecuteScalar()); 199 sqlConn.Close(); 200 return result; 201 202 } 203 /// <summary> 204 /// 执行无参SQL语句，并返回SqlDataReader 205 /// </summary> 206 /// <param name="safeSql">sql字符串</param> 207 /// <returns>返回DataReader</returns> 208 public static SqlDataReader ExecuteGetReader(string safeSql) 209 { 210 SqlConnection sqlConn = new SqlConnection(connectionString); 211 { 212 sqlConn.Open(); 213 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 214 try 215 { 216 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 217 return reader; 218 } 219 catch 220 { 221 sqlConn.Close(); 222 throw; 223 } 224 225 } 226 } 227 /// <summary> 228 /// 执行有参SQL语句，并返回SqlDataReader 229 /// </summary> 230 /// <param name="safeSql">sql字符串</param> 231 /// <param name="values">参数值</param> 232 /// <returns>返回DataReader</returns> 233 public static SqlDataReader ExecuteGetReader(string sql, params SqlParameter[] values) 234 { 235 SqlConnection sqlConn = new SqlConnection(connectionString); 236 237 sqlConn.Open(); 238 SqlCommand cmd = new SqlCommand(sql, sqlConn); 239 cmd.Parameters.AddRange(values); 240 try 241 { 242 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 243 return reader; 244 } 245 catch 246 { 247 sqlConn.Close(); 248 throw; 249 } 250 251 252 } 253 254 /// <summary> 255 /// 执行无参存储过程，并返回SqlDataReader 256 /// </summary> 257 /// <param name="safeSql">存储过程名</param> 258 /// <returns>返回DataReader</returns> 259 public static SqlDataReader ExecuteProcGetReader(string safeSql) 260 { 261 SqlConnection sqlConn = new SqlConnection(connectionString); 262 263 sqlConn.Open(); 264 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 265 cmd.CommandType = CommandType.StoredProcedure; 266 try 267 { 268 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 269 return reader; 270 } 271 catch 272 { 273 sqlConn.Close(); 274 throw; 275 } 276 277 278 } 279 /// <summary> 280 /// 执行有参存储过程，并返回SqlDataReader 281 /// </summary> 282 /// <param name="safeSql">存储过程名</param> 283 /// <param name="values">参数值</param> 284 /// <returns>返回DataReader</returns> 285 public static SqlDataReader ExecuteProcGetReader(string sql, params SqlParameter[] values) 286 { 287 SqlConnection sqlConn = new SqlConnection(connectionString); 288 289 sqlConn.Open(); 290 SqlCommand cmd = new SqlCommand(sql, sqlConn); 291 cmd.CommandType = CommandType.StoredProcedure; 292 cmd.Parameters.AddRange(values); 293 294 try 295 { 296 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 297 298 return reader; 299 } 300 catch 301 { 302 sqlConn.Close(); 303 throw; 304 } 305 306 } 307 308 309 public static SqlDataReader ExecuteProcGetReader2(string sql, params SqlParameter[] values) 310 { 311 string connectionString = ConfigurationManager.AppSettings["HuachenManagerConnectionString"].ToString();//数据库连接字符串 312 SqlConnection connection2 = new SqlConnection(connectionString); 313 connection2.Open(); 314 315 SqlCommand cmd = new SqlCommand(sql, connection2); 316 cmd.CommandType = CommandType.StoredProcedure; 317 cmd.Parameters.AddRange(values); 318 try 319 { 320 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 321 322 return reader; 323 } 324 catch 325 { 326 connection2.Close(); 327 throw; 328 } 329 } 330 331 332 333 /// <summary> 334 /// 执行无参存储过程，并返回DataTable对象 335 /// </summary> 336 /// <param name="safeSql">存储过程名</param> 337 /// <returns>返回DataTable</returns> 338 public static DataTable ExecuteProcGetDataTable(string safeSql) 339 { 340 SqlConnection sqlConn = new SqlConnection(connectionString); 341 sqlConn.Open(); 342 DataSet ds = new DataSet(); 343 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 344 cmd.CommandType = CommandType.StoredProcedure; 345 SqlDataAdapter da = new SqlDataAdapter(cmd); 346 da.Fill(ds); 347 sqlConn.Close(); 348 return ds.Tables[0]; 349 350 } 351 352 /// <summary> 353 /// 执行SQL语句，并返回DataTable对象 354 /// </summary> 355 /// <param name="safeSql">SQL语句</param> 356 /// <returns>返回DataTable</returns> 357 public static DataTable ExecuteSqlGetDataTable(string safeSql) 358 { 359 SqlConnection sqlConn = new SqlConnection(connectionString); 360 sqlConn.Open(); 361 DataSet ds = new DataSet(); 362 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 363 cmd.CommandType = CommandType.Text; 364 SqlDataAdapter da = new SqlDataAdapter(cmd); 365 da.Fill(ds); 366 sqlConn.Close(); 367 return ds.Tables[0]; 368 369 } 370 371 372 /// <summary> 373 /// 执行无参存储过程，并返回DataTable对象 374 /// </summary> 375 /// <param name="safeSql">存储过程名</param> 376 /// <param name="values">参数值</param> 377 /// <returns>返回DataTable对象</returns> 378 public static DataTable ExecuteProcGetDataTable(string safeSql, params SqlParameter[] values) 379 { 380 SqlConnection sqlConn = new SqlConnection(connectionString); 381 sqlConn.Open(); 382 DataSet ds = new DataSet(); 383 SqlCommand cmd = new SqlCommand(safeSql, sqlConn); 384 cmd.CommandType = CommandType.StoredProcedure; 385 cmd.Parameters.AddRange(values); 386 SqlDataAdapter da = new SqlDataAdapter(cmd); 387 da.Fill(ds); 388 sqlConn.Close(); 389 return ds.Tables[0]; 390 391 } 392 } 393 394 //调用方法 395 public static int BidDoc_Add(BidDoc bidDoc) 396 { 397 procname = "dbo.BidDoc_Add"; 398 SqlParameter[] prams = { 399 new SqlParameter("@BidID",SqlDbType.Int), 400 new SqlParameter("@EmployeeID",SqlDbType.Int), 401 new SqlParameter("@EmployeeList",SqlDbType.VarChar,50), 402 new SqlParameter("@SendEmployeeID",SqlDbType.Int) 403 404 }; 405 406 prams[0].Value = bidDoc.BidID; 407 prams[1].Value = bidDoc.EmployeeID; 408 prams[2].Value = bidDoc.EmployeeList; 409 410 411 412 return DBHelper.ExecuteProcGetScalar(procname, prams); 413 }