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();
}
///
///
更新数据行
///
///
要更新的列集合
///
要更新的列集合的对应值
///
所属表名称
///
表主键
///
行所属的ID
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();
}
//
----------------------------------------------------------------------------------