ASP.NET[C#]的ACCESS数据库操作类

ASP.NET[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(); } // ----------------------------------------------------------------------------------