增删改查练习
1. 改
CommandBuilder最大的用处就是组建一个DataAdapter的UpdateCommand,InsertCommand,DeleteCommand这三个Command的CommandText。
一般来说,当使用DataAdapter的Update方法时,DataAdapter都会把相应的工作分给上述三个Command来处理,也就是说,要用Update方法,就必须配置好这三个Command。 Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace UpdatingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdapter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // SqlCommandBuilder
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Customers");
Console.WriteLine("name before change:{0}", thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme,Inc.";
thisAdapter.Update(thisDataSet, "Customers");//Update
Console.WriteLine("name after change:{0}", thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
CommandBuilder:命令构造器using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace UpdatingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdapter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // SqlCommandBuilder
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Customers");
Console.WriteLine("name before change:{0}", thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme,Inc.";
thisAdapter.Update(thisDataSet, "Customers");//Update
Console.WriteLine("name after change:{0}", thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
CommandBuilder最大的用处就是组建一个DataAdapter的UpdateCommand,InsertCommand,DeleteCommand这三个Command的CommandText。
如果我们用手工去配置的话,那工作量是很惊人的,而且还不一定正确。所以MS给我们提供了一个专门的工具CommandBuilder。
如果没有SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); 这一句, 那么, thisAdapter.Update(thisDataSet, "Customers");就会报错.
2.增
Code
namespace AddingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdapter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Customers");
Console.WriteLine("# rows before change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
DataRow thisrow = thisDataSet.Tables["Customers"].NewRow();
thisrow["CustomerID"] = "ZACZI";
thisrow["CompanyName"] = "Zachary Zithers Ltd.";
thisDataSet.Tables["Customers"].Rows.Add(thisrow);
Console.WriteLine("# rows after change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
thisAdapter.Update(thisDataSet, "Customers");
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
DataSet是内存中非连接的数据副本, DataAdapter负责连接到磁盘上的数据库, 因此需要调用它的Update()方法, 才能使DataSet中的内存数据与磁盘上的数据库同步.namespace AddingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdapter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Customers");
Console.WriteLine("# rows before change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
DataRow thisrow = thisDataSet.Tables["Customers"].NewRow();
thisrow["CustomerID"] = "ZACZI";
thisrow["CompanyName"] = "Zachary Zithers Ltd.";
thisDataSet.Tables["Customers"].Rows.Add(thisrow);
Console.WriteLine("# rows after change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
thisAdapter.Update(thisDataSet, "Customers");
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
3. 查
Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace FindingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdapter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Customers");
Console.WriteLine("# rows before change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
DataColumn[] keys = new DataColumn[1]; //创建一个DataColumns数组
keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; //将键数组的第一个元素keys[0]赋给Customers表中的CustomerID列.
thisDataSet.Tables["Customers"].PrimaryKey = keys; //将keys赋给DataTable对象Customers的PrimaryKey属性
//thisAdapter.MissingMappingAction = MissingSchemaAction.AddWithKey;
//thisAdapter.Fill(thisDataSet, "Customers"); //书上说这样也可以,但是运行出错:不能隐式转换
DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI"); //因为Find()返回一个DataRow,因此建立一个DataRow对象findRow来获取结果
if (findRow == null)
{
Console.WriteLine("ZACZI not found, will add to Customers table");
DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
thisRow["CustomerID"] = "ZACZI";
thisRow["CompanyName"] = "Zachary Zithers Ltd.";
thisDataSet.Tables["Customers"].Rows.Add(thisRow);
if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI")) != null)
{
Console.WriteLine("ZACZI successfully added to Customers table");
}
}
else
{
Console.WriteLine("ZACZI already present in database");
}
thisAdapter.Update(thisDataSet, "Customers");
Console.WriteLine("# rows after change {0}", thisDataSet.Tables["Customers"].Rows.Count);
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
使用Find()检查要添加的行是否已经存在.using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace FindingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdapter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Customers");
Console.WriteLine("# rows before change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
DataColumn[] keys = new DataColumn[1]; //创建一个DataColumns数组
keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; //将键数组的第一个元素keys[0]赋给Customers表中的CustomerID列.
thisDataSet.Tables["Customers"].PrimaryKey = keys; //将keys赋给DataTable对象Customers的PrimaryKey属性
//thisAdapter.MissingMappingAction = MissingSchemaAction.AddWithKey;
//thisAdapter.Fill(thisDataSet, "Customers"); //书上说这样也可以,但是运行出错:不能隐式转换
DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI"); //因为Find()返回一个DataRow,因此建立一个DataRow对象findRow来获取结果
if (findRow == null)
{
Console.WriteLine("ZACZI not found, will add to Customers table");
DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
thisRow["CustomerID"] = "ZACZI";
thisRow["CompanyName"] = "Zachary Zithers Ltd.";
thisDataSet.Tables["Customers"].Rows.Add(thisRow);
if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI")) != null)
{
Console.WriteLine("ZACZI successfully added to Customers table");
}
}
else
{
Console.WriteLine("ZACZI already present in database");
}
thisAdapter.Update(thisDataSet, "Customers");
Console.WriteLine("# rows after change {0}", thisDataSet.Tables["Customers"].Rows.Count);
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
在使用Find()之前, 需要构建一个主键. 主键就是用于搜索的键. 它由表的一列或多列构成, 包含可以在表中唯一标识行的值或值的集合.
因此, 当通过键搜索时, 可以找到唯一的一行.
4.删
Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DeletingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdpter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdpter);
DataSet thisDataSet = new DataSet();
thisAdpter.Fill(thisDataSet, "Customers");
Console.WriteLine("# rows before change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
DataColumn[] keys = new DataColumn[1];
keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
thisDataSet.Tables["Customers"].PrimaryKey = keys;
DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI");
if (findRow != null)
{
Console.WriteLine("ZACZI already in Customers table");
Console.WriteLine("Removing ZACZI");
findRow.Delete();
thisAdpter.Update(thisDataSet, "Customers");
}
Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Customers"].Rows.Count);
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
当调用Delete()时, 在调用Update改变数据库之前, 数据库中的行是不会被删除的.using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DeletingData
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Server=localhost;Integrated Security=True; Database=northwind";
string strsql = @"select customerID,CompanyName from Customers";
SqlConnection thisConnection = new SqlConnection(connstring);
SqlDataAdapter thisAdpter = new SqlDataAdapter(strsql, thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdpter);
DataSet thisDataSet = new DataSet();
thisAdpter.Fill(thisDataSet, "Customers");
Console.WriteLine("# rows before change:{0}", thisDataSet.Tables["Customers"].Rows.Count);
DataColumn[] keys = new DataColumn[1];
keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
thisDataSet.Tables["Customers"].PrimaryKey = keys;
DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI");
if (findRow != null)
{
Console.WriteLine("ZACZI already in Customers table");
Console.WriteLine("Removing ZACZI");
findRow.Delete();
thisAdpter.Update(thisDataSet, "Customers");
}
Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Customers"].Rows.Count);
thisConnection.Close();
Console.WriteLine("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
}
}
实际上, Delete()方法并不执行删除操作, 它仅仅标记要删除的行.