ASP.NET数据库访问类

最新推荐文章于 2023-07-08 16:30:44 发布
最新推荐文章于 2023-07-08 16:30:44 发布
阅读量529 收藏
点赞数
分类专栏： .Net 文章标签： 数据库 asp.net dataset string exception cmd
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhaoyabin/article/details/5140082
版权

比较好的数据库访问类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
namespace PublicClass
{
    /// <summary>    ///
    /// Copyright(c) 2008~2011 KingBoy Software Co.,Ltd
    /// FileName:数据库访问抽象类
    /// Author:KingBoy  Version:1.0.0.0  Date:2008-10-05
    /// Description:提供不同数据库操作的标准类。
    /// </summary>
    public abstract class DB
    {
       
      #region var
         private string strService="";  // 定义服务器名称
         private string strDataBase=""; //定义数据库名称
         private string strUser="";  //定义登陆用户名
         private string strPassword  = ""; //定义登陆用户密码
         private int intType = 0 ;  //定义连接类型      
       #endregion
      #region property
        /// <summary>
        /// 连接服务器名称
        /// </summary>
        public string serviceName
        {
          get{return strService;}
          set{strService =value;}
        }
        /// <summary>
        /// 连接数据库名称
        /// </summary>
        public string databaseName
        {
         get{return strDataBase;}
         set{strDataBase=value;}
        }
        /// <summary>
        /// 连接数据库用户名称
        /// </summary>
        public string userName
        {
         get{return strUser;}
         set{strUser =value;}
        }
        /// <summary>
        /// 连接数据库密码
        /// </summary>
        public string password
        {
         get{return strPassword;}
         set{strPassword=value;}
        }
        /// <summary>
        /// 连接数据库类型
        /// </summary>
       public int type
       {
        get{return intType;}
        set{intType = value;}
       }
      
        #endregion
       #region method
        public abstract bool testConnect(); //测试连接的抽象方法，返回值如果为true表示测试连接成功，否则表示失败
        public abstract int sqlCommand(string cmd); //数据库操作命令，返回值大于0表示操作成功，否则表示失败。
        public abstract int sqlCommand(string[] strcmd);//多个数据库操作命令，返回值大于0表示操作成功，否则表示失败。
        public abstract int sqlCommand(SqlCommand cmd);//重载
        public abstract int sqlCommand(SqlCommand[] cmd);
        public abstract int sqlCommand(OleDbCommand cmd);
        public abstract int sqlCommandSp(SqlCommand cmd); //利用存储过程进行数据库操作，返回值大于0表示操作成功，否则表示失败。
        public abstract int sqlCommandSp(SqlCommand[] tmpcmd);
        public abstract int sqlCommandSp(OleDbCommand cmd);//利用存储过程进行数据库操作，返回值大于0表示操作成功，否则表示失败。
        public abstract int sqlCommandSp(OleDbCommand[] tmpcmd);
        public abstract DataSet dbToDS(string strcmd, string strTableName);//根据指定参数命令strcmd,从数据库中查询所需数据，以数据集返回。strTableName返回数据集中的表名。
        public abstract DataSet dbToDSSp(SqlCommand cmd, string strTableName);
        public abstract DataSet dbToDSSp(OleDbCommand cmd, string strTableName);
        public abstract int restoreFile(string strcmd, string fileName, string fileType, string filePath, string fileField);
        /// <summary>
        /// 读入指定文件，返回字节数组
        /// </summary>
        /// <param name="strPath">文件路径</param>
        /// <returns>成功返回字节数组，否则返回空</returns>
        public byte[] readFileToStream(string strPath)//文件转变成字节数组
        {
            try
            {
                //创建文件流对象，打开指定路径的文件
                System.IO.FileStream st = new System.IO.FileStream(strPath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
                System.IO.BinaryReader mbr = new System.IO.BinaryReader(st);
                //定义能够存放文件的字节数组
                byte[] buffer = new byte[st.Length];
                //读取文件数据到字节数组
                mbr.Read(buffer, 0, (int)st.Length);//(从零开始存放，存放到st的长度)
                st.Close();
                return buffer;
            }
            catch (Exception)
            {
                return null;
            }
        }

       #endregion
      
    }
    /// <summary>
    /// 执行SQL Server数据库访问类
    /// </summary>
    public class DBSQL : DB
    {
        #region var
        private string strConnect = "";
        private SqlConnection sqlConnect = new SqlConnection(); //定义连接对象
        private SqlCommand sqlCmd = new SqlCommand();    //定义数据库操作命令
        private SqlDataAdapter sqlDA = new SqlDataAdapter(); //定义数据库操作适配器
        private DataSet ds = new DataSet(); //定义数据集
        #endregion
        #region property
        public string connection
        {
            get { return strConnect; }
            set { strConnect = value; }
        }
        #endregion
        #region method
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="strS">服务器名</param>
        /// <param name="strD">数据库名</param>
        /// <param name="strU">用户名</param>
        /// <param name="strP">密码</param>
        /// <param name="t">连接类型</param>
        public DBSQL(string strS, string strD, string strU, string strP)
        {
            serviceName  = strS;
           databaseName  = strD;
            userName  = strU;
            password  = strP;
            type = 3;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        public DBSQL()
        {
            strConnect = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ServerName"].ConnectionString;//从web.config中获取servername
            type = 4;
        }
        /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <returns>返回连接对象</returns>
        private SqlConnection getConnection()
        {
            try
            {
               switch (type)
                {
                    case 0: //远程连接
                        strConnect = "Data Source=" + serviceName + ",1433;Network Library=DBMSSOCN;Initial Catalog=" + databaseName + ";User Id=" + userName  + ";password=" + password  + ";Connect Timeout=60;";
                        break;
                    case 1://本地连接
                        strConnect = "workstation id=" + serviceName + ";packet size=4096;integrated security=SSPI;data source=" + serviceName + ";persist security info=False;initial catalog=" + databaseName + ";Connect Timeout=60;";
                        break;
                    case 2: // 信用连接
                        strConnect = "Server=" + serviceName + ";Database=" + databaseName + ";User ID=" + userName  + ";Password=" +password  + ";Trusted_Connection=False" + ";Connect Timeout=60;";
                        break;
                    case 3 : //默认连接
                        strConnect = "Data Source=" + serviceName + ";User ID=" + userName  + ";Pwd=" + password  + ";Initial Catalog=" + databaseName + ";Connect Timeout=60;";
                        break;
                    default:  // 本地连接
                        //strConnect = "workstation id=" + serviceName + ";packet size=4096;integrated security=SSPI;data source=" + serviceName  + ";persist security info=False;initial catalog=" + databaseName + ";Connect Timeout=60;";
                        break;
                }
                sqlConnect.ConnectionString = strConnect;
                sqlCmd.Connection = sqlConnect;
              return sqlConnect;
            }
            catch (Exception)
            {
                return null;
            }
        }
        /// <summary>
        /// //测试连接的方法.
        /// </summary>
        /// <returns>返回值如果为true表示测试连接成功，否则表示失败</returns>
        public override  bool testConnect()
        {
            try
            {
                switch (type)
                {
                    case 0: //远程连接
                        strConnect = "Data Source=" + serviceName + ",1433;Network Library=DBMSSOCN;Initial Catalog=" + databaseName + ";User Id=" + userName  + ";password=" + password  + ";Connect Timeout=60;";
                        break;
                    case 1://本地连接
                        strConnect = "workstation id=" + serviceName + ";packet size=4096;integrated security=SSPI;data source=" + serviceName + ";persist security info=False;initial catalog=" + databaseName + ";Connect Timeout=60;";
                        break;
                    case 2: // 信用连接
                        strConnect = "Server=" + serviceName + ";Database=" + databaseName + ";User ID=" + userName  + ";Password=" +password  + ";Trusted_Connection=False" + ";Connect Timeout=60;";
                        break;
                    case 3 : //默认连接
                        strConnect = "Data Source=" + serviceName + ";User ID=" + userName  + ";Pwd=" + password  + ";Initial Catalog=" + databaseName + ";Connect Timeout=60;";
                        break;
                    default:  // 本地连接
                        //strConnect = "workstation id=" + serviceName + ";packet size=4096;integrated security=SSPI;data source=" + serviceName  + ";persist security info=False;initial catalog=" + databaseName + ";Connect Timeout=60;";
                        break;
                }
                sqlConnect.ConnectionString = strConnect;
                sqlCmd.Connection = sqlConnect;
                sqlConnect.Open();
                if (sqlConnect.State == ConnectionState.Open)
                    return true;
                else
                    return false;
            }
            catch (Exception )
            {
                return false;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        public string Sqldatareader(string strcom, int i)//占用网络资源，但反应快
        {
            try
            {
                sqlConnect = getConnection();
               if (sqlConnect!=null)
                {
                    if (sqlConnect.State != ConnectionState.Open)
                        sqlConnect.Open();
                   if (sqlConnect.State == ConnectionState.Open)
                   {
                    sqlCmd.Connection = sqlConnect;                  
                    sqlCmd.CommandText = strcom;
                    SqlDataReader dr = sqlCmd.ExecuteReader();
                    if (dr.Read())
                        return dr[i].ToString();
                    else
                        return "";
                   }
                   else
                   {
                       return "";
                   }
                  
                }
                else
                    return "";
            }
            catch (Exception )
            {
                //System.Windows.Forms.MessageBox.Show(ex.ToString());
                return "";
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 数据库操作命令。
        /// </summary>
        /// <param name="cmd">数据库操作命令</param>
        /// <returns>返回值大于0表示操作成功，否则表示失败</returns>
        public override int sqlCommand(string cmd)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null) //数据库连接创建成功
                {
                    if (sqlConnect.State!=ConnectionState.Open)
                    sqlConnect.Open();                   
                    sqlCmd.CommandText = cmd;                  
                    return sqlCmd.ExecuteNonQuery();                                
                }
                else
                    return 0; //数据库连接创建失败
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        public bool todatareader(string strcom)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null)
                {
                    if (sqlConnect.State!=ConnectionState.Open)
                     sqlConnect.Open();
                if (sqlConnect.State == ConnectionState.Open)
                {
                sqlCmd.CommandText = strcom;
                    sqlCmd.CommandType = CommandType.Text;
                    SqlDataReader dr = sqlCmd.ExecuteReader();
                    if (dr.Read())
                        return true;
                    else
                        return false;
                }
                else
                {
                    return false;
                } 
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.ToString());
                return false;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 多个数据库操作命令。
        /// </summary>
        /// <param name="strcmd">数据库操作命令数组</param>
        /// <returns>返回值大于0表示操作成功，否则表示失败</returns>
        public override int sqlCommand(string[] strcmd)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null) //数据库连接成功
                {
                   if (sqlConnect.State!=ConnectionState.Open)
                     sqlConnect.Open();
                    if (sqlConnect.State == ConnectionState.Open)
                    {
                    SqlTransaction trans; //定义事务变量
                    trans = sqlConnect.BeginTransaction(); //开始事务
                    int i;
                    //循环执行数组命令
                    for (i = 0;i < strcmd.Length;i++)
                    {
                     
                        sqlCmd.Connection = sqlConnect;
                        sqlCmd.CommandTimeout = 0;
                        sqlCmd.CommandType = CommandType.Text;//字符串命令类型
                        sqlCmd.CommandText = strcmd[i];
                        sqlCmd.Transaction = trans; //命令获取事务
                        if (sqlCmd.ExecuteNonQuery() <= 0)
                        {
                            //执行失败，事务回滚。
                            trans.Rollback();
                            return 0;
                        }       
                    }
                    trans.Commit(); //执行事务;事务结束点。能执行则成功
                    return 1;
                    }
                    else
                    {
                        return -1;
                    }
                    
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 数据库命令操作
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public override int sqlCommand(SqlCommand[] cmd)
        {

            try
            {
                sqlConnect = getConnection();
                if (sqlConnect != null) //数据库连接成功
                {
                    if (sqlConnect.State != ConnectionState.Open)
                        sqlConnect.Open();
                    if (sqlConnect.State == ConnectionState.Open)
                    {
                        SqlTransaction trans; //定义事务变量
                        trans = sqlConnect.BeginTransaction(); //开始事务
                        int i;
                        //循环执行数组命令
                        for (i = 0; i <cmd.Length; i++)
                        {

                            cmd[i].Connection = sqlConnect;
                            cmd[i].CommandTimeout = 0;
                            cmd[i].Transaction = trans; //命令获取事务
                            if (cmd[i].ExecuteNonQuery() <= 0)
                            {
                                //执行失败，事务回滚。
                                trans.Rollback();
                                return 0;
                            }
                        }
                        trans.Commit(); //执行事务;事务结束点。能执行则成功
                        return 1;
                    }
                    else
                    {
                        return -1;
                    }

                }
                else
                {
                    return 0;
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 利用数据库操作对象进行数据库操作
        /// </summary>
        /// <param name="cmd">数据库操作命令对象</param>
        /// <returns>成功返回大于0的整数，否则返回0或小于0的整数</returns>
        public override  int sqlCommand(SqlCommand cmd)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null)
                {
                    cmd.Connection = sqlConnect;
                    if (sqlConnect.State!=ConnectionState.Open)
                    sqlConnect.Open();
                    if (sqlConnect.State == ConnectionState.Open)
                    {
                     return cmd.ExecuteNonQuery();
                    }
                    else
                        return -1; 
                }
                else
                    return 0;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        public override  int sqlCommand(OleDbCommand cmd) { return -1; }
        /// <summary>
        /// 利用存储过程进行数据库操作。
        /// </summary>
        /// <param name="cmd">数据库操作命令对象</param>
        /// <returns>返回值大于0表示操作成功，否则表示失败</returns>
        public override int sqlCommandSp(SqlCommand cmd)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null) //数据库连接成功
                {
                    cmd.Connection = sqlConnect; //tmpcmd获取连接对象
                    if (sqlConnect.State!=ConnectionState.Open)
                     sqlConnect.Open();
                    if (sqlConnect.State == ConnectionState.Open)
                    {
                        cmd.CommandType = CommandType.StoredProcedure; //设置命令类型为存储过程                   
                        return cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        return -1;
                    }                   
                }
                else
                    return 0;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 利用存储过程完成数据库操作
        /// </summary>
        /// <param name="tmpcmd">命令对象数组</param>
        /// <returns>返回值大于0表示操作成功，否则表示失败</returns>
        public override int sqlCommandSp(SqlCommand[] tmpcmd)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null) //连接成功
                {
                    if (sqlConnect.State!=ConnectionState.Open)
                     sqlConnect.Open();
                    if (sqlConnect.State == ConnectionState.Open)
                    {
                    SqlTransaction trans; //定义事务变量                  
                    trans = sqlConnect.BeginTransaction();//事务对象获取值
                    int i = 0;
                    //循环执行存储过程命令，如果失败将会执行会滚。
                    for (i = 0; i < tmpcmd.Length; i++)
                    {
                        tmpcmd[i].Connection = sqlConnect;
                        tmpcmd[i].CommandTimeout = 0;
                        tmpcmd[i].CommandType = CommandType.StoredProcedure;
                        if (tmpcmd[i].ExecuteNonQuery() <= 0)
                        {
                            trans.Rollback();
                            return 0;
                        }
                    }
                    trans.Commit();
                    return 1;
                    }
                    else
                    {
                        return -1;
                    }
                }
                else
                    return 0;
            }
            catch (Exception)
            { return -1; }
            finally
            {
                sqlConnect.Close();
            }
        }
        public override int sqlCommandSp(OleDbCommand cmd) { return -1; }//利用存储过程进行数据库操作，返回值大于0表示操作成功，否则表示失败。
        public override int sqlCommandSp(OleDbCommand[] tmpcmd) { return -1; }
        /// <summary>
        /// 根据指定参数命令strcmd,从数据库中查询所需数据，以数据集返回。strTableName返回数据集中的表名。
        /// </summary>
        /// <param name="strcmd">参数命令</param>
        /// <param name="strTableName">返回数据集中的表名</param>
        /// <returns>从数据库中查询所需数据，以数据集返回</returns>
        public override DataSet dbToDS(string strcmd, string strTableName)
        {
            try
            {
                DataSet ds = new DataSet(); //创建ds数据库对象
                sqlConnect = getConnection();
                if (sqlConnect!=null) //数据库连接成功
                {
                    if (sqlConnect.State!=ConnectionState.Open)
                      sqlConnect.Open();
                    if (sqlConnect.State == ConnectionState.Open)
                    {
                    sqlCmd.CommandText = strcmd;
                    sqlDA.SelectCommand = sqlCmd;
                    //sqlConnect.Open();
                    ds.Clear();
                    //sqlDA.SelectCommand.ExecuteNonQuery();
                    sqlDA.Fill(ds, strTableName); //填充数据集
                    return ds; //返回数据集
                    }
                    else
                    {
                        return null;
                    }
                  
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 根据指定参数命令cmd,从数据库中查询所需数据，以数据集返回。strTableName返回数据集中的表名。
        /// </summary>
        /// <param name="cmd">查询命令对象</param>
        /// <param name="strTableName">返回数据集中的表名</param>
        /// <returns>以数据集形式返回数据，否则返回null</returns>
        public override DataSet dbToDSSp(SqlCommand cmd, string strTableName)
        {
            try
            {
                sqlConnect = getConnection();
                if (sqlConnect!=null) //连接成功
                {
                    if (sqlConnect.State!=ConnectionState.Open)
                      sqlConnect.Open();
                     if (sqlConnect.State == ConnectionState.Open)
                     {
                     cmd.Connection = sqlConnect;
                    cmd.CommandType = CommandType.StoredProcedure; //设置命令类型为存储过程
                    sqlDA.SelectCommand = cmd;
                    DataSet ds = new DataSet(); //创建数据集对象
                  
                    ds.Clear();
                   // sqlDA.SelectCommand.ExecuteNonQuery();
                    sqlDA.Fill(ds, strTableName); //填充数据集
                    return ds;
                     }
                     else
                     {
                         return null;
                     }
                   
                }
                else
                    return null;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        public override DataSet dbToDSSp(OleDbCommand cmd, string strTableName) { return null; }
        /// <summary>
        /// 恢复数据库中的文件
        /// </summary>
        /// <param name="strcmd">查询命令</param>
        /// <param name="fileName">恢复的文件名</param>
        /// <param name="fileType">文件类型</param>
        /// <param name="filePath">存放路径</param>
        /// <param name="fileField">数据库中存放文件的字段名</param>
        /// <returns>成功返回大于0的整数，否则表示失败</returns>
        public override int restoreFile(string strcmd, string fileName, string fileType, string filePath, string fileField)
        {
            try
            {
                DataSet ds = new DataSet("temp");
                if (fileType.Length <= 0) return 0; //文件类型不合法，返回0
                ds = dbToDS(strcmd, "temp");//根据查询指令获取数据
                if (ds != null)
                {
                    byte[] b = (byte[])ds.Tables["temp"].Rows[0][fileField];
                    if (b.Length > 0)
                    {
                        System.IO.FileStream stream1 = new System.IO.FileStream(filePath + fileName + "." + fileType, System.IO.FileMode.OpenOrCreate);
                        stream1.Write(b, 0, b.Length);
                        return 1;
                    }
                    else
                        return 0;
                }
                else
                    return 0;

            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
        /// <summary>
        /// 备份数据库，path备份完整的数据库路经
        /// </summary>
        /// <param name="sourceDBName">源数据库名</param>
        /// <param name="path">别分路径</param>
        /// <returns>返回1表示成功，否则表示失败</returns>
        public int backupDB(string sourceDBName, string path)
        {
            try
            {
                sqlCommand("BACKUP DATABASE " + sourceDBName + " TO disk='" + path + ".bak'");
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
        }
        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="sourceDBName">源数据库名</param>
        /// <param name="path">还原路径</param>
        /// <returns>返回1表示成功，否则表示失败</returns>
        public int restoreDB(string sourceDBName, string path)
        {
            try
            {
                sqlCommand("RESTORE DATABASE " + sourceDBName + " FROM DISK = '" + path + "'");
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
        }          
#endregion
       
    }
    /// <summary>
    /// OLE类型数据库操作
    /// </summary>
    public class DBOLE : DB
    {
        #region var
        private string strConnect = "";
        private OleDbConnection oleConnect = new OleDbConnection(); //定义连接对象
        private OleDbCommand oleCmd = new OleDbCommand();  //定义数据库操作命令
        private OleDbDataAdapter oleDA = new OleDbDataAdapter(); //定义数据库操作适配器
        private DataSet oleDS = new DataSet(); //定义数据集
        #endregion
        #region method
        /// <summary>
        /// 构造函数
        /// </summary>
        public DBOLE()
        {
            databaseName = ConfigurationSettings.AppSettings["accessConnection"];
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="strD">数据库名</param>
        /// <param name="strU">用户名</param>
        /// <param name="strP">密码</param>
        /// <param name="intT">连接类型</param>
        public DBOLE(string strD, string strU, string strP, int  intT)
        {
            databaseName = strD;
            userName  = strU;
            password  = strP;
            type  = intT  ;
        }
        /// <summary>
        /// 测试连接的抽象方法，返回值如果为true表示测试连接成功，否则表示失败
        /// </summary>
        /// <returns></returns>
        public override  bool testConnect()
        {
            try
            {
                switch (type )
                {
                    case 0  :
                        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName  + ";User Id=" + userName  + ";Password=" + password  + "; ";
                        break;
                    case 1 :
                        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName + ";Jet OLEDB:Database Password=" +password  + ";";
                        break;
                    case 2 :
                        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName + ";Jet OLEDB:System Database=system.mdw;";
                        break;
                    case 3 :
                        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName + ";Jet OLEDB:System Database=system.mdw;User ID=" + userName  + ";Password=" + password  + ";";
                        break;
                    default:
                        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName + ";Jet OLEDB:System Database=system.mdw;";
                        break;
                }
                oleConnect.ConnectionString = strConnect;
                oleCmd.Connection = oleConnect;
                oleConnect.Open();
                if (oleConnect.State == ConnectionState.Open)
                    return true;
                else
                    return false;
            }
            catch (Exception )
            {
                return false;
            }
            finally
            {
                oleConnect.Close();
            }
        }
        /// <summary>
        /// 数据库操作命令。
        /// </summary>
        /// <param name="cmd">数据库命令字符串</param>
        /// <returns>返回值大于0表示操作成功，否则表示失败</returns>
        public override int sqlCommand(string cmd)
        {
            try
            {
                if (testConnect() == true)
                {
                    oleCmd.CommandText = cmd;
                    oleConnect.Open();
                    return oleCmd.ExecuteNonQuery();
                }
                else
                    return 0;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                oleConnect.Close();
            }
        }
        /// <summary>
        /// 数据库命令对象对数据库进行操作
        /// </summary>
        /// <param name="cmd">数据库命令对象</param>
        /// <returns>返回值大于0表示操作成功，否则表示失败</returns>
        public override int sqlCommand(OleDbCommand cmd)
        {
            try
            {
                if (testConnect() == true)
                {
                    cmd.Connection = oleConnect;
                    oleConnect.Open();
                    return cmd.ExecuteNonQuery();
                }
                else
                    return 0;
            }
            catch (Exception )
            {
                return -1;
            }
            finally
            {
                oleConnect.Close();
            }
        }
        public override int sqlCommand(string[] strcmd){ return -1; }//多个数据库操作命令，返回值大于0表示操作成功，否则表示失败。
        public override int sqlCommand(SqlCommand cmd) { return -1; }
        public override int sqlCommandSp(SqlCommand cmd) { return -1; } //利用存储过程进行数据库操作，返回值大于0表示操作成功，否则表示失败。
        public override int sqlCommand(SqlCommand[] cmd){return -1;}      
        public override int sqlCommandSp(SqlCommand[] tmpcmd) { return -1; }
        public override int sqlCommandSp(OleDbCommand cmd) { return -1; }//利用存储过程进行数据库操作，返回值大于0表示操作成功，否则表示失败。
        public override int sqlCommandSp(OleDbCommand[] tmpcmd) { return -1; }
        /// <summary>
        /// 根据指定参数命令strcmd,从数据库中查询所需数据，以数据集返回。strTableName返回数据集中的表名。
        /// </summary>
        /// <param name="strcmd">查询命令字符串</param>
        /// <param name="strTableName">数据集中的表明</param>
        /// <returns>以数据集返回</returns>
        public override DataSet dbToDS(string strcmd, string strTableName)
        {
            try
            {
                if (testConnect() == true)
                {
                    oleCmd.CommandText = strcmd;
                    oleDA.SelectCommand = oleCmd;
                    oleConnect.Open();
                    oleDS.Clear();
                    oleDA.SelectCommand.ExecuteNonQuery();
                    oleDA.Fill(oleDS, strTableName);
                    return oleDS;
                }
                else
                    return null;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                oleConnect.Close();
            }
        }
        public override DataSet dbToDSSp(SqlCommand cmd, string strTableName) { return null; }
        public override DataSet dbToDSSp(OleDbCommand cmd, string strTableName) { return null; }
        /// <summary>
        /// 恢复数据库中的文件
        /// </summary>
        /// <param name="strcmd">查询命令</param>
        /// <param name="fileName">恢复的文件名</param>
        /// <param name="fileType">文件类型</param>
        /// <param name="filePath">存放路径</param>
        /// <param name="fileField">数据库中存放文件的字段名</param>
        /// <returns>成功返回大于0的整数，否则表示失败</returns>
        public override int restoreFile(string strcmd, string fileName, string fileType, string filePath, string fileField)
        {
            try
            {
                DataSet ds = new DataSet("temp");
                if (fileType.Length <= 0) return 0; //文件类型不合法，返回0
                ds = dbToDS(strcmd, "temp");//根据查询指令获取数据
                if (ds != null)
                {
                    byte[] b = (byte[])ds.Tables["temp"].Rows[0][fileField];
                    if (b.Length > 0)
                    {
                        System.IO.FileStream stream1 = new System.IO.FileStream(filePath + fileName + "." + fileType, System.IO.FileMode.OpenOrCreate);
                        stream1.Write(b, 0, b.Length);
                        return 1;
                    }
                    else
                        return 0;
                }
                else
                    return 0;

            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                oleConnect.Close();
            }
        }
        /// <summary>
        /// 备份数据库，path备份完整的数据库路经
        /// </summary>
        /// <param name="sourceDBName">源数据库名</param>
        /// <param name="path">别分路径</param>
        /// <returns>返回1表示成功，否则表示失败</returns>
        public int backupDB(string sourceDBName, string path)
        {
            try
            {
                sqlCommand("BACKUP DATABASE " + sourceDBName + " TO disk='" + path + ".bak'");
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
        }
        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="sourceDBName">源数据库名</param>
        /// <param name="path">还原路径</param>
        /// <returns>返回1表示成功，否则表示失败</returns>
        public int restoreDB(string sourceDBName, string path)
        {
            try
            {
                sqlCommand("RESTORE DATABASE " + sourceDBName + " FROM DISK = '" + path + "'");
                return 1;
            }
            catch (Exception)
            {
                return -1;
            }
        }                   
#endregion
    }
}

zhaoyabin
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Asp.net数据库访问类（Access版本）
01-07
通用access数据库访问类（asp.net），几乎囊括所有的access数据库操作，搞这个不容易啊
ASP.NET封装的SQL数据库访问类
10-30
ASP.NET 封装的 SQL 数据库访问类 ASP.NET SQL 数据库封装访问类代码是 ASP.NET Framework 中的一种数据库访问机制，通过封装 SQL 数据库访问类来实现对数据库的操作。本文将详细介绍该类的实现机制和相关知识点。...
ASP.NET通用数据库访问类
thewebcode
08-19 540
以前我在对数据库进行操作的时候都是在同一页面对数据库进行操作。这样的话，就是操作繁琐，而且需要重复的书写对数据库操作的代码，这样不仅浪费了很多的时间，而且也使得代码看起来非常的凌乱。至从接触到了三层架构的思想，明白了分层的好处，还有面向对象的好处。今天在看书的时候，看到周金桥老师的对数据库访问的通用类，就试着模仿的写了一个，代码清晰，而且很实用，包括了对数据库的所有的常用的操作。 ...
.net常用通用数据访问类(带数组参数方法，新增调用数据库存储过程方法)
weixin_42359607的博客
03-14 250
一、App.config一般配置文件编写 <?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" /> <...
ASP.NET的数据访问和数据库集成
最新发布
听风的声音
07-08 1246
ADO.NET是.NET Framework中用于数据访问的核心技术。它提供了一组类和API，用于连接和操作各种数据源，如关系型数据库、XML和Web服务等。ADO.NET使用一种灵活的、基于组件的模型来处理数据，提供了高性能和可扩展的数据访问解决方案。在本节中，我们深入探讨了ASP.NET的数据访问和数据库集成。我们了解了ADO.NET作为.NET Framework中的核心数据访问技术，并讨论了数据库连接、数据库命令以及数据集和数据表的概念。
asp.net访问数据的方式
Blackoutdragon的博客
05-29 807
文章目录访问数据库的方法使用代码进行访问DbConnection对象的基本说明连接字符串的定义连接到数据库使用Command对象常用的属性常用方法DataReader对象的使用![在这里插入图片描述](https://img-blog.csdnimg.cn/20210529150407575.png)使用基于内容的对象访问数据库DataSet简单的登陆界面的代码——使用数据库进行验证 访问数据库的方法 使用代码进行访问 基本步骤： 使用DbConnect对象建立到数据源的连接 在连接上建立DbComma
Asp.net(c#)数据库操作类
热门推荐
死亡凋零的专栏
01-22 1万+
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using
ASP.NET 数据库访问通用工具
05-02
综上所述，ASP.NET数据库访问通用工具的构建涉及多个层面，从基本的数据库连接管理到复杂的事务处理，都需要精心设计和实现。通过创建这样一个工具，开发者可以专注于业务逻辑，而不是重复的数据库访问代码，从而...
asp.net Oracle数据库访问操作类
01-01
代码如下:using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.Oracle... ///  /// 数据访问抽象基础类 /// /// </summary>public class DBBase{
ASP.NET 数据访问类
01-01
using System; using System.Data; using System.Data.SqlClient; namespace SysClassLibrary { /// <summary> /// DataAccess 的摘要说明。 /// 数据处理基类,调用方式：DataAccess.DataSet((string)...
asp.net(C#)通用数据库操作类
08-28
asp.net(C#)通用数据库操作类,下载后在您的程序中就可以使用。
SQL数据库通用访问类(ASP.NET)
09-27
SQL数据库通用访问类(ASP.NET)
asp.net数据库操作类
12-08
asp.net数据库操作类
【ASP.NET开发】ASP.NET对SQLServer的通用数据库访问类 分类： ...
weixin_34297300的博客
09-20 212
怎么说呢，作为程序员，我们明天都应该学习新的知识。 以前我在对数据库进行操作的时候都是在同一页面对数据库进行操作。这样的话，就是操作繁琐，而且需要重复的书写对数据库操作的代码，这样不仅浪费了很多的时间，而且也使得代码看起来非常的凌乱。至从接触到了三层架构的思想，明白了分层的好处，还有面向对象的好处。今天在看书的时候，看到周金桥老师的对数据库访问的通用类，就试着模仿的写了一个，代码清晰，而...
asp.net数据库访问学习心得
Icandoeverything的专栏
03-24 4301
 asp.net数据库访问学习心得1（Oracle访问）在asp.net中通过ADO.NET访问数据库很方便，介绍这方面代码的文章也数不胜数，在这里我只说一下自己的心得还有遇到的问题及解决办法。            asp.net访问SQL Server数据库使用SqlClient，访问其他数据使用OleDb，访问Oracle还可以用OracleClient，但是要自己添加引用。      
ASP.NET连接数据库实现登录和注册
ba_wang_mao的专栏
08-18 1万+
连接数据库 在连接之前，我们先在数据库中建立数据表以及待会要用到的数据 创建连接数据库的类 右击项目->添加新项 选择类 点击是 创建好的类如下 连接数据库呢我们肯定需要用到一些关于数据库的类。 首先我们要引入命名空间using System.Data.SqlClient; 然后，网站连接数据库并且操作一共需要三步：连接、准备执行、执行 我们在这个类里面写前两步 连接 数据库的连接字符串，可以直接复制数据库的连接字符串Data Source=.\SQLE..
Java_变量&&数据类型&&关键字&&标识符
qq_43408367的博客
04-16 437
## Java_变量的基本概念 1. 当需要在程序中记录单个数据内容时，则可以申明一个变量，声明变量的本质就是在内存中申请一个存储单元，由于该存储单元中的数据内容可以在同一类型范围内发生改变，因此得名为"变量" 2. 由于存放的数据内容大小不一样，导致所需存储单元的大小不一样，在Java语言中使用数据类型加以描述，为了便于下次访问还需要给该变量指定一个名字，用于记录该变量对应的存储单元。 3. 变量的声明方式：**数据类型 变量名 = 初始值；**，其中=初始值可以省略，但不可以省略 PS:**变量的注意
ASP.NET访问数据库连接模式
ghr1249184750的博客
07-14 203
1、引用三种命名空间 using System.Data; using System.Data.SqlClient; using System.Configuration; 2、创建连接字符串 private static string strConn = ConfigurationManager.ConnectionStrings["ApplyDBConnectionString"].ConnectionString; 3、执行查询 /// <param name="sql">
.NET框架下的ASP.NET与ADO.NET数据库开发详解
ASP.NET和ADO.NET是微软.NET框架下的核心技术，用于构建动态网站和数据库驱动的应用程序。本文档深入探讨了这两个技术在数据库开发中的应用。 ASP.NET是.NET框架的一部分，它提供了创建交互式Web应用程序的工具和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值