SqlDataAdapter的几个属性的学习
今天学习一下SqlDataAdapter的几个属性包括SelectCommand、InsertCommand、UpdateCommand、DeleteCommand。以前更新数据库的时候从没有用到以上属性。今天看了一下上面的几个属性感觉挺不错的。
1.首先创建一个Class包含一个方法返回一个SqlDataAdapter类型的对象:
//创建两个表tb_City、tb_Card
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection connection)
{
SqlDataAdapter adapter = new SqlDataAdapter();
//create the SelectCommand
SqlCommand command = new SqlCommand("select tb_City.Province,tb_City.City, tb_Card.CardName from tb_City , tb_Card", connection);
adapter.SelectCommand = command;
//Create the insertCommand
command = new SqlCommand("insert into tb_City(Province,City)values(@Province,@City) insert into tb_Card(CardName)values(@CardName)",connection);
command.Parameters.Add("@Province", SqlDbType.Text,15, "Province");
command.Parameters.Add("@City", SqlDbType.Text, 15, "City");
command.Parameters.Add("@CardName", SqlDbType.Text, 15, "CardName");
adapter.InsertCommand = command;
//Create the UpdateCommand
command = new SqlCommand("update tb_City set Province=@Province,City=@City where Province=@oldProvince", connection);
command.Parameters.Add("@Province", SqlDbType.NVarChar, 15, "Province");
command.Parameters.Add("@City", SqlDbType.NVarChar, 15, "City");
command.Parameters.Add("@oldProvince",SqlDbType.NVarChar,12,"Province").SourceVersion=DataRowVersion.Original;
adapter.UpdateCommand = command;
//Delete the UpdateCommand
command = new SqlCommand("delete from tb_City where Province=@oldProvince",connection);
command.Parameters.Add("@Province",SqlDbType.NVarChar,12,"Province");
command.Parameters.Add("@oldProvince", SqlDbType.NVarChar, 12, "Province").SourceVersion=DataRowVersion.Original;
adapter.DeleteCommand = command;
return adapter;
}
//绑定数据源
public class GridViewClass
{
private static SqlConnection con;
private static DataSet ds;
private static SqlDataAdapter da;
public static DataSet ReadData()
{
con = new SqlConnection("server=(local);database=db_04;uid=sa;pwd=");
con.Open();
da = CustomerAdapter.CreateCustomerAdapter(con);
ds = new DataSet();
da.Fill(ds);
con.Close();
return ds;
}
public static void AddData()//数据源添加10行数据
{
for (int i = 0; i < 10; i++)
{
DataRow dr = ds.Tables[0].NewRow();
dr["Province"] = "yunnan"+i.ToString();
dr["City"] = "kunming"+i.ToString();
dr["CardName"] = "123888"+i.ToString();
ds.Tables[0].Rows.Add(dr);
da.Update(ds);
}
}
public static void UpdateDate()//修改一行数据
{
ds.Tables[0].Rows[0]["Province"] = "99999";
ds.Tables[0].Rows[0]["City"] = "中华人民共和国";
da.Update(ds);
}
public static void Del()//删除全部行数据
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ds.Tables[0].Rows[i].Delete();
da.Update(ds);
}
}
}
以上每当调用 da.Update(ds)方法是会自动调用对应的InsertCommand、UpdateCommand、DelteCommand所对应Sql语句。我以前修改数据不是用这种方法的,这种方法还是比较方便的,看样子还是得把基础打扎实,这样才能事半功倍。。
以上是今天学习的一点体会不当之处还请不吝赐教。。