// Assumes that connection is a valid OleDbConnection object. OleDbDataAdapter adapter = new OleDbDataAdapter( "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID", connection); adapter.InsertCommand = new OleDbCommand( "INSERT INTO Categories (CategoryName) Values(?)", connection); adapter.InsertCommand.CommandType = CommandType.Text; adapter.InsertCommand.Parameters.Add( _ "@CategoryName", OleDbType.Char, 15, "CategoryName"); connection.Open(); // Fill the DataSet. DataSet categories = new DataSet(); adapter.Fill(categories, "Categories"); // Add a new row. DataRow newRow = categories.Tables["Categories"].NewRow(); newRow["CategoryName"] = "New Category"; categories.Tables["Categories"].Rows.Add(newRow); // Include an event to fill in the Autonumber value. adapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated); // Update the DataSet. adapter.Update(categories, "Categories"); connection.Close(); // Event procedure for OnRowUpdated protected static void OnRowUpdated( object sender, OleDbRowUpdatedEventArgs args) { // Include a variable and a command to retrieve the identity value from the Access database. int newID = 0; OleDbCommand idCMD = new OleDbCommand( "SELECT @@IDENTITY", connection); if (args.StatementType == StatementType.Insert) { // Retrieve the identity value and store it in the CategoryID column. newID = (int)idCMD.ExecuteScalar(); args.Row["CategoryID"] = newID; } }
实现例子一:
public static int Update()
{
int iRet = 0;
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); //定义OleDbDataAdapte对象
System.Data.DataSet dataSet = new System.Data.DataSet(); //定义DataSet
try
{
m_command.CommandText = "select * from " + m_ziTable;
dataAdapter.SelectCommand = m_command;
// 该语句用于将数据库内各表的一些关系及表内的主键值传给DataTable不执行该语句可能会有类似于找不到主键之类的报错
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dataAdapter.Fill(dataSet, m_ziTable);
OleDbCommandBuilder commandBuild = new OleDbCommandBuilder(dataAdapter);
// 取得某行的某列的值,并进行修改
// DataRow myDataRow= myDataSet.Tables["TableName"].Rows.Find("primary key data");
// 就是在某张表的主键里查找某一个特定值,返回的是该行的一行数据
DataRow myDataRow = dataSet.Tables[m_ziTable].Rows.Find("我");
string mydc = myDataRow["zi"].ToString(); // 获取值
myDataRow["mingPinLv"] = 50; // 修改值
// 添加行
DataRow r = dataSet.Tables[m_ziTable].NewRow();
r["ID"] = 17033;
r["zi"] = "筗";
dataSet.Tables[m_ziTable].Rows.Add(r);
// 删除行
foreach (DataRow dr in dataSet.Tables[m_ziTable].Rows)
{
if (dr["zi"].ToString().Trim().Equals("?"))
{
dr.Delete(); //删除DataSet 中的行
}
}
// 更新数据行
dataAdapter.Update(dataSet, m_ziTable);
}
catch (OleDbException e)
{
Console.WriteLine("Error: {0}", e.Errors[0].Message);
}
m_reader.Close();
return iRet;
}
用下边语句可以将数据库表显示出来:
dataGridView1.DataSource = RM_db.dataSet.Tables["zi"].DefaultView;
dataGridView1.Update();
=============================================================
(access数据库的 "会计科目 "表,有字段有记录,没有设置主键!)
(定义后两个按钮控件、一个打开文件控件、一个dataGridView控件!)
运行结果:没有设置任何主键信息!
望高手援助指点,修改!
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private DataTable DT = new DataTable();
private OdbcDataAdapter SDA = new OdbcDataAdapter();
//“打开”按钮打开数据库文件并把表传给dataGridView!
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
OdbcConnection conn = new OdbcConnection( "Driver={Microsoft Access Driver (*.mdb)};Dbq= " + openFileDialog1.FileName);
OdbcCommand SCD = new OdbcCommand( "select * from 会计科目 ", conn);
SDA.SelectCommand = SCD;
OdbcCommandBuilder SCB = new OdbcCommandBuilder(SDA);
DataSet myset = new DataSet();
SDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
SDA.Fill(myset);
DT = myset.Tables[0];
dataGridView1.DataSource = DT;
}
//“更新”按钮用来更新在dataGridView对数据的任意修改! 这个方法很好
private void button2_Click(object sender, EventArgs e)
{
try
{
OdbcCommandBuilder SCB = new OdbcCommandBuilder(SDA);
SDA.Update(DT);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show( "更新成功! ");
}
}
}
=========================================================================================
ASP.net:通过DataTable获得表的主键
很多情形下我们需要知道表的主键是什么。在ADO.Net中提供了DataTable可以映射数据库的表。于是便可以利用DataTable的属性PrimaryKey,它是DataColumn[] 类型是一个数组。我们可以使用如下的代码
DataColumn[] cols;
cols = Table.PrimaryKey;
//注意不是cols是DataColumn数组,不是DataColumn变量。这样做主要是为了处理联合主键的问题。
for(int i = 0; i < cols.Length; i++)
{
MessageBox.Show(cols[i].ColumnName);
}
按理这个问题就已经解决了,但是cols.Length却是0。原来在默认的情况下填充DataTable时并没有从数据库中取的主键的信息。如何获得主键呢?经过研究发现在填充Dataset的时候可以使用DataAdapter的MissingSchemaAction属性帮助我们解决这个问题,于是有如下的代码:
//使用DataAdapter填充DataTable
dataadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dataadapter.Fill(Table);
DataColumn[] cols;
cols = Table.PrimaryKey;
//注意不是cols是DataColumn数组,不是DataColumn变量。这样做主要是为了处理联合主键的问题。
for(int i = 0; i < cols.Length; i++)
{
MessageBox.Show(cols[i].ColumnName);
}
这样我们便可以如愿以偿了。MissingSchemaAction属性是确定现有Dataset(或DataTable)架构与传入数据不匹配时需要执行的操作。MissingSchemaAction.AddWithKey是枚举值,它的作用是添加必需的列和主键信息以完成架构,利用它用户可以在每个 DataTable上显式设置主键约束。
==================================================================================
下面我们来看看如何利用DataSet和DataAdapter来操作数据库
MyConnection.Open(); //打开数据库
MyCommand.Connection = MyConnection; //设置Command
MyCommand. CommandText = “select * from [admin]”; //设置Command
OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter(); //定义OleDbDataAdapte对象
MyDataAdapter.SelectCommand = MyCommand; //设置OleDbDataAdapte对象的SelectCommand属性
System.Data.DataSet MyDataSet = new System.Data.DataSet(); //定义DataSet
MyDataAdapter.Fill(MyDataSet,"admin"); //通过OleDbDataAdapte对象的SelectCommand属性填充MyDataSet
MyConnection.Close(); //关闭数据库
整个过程分以下几步:
1.建立数据库连接
2.建立OleDbDataAdapter对象!
3.实例化OleDbDataAdapter对象!
4.建立一个DataSet对象,执行SQL语句得到的表添加到其中
5.关闭数据库连接
通过上面的步骤我们就可以使用DataBind将DataSet中的数据绑定到特定的控件上了!
我们利用DataSet和DataAdapter能够更加方便的对数据库进行操作,如何通过OleDbDataAdapter来执行数据库的操作?我们只需要对DataSet中的数据进行增加、删除、修改等操作,然后在将DataSet提交给数据库即可。
//利用利用DataSet和DataAdapter操作数据库
public Boolean DoDB()
{
MyConnection.Open(); //打开数据库
MyCommand.Connection = MyConnection;
MyCommand. CommandText = “select * from admin”;
OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter();
MyDataAdapter.SelectCommand = MyCommand;
System.Data.DataSet MyDataSet = new System.Data.DataSet();
MyDataAdapter.Fill(MyDataSet,"admin"); //通过OleDbDataAdapte对象的SelectCommand属性填充MyDataSet
OleDbCommandBuilder MyCommandBuild = new OleDbCommandBuilder(MyDataAdapter);//关联DataSet和数据库的操作的,必不可少
foreach(DataRow dr in MyDataSet.Tables["Admin"].Rows)
{
if(dr["Admin_Code"].ToString().Trim().Equals("a"))
{
dr.Delete(); //删除DataSet 中的行
}
}
MyDataSet.Tables["Admin"].Rows[0][0] = "ss";//更新DataSet中第一行第一列的值
string [] dd = new String[3]{"a","b","v"};
MyDataSet.Tables["Admin"].Rows.Add(dd);//增加一行
MyDataAdapter.Update(MyDataSet,"Admin");//将DataSet中”Admin”表中的数据提交给数据库,完成数据库的更新
MyConnection.Close();//关闭数据库
}
这个程序同Command的delete、insert、update例程是执行同样的功能的,我这里改成了用MyDataAdapter来达到同样的效果!
要通过MyDataAdapter执行对数据库的操作,要有下面的几步:
1. 建立数据库连接MyConnection
2. 实例化OleDbDataAdapter对象!
3. 建立一个DataSet对象,并把执行select语句得到的记录添加到其中
4. 建立OleDbCommandBuilder对象! 并让它与我们前面的OleDbDataAdapter对象关联!语句如下:OleDbCommandBuilder MyCommandBuild = new OleDbCommandBuilder(MyDataAdapter);
5. 对DataSet中包含表的特定记录进行增加、删除、修改
6. 执行OleDbDataAdapter对象的Update命令更新数据库,语句如下: MyDataAdapter.Update(ds,"notes");
7. 关闭数据库连接
总结:
DataSet是ADO.NET中非常重要的内容,也是ADO.NET和ADO的区别的一个重要表现,特别适合成批的数据操作,也是数据棒定的重要来源。OleDbDataAdapter是DataSet和数据源之间建立联系的一个桥梁,要熟练的使用DataSet我们需要熟练的掌握OleDbDataAdapter。
=============================================================================
///
/// 添加行
///
/// 此行中的字段集合
/// 此行中的字段集合的对应值
/// 所属表名称
/// 表中主键
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
{
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
{
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();
}
//网上很多都是操作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();
}