1.C#封装数据库访问代码
App.config应用程序配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="constr" value="data source = localhost; uid=sa;pwd=sa;database=MailSend ">
</add>
</appSettings>
</configuration>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace MailSend
{
class DBConnection
{
//定义连接字符串strConnection,从配置文件中获得
private static string strConnection = System.Configuration.ConfigurationSettings.AppSettings["constr"];
//封装了执行不带返回结果集的查询结果
private static int SqlExecuteNoQuery(SqlCommand objCommand)
{
//定义数据库连接句柄和命令对象
SqlConnection objConnection = new SqlConnection(strConnection);
objCommand.Connection = objConnection;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
//执行SQL语句并返回
return objCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
//关闭连接
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
//根据commnd填充DataReader
public static SqlDataReader SqlExecuteReader(SqlCommand objCommand)
{
SqlConnection objConnection = new SqlConnection(strConnection);
objCommand.Connection = objConnection;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
//返回SqlDataReader对象
return objCommand.ExecuteReader();
}
catch(Exception e)
{
throw e;
}
finally
{
//关闭连接
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
//根据查询结果填充DataSet
private static DataSet GetDataSet(SqlCommand objCommand,string strTableName)
{
SqlConnection objConnection = new SqlConnection(strConnection);
objCommand.Connection = objConnection;
try
{
if(objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objCommand);
DataSet objDataSet = new DataSet();
objSqlDataAdapter.Fill(objDataSet,strTableName);
return objDataSet;
}
catch(Exception e)
{
throw e;
}
finally
{
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
}
}
2.VC封装数据库访问代码
// ADOConnect.h: interface for the ADOConnect class.
//
//////////////////////////////////////////////////////////////////////
#if !defined(AFX_ADOCONNECT_H__37418759_4445_4343_BA48_2E181C1295A5__INCLUDED_)
#define AFX_ADOCONNECT_H__37418759_4445_4343_BA48_2E181C1295A5__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
//导入ADO接口
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("BOF","adoBOF") rename("EOF","adoEOF")
//读出COM类型库.tlb文件,编译时系统会生成msado15.tlh和ado.tli
class ADOConnect
{
public:
ADOConnect();
virtual ~ADOConnect();
//初始化-连接数据库
void OnInitDBConn();
//执行查询，返回结果集，bstrSQL只要执行的语句
_RecordsetPtr& GetRecordSet(_bstr_t bstrSQL);
//执行查询不返回结果集
BOOL ExecuteSQL(_bstr_t bstrSQL);
//断开数据库连接
void ExitConnect();
//定义变量
public:
_ConnectionPtr m_pConnection;//指向Connection对象的指针
_RecordsetPtr m_pRecordset;//指向Recordset对象的指针
};
#endif // !defined(AFX_ADOCONNECT_H__37418759_4445_4343_BA48_2E181C1295A5__INCLUDED_)
// ADOConnect.cpp: implementation of the ADOConnect class.
//
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include "ADOConnect.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
ADOConnect::ADOConnect()
{
}
ADOConnect::~ADOConnect()
{
}
//使用Connection 对象连接数据库
void ADOConnect::OnInitDBConn()
{
//ADO库是一组COM动态库初始化OLE/COM库环境,为访问ADO接口做准备
::CoInitialize(NULL);
try
{
//创建Connection对象的实例
m_pConnection.CreateInstance("ADODB.Connection");
_bstr_t strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ChargeManage;Data Source=CURT-PC";
// _bstr_t strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ChargeManage;Data Source=CURT-PC";
m_pConnection->Open(strConnection,"","",adModeUnknown);
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
}
}
//使用记录集
_RecordsetPtr& ADOConnect::GetRecordSet(_bstr_t bstrSQL)
{
try
{
if (m_pConnection==NULL)
{
OnInitDBConn();
}
m_pRecordset.CreateInstance(_uuidof(Recordset));
m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
}
return m_pRecordset;//返回记录集
}
BOOL ADOConnect::ExecuteSQL(_bstr_t bstrSQL)
{
_variant_t RecordsAffected;
try
{
if (m_pConnection==NULL)
{
OnInitDBConn();
}
m_pConnection->Execute(bstrSQL,NULL,adCmdText);
return TRUE;
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
return FALSE;
}
}
void ADOConnect::ExitConnect()
{
//关闭记录集和连接
if (m_pRecordset!=NULL)
{
m_pRecordset->Close();
}
m_pConnection->Close();
::CoUninitialize();//释放环境
}
