ASP.NET操作Access数据库

//网上很多都是操作SQLSER的，整理了一下，不是很完善，但不影响使用，呵呵……

//privatestringdatapatch=ConfigurationSettings.AppSettings["acessconn"];//数据库地址

privatestringdatapatch="db/global.asa";//数据库地址

///

///取得dataset

//

///查询语句

///

publicDataSetGetDataSet(stringCommandtext)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrCommandText=Commandtext;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.Fill(myDataset);

myConnection.Close();

returnmyDataset;

}

///

///取得表

///

///查询语句

///

publicDataTableGetDataTable(stringCommandtext)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrCommandText=Commandtext;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.Fill(myDataset);

DataTablemytable=myDataset.Tables[0];

myConnection.Close();

returnmytable;

}

///

///取得某行的某列的值

///

///列的名称

///所属表名

///表的主键

///列所属的主键值

///

publicstringGetDataColum(stringstrColumnName,stringstrTableName,stringstrColumnkey,stringstrColumnValue)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrCommandText="select"+strColumnName+","+strColumnkey+"from"+strTableName;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.MissingSchemaAction=MissingSchemaAction.AddWithKey;

myAdpater.Fill(myDataset);

myConnection.Close();

DataTablemytable=myDataset.Tables[0];

DataRowmydr=mytable.Rows.Find(strColumnValue);

stringmydc=mydr[strColumnName].ToString();

returnmydc;

}

///

///更新某个字段

///

///要更新字段名称

///要更新的值

///所属表名称

///表中KEY

///表中KEY的值

publicvoidUpdateColum(stringstrColumnName,stringstrValue,stringstrTableName,stringstrColumnKey,stringstrColumnValue)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrCommandText="select"+strColumnKey+","+strColumnName+"from"+strTableName;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.MissingSchemaAction=MissingSchemaAction.AddWithKey;

myAdpater.Fill(myDataset,strTableName);

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

drFindRow[strColumnName]=strValue;

myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}

///

///添加行

///

///此行中的字段集合

///此行中的字段集合的对应值

///所属表名称

///表中主键

publicvoidAddRow(string[]columns,string[]columnvalue,stringstrTableName,stringstrColumnKey)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrColumnCount="";

foreach(stringthiscolunmnameincolumns)

{

strColumnCount=thiscolunmname+","+strColumnCount;

}

stringstrCommandText="select"+strColumnCount+strColumnKey+"from"+strTableName;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.Fill(myDataset,strTableName);

DataRownewrow=myDataset.Tables[strTableName].NewRow();

for(inti=0;i{

stringstrColumnName=columns[i].ToString();

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

}

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

myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}

///

///更新数据行

///

///要更新的列集合

///要更新的列集合的对应值

///所属表名称

///表主键

///行所属的ＩＤ

publicvoidupdateRow(string[]columns,string[]columnvalue,stringstrTableName,stringstrColumnKey,stringstrColumnValue)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrColumnCount="";

foreach(stringthiscolunmnameincolumns)

{

strColumnCount=thiscolunmname+","+strColumnCount;

}

stringstrCommandText="select"+strColumnCount+strColumnKey+"from"+strTableName;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.MissingSchemaAction=MissingSchemaAction.AddWithKey;

myAdpater.Fill(myDataset,strTableName);

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

for(inti=0;i{

stringstrColumnName=columns[i].ToString();

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

}

myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}

///

///删除行

///

///所属表

///主键字段名

///此行主键值

publicvoiddelectRow(stringstrTableName,stringstrColumnKey,stringstrColumnValue)

{

stringstrConnection="Provider=Microsoft.Jet.OLEDB.4.0;"+

"DataSource="+Server.MapPath(datapatch);

stringstrCommandText="select"+strColumnKey+"from"+strTableName;

OleDbConnectionmyConnection=newOleDbConnection(strConnection);

myConnection.Open();

OleDbDataAdaptermyAdpater=newOleDbDataAdapter(strCommandText,myConnection);

OleDbCommandBuildermyBuilder=newOleDbCommandBuilder(myAdpater);

DataSetmyDataset=newDataSet();

myAdpater.MissingSchemaAction=MissingSchemaAction.AddWithKey;

myAdpater.Fill(myDataset,strTableName);

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

newrow.Delete();

myAdpater.Update(myDataset,strTableName);

myConnection.Close();

}