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

}

 

执行结果：