初学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;
}
}
}
}
