ADO.NET中不常用的功能

ADO.NET中不常用的功能

测试数据：

SqlConnection conn = new SqlConnection("Data Source=192.168.6.4;Initial Catalog=chapter30;uid=sa;pwd=123456;"); SqlCommand cmd = new SqlCommand(); string sqlText = @"Select * from Student Select * from Class Select * from SelectClass Select * from Teacher "; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = sqlText; adapter.TableMappings.Add("Table", "Student"); adapter.TableMappings.Add("Table1", "Class"); adapter.TableMappings.Add("Table2", "SelectClass"); adapter.TableMappings.Add("Table3", "Teacher"); try { conn.Open(); adapter.Fill(ds); } catch { } finally { conn.Close(); } DisplayTableData(ds.Tables["Student"]); DisplayTableData(ds.Tables["Class"]); DisplayTableData(ds.Tables["SelectClass"]); DisplayTableData(ds.Tables["Teacher"]); var student=ds.Tables["Student"]; var classes=ds.Tables["Class"]; var selectclass=ds.Tables["SelectClass"]; var teacher=ds.Tables["Teacher"];

DataRow的行状态

DataRow有4种状态：unchange,modify,detach,deleted

//unchange DisplayRowState(student.Rows[0]); //unchange //modify student.Rows[0][1] = "new_" + student.Rows[0][1].ToString(); DisplayRowState(student.Rows[0]); //detach var tmpRow = student.Rows[0]; student.Rows.Remove(tmpRow); DisplayRowState(tmpRow); //deleted student.Rows[0].Delete(); DisplayRowState(student.Rows[0]);

DataRow的数据版本

//数据行的原始值 var tmpRow2=student.Rows[1]; tmpRow2[1] = "new" + tmpRow2[1].ToString(); Console.WriteLine(tmpRow2[1, DataRowVersion.Default]); Console.WriteLine(tmpRow2[1, DataRowVersion.Current]); Console.WriteLine(tmpRow2[1, DataRowVersion.Original]); //调用Proposed需要调用beginedit方法 tmpRow2.BeginEdit(); Console.WriteLine(tmpRow2[1, DataRowVersion.Proposed]); tmpRow2.EndEdit(); 通过关系获取数据 //一对多 ds.Relations.Add("teachers", ds.Tables["Teacher"].Columns["Id"],ds.Tables["Class"].Columns["TeacherId"]); //多对多的关系在这里会被拆分了1对多 ds.Relations.Add("selectclass", new DataColumn[]{ ds.Tables["Class"].Columns["Id"], //ds.Tables["Student"].Columns["Id"] }, new DataColumn[]{ ds.Tables["SelectClass"].Columns["ClassId"], //ds.Tables["SelectClass"].Columns["StudentId"] } ); //var rows = tmpRow3.GetChildRows("students"); //var rows = tmpRow3.GetChildRows("teachers"); var rows = tmpRow3.GetChildRows("selectclass"); foreach (DataRow tmp in rows) { foreach (var tmp2 in tmp.ItemArray) { Console.Write("{0}\t", tmp2); } Console.WriteLine(); }

执行结果

数据约束

//设置主键 student.PrimaryKey = new DataColumn[] { student.Columns["Id"] }; //设置外键 ForeignKeyConstraint fk = new ForeignKeyConstraint(student.Columns["Id"], selectclass.Columns["StudentId"]); fk.UpdateRule = Rule.Cascade; selectclass.Constraints.Add(fk); //设置唯一约束 UniqueConstraint uq = new UniqueConstraint(student.Columns["Id"]); student.Constraints.Add(uq);

用XSD文件生成ADO.NET对象

//将Schema文件抓换为类对象，命令 XSD [schema文件名] /d /out:输出的文件路径 //看来Microsoft把O/R进行到底，连Xml文件都不放过。 Books books = new Books(); books.book.AddbookRow("MJ传奇一", "P123456789", "王小帅", "清华出版社", "99"); books.book.AddbookRow("MJ传奇二", "P123456789", "王小帅", "清华出版社", "99"); books.book.AddbookRow("MJ传奇三", "P123456789", "王小帅", "清华出版社", "99"); books.book.AddbookRow("MJ传奇四", "P123456789", "王小帅", "清华出版社", "99"); books.book.AddbookRow("MJ传奇五", "P123456789", "王小帅", "清华出版社", "99"); books.book.AddbookRow("MJ传奇六", "P123456789", "王小帅", "清华出版社", "99"); foreach (Books.bookRow book in books.book) { Console.WriteLine(book.Name); }

执行结果：