初学ADO.NET时写的数据库访问类-asp.net入门笔记（八）

初学ADO.NET时写的数据库访问类

/*************************************************

OleDb

**************************************************/

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace Db
{
 /// <summary>
 ///
 /// </summary>
 public class OleDb
 {
  public OleDb()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   m_oleDbCnn = null;
   //
  }

  /// <summary>
  /// Connecion连接 字符
  /// </summary>
  private string m_strCnn;// = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
  /// <summary>
  /// OleDbConnection
  /// </summary>
  private OleDbConnection m_oleDbCnn =  null;//new OleDbConnection(m_strCnn);

  /// <summary>
  /// 打开Connection
  /// </summary>
  private void OpenCnn()
  {
   if (m_oleDbCnn.State==ConnectionState.Open)
   {
    m_oleDbCnn.Close();
   }
   m_oleDbCnn.Open();
  }

  /// <summary>
  /// //执行delete，update,insert等操作
  /// </summary>
  public int ExeNoQuery(string strSql)
  {
   int nRet = -1;
   m_strError = "";
   OleDbCommand oleDbCmd = new OleDbCommand(strSql,m_oleDbCnn);
   try
   {
    OpenCnn();
    nRet = oleDbCmd.ExecuteNonQuery();
   }
   catch (OleDbException ex)
   {
    nRet = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_oleDbCnn.State==ConnectionState.Open)
    {
     oleDbCmd.Dispose();
     m_oleDbCnn.Close();
    }
   }
   return nRet;
  }

  /// <summary>
  /// //执行delete,update,insert等操作
  /// </summary>
  public int ExeNoQuery(OleDbCommand oleDbCmd)
  {
   int nRet = -1;
   m_strError = "";
   oleDbCmd.Connection = m_oleDbCnn;
   try
   {
    nRet = oleDbCmd.ExecuteNonQuery();
   }
   catch (OleDbException ex)
   {
    nRet = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_oleDbCnn.State==ConnectionState.Open)
    {
     m_oleDbCnn.Close();
    }
   }
   return nRet;
  }

  /// <summary>
  /// 判断是否存在
  /// </summary>
  public int ExeIsExist(string strSql)
  {
   int nExist = -1;
   m_strError = "";
   OleDbCommand oleDbCmd = new OleDbCommand(strSql,m_oleDbCnn);
   try
   {
    OpenCnn();
    OleDbDataReader dr= oleDbCmd.ExecuteReader();
    if (dr.Read())
    {
     nExist = 1;
    }
    else
    {
     nExist = 0;
    }
   }
   catch (OleDbException ex)
   {
    nExist = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_oleDbCnn.State==ConnectionState.Open)
    {
     oleDbCmd.Dispose();
     m_oleDbCnn.Close();
    }
   }
   return nExist;
  }

  /// <summary>
  /// 判断是否存在
  /// </summary>
  public int ExeIsExist(OleDbCommand oleDbCmd)
  {
   int nExist = -1;
   m_strError = "";
   oleDbCmd.Connection = m_oleDbCnn;
   try
   {
    OpenCnn();
    OleDbDataReader dr = oleDbCmd.ExecuteReader();
    if (dr.Read())
    {
     nExist = 1;
    }
    else
    {
     nExist = 0;
    }
   }
   catch (OleDbException ex)
   {
    nExist = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_oleDbCnn.State==ConnectionState.Open)
    {
     m_oleDbCnn.Close();
    }
   }
   return nExist;
  }

  /// <summary>
  /// 执行ExecuteScalar
  /// </summary>
  public int ExeScalar(string strSql)
  {
   int nNum = -1;
   m_strError = "";
   OleDbCommand oleDbCmd = new OleDbCommand(strSql,m_oleDbCnn);
   try
   {
    OpenCnn();
    nNum = (int)oleDbCmd.ExecuteScalar();
   }
   catch (OleDbException ex)
   {
    nNum = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_oleDbCnn.State==ConnectionState.Open)
    {
     oleDbCmd.Dispose();
     m_oleDbCnn.Close();
    }
   }
   return nNum;
  }

  /// <summary>
  /// 返回OleDbDataReader
  /// </summary>
  public OleDbDataReader ExeSqlDr(string strSql)
  {
   OleDbDataReader dr = null;
   m_strError = "";
   OleDbCommand oleDbCmd = new OleDbCommand(strSql,m_oleDbCnn);
   try
   {
    OpenCnn();
    dr = oleDbCmd.ExecuteReader();
   }
   catch (OleDbException ex)
   {
    dr = null;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_oleDbCnn.State==ConnectionState.Open)
    {
     oleDbCmd.Dispose();
    }
   }
   return dr;
  }

  /// <summary>
  /// 返回DataSet
  /// </summary>
  public DataSet ExeSqlDs(string strSql, string strTable)
  {
   m_strError = "";
   DataSet ds = new DataSet();
   OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter(strSql,m_oleDbCnn);
   try
   {
    oleDbAdapter.Fill(ds,strTable);
   }
   catch (OleDbException ex)
   {
    ds = null;
    m_strError = ex.ToString();
   }
   return ds;
  }

  /// <summary>
  /// 返回DataSet
  /// </summary>
  public DataSet ExeSqlDs(string strSql, int nStart, int nCount, string strTable)
  {
   m_strError = "";
   DataSet ds = new DataSet();
   OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter(strSql,m_oleDbCnn);
   try
   {
    oleDbAdapter.Fill(ds,nStart,nCount,strTable);
   }
   catch (OleDbException ex)
   {
    ds = null;
    m_strError = ex.ToString();
   }
   return ds;
  }

  /// <summary>
  /// 返回DataSet
  /// </summary>
  public DataSet ExeSqlDs(OleDbCommand oleDbCmd, string strTable)
  {
   m_strError = "";
   DataSet ds = new DataSet();
   oleDbCmd.Connection = m_oleDbCnn;
   OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter();
   oleDbAdapter.SelectCommand = oleDbCmd;
   try
   {
    oleDbAdapter.Fill(ds,strTable);
   }
   catch (OleDbException ex)
   {
    ds = null;
    m_strError = ex.ToString();
   }
   return ds;
  }

  /// <summary>
  /// //设定Connection 字符串，new cnn
  /// </summary>
  public void SetConnectionString(string strSource)
  {
   m_strCnn = strSource;
   m_oleDbCnn = null;
   m_oleDbCnn = new OleDbConnection(m_strCnn);
  }

  /// <summary>
  /// 错误信息
  /// </summary>
  private string m_strError;

  /// <summary>
  /// 错误
  /// </summary>
  public string StrErrorInfo
  {
   get
   {
    return m_strError;
   }
  }
 }
}

/*************************************************

SqlDb

**************************************************/

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Db
{
 /// <summary>
 ///
 /// </summary>
 public class SqlDb
 {
  
  public SqlDb()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   m_sqlCnn = null;
   //
  }

  /// <summary>
  /// Connection参数
  /// </summary>
  private string m_strCnn;   // = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
  private SqlConnection m_sqlCnn = null;    //new SqlConnection(m_strCnn);

  /// <summary>
  /// 执行delete，insert，update等操作，返回值为影响的行数
  /// </summary>
  public int ExeNoQuery(string strSql)
  {
   int nRet = -1;
   m_strError = "";
   SqlCommand sqlCmd = new SqlCommand(strSql,m_sqlCnn);
   try
   {
    OpenCnn();
    nRet = sqlCmd.ExecuteNonQuery();
   }
   catch (SqlException ex)
   {
    nRet = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_sqlCnn.State==ConnectionState.Open)
    {
     sqlCmd.Dispose();
     m_sqlCnn.Close();
    }
   }
   return nRet;
  }

  /// <summary>
  /// 执行delete，update,insert操作，返回值为受影响的行数
  /// </summary>
  public int ExeNoQuery(SqlCommand sqlCmd)
  {
   int nRet = -1;
   m_strError = "";
   sqlCmd.Connection = m_sqlCnn;
   try
   {
    OpenCnn();
    nRet = sqlCmd.ExecuteNonQuery();
   }
   catch (SqlException ex)
   {
    nRet = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_sqlCnn.State==ConnectionState.Open)
    {
     m_sqlCnn.Close();
    }
   }
   return nRet;
  }

  //判断是否存在
  public int ExeIsExist(string strSql)
  {
   int nExist = -1;
   m_strError = "";
   SqlCommand sqlCmd = new SqlCommand(strSql,m_sqlCnn);
   try
   {
    OpenCnn();
    SqlDataReader sqlReader = sqlCmd.ExecuteReader();
    if (sqlReader.Read())
    {
     nExist = 1;
    }
    else
    {
     nExist = 0;
    }

   }
   catch (SqlException ex)
   {
    nExist = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_sqlCnn.State==ConnectionState.Open)
    {
     sqlCmd.Dispose();
     m_sqlCnn.Close();
    }
   }
   return nExist;
  }

  /// <summary>
  /// 判断是否存在
  /// </summary>
  public int ExeIsExist(SqlCommand sqlCmd)
  {
   int nExist = -1;
   m_strError = "";
   sqlCmd.Connection = m_sqlCnn;
   try
   {
    OpenCnn();
    SqlDataReader sqlReader = sqlCmd.ExecuteReader();
    if (sqlReader.Read())
    {
     nExist = 1;
    }
    else
    {
     nExist = 0;
    }
   }
   catch (SqlException ex)
   {
    nExist = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_sqlCnn.State==ConnectionState.Open)
    {
     m_sqlCnn.Close();
    }
   }
   return nExist;
  }

  /// <summary>
  /// //返回DataSet
  /// </summary>
  public DataSet ExeSqlDs(string strSql, string strTable)
  {
   m_strError = "";
   SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSql,m_sqlCnn);
   DataSet dbSet = new DataSet();
   try
   {
    sqlAdapter.Fill(dbSet,strTable);
   }
   catch (SqlException ex)
   {
    dbSet = null;
    m_strError = ex.ToString();
   }
   
   return dbSet;
  }

  /// <summary>
  /// //返回DataSet
  /// </summary>
  public DataSet ExeSqlDs(string strSql, int nStart, int nCount, string strTable)
  {
   m_strError = "";
   SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSql,m_sqlCnn);
   DataSet dbSet = new DataSet();
   try
   {
    sqlAdapter.Fill(dbSet,nStart,nCount,strTable);
   }
   catch (SqlException ex)
   {
    dbSet = null;
    m_strError = ex.ToString();
   }
   return dbSet;
  }

  //返回DataReader
  public SqlDataReader ExeSqlDr(string strSql)
  {
   m_strError = "";
   SqlDataReader dr = null;
   SqlCommand sqlCmd = new SqlCommand(strSql,m_sqlCnn);
   try
   {
    OpenCnn();
    dr = sqlCmd.ExecuteReader();
   }
   catch (SqlException ex)
   {
    dr = null;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_sqlCnn.State==ConnectionState.Open)
    {
     sqlCmd.Dispose();
    }
   }
   return dr;
  }

  /// <summary>
  /// 执行ExecuteScalar
  /// </summary>
  public int ExeScalar(string strSql)
  {
   int nCount = -1;
   m_strError = "";
   SqlCommand sqlCmd = new SqlCommand(strSql,m_sqlCnn);
   try
   {
    OpenCnn();
    nCount = (int)sqlCmd.ExecuteScalar();
   }
   catch (SqlException ex)
   {
    nCount = -1;
    m_strError = ex.ToString();
   }
   finally
   {
    if (m_sqlCnn.State==ConnectionState.Open)
    {
     sqlCmd.Dispose();
     m_sqlCnn.Close();
    }
   }
   return nCount;
  }

  /// <summary>
  /// 打开Connection
  /// </summary>
  private void OpenCnn()
  {
   if (m_sqlCnn.State==ConnectionState.Open)
   {
    m_sqlCnn.Close();
   }
   m_sqlCnn.Open();
  }

  /// <summary>
  /// 返回DataSet
  /// </summary>
  public DataSet ExeSqlDs(SqlCommand sqlCmd, string strTable)
  {
   m_strError = "";
   DataSet ds = new DataSet();
   sqlCmd.Connection = m_sqlCnn;
   SqlDataAdapter sqlAdapter = new SqlDataAdapter();
   sqlAdapter.SelectCommand = sqlCmd;
   try
   {
    sqlAdapter.Fill(ds,strTable);
   }
   catch (SqlException ex)
   {
    ds = null;
    m_strError = ex.ToString();
   }
   return ds;
  }

  /// <summary>
  /// 设定Connection 字符串，new Connection
  /// </summary>
  public void SetConnectionString(string strSource)
  {
   m_strCnn = strSource;
   m_sqlCnn = null;
   m_sqlCnn = new SqlConnection(m_strCnn);
  }

  /// <summary>
  /// 错误信息
  /// </summary>

  private string m_strError;

  /// <summary>
  /// 错误信息
  /// </summary>
  public string StrErrorInfo
  {
   get
   {
    return m_strError;
   }
  }

 }
}