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.StateChange += new StateChangeEventHandler(CnStateChange);
cn.StateChange += new StateChangeEventHandler(CnStateChange2);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT LastName, FirstName FROM Employee";
cmd.Connection = cn;
try
{
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
Console.WriteLine(dr.GetString(0) + "-" + dr.GetString(1));
}
}
catch(SqlException ex)
{
Console.WriteLine (ex.Message);
}
finally
{
cn.Close();
}
}
static void CnStateChange2(object sender, StateChangeEventArgs ev)
{
Console.WriteLine("------------------------------");
Console.WriteLine("Entering Second Statechange EventHandler");
Console.WriteLine("Sender = " + sender.ToString());
Console.WriteLine("Original State = " + ev.OriginalState.ToString());
Console.WriteLine("Current State = " + ev.CurrentState.ToString());
Console.WriteLine("Exiting Second StateChange EventHandler");
Console.WriteLine("------------------------------");
}
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("------------------------------");
}
}
Author: coder
On row updating and updated 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);
try
{
cn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, 0, 1, "Employee");
da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
DataTable dt = ds.Tables["Employee"];
dt.Rows[0][1] = "T";
da.Update(ds, "Employee");
da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);
} catch (SqlException ex) {
Console.WriteLine(ex.Message);
}
finally
{
cn.Close();
}
}
static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
Console.WriteLine("OnRowUpdating event");
if (e.Status != UpdateStatus.Continue)
Console.WriteLine("RowStatus = " + e.Status.ToString());
}
static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
Console.WriteLine("OnRowUpdating event");
if (e.Status != UpdateStatus.Continue)
Console.WriteLine("RowStatus = " + e.Status.ToString());
}
}
How to use the InfoMessage event
using System;
using System.Data;
using System.Data.SqlClient;
class InfoMessage
{
public static void InfoMessageHandler(object mySender, SqlInfoMessageEventArgs myEvent)
{
Console.WriteLine("The following message was produced:
" + myEvent.Errors[0]);
}
public static void Main(){
SqlConnection mySqlConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
mySqlConnection.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
mySqlConnection.Open();
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "PRINT 'This is the message from the PRINT statement'";
mySqlCommand.ExecuteNonQuery();
mySqlCommand.CommandText = "RAISERROR('This is the message from the RAISERROR statement', 10, 1)";
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
}
}
How to use the StateChange event
using System;
using System.Data;
using System.Data.SqlClient;
class StateChange
{
public static void StateChangeHandler(object mySender, StateChangeEventArgs myEvent)
{
Console.WriteLine("mySqlConnection State has changed from "+myEvent.OriginalState + "to "+myEvent.CurrentState);
}
public static void Main(){
SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
mySqlConnection.StateChange +=new StateChangeEventHandler(StateChangeHandler);
Console.WriteLine("Calling mySqlConnection.Open()");
mySqlConnection.Open();
Console.WriteLine("Calling mySqlConnection.Close()");
mySqlConnection.Close();
}
}
SQL SERVER TYPES, COMPATIBLE Sql* TYPES, AND GetSql* METHODS
SQL SERVER TYPE Sql* TYPE GetSql* METHOD
bigint SqlInt64 GetSqlInt64()
int SqlInt32 GetSqlInt32()
smallint SqlInt16 GetSqlInt16()
tinyint SqlByte GetSqlByte()
bit SqlBoolean GetSqlBoolean()
decimal SqlDecimal GetSqlDecimal()
numeric SqlDecimal GetSqlDecimal()
money SqlMoney GetSqlMoney()
smallmoney SqlMoney GetSqlMoney()
float SqlDouble GetSqlDouble()
real SqlSingle GetSqlSingle()
datetime SqlDateTime GetSqlDateTime()
smalldatetime SqlDateTime GetSqlDateTime()
char SqlString GetSqlString()
varchar SqlString GetSqlString()
text SqlString GetSqlString()
nchar SqlString GetSqlString()
nvarchar SqlString GetSqlString()
ntext SqlString GetSqlString()
binary SqlBinary GetSqlBinary()
varbinary SqlBinary GetSqlBinary()
image SqlBinary GetSqlBinary()
sql_varient object GetSqlValue()
timestamp SqlBinary GetSqlBinary()
uniqueidentifier SqlGuid GetSqlGuid()
Shows the SQL server types, the corresponding Sql types, and the GetSql*** methods used to read a column as the Sql type
Sql* TYPE VALUES
===============================================================================
Sql Binary A variable-length string of binary data.
SqlBoolean An integer with either a 1 or 0 value.
SqlByte An 8-bit unsigned integer value between 0 and 2^8 - 1 (255).
SqlDateTime A date and time between 12:00:00 AM January 1, 1753 and 11:59:59 PM December 31, 9999. This is accurate to 3.33 milliseconds.
SqlDecimal Fixed precision and scale numeric value between -1038 + 1 and 1038 - 1.
SqlDouble A 64-bit floating-point number between -1.79769313486232E308 and 1.79769313486232E308 with 15 significant figures of precision.
SqlGuid A 128-bit integer value (16 bytes) that that is unique across all computers and networks.
SqlInt16 A 16-bit signed integer between -2^15 (-32,768) and 2^15 - 1 (32,767).
SqlInt32 A 32-bit signed integer between-2^31 (-2,147,483,648) and 2^31 - 1 (2,147,483,647).
SqlInt64 A 64-bit signed integer between -2^63 (-9,223,372,036,854,775,808) and 2^63 - 1 (9,223,372,036,854,775,807).
SqlMoney A currency value between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. This is accurate to 1/10,000th of a currency unit.
SqlSingle A 32-bit floating-point number between -3.402823E38 and 3.402823E38 with seven significant figures of precision.
SqlString A variable-length string of characters.
how to execute a SELECT statement using a SqlCommand object
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteSelect {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 5 CustomerID, CompanyName, ContactName, Address " +
"FROM Customers " +
"ORDER BY CustomerID";
mySqlConnection.Open();
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
while (mySqlDataReader.Read()) {
Console.WriteLine("mySqlDataReader[" CustomerID"] = " + mySqlDataReader["CustomerID"]);
Console.WriteLine("mySqlDataReader[" CompanyName"] = " + mySqlDataReader["CompanyName"]);
Console.WriteLine("mySqlDataReader[" ContactName"] = " + mySqlDataReader["ContactName"]);
Console.WriteLine("mySqlDataReader[" Address"] = " + mySqlDataReader["Address"]);
}
mySqlDataReader.Close();
mySqlConnection.Close();
}
}