using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
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());
}
}
Database ADO.net
Bind related tables
using System;
using System.Collections.Generic;
using System.ComponentModel;
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.ComponentModel.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.ComponentModel.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());
}
}
Set SelectCommand
using System;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
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.ComponentModel.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.ComponentModel.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());
}
}
}
Fill a DataGrid
using System;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
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.ComponentModel.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.ComponentModel.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());
}
}
}
Scrolling Data Binding
using System;
using System.Collections.Generic;
using System.ComponentModel;
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 retrieve_Click(object sender, EventArgs e) {
retrieve.Enabled = false;
ds = CreateDataSet();
textName.DataBindings.Add("Text", ds, "Products.ProductName");
textQuan.DataBindings.Add("Text", ds, "Products.QuantityPerUnit");
trackBar.Minimum = 0;
trackBar.Maximum = this.BindingContext[ds, "Products"].Count - 1;
textName.Enabled = true;
textQuan.Enabled = true;
trackBar.Enabled = true;
}
private DataSet CreateDataSet() {
string customers = "SELECT * FROM Products";
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["northwind"].ConnectionString)) {
SqlDataAdapter da = new SqlDataAdapter(customers, con);
da.Fill(ds, "Products");
}
return ds;
}
private void trackBar_Scroll(object sender, EventArgs e) {
this.BindingContext[ds, "Products"].Position = trackBar.Value;
}
private DataSet ds;
private void InitializeComponent() {
this.retrieve = new System.Windows.Forms.Button();
this.textName = new System.Windows.Forms.TextBox();
this.textQuan = new System.Windows.Forms.TextBox();
this.trackBar = new System.Windows.Forms.TrackBar();
((System.ComponentModel.ISupportInitialize)(this.trackBar)).BeginInit();
this.SuspendLayout();
this.retrieve.Location = new System.Drawing.Point(12, 12);
this.retrieve.Size = new System.Drawing.Size(75, 23);
this.retrieve.Text = "Retrieve";
this.retrieve.UseVisualStyleBackColor = true;
this.retrieve.Click += new System.EventHandler(this.retrieve_Click);
this.textName.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.textName.Enabled = false;
this.textName.Location = new System.Drawing.Point(12, 42);
this.textName.Size = new System.Drawing.Size(268, 20);
this.textQuan.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.textQuan.Enabled = false;
this.textQuan.Location = new System.Drawing.Point(13, 69);
this.textQuan.Size = new System.Drawing.Size(267, 20);
this.trackBar.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.trackBar.Enabled = false;
this.trackBar.Location = new System.Drawing.Point(13, 96);
this.trackBar.Size = new System.Drawing.Size(267, 45);
this.trackBar.Scroll += new System.EventHandler(this.trackBar_Scroll);
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(292, 148);
this.Controls.Add(this.trackBar);
this.Controls.Add(this.textQuan);
this.Controls.Add(this.textName);
this.Controls.Add(this.retrieve);
this.Name = "Form1";
this.Text = "ScrollingDataBinding";
((System.ComponentModel.ISupportInitialize)(this.trackBar)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
private System.Windows.Forms.Button retrieve;
private System.Windows.Forms.TextBox textName;
private System.Windows.Forms.TextBox textQuan;
private System.Windows.Forms.TrackBar trackBar;
[STAThread]
static void Main() {
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
Execute your Sql query
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Queries : System.Windows.Forms.Form {
private System.Windows.Forms.TextBox txtResult;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Button cmdExecute;
private System.Windows.Forms.TextBox txtSql;
private System.ComponentModel.Container components = null;
public Queries() {
InitializeComponent();
}
private void InitializeComponent() {
this.txtSql = new System.Windows.Forms.TextBox();
this.txtResult = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.cmdExecute = new System.Windows.Forms.Button();
this.SuspendLayout();
this.txtSql.Location = new System.Drawing.Point(0, 32);
this.txtSql.Multiline = true;
this.txtSql.Name = "txtSql";
this.txtSql.Size = new System.Drawing.Size(400, 72);
this.txtSql.TabIndex = 0;
this.txtSql.Text = "";
this.txtResult.Location = new System.Drawing.Point(0, 184);
this.txtResult.Multiline = true;
this.txtResult.Name = "txtResult";
this.txtResult.Size = new System.Drawing.Size(400, 88);
this.txtResult.TabIndex = 1;
this.txtResult.Text = "";
this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.label1.Location = new System.Drawing.Point(8, 8);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(384, 16);
this.label1.TabIndex = 2;
this.label1.Text = "Type a SQL statement in the text box.";
this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
this.label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.label2.Location = new System.Drawing.Point(0, 160);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(392, 16);
this.label2.TabIndex = 3;
this.label2.Text = "Execution Result";
this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
this.cmdExecute.Location = new System.Drawing.Point(152, 112);
this.cmdExecute.Name = "cmdExecute";
this.cmdExecute.Size = new System.Drawing.Size(104, 32);
this.cmdExecute.TabIndex = 4;
this.cmdExecute.Text = "Execute Command";
this.cmdExecute.Click += new System.EventHandler(this.cmdExecute_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(400, 275);
this.Controls.Add(this.cmdExecute);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.txtResult);
this.Controls.Add(this.txtSql);
this.Name = "Queries";
this.Text = "Tables and Relationships";
this.ResumeLayout(false);
}
private void cmdExecute_Click(object sender, System.EventArgs e) {
try{
SqlConnection conn = new SqlConnection(@"server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
conn.Open();
string strSQL=txtSql.Text;
SqlCommand cmd= new SqlCommand(strSQL, conn);
cmd.ExecuteReader();
conn.Close();
txtResult.Text = "SQL executed successfully.";
} catch (System.Data.SqlClient.SqlException ex) {
txtResult.Text =
"There was an error in executing the SQL. " +
"Error Message:" + ex.Message;
}
}
static void Main() {
Application.Run(new Queries());
}
}
Write data in database table to XML file
using System;
using System.Data;
using System.Data.SqlClient;
class WriteXML {
static void Main(){
string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string qry = @"select * from employee";
SqlConnection conn = new SqlConnection(connString);
try{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds, "employee");
ds.WriteXml(@"employee.xml");
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
}
}
}