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(); } } }
Use the ExecuteXmlReader() method to run a SELECT statement that returns XML
using System; using System.Data; using System.Data.SqlClient; using System.Xml; class ExecuteXmlReader { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ID, FirstName, LastName " + "FROM Employee " + "ORDER BY ID " + "FOR XML AUTO"; mySqlConnection.Open(); XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader(); myXmlReader.Read(); while (!myXmlReader.EOF) { Console.WriteLine(myXmlReader.ReadOuterXml()); } myXmlReader.Close(); mySqlConnection.Close(); } }
How to write and read XML files
using System; using System.Data; using System.Data.SqlClient; class WriteAndReadXML { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 2 CustomerID, CompanyName, ContactName, " + "Address " + "FROM Customers " + "ORDER BY CustomerID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close(); myDataSet.WriteXml("myXmlFile.xml"); myDataSet.WriteXml("myXmlFile2.xml", XmlWriteMode.WriteSchema); myDataSet.WriteXmlSchema("myXmlSchemaFile.xml"); myDataSet.Clear(); myDataSet.ReadXml("myXmlFile.xml"); DataTable myDataTable = myDataSet.Tables["Customers"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]); Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]); Console.WriteLine("ContactName = " + myDataRow["ContactName"]); Console.WriteLine("Address = " + myDataRow["Address"]); } } }