[转]C#操作Access数据库类

代码

// 网上很多都是操作SQL SER 的，整理了一下，不是很完善，但不影响使用，呵呵……
// private string datapatch = ConfigurationSettings.AppSettings["acessconn"]; // 数据库地址
private string datapatch = " db/global.asa " ; // 数据库地址
///
/// 取得dataset
///
/// 查询语句
///
public DataSet GetDataSet( string Commandtext)
{
string strConnection = " Provider=Microsoft.Jet.OLEDB.4.0; " +
" Data Source= " + Server.MapPath(datapatch);
string strCommandText = Commandtext;
OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnection.Open();
OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);
OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);
DataSet myDataset = new DataSet();
myAdpater.Fill(myDataset);
myConnection.Close();
return myDataset;
}
///
/// 取得表
///
/// 查询语句
///
public DataTable GetDataTable( string Commandtext)
{
string strConnection = " Provider=Microsoft.Jet.OLEDB.4.0; " +
" Data Source= " + Server.MapPath(datapatch);
string strCommandText = Commandtext;
OleDbConnection myConnection = new OleDbConnection(strConnection);
myConnection.Open();
OleDbDataAdapter myAdpater = new OleDbDataAdapter(strCommandText,myConnection);
OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdpater);
DataSet myDataset = new DataSet();
myAdpater.Fill(myDataset);
DataTable mytable = myDataset.Tables[ 0 ];
myConnection.Close();
return mytable;
}
///
/// 取得某行的某列的值
///
/// 列的名称
/// 所属表名
/// 表的主键
/// 列所属的主键值
///
public string GetDataColum( string strColumnName, string strTableName, string strColumnkey, string strColumnValue)
{
string strConnection = " Provider=Microsoft.Jet.OLEDB.4.0; " +
" Data Source= " + Server.MapPath(datapatch);
string strCommandText = " select " + strColumnName + " , " + 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);
myConnection.Close();
DataTable mytable = myDataset.Tables[ 0 ];
DataRow mydr = mytable.Rows.Find(strColumnValue);
string mydc = mydr[strColumnName].ToString();
return mydc;
}
///
/// 更新某个字段
///
/// 要更新字段名称
/// 要更新的值
/// 所属表名称
/// 表中KEY
/// 表中KEY的值
public void UpdateColum( string strColumnName, string strValue, string strTableName, string strColumnKey, string strColumnValue)
{
string strConnection = " Provider=Microsoft.Jet.OLEDB.4.0; " +
" Data Source= " + Server.MapPath(datapatch);
string strCommandText = " select " + strColumnKey + " , " + strColumnName + " 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 drFindRow = myDataset.Tables[strTableName].Rows.Find(strColumnValue);
drFindRow[strColumnName] = strValue;
myAdpater.Update(myDataset,strTableName);
myConnection.Close();
}
///
/// 添加行
///
/// 此行中的字段集合
/// 此行中的字段集合的对应值
/// 所属表名称
/// 表中主键
public void AddRow( string []columns, string []columnvalue, string strTableName, string strColumnKey)
{
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.Fill(myDataset,strTableName);
DataRow newrow = myDataset.Tables[strTableName].NewRow();
for ( int i = 0 ;i < columns.Length;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 < columns.Length;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();
} // ----------------------------------------------------------------------------------

 

转载于:https://www.cnblogs.com/chinaHunk/articles/1890515.html