ADO.NET断开式操作

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace t5_断开式操作
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void BindData()
{
string connStr = "Data Source=(local);Initial Catalog=hem09;User ID=sa;Password=123456";
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from employee";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds);//本句才真正在执行sql语句,并将数据放到ds中
dataGridView1.DataSource = ds.Tables[0];

#region 通过SqlDataReader获取表的结构信息
//DataSet ds = new DataSet();
//SqlCommand cmd = new SqlCommand(sql, conn);
//conn.Open();
////创建列部分
//DataTable dt=new DataTable();
//SqlDataReader reader = cmd.ExecuteReader();
//for (int i = 0; i < reader.FieldCount; i++)
//{
// //Console.WriteLine(reader.GetName(i));
// DataColumn dc = new DataColumn(reader.GetName(i));
// dc.DataType = reader.GetFieldType(i);
// dt.Columns.Add(dc);
//}
////进行数据的读取存储
//while (reader.Read())
//{
// DataRow dr = dt.NewRow();
// for (int i = 0; i < reader.FieldCount; i++)
// {
// dr[i] = reader[i];
// }
// dt.Rows.Add(dr);
//}
//ds.Tables.Add(dt);
#endregion

}
}

private void Form1_Load(object sender, EventArgs e)
{
BindData();

}

private void updateToolStripMenuItem_Click(object sender, EventArgs e)
{//将数据更新回数据库
//获取源数据
DataTable dt = dataGridView1.DataSource as DataTable;
if (dt != null)
{
string connStr = "Data Source=(local);Initial Catalog=hem09;User ID=sa;Password=123456";
using (SqlConnection conn = new SqlConnection(connStr))
{
//构造适配器对象
SqlDataAdapter adapter = new SqlDataAdapter();
//构造修改语句
string sql = "update employee set ename=@name,ecode=@code where eid=@id";
//构造用于修改的命令对象
SqlCommand cmdUpdate = new SqlCommand(sql, conn);
cmdUpdate.Parameters.Add("@name", SqlDbType.NVarChar, 10, "ename");
cmdUpdate.Parameters.Add("@code", SqlDbType.VarChar, 18, "ecode");
cmdUpdate.Parameters.Add("@id", SqlDbType.Int, 4, "eid");
//构造适配器的修改命令属性
adapter.UpdateCommand = cmdUpdate;

conn.Open();
//完成数据更新,会逐条的对比数据
//情况1:dt中有的数据,而数据库中没有,则会调用InsertCommand执行
//情况2:dt中没有的数据,而数据库中有,则会调用DeleteCommand执行
//情况3:都有,但是不一样,则会调用UpdateCommand执行

adapter.Update(dt);
}
}
}

private void fillToolStripMenuItem_Click(object sender, EventArgs e)
{

}
}
}