//网上很多都是操作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(); }
//
----------------------------------------------------------------------------------