DataSet用法(三) 操作数据

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);

 

}

 

}

 

}

posted @ 2011-01-11 09:47  快乐的langYa  阅读(2175)  评论(0编辑  收藏  举报