using System; using System.Data; using System.Data.SqlClient; class Class1{ public static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlDataAdapter thisAdapter = new SqlDataAdapter( "SELECT ID, FirstName FROM Employee", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Employee"); Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Employee"].Rows.Count); DataRow thisRow = thisDataSet.Tables["Employee"].NewRow(); thisRow["ID"] = "123"; thisRow["FirstName"] = "Ltd"; thisDataSet.Tables["Employee"].Rows.Add(thisRow); Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Employee"].Rows.Count); thisAdapter.Update(thisDataSet, "Employee"); } }
Database ADO.net
Create connection string with SqlConnectionStringBuilder
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
class Program {
static void Main(string[] args) {
SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder();
cnStrBuilder.UserID = “sa”;
cnStrBuilder.Password = “”;
cnStrBuilder.InitialCatalog = “Cars”;
cnStrBuilder.DataSource = “(local)”;
cnStrBuilder.ConnectTimeout = 5;
SqlConnection cn = new SqlConnection();
cn.ConnectionString = cnStrBuilder.ConnectionString;
cn.Open();
ShowConnectionStatus(cn);
string strSQL = “SELECT * FROM Inventory”;
SqlCommand myCommand = new SqlCommand(strSQL, cn);
SqlDataReader myDataReader;
myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (myDataReader.Read()) {
for (int i = 0; i < myDataReader.FieldCount; i++) {
Console.Write("{0} = {1} ",myDataReader.GetName(i),myDataReader.GetValue(i).ToString().Trim());
}
}
myDataReader.Close();
ShowConnectionStatus(cn);
}
private static void ShowConnectionStatus(DbConnection cn) {
Console.WriteLine("Database location: {0}", cn.DataSource);
Console.WriteLine("Database name: {0}", cn.Database);
Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout);
Console.WriteLine("Connection state: {0}
", cn.State.ToString());
}
}
[/csharp]
Create SqlCommand object
using System; using System.Data; using System.Data.SqlClient; class CommandExampleSql { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand thisCommand = new SqlCommand(); Console.WriteLine("Command created."); try { thisConnection.Open(); thisCommand.Connection = thisConnection; thisCommand.CommandText = "SELECT COUNT(*) FROM Employee"; Console.WriteLine("Ready to execute SQL command: {0}", thisCommand.CommandText); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
Creating SQL Connections
/* * 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.SqlClient; namespace Client.Chapter_13___ADO.NET { public class CreatingSQLConnections { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection("Data Source=(local); Initial Catalog = MyDatabase; User ID=sa;Password="); MyConnection.Open(); } } }
Connect to Access database
// connect to Access database /* * 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.OleDb; namespace Client.Chapter_13___ADO.NET { public class ConnectingToAccess { static void Main(string[] args) { OleDbConnection MyConnection = new OleDbConnection ( @"Provider=Microsft.Jet.OLEDB.4.0; Data Source = c:MyAccessDB.mdb"); MyConnection.Open(); } } }
SqlConnection: info message event handler
using System; using System.Data; using System.Data.SqlClient; class SqlDemo { static void Main(){ string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; SqlConnection cn = new SqlConnection(connString); cn.InfoMessage += new SqlInfoMessageEventHandler(CnInfoMessage); cn.StateChange += new StateChangeEventHandler(CnStateChange); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT TOP 2 Id FROM Employee"; cmd.Connection = cn; try { cn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read()) { Console.WriteLine(dr.GetInt32(0)); } dr.Close(); cmd.CommandText = "PRINT 'Get Id for all Employees'"; cmd.ExecuteNonQuery(); } catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { cn.Close(); } } static void CnInfoMessage(object sender, SqlInfoMessageEventArgs ev) { foreach (SqlError err in ev.Errors) { Console.WriteLine("Entering InfoMessage Event Handler"); Console.WriteLine("Source- " + err.Source); Console.WriteLine("State- " + err.State); Console.WriteLine("Number- " + err.Number); Console.WriteLine("Procedure- " + err.Procedure); Console.WriteLine("Server- " + err.Server); Console.WriteLine("Message- " + err.Message); Console.WriteLine("Exiting InfoMessage Event Handler"); } } static void CnStateChange(object sender, StateChangeEventArgs ev) { Console.WriteLine("Entering StateChange EventHandler"); Console.WriteLine("Sender = "+ sender.ToString()); Console.WriteLine("Original State = "+ ev.OriginalState.ToString()); Console.WriteLine("Current State = "+ ev.CurrentState.ToString()); Console.WriteLine("Exiting StateChange EventHandler"); } }
SqlConnection state change event
using System; using System.Data; using System.Data.SqlClient; class SqlDemo { static void Main(){ string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; SqlConnection cn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT TOP 1 Id, FirstName FROM Employee"; cmd.Connection = cn; cn.StateChange += new StateChangeEventHandler(CnStateChange); try { cn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read()) { Console.WriteLine(dr.GetString(0) + "-" + dr.GetString(1)); } } catch(SqlException e1) { Console.WriteLine(e1.Message); } finally { cn.Close(); } } static void CnStateChange(object sender, StateChangeEventArgs ev) { Console.WriteLine("------------------------------"); Console.WriteLine("Entering StateChange EventHandler"); Console.WriteLine("Sender = "+ sender.ToString()); Console.WriteLine("Original State = "+ ev.OriginalState.ToString()); Console.WriteLine("Current State = "+ ev.CurrentState.ToString()); Console.WriteLine("Exiting StateChange EventHandler"); Console.WriteLine("------------------------------"); } }