SqlConnection conn = new SqlConnection("server=(local);database=northwind;uid=sa;pwd=sasasa");
SqlDataAdapter adapter = new SqlDataAdapter("select * from Customers", conn);
Console.WriteLine("填充前:"+conn.State.ToString());
DataSet ds = new DataSet();
adapter.Fill(ds, "s");
Console.WriteLine("填充后:" + conn.State.ToString());
foreach (DataRow dr in ds.Tables["s"].Rows)
{
Console.WriteLine(dr["CustomerID"] + "\t" + dr["CompanyName"]);
}
这个程序里面,SqlConnection并没有显式的打开或关闭,因为SqlDataAdapter自动完成了这个工作,数据授权对象会在需要的时候打开连接,在完成工作后关闭它,注意:SqlDataAdapter延续SqlConnection的状态,即:原来SqlConnection是打开的,则执行完后,仍是打开的,SqlConnection原来是关闭的,则执行完成后仍然是关闭的。上例中,输出的 SqlConnection的State均是Closed。
--------------------------------------------------------------------------------------------------------------
修改
SqlConnection conn = new SqlConnection("server=(local);database=northwind;uid=sa;pwd=sasasa");
SqlDataAdapter adapter = new SqlDataAdapter("select Companyname from customers", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds,"cus");
Console.WriteLine(ds.Tables["cus"].Rows[9]["Companyname"]);
ds.Tables["cus"].Rows[9]["Companyname"] = "aaaaaaaaaaaaaaaaaaaaa";//在这句执行以后,直接运行Console.WriteLine的话,发现已经增加了一行了,但是真正的查询数据库的时候发现并未增加,原因是:ds中的数据表现的是内存中的数据,而内存中的数据是对应数据库中的表的,任何对ds的修改都只是对内存数据进行修改,必须使用adapter.Update(ds, "cus"),此句将内存数据更新至磁盘上数据库中的表
adapter.Update(ds, "cus");//非常重要,必不可少
Console.WriteLine(ds.Tables["cus"].Rows[9]["Companyname"]);
有了SqlCommandBuilder,将SqlDataAdapter传递之后,自动生成SQL命令,并将其与传递进来的DataAdapter关联。
注意:此时在内存中的DataSet只有Companyname一列,此时如果想要进行如下操作,则会报错,ds.Tables["cus"].Rows[9]["CustomerID"]="sfdsa",原因是内存中的表没有CustomerID这一列,如果要进行此项修改,必须修改前面的Select语句:select Companyname,CustomerID from customers或者select * from customers,但是后面一种占用较大内存。
----------------------------------------------------------------------------------------------------------
增加:
SqlConnection conn = new SqlConnection("server=(local);database=northwind;uid=sa;pwd=sasasa");
SqlDataAdapter adapter = new SqlDataAdapter("select * from customers", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "cus");
Console.WriteLine(ds.Tables["cus"].Rows.Count);
DataRow dr = ds.Tables["cus"].NewRow();
dr["customerid"] = "edf";
dr["companyname"] = "adsfas";
ds.Tables["cus"].Rows.Add(dr);
adapter.Update(ds, "cus");
Console.WriteLine(ds.Tables["cus"].Rows.Count);
---------------------------------------------------------------------------------------------------------
查询指定的行:
SqlConnection conn = new SqlConnection("server=(local);database=northwind;uid=sa;pwd=sasasa");
SqlDataAdapter adapter = new SqlDataAdapter("select * from customers", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "cus");
Console.WriteLine(ds.Tables["cus"].Rows.Count);
DataColumn[] dc = new DataColumn[1];
dc[0] = ds.Tables["cus"].Columns["customerid"];
ds.Tables["cus"].PrimaryKey = dc;//设置主键
DataRow dr = ds.Tables["cus"].Rows.Find("edf1");//获取主键
if (dr == null)
{
Console.WriteLine("没有被找到,需要被添加");
DataRow addrow = ds.Tables["cus"].NewRow();
addrow["customerid"] = "edf1";
addrow["companyname"] = "fdsadsf";
ds.Tables["cus"].Rows.Add(addrow);
}
else
{
Console.WriteLine("已经被找到");
}
adapter.Update(ds, "cus");
Console.WriteLine(ds.Tables["cus"].Rows.Count);
--------------------------------------------------------------------------------------------------------
删除:
SqlConnection conn = new SqlConnection("server=(local);database=northwind;uid=sa;pwd=sasasa");
SqlDataAdapter adapter = new SqlDataAdapter("select * from customers", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "cus");
DataColumn[] key = new DataColumn[1];
key[0] = ds.Tables["cus"].Columns["customerid"];
ds.Tables["cus"].PrimaryKey = key;
DataRow dr = ds.Tables["cus"].Rows.Find("edf1");
if (dr == null)
{
Console.WriteLine("没有被找到,需要被添加");
}
else
{
Console.WriteLine("已经被找到,需要被删除");
dr.Delete();//此处紧紧标识要被删除的行,真正执行删除语句的是下面的adapter.update(ds,"cus");此处的删除仅对内存中的数据进行修改
}
adapter.Update(ds, "cus");
如果只需要执行SQL语句或SP,就没必要用到DataAdapter ,直接用SqlCommand的Execute系列方法就可以了。sqlDataadapter的作用是实现Dataset和DB之间的桥梁:比如将对DataSet的修改更新到数据库。
SqlDataAdapter的UpdateCommand的执行机制是:当调用SqlDataAdapter.Update()时,检查DataSet中的所有行,然后对每一个修改过的Row执行SqlDataAdapter.UpdateCommand ,也就是说如果未修改DataSet中的数据,SqlDataAdapter.UpdateCommand不会执行。