C#資料訪問類

訪問|資料 public class operateDB
{


private static string conStr;
public static OleDbConnection cnn;
OleDbDataAdapter da;
OleDbCommandBuilder cb;
OleDbCommand cmd;

//建構函式
#region initialize
public operateDB()
{
//
// TODO: 在此處添加建構函式邏輯
//
cnn=new OleDbConnection();

da=new OleDbDataAdapter();
//不用OleDbCommand對象更新到資料庫時，必須有下面一行
cb=new OleDbCommandBuilder(da);

cmd=new OleDbCommand();

}
#endregion initialize

//連接字串
#region get&setConnectionString

/// <summary>
/// 擷取連接字串
/// </summary>
public string MyConStr
{
get {return conStr;}
set {conStr = value;}
}

#endregion get&setConnectionString

//獲得表的名稱
#region acquireTableNames

/// <summary>
/// 擷取資料庫中的表名集合
/// </summary>
/// <returns></returns>
public DataTable tablesCollection()
{
DataTable tbl=new DataTable();
try
{

cnn.ConnectionString=conStr;
cnn.Open();

tbl = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});

}
catch(Exception ce)
{
Console.WriteLine("產生錯誤:\n{0}",ce.Message);
}
finally
{
cnn.Close();
}
return tbl;
}

#endregion acquireTableNames

//填充資料
#region fillTable

/// <summary>
/// 填充dataTable的查詢
/// </summary>
/// <param name="tblName">資料表(必須輸入資料庫中存在的名稱,也可以是視圖)</param>
/// <param name="sqlStr">SQL語句</param>
/// <returns>記錄條數</returns>
public int select(DataTable tblName,string sqlStr)
{
int i=0;

// try
// {
//
tblName.Clear();
da.Dispose();

if (cnn.ConnectionString=="")
cnn.ConnectionString=conStr;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
// OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);
cmd.Connection=cnn;
cmd.CommandType=CommandType.Text;
cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr;
da.SelectCommand=cmd;

i=da.Fill(tblName);
//
//
// }
// catch(Exception ce)
// {
// Console.WriteLine("產生錯誤:\n{0}",ce.Message);
// }
// finally
// {
//this.da.Dispose();
cnn.Close();

// }
return i;
}

#endregion fillTable

//插入記錄
#region insert(use CommandBuilder)
/// <summary>
/// 插入記錄(用OleDbCommandBuilder)
/// </summary>
/// <param name="tblName">資料表</param>
/// <param name="newRow">與表中欄位對應的新行</param>
/// <returns>影響的行數</returns>
public int insert(DataTable tblName,DataRow newRow)
{
cnn.Open();
int i=0;

//
// try
// {
//如何判斷OleDbDataAdapter是否已經Dispose

//下面如果不產生新的OleDbDataAdapter、OleDbCommandBuilder、OleDbCommand，
//而用原來的全域da,cb,cmd,則在一次操作中只能更新一張表
OleDbDataAdapter daIn=new OleDbDataAdapter();
OleDbCommandBuilder cbIn=new OleDbCommandBuilder(daIn);
OleDbCommand cmdIn=new OleDbCommand("select * from "+tblName.TableName,cnn);
daIn.SelectCommand=cmdIn;

// foreach (DataTable dt in da.TableMappings)
// {
// if (dt.TableName!=tblName.TableName)
// dt.Clear();
// }
tblName.Rows.Add(newRow);


i=daIn.Update(tblName);

//
// }
// catch(Exception ce)
// {
// Console.WriteLine("產生錯誤:\n{0}",ce.Message);
// }
// finally
// {
// cnn.Close();
// }
// cnn.Close();
return i;
}
#endregion insert(use CommandBuilder)

//插入記錄
#region insert(use InsideTransaction,DataTable[])

public string insert(DataTable[] tbls,DataRow[] newRows)
{
int[] num=new int[tbls.Length];
int sum=0;
bool judge=false;
string str="";

if (tbls.Length==newRows.Length)
{
cnn.Open();
OleDbTransaction tran=cnn.BeginTransaction();

for (int i=0;i<tbls.Length;i++)
{
// this.select(tbls[i],"1=1",tran);
da.InsertCommand=insertCmd(tbls[i],"操作編號");

tbls[i].Rows.Add(newRows[i]);

da.InsertCommand.Transaction=tran;
try
{
num[i]=da.Update(tbls[i]);
sum+=num[i];
}
catch
{
sum=-1;
}


if (num[i]==0)
judge=true;
}

if (judge)
{
tran.Rollback();
str="更新失敗";
sum=0;
}
else
{
tran.Commit();
str="更新成功";
}

}
cnn.Close();

return str+",影響了 "+sum.ToString()+" 條記錄";

}

#endregion insert(use InsideTransaction,DataTable[])

//插入記錄
#region insert(use OutsideTransaction)

/// <summary>
/// 填充DataTable(用於交易處理)
/// </summary>
/// <param name="tblName">表</param>
/// <param name="sqlStr">SQL語句</param>
/// <param name="trs">Transaction對象</param>
/// <returns>行數</returns>
public int select(DataTable tblName,string sqlStr,OleDbTransaction trs)
{
int i=0;

// try
// {
//
tblName.Clear();
da.Dispose();

if (cnn.ConnectionString=="")
cnn.ConnectionString=conStr;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
// OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);
cmd.Connection=cnn;
cmd.CommandType=CommandType.Text;
cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr;
da.SelectCommand=cmd;

cmd.Transaction=trs;
i=da.Fill(tblName);

return i;
}

/// <summary>
/// 插入記錄(用OleDbDataAdapter.Update方法及OleDbTransaction)
/// </summary>
/// <param name="tblName">資料表</param>
/// <param name="newRow">新行</param>
/// <param name="trs">事務對象</param>
/// <returns></returns>
public int insert(DataTable tblName,DataRow newRow,OleDbTransaction trs)
{

da.InsertCommand=insertCmd(tblName,"noo");

int num=0;

try
{
tblName.Rows.Add(newRow);

da.InsertCommand.Transaction=trs;

num=da.Update(tblName);
}
catch
{

}

return num;

}


#endregion insert(use OutsideTransaction)

//構造插入的Command
#region insertCommand
/// <summary>
/// 構造insertCommand
/// </summary>
/// <param name="dtl">資料表</param>
/// <param name="identityCol">identity列的名稱</param>
/// <returns></returns>
private static OleDbCommand insertCmd(DataTable dtl,string identityCol)
{
OleDbCommand inCmd=new OleDbCommand();
inCmd.Connection=cnn;

string sqlStr="";
string strValue="";

sqlStr = "INSERT " + dtl.TableName.ToString() + "(";
strValue = ") Values (";

for (int i=0;i<dtl.Columns.Count;i++)
{
//對於IDENTITY列無需賦值
if (dtl.Columns[i].ToString() != identityCol)
{
sqlStr += "[" + dtl.Columns[i].ToString() + "], ";
strValue +="?,";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = "@" + dtl.Columns[i].ToString();
myPara.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
// myPara.Direction = ParameterDirection.Input;
myPara.SourceColumn = dtl.Columns[i].ToString();
// myPara.SourceVersion = DataRowVersion.Current;

inCmd.Parameters.Add(myPara);
}

}

sqlStr=sqlStr.Substring(0,sqlStr.Length-2);
strValue=strValue.Substring(0,strValue.Length-1);

sqlStr += strValue + ")";


inCmd.CommandText = sqlStr;

return inCmd;

}

#endregion insertCommand

//修改
#region update
/// <summary>
/// 修改記錄
/// </summary>
/// <param name="tblName">資料表</param>
/// <param name="strUp">SQL語句</param>
/// <returns>影響的行數</returns>
public int update(DataTable tblName,string strUp)
{
cnn.Close();
return i;
}
#endregion update

//刪除
#region del(use CommandBuilder)
/// <summary>
/// 刪除記錄
/// </summary>
/// <param name="tblName">資料表</param>
/// <param name="strDel">SQL語句</param>
/// <returns>影響的行數</returns>
public int delete(DataTable tblName,string strDel) //strDel是刪除條件
{
int rows=0;

//用OleDbDataAdapter.Update方法自動更新必須在where中存在主鍵或唯一值
// try
// {
//
cnn.Open();
rows=tblName.Rows.Count;

for (int i=0;i< tblName.Rows.Count;i++)
{
tblName.Rows[i].Delete();
}

//注意，如在da.Update前面用了下面的AcceptChanges方法，因為記錄被刪除--更新到資料庫失敗
//tblName.AcceptChanges();
da.Update(tblName);
//

// }
// catch(Exception ce)
// {
// Console.WriteLine("產生錯誤:\n{0}",ce.Message);
// }
// finally
// {
cnn.Close();
// }
///
//用OleDbCommand直接更新
// try
// {
// string str="delete from "+tblName.TableName+" where "+strDel;
// cnn.Open();
// OleDbCommand cmdD=new OleDbCommand(str,cnn);
// cmdD.CommandType=CommandType.Text;
// rows=cmdD.ExecuteNonQuery();
// }
//
// catch(Exception ce)
// {
// Console.WriteLine("產生錯誤:\n{0}",ce.Message);
// }
// finally
// {
// cnn.Close();
// }
return rows;
}
#endregion del(use CommandBuilder)

//構造刪除的Command
#region delCommand(create OleDbDataAdapter.deleteCommand)

public int delete(DataTable tblName)
{
int rows=0;

da.DeleteCommand=delCmd(tblName);

for (int i=0;i< tblName.Rows.Count;i++)
{
tblName.Rows[i].Delete();
}

rows=da.Update(tblName);

return rows;
}


private static OleDbCommand delCmd(DataTable dtl)
{
OleDbCommand delCmd=new OleDbCommand();
delCmd.Connection=cnn;

string sqlStr="";

sqlStr = "delete from " + dtl.TableName.ToString() + " where ";

for (int i=0;i<dtl.Columns.Count;i++)
{
sqlStr += "([" + dtl.Columns[i].ToString() + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ToString()+"] IS NULL) AND";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = "or1_" + dtl.Columns[i].ToString();
myPara.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
myPara.Direction = ParameterDirection.Input;
myPara.SourceColumn = dtl.Columns[i].ToString();
myPara.SourceVersion = DataRowVersion.Original;

delCmd.Parameters.Add(myPara);

int j=delCmd.Parameters.Count;

bool b=dtl.Columns[i].AllowDBNull;
if (b)
{

OleDbParameter myPara1 = new OleDbParameter();
myPara1.ParameterName = "or2_" + dtl.Columns[i].ToString();
myPara1.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
myPara1.Direction = ParameterDirection.Input;
myPara1.SourceColumn = dtl.Columns[i].ToString();
myPara1.SourceVersion = DataRowVersion.Original;
delCmd.Parameters.Add(myPara1);
j=delCmd.Parameters.Count;
}


}
sqlStr=sqlStr.Substring(0,sqlStr.Length-3);

delCmd.CommandText = sqlStr;

return delCmd;

}

#endregion delCommand(create OleDbDataAdapter.deleteCommand)

#region amendDataBase
public void addColumn(DataTable tblName,string strUp) //修改表的結構,更新到資料庫
{
cnn.Open();

// OleDbCommand cmdS=new OleDbCommand("select * from "+tblName.TableName,cnn);
// da.SelectCommand=cmdS;
// OleDbCommandBuilder cb=new OleDbCommandBuilder(da);
// DataColumn colItem = new DataColumn(strUp,Type.GetType("System.String"));
//
// tblName.Columns.Add(colItem);

//為什麼上面的方法不行，只能直接用SQL語句嗎?

da.Fill(tblName);
da.Update(tblName);
}

#endregion amendDataBase

//調用預存程序
#region execProc(return dataTable)
/// <summary>
/// 執行預存程序
/// </summary>
/// <param name="procName">預存程序名字</param>
/// <param name="ParaValue">參數的值</param>
/// <param name="ParaName">參數名字</param>
/// <param name="ParaType">參數的類型</param>
/// <returns></returns>
public DataTable ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType)
{
OleDbCommand cmdp=new OleDbCommand();
cmdp.Connection=cnn;
cmdp.CommandType=CommandType.StoredProcedure;
cmdp.CommandText=procName;

for (int i=0;i<ParaName.Length;i++)
{
OleDbParameter pt=new OleDbParameter();

ParaName[i]="@"+ParaName[i];

//參數名字
//pt.ParameterName=ParaName[i];
pt.SourceColumn=ParaName[i];

pt.OleDbType=GetOleDbType(ParaType[i]);

pt.Value=ParaValue[i];

cmdp.Parameters.Add(pt);

}
DataTable dtl=new DataTable();
cnn.Open();

da.SelectCommand=cmdp;
da.Fill(dtl);
cnn.Close();
return dtl;

}

/// <summary>
/// 設定OleDbParameter對象的DbType（把字串變為相應的OleDbType類型）
/// </summary>
/// <param name="type">傳入參數的字串</param>
/// <returns></returns>
private static OleDbType GetOleDbType(string type)
{
// try
// {
// return (OleDbType)Enum.Parse(typeof(OleDbType), type, true);
// }
// catch
// {
// return OleDbType.Varchar;
// }

switch (type)
{
case "date":
return OleDbType.DBDate;
break;
case "num":
return OleDbType.Integer;
break;
default:
return OleDbType.VarChar;
}
}
#endregion execProc(return dataTable)
}