此处将资料从数据库得出后显在DataGridView上显示。然后变更数据。变更数据的方法在按钮事件中。
资料显示方法不用多说。
更新时主要用到SqlCommandBuilder类和SqlDataAdapter.Update()方法。
SqlCommandBuilder对象负责生成用于更新数据库的SQL语句,不必自己创建这些语句。
UpDate方法自动遍历DataTable中的行,以找出需要对数据库作出变动。Rows集合中每个DataRow对象都具有属性RowState,可以跟踪此行是否已删除、添加、修改,还是未作变动。所作的任何变化都会反映到数据库中。
例:更新表中内容:
using
System.Data.SqlClient;
namespace UpdatingData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
btnUpdate.Click += new EventHandler(btnUpdate_Click);
UpdateData();
}
string sConnection = " Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123 " ;
DataSet dsSet = new DataSet();
SqlDataAdapter sdaAdapter = null ;
SqlCommandBuilder scbBuilder = null ;
private void UpdateData()
{
// 建立Connection
SqlConnection scConnection = new SqlConnection(sConnection);
// 建立Command
SqlCommand scCommand = scConnection.CreateCommand();
scCommand.CommandText = " select customerID,contactName from customers " ;
// 建立Adapter
sdaAdapter = new SqlDataAdapter(scCommand);
// 该对象负责生成用于更新数据库的SQL语句,不必自己创建这些语句
scbBuilder = new SqlCommandBuilder(sdaAdapter);
// 得到数据
sdaAdapter.Fill(dsSet, " customers " );
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
void btnUpdate_Click( object sender, EventArgs e)
{
// 设置值
dsSet.Tables[ " customers " ].Rows[ 3 ][ " contactName " ] = " Thomas Hardy " ;
// 更新数据(UpDate方法自动遍历DataTable中的行,以找出需要对数据库作出变动)
// Rows集合中每个DataRow对象都具有属性RowState,可以跟踪此行是否已删除、添加、修改,还是未作变动。所作的任何变化都会反映到数据库中。
sdaAdapter.Update(dsSet, " customers " );
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
}
}
namespace UpdatingData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
btnUpdate.Click += new EventHandler(btnUpdate_Click);
UpdateData();
}
string sConnection = " Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123 " ;
DataSet dsSet = new DataSet();
SqlDataAdapter sdaAdapter = null ;
SqlCommandBuilder scbBuilder = null ;
private void UpdateData()
{
// 建立Connection
SqlConnection scConnection = new SqlConnection(sConnection);
// 建立Command
SqlCommand scCommand = scConnection.CreateCommand();
scCommand.CommandText = " select customerID,contactName from customers " ;
// 建立Adapter
sdaAdapter = new SqlDataAdapter(scCommand);
// 该对象负责生成用于更新数据库的SQL语句,不必自己创建这些语句
scbBuilder = new SqlCommandBuilder(sdaAdapter);
// 得到数据
sdaAdapter.Fill(dsSet, " customers " );
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
void btnUpdate_Click( object sender, EventArgs e)
{
// 设置值
dsSet.Tables[ " customers " ].Rows[ 3 ][ " contactName " ] = " Thomas Hardy " ;
// 更新数据(UpDate方法自动遍历DataTable中的行,以找出需要对数据库作出变动)
// Rows集合中每个DataRow对象都具有属性RowState,可以跟踪此行是否已删除、添加、修改,还是未作变动。所作的任何变化都会反映到数据库中。
sdaAdapter.Update(dsSet, " customers " );
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
}
}
例:在表中增加行
using
System.Data.SqlClient;
namespace AddingData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
btnAdd.Click += new EventHandler(btnAdd_Click);
UpdateData();
}
string sConnection = " Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123 " ;
DataSet dsSet = new DataSet();
SqlDataAdapter sdaAdapter = null ;
SqlCommandBuilder scbBuilder = null ;
private void UpdateData()
{
// 建立Connection
SqlConnection scConnection = new SqlConnection(sConnection);
// 建立Command
SqlCommand scCommand = scConnection.CreateCommand();
scCommand.CommandText = " select customerID,companyName from customers " ;
// 建立Adapter
sdaAdapter = new SqlDataAdapter(scCommand);
// 该对象负责生成用于更新数据库的SQL语句,不必自己创建这些语句
scbBuilder = new SqlCommandBuilder(sdaAdapter);
// 得到数据
sdaAdapter.Fill(dsSet, " customers " );
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
void btnAdd_Click( object sender, EventArgs e)
{
AddRow();
}
private void AddRow()
{
// 新建表中的行
DataRow drRow = dsSet.Tables[ " customers " ].NewRow();
drRow[ " customerID " ] = " ZaCzi " ;
drRow[ " companyName " ] = " Zachary Zithers Ltd. " ;
// 增加行
dsSet.Tables[ " customers " ].Rows.Add(drRow);
// 更新表
sdaAdapter.Update(dsSet, " customers " );
// 显示
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
}
}
namespace AddingData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
btnAdd.Click += new EventHandler(btnAdd_Click);
UpdateData();
}
string sConnection = " Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123 " ;
DataSet dsSet = new DataSet();
SqlDataAdapter sdaAdapter = null ;
SqlCommandBuilder scbBuilder = null ;
private void UpdateData()
{
// 建立Connection
SqlConnection scConnection = new SqlConnection(sConnection);
// 建立Command
SqlCommand scCommand = scConnection.CreateCommand();
scCommand.CommandText = " select customerID,companyName from customers " ;
// 建立Adapter
sdaAdapter = new SqlDataAdapter(scCommand);
// 该对象负责生成用于更新数据库的SQL语句,不必自己创建这些语句
scbBuilder = new SqlCommandBuilder(sdaAdapter);
// 得到数据
sdaAdapter.Fill(dsSet, " customers " );
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
void btnAdd_Click( object sender, EventArgs e)
{
AddRow();
}
private void AddRow()
{
// 新建表中的行
DataRow drRow = dsSet.Tables[ " customers " ].NewRow();
drRow[ " customerID " ] = " ZaCzi " ;
drRow[ " companyName " ] = " Zachary Zithers Ltd. " ;
// 增加行
dsSet.Tables[ " customers " ].Rows.Add(drRow);
// 更新表
sdaAdapter.Update(dsSet, " customers " );
// 显示
dgvView.DataSource = dsSet.Tables[ " customers " ];
}
}
}