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()); } } }
Author: coder
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"]); } } }
Fill Data from database table to ListView
using System;
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.Button cmdExecute;
private System.Windows.Forms.TextBox txtSql;
private System.Windows.Forms.ListView lvwResult;
private System.ComponentModel.Container components = null;
public Form1() {
InitializeComponent();
}
private void InitializeComponent() {
this.label1 = new System.Windows.Forms.Label();
this.cmdExecute = new System.Windows.Forms.Button();
this.txtSql = new System.Windows.Forms.TextBox();
this.lvwResult = new System.Windows.Forms.ListView();
this.SuspendLayout();
this.label1.Location = new System.Drawing.Point(0, 0);
this.label1.Name = “label1”;
this.label1.Size = new System.Drawing.Size(296, 16);
this.label1.TabIndex = 0;
this.label1.Text = “Enter a SQL query or statement and click Execute.”;
this.cmdExecute.Location = new System.Drawing.Point(227, 224);
this.cmdExecute.Name = “cmdExecute”;
this.cmdExecute.TabIndex = 1;
this.cmdExecute.Text = “Execute”;
this.cmdExecute.Click += new System.EventHandler(this.cmdExecute_Click);
this.txtSql.Location = new System.Drawing.Point(0, 16);
this.txtSql.Multiline = true;
this.txtSql.Name = “txtSql”;
this.txtSql.Size = new System.Drawing.Size(528, 200);
this.txtSql.TabIndex = 2;
this.txtSql.Text = “”;
this.lvwResult.GridLines = true;
this.lvwResult.Location = new System.Drawing.Point(0, 256);
this.lvwResult.Name = “lvwResult”;
this.lvwResult.Size = new System.Drawing.Size(528, 200);
this.lvwResult.TabIndex = 3;
this.lvwResult.View = System.Windows.Forms.View.Details;
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(528, 452);
this.Controls.Add(this.lvwResult);
this.Controls.Add(this.txtSql);
this.Controls.Add(this.cmdExecute);
this.Controls.Add(this.label1);
this.Name = “Form1”;
this.Text = “Query Processor”;
this.ResumeLayout(false);
}
static void Main() {
Application.Run(new Form1());
}
private void cmdExecute_Click(object sender, System.EventArgs e) {
SqlConnection conn = new SqlConnection(“server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI”);
try {
lvwResult.Columns.Clear() ;
lvwResult.Items.Clear();
conn.Open();
txtSql.Text =”select * from Employee”;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text;
SqlDataReader dr = cmd.ExecuteReader();
for (int i = 0; i< dr.FieldCount; i++) { ColumnHeader ch = new ColumnHeader(); ch.Text=dr.GetName(i); lvwResult.Columns.Add(ch); } ListViewItem itmX; while (dr.Read()) { itmX=new ListViewItem(); itmX.Text= dr.GetValue(0).ToString(); for (int i=1 ; i< dr.FieldCount; i++) { itmX.SubItems.Add(dr.GetValue(i).ToString()); } lvwResult.Items.Add(itmX); } dr.Close(); } catch ( System.Data.SqlClient.SqlException ex) { Console.WriteLine("There was an error in executing the SQL." + " Error Message:" + ex.Message, "SQL"); } finally { conn.Close(); } } } [/csharp]