pythondataset⽤法_dataset⽤法（2）
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;
python新手代码useridrowUser = 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);
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。