ADO.NET DataSet

ゞ浴缸里的玫瑰 2022-08-07 05:47 105阅读 0赞

## [ADO.NET DataSet][] ##

Posted on 2008-10-10 10:10 [yiki][] 阅读(94) [评论(0)][0]   [编辑][Link 1] [收藏][Link 2] [网摘][Link 3] 所属分类: [ASP.NET][] ![1307805.html_type_1_webview_1][]

![None.gif][] DataSet技术 DataTable DataView  
![None.gif][] // \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
![None.gif][] //  在数据库vls 中有个 PROVINCE表 其中字段有 PROVINCE\_ID/ PROVINCE\_CDE/ PROVINCE\_NAME ××包含中国34省，市，自治区  
![None.gif][] // \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/   
![None.gif][] From 与ASP.NET 用法一样  
![None.gif][] 1 .绑定DataGird  
![None.gif][] string  constr = " user id=sa;password=168168;initial catalog=VLS;data source=yu;Connect Timeout=30 " ;  
![None.gif][]SqlConnection mycon = new  SqlConnection(constr);  
![None.gif][]     
![None.gif][] string  sqlstr = " select \* from PROVINCE " ;  
![None.gif][]SqlDataAdapter dap = new  SqlDataAdapter(sqlstr,mycon);  
![None.gif][] //  也可以用下面3句 调用 SqlCommand组件  
![None.gif][] // SqlCommand mycomd=new SqlCommand(sqlstr,mycon);  
![None.gif][] // SqlDataAdapter dap=new SqlDataAdapter();  
![None.gif][] // dap.SelectCommand=mycomd;    
![None.gif][]   
![None.gif][]mycon.Open();  
![None.gif][]DataSet ds = new  DataSet();  
![None.gif][]dap.Fill(ds, " PROVINCE " );  
![None.gif][]mycon.Close();  
![None.gif][]  
![None.gif][] this .DataGrid1.DataSource = ds.Tables\[ 0 \].DefaultView;  
![None.gif][] this .DataGrid1.DataBind();  
![None.gif][] 2 .增加  
![None.gif][] string  constr = " user id=sa;password=168168;initial catalog=VLS;data source=yu;Connect Timeout=30 " ;  
![None.gif][]SqlConnection mycon = new  SqlConnection(constr);  
![None.gif][]    
![None.gif][] string  sqlstr = " select \* from PROVINCE " ;  
![None.gif][]SqlCommand mycomd = new  SqlCommand(sqlstr,mycon);  
![None.gif][]mycon.Open();  
![None.gif][]SqlDataAdapter myAdapter = new  SqlDataAdapter();  
![None.gif][]myAdapter.SelectCommand = mycomd;  
![None.gif][]DataSet ds = new  DataSet();  
![None.gif][]myAdapter.Fill(ds, " PROVINCE " );  
![None.gif][] // 增加   
![None.gif][] for ( int  i = 0 ;i < 34 ;i \++ )  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]DataRow drw=ds.Tables\[0\].NewRow();  
![InBlock.gif][]drw\["PROVINCE\_ID"\]="temp"\+Convert.ToString(i);  
![InBlock.gif][]drw\["PROVINCE\_NAME"\]="你好们";  
![InBlock.gif][]ds.Tables\[0\].Rows.Add(drw);  
![ExpandedBlockEnd.gif][]\}   
![None.gif][]SqlCommandBuilder myCommandBuilder  =   new  SqlCommandBuilder(myAdapter);  
![None.gif][]myAdapter.Update(ds, " PROVINCE " );   
![None.gif][]ds.Tables\[ 0 \].AcceptChanges();  
![None.gif][]mycon.Close();  
![None.gif][] 3 .删除 修改  // \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*删除 修改记录的 表一定要有主键   
![None.gif][] string  constr = " user id=sa;password=168168;initial catalog=VLS;data source=yu;Connect Timeout=30 " ;  
![None.gif][]SqlConnection mycon = new  SqlConnection(constr);  
![None.gif][]     
![None.gif][] string  sqlstr = " select \* from PROVINCE " ;  
![None.gif][]SqlCommand mycomd = new  SqlCommand(sqlstr,mycon);  
![None.gif][]mycon.Open();  
![None.gif][]SqlDataAdapter myAdapter = new  SqlDataAdapter();  
![None.gif][]myAdapter.SelectCommand = mycomd;  
![None.gif][]DataSet ds = new  DataSet();  
![None.gif][]myAdapter.Fill(ds, " PROVINCE " );  
![None.gif][]      
![None.gif][]DataRow \[\]drws = ds.Tables\[ 0 \].Select( " PROVINCE\_ID like 'temp%' " );  
![None.gif][] // 删除    
![None.gif][] foreach (DataRow drw  in  drws)  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]Response.Write(drw\["PROVINCE\_NAME"\].ToString()\+" ");  
![InBlock.gif][]drw.Delete();  
![ExpandedBlockEnd.gif][]\}   
![None.gif][] // 修改   
![None.gif][] foreach (DataRow drw  in  ds.Tables\[ 0 \].Rows)  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]Response.Write(drw\["PROVINCE\_NAME"\].ToString()\+" ");  
![InBlock.gif][]drw\["PROVINCE\_CDE"\]="M2";  
![ExpandedBlockEnd.gif][]\}   
![None.gif][]SqlCommandBuilder myCommandBuilder  =   new  SqlCommandBuilder(myAdapter);  
![None.gif][]myAdapter.Update(ds,ds.Tables\[ 0 \].TableName);   
![None.gif][]ds.Tables\[ 0 \].AcceptChanges();  
![None.gif][]mycon.Close();  
![None.gif][]  
![None.gif][]DataTable  
![None.gif][] 1 .新建表绑定  
![None.gif][]DataTable dt = new  DataTable();  
![None.gif][]DataRow dr;  
![None.gif][]dt.Columns.Add( new  DataColumn( " id " , typeof (Int32)));  
![None.gif][]dt.Columns.Add( new  DataColumn( " text " , typeof ( string )));        
![None.gif][] for ( int  i = 0 ;i < 10 ;i \++ )  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]dr=dt.NewRow();  
![InBlock.gif][]dr\[0\]=i;  
![InBlock.gif][]dr\[1\]="sds"\+i.ToString();  
![InBlock.gif][]dt.Rows.Add(dr);  
![ExpandedBlockEnd.gif][]\}   
![None.gif][] this .DataGrid1.DataSource = dt.DefaultView;  
![None.gif][] this .DataGrid1.DataBind();  
![None.gif][] 2 . // DataTable 经常用到的 select属性 用来 判断重复   
![None.gif][] DataTable dt = new  DataTable();DataRow dr;  
![None.gif][]dt.Columns.Add( new  DataColumn( " id " , typeof ( string )));  
![None.gif][]dt.Columns.Add( new  DataColumn( " text " , typeof ( string )));  
![None.gif][] for ( int  i = 0 ;i < 10 ;i \++ )  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]dr=dt.NewRow();  
![InBlock.gif][]dr\[0\]="ff"\+i.ToString();;  
![InBlock.gif][]dr\[1\]="sds"\+i.ToString();  
![InBlock.gif][]dt.Rows.Add(dr);  
![ExpandedBlockEnd.gif][]\}   
![None.gif][] string  str = " id='ff3' and text='sds3' " ;  
![None.gif][] // 模糊查询  // string str=" text like 's%'";   
![None.gif][] DataRow\[\] drws = dt.Select(str);  
![None.gif][] if (drws.Length > 0 )  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{Response.Write("有重复记录！");\}   
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] else ![dot.gif][] \{Response.Write("无重复记录！");\}   
![None.gif][]  
![None.gif][]DataView  
![None.gif][] // DataView 就是 筛选，过滤后的DataTable   
![None.gif][] 1 .筛选  
![None.gif][]DataTable dt = new  DataTable();DataRow dr;  
![None.gif][]dt.Columns.Add( new  DataColumn( " id " , typeof (Int32)));  
![None.gif][]dt.Columns.Add( new  DataColumn( " text " , typeof ( string )));  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] for ( int  i = 0 ;i < 10 ;i \++ ) ![dot.gif][] \{  
![InBlock.gif][]dr=dt.NewRow();  
![InBlock.gif][]dr\[0\]=i;  
![InBlock.gif][]dr\[1\]="sds"\+i.ToString();  
![ExpandedBlockEnd.gif][]dt.Rows.Add(dr);    \}   
![None.gif][]dt.DefaultView.RowFilter = " id=5 " ;  
![None.gif][] this .DataGrid1.DataSource = dt.DefaultView;  
![None.gif][] this .DataGrid1.DataBind();  
![None.gif][] 2 .排序  
![None.gif][]DataTable dt = new  DataTable();DataRow dr;  
![None.gif][]dt.Columns.Add( new  DataColumn( " id " , typeof (Int32)));  
![None.gif][]dt.Columns.Add( new  DataColumn( " text " , typeof ( string )));  
![None.gif][] for ( int  i = 0 ;i < 10 ;i \++ )  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]dr=dt.NewRow();  
![InBlock.gif][]dr\[0\]=i;   
![InBlock.gif][]dr\[1\]="sds"\+i.ToString();  
![InBlock.gif][]dt.Rows.Add(dr);  
![ExpandedBlockEnd.gif][]\}   
![None.gif][]dt.DefaultView.Sort = " id desc " ;  
![None.gif][] this .DataGrid1.DataSource = dt.DefaultView;  
![None.gif][] this .DataGrid1.DataBind();

DataSet是ADO.NET开发人员为方便数据处理开发出来的，是数据的集合，是为解决DataReader的缺陷设计的，DataReader数据处理速度快，但它是只读的，　而且一旦移到下一行，就不能查看上一行的数据，DataSet则可以自由移动指针。DataSet的数据是与数据库断开的。DataSet还可用于多层应用程序中，如果应用程序运行在中间层的业务对象中来访问数据库，则业务对象需将脱机数据结构传递给客户应用程序。

DataSet的功能：浏览、排序、搜索、过滤、处理分级数据、缓存更改等。还可以与ＸＭＬ数据互换。DataSet中可包括多个DataTable，可将多个查询结构存到一个DataSet中，方便操作，而DataTable中又包括多个DataRow、DataColumn，可通过这些DataRow、DataColumn来查看、操作其中的数据，而需将操作结果返回给数据库的话，则可以调用DataAdapter的Update方法。

DataSet的操作：

![None.gif][] DataSet ds = new  DataSet();  
![None.gif][]DataTable dt = new  DataTable( " newTable " );  
![None.gif][]ds.Tables.Add(dt);

![None.gif][] DataSet ds = new  DataSet();  
![None.gif][]DataTable dt = ds.Tables.Add( " newTable " );

上述两种方法都可以在DataSet中添加一个DataTable，看需要而进行选择。添加DataTable后，需向其中添加行和列。

![None.gif][] DataSet ds = new  DataSet();  
![None.gif][]DataTable dt = ds.Tables.Add( " newTables " );  
![None.gif][]DataColumn col = dt.Columns.Add( " newColumn " , typeof ( int ));  
![None.gif][]col.AllowDBNull = false ;  
![None.gif][]col.MaxLength = 4 ;  
![None.gif][]col.Unique = true ;  
![None.gif][]

上述代码向DataSet中的DataTable中添加名为”newColumn”，类型为int且不为空，最大长度为４和唯一性为真的列。

![ExpandedBlockStart.gif][] ![ContractedBlock.gif][] dt.PrimaryKey = new  DataColumn\[\] ![dot.gif][] \{dt.Columns\["ID"\]\}

这段代码是继续上面代码的，为一个DataTable中添加一个主键列，主键列是一个数据组，如有多个主键，只需在数组中添加一个列即可。如下：

![ExpandedBlockStart.gif][] ![ContractedBlock.gif][] dt.PrimaryKey = new  DataColumns\[\] ![dot.gif][] \{dt.Columns\["OrderID"\],dt.Columns\["ProductID"\]\}

添加外键：

![None.gif][] ForeignKeyConstraint fk;  
![None.gif][]fk = new  ForeignKeyConstraint(ds.Tables\[ " Customers " \].Columns\[ " CustomerID " \],ds.Tables\[ " Orders " \].Columns\[ " CustomerID " \]);  
![None.gif][]ds.Tables\[ " Orders " \].Constraints.Add(fk);  
![None.gif][] // 上述代码假如已经为Cusomers表和Orders创建了主键，此句为添加外键约束。

上述是根据Customers表和Orders表的CustomerID来创建约束。

下面介绍修改DataRow中的内容：

![None.gif][] DataRow dr = ds.Tables\[ " Customer " \].Rows.Find( " ANTON " );  
![None.gif][] if (dr == null )  
![None.gif][]  
![None.gif][] else   
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
![InBlock.gif][]dr.BeginEdit();   
![InBlock.gif][]dr\["CompanyName"\]="newValue";  
![InBlock.gif][]dr\["ContactName"\]="newValue2";  
![InBlock.gif][]dr.EndEdit();  
![ExpandedBlockEnd.gif][]\}   
![None.gif][] // 上面代码通过Row集合的Find方法来在DataTable中的行进行定位，找到"ANTON"行，再修改"ANTON"行中CompanyName列和ContactName列的值。通过BeginEdit和EndEdit来缓存对行的修改，还可调用 CancelEdit为取消修改。

判断某列是否为空值：

![None.gif][] DataRow dr = ds.Tables\[ " Customers " \].Rows.Find( " aaa " );  
![None.gif][] if (dr.IsNull( " ContactName " );  
![None.gif][] ..  
![None.gif][] else   
![None.gif][] dr\[ " ContactName " \] = DBNull.Value  
![None.gif][] // 这里判断ContactName列是否为空，如果不是则为其赋空值，呵，很无厘头的做法，这里只为演示为列赋空值的做法。

删除DataRow:

有两种方法可以删除DataRow,Delete方法和Remove方法和RemoveAt方法。其区别是Delete方法实际上不是从DataTable中删除掉一行，而是将其标志为删除，仅仅是做个记号，而Remove方法则是真正的从DataRow中删除一行，RemoveAt方法是根本行的索引来删除。列：

![None.gif][] DataRow dr = ds.Tables\[ " table " \].Rows.Find( " a " );  
![None.gif][]ds.Tables\[ " table " \].Remove(dr);  
![None.gif][]或  
![None.gif][]ds.Tables\[ " table " \].Remove(index);  
![None.gif][] // dr 为"a"所在的行，查出后将其删除，index为 "a"所在的索引号。

关于DataSet中的其用法，参照MSDN

[ADO.NET DataSet]: http://www.cnblogs.com/yiki/archive/2008/10/10/1307805.html
[yiki]: http://www.cnblogs.com/yiki/
[0]: http://writeblog.csdn.net/#commentform
[Link 1]: http://www.cnblogs.com/yiki/admin/EditPosts.aspx?postid=1307805
[Link 2]: http://www.cnblogs.com/yiki/AddToFavorite.aspx?id=1307805
[Link 3]: http://writeblog.csdn.net/#
[ASP.NET]: http://www.cnblogs.com/yiki/category/80418.html
[1307805.html_type_1_webview_1]: /images/20220805/05ca40263eeb447898195f96aeb10ef3.png
[None.gif]: /images/20220805/894cb7baf4124676a8f02fa9b93eabfd.png
[ExpandedBlockStart.gif]: http://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif
[ContractedBlock.gif]: http://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif
[dot.gif]: /images/20220805/a1806b0eb63b4beda81d19c584c983a5.png
[InBlock.gif]: /images/20220805/c248c773cdae420184bf9e8e8fab6fae.png
[ExpandedBlockEnd.gif]: /images/20220805/06fcc902403b4a8d85f027d8c7f90e4d.png