在vs里面连接数据库的方法有很多，这里是通用的方法和基本操作
SQL
1 ///2 /// 数据访问抽象基础类 3 /// Copyright (C) Maticsoft 4 /// 5 public abstract class DbHelperSQL 6 { 7 public static int DataType = 1; 8 //数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现. 9 public static string connectionString = ConfigurationManager.ConnectionStrings["ReadContext"].ConnectionString; 10 public DbHelperSQL() 11 { 12 if(DataType==2) 13 connectionString = ConfigurationManager.ConnectionStrings["ReadContextMySql"].ConnectionString; 14 } 15 16 #region 公用方法 17 ///18 /// 判断是否存在某表的某个字段 19 /// 20 /// 表名称 21 /// 列名称 22 ///是否存在 23 public static bool ColumnExists(string tableName, string columnName) 24 { 25 string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'"; 26 object res = GetSingle(sql); 27 if (res == null) 28 { 29 return false; 30 } 31 return Convert.ToInt32(res) > 0; 32 } 33 public static int GetMaxID(string FieldName, string TableName) 34 { 35 string strsql = "select max(" + FieldName + ")+1 from " + TableName; 36 object obj = GetSingle(strsql); 37 if (obj == null) 38 { 39 return 1; 40 } 41 else 42 { 43 return int.Parse(obj.ToString()); 44 } 45 } 46 public static bool Exists(string strSql) 47 { 48 object obj = GetSingle(strSql); 49 return ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) ? false : true; 50 } 51 ///52 /// 表是否存在 53 /// 54 /// 55 ///56 public static bool TabExists(string TableName) 57 { 58 string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; 59 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')"; 60 object obj = GetSingle(strsql); 61 int cmdresult; 62 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 63 { 64 cmdresult = 0; 65 } 66 else 67 { 68 cmdresult = int.Parse(obj.ToString()); 69 } 70 if (cmdresult == 0) 71 { 72 return false; 73 } 74 else 75 { 76 return true; 77 } 78 } 79 public static bool Exists(string strSql, params SqlParameter[] cmdParms) 80 { 81 object obj = GetSingle(strSql, cmdParms); 82 int cmdresult; 83 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 84 { 85 cmdresult = 0; 86 } 87 else 88 { 89 cmdresult = int.Parse(obj.ToString()); 90 } 91 if (cmdresult == 0) 92 { 93 return false; 94 } 95 else 96 { 97 return true; 98 } 99 }100 #endregion101 102 #region 执行简单SQL语句103 104 /// 105 /// 执行SQL语句，返回影响的记录数106 /// 107 /// SQL语句108 ///影响的记录数 109 public static int ExecuteSql(string SQLString)110 {111 using (SqlConnection connection = new SqlConnection(connectionString))112 {113 using (SqlCommand cmd = new SqlCommand(SQLString, connection))114 {115 try116 {117 connection.Open();118 int rows = cmd.ExecuteNonQuery();119 return rows;120 }121 catch (System.Data.SqlClient.SqlException e)122 {123 124 connection.Close();125 throw e;126 }127 }128 }129 }130 131 public static int ExecuteSqlByTime(string SQLString, int Times)132 {133 using (SqlConnection connection = new SqlConnection(connectionString))134 {135 using (SqlCommand cmd = new SqlCommand(SQLString, connection))136 {137 try138 {139 connection.Open();140 cmd.CommandTimeout = Times;141 int rows = cmd.ExecuteNonQuery();142 return rows;143 }144 catch (System.Data.SqlClient.SqlException e)145 {146 147 connection.Close();148 throw e;149 }150 }151 }152 }153 ///154 /// 执行多条SQL语句，实现数据库事务。155 /// 156 /// 多条SQL语句 157 public static int ExecuteSqlTran(ListSQLStringList)158 {159 using (SqlConnection conn = new SqlConnection(connectionString))160 {161 conn.Open();162 SqlCommand cmd = new SqlCommand();163 cmd.Connection = conn;164 SqlTransaction tx = conn.BeginTransaction();165 cmd.Transaction = tx;166 try167 {168 int count = 0;169 for (int n = 0; n < SQLStringList.Count; n++)170 {171 string strsql = SQLStringList[n];172 if (strsql.Trim().Length > 1)173 {174 cmd.CommandText = strsql;175 count += cmd.ExecuteNonQuery();176 }177 }178 tx.Commit();179 return count;180 }181 catch(Exception e)182 {183 tx.Rollback();184 return 0;185 }186 }187 }188 /// 189 /// 执行带一个存储过程参数的的SQL语句。190 /// 191 /// SQL语句192 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加193 ///影响的记录数 194 public static int ExecuteSql(string SQLString, string content)195 {196 using (SqlConnection connection = new SqlConnection(connectionString))197 {198 SqlCommand cmd = new SqlCommand(SQLString, connection);199 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);200 myParameter.Value = content;201 cmd.Parameters.Add(myParameter);202 try203 {204 connection.Open();205 int rows = cmd.ExecuteNonQuery();206 return rows;207 }208 catch (System.Data.SqlClient.SqlException e)209 {210 211 throw e;212 }213 finally214 {215 cmd.Dispose();216 connection.Close();217 }218 }219 }220 ///221 /// 执行带一个存储过程参数的的SQL语句。222 /// 223 /// SQL语句224 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加225 ///影响的记录数 226 public static object ExecuteSqlGet(string SQLString, string content)227 {228 using (SqlConnection connection = new SqlConnection(connectionString))229 {230 SqlCommand cmd = new SqlCommand(SQLString, connection);231 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);232 myParameter.Value = content;233 cmd.Parameters.Add(myParameter);234 try235 {236 connection.Open();237 object obj = cmd.ExecuteScalar();238 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))239 {240 return null;241 }242 else243 {244 return obj;245 }246 }247 catch (System.Data.SqlClient.SqlException e)248 {249 250 throw e;251 }252 finally253 {254 cmd.Dispose();255 connection.Close();256 }257 }258 }259 ///260 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)261 /// 262 /// SQL语句263 /// 图像字节,数据库的字段类型为image的情况264 ///影响的记录数 265 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)266 {267 using (SqlConnection connection = new SqlConnection(connectionString))268 {269 SqlCommand cmd = new SqlCommand(strSQL, connection);270 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);271 myParameter.Value = fs;272 cmd.Parameters.Add(myParameter);273 try274 {275 connection.Open();276 int rows = cmd.ExecuteNonQuery();277 return rows;278 }279 catch (System.Data.SqlClient.SqlException e)280 {281 282 throw e;283 }284 finally285 {286 cmd.Dispose();287 connection.Close();288 }289 }290 }291 292 ///293 /// 执行一条计算查询结果语句，返回查询结果（object）。294 /// 295 /// 计算查询结果语句296 ///查询结果（object） 297 public static object GetSingle(string SQLString)298 {299 using (SqlConnection connection = new SqlConnection(connectionString))300 {301 using (SqlCommand cmd = new SqlCommand(SQLString, connection))302 {303 try304 {305 connection.Open();306 object obj = cmd.ExecuteScalar();307 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))308 {309 return null;310 }311 else312 {313 return obj;314 }315 }316 catch (System.Data.SqlClient.SqlException e)317 {318 319 connection.Close();320 throw e;321 }322 }323 }324 }325 public static object GetSingle(string SQLString, int Times)326 {327 using (SqlConnection connection = new SqlConnection(connectionString))328 {329 using (SqlCommand cmd = new SqlCommand(SQLString, connection))330 {331 try332 {333 connection.Open();334 cmd.CommandTimeout = Times;335 object obj = cmd.ExecuteScalar();336 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))337 {338 return null;339 }340 else341 {342 return obj;343 }344 }345 catch (System.Data.SqlClient.SqlException e)346 {347 348 connection.Close();349 throw e;350 }351 }352 }353 }354 ///355 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )356 /// 357 /// 查询语句358 ///SqlDataReader 359 public static SqlDataReader ExecuteReader(string strSQL)360 {361 SqlConnection connection = new SqlConnection(connectionString);362 SqlCommand cmd = new SqlCommand(strSQL, connection);363 try364 {365 connection.Open();366 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);367 return myReader;368 }369 catch (System.Data.SqlClient.SqlException e)370 {371 372 throw e;373 } 374 375 }376 ///377 /// 执行查询语句，返回DataSet378 /// 379 /// 查询语句380 ///DataSet 381 public static DataSet Query(string SQLString)382 {383 using (SqlConnection connection = new SqlConnection(connectionString))384 {385 DataSet ds = new DataSet();386 try387 {388 connection.Open();389 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);390 command.Fill(ds, "ds");391 }392 catch (System.Data.SqlClient.SqlException e)393 {394 LogHelper.WriteErrorLog(String.Format(@"Query-失败：提交时间：{0},执行SQL语句：{1}", DateTime.Now, SQLString), e);395 throw e;396 }397 return ds;398 }399 }400 public static DataSet Query(string SQLString, int Times)401 {402 using (SqlConnection connection = new SqlConnection(connectionString))403 {404 DataSet ds = new DataSet();405 try406 {407 connection.Open();408 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);409 command.SelectCommand.CommandTimeout = Times;410 command.Fill(ds, "ds");411 }412 catch (System.Data.SqlClient.SqlException e)413 {414 LogHelper.WriteErrorLog(String.Format(@"Query-失败：提交时间：{0},执行SQL语句：{1}",DateTime.Now, SQLString), e);415 throw e;416 }417 return ds;418 }419 }420 421 422 423 #endregion424 425 #region 执行带参数的SQL语句426 427 ///428 /// 执行SQL语句，返回影响的记录数429 /// 430 /// SQL语句431 ///影响的记录数 432 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)433 {434 using (SqlConnection connection = new SqlConnection(connectionString))435 {436 using (SqlCommand cmd = new SqlCommand())437 {438 try439 {440 PrepareCommand(cmd, connection, null, SQLString, cmdParms);441 int rows = cmd.ExecuteNonQuery();442 cmd.Parameters.Clear();443 return rows;444 }445 catch (System.Data.SqlClient.SqlException e)446 {447 throw e;448 }449 }450 }451 }452 453 454 ///455 /// 执行多条SQL语句，实现数据库事务。456 /// 457 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）458 public static void ExecuteSqlTran(Hashtable SQLStringList)459 {460 using (SqlConnection conn = new SqlConnection(connectionString))461 {462 conn.Open();463 using (SqlTransaction trans = conn.BeginTransaction())464 {465 SqlCommand cmd = new SqlCommand();466 try467 {468 //循环469 foreach (DictionaryEntry myDE in SQLStringList)470 {471 string cmdText = myDE.Key.ToString();472 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;473 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);474 int val = cmd.ExecuteNonQuery();475 cmd.Parameters.Clear();476 }477 trans.Commit();478 }479 catch(Exception e)480 {481 482 trans.Rollback();483 throw;484 }485 }486 }487 }488 ///489 /// 执行多条SQL语句，实现数据库事务。490 /// 491 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）492 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)493 {494 using (SqlConnection conn = new SqlConnection(connectionString))495 {496 conn.Open();497 using (SqlTransaction trans = conn.BeginTransaction())498 {499 SqlCommand cmd = new SqlCommand();500 try501 {502 int indentity = 0;503 //循环504 foreach (DictionaryEntry myDE in SQLStringList)505 {506 string cmdText = myDE.Key.ToString();507 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;508 foreach (SqlParameter q in cmdParms)509 {510 if (q.Direction == ParameterDirection.InputOutput)511 {512 q.Value = indentity;513 }514 }515 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);516 int val = cmd.ExecuteNonQuery();517 foreach (SqlParameter q in cmdParms)518 {519 if (q.Direction == ParameterDirection.Output)520 {521 indentity = Convert.ToInt32(q.Value);522 }523 }524 cmd.Parameters.Clear();525 }526 trans.Commit();527 }528 catch(Exception e)529 {530 531 trans.Rollback();532 throw;533 }534 }535 }536 }537 ///538 /// 执行一条计算查询结果语句，返回查询结果（object）。539 /// 540 /// 计算查询结果语句541 ///查询结果（object） 542 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)543 {544 using (SqlConnection connection = new SqlConnection(connectionString))545 {546 using (SqlCommand cmd = new SqlCommand())547 {548 try549 {550 PrepareCommand(cmd, connection, null, SQLString, cmdParms);551 object obj = cmd.ExecuteScalar();552 cmd.Parameters.Clear();553 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))554 {555 return null;556 }557 else558 {559 return obj;560 }561 }562 catch (System.Data.SqlClient.SqlException e)563 {564 565 throw e;566 }567 }568 }569 }570 571 ///572 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )573 /// 574 /// 查询语句575 ///SqlDataReader 576 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)577 {578 SqlConnection connection = new SqlConnection(connectionString);579 SqlCommand cmd = new SqlCommand();580 try581 {582 PrepareCommand(cmd, connection, null, SQLString, cmdParms);583 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);584 cmd.Parameters.Clear();585 return myReader;586 }587 catch (System.Data.SqlClient.SqlException e)588 {589 590 throw e;591 }592 // finally593 // {594 // cmd.Dispose();595 // connection.Close();596 // } 597 598 }599 600 ///601 /// 执行查询语句，返回DataSet602 /// 603 /// 查询语句604 ///DataSet 605 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)606 {607 using (SqlConnection connection = new SqlConnection(connectionString))608 {609 SqlCommand cmd = new SqlCommand();610 PrepareCommand(cmd, connection, null, SQLString, cmdParms);611 using (SqlDataAdapter da = new SqlDataAdapter(cmd))612 {613 DataSet ds = new DataSet();614 try615 {616 da.Fill(ds, "ds");617 cmd.Parameters.Clear();618 }619 catch (System.Data.SqlClient.SqlException ex)620 {621 LogHelper.WriteErrorLog(String.Format(@"DbHelperSQL.Query（执行查询语句）-失败：提交时间：{0}", DateTime.Now), ex);622 }623 return ds;624 }625 }626 }627 628 629 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)630 {631 if (conn.State != ConnectionState.Open)632 conn.Open();633 cmd.Connection = conn;634 cmd.CommandText = cmdText;635 if (trans != null)636 cmd.Transaction = trans;637 cmd.CommandType = CommandType.Text;//cmdType;638 if (cmdParms != null)639 {640 641 642 foreach (SqlParameter parameter in cmdParms)643 {644 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&645 (parameter.Value == null))646 {647 parameter.Value = DBNull.Value;648 }649 cmd.Parameters.Add(parameter);650 }651 }652 }653 654 #endregion655 656 #region 存储过程操作657 658 ///659 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )660 /// 661 /// 存储过程名662 /// 存储过程参数663 ///SqlDataReader 664 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)665 {666 SqlConnection connection = new SqlConnection(connectionString);667 SqlDataReader returnReader;668 connection.Open();669 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);670 command.CommandType = CommandType.StoredProcedure;671 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);672 return returnReader;673 674 }675 676 677 ///678 /// 执行存储过程679 /// 680 /// 存储过程名681 /// 存储过程参数682 /// DataSet结果中的表名683 ///DataSet 684 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)685 {686 using (SqlConnection connection = new SqlConnection(connectionString))687 {688 DataSet dataSet = new DataSet();689 connection.Open();690 SqlDataAdapter sqlDA = new SqlDataAdapter();691 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);692 sqlDA.Fill(dataSet, tableName);693 connection.Close();694 return dataSet;695 }696 }697 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)698 {699 using (SqlConnection connection = new SqlConnection(connectionString))700 {701 DataSet dataSet = new DataSet();702 connection.Open();703 SqlDataAdapter sqlDA = new SqlDataAdapter();704 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);705 sqlDA.SelectCommand.CommandTimeout = Times;706 sqlDA.Fill(dataSet, tableName);707 connection.Close();708 return dataSet;709 }710 }711 712 713 ///714 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)715 /// 716 /// 数据库连接717 /// 存储过程名718 /// 存储过程参数719 ///SqlCommand 720 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)721 {722 SqlCommand command = new SqlCommand(storedProcName, connection);723 command.CommandType = CommandType.StoredProcedure;724 foreach (SqlParameter parameter in parameters)725 {726 if (parameter != null)727 {728 // 检查未分配值的输出参数,将其分配以DBNull.Value.729 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&730 (parameter.Value == null))731 {732 parameter.Value = DBNull.Value;733 }734 command.Parameters.Add(parameter);735 }736 }737 738 return command;739 }740 741 ///742 /// 执行存储过程，返回影响的行数 743 /// 744 /// 存储过程名745 /// 存储过程参数746 /// 影响的行数747 ///748 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)749 {750 using (SqlConnection connection = new SqlConnection(connectionString))751 {752 int result;753 connection.Open();754 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);755 rowsAffected = command.ExecuteNonQuery();756 result = (int)command.Parameters["ReturnValue"].Value;757 //Connection.Close();758 return result;759 }760 }761 762 /// 763 /// 创建 SqlCommand 对象实例(用来返回一个整数值) 764 /// 765 /// 存储过程名766 /// 存储过程参数767 ///SqlCommand 对象实例 768 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)769 {770 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);771 command.Parameters.Add(new SqlParameter("ReturnValue",772 SqlDbType.Int, 4, ParameterDirection.ReturnValue,773 false, 0, 0, string.Empty, DataRowVersion.Default, null));774 return command;775 }776 #endregion777 778 }779 780 781
MySql
public class DataHelper { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = ConfigurationManager.ConnectionStrings["ReadContextMysql"].ConnectionString; //public DbHelperMySQL() //{ //} #region 公用方法 /// /// 得到最大值 /// /// /// /// /// 是否存在 /// /// /// /// 是否存在（基于MySqlParameter） /// /// /// /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } public static int ExecuteSqlByTime(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// /// 执行MySql和Oracle滴混合事务 /// /// SQL命令行列表 /// Oracle命令行列表 /// 执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功 //public static int ExecuteSqlTran(List list, List oracleCmdSqlList) //{ // using (MySqlConnection conn = new MySqlConnection(connectionString)) // { // conn.Open(); // MySqlCommand cmd = new MySqlCommand(); // cmd.Connection = conn; // MySqlTransaction tx = conn.BeginTransaction(); // cmd.Transaction = tx; // try // { // foreach (CommandInfo myDE in list) // { // string cmdText = myDE.CommandText; // MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters; // PrepareCommand(cmd, conn, tx, cmdText, cmdParms); // if (myDE.EffentNextType == EffentNextType.SolicitationEvent) // { // if (myDE.CommandText.ToLower().IndexOf("count(") == -1) // { // tx.Rollback(); // throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式"); // //return 0; // } // object obj = cmd.ExecuteScalar(); // bool isHave = false; // if (obj == null && obj == DBNull.Value) // { // isHave = false; // } // isHave = Convert.ToInt32(obj) > 0; // if (isHave) // { // //引发事件 // myDE.OnSolicitationEvent(); // } // } // if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) // { // if (myDE.CommandText.ToLower().IndexOf("count(") == -1) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); // //return 0; // } // object obj = cmd.ExecuteScalar(); // bool isHave = false; // if (obj == null && obj == DBNull.Value) // { // isHave = false; // } // isHave = Convert.ToInt32(obj) > 0; // if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); // //return 0; // } // if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); // //return 0; // } // continue; // } // int val = cmd.ExecuteNonQuery(); // if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); // //return 0; // } // cmd.Parameters.Clear(); // } // string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); // bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); // if (!res) // { // tx.Rollback(); // throw new Exception("执行失败"); // // return -1; // } // tx.Commit(); // return 1; // } // catch (MySql.Data.MySqlClient.MySqlException e) // { // tx.Rollback(); // throw e; // } // catch (Exception e) // { // tx.Rollback(); // throw e; // } // } //} /// /// 执行多条SQL语句，实现数据库事务。 /// /// 多条SQL语句 public static int ExecuteSqlTran(List SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; MySqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 public static int ExecuteSql(string SQLString, string content) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(SQLString, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 public static object ExecuteSqlGet(string SQLString, string content) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(SQLString, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// /// SQL语句 /// 图像字节,数据库的字段类型为image的情况 /// 影响的记录数 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(strSQL, connection); MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public static object GetSingle(string SQLString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } public static object GetSingle(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) /// /// 查询语句 /// MySqlDataReader public static MySqlDataReader ExecuteReader(string strSQL) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand cmd = new MySqlCommand(strSQL, connection); try { connection.Open(); MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet public static DataSet Query(string SQLString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); try { using (MySqlCommand cmd=new MySqlCommand(SQLString,connection)) { using (MySqlDataAdapter command = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); command.Fill(ds); return ds; } } } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } } } public static DataSet Query(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 执行带参数的SQL语句 /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]） public static void ExecuteSqlTran(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]） //public static int ExecuteSqlTran(System.Collections.Generic.List cmdList) //{ // using (MySqlConnection conn = new MySqlConnection(connectionString)) // { // conn.Open(); // using (MySqlTransaction trans = conn.BeginTransaction()) // { // MySqlCommand cmd = new MySqlCommand(); // try // { int count = 0; // //循环 // foreach (CommandInfo myDE in cmdList) // { // string cmdText = myDE.CommandText; // MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters; // PrepareCommand(cmd, conn, trans, cmdText, cmdParms); // if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) // { // if (myDE.CommandText.ToLower().IndexOf("count(") == -1) // { // trans.Rollback(); // return 0; // } // object obj = cmd.ExecuteScalar(); // bool isHave = false; // if (obj == null && obj == DBNull.Value) // { // isHave = false; // } // isHave = Convert.ToInt32(obj) > 0; // if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) // { // trans.Rollback(); // return 0; // } // if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) // { // trans.Rollback(); // return 0; // } // continue; // } // int val = cmd.ExecuteNonQuery(); // count += val; // if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) // { // trans.Rollback(); // return 0; // } // cmd.Parameters.Clear(); // } // trans.Commit(); // return count; // } // catch // { // trans.Rollback(); // throw; // } // } // } //} /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]） //public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List SQLStringList) //{ // using (MySqlConnection conn = new MySqlConnection(connectionString)) // { // conn.Open(); // using (MySqlTransaction trans = conn.BeginTransaction()) // { // MySqlCommand cmd = new MySqlCommand(); // try // { // int indentity = 0; // //循环 // foreach (CommandInfo myDE in SQLStringList) // { // string cmdText = myDE.CommandText; // MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters; // foreach (MySqlParameter q in cmdParms) // { // if (q.Direction == ParameterDirection.InputOutput) // { // q.Value = indentity; // } // } // PrepareCommand(cmd, conn, trans, cmdText, cmdParms); // int val = cmd.ExecuteNonQuery(); // foreach (MySqlParameter q in cmdParms) // { // if (q.Direction == ParameterDirection.Output) // { // indentity = Convert.ToInt32(q.Value); // } // } // cmd.Parameters.Clear(); // } // trans.Commit(); // } // catch // { // trans.Rollback(); // throw; // } // } // } //} /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]） public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } } } /// /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) /// /// 查询语句 /// MySqlDataReader public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand cmd = new MySqlCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } // finally // { // cmd.Dispose(); // connection.Close(); // } } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds; } } } private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (MySqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion }
web.config
其实上面那句是些着玩的小面这句就够了
PS：小菜一只请多多指教，可能有错请指出