provider采用OLEDB.
using
System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
/**/ /// <summary>
/// OleDb 数据库操作类
/// Allen.gong 3.31
/// </summary>
public class clsOleDB
... {
私有变量定义#region 私有变量定义
private OleDbConnection oleCnn;
private OleDbCommand oleCmm;
private string strConn; /**//* 数据库联接字符串 */
private string strErr = "";
private string pthLog = "";
private string pthCnn = "";
#endregion
类构造clsOleDB#region 类构造clsOleDB
/**//// <summary>
/// 类构造clsOleDB
/// <param name="strConn"></param>
public clsOleDB(string strCnn, string strLog)
...{
this.pthCnn = strCnn;
this.pthLog = strLog;
}
#endregion
设置数据库连接字符串#region 设置数据库连接字符串
/**//// <summary>
/// 设置数据库连接字符串
/// <param name="strConn"></param>
public void setConn(string strDB)
...{
this.strConn = getDBConn(strDB); /**//*调用getDBConn()方法从文件中读取数据库连接字符串并赋给strConn*/
}
#endregion
日志记录函数#region 日志记录函数
/**//// <summary>
/// 日志记录函数
/// </summary>
/// <param name="strPath"></param>
/// <param name="strInfo"></param>
private void wteLog(string strPath, string strInfo) /**//*若在pop页面调用需要把方法设置为public*/
...{
if (strPath.Trim() == "")
...{
strPath = @"C:logPOC " + System.DateTime.Now.ToString("yyyy-MM-dd") + ".txt";
}
try
...{
FileInfo fleLog = new FileInfo(strPath);
StreamWriter steWrt = fleLog.AppendText();
steWrt.WriteLine(DateTime.Now.ToString());
steWrt.WriteLine(strInfo);
steWrt.Close();
}
catch
...{
}
}
#endregion
取得数据库连接字符串#region 取得数据库连接字符串
/**//// <summary>
/// 取得数据库连接字符串
/// </summary>
/// <returns></returns>
public string getDBConn(string strDB)
...{
string strLine = "";
try
...{
FileInfo fleInfo = new FileInfo(pthCnn);
StreamReader steReader = fleInfo.OpenText();
bool booExist = false;
do
...{
//从文本文件读取一行
strLine = "";
strLine = steReader.ReadLine();
//空白行时,退出循环
if (strLine == null)
...{
break;
}
int intLoc = strLine.IndexOf('=');
int intLoc2 = strLine.Length - intLoc;
string strName = strLine.Substring(0, intLoc);
if (strName.ToUpper() == strDB.ToUpper())
...{
strLine = strLine.Substring(intLoc + 1, intLoc2 - 1);
booExist = true;
}
}
while (!booExist);
steReader.Close();
}
catch (Exception ee)
...{
this.wteLog(this.pthLog, "调用getDBConn方法出错:" + ee.Message);
}
return strLine;
}
#endregion
数据库连接打开#region 数据库连接打开
/**//// <summary>
/// 数据库连接打开
/// </summary>
private void oleOpen()
...{
oleCnn = new OleDbConnection(strConn);
try
...{
oleCnn.Open();
}
catch (Exception e)
...{
strErr = e.Message;
oleCnn.Close();
}
}
#endregion
根据SQL查询返回 (dv)#region 根据SQL查询返回 (dv)
/**//// <summary>
/// 根据SQL查询返回DataView
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataView getDvRtn(string strSQL)
...{
oleOpen();
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleCnn);
DataSet dsRlt = new DataSet();
oleDa.Fill(dsRlt);
DataView dvRtn = new DataView(dsRlt.Tables[0]);
oleClose();
return dvRtn;
}
#endregion
根据SQL查询返回 (ds)#region 根据SQL查询返回 (ds)
/**//// <summary>
/// 根据SQL查询返回 (ds)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet getDsRtn(string strSQL)
...{
oleOpen();
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleCnn);
DataSet dsRtn = new DataSet();
oleDa.Fill(dsRtn);
oleClose();
return dsRtn;
}
#endregion
根据SQL查询返回 (dt)#region 根据SQL查询返回 (dt)
/**//// <summary>
/// 根据SQL查询返回 (dt)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable getDtRtn(string strSQL)
...{
oleOpen();
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleCnn);
DataSet dsRtn = new DataSet();
DataTable dtRtn = new DataTable();
oleDa.Fill(dsRtn);
//根据查询的结果返回具体的 (dt)
if (dsRtn != null)
...{
if (dsRtn.Tables.Count > 0)
...{
dtRtn = dsRtn.Tables[0];
}
else
...{
dtRtn = null;
}
}
else
...{
dtRtn = null;
}
//数据库连接关闭
oleClose();
//具体值返回
return dtRtn;
}
#endregion
根据SQL查询返回 (dr)#region 根据SQL查询返回 (dr)
/**//// <summary>
/// 根据SQL查询返回 (dr)
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public OleDbDataReader getDrRtn(string strSQL)
...{
oleOpen();
oleCmm = new OleDbCommand(strSQL, oleCnn);
OleDbDataReader drRtn = oleCmm.ExecuteReader();
oleClose();
return drRtn;
}
#endregion
执行SQL语句 (返回影响行数)#region 执行SQL语句 (返回影响行数)
/**//// <summary>
/// 执行SQL语句 (返回影响行数)
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public int exeSql(string strSQL)
...{
oleOpen();
oleCmm = new OleDbCommand(strSQL, oleCnn);
int intNum = oleCmm.ExecuteNonQuery();
oleClose();
return intNum;
}
#endregion
数据库连接关闭#region 数据库连接关闭
/**//// <summary>
/// 数据库连接关闭
/// </summary>
public void oleClose()
...{
oleCnn.Close();
}
#endregion
}
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
/**/ /// <summary>
/// OleDb 数据库操作类
/// Allen.gong 3.31
/// </summary>
public class clsOleDB
... {
私有变量定义#region 私有变量定义
private OleDbConnection oleCnn;
private OleDbCommand oleCmm;
private string strConn; /**//* 数据库联接字符串 */
private string strErr = "";
private string pthLog = "";
private string pthCnn = "";
#endregion
类构造clsOleDB#region 类构造clsOleDB
/**//// <summary>
/// 类构造clsOleDB
/// <param name="strConn"></param>
public clsOleDB(string strCnn, string strLog)
...{
this.pthCnn = strCnn;
this.pthLog = strLog;
}
#endregion
设置数据库连接字符串#region 设置数据库连接字符串
/**//// <summary>
/// 设置数据库连接字符串
/// <param name="strConn"></param>
public void setConn(string strDB)
...{
this.strConn = getDBConn(strDB); /**//*调用getDBConn()方法从文件中读取数据库连接字符串并赋给strConn*/
}
#endregion
日志记录函数#region 日志记录函数
/**//// <summary>
/// 日志记录函数
/// </summary>
/// <param name="strPath"></param>
/// <param name="strInfo"></param>
private void wteLog(string strPath, string strInfo) /**//*若在pop页面调用需要把方法设置为public*/
...{
if (strPath.Trim() == "")
...{
strPath = @"C:logPOC " + System.DateTime.Now.ToString("yyyy-MM-dd") + ".txt";
}
try
...{
FileInfo fleLog = new FileInfo(strPath);
StreamWriter steWrt = fleLog.AppendText();
steWrt.WriteLine(DateTime.Now.ToString());
steWrt.WriteLine(strInfo);
steWrt.Close();
}
catch
...{
}
}
#endregion
取得数据库连接字符串#region 取得数据库连接字符串
/**//// <summary>
/// 取得数据库连接字符串
/// </summary>
/// <returns></returns>
public string getDBConn(string strDB)
...{
string strLine = "";
try
...{
FileInfo fleInfo = new FileInfo(pthCnn);
StreamReader steReader = fleInfo.OpenText();
bool booExist = false;
do
...{
//从文本文件读取一行
strLine = "";
strLine = steReader.ReadLine();
//空白行时,退出循环
if (strLine == null)
...{
break;
}
int intLoc = strLine.IndexOf('=');
int intLoc2 = strLine.Length - intLoc;
string strName = strLine.Substring(0, intLoc);
if (strName.ToUpper() == strDB.ToUpper())
...{
strLine = strLine.Substring(intLoc + 1, intLoc2 - 1);
booExist = true;
}
}
while (!booExist);
steReader.Close();
}
catch (Exception ee)
...{
this.wteLog(this.pthLog, "调用getDBConn方法出错:" + ee.Message);
}
return strLine;
}
#endregion
数据库连接打开#region 数据库连接打开
/**//// <summary>
/// 数据库连接打开
/// </summary>
private void oleOpen()
...{
oleCnn = new OleDbConnection(strConn);
try
...{
oleCnn.Open();
}
catch (Exception e)
...{
strErr = e.Message;
oleCnn.Close();
}
}
#endregion
根据SQL查询返回 (dv)#region 根据SQL查询返回 (dv)
/**//// <summary>
/// 根据SQL查询返回DataView
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataView getDvRtn(string strSQL)
...{
oleOpen();
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleCnn);
DataSet dsRlt = new DataSet();
oleDa.Fill(dsRlt);
DataView dvRtn = new DataView(dsRlt.Tables[0]);
oleClose();
return dvRtn;
}
#endregion
根据SQL查询返回 (ds)#region 根据SQL查询返回 (ds)
/**//// <summary>
/// 根据SQL查询返回 (ds)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet getDsRtn(string strSQL)
...{
oleOpen();
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleCnn);
DataSet dsRtn = new DataSet();
oleDa.Fill(dsRtn);
oleClose();
return dsRtn;
}
#endregion
根据SQL查询返回 (dt)#region 根据SQL查询返回 (dt)
/**//// <summary>
/// 根据SQL查询返回 (dt)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable getDtRtn(string strSQL)
...{
oleOpen();
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleCnn);
DataSet dsRtn = new DataSet();
DataTable dtRtn = new DataTable();
oleDa.Fill(dsRtn);
//根据查询的结果返回具体的 (dt)
if (dsRtn != null)
...{
if (dsRtn.Tables.Count > 0)
...{
dtRtn = dsRtn.Tables[0];
}
else
...{
dtRtn = null;
}
}
else
...{
dtRtn = null;
}
//数据库连接关闭
oleClose();
//具体值返回
return dtRtn;
}
#endregion
根据SQL查询返回 (dr)#region 根据SQL查询返回 (dr)
/**//// <summary>
/// 根据SQL查询返回 (dr)
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public OleDbDataReader getDrRtn(string strSQL)
...{
oleOpen();
oleCmm = new OleDbCommand(strSQL, oleCnn);
OleDbDataReader drRtn = oleCmm.ExecuteReader();
oleClose();
return drRtn;
}
#endregion
执行SQL语句 (返回影响行数)#region 执行SQL语句 (返回影响行数)
/**//// <summary>
/// 执行SQL语句 (返回影响行数)
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public int exeSql(string strSQL)
...{
oleOpen();
oleCmm = new OleDbCommand(strSQL, oleCnn);
int intNum = oleCmm.ExecuteNonQuery();
oleClose();
return intNum;
}
#endregion
数据库连接关闭#region 数据库连接关闭
/**//// <summary>
/// 数据库连接关闭
/// </summary>
public void oleClose()
...{
oleCnn.Close();
}
#endregion
}
连接字符串文件:
1DB=Provider=SQLOLEDB.1;User ID=sa;Password=sa;Initial Catalog=Database1;Data Source=.
2DB=Provider=SQLOLEDB.1;User ID=sa;Password=sa;Initial Catalog=Database2;Data Source=.
3DB=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FSDBNL;Data Source=.
SQLSERVER默认的连接方式的类和这个差不多,多写了一个方法而已:
/**/
/// <summary>
/// 建立数据库连接
/// 从web.config里读取配置
/// </summary>
/// <returns>SqlConnecion</returns>
// public SqlConnection createConnection()
// {
// sqlConn = new SqlConnection(ConfigurationSettings.AppSettings["Conn_Ultimus"]);
// return sqlConn;
// }
/// 建立数据库连接
/// 从web.config里读取配置
/// </summary>
/// <returns>SqlConnecion</returns>
// public SqlConnection createConnection()
// {
// sqlConn = new SqlConnection(ConfigurationSettings.AppSettings["Conn_Ultimus"]);
// return sqlConn;
// }
默认SQLSERVER连接不需要provider,连接字符串文件写法就不同:
1DB=User ID=sa;Password=sa;Initial Catalog=Database1;Data Source=.
2DB=User ID=sa;Password=sa;Initial Catalog=Database2;Data Source=.
3DB=Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FSDBNL;Data Source=.