• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号
登陆 注册

C#全能数据库操作类及调用示例SQLSERVERC#数据库操作类(连接、执行SQL) ...

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
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();
            }
        }
    }
}
View Code

使用示例

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++)

鲜花
握手
雷人
路过
鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
More+
上一篇：
c#四舍五入、上取整、下取整发布时间：2022-07-10
下一篇：
C#中获取星期几发布时间：2022-07-10
热门推荐
More+
热门话题
More+
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服
地址：深圳市南山区西丽大学城创智工业园
电邮：jeky_zhao#qq.com
移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap