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.TextBox textBox1; private System.Windows.Forms.TextBox textBox2; private System.Windows.Forms.Button buttonBack; private System.Windows.Forms.Button buttonNext; private System.Data.DataSet dataSet1; private System.ComponentModel.Container components = null; private BindingManagerBase bMgr; public Form1() { InitializeComponent(); } private void InitializeComponent() { this.textBox1 = new System.Windows.Forms.TextBox(); this.textBox2 = new System.Windows.Forms.TextBox(); this.buttonBack = new System.Windows.Forms.Button(); this.buttonNext = new System.Windows.Forms.Button(); this.dataSet1 = new System.Data.DataSet(); ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit(); this.SuspendLayout(); this.textBox1.Location = new System.Drawing.Point(8, 8); this.textBox1.Name = "textBox1"; this.textBox1.Size = new System.Drawing.Size(160, 20); this.textBox1.TabIndex = 0; this.textBox1.Text = "textBox1"; this.textBox2.Location = new System.Drawing.Point(8, 40); this.textBox2.Name = "textBox2"; this.textBox2.Size = new System.Drawing.Size(160, 20); this.textBox2.TabIndex = 1; this.textBox2.Text = "textBox2"; this.buttonBack.Location = new System.Drawing.Point(24, 80); this.buttonBack.Name = "buttonBack"; this.buttonBack.Size = new System.Drawing.Size(56, 23); this.buttonBack.TabIndex = 2; this.buttonBack.Text = "<< Back"; this.buttonBack.Click += new System.EventHandler(this.buttonBack_Click); this.buttonNext.Location = new System.Drawing.Point(96, 80); this.buttonNext.Name = "buttonNext"; this.buttonNext.Size = new System.Drawing.Size(56, 23); this.buttonNext.TabIndex = 3; this.buttonNext.Text = "Next>>"; this.buttonNext.Click += new System.EventHandler(this.buttonNext_Click); this.dataSet1.DataSetName = "NewDataSet"; this.dataSet1.Locale = new System.Globalization.CultureInfo("en-US"); this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(176, 108); this.Controls.Add(this.buttonNext); this.Controls.Add(this.buttonBack); this.Controls.Add(this.textBox2); this.Controls.Add(this.textBox1); this.Name = "Form1"; this.Text = "Form1"; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit(); this.ResumeLayout(false); } static void Main() { Application.Run(new Form1()); } private void Form1_Load(object sender, System.EventArgs e) { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql = @"select * from employee "; SqlConnection conn = new SqlConnection(connString); SqlDataAdapter da = new SqlDataAdapter(sql, conn); da.Fill(dataSet1, "employee"); textBox1.DataBindings.Add("text", dataSet1, "employee.firstname"); textBox2.DataBindings.Add("text", dataSet1, "employee.lastname"); bMgr = this.BindingContext[dataSet1, "employee"]; } private void buttonNext_Click(object sender, System.EventArgs e) { bMgr.Position += 1; } private void buttonBack_Click(object sender, System.EventArgs e) { bMgr.Position -= 1; } }
Database ADO.net
Using ADO.NET Events
/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { public class UsingADONETEvents { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); MyConnection.StateChange += new StateChangeEventHandler(OnStateChange); MyConnection.Open(); //Trigger Open Event MyConnection.Close(); } public static void OnStateChange(object sender, System.Data.StateChangeEventArgs e) { Console.WriteLine("Connection State Chnaged: {0}", ((SqlConnection)sender).State); } } }
Connect to an Access database file
using System; using System.Data; using System.Data.OleDb; class AccessConnect { static void Main() { string connString = @"provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;"; OleDbConnection conn = new OleDbConnection(connString); try { conn.Open(); Console.WriteLine("Connection opened."); Console.WriteLine("Connection Properties:"); Console.WriteLine(" Connection String: {0}", conn.ConnectionString); Console.WriteLine(" Database: {0}",conn.Database); Console.WriteLine(" DataSource: {0}",conn.DataSource); Console.WriteLine(" ServerVersion: {0}", conn.ServerVersion); Console.WriteLine(" State: {0}", conn.State); } catch (OleDbException e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); Console.WriteLine("Connection closed."); } } }
Read query result data from Access database
using System; using System.Data; using System.Data.OleDb; class CommandOleDbQuery { static void Main() { OleDbConnection thisConnection = new OleDbConnection("provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;"); OleDbCommand thisCommand = new OleDbCommand("SELECT ID, FirstName FROM Employee",thisConnection); try { thisConnection.Open(); OleDbDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine("Product ID and Name: {0} {1}", thisReader.GetValue(0), thisReader.GetValue(1)); } } catch (OleDbException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
Use OdbcCommand to read data in Access database and fill the 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.Odbc; public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.Button button1; private System.Windows.Forms.DataGrid dataGrid1; private System.Windows.Forms.Button button2; public Form1() { this.button1 = new System.Windows.Forms.Button(); this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.button2 = new System.Windows.Forms.Button(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); this.button1.Location = new System.Drawing.Point(8, 240); this.button1.Size = new System.Drawing.Size(104, 32); this.button1.Text = "Get Data From Biblio"; 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, 8); this.dataGrid1.Size = new System.Drawing.Size(280, 224); this.button2.Location = new System.Drawing.Point(120, 240); this.button2.Size = new System.Drawing.Size(152, 32); this.button2.Text = "Get Data From Access Northwind"; this.button2.Click += new System.EventHandler(this.button2_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.button2, this.dataGrid1, this.button1}); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } [STAThread] static void Main() { Application.Run(new Form1()); } private OdbcConnection cn; private void button1_Click(object sender, System.EventArgs e) { try { cn = new OdbcConnection("DRIVER={SQL Server};SERVER=.;uid=admin;pwd=pw;database=biblio"); cn.Open(); OdbcCommand sc = new OdbcCommand("SELECT title, price FROM titles WHERE title LIKE 'Hi%'", cn); OdbcDataAdapter da = new OdbcDataAdapter(sc); DataSet ds = new DataSet("TitlesDS"); da.Fill(ds); dataGrid1.DataSource = ds.Tables[0]; } catch (Exception ex) { Debug.WriteLine(ex.ToString()); } finally { cn.Close(); } } private void button2_Click(object sender, System.EventArgs e) { try { cn = new OdbcConnection(@"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:NorthWind.mdb"); cn.Open(); OdbcCommand sc = new OdbcCommand("SELECT * from Employees", cn); OdbcDataAdapter da = new OdbcDataAdapter(sc); DataSet ds = new DataSet("TitlesDS"); da.Fill(ds); dataGrid1.DataSource = ds.Tables[0]; } catch (Exception ex) { MessageBox.Show("You probably need to change the file path to NorthWind.MDB used in the OdbcConnection Constructor or move a copy of the NorthWind.mdb to c:"); Debug.WriteLine(ex.ToString()); } finally { cn.Close(); } } }
How to use an OleDbConnection object to connect to an Access database
using System; using System.Data; using System.Data.OleDb; class OleDbConnectionAccess { public static void Main() { string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=.Northwind.mdb"; OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); myOleDbCommand.CommandText = "SELECT CustomerID, CompanyName, ContactName, Address "+ "FROM Customers "+ "WHERE CustomerID = 'ALFKI'"; myOleDbConnection.Open(); OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader(); myOleDbDataReader.Read(); Console.WriteLine("myOleDbDataReader[" CustomerID"] = "+ myOleDbDataReader["CustomerID"]); Console.WriteLine("myOleDbDataReader[" CompanyName"] = "+ myOleDbDataReader["CompanyName"]); Console.WriteLine("myOleDbDataReader[" ContactName"] = "+ myOleDbDataReader["ContactName"]); Console.WriteLine("myOleDbDataReader[" Address"] = "+ myOleDbDataReader["Address"]); myOleDbDataReader.Close(); myOleDbConnection.Close(); } }
Reading Access Data
using System; using System.Data; using System.Data.OleDb; using System.Collections.Generic; using System.Text; class Program { static void Main(string[] args) { OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: wind.mdb"); thisConnection.Open(); OleDbCommand thisCommand = thisConnection.CreateCommand(); thisCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers"; OleDbDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine(" {0} {1}",thisReader["CustomerID"], thisReader["CompanyName"]); } thisReader.Close(); thisConnection.Close(); } }