ASP.NET的ACCESS數據庫操作類

　　//網上很多都是操作SQL SER 的整理了一下不是很完善但不影響使用呵呵……
//private string datapatch = ConfigurationSettingsAppSettings[acessconn];//數據庫地址

private string datapatch = db/globalasa;//數據庫地址
///
/// 取得dataset
//
/// 查詢語句
///
public DataSet GetDataSet(string Commandtext)
{

string strConnection = Provider=MicrosoftJetOLEDB; +
Data Source= + ServerMapPath(datapatch);
string strCommandText = Commandtext;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnectionOpen();

OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandTextmyConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();

myAdpaterFill(myDataset);

myConnectionClose();
return myDataset;
}
///
/// 取得表
///
/// 查詢語句
///
public DataTable GetDataTable(string Commandtext)
{

string strConnection = Provider=MicrosoftJetOLEDB; +
Data Source= + ServerMapPath(datapatch);
string strCommandText =Commandtext;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnectionOpen();

OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandTextmyConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();

myAdpaterFill(myDataset);

DataTable mytable = myDatasetTables[];

myConnectionClose();
return mytable;
}
///
/// 取得某行的某列的值
///
/// 列的名稱
/// 所屬表名
/// 表的主鍵
/// 列所屬的主鍵值
///
public string GetDataColum(string strColumnNamestring strTableNamestring strColumnkeystring strColumnValue)
{

string strConnection = Provider=MicrosoftJetOLEDB; +
Data Source= + ServerMapPath(datapatch);
string strCommandText = select +strColumnName++strColumnkey+ from +strTableName;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnectionOpen();

　　OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandTextmyConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();

myAdpaterMissingSchemaAction = MissingSchemaActionAddWithKey;

myAdpaterFill(myDataset);

myConnectionClose();

DataTable mytable = myDatasetTables[];
DataRow mydr = mytableRowsFind(strColumnValue);
string mydc = mydr[strColumnName]ToString();


return mydc;
}


///
/// 更新某個字段
///
/// 要更新字段名稱
/// 要更新的值
/// 所屬表名稱
/// 表中KEY
/// 表中KEY的值
public void UpdateColum(string strColumnNamestring strValuestring strTableNamestring strColumnKeystring strColumnValue)
{

string strConnection = Provider=MicrosoftJetOLEDB; +
Data Source= + ServerMapPath(datapatch);
string strCommandText = select +strColumnKey++strColumnName+ from +strTableName;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnectionOpen();

OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandTextmyConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();


myAdpaterMissingSchemaAction = MissingSchemaActionAddWithKey;

myAdpaterFill(myDatasetstrTableName);

DataRow drFindRow = myDatasetTables[strTableName]RowsFind(strColumnValue);

drFindRow[strColumnName] = strValue;

　　myAdpaterUpdate(myDatasetstrTableName);

myConnectionClose();

}

　　///
/// 添加行
///
/// 此行中的字段集合
/// 此行中的字段集合的對應值
/// 所屬表名稱
/// 表中主鍵

public void AddRow(string[]columnsstring[]columnvaluestring strTableNamestring strColumnKey)
{

string strConnection = Provider=MicrosoftJetOLEDB; +
Data Source= + ServerMapPath(datapatch);

string strColumnCount = ;
foreach(string thiscolunmname in columns)
{
strColumnCount = thiscolunmname++strColumnCount;
}

string strCommandText = select +strColumnCount+strColumnKey+ from +strTableName;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnectionOpen();

OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandTextmyConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();


myAdpaterFill(myDatasetstrTableName);

DataRow newrow = myDatasetTables[strTableName]NewRow();

for(int i=;i {
string strColumnName = columns[i].ToString();

newrow[strColumnName] = columnvalue[i].ToString();
}


myDataset.Tables[strTableName].Rows.Add(newrow);

myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}
///
/// 更新數據行
///
/// 要更新的列集合
/// 要更新的列集合的對應值
/// 所屬表名稱
/// 表主鍵
/// 行所屬的ＩＤ
public void updateRow(string[]columns,string[]columnvalue,string strTableName,string strColumnKey,string strColumnValue)
{

string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + Server.MapPath(datapatch);

string strColumnCount = "";
foreach(string thiscolunmname in columns)
{
strColumnCount = thiscolunmname+","+strColumnCount;
}

　　string strCommandText = "select "+strColumnCount+strColumnKey+" from "+strTableName;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnection.Open();

OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();

myAdpater.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myAdpater.Fill(myDataset,strTableName);

DataRow updaterow = myDataset.Tables[strTableName].Rows.Find(strColumnValue);

for(int i=0;i {
string strColumnName = columns[i].ToString();

updaterow[strColumnName] = columnvalue[i].ToString();
}


myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}

　
///
/// 刪除行
///
/// 所屬表
/// 主鍵字段名
/// 此行主鍵值
public void delectRow(string strTableName,string strColumnKey,string strColumnValue)
{

string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + Server.MapPath(datapatch);


string strCommandText = "select "+strColumnKey+" from "+strTableName;

OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnection.Open();

OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);

DataSet myDataset = new DataSet();

myAdpater.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myAdpater.Fill(myDataset,strTableName);

DataRow newrow = myDataset.Tables[strTableName].Rows.Find(strColumnValue);

newrow.Delete();

myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}

From:http://tw.wingwit.com/Article/program/net/201311/12662.html