Csharp/C Sharp/Database ADO.net/DataGrid
Содержание
<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.Configuration; using System.Data.SqlClient; public class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void getData_Click(object sender, EventArgs e) {
string orders = "SELECT * FROM Orders";
string customers = "SELECT * FROM Customers";
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["northwind"].ConnectionString)) {
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(customers, con);
da.Fill(ds, "Customers");
da = new SqlDataAdapter(orders, con);
da.Fill(ds, "Orders");
ds.Relations.Add("CustomerOrders",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
dataGrid1.SetDataBinding(ds, "Customers");
}
}
private void InitializeComponent() {
this.getData = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
this.getData.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
this.getData.Location = new System.Drawing.Point(316, 299);
this.getData.Name = "getData";
this.getData.Size = new System.Drawing.Size(75, 23);
this.getData.TabIndex = 1;
this.getData.Text = "Get Data";
this.getData.UseVisualStyleBackColor = true;
this.getData.Click += new System.EventHandler(this.getData_Click);
this.dataGrid1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(12, 12);
this.dataGrid1.Size = new System.Drawing.Size(379, 281);
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(403, 334);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.getData);
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
private System.Windows.Forms.Button getData;
private System.Windows.Forms.DataGrid dataGrid1;
[STAThread]
static void Main() {
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
</source>
Fill a DataGrid
<source lang="csharp"> using System; using System.Diagnostics; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class Form1 : System.Windows.Forms.Form {
internal System.Windows.Forms.DataGrid DataGrid1;
internal System.Windows.Forms.Button btnRunQuery;
private System.Windows.Forms.Button btnRunQuery2;
public Form1() {
this.DataGrid1 = new System.Windows.Forms.DataGrid();
this.btnRunQuery = new System.Windows.Forms.Button();
this.btnRunQuery2 = new System.Windows.Forms.Button();
((System.ruponentModel.ISupportInitialize)(this.DataGrid1)).BeginInit();
this.SuspendLayout();
this.DataGrid1.DataMember = "";
this.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.DataGrid1.Location = new System.Drawing.Point(8, 8);
this.DataGrid1.Size = new System.Drawing.Size(280, 200);
this.btnRunQuery.Location = new System.Drawing.Point(8, 224);
this.btnRunQuery.Size = new System.Drawing.Size(80, 24);
this.btnRunQuery.Text = "Run Query 1";
this.btnRunQuery.Click += new System.EventHandler(this.btnRunQuery_Click);
this.btnRunQuery2.Location = new System.Drawing.Point(104, 224);
this.btnRunQuery2.Size = new System.Drawing.Size(80, 24);
this.btnRunQuery2.Text = "Run Query 2";
this.btnRunQuery2.Click += new System.EventHandler(this.btnRunQuery2_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btnRunQuery2,
this.DataGrid1,
this.btnRunQuery});
((System.ruponentModel.ISupportInitialize)(this.DataGrid1)).EndInit();
this.ResumeLayout(false);
}
[STAThread]
static void Main() {
Application.Run(new Form1());
}
private void btnRunQuery_Click(object sender, System.EventArgs e) {
try {
SqlConnection cn = new SqlConnection("data source=.;database=biblio;uid=admin;pwd=pw");
SqlDataAdapter da = new SqlDataAdapter("Select top 50 Author, Year_born from Authors Where Year_born is not null", cn);
DataSet ds = new DataSet();
da.Fill(ds, "Authors");
DataGrid1.DataSource = ds.Tables["Authors"];
} catch (SqlException ex) {
Debug.WriteLine(ex.ToString());
}
}
private void btnRunQuery2_Click(object sender, System.EventArgs e) {
try {
SqlConnection cn = new SqlConnection("data source=.;database=biblio;uid=admin;pwd=pw");
SqlDataAdapter da = new SqlDataAdapter("Select Title, Price from Titles where Title like "Hit%"", cn);
DataSet ds = new DataSet();
da.Fill(ds, "Titles and Price");
DataGrid1.DataSource = ds.Tables["Titles and Price"];
} catch (SqlException ex) {
Debug.WriteLine(ex.ToString());
}
}
}
</source>
Set Data Binding with DataViewManager
<source lang="csharp">
using System; using System.Configuration; using System.Data; using System.Data.rumon; using System.Data.SqlClient; using System.Windows.Forms; class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void getData_Click(object sender, EventArgs e) {
string orders = "SELECT * FROM Orders";
string customers = "SELECT * FROM Customers";
using (SqlConnection con = new SqlConnection(ConfigurationSettings.ConnectionStrings["northwind"].ConnectionString)) {
SqlDataAdapter da = new SqlDataAdapter(orders, con);
DataSet ds = new DataSet();
da.Fill(ds, "Orders");
da = new SqlDataAdapter(customers, con);
da.Fill(ds, "Customers");
ds.Relations.Add("CustomerOrders",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
DataViewManager dvm = new DataViewManager(ds);
dvm.DataViewSettings["Customers"].RowFilter = "Country="UK"";
dataGrid.SetDataBinding(dvm, "Customers");
}
}
private void InitializeComponent() {
this.getData = new System.Windows.Forms.Button();
this.dataGrid = new System.Windows.Forms.DataGrid();
this.SuspendLayout();
//
// getData
//
this.getData.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
this.getData.Location = new System.Drawing.Point(684, 558);
this.getData.Name = "getData";
this.getData.TabIndex = 0;
this.getData.Text = "Get Data";
this.getData.Click += new System.EventHandler(this.getData_Click);
//
// dataGrid
//
this.dataGrid.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.dataGrid.Location = new System.Drawing.Point(13, 13);
this.dataGrid.Name = "dataGrid";
this.dataGrid.Size = new System.Drawing.Size(745, 534);
this.dataGrid.TabIndex = 1;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(771, 593);
this.Controls.Add(this.dataGrid);
this.Controls.Add(this.getData);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);
}
private System.Windows.Forms.Button getData;
private System.Windows.Forms.DataGrid dataGrid;
[STAThread]
static void Main() {
Application.EnableVisualStyles();
Application.Run(new Form1());
}
}
</source>
Set DataSource from 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.SqlClient;
public class Form1 : System.Windows.Forms.Form {
const string strConnect = "data source=\".\";database="biblio";uid=\"admin\";pwd=pw";
string strQuery = "Select Title, Price from Titles where Title like "Hit%"";
SqlConnection cn = new SqlConnection(strConnect); // = new SqlDataAdapter();
DataSet ds = new DataSet();
internal System.Windows.Forms.Button Button1;
internal System.Windows.Forms.DataGrid DataGrid1;
private System.Data.SqlClient.SqlDataAdapter da;
public Form1() {
this.Button1 = new System.Windows.Forms.Button();
this.DataGrid1 = new System.Windows.Forms.DataGrid();
this.da = new System.Data.SqlClient.SqlDataAdapter();
((System.ruponentModel.ISupportInitialize)(this.DataGrid1)).BeginInit();
this.SuspendLayout();
this.Button1.Location = new System.Drawing.Point(168, 266);
this.Button1.Size = new System.Drawing.Size(96, 32);
this.Button1.Text = "Get Data";
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.DataGrid1.DataMember = "";
this.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.DataGrid1.Location = new System.Drawing.Point(8, 18);
this.DataGrid1.Size = new System.Drawing.Size(392, 232);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(408, 317);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.Button1,
this.DataGrid1});
this.Load += new System.EventHandler(this.Form1_Load);
((System.ruponentModel.ISupportInitialize)(this.DataGrid1)).EndInit();
this.ResumeLayout(false);
}
[STAThread]
static void Main() {
Application.Run(new Form1());
}
private void Form1_Load(object sender, System.EventArgs e) {
try {
cn.Open();
da.SelectCommand = new SqlCommand(strQuery, cn);
} catch (SqlException ex) {
Console.WriteLine(ex.ToString());
}
}
private void Button1_Click(object sender, System.EventArgs e) {
da.Fill(ds, "Titles and Price");
DataGrid1.DataSource = ds.Tables["Titles and Price"];
}
}
</source>
Set SelectCommand
<source lang="csharp"> using System; using System.Diagnostics; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class Form1 : System.Windows.Forms.Form {
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox txtStateWanted;
private System.Windows.Forms.Button btnFind;
private System.Windows.Forms.DataGrid dataGrid1;
public Form1() {
this.label1 = new System.Windows.Forms.Label();
this.txtStateWanted = new System.Windows.Forms.TextBox();
this.btnFind = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
this.label1.Location = new System.Drawing.Point(62, 241);
this.label1.Size = new System.Drawing.Size(32, 23);
this.label1.Text = "State";
this.txtStateWanted.Location = new System.Drawing.Point(102, 241);
this.txtStateWanted.Size = new System.Drawing.Size(64, 20);
this.txtStateWanted.Text = "CA";
this.btnFind.Location = new System.Drawing.Point(206, 241);
this.btnFind.Text = "Fill";
this.btnFind.Click += new System.EventHandler(this.btnFind_Click);
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(6, 9);
this.dataGrid1.Size = new System.Drawing.Size(280, 224);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.label1,
this.txtStateWanted,
this.btnFind,
this.dataGrid1});
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
[STAThread]
static void Main() {
Application.Run(new Form1());
}
private void btnFind_Click(object sender, System.EventArgs e) {
try {
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT pubid, pubname, city, state FROM publishers where state = @State", "data source=.;database=biblio;uid=admin;pwd=pw");
da.SelectCommand.Parameters.Add("@State", txtStateWanted.Text);
da.Fill(ds, "PublishersIn" + txtStateWanted.Text);
dataGrid1.DataSource = ds.Tables[0];
} catch (SqlException sex) {
Debug.WriteLine(sex.ToString());
}
}
}
</source>
Set up relation between two tables
<source lang="csharp"> using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class Form1 : System.Windows.Forms.Form {
private System.Windows.Forms.DataGrid grdOrders;
private System.Windows.Forms.DataGrid grdOrderDetails;
private System.Windows.Forms.DataGrid grdCustomers;
public Form1() {
SqlConnection cn = new SqlConnection(@"data source=(local);uid=sa;password=;database=northwind");
DataSet ds = new DataSet("CustOrders");
SqlDataAdapter daCust = new SqlDataAdapter("select * from customers;select * from orders;select * from [order details]", cn);
daCust.Fill(ds);
ds.Relations.Add("CustOrder", ds.Tables["Table"].Columns["customerid"], ds.Tables["Table1"].Columns["customerid"]);
ds.Relations.Add("OrderDetail", ds.Tables["Table1"].Columns["orderid"], ds.Tables["Table2"].Columns["orderid"]);
grdCustomers.DataSource = ds;
grdCustomers.DataMember = "Table";
grdOrders.DataSource = ds;
grdOrders.DataMember = "Table.CustOrder";
grdOrderDetails.DataSource = ds;
grdOrderDetails.DataMember = "Table.CustOrder.OrderDetail";
this.grdCustomers = new System.Windows.Forms.DataGrid();
this.grdOrders = new System.Windows.Forms.DataGrid();
this.grdOrderDetails = new System.Windows.Forms.DataGrid();
((System.ruponentModel.ISupportInitialize)(this.grdCustomers)).BeginInit();
((System.ruponentModel.ISupportInitialize)(this.grdOrders)).BeginInit();
((System.ruponentModel.ISupportInitialize)(this.grdOrderDetails)).BeginInit();
this.SuspendLayout();
//
this.grdCustomers.AllowNavigation = false;
this.grdCustomers.DataMember = "";
this.grdCustomers.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.grdCustomers.Location = new System.Drawing.Point(40, 16);
this.grdCustomers.Name = "grdCustomers";
this.grdCustomers.Size = new System.Drawing.Size(448, 152);
this.grdCustomers.TabIndex = 0;
//
this.grdOrders.AllowNavigation = false;
this.grdOrders.DataMember = "";
this.grdOrders.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.grdOrders.Location = new System.Drawing.Point(40, 176);
this.grdOrders.Name = "grdOrders";
this.grdOrders.Size = new System.Drawing.Size(448, 144);
this.grdOrders.TabIndex = 1;
//
this.grdOrderDetails.DataMember = "";
this.grdOrderDetails.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.grdOrderDetails.Location = new System.Drawing.Point(40, 328);
this.grdOrderDetails.Name = "grdOrderDetails";
this.grdOrderDetails.Size = new System.Drawing.Size(448, 136);
this.grdOrderDetails.TabIndex = 2;
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(528, 483);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.grdOrderDetails,
this.grdOrders,
this.grdCustomers});
this.Name = "Form1";
this.Text = "Form1";
((System.ruponentModel.ISupportInitialize)(this.grdCustomers)).EndInit();
((System.ruponentModel.ISupportInitialize)(this.grdOrders)).EndInit();
((System.ruponentModel.ISupportInitialize)(this.grdOrderDetails)).EndInit();
this.ResumeLayout(false);
}
[STAThread]
static void Main() {
Application.Run(new Form1());
}
}
</source>
