linkdatabase.h

Visual C++.net数据库开发经典案例

#pragma once

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Diagnostics;
using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::Windows::Forms;
 
namespace My
{
	/// <summary> 
	/// LinkDataBase 摘要
	/// 用于根据SQL生成不同的Dataset
	/// </summary>
	__gc public class LinkDataBase :  public System::ComponentModel::Component
	{

	
		//--------------------成员声明------------------------
	public:		
		//数据库连接字符串
		static String* ConnectionString = new String("workstation id=localhost;\
												 Integrated Security=SSPI;\
												 database=libbook");	
		SqlConnection* myConnection;
	private:
		String* strSQL;//SQL查询语句
		SqlCommandBuilder* sqlCmdBld;
		SqlCommand* sqlCmd;
		DataSet* ds;
		SqlDataAdapter* da;
		//------------------成员声明结束----------------------

	public:
		LinkDataBase(void)
		{
			InitializeComponent();

			//成员变量初始化
			
			//打开连接
			this->myConnection = new SqlConnection(this->ConnectionString);			
			this->myConnection->Open();
			//初始化Command
			this->sqlCmd = new SqlCommand();
			this->sqlCmd->Connection = this->myConnection;
			//初始化Adapter
			this->da = new SqlDataAdapter(this->sqlCmd);			
			ds = new DataSet();
		}
		LinkDataBase(System::ComponentModel::IContainer *container) : components(0)
		{
			/// <summary>
			/// Windows.Forms 类撰写设计器支持所必需的
			/// </summary>

			container->Add(this);
			InitializeComponent();
		}
		
	protected: 
		void Dispose(Boolean disposing)
		{
			if (disposing && components)
			{
				components->Dispose();
			}
			__super::Dispose(disposing);
		}
				
	private:
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		System::ComponentModel::Container *components;
		
		/// <summary>
		/// 设计器支持所需的方法 - 不要用代码编辑器
		///修改此方法的内容。
		/// </summary>		
		void InitializeComponent(void)
		{
			components = new System::ComponentModel::Container();
		}
			//--------------------成员方法------------------------
	public:
		//------------------------选择数据库----------------------------
		//生成并返回一个数据集
		DataSet* SelectDataBase(String* tempStrSQL,String* tempTableName)
		{
			//如果没有指定数据表名称，则设置为"Table1"
			if(tempTableName == NULL)
			{
				tempTableName = new String("Table1");
			}
			//根据输入的SQL语句操作数据库
			this->strSQL = tempStrSQL;
			da->SelectCommand->CommandText = this->strSQL;
			try
			{
				this->da->Fill(this->ds,tempTableName);
			}
			catch(Exception* e)
			{
				MessageBox::Show(e->Message,"提示");
			}

			//返回填充了数据的DataSet，其中的数据表以tempTableName命名
			return this->ds;
		}
		DataSet* SelectDataBase(String* tempStrSQL)
		{
			String* tempTableName = new String("Table1");
			return SelectDataBase(tempStrSQL,tempTableName);
		}	
		
		//对指定的数据集进行选择
		void SelectDataBase(DataSet* SpecificDataSet,String* tempStrSQL,String* tempTableName)
		{
			//如果没有指定数据表名称，则设置为"Table1"
			if(tempTableName == NULL)
			{
				tempTableName = new String("Table1");
			}
			//根据输入的SQL语句操作数据库
			this->strSQL = tempStrSQL;
			da->SelectCommand->CommandText =  this->strSQL;	
			try
			{
				this->da->Fill(SpecificDataSet,tempTableName);
			}
			catch(Exception* e)
			{
				MessageBox::Show(e->Message,"提示");
			}			
			//this->da->Update(ChangedDataSet,tempTableName);
		}
		void SelectDataBase(DataSet* SpecificDataSet,String* tempStrSQL)
		{	
			String* tempTableName = new String("Table1");
			SelectDataBase(SpecificDataSet,tempStrSQL,tempTableName);
		}
		//对指定DataSet用特定DataAdapter进行操作
		void SelectDataBase(SqlDataAdapter* dataAdapter,DataSet* SpecificDataSet,String* tempStrSQL,String* tempTableName)
		{
			//如果没有指定数据表名称，则设置为"Table1"
			if(tempTableName == NULL)
			{
				tempTableName = new String("Table1");
			}
			//根据输入的SQL语句操作数据库		
			
			SqlCommand* sqlCmd = new SqlCommand(tempStrSQL,this->myConnection);
			dataAdapter->SelectCommand = sqlCmd;
			try
			{
				dataAdapter->Fill(SpecificDataSet,tempTableName);
			}
			catch(Exception* e)
			{
				MessageBox::Show(e->Message,"提示");
			}				
		}
		//-----------------------更新数据库数据--------------------------
		void UpdateDataBase(DataSet* SpecificDataSet,String* tempTableName)
		{
			//如果没有指定数据表名称，则设置为"Table1"
			if(tempTableName == NULL)
			{
				tempTableName = new String("Table1");
			}
			try
			{
				sqlCmdBld = new SqlCommandBuilder(this->da);
				if(SpecificDataSet->HasChanges(DataRowState::Deleted))
					this->da->Update(SpecificDataSet->GetChanges(DataRowState::Deleted),tempTableName);
				if(SpecificDataSet->HasChanges(DataRowState::Modified))
					this->da->Update(SpecificDataSet->GetChanges(DataRowState::Modified),tempTableName);
				if(SpecificDataSet->HasChanges(DataRowState::Added))
					this->da->Update(SpecificDataSet->GetChanges(DataRowState::Added),tempTableName);
			}
			catch(Exception* e)
			{
				MessageBox::Show(e->Message,"提示");
				SpecificDataSet->RejectChanges();
				return;
			}			
			SpecificDataSet->Tables->Item[tempTableName]->AcceptChanges();
		}
		void UpdateDataBase(DataSet* SpecificDataSet)
		{
			String* tempTableName = new String("Table1");
			UpdateDataBase(SpecificDataSet,tempTableName);
		}
		void UpdateDataBase(SqlDataAdapter* dataAdapter,DataSet* SpecificDataSet,String* tempTableName)
		{
			//如果没有指定数据表名称，则设置为"Table1"
			if(tempTableName == NULL)
			{
				tempTableName = new String("Table1");
			}
			try
			{
				sqlCmdBld = new SqlCommandBuilder(dataAdapter);
				if(SpecificDataSet->HasChanges(DataRowState::Deleted))
					dataAdapter->Update(SpecificDataSet->GetChanges(DataRowState::Deleted),tempTableName);
				if(SpecificDataSet->HasChanges(DataRowState::Modified))
					dataAdapter->Update(SpecificDataSet->GetChanges(DataRowState::Modified),tempTableName);
				if(SpecificDataSet->HasChanges(DataRowState::Added))
					dataAdapter->Update(SpecificDataSet->GetChanges(DataRowState::Added),tempTableName);		

			}
			catch(Exception* e)
			{
				MessageBox::Show(e->Message,"提示");
				SpecificDataSet->RejectChanges();
				return;
			}		
			
			SpecificDataSet->Tables->Item[tempTableName]->AcceptChanges();
		}
		//-----------------------SQL语言操作-----------------------------
		int SQLOperate(String* tempStrSQL)
		{
			int r;
			SqlCommand* tempSqlCmd = new SqlCommand(tempStrSQL,this->myConnection);
			try
			{
				r = tempSqlCmd->ExecuteNonQuery();
			}
			catch(Exception* e)
			{
				MessageBox::Show(e->Message,"提示");
				r = 0;
			}
			return r;
		}
		SqlDataReader* SQLRead(String* tempStrSQL)
		{
			SqlCommand* tempSqlCmd = new SqlCommand(tempStrSQL,this->myConnection);
			return tempSqlCmd->ExecuteReader();
		}
	//------------------成员方法结束----------------------
	};

}