|
#2
huchao09112008-04-25 15:21
|
using System;
using System.Data ;
using System.Data.OleDb ;
namespace EIP.SQLconn
{
public class DataAccess
{
private OleDbConnection OleConnString ;
private OleDbTransaction trans ;
/******************************************************************************
* FunName : ExecSqlInTrans
* Input : strSqls
* Return : bool
* Describe :
* This function is to execute a set of sql strings splitted by ";", such as
* "insert into acl values('admin','UserMng');insert into acl values('Jason','LogMng')" ;
*
* In the example as up, the function can execute two sql strings in one transaction. when
* one sql string executing is failure, the two executing will both rollback to the old
* state.
*
* ****************************************************************************/
public bool ExecSqlInTrans(string strSqls)
{
OleConnString = GetConnect() ;
OleDbCommand OleCmd = new OleDbCommand("",OleConnString) ;
OleConnString.Open() ;
trans = OleConnString.BeginTransaction() ;
OleCmd.Transaction = trans ;
try
{
int i = 0 ;
string[] strSql = strSqls.Split(';') ;
for(i=0;i<strSql.Length;i++)
{
= strSql[i] ;
if(OleCmd.ExecuteNonQuery() < 0)
{
OleCmd.Transaction.Rollback() ;
return false ;
}
}
OleCmd.() ;
return true ;
}
catch
{
OleCmd.Transaction.Rollback() ;
return false ;
}
finally
{
OleConnString.Close() ;
}
}
/******************************************************************************
* FunName : GetConnect
* Input :
* Return : OleDbConnection
* Describe :
* Create the database connection and return OleDbConnection.
*
* ****************************************************************************/
public OleDbConnection GetConnect()
{
string strOleSqlConn = System.Configuration.ConfigurationSettings.AppSettings["Connection"] ;
OleConnString = new OleDbConnection(strOleSqlConn) ;
return OleConnString ;
}
/******************************************************************************
* FunName : GetDataReader
* Input : strSql
* Return : OleDbDataReader
* Describe :
* Execute the query and return OleDbDataReader.
*
* ****************************************************************************/
public OleDbDataReader GetDataReader(string strSql)
{
OleConnString = GetConnect() ;
OleConnString.Open() ;
try
{
OleDbCommand OleCmd = new OleDbCommand(strSql,OleConnString) ;
OleDbDataReader OleDr = OleCmd.ExecuteReader() ;
return OleDr ;
}
finally
{
OleConnString.Close() ;
}
}
/******************************************************************************
* FunName : GetDataSet
* Input : strSql
* Return : DataSet
* Describe :
* Execute the query and return DataSet.
*
* ****************************************************************************/
public DataSet GetDataSet(string strSql)
{
OleConnString = GetConnect() ;
OleConnString.Open() ;
try
{
DataSet OleDs = new DataSet() ;
OleDbDataAdapter OleDa = new OleDbDataAdapter(strSql,OleConnString) ;
OleDa.Fill(OleDs) ;
return OleDs ;
}
finally
{
OleConnString.Close() ;
}
}
/******************************************************************************
* FunName : CreateDataView
* Input : strSql
* Return : DataView
* Describe :
* Execute the query and return DataView.
*
* ****************************************************************************/
public DataView CreateDataView(string strSql)
{
OleConnString = GetConnect() ;
OleConnString.Open() ;
try
{
DataSet DS = new DataSet() ;
OleDbDataAdapter OleDa = new OleDbDataAdapter(strSql,OleConnString) ;
OleDa.Fill(DS) ;
DataView DV = new DataView() ;
DV.Table = DS.Tables[0] ;
return DV ;
}
finally
{
OleConnString.Close() ;
}
}
/******************************************************************************
* FunName : CreateDataTable
* Input : strSql
* Return : CreateDataTable
* Describe :
* Execute the query and return CreateDataTable.
*
* ****************************************************************************/
public DataTable CreateDataTable(string strSql)
{
OleConnString = GetConnect() ;
OleConnString.Open() ;
try
{
DataSet DS = new DataSet() ;
OleDbDataAdapter OleDa = new OleDbDataAdapter(strSql,OleConnString) ;
OleDa.Fill(DS) ;
DataTable DT = new DataTable() ;
DT = DS.Tables[0] ;
return DT ;
}
finally
{
OleConnString.Close() ;
}
}
/******************************************************************************
* FunName : ExecSql
* Input : strSql
* Return : bool
* if ture, successful;if false, failure
* Describe :
* Execute the query.
*
* ****************************************************************************/
public bool ExecSql(string strSql)
{
OleConnString = GetConnect() ;
OleConnString.Open();
OleDbCommand OleCmd = new OleDbCommand(strSql,OleConnString) ;
try
{
if( OleCmd.ExecuteNonQuery() > 0)
{
return true ;
}
else
{
return false ;
}
}
catch
{
return false ;
}
finally
{
OleConnString.Close() ;
}
}
/******************************************************************************
* FunName : GetIntFromDataReader
* Input : strSql
* Return : int
* Describe :
* Execute the query and return int.
*
* ****************************************************************************/
public int GetIntFromDataReader(string strSql)
{
// string sql="";
// sql="select count(*) from BBS";
// EIP.SQLconn.DataAccess DataAs = new EIP.SQLconn.DataAccess();
OleConnString = GetConnect() ;
OleConnString.Open() ;
try
{
OleDbCommand OleCmd = new OleDbCommand(strSql,OleConnString) ;
OleDbDataReader OleDr = OleCmd.ExecuteReader() ;
OleDr.Read();
int i = OleDr.GetInt32(0);
OleDr.Close();
return i;
}
finally
{
OleConnString.Close() ;
}
}
}
}
苦恼啊