DataSet用法3操作数据
1、为DataTable添加列
(1)添加列
DataTable tbl = ds.Tables.Add("User");
DataColumn col =tbl.Columns.Add("UserID",typeof(int));
col.AllowDBNull = false;
col.MaxLength = 6;
col.Unique = true;
tbl.PrimaryKey = new DataColumn[]{tbl.Columns["UserID"]};
当设置主键时，AllowDBNull自动设置为False;
(2)添加自增列
DataSet ds = new DataSet();
DataTable tbl = ds.Tables.Add("User");
DataColumn col = tbl.Columns.Add("UserID",typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;
col.ReadOnly = true;
2、修改DataTable中的数据
（1）添加数据行
DataRow row = ds.Tables["User"].NewRow();
row["UserID"] = "123456";
ds.Tables["User"].Rows.Add(row);
object[] aValues ={"123456","张三"}
da.Tables["User"].LoadDataRow(aValues,false);
（2）修改当前行
修改行的内容 Datast并不会自动修改数据库中相应的内容，而是要使用SqlDataAdapter对象的Update方法来提交修改。
//查找UserID为123456的用户，有则修改
DataRow User;
User = ds.Tables["UserID"].Rows.Find("123456");
if(User == null)
//没有查找客户
else
{
User.BeginEdit();
User["UserName"] ="王五";
User.EndEdit();
}
obejct[] User ={null,”王五”} ;//null表示不修改该列的数据
DataRow rowUser;
rowUser = ds.Tables["UserID"].Rows.Find("123456");
rowUser.ItemArray = User;
(3)处理行中的空值
A:查看是否为空
DataRow rowUser;
rowUser = ds.Tables["UserID"].Rows.Find("123456");
if(rowUser.IsNull("Address"))
Console.WriteLine("Address is Null");
else
Console.WriteLine("Address is not Null");
B:赋予空值
rowUser["Address"] = DBNull.Value;
（4）删除行
DataRow rowUser;
rowUser = ds.Tables["UserID"].Rows.Find("123456");
rowUser.Delete();
(5)清除DataRow
A:
DataRow rowUser;
rowUser = ds.Tables["UserID"].Rows.Find("123456");
rowUser.ItemArray = aUser;
da.Tables["User"].Remove(rowUser);
B:
ds.Tables["User"].RemoveAt(intIndex);
3、实例
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace sqlconnection1
{
class Program
{
private void SQLConnectionF(string source, string select)
{
//创建连接
SqlConnection con = new SqlConnection(source);
SqlDataAdapter adapt = new SqlDataAdapter(select,con);
try
{
con.Open();
Console.WriteLine("connection is successful!");
}
catch (Exception e)
{
Console.WriteLine("connection error is :{0}", e.ToString());
}
SqlCommandBuilder mybuilder = new SqlCommandBuilder(adapt);
//创建DataSet
DataSet ds = new DataSet();
//将数据添加到DataSet中
adapt.Fill(ds,"mytest");
//取出mytest表各列名
Console.WriteLine("{0,-15} {1,-10} {2,-10}",ds.Tables["mytest"].Columns[0],
ds.Tables["mytest"].Columns[1], ds.Tables["mytest"].Columns[2],
ds.Tables["mytest"].Columns[3], ds.Tables["mytest"].Columns[4]);
//输出DataSet中的所有数据
Console.WriteLine("before up data");
foreach (DataRow row in ds.Tables["mytest"].Rows)
{
Console.WriteLine("{0,-35} {1,-10} {2,-10} {3}",row[0] ,
row[1] , row[2] , row[3]);
}
//将第一行的第四列的值修改为123
DataRow rows1 = ds.Tables["mytest"].Rows[0];
rows1[3] = "123";
//删除该行
rows1.Delete();
//插入一行
DataRow newrow = ds.Tables["mytest"].NewRow();
newrow[0] = "mmm";
newrow[1] = 36;
newrow[2] = "aaa";
newrow[3] = 222;
ds.Tables["mytest"].Rows.Add(newrow);
//在DataSet中查找数据
DataColumn[] keys = new DataColumn[2];
keys[0] = ds.Tables["mytest"].Columns["name"];
keys[1] = ds.Tables["mytest"].Columns["number"];
//keys[1] = ds.Tables["mytest"].Columns ["type"];
ds.Tables["mytest"].PrimaryKey = keys;
DataRow findrow = ds.Tables["mytest"].Rows.Find("rrr");
if (findrow != null)
{
Console.WriteLine("{0}is find in tables",findrow);
//delete the row
Console.WriteLine("removing the rows ........");
findrow.Delete();
}
//用DataSet中的数据更新表
adapt.Update(ds,"mytest");
//输出DataSet中的所有数据
Console.WriteLine("after up data");
foreach (DataRow row in ds.Tables["mytest"].Rows)
{
Console.WriteLine("{0,-15} {1,-10} {2,-10} {3}", row[0],
row[1], row[2], row[3]);
}
Console.ReadLine();
con.Close();
}
static void Main(string[] args)
{
string sou = "server=duanyf\\SQLEXPRESS;" + "Initial Catalog=master;" + "UID = sa;" + "Password = dyf123";
string sel = "SELECT name,number,type,low,high From dbo.spt_values WHERE number=36";
Program sqlcon = new Program();
sqlcon.SQLConnectionF(sou, sel);
}
}
}
