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();

}