Csharp/C Sharp/Database ADO.net/DataGrid

Содержание

1 Bind related tables
2 Fill a DataGrid
3 Set Data Binding with DataViewManager
4 Set DataSource from DataSet
5 Set SelectCommand
6 Set up relation between two tables

Bind related tables

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

Fill a DataGrid

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

Set Data Binding with DataViewManager

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

Set DataSource from DataSet

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"];
    }
}

Set SelectCommand

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

Set up relation between two tables

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

Csharp/C Sharp/Database ADO.net/DataGrid

Содержание

Bind related tables

Fill a DataGrid

Set Data Binding with DataViewManager

Set DataSource from DataSet

Set SelectCommand

Set up relation between two tables

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация