西夏普的部落格

1. 前言
在C#的程式設計中，如果要常常從資料庫擷取資料以及操作資料庫的新增、修改、刪除功能，需要撰寫一些冗長且重複的程式碼，因此將操作資料庫寫成簡單的方法，以下是此方法的程式。

2. 說明
以下舉出常用的資料庫包括MSSQL、MySQL與Oracle，首先加入命名空間。

using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;

MSSQL:

public static SqlConnection OpenSqlConn(string Server, string Database, string dbuid, string dbpwd)
{
	string cnstr = string.Format("server={0};database={1};uid={2};pwd={3};Connect Timeout = 180", Server, Database, dbuid, dbpwd);
	SqlConnection icn = new SqlConnection();
	icn.ConnectionString = cnstr;
	if (icn.State == ConnectionState.Open) icn.Close();
	icn.Open();
	return icn;
}

public static DataTable GetSqlDataTable(string Server, string Database, string dbuid, string dbpwd, string SqlString)
{
	DataTable myDataTable = new DataTable();
	SqlConnection icn = null;
	icn = OpenSqlConn(Server, Database, dbuid, dbpwd);
	SqlCommand isc = new SqlCommand();
	SqlDataAdapter da = new SqlDataAdapter(isc);
	isc.Connection = icn;
	isc.CommandText = SqlString;
	isc.CommandTimeout = 600;
	DataSet ds = new DataSet();
	ds.Clear();
	da.Fill(ds);
	myDataTable = ds.Tables[0];
	if (icn.State == ConnectionState.Open) icn.Close();
	return myDataTable;
}

public static void SqlInsertUpdateDelete(string Server, string Database, string dbuid, string dbpwd, string SqlSelectString)
{
	SqlConnection icn = OpenSqlConn(Server, Database, dbuid, dbpwd);
	SqlCommand cmd = new SqlCommand(SqlSelectString, icn);
	SqlTransaction mySqlTransaction = icn.BeginTransaction();
	try
	{
		cmd.Transaction = mySqlTransaction;
		cmd.ExecuteNonQuery();
		mySqlTransaction.Commit();
	}
	catch (Exception ex)
	{
		mySqlTransaction.Rollback();
		throw (ex);
	}
	if (icn.State == ConnectionState.Open) icn.Close();
}

 MySQL:

public static MySqlConnection MyOpenConn(string Server, string Database, string dbuid, string dbpwd)
{
	string cnstr = string.Format("server={0};database={1};uid={2};pwd={3};Connect Timeout = 180; CharSet=utf8", Server, Database, dbuid, dbpwd);
	MySqlConnection icn = new MySqlConnection();
	icn.ConnectionString = cnstr;
	if (icn.State == ConnectionState.Open) icn.Close();
	icn.Open();
	return icn;
}

public static DataTable GetMyDataTable(string Server, string Database, string dbuid, string dbpwd, string SqlString)
{
	DataTable myDataTable = new DataTable();
	MySqlConnection icn = null;
	icn = MyOpenConn(Server, Database, dbuid, dbpwd);
	MySqlCommand isc = new MySqlCommand();
	MySqlDataAdapter da = new MySqlDataAdapter(isc);
	isc.Connection = icn;
	isc.CommandText = SqlString;
	isc.CommandTimeout = 600;
	DataSet ds = new DataSet();
	ds.Clear();
	da.Fill(ds);
	myDataTable = ds.Tables[0];
	if (icn.State == ConnectionState.Open) icn.Close();
	return myDataTable;
}

public static void MySqlInsertUpdateDelete(string Server, string Database, string dbuid, string dbpwd, string SqlSelectString)
{
	MySqlConnection icn = MyOpenConn(Server, Database, dbuid, dbpwd);
	MySqlCommand cmd = new MySqlCommand(SqlSelectString, icn);
	MySqlTransaction mySqlTransaction = icn.BeginTransaction();
	try
	{
		cmd.Transaction = mySqlTransaction;
		cmd.ExecuteNonQuery();
		mySqlTransaction.Commit();
	}
	catch (Exception ex)
	{
		mySqlTransaction.Rollback();
		throw (ex);
	}
	if (icn.State == ConnectionState.Open) icn.Close();
}

Oracle:

public static OracleConnection OpenConn(string Server, string Database, string dbuid, string dbpwd)
{
	string cnstr = string.Format("server={0};Data Source={1};User ID={2};Password={3}", Server, Database, dbuid, dbpwd);
	OracleConnection icn = new OracleConnection();
	icn.ConnectionString = cnstr;
	if (icn.State == ConnectionState.Open) icn.Close();
	icn.Open();
	return icn;
}

public static DataTable GetDataTable(string Server, string Database, string dbuid, string dbpwd, string SqlString)
{
	DataTable myDataTable = new DataTable();
	OracleConnection icn = null;
	icn = OpenConn(Server, Database, dbuid, dbpwd);
	OracleCommand isc = new OracleCommand();
	OracleDataAdapter da = new OracleDataAdapter(isc);
	isc.Connection = icn;
	isc.CommandText = SqlString;
	isc.CommandTimeout = 600;
	DataSet ds = new DataSet();
	ds.Clear();
	da.Fill(ds);
	myDataTable = ds.Tables[0];
	if (icn.State == ConnectionState.Open) icn.Close();
	return myDataTable;
}

public static void OracleInsertUpdateDelete(string Server, string Database, string dbuid, string dbpwd, string SqlSelectString)
{
	OracleConnection icn = OpenConn(Server, Database, dbuid, dbpwd);
	OracleCommand cmd = new OracleCommand(SqlSelectString, icn);
	OracleTransaction mySqlTransaction = icn.BeginTransaction();
	try
	{
		cmd.Transaction = mySqlTransaction;
		cmd.ExecuteNonQuery();
		mySqlTransaction.Commit();
	}
	catch (Exception ex)
	{
		mySqlTransaction.Rollback();
		throw (ex);
	}
	if (icn.State == ConnectionState.Open) icn.Close();
}

 

3. 應用
這樣以後要取得DataTable只要寫下列程式碼(以Oracle為例)

string sql = "select * from TableName";
DataTable myDataTable = GetDataTable("ServerName","DataBaseName","UserName","PassWord",sql);