Csharp/CSharp Tutorial/ADO.Net/DataSet
Содержание
- 1 Creating a Strongly Typed DataSet
- 2 DataSet.DataTable count
- 3 DataSet.DataTable name
- 4 DataSet Merge event
- 5 DataSet Read with SqlDataAdapter
- 6 Fill a DataSet
- 7 Find Rows In Data
- 8 Load XML to DataSet
- 9 Mapping Table and Column Names Between a Data Source and DataSet
- 10 Merge two DataSet
- 11 Multitabled DataSet App
- 12 Navigating Between Parent and Child Tables in an Untyped DataSet
- 13 Read schema and reload data with DataSet
- 14 Retrieving Schema and Constraints for a DataSet
- 15 Simple Query Dataset
Creating a Strongly Typed DataSet
<source lang="csharp">using System;
using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { string xsdFileName = "AdventureWorks.xsd"; string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlText = "SELECT * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;"; SqlDataAdapter da = new SqlDataAdapter(sqlText, sqlConnectString); da.TableMappings.Add("Table", "SalesOrderHeader"); da.TableMappings.Add("Table1", "SalesOrderDetail"); DataSet ds = new DataSet("AdventureWorks"); da.FillSchema(ds, SchemaType.Mapped); ds.Relations.Add("SalesOrderHeader_SalesOrderDetail", ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"], ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]); ds.WriteXmlSchema(xsdFileName); } }</source>
DataSet.DataTable count
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT FirstName, LastName FROM Person.Contact"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.Fill(dt); foreach (DataRow row in dt.Rows) Console.WriteLine("{0} {1}", row[0], row["LastName"]);
DataSet ds = new DataSet( ); da.Fill(ds, "Contact"); Console.WriteLine(ds.Tables.Count); Console.WriteLine(ds.Tables[0].TableName); foreach (DataRow row in ds.Tables["Contact"].Rows) Console.WriteLine("{0} {1}", row[0], row[1]); } }</source>
DataSet.DataTable name
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT FirstName, LastName FROM Person.Contact"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.Fill(dt); foreach (DataRow row in dt.Rows) Console.WriteLine("{0} {1}", row[0], row["LastName"]);
DataSet ds = new DataSet( ); da.Fill(ds, "Contact"); Console.WriteLine(ds.Tables.Count); Console.WriteLine(ds.Tables[0].TableName); foreach (DataRow row in ds.Tables["Contact"].Rows) Console.WriteLine("{0} {1}", row[0], row[1]); } }</source>
DataSet Merge event
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb; public class MainClass {
public static void Main(){ OleDbConnection conn = new OleDbConnection(); string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.mdb"; conn.ConnectionString = strDSN; conn.Open(); string sql = "SELECT * FROM orders "; OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); DataSet ds1 = new DataSet("ds1"); da.Fill(ds1); sql = "SELECT * FROM Customers "; da = new OleDbDataAdapter(sql, conn); DataSet ds2 = new DataSet("ds2"); da.Fill(ds2); ds1.MergeFailed += new MergeFailedEventHandler(OnMergeFailed); ds1.Merge(ds2); } protected static void OnMergeFailed (object sender, MergeFailedEventArgs args) { MessageBox.Show(args.Conflict.ToString()); }
}</source>
DataSet Read with SqlDataAdapter
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text;
class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename="NORTHWND.MDF";" + @"Integrated Security=True;Connect Timeout=30;User Instance=true"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, ContactName FROM Customers", thisConnection); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Customers"); foreach (DataRow theRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine(theRow["CustomerID"] + "\t" +theRow["ContactName"]); } thisConnection.Close(); } }</source>
Fill a DataSet
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql = @"select productname,unitprice from products where unitprice < 20"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, "products"); DataTable dt = ds.Tables["products"]; foreach (DataRow row in dt.Rows) { foreach (DataColumn col in dt.Columns) Console.WriteLine(row[col]); } } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } }
}</source>
Find Rows In Data
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main() { SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT * FROM Employee", MyConnection); SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter); DataSet MyDataSet = new DataSet(); MyDataAdapter.Fill(MyDataSet); DataColumn[] MyKey = new DataColumn[1]; MyKey[0] = MyDataSet.Tables[0].Columns[0]; MyDataSet.Tables[0].PrimaryKey = MyKey; DataRow FindMyRow = MyDataSet.Tables[0].Rows.Find(1); }
}</source>
Load XML to DataSet
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlDataAdapter MyAdapter = new SqlDataAdapter("SELECT * FROM Employee", MyConnection); DataSet MyDataSet = new DataSet(); MyAdapter.Fill(MyDataSet, "MyTable"); MyDataSet.WriteXml(@"c:\Sample.xml"); }
}</source>
Mapping Table and Column Names Between a Data Source and DataSet
<source lang="csharp">using System; using System.Data; using System.Data.rumon; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 Title, FirstName, LastName FROM Person.Contact"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTableMapping dtm = da.TableMappings.Add("Table", "myContact"); dtm.ColumnMappings.Add("Title", "myTitle"); dtm.ColumnMappings.Add("FirstName", "myFirstName"); dtm.ColumnMappings.Add("LastName", "myLastName"); DataSet ds = new DataSet(); da.Fill(ds); Console.WriteLine("DataTable name = {0}",ds.Tables[0].TableName); foreach(DataColumn col in ds.Tables["myContact"].Columns) { Console.WriteLine(col.Ordinal); Console.WriteLine(col.ColumnName); } foreach(DataRow row in ds.Tables["myContact"].Rows) { Console.WriteLine( "Title = {0}, FirstName = {1}, LastName = {2}", row["myTitle"], row["myFirstName"], row["myLastName"]); } } }</source>
Merge two DataSet
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb; public class MainClass {
public static void Main(){ OleDbConnection conn = new OleDbConnection(); string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.mdb"; conn.ConnectionString = strDSN; conn.Open(); string sql = "SELECT * FROM orders "; OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); DataSet ds1 = new DataSet("ds1"); da.Fill(ds1); sql = "SELECT * FROM Customers "; da = new OleDbDataAdapter(sql, conn); DataSet ds2 = new DataSet("ds2"); da.Fill(ds2); ds1.MergeFailed += new MergeFailedEventHandler(OnMergeFailed); ds1.Merge(ds2); } protected static void OnMergeFailed (object sender, MergeFailedEventArgs args) { MessageBox.Show(args.Conflict.ToString()); }
}</source>
Multitabled DataSet App
<source lang="csharp">using System; using System.Collections.Generic; using System.ruponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.Configuration;
public class MainForm : Form { private DataSet yourDBDS = new DataSet("YourDB"); private SqlCommandBuilder sqlCBInventory; private SqlCommandBuilder sqlCBCustomers; private SqlCommandBuilder sqlCBOrders; private SqlDataAdapter invTableAdapter; private SqlDataAdapter custTableAdapter; private SqlDataAdapter ordersTableAdapter; private string cnStr = "SqlProvider"; public MainForm() { InitializeComponent(); invTableAdapter = new SqlDataAdapter("Select * from Inventory", cnStr); custTableAdapter = new SqlDataAdapter("Select * from Customers", cnStr); ordersTableAdapter = new SqlDataAdapter("Select * from Orders", cnStr); sqlCBInventory = new SqlCommandBuilder(invTableAdapter); sqlCBOrders = new SqlCommandBuilder(ordersTableAdapter); sqlCBCustomers = new SqlCommandBuilder(custTableAdapter); invTableAdapter.Fill(yourDBDS, "Inventory"); custTableAdapter.Fill(yourDBDS, "Customers"); ordersTableAdapter.Fill(yourDBDS, "Orders"); BuildTableRelationship(); dataGridViewInventory.DataSource = yourDBDS.Tables["Inventory"]; dataGridViewCustomers.DataSource = yourDBDS.Tables["Customers"]; dataGridViewOrders.DataSource = yourDBDS.Tables["Orders"]; } private void BuildTableRelationship() { DataRelation dr = new DataRelation("CustomerOrder",yourDBDS.Tables["Customers"].Columns["CustID"],yourDBDS.Tables["Orders"].Columns["CustID"]); yourDBDS.Relations.Add(dr); dr = new DataRelation("InventoryOrder",yourDBDS.Tables["Inventory"].Columns["CarID"],yourDBDS.Tables["Orders"].Columns["CarID"]); yourDBDS.Relations.Add(dr); } private void btnUpdate_Click(object sender, EventArgs e) { try { invTableAdapter.Update(yourDBDS, "Inventory"); custTableAdapter.Update(yourDBDS, "Customers"); ordersTableAdapter.Update(yourDBDS, "Orders"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void btnGetOrderInfo_Click(object sender, System.EventArgs e) { string strOrderInfo = string.Empty; DataRow[] drsCust = null; DataRow[] drsOrder = null; int custID = int.Parse(this.txtCustID.Text); drsCust = yourDBDS.Tables["Customers"].Select(string.Format("CustID = {0}", custID)); strOrderInfo += string.Format("Customer {0}: {1} {2}\n", drsCust[0]["CustID"].ToString(), drsCust[0]["FirstName"].ToString().Trim(), drsCust[0]["LastName"].ToString().Trim()); drsOrder = drsCust[0].GetChildRows(yourDBDS.Relations["CustomerOrder"]); foreach (DataRow r in drsOrder) strOrderInfo += string.Format("Order Number: {0}\n", r["OrderID"]); DataRow[] drsInv = drsOrder[0].GetParentRows(yourDBDS.Relations["InventoryOrder"]); foreach (DataRow r in drsInv) { strOrderInfo += r["Make"]; strOrderInfo += r["Color"]; strOrderInfo += r["PetName"]; } MessageBox.Show(strOrderInfo, "Order Details"); } private void InitializeComponent() { this.dataGridViewInventory = new System.Windows.Forms.DataGridView(); this.label1 = new System.Windows.Forms.Label(); this.label2 = new System.Windows.Forms.Label(); this.dataGridViewCustomers = new System.Windows.Forms.DataGridView(); this.label3 = new System.Windows.Forms.Label(); this.dataGridViewOrders = new System.Windows.Forms.DataGridView(); this.btnUpdateDatabase = new System.Windows.Forms.Button(); this.btnGetOrderInfo = new System.Windows.Forms.Button(); this.txtCustID = new System.Windows.Forms.TextBox(); this.label4 = new System.Windows.Forms.Label(); this.groupBox1 = new System.Windows.Forms.GroupBox(); ((System.ruponentModel.ISupportInitialize)(this.dataGridViewInventory)).BeginInit(); ((System.ruponentModel.ISupportInitialize)(this.dataGridViewCustomers)).BeginInit(); ((System.ruponentModel.ISupportInitialize)(this.dataGridViewOrders)).BeginInit(); this.groupBox1.SuspendLayout(); this.SuspendLayout(); // // dataGridViewInventory // this.dataGridViewInventory.Location = new System.Drawing.Point(12, 34); this.dataGridViewInventory.Name = "dataGridViewInventory"; this.dataGridViewInventory.Size = new System.Drawing.Size(509, 82); this.dataGridViewInventory.TabIndex = 0; this.dataGridViewInventory.Text = "dataGridView1"; // // label1 // this.label1.AutoSize = true; this.label1.Location = new System.Drawing.Point(11, 18); this.label1.Name = "label1"; this.label1.Size = new System.Drawing.Size(88, 13); this.label1.TabIndex = 1; this.label1.Text = "Current Inventory"; // // label2 // this.label2.AutoSize = true; this.label2.Location = new System.Drawing.Point(12, 126); this.label2.Name = "label2"; this.label2.Size = new System.Drawing.Size(93, 13); this.label2.TabIndex = 3; this.label2.Text = "Current Customers"; // // dataGridViewCustomers // this.dataGridViewCustomers.Location = new System.Drawing.Point(13, 142); this.dataGridViewCustomers.Name = "dataGridViewCustomers"; this.dataGridViewCustomers.Size = new System.Drawing.Size(509, 90); this.dataGridViewCustomers.TabIndex = 2; this.dataGridViewCustomers.Text = "dataGridView2"; // // label3 // this.label3.AutoSize = true; this.label3.Location = new System.Drawing.Point(12, 244); this.label3.Name = "label3"; this.label3.Size = new System.Drawing.Size(75, 13); this.label3.TabIndex = 5; this.label3.Text = "Current Orders"; // // dataGridViewOrders // this.dataGridViewOrders.Location = new System.Drawing.Point(13, 260); this.dataGridViewOrders.Name = "dataGridViewOrders"; this.dataGridViewOrders.Size = new System.Drawing.Size(509, 90); this.dataGridViewOrders.TabIndex = 4; this.dataGridViewOrders.Text = "dataGridView3"; // // btnUpdateDatabase // this.btnUpdateDatabase.Location = new System.Drawing.Point(407, 368); this.btnUpdateDatabase.Name = "btnUpdateDatabase"; this.btnUpdateDatabase.Size = new System.Drawing.Size(114, 23); this.btnUpdateDatabase.TabIndex = 6; this.btnUpdateDatabase.Text = "Update Database"; this.btnUpdateDatabase.Click += new System.EventHandler(this.btnUpdate_Click); // // btnGetOrderInfo // this.btnGetOrderInfo.Location = new System.Drawing.Point(84, 71); this.btnGetOrderInfo.Name = "btnGetOrderInfo"; this.btnGetOrderInfo.Size = new System.Drawing.Size(110, 23); this.btnGetOrderInfo.TabIndex = 7; this.btnGetOrderInfo.Text = "Get Order Details"; this.btnGetOrderInfo.Click += new System.EventHandler(this.btnGetOrderInfo_Click); // // txtCustID // this.txtCustID.Location = new System.Drawing.Point(84, 29); this.txtCustID.Name = "txtCustID"; this.txtCustID.Size = new System.Drawing.Size(110, 20); this.txtCustID.TabIndex = 8; // // label4 // this.label4.AutoSize = true; this.label4.Location = new System.Drawing.Point(10, 29); this.label4.Name = "label4"; this.label4.Size = new System.Drawing.Size(68, 13); this.label4.TabIndex = 9; this.label4.Text = "Customer ID:"; // // groupBox1 // this.groupBox1.Controls.Add(this.btnGetOrderInfo); this.groupBox1.Controls.Add(this.label4); this.groupBox1.Controls.Add(this.txtCustID); this.groupBox1.Location = new System.Drawing.Point(15, 368); this.groupBox1.Name = "groupBox1"; this.groupBox1.Size = new System.Drawing.Size(200, 100); this.groupBox1.TabIndex = 10; this.groupBox1.TabStop = false; this.groupBox1.Text = "Lookup Customer Order"; // // MainForm // this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(534, 480); this.Controls.Add(this.groupBox1); this.Controls.Add(this.btnUpdateDatabase); this.Controls.Add(this.label3); this.Controls.Add(this.dataGridViewOrders); this.Controls.Add(this.label2); this.Controls.Add(this.dataGridViewCustomers); this.Controls.Add(this.label1); this.Controls.Add(this.dataGridViewInventory); this.Name = "MainForm"; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = "YourDB Database Manipulator"; ((System.ruponentModel.ISupportInitialize)(this.dataGridViewInventory)).EndInit(); ((System.ruponentModel.ISupportInitialize)(this.dataGridViewCustomers)).EndInit(); ((System.ruponentModel.ISupportInitialize)(this.dataGridViewOrders)).EndInit(); this.groupBox1.ResumeLayout(false); this.groupBox1.PerformLayout(); this.ResumeLayout(false); this.PerformLayout(); } private System.Windows.Forms.DataGridView dataGridViewInventory; private System.Windows.Forms.Label label1; private System.Windows.Forms.Label label2; private System.Windows.Forms.DataGridView dataGridViewCustomers; private System.Windows.Forms.Label label3; private System.Windows.Forms.DataGridView dataGridViewOrders; private System.Windows.Forms.Button btnUpdateDatabase; private System.Windows.Forms.Button btnGetOrderInfo; private System.Windows.Forms.TextBox txtCustID; private System.Windows.Forms.Label label4; private System.Windows.Forms.GroupBox groupBox1; [STAThread] static void Main() { Application.EnableVisualStyles(); Application.Run(new MainForm()); } }</source>
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;"; DataSet ds = new DataSet( ); SqlDataAdapter da; da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.TableMappings.Add("Table", "SalesOrderHeader"); da.TableMappings.Add("Table1", "SalesOrderDetail"); da.Fill(ds); DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail", ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"], ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]); ds.Relations.Add(dr); for (int i = 0; i < 2; i++){ DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i]; Console.WriteLine("HEADER: OrderID = {0}, CustomerID = {1}",rowHeader["SalesOrderID"],rowHeader["CustomerID"]); foreach (DataRow rowDetail in rowHeader.GetChildRows(dr)) { Console.WriteLine(rowDetail["SalesOrderID"]); Console.WriteLine(rowDetail["SalesOrderDetailID"]); Console.WriteLine(rowDetail["LineTotal"]); Console.WriteLine(rowDetail.GetParentRow(dr)["CustomerID"]); } } } }</source>
Read schema and reload data with DataSet
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class SaveDataSet {
private static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; public static void Main() { string SQL = "SELECT CategoryID, CategoryName,Description FROM Categories"; SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(com); DataSet ds = new DataSet("Nortwind"); con.Open(); adapter.FillSchema(ds, SchemaType.Mapped, "Categories"); adapter.Fill(ds, "Categories"); con.Close(); ds.WriteXmlSchema("mydata.xsd"); ds.WriteXml("mydata.xml"); ds.Reset();
ds.ReadXmlSchema("mydata.xsd"); ds.ReadXml("mydata.xml"); Console.WriteLine(ds.GetXml()); }
}</source>
Retrieving Schema and Constraints for a DataSet
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT ContactID,FirstName, LastName FROM Person.Contact"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataSet ds = new DataSet( ); da.Fill(ds, "Contact"); Console.WriteLine("GetXmlSchema( ) = {0}", ds.GetXmlSchema( )); DataSet dsSchema = new DataSet( ); da.FillSchema(dsSchema, SchemaType.Source, "Person.Contact"); da.Fill(dsSchema, "Contact"); Console.WriteLine(dsSchema.Tables.Count); Console.WriteLine("GetXmlSchema( ) = {0}", dsSchema.GetXmlSchema( )); } }</source>
Simple Query Dataset
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; class MainClass {
[STAThread] static void Main(string[] args) { DataSet dset = new DataSet(); string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; using ( SqlConnection conn = new SqlConnection( cstr ) ) { conn.Open(); SqlDataAdapter da = new SqlDataAdapter( "select * from Employee", conn ); da.Fill( dset, "Employee" ); } foreach( DataRow dr in dset.Tables[ "Employee" ].Rows ) { System.Console.WriteLine( "{0}", dr[ "firstName" ] ); } }
}</source>