初学ADO.NET时写的数据库访问类-.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;
   }
  }

 }
}