using System; using System.Data; using System.Data.SqlClient; class ExecuteDDL { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "CREATE TABLE MyEmployee (" + " ID int CONSTRAINT PK_Persons PRIMARY KEY," + " FirstName nvarchar(15) NOT NULL," + " LastName nvarchar(15) NOT NULL," + " DateOfBirth datetime" + ")"; mySqlConnection.Open(); Console.WriteLine("Creating MyEmployee table"); int result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlCommand.CommandText = "ALTER TABLE MyEmployee " + "ADD EmployerID nchar(5) CONSTRAINT FK_Persons_Customers " + "REFERENCES Employee(ID)"; Console.WriteLine("Altering MyEmployee table"); result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlCommand.CommandText = "DROP TABLE MyEmployee"; Console.WriteLine("Dropping MyEmployee table"); result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlConnection.Close(); } }
Author: coder
Create database through SqlConnection
using System; using System.Data; using System.Data.SqlClient; class CommandExampleCreateDb { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { thisConnection.Open(); nonqueryCommand.CommandText = "CREATE DATABASE MyDb"; Console.WriteLine(nonqueryCommand.CommandText); nonqueryCommand.ExecuteNonQuery(); Console.WriteLine("Database created, now switching"); thisConnection.ChangeDatabase("MyDb"); nonqueryCommand.CommandText = "CREATE TABLE MyJava2sTable (COL1 integer)"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); nonqueryCommand.CommandText = "INSERT INTO MyJava2sTable VALUES (99)"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
SqlConnection connection string
using System; using System.Data.SqlClient; class ConnectToSqlConnection { static void Main(string[] args) { String sConn = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; String sSQL = "select id, firstname, lastname from Employee"; SqlConnection oConn = new SqlConnection(sConn); oConn.Open(); SqlCommand oCmd = new SqlCommand(sSQL, oConn); SqlDataReader oReader = oCmd.ExecuteReader(); int idxID = oReader.GetOrdinal("id"); int idxFirstName = oReader.GetOrdinal("firstname"); int idxLastName = oReader.GetOrdinal("lastname"); while(oReader.Read()) { Console.WriteLine("{0} {1} {2}", oReader.GetValue(idxID), oReader.GetValue(idxFirstName), oReader.GetValue(idxLastName)); } } }
Ole db connection string for SQL Server
using System; using System.Data.OleDb; public class ReadFromOleDb { [STAThread] static void Main(string[] args) { String sConn = "provider=sqloledb;server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; String sSQL = "select id, firstname, lastname from Employee"; OleDbConnection oConn = new OleDbConnection(sConn); oConn.Open(); OleDbCommand oCmd = new OleDbCommand(sSQL, oConn); OleDbDataReader oReader = oCmd.ExecuteReader(); int idxID = oReader.GetOrdinal("id"); int idxFirstName = oReader.GetOrdinal("firstname"); int idxLastName = oReader.GetOrdinal("lastname"); while(oReader.Read()) { Console.WriteLine("{0} {1} {2}", oReader.GetValue(idxID), oReader.GetValue(idxFirstName), oReader.GetValue(idxLastName)); } } }
Navigate the binded data using BindingManagerBase
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; } }
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."); } } }