using System;
using System.Data;
using System.Data.SqlClient;

namespace 進(jìn)銷存管理系統(tǒng)
{
	/// <summary>
	/// LinkDataBase 的摘要說明。
	/// </summary>
	public class LinkDataBase
	{
		private string strSQL;
		//與SQL Server的連接字符串設(shè)置
		private string connectionString = "workstation id=localhost;Integrated Security=SSPI;database=jxcbook";
        //與數(shù)據(jù)庫的連接
		private SqlConnection myConnection;
		
		private SqlCommandBuilder sqlCmdBld;
		private DataSet ds = new DataSet();
		private SqlDataAdapter da;

		public LinkDataBase()
		{
			//
			// TODO: 在此處添加構(gòu)造函數(shù)邏輯
			//
		}

/////////////////////////////////  操作脫機(jī)數(shù)據(jù)庫(創(chuàng)建了該類的實(shí)例時(shí)直接用)  /////////////////////////////////////////////////////
		
		//根據(jù)輸入的SQL語句檢索數(shù)據(jù)庫數(shù)據(jù)
		public DataSet SelectDataBase(string tempStrSQL,string tempTableName)
		{ 
			this.strSQL = tempStrSQL;
			this.myConnection = new SqlConnection(connectionString);
			this.da = new SqlDataAdapter(this.strSQL,this.myConnection);
			this.ds.Clear();
			this.da.Fill(ds,tempTableName);
			return ds;//返回填充了數(shù)據(jù)的DataSet，其中數(shù)據(jù)表以tempTableName給出的字符串命名
		}

		//數(shù)據(jù)庫數(shù)據(jù)更新(傳DataSet和DataTable的對(duì)象)
		public DataSet UpdateDataBase(DataSet changedDataSet,string tableName)
		{
			this.myConnection = new SqlConnection(connectionString);
			this.da = new SqlDataAdapter(this.strSQL,this.myConnection);
			this.sqlCmdBld = new SqlCommandBuilder(da);
			this.da.Update(changedDataSet,tableName);
			return changedDataSet;//返回更新了的數(shù)據(jù)庫表
		}

/////////////////////////////////  直接操作數(shù)據(jù)庫(未創(chuàng)建該類的實(shí)例時(shí)直接用)  /////////////////////////////////////////////////////

		//檢索數(shù)據(jù)庫數(shù)據(jù)(傳字符串,直接操作數(shù)據(jù)庫)
		public DataTable SelectDataBase(string tempStrSQL)
		{
			this.myConnection = new SqlConnection(connectionString);
			DataSet tempDataSet = new DataSet();
			this.da = new SqlDataAdapter(tempStrSQL,this.myConnection);
			this.da.Fill(tempDataSet);
			return tempDataSet.Tables[0];
		}

		//數(shù)據(jù)庫數(shù)據(jù)更新(傳字符串，直接操作數(shù)據(jù)庫)
		public int UpdateDataBase(string tempStrSQL)
		{
			this.myConnection = new SqlConnection(connectionString);
			//使用Command之前一定要先打開連接,后關(guān)閉連接,而DataAdapter則會(huì)自動(dòng)打開關(guān)閉連接
			myConnection.Open();
			SqlCommand tempSqlCommand = new SqlCommand(tempStrSQL,this.myConnection);
			int intNumber = tempSqlCommand.ExecuteNonQuery();//返回?cái)?shù)據(jù)庫中影響的行數(shù)
			myConnection.Close();
			return intNumber;
		}
	}
}