通过数据库访问类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

public class DbManager
    {
        public  DbProviderFactory Factory;
        private DbConnection _conn;
        private DbCommand _cmd;
        private DbDataAdapter _dap;

        private String _server = "";
        private String _db = "";
        private String _user = "";
        private String _pw = "";
        private String _type = "";
        private Dictionary<string,string> _dbTypeDic;
        private Dictionary<string, string> _diverDic;

        public static DbManager GetInstance(String sever, String database, String u, String psw, String dbType)
        {
            try
            {
                DbManager dbm = new DbManager();

                dbm._dbTypeDic = new Dictionary<string, string>();
                dbm._dbTypeDic.Add("SqlServer", "System.Data.SqlClient");
                dbm._dbTypeDic.Add("Oracle", "System.Data.OracleClient");

                dbm._diverDic = new Dictionary<string, string>();
                dbm._diverDic.Add("SqlServer", "SQL Server");
                dbm._diverDic.Add("Oracle", "Microsoft ODBC for Oracle");
               

                dbm._server = sever;
                dbm._db = database;
                dbm._user = u;
                dbm._pw = psw;
                dbm._type = dbType;
                dbm.Factory = dbm.GetFac(dbm._dbTypeDic[dbm._type]);

                StringBuilder sb = new StringBuilder("Server=");
                sb.Append(dbm._server);
                sb.Append(";initial catalog=");
                sb.Append(dbm._db);
                sb.Append(";UID=");
                sb.Append(dbm._user);
                sb.Append(";PWD=");
                sb.Append(dbm._pw);
                sb.Append(";Connection Timeout=60");

                dbm._conn = dbm.Factory.CreateConnection();
                if (dbm._conn != null) 
                    dbm._conn.ConnectionString = sb.ToString();

                
                dbm._cmd = dbm.Factory.CreateCommand(); 

                dbm._dap = dbm.Factory.CreateDataAdapter();
                if (dbm._dap != null)
                {
                    dbm._dap.SelectCommand = dbm._cmd;
                    if (dbm._dap.SelectCommand != null) 
                        dbm._dap.SelectCommand.Connection = dbm._conn;
                }


                if (dbm._conn != null) 
                    dbm._conn.Open();
                return dbm;
            }
            catch (Exception e)
            {return null;
            }
            
        }
        public DbProviderFactory GetFac(string providername)
        {
            try
            {
                DbProviderFactory fact = DbProviderFactories.GetFactory(providername);

                return fact;
            }
            catch (Exception e)
            {
return null;
            }
            
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (_conn != null)
                try
                {
                    _conn.Close();
                }
                catch (Exception ex)
                {

                }
        }

        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(String sql, List<DbParamter> ps)
        {
            DataTable dt = null;
            if (_conn != null && _conn.State == ConnectionState.Closed)
            {
                try
                {
                    _conn.Open();
                    _cmd.CommandTimeout = 120;
                }
                catch { }
            }
            if (_conn != null && _conn.State == ConnectionState.Open)
            {
                try
                {
                    DataSet ds = new DataSet();
                    _cmd.CommandText = sql;
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.Clear();
                    if (ps != null)
                    {
                        
                        foreach (var p in ps)
                        {
                            var param = _cmd.CreateParameter();
                            param.DbType = p.Type;
                            param.ParameterName = p.Name;
                            param.Value = p.Value;
                            _cmd.Parameters.Add(param);
                        }
                        
                    }
                    _cmd.Prepare();
                    _dap.Fill(ds, "table");
                    dt = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    _conn.Close();
                }
            }
            return dt;
        }
        public DataTable ExecuteQuerySql(String sql)
        {
            DataTable dt = null;
            if (_conn != null && _conn.State == ConnectionState.Closed)
            {
                try
                {
                    _conn.Open();
                    _cmd.CommandTimeout = 120;
                }
                catch { }
            }
            if (_conn != null && _conn.State == ConnectionState.Open)
            {
                try
                {
                    //cmd = conn.CreateCommand();
                    //cmd.Connection = conn;
                    DataSet ds = new DataSet();
                    _cmd.CommandText = sql;
                    _cmd.CommandType = CommandType.Text;
                    //cmd.ExecuteNonQuery();
                    _dap.Fill(ds, "table");
                    dt = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    _conn.Close();
                }
            }
            return dt;
           
        }
        /// <summary>
        /// 更新数据库
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExecuteUpdate(String sql, List<DbParamter> ps)
        {
            if (_conn != null && _conn.State == ConnectionState.Closed)
            {
                try
                {
                    _conn.Open();
                    _cmd.CommandTimeout = 120;
                }
                catch (Exception)
                { }
            }
            if (_conn != null && _conn.State == ConnectionState.Open)
            {
                try
                {
                    _cmd.CommandText = sql;
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.Clear();
                    if (ps != null)
                    {

                        foreach (var p in ps)
                        {
                            var param = _cmd.CreateParameter();
                            param.DbType = p.Type;
                            param.ParameterName = p.Name;
                            param.Value = p.Value;
                            _cmd.Parameters.Add(param);
                        }

                    }
                    _cmd.Prepare();

                    _cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    _conn.Close();
                }
            }
            return false;
        }
    }
    public class DbParamter
    {
        public string Name { get; set; }

        public DbType Type { get; set; }

        public object Value { get; set; }

        public DbParamter(string n,DbType t,object v)
        {
            Name = n;
            Type = t;
            Value = v;
        }

    }

只测试过Sqlserver数据库连接，别的数据库没试过，不确定数据库连接字符串是不是得改

转载于:https://www.cnblogs.com/nygfcn1234/p/3216998.html