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