Access数据库链接

Ｃ# Access数据库连接 2009-07-21 20:08 １.连接方法 web.config

程序中的数据访问类中把\"SQLConnString\"和\"dbPath\"取出来连接成一个字符串\"CONN_STRING_NON_DTC\"

public static readonly string CONN_STRING_NON_DTC =

System.Configuration.ConfigurationManager.AppSettings[\"SQLConnString\"].ToString() +

System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[\"dbPath\"]) + \";\"; 这是VS2005中的写法

把CONN_STRING_NON_DTC定义成static readonly是为了使用方便 连接类－－取得dataset：

public static string connstring =

\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" +

System.Web.HttpContext.Current.Server.MapPath(\"~/App_Data/webhww.mdb\");

public static DataSet GreatDs(string sql) {

OleDbDataAdapter Dar = new OleDbDataAdapter(sql, connstring); DataSet ds = new DataSet(); Dar.Fill(ds); return ds; }

public static void DoSql(string sql) {

OleDbConnection conn = new OleDbConnection();//创建连接对象 conn.ConnectionString = connstring;//给连接字符串赋值 conn.Open();//打开数据库

OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery();// conn.Close();//关闭数据库

} ２.

///

/// 取得表 ///

/// 查询语句 ///

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+\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+\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+\}

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 {

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+\}

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