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);
}
执行结果：
