用C#写一个SqlHelper类，方便每次复制一下。
/// <summary> /// 数据库连接类 /// </summary> public static class SqlHelper { /// <summary> /// 连接字符串 /// </summary> private static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["AccountBook_DBConnection"].ConnectionString; /// <summary> /// 无参同步执行语句，返回受影响的行数 /// </summary> /// <param name="Sql"></param> /// <returns></returns> public static int ExecuteQuery(string Sql) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(Sql, conn); try { conn.Open(); // 打开数据库 cmd.Transaction = conn.BeginTransaction(); // 开启事务 int result = cmd.ExecuteNonQuery(); cmd.Transaction.Commit(); // 提交事务 return result; } catch (Exception ex) { if (cmd.Transaction != null) { cmd.Transaction.Rollback(); // 回滚事务 } throw ex; } finally { cmd.Transaction.Dispose(); conn.Close(); // 关闭数据库 } } /// <summary> /// 带参同步执行语句，返回受影响的行数 /// </summary> /// <param name="Sql"></param> /// <param name="sqlParameters"></param> /// <returns></returns> public static int ExecuteQuery(string Sql, SqlParameter[] sqlParameters) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(Sql, conn); try { cmd.Parameters.AddRange(sqlParameters); // 添加参数 conn.Open(); // 打开数据库 cmd.Transaction = conn.BeginTransaction(); // 开启事务 int result = cmd.ExecuteNonQuery(); cmd.Transaction.Commit(); // 提交事务 return result; } catch (Exception ex) { if (cmd.Transaction != null) { cmd.Transaction.Rollback(); // 回滚事务 } throw ex; } finally { cmd.Transaction.Dispose(); conn.Close(); // 关闭数据库 } } /// <summary> /// 无参同步执行语句，返回DataTable /// </summary> /// <param name="Sql"></param> /// <returns></returns> public static DataTable Query(string Sql) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(Sql, conn); try { conn.Open(); // 打开数据库 cmd.Transaction = conn.BeginTransaction(); // 开启事务 SqlDataReader sdr = cmd.ExecuteReader(); DataTable result = ConvertDataReaderToDataTable(sdr); cmd.Transaction.Commit(); // 提交事务 return result; } catch (Exception ex) { if (cmd.Transaction != null) { cmd.Transaction.Rollback(); // 回滚事务 } throw ex; } finally { cmd.Transaction.Dispose(); conn.Close(); // 关闭数据库 } } /// <summary> /// 带参同步执行语句，返回DataTable /// </summary> /// <param name="Sql"></param> /// <param name="sqlParameters"></param> /// <returns></returns> public static DataTable Query(string Sql, SqlParameter[] sqlParameters) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(Sql, conn); try { cmd.Parameters.AddRange(sqlParameters); // 添加参数 conn.Open(); // 打开数据库 cmd.Transaction = conn.BeginTransaction(); // 开启事务 SqlDataReader sdr = cmd.ExecuteReader(); DataTable result = ConvertDataReaderToDataTable(sdr); cmd.Transaction.Commit(); // 提交事务 return result; } catch (Exception ex) { if (cmd.Transaction != null) { cmd.Transaction.Rollback(); // 回滚事务 } throw ex; } finally { cmd.Transaction.Dispose(); conn.Close(); // 关闭数据库 } } private static DataTable ConvertDataReaderToDataTable(SqlDataReader reader) { try { DataTable objDataTable = new DataTable(); int intFieldCount = reader.FieldCount; for (int intCounter = 0; intCounter < intFieldCount; ++intCounter) { objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter)); } objDataTable.BeginLoadData(); object[] objValues = new object[intFieldCount]; while (reader.Read()) { reader.GetValues(objValues); objDataTable.LoadDataRow(objValues, true); } reader.Close(); objDataTable.EndLoadData(); return objDataTable; } catch (Exception ex) { throw new Exception("转换出错!", ex); } } }