C#全能数据库操作类及调用示例
using System; using System.Data; using System.Data.Common; using System.Configuration; namespace MSCL { /// /// DbHelper通用数据库类 /// public class DbHelper { /// /// /// private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"]; /// /// /// private static string dbConnectionString = ConfigurationManager.AppSettings["ConnectionString"]; private DbConnection connection; public DbHelper() { this.connection = CreateConnection(DbHelper.dbConnectionString); } public DbHelper(string connectionString) { this.connection = CreateConnection(connectionString); } public static DbConnection CreateConnection() { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = DbHelper.dbConnectionString; return dbconn; } public static DbConnection CreateConnection(string connectionString) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = connectionString; return dbconn; } /// /// 执行存储过程 /// ///存储过程名 /// public DbCommand GetStoredProcCommand(string storedProcedure) { DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = storedProcedure; dbCommand.CommandType = CommandType.StoredProcedure; return dbCommand; } /// /// 执行SQL语句 /// ///SQL语句 /// public DbCommand GetSqlStringCommand(string sqlQuery) { DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = sqlQuery; dbCommand.CommandType = CommandType.Text; return dbCommand; } #region 增加参数 public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection) { foreach (DbParameter dbParameter in dbParameterCollection) { cmd.Parameters.Add(dbParameter); } } /// /// 增加输出参数 /// /// /// /// /// public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Size = size; dbParameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(dbParameter); } /// /// 增加输入参数 /// /// /// /// /// public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Value = value; dbParameter.Direction = ParameterDirection.Input; cmd.Parameters.Add(dbParameter); } /// /// 增加返回参数 /// /// /// /// public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(dbParameter); } public DbParameter GetParameter(DbCommand cmd, string parameterName) { return cmd.Parameters[parameterName]; } #endregion #region 执行 /// /// 执行查询返回DataSet /// /// /// public DataSet ExecuteDataSet(DbCommand cmd) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; } /// /// 执行查询返回DataTable /// /// /// public DataTable ExecuteDataTable(DbCommand cmd) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataTable dataTable = new DataTable(); dbDataAdapter.Fill(dataTable); return dataTable; } /// /// 执行查询返回DataReader /// /// /// public DbDataReader ExecuteReader(DbCommand cmd) { cmd.Connection.Open(); DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } /// /// 执行SQL语句，返回影响行数 /// /// /// public int ExecuteNonQuery(DbCommand cmd) { cmd.Connection.Open(); int ret = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return ret; } /// /// 返回首行首列对象 /// /// /// public object ExecuteScalar(DbCommand cmd) { cmd.Connection.Open(); object ret = cmd.ExecuteScalar(); cmd.Connection.Close(); return ret; } #endregion #region 执行事务 /// /// 执行事务返回DataSet /// /// /// /// public DataSet ExecuteDataSet(DbCommand cmd, Trans t) { cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; } /// /// 执行事务返回DataTable /// /// /// /// public DataTable ExecuteDataTable(DbCommand cmd, Trans t) { cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataTable dataTable = new DataTable(); dbDataAdapter.Fill(dataTable); return dataTable; } /// /// 执行事务返回DataReader /// /// /// /// public DbDataReader ExecuteReader(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbDataReader reader = cmd.ExecuteReader(); return reader; } /// /// 执行事务SQL语句返回影响行数 /// /// /// /// public int ExecuteNonQuery(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; int ret = cmd.ExecuteNonQuery(); return ret; } /// /// 执行事务SQL语句返回首行首列 /// /// /// /// public object ExecuteScalar(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; object ret = cmd.ExecuteScalar(); return ret; } #endregion } public class Trans : IDisposable { private DbConnection conn; private DbTransaction dbTrans; public DbConnection DbConnection { get { return this.conn; } } public DbTransaction DbTrans { get { return this.dbTrans; } } public Trans() { conn = DbHelper.CreateConnection(); conn.Open(); dbTrans = conn.BeginTransaction(); } public Trans(string connectionString) { conn = DbHelper.CreateConnection(connectionString); conn.Open(); dbTrans = conn.BeginTransaction(); } public void Commit() { dbTrans.Commit(); this.Colse(); } public void RollBack() { dbTrans.Rollback(); this.Colse(); } public void Dispose() { this.Colse(); } public void Colse() { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } } using System; using System.Data; using System.Data.Common; using System.Configuration; namespace MSCL { /// /// DbHelper通用数据库类 /// public class DbHelper { /// /// /// private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"]; /// /// /// private static string dbConnectionString = ConfigurationManager.AppSettings["ConnectionString"]; private DbConnection connection; public DbHelper() { this.connection = CreateConnection(DbHelper.dbConnectionString); } public DbHelper(string connectionString) { this.connection = CreateConnection(connectionString); } public static DbConnection CreateConnection() { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = DbHelper.dbConnectionString; return dbconn; } public static DbConnection CreateConnection(string connectionString) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = connectionString; return dbconn; } /// /// 执行存储过程 /// ///存储过程名 /// public DbCommand GetStoredProcCommand(string storedProcedure) { DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = storedProcedure; dbCommand.CommandType = CommandType.StoredProcedure; return dbCommand; } /// /// 执行SQL语句 /// ///SQL语句 /// public DbCommand GetSqlStringCommand(string sqlQuery) { DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = sqlQuery; dbCommand.CommandType = CommandType.Text; return dbCommand; } #region 增加参数 public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection) { foreach (DbParameter dbParameter in dbParameterCollection) { cmd.Parameters.Add(dbParameter); } } /// /// 增加输出参数 /// /// /// /// /// public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Size = size; dbParameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(dbParameter); } /// /// 增加输入参数 /// /// /// /// /// public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Value = value; dbParameter.Direction = ParameterDirection.Input; cmd.Parameters.Add(dbParameter); } /// /// 增加返回参数 /// /// /// /// public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType) { DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(dbParameter); } public DbParameter GetParameter(DbCommand cmd, string parameterName) { return cmd.Parameters[parameterName]; } #endregion #region 执行 /// /// 执行查询返回DataSet /// /// /// public DataSet ExecuteDataSet(DbCommand cmd) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; } /// /// 执行查询返回DataTable /// /// /// public DataTable ExecuteDataTable(DbCommand cmd) { DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataTable dataTable = new DataTable(); dbDataAdapter.Fill(dataTable); return dataTable; } /// /// 执行查询返回DataReader /// /// /// public DbDataReader ExecuteReader(DbCommand cmd) { cmd.Connection.Open(); DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } /// /// 执行SQL语句，返回影响行数 /// /// /// public int ExecuteNonQuery(DbCommand cmd) { cmd.Connection.Open(); int ret = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return ret; } /// /// 返回首行首列对象 /// /// /// public object ExecuteScalar(DbCommand cmd) { cmd.Connection.Open(); object ret = cmd.ExecuteScalar(); cmd.Connection.Close(); return ret; } #endregion #region 执行事务 /// /// 执行事务返回DataSet /// /// /// /// public DataSet ExecuteDataSet(DbCommand cmd, Trans t) { cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; } /// /// 执行事务返回DataTable /// /// /// /// public DataTable ExecuteDataTable(DbCommand cmd, Trans t) { cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataTable dataTable = new DataTable(); dbDataAdapter.Fill(dataTable); return dataTable; } /// /// 执行事务返回DataReader /// /// /// /// public DbDataReader ExecuteReader(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; DbDataReader reader = cmd.ExecuteReader(); return reader; } /// /// 执行事务SQL语句返回影响行数 /// /// /// /// public int ExecuteNonQuery(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; int ret = cmd.ExecuteNonQuery(); return ret; } /// /// 执行事务SQL语句返回首行首列 /// /// /// /// public object ExecuteScalar(DbCommand cmd, Trans t) { cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; object ret = cmd.ExecuteScalar(); return ret; } #endregion } public class Trans : IDisposable { private DbConnection conn; private DbTransaction dbTrans; public DbConnection DbConnection { get { return this.conn; } } public DbTransaction DbTrans { get { return this.dbTrans; } } public Trans() { conn = DbHelper.CreateConnection(); conn.Open(); dbTrans = conn.BeginTransaction(); } public Trans(string connectionString) { conn = DbHelper.CreateConnection(connectionString); conn.Open(); dbTrans = conn.BeginTransaction(); } public void Commit() { dbTrans.Commit(); this.Colse(); } public void RollBack() { dbTrans.Rollback(); this.Colse(); } public void Dispose() { this.Colse(); } public void Colse() { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } }
使用示例
using System; using System.Collections.Generic; using System.Text; namespace MSCL { #region 使用示例 /* List ftvlist = new List(); ftvlist.Add(new FieldTypeValue("ErrorDetail", "这是个错误")); ftvlist.Add(new FieldTypeValue("ErrorFlag", "1")); ftvlist.Add(new FieldTypeValue("ErrorRemark","这是个错误，我还没有处理")); ftvlist.Add(new FieldTypeValue("ErrorTime", DateTime.Now.ToString())); ftvlist.Add(new FieldTypeValue("OprationTime", DateTime.Now.ToString())); //新增 string sql = MSCL.BuilderSql.createInsertSql("AFM_SysLog", ftvlist); MSCL.SqlHelper.ExecSql(sql); //修改 string sql = MSCL.BuilderSql.createUpdateSql("AFM_SysLog", ftvlist, "ErrorID", "166"); MSCL.SqlHelper.ExecSql(sql); //删除 string sql = MSCL.BuilderSql.createDeleteSql("AFM_SysLog", "ErrorID", "166"); MSCL.SqlHelper.ExecSql(sql); */ #endregion #region 数据表字段类 /// /// 数据表字段类 /// public class FieldTypeValue { /// /// 字段容器 /// ///字段名 ///字段值 ///是否数字字段 public FieldTypeValue(string fieldName, string fieldValue, bool isNum) { this.fieldName = fieldName; this.fieldValue = fieldValue; this.isNum = isNum; } /// /// 字段容器 /// ///字段名 ///字段值 public FieldTypeValue(string fieldName, string fieldValue) { this.fieldName = fieldName; this.fieldValue = fieldValue; } private string fieldName; /// /// 字段名 /// public string FieldName { get { return fieldName; } set { fieldName = value; } } private bool isNum = false; /// /// 是否数字 /// public bool IsNum { get { return isNum; } set { isNum = value; } } private string fieldValue; /// /// 字段值 /// public string FieldValue { get { return fieldValue; } set { fieldValue = value; } } } #endregion #region SQL语句的构造类 /// /// SQL语句的构造类 /// public class BuilderSql { /// /// 构造新增Insert语句 /// ///表名 ///字段list /// public static string createInsertSql(string tableName, List ftvlist) { StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(tableName); sb.Append("("); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (i != ftvlist.Count - 1) { sb.Append(ftv.FieldName + ","); } else { sb.Append(ftv.FieldName); } } sb.Append(") values("); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (ftv.IsNum) { if (i != ftvlist.Count - 1) { sb.Append(ftv.FieldValue + ","); } else { sb.Append(ftv.FieldValue); } } else { if (i != ftvlist.Count - 1) { sb.Append("'" + ftv.FieldValue + "',"); } else { sb.Append("'" + ftv.FieldValue + "'"); } } } sb.Append(")"); return sb.ToString(); } /// /// 构造更新Update语句 /// ///表名 ///字段list ///主键名 ///主键值 /// public static string createUpdateSql(string tableName, List ftvlist, string pkName, string pkValue) { StringBuilder sb = new StringBuilder(); sb.Append(" update "); sb.Append(tableName); sb.Append(" set"); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (i != ftvlist.Count - 1) { if (ftv.IsNum) { sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ","); } else { sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',"); } } else { if (ftv.IsNum) { sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ""); } else { sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'"); } } } sb.Append(" where " + pkName + "=" + pkValue); return sb.ToString(); } /// /// 构造删除Delete语句 /// ///表名 ///主键名 ///主键值 /// public static string createDeleteSql(string tableName, string pkName, string pkValue) { StringBuilder sb = new StringBuilder(); sb.Append(" delete from "); sb.Append(tableName); sb.Append(" where " + pkName + " = '" + pkValue + "'"); return sb.ToString(); } } #endregion } using System; using System.Collections.Generic; using System.Text; namespace MSCL { #region 使用示例 /* List ftvlist = new List(); ftvlist.Add(new FieldTypeValue("ErrorDetail", "这是个错误")); ftvlist.Add(new FieldTypeValue("ErrorFlag", "1")); ftvlist.Add(new FieldTypeValue("ErrorRemark","这是个错误，我还没有处理")); ftvlist.Add(new FieldTypeValue("ErrorTime", DateTime.Now.ToString())); ftvlist.Add(new FieldTypeValue("OprationTime", DateTime.Now.ToString())); //新增 string sql = MSCL.BuilderSql.createInsertSql("AFM_SysLog", ftvlist); MSCL.SqlHelper.ExecSql(sql); //修改 string sql = MSCL.BuilderSql.createUpdateSql("AFM_SysLog", ftvlist, "ErrorID", "166"); MSCL.SqlHelper.ExecSql(sql); //删除 string sql = MSCL.BuilderSql.createDeleteSql("AFM_SysLog", "ErrorID", "166"); MSCL.SqlHelper.ExecSql(sql); */ #endregion #region 数据表字段类 /// /// 数据表字段类 /// public class FieldTypeValue { /// /// 字段容器 /// ///字段名 ///字段值 ///是否数字字段 public FieldTypeValue(string fieldName, string fieldValue, bool isNum) { this.fieldName = fieldName; this.fieldValue = fieldValue; this.isNum = isNum; } /// /// 字段容器 /// ///字段名 ///字段值 public FieldTypeValue(string fieldName, string fieldValue) { this.fieldName = fieldName; this.fieldValue = fieldValue; } private string fieldName; /// /// 字段名 /// public string FieldName { get { return fieldName; } set { fieldName = value; } } private bool isNum = false; /// /// 是否数字 /// public bool IsNum { get { return isNum; } set { isNum = value; } } private string fieldValue; /// /// 字段值 /// public string FieldValue { get { return fieldValue; } set { fieldValue = value; } } } #endregion #region SQL语句的构造类 /// /// SQL语句的构造类 /// public class BuilderSql { /// /// 构造新增Insert语句 /// ///表名 ///字段list /// public static string createInsertSql(string tableName, List ftvlist) { StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(tableName); sb.Append("("); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (i != ftvlist.Count - 1) { sb.Append(ftv.FieldName + ","); } else { sb.Append(ftv.FieldName); } } sb.Append(") values("); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (ftv.IsNum) { if (i != ftvlist.Count - 1) { sb.Append(ftv.FieldValue + ","); } else { sb.Append(ftv.FieldValue); } } else { if (i != ftvlist.Count - 1) { sb.Append("'" + ftv.FieldValue + "',"); } else { sb.Append("'" + ftv.FieldValue + "'"); } } } sb.Append(")"); return sb.ToString(); } /// /// 构造更新Update语句 /// ///表名 ///字段list ///主键名 ///主键值 /// public static string createUpdateSql(string tableName, List ftvlist, string pkName, string pkValue) { StringBuilder sb = new StringBuilder(); sb.Append(" update "); sb.Append(tableName); sb.Append(" set"); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (i != ftvlist.Count - 1) { if (ftv.IsNum) { sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ","); } else { sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',"); } } else { if (ftv.IsNum) { sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ""); } else { sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'"); } } } sb.Append(" where " + pkName + "=" + pkValue); return sb.ToString(); } /// /// 构造删除Delete语句 /// ///表名 ///主键名 ///主键值 /// public static string createDeleteSql(string tableName, string pkName, string pkValue) { StringBuilder sb = new StringBuilder(); sb.Append(" delete from "); sb.Append(tableName); sb.Append(" where " + pkName + " = '" + pkValue + "'"); return sb.ToString(); } } #endregion } [csharp] using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Text; using System.Data.SqlClient; using MSCL; public partial class SQLDemo : System.Web.UI.Page { DbHelper db = new DbHelper(); protected void Page_Load(object sender, EventArgs e) { } //新增数据 protected void Button1_Click(object sender, EventArgs e) { List ftvlist = new List(); ftvlist.Add(new FieldTypeValue("[D_Name]", "测试用户" + DateTime.Now.ToString("yyyyMMddhhmmss"))); ftvlist.Add(new FieldTypeValue("[D_Password]", "测试密码" + DateTime.Now.ToString("yyyyMMddhhmmss"))); ftvlist.Add(new FieldTypeValue("[D_Else]", "测试备注" + DateTime.Now.ToString("yyyyMMddhhmmss"))); string sql = BuilderSql.createInsertSql("TestTable", ftvlist); int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql)); if (opFlag > 0) { JsHelper.Alert("新增成功!", Page); } else { JsHelper.Alert("新增失败!", Page); }; } //修改数据 protected void Button2_Click(object sender, EventArgs e) { List ftvlist = new List(); ftvlist.Add(new FieldTypeValue("[D_Name]", "这是个错误dsadsadasd")); ftvlist.Add(new FieldTypeValue("[D_Password]", "aaa这是个错误，我还没有处理")); ftvlist.Add(new FieldTypeValue("[D_Else]", "aaa这是个错误，我还没有处理")); string sql = BuilderSql.createUpdateSql("TestTable", ftvlist, "D_ID", "1"); int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql)); if (opFlag > 0) { JsHelper.Alert("修改成功!", Page); } else { JsHelper.Alert("修改失败!", Page); }; } //删除数据 protected void Button3_Click(object sender, EventArgs e) { string sql = BuilderSql.createDeleteSql("[TestTable]", "[D_Id]", "1"); int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql)); if (opFlag > 0) { JsHelper.Alert("删除成功!", Page); } else { JsHelper.Alert("删除失败!", Page); }; } //事务提交 protected void Button4_Click(object sender, EventArgs e) { using (Trans t = new Trans()) { try { db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa1','bbbbbb1','cccccc1')"), t); db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa2','bbbbbb2','cccccc2')"), t); db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa3','bbbbbb3','cccccc3')"), t); db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa4','bbbbbb4','cccccc4')"), t); db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa5','bbbbbb5','cccccc5')"), t); t.Commit(); JsHelper.Alert("事务提交成功!", Page); } catch { t.RollBack(); JsHelper.Alert("事务提交失败!", Page); } } } }
SQL SERVER C#数据库操作类(连接、执行SQL)
using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace LiTianPing.SQLServerDAL //可以修改成实际项目的命名空间名称 { /// <summary> /// Copyright (C) 2004-2008 LiTianPing /// 数据访问基础类(基于SQLServer) /// 用户可以修改满足自己项目的需要。 /// </summary> public abstract class DbHelperSQL { //数据库连接字符串(web.config来配置) //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" /> protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"]; public DbHelperSQL() { } #region 公用方法 public static int GetMaxID(string FieldName,string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql, params SqlParameter[] cmdParms) { object obj = GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString,connection)) { try { connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.SqlClient.SqlException E) { connection.Close(); throw new Exception(E.Message); } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection=conn; SqlTransaction tx=conn.BeginTransaction(); cmd.Transaction=tx; try { for(int n=0;n<SQLStringList.Count;n++) { string strsql=SQLStringList[n].ToString(); if (strsql.Trim().Length>1) { cmd.CommandText=strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch(System.Data.SqlClient.SqlException E) { tx.Rollback(); throw new Exception(E.Message); } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString,string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString,connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText); myParameter.Value = content ; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL,byte[] fs) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(strSQL,connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image); myParameter.Value = fs ; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using(SqlCommand cmd = new SqlCommand(SQLString,connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))) { return null; } else { return obj; } } catch(System.Data.SqlClient.SqlException e) { connection.Close(); throw new Exception(e.Message); } } } } /// <summary> /// 执行查询语句，返回SqlDataReader /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string strSQL) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL,connection); try { connection.Open(); SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch(System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString,connection); command.Fill(ds,"ds"); } catch(System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 执行带参数的SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString,params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null,SQLString, cmdParms); int rows=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch(System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText=myDE.Key.ToString(); SqlParameter[] cmdParms=(SqlParameter[])myDE.Value; PrepareCommand(cmd,conn,trans,cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); trans.Commit(); } } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString,params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { 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(System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } } } } /// <summary> /// 执行查询语句，返回SqlDataReader /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string SQLString,params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, null,SQLString, cmdParms); SqlDataReader myReader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return myReader; } catch(System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString,params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null,SQLString, cmdParms); using( SqlDataAdapter da = new SqlDataAdapter(cmd) ) { DataSet ds = new DataSet(); try { da.Fill(ds,"ds"); cmd.Parameters.Clear(); } catch(System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } } private static void PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans, string cmdText, SqlParameter[] 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 (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } #endregion #region 存储过程操作 /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters ) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters ); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(); return returnReader; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName ) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); sqlDA.Fill( dataSet, tableName ); connection.Close(); return dataSet; } } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand( storedProcName, connection ); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { command.Parameters.Add( parameter ); } return command; } /// <summary> /// 执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected ) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection,storedProcName, parameters ); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// <summary> /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand 对象实例</returns> private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters ); command.Parameters.Add( new SqlParameter ( "ReturnValue", SqlDbType.Int,4,ParameterDirection.ReturnValue, false,0,0,string.Empty,DataRowVersion.Default,null )); return command; } #endregion } }