using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Web; using System.Data;namespace DBUtility { public class MSSQLHelper { private static readonly string CONN_Str = ConfigurationManager.ConnectionStrings["SQLConStr"].ConnectionString; private static SqlCommand com = new SqlCommand(); private static SqlConnection con = new SqlConnection();
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Web;
using System.Data;namespace DBUtility
{
public class MSSQLHelper
{
private static readonly string CONN_Str = ConfigurationManager.ConnectionStrings["SQLConStr"].ConnectionString;
private static SqlCommand com = new SqlCommand();
private static SqlConnection con = new SqlConnection();
#region private methods
private static void OpenConnection()
{
if (con.State != ConnectionState.Open)
{
con.ConnectionString = CONN_Str;
com.Connection = con;
con.Open();
}
}
private static void CloseConnection()
{
if (con.State != ConnectionState.Closed)
{
con.Close();
con.Dispose();
}
}
private static void DiscoverParameters(bool returnValue,object [] values)
{
//OpenConnection();
com.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(com);
//CloseConnection();
if (!returnValue)
{
com.Parameters.RemoveAt(0);
}
int temp = 0;
for (int i = 0, j = com.Parameters.Count; i < j; i++)
{
if (com.Parameters[i].Direction == ParameterDirection.InputOutput || com.Parameters[i].Direction == ParameterDirection.Output)
{
temp++;
com.Parameters[i].Value = DBNull.Value;
}
else
{
com.Parameters[i].Value = values[i-temp];
}
}
} private void AssignParametersValue(SqlParameter[] parameters, object[] paraValues)
{
for (int i = 0, j = parameters.Length; i < j; i++)
{
if (paraValues[i] == null)
parameters[i].Value = DBNull.Value;
else
{
parameters[i].Value = paraValues[i];
}
}
}
private void AssignParametersValue(object[] paraValues)
{
for (int i = 0, j = com.Parameters.Count; i < j; i++)
{
com.Parameters[i].Value = paraValues[i];
}
}
private void AttachParameters(SqlParameter[] parameters)
{
com.Parameters.Clear();
foreach (SqlParameter sp in parameters)
{
if (sp != null)
{
if ((sp.Direction == ParameterDirection.Input || sp.Direction == ParameterDirection.InputOutput) && sp.Value == null)
{
sp.Value = DBNull.Value;
}
com.Parameters.Add(sp);
}
}
}
#endregion private methods
#region public method
public static object ExecuteScalar(string commandText, params object[] values)
{
object retVal = null;
OpenConnection();
com.CommandText = commandText;
com.CommandType = CommandType.Text;
retVal = com.ExecuteScalar();
CloseConnection();
return retVal;
}
public static object ExecuteSpScalar(string commandText,params object[] values)
{
object retVal = null;
OpenConnection();
com.CommandText = commandText;
com.CommandType = CommandType.Text;
com.CommandType = CommandType.StoredProcedure;
DiscoverParameters(false, values);
retVal = com.ExecuteScalar();
CloseConnection();
return retVal;
} public static DataSet ExecuteDataSet(string commandText, params object[] values)
{
DataSet ds = new DataSet();
OpenConnection();
com.CommandText = commandText;
com.CommandType = CommandType.Text;
SqlDataAdapter sda = new SqlDataAdapter(com);
if (sda == null)
{
ds = null;
}
else
{
sda.Fill(ds);
}
CloseConnection();
return ds;
} public static DataSet ExecuteSpDataSet(string commandText, params object[] values)
{
DataSet ds = new DataSet();
OpenConnection();
com.CommandText = commandText;
com.CommandType = CommandType.Text;
com.CommandType = CommandType.StoredProcedure;
DiscoverParameters(false, values);
SqlDataAdapter sda = new SqlDataAdapter(com);
if (sda == null)
{
ds = null;
}
else
{
sda.Fill(ds);
}
CloseConnection();
return ds;
} public static DataTable ExecuteDataTable(string commandText, params object[] values)
{
DataSet ds=ExecuteDataSet(commandText, values);
if(ds==null)
return null;
else
return ds.Tables[0];
} public static DataTable ExecuteSpDataTable(string commandText, params object[] values)
{
DataSet ds = ExecuteSpDataSet(commandText, values);
if(ds==null)
return null;
else
return ds.Tables[0];
} public static int ExecuteNonQuery(string commandText, params object[] values)
{
int retVal = -1;
OpenConnection();
com.CommandText = commandText;
com.CommandType = CommandType.Text;
retVal = com.ExecuteNonQuery();
CloseConnection();
return retVal;
} public static int ExecuteSpNonQuery(string commandText, params object[] values)
{
int retVal = -1;
OpenConnection();
com.CommandText = commandText;
com.CommandType = CommandType.Text;
com.CommandType = CommandType.StoredProcedure;
DiscoverParameters(false, values);
retVal = com.ExecuteNonQuery();
CloseConnection();
return retVal;
}
public static SqlDataReader ExecuteDataReader(string commandText, params object[] values)
{
OpenConnection();
com.CommandText=commandText;
com.CommandType = CommandType.Text;
SqlDataReader sdr = com.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
public static SqlDataReader ExecuteSpDataReader(string commandText, params object[] values)
{
OpenConnection();
com.CommandText=commandText;
com.CommandType = CommandType.Text;
com.CommandType = CommandType.StoredProcedure;
DiscoverParameters(false, values);
SqlDataReader sdr = com.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
} #endregion public method
}
}
/////用来访问数据库的类。要用次类的话，程序中应该进行的初始化工作为：
/////在stdafx.h中包含下面一句，用来将ado的dll文件引入到程序中：
/////#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")
/////在主进程的InitInstance()中要添加的语句，用来初始化ole
///// if(!AfxOleInit())
///// {
///// AfxMessageBox("OLE初始化错误");
///// return FALSE;
///// }
/////
////关于m_pRecordset指针的释放问题
////在.cpp文件中要包含"CPFile.h"
///////////////////////////////////////////////////////////////////////////////////////////
class CPData
{
public: //默认初始化构造函数
CPData(); //传入连接的构造函数
CPData(_ConnectionPtr pConnection); //析构函数
virtual ~CPData();
public: //连接数据库
BOOL Connect(CString strUser,CString strPassword,CString strFileName="ConnectionParam.udl",int nOptions=-1,CString strConStr="Provider=sqloledb.1;Data Source=(local);Database=VLan"); //关闭数据库的连接
void DisConnect(); //数据库查询语句，用来对数据库的各种字段进行查询
//如果成功返回TRUE,否则返回FALSE.查询的结果存储在类的共有成员变量m_pRecordset中
//查询结果的行数和列数分别保存在共有成员变量m_nResultRow和m_nResultCol中
BOOL Select(CString strSql); //查询语句，负责对仅仅查询一个字段的情况进行处理
//结果存放在CStringArray类型的变量pResult中
BOOL Select(CString strSql,CStringArray& Result);
//对多个字段进行查询
BOOL SelectMulitCol(CString strSql,CStringArray& Result);
//打开表
BOOL OpenTable(CString strTable); //打开表
BOOL OpenTable(CString strTable,CStringArray& Result); //进行其它的更新操作
BOOL Execute(CString strSql);
public:
BOOL ExecuteTrans(CStringArray& aSql);
//关闭结果集合
void CloseRecordset(); //得到操作结果的列数
long GetResultCol(); //得到操作结果的条数
long GetResultRow(); //得到操作结果
_RecordsetPtr GetResult();
private:
//数据库操作返回的结果条数
long m_nResultRow; //返回的_RecordsetPtr中列数
long m_nResultCol; //连接指针
_ConnectionPtr m_pConnection; //命令执行指针
_CommandPtr m_pCommand; //结果集指针
_RecordsetPtr m_pRecordset;
};实现://////////////////////////////////////////////////////////////////////
// 构造函数
////////////////////////////////////////////////////////////////////////默认的构造函数
CPData::CPData()
{
m_pConnection = NULL;
//初始化
m_nResultRow = 0;
m_nResultCol=0; //创建对象
m_pRecordset.CreateInstance(_uuidof(Recordset));
m_pCommand.CreateInstance(_uuidof(Command));
}//传入参数的构造函数
CPData::CPData(_ConnectionPtr pConnection)
{
m_pConnection = pConnection;
m_nResultRow = 0;
m_nResultCol=0;
//创建对象
m_pRecordset.CreateInstance(_uuidof(Recordset));
m_pCommand.CreateInstance(_uuidof(Command)); }
//////////////////////////////////////////////////////////////////////
// 析构函数
//////////////////////////////////////////////////////////////////////
CPData::~CPData()
{
if(m_pRecordset->State!=adStateClosed)
m_pRecordset->Close();
m_pRecordset = NULL;
if(m_pCommand->State!=adStateClosed)
m_pCommand->Release();
m_pCommand = NULL;
if(m_pConnection->State!=adStateClosed) //如果连接还没有关闭
m_pConnection->Close();
m_pConnection = NULL;
}/////////////////////////////////////////////////////////////////////
///简单操作函数
//////////////////////////////////////////////////////////////////////得到操作结果的行数
long CPData::GetResultRow()
{
return this->m_nResultRow;
}//得到操组结果的列数
long CPData::GetResultCol()
{
return this->m_nResultCol;
}//得到操作结果
_RecordsetPtr CPData::GetResult()
{
return this->m_pRecordset;
}///////////////////////////////////////////////////////////////////////
///连接操作
/////////////////////////////////////////////////////////////////////////连接到数据库
//1.连接字符串可以自己构造,也可以从文件中读出
BOOL CPData::Connect(CString strUser,CString strPassword,CString strFileName,int nOptions,CString strConStr)
{
try{
m_pConnection.CreateInstance(__uuidof(Connection));
HRESULT hr;
//如果用文件方式配置数据源,进行配置
if(strFileName.Compare("")!=0&&CPFile::IsFileExist(strFileName))
{
CString con = "File Name="+strFileName;
m_pConnection->ConnectionString =(_bstr_t)con;
hr=m_pConnection->Open("","","",nOptions);
}
else
{
//自己配置连接字符串
m_pConnection->ConnectionString = (_bstr_t)strConStr;
hr=m_pConnection->Open("",_bstr_t(strUser),_bstr_t(strPassword),nOptions);
}
//进行连接
//连接失败
if(FAILED(hr))
{
AfxMessageBox("连接失败!");
return FALSE;
}
}
catch(_com_error&e)
{
AfxMessageBox(e.Description()+"B");
return FALSE;
}
return TRUE;
}//断开连接
void CPData::DisConnect()
{
if(m_pConnection->State!=adStateClosed)
m_pConnection->Close();
}///////////////////////////////////////////////////////////////////////
///更新操作
///////////////////////////////////////////////////////////////////////
BOOL CPData::Execute(CString strSql)
{
try
{
_variant_t vNULL;
vNULL.vt = VT_ERROR; ///定义为无参数
vNULL.scode = DISP_E_PARAMNOTFOUND; ///非常关键的一句，将建立的连接赋值给它
m_pCommand->ActiveConnection = m_pConnection; ///命令字串
m_pCommand->CommandText = (_bstr_t)strSql; ///执行命令，取得记录集
m_pRecordset = m_pCommand->Execute(&vNULL,&vNULL,adCmdText); //确实，vNULL中的intVal是执行操作所影响的行数
m_nResultRow = 0;
m_nResultRow = vNULL.intVal;
}
catch(_com_error&e)
{
m_nResultRow = 0;
return FALSE;
}
return TRUE;
}
///////////////////////////////////////////////////////////////////////
///查询操作
///////////////////////////////////////////////////////////////////////BOOL CPData::Select(CString strSql)
{
try
{
m_nResultCol=0;
m_nResultRow=0;
m_pRecordset->CursorLocation=adUseClient; //设置游标位置,设置为客户端形式,否则GetRecordCount()返回值不对
m_pRecordset->Open(_variant_t(strSql),_variant_t((IDispatch *)m_pConnection,true),adOpenDynamic,adLockOptimistic,adCmdText);
m_nResultCol = m_pRecordset->Fields->GetCount();//得到查询结果的列数
m_nResultRow = m_pRecordset->GetRecordCount(); //得到查询结果的行数
}
catch(_com_error&e)
{
AfxMessageBox(e.Description()+"D");
return FALSE;
}
return TRUE;
}//查询语句，负责对仅仅查询一个字段的情况进行处理
//结果存放在CStringArray类型的变量pResult中
BOOL CPData::Select(CString strSql,CStringArray& Result)
{
if(Select(strSql)!=0)
{
Result.RemoveAll();
for(int i=0;i<m_nResultRow;i++)
{
_variant_t value;
value=m_pRecordset->Fields->Item[(long)0]->Value;
if(value.vt==3||value.vt==14)
{
CString strTrans;
strTrans.Format("%ld",value.intVal);
Result.Add(strTrans);
}
else
Result.Add(value.bstrVal);//
m_pRecordset->MoveNext();
}
m_pRecordset->Close();
return TRUE;
}
else
{
m_pRecordset->Close();
return FALSE;
}
}BOOL CPData::SelectMulitCol(CString strSql,CStringArray& Result)
{
if(Select(strSql)!=0)
{
Result.RemoveAll();
_variant_t value;
for(int i=0;i<m_nResultRow;i++)
{
for(int j=0;j<m_nResultCol;j++)
{
value=m_pRecordset->Fields->Item[(long)(/*i*m_nResultCol+*/j)]->Value;
if(value.vt==3||value.vt==14)
{
CString strTrans;
strTrans.Format("%ld",value.intVal);
Result.Add(strTrans);
}
else
if(value.vt==7)
{
COleDateTime time = value.date;
CString strTemp;
strTemp.Format("%d-%d-%d %s",time.GetYear(),time.GetMonth(),time.GetDay(),time.Format("%H:%M:%S"));
Result.Add(strTemp);
}
else
Result.Add(value.bstrVal);//
}
m_pRecordset->MoveNext();
}
m_pRecordset->Close();
return TRUE;
}
else
{
m_pRecordset->Close();
return FALSE;
}
}//打开整张表
BOOL CPData::OpenTable(CString strTable)
{
try
{
m_nResultCol=0;
m_nResultRow=0;
m_pRecordset->CursorLocation=adUseClient; //设置游标位置,设置为客户端形式,否则GetRecordCount()返回值不对
m_pRecordset->Open(_variant_t(strTable),_variant_t((IDispatch *)m_pConnection,true),adOpenDynamic,adLockOptimistic,adCmdTable);
m_nResultCol = m_pRecordset->Fields->GetCount();//得到查询结果的列数
m_nResultRow = m_pRecordset->GetRecordCount(); //得到查询结果的行数
}
catch(_com_error&e)
{
AfxMessageBox(e.Description()+"E");
return FALSE;
}
return TRUE;
}BOOL CPData::OpenTable(CString strTable,CStringArray& Result)
{
if(OpenTable(strTable)!=0)
{
Result.RemoveAll();
_variant_t value;
for(int i=0;i<m_nResultRow;i++)
{
for(int j=0;j<m_nResultCol;j++)
{
value=m_pRecordset->Fields->Item[(long)(/*i*m_nResultCol+*/j)]->Value;
if(value.vt==3||value.vt==14)
{
CString strTrans;
strTrans.Format("%ld",value.intVal);
Result.Add(strTrans);
}
else
if(value.vt==7)
{
COleDateTime time = value.date;
CString strTemp;
strTemp.Format("%d-%d-%d %s",time.GetYear(),time.GetMonth(),time.GetDay(),time.Format("%H:%M:%S"));
Result.Add(strTemp);
}
else
Result.Add(value.bstrVal);//
}
m_pRecordset->MoveNext();
}
m_pRecordset->Close();
return TRUE;
}
else
{
return FALSE;
}
}/////////////////////////////////////////////////////////////////////////////
///关闭结果集
/////////////////////////////////////////////////////////////////////////////
void CPData::CloseRecordset()
{
if(m_pRecordset->State != adStateClosed)
m_pRecordset->Close();
}
BOOL CPData::ExecuteTrans(CStringArray& aSql)
{
try{
int nNum = aSql.GetSize();
m_pConnection->BeginTrans();
for(int i=0;i<nNum;i++)
{
CString strSql = aSql.GetAt(i);
m_pConnection->Execute((_bstr_t)aSql.GetAt(i),NULL,adCmdText);
}
m_pConnection->CommitTrans();
return TRUE;
}
catch(_com_error& e)
{
m_pConnection->RollbackTrans();
AfxMessageBox(e.Description()+"F");
return FALSE;
}
}
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lxpcgh/archive/2005/06/08/390615.aspx
先把CADODatabase学会了....收藏了.