一開始寫資料庫連結的時候，每一次的增刪修查都都要寫一長串的程式，修改起來非常的麻煩，決定寫一個ClassLibrary來管理我的SQL指令。
1.新增一個專案為ClassLibrary
2.原本的專案參考此ClassLibrary。
3.新增一個ClassSqlStatement.cs在ClassLibrary內。
4.輸入程式碼
namespace ClassLibrary
{
public class ClassSqlStatement
{
private static string DATABASE_SQL_PRODSER = //SQL Server資訊
private static string DATABASE_SQL_CHIS02 = //SQL Server資訊
public static string SqlProdser { get { return DATABASE_SQL_PRODSER; } }
public static string SqlChis02 { get { return DATABASE_SQL_CHIS02; } }
public static void ReadDataTable(DataTable dt)
{
if (dt == null)
{
Debug.WriteLine("dt is Null");
return;
}
foreach (DataRow dr in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
Debug.Write(dr[dc] + " ");
}
Debug.WriteLine("");
}
}
public static DataTable SqlSearch(string comstr, string SqlServer)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(SqlServer))
{
try
{
SqlCommand com = new SqlCommand(comstr);
con.Open();
com.Connection = con;
if (com.ExecuteScalar() == null || com.ExecuteScalar() is DBNull)
{
return null;
}
SqlDataAdapter adapter = new SqlDataAdapter(com);
adapter.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return dt;
}
public static DataTable SqlSearch(string comstr, string SqlServer, SqlParameter[] SqlParam)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(SqlServer))
{
try
{
con.Open();
SqlCommand com = new SqlCommand(comstr)
{
Connection = con
};
foreach (SqlParameter sp in SqlParam)
{
com.Parameters.AddWithValue(sp.ParameterName, sp.Value);
}
if (com.ExecuteScalar() == null || com.ExecuteScalar() is DBNull)
{
return null;
}
SqlDataAdapter adapter = new SqlDataAdapter(com);
adapter.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return dt;
}
public static void SqlTrasaction(string comstr, string SqlServer, SqlParameter[] SqlParam)
{
using (SqlConnection con = new SqlConnection(SqlServer))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand com = new SqlCommand(comstr)
{
Connection = con,
Transaction = tran
};
try
{
foreach (SqlParameter sp in SqlParam)
{
com.Parameters.AddWithValue(sp.ParameterName, sp.Value);
}
com.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
tran.Rollback();
}
}
}
}
}
這樣的話，以後要使用只要ClassSqlStatement.SqlSearch()就可以呼叫方法囉～。