SqlCommand Object
- A SqlCommand object allows you to specify what type of interaction you want to perform with a database.
- For example insert, update, delete and get commands on rows in a database table.
- SqlCommand object can be used with the disconnected and connected approach.
Creating a SqlCommand object
SqlCommand cmd = new SqlCommand("Select Category from Categories", conn);
It takes string parameter that holds the command you want to execute and a reference to a SqlConnection object.
Querying Data
When using a SQL select command, you retrieve a data set for viewing.
To get the results send by the query we can use the sqlCommand object's function ExecuteReader which returns the SqlDataReader object.
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
Inserting Data
To insert data into database use the ExecuteNonQuery method of the SqlCommand object.
string insertString = @"
insert into Categories
(CategoryName, Description)
values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
ExecuteNonQuery method returns number of affected records.
Updating data
The executeNonQuery method is also used for updating data.
// prepare command string
string updateString = @"
update Categories
set CategoryName = 'Other'
where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
The executeNonQuery method is also used for updating data.
// prepare command string
string updateString = @"
update Categories
set CategoryName = 'Other'
where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Deleting Data
You can also used ExecuteNonQuery method for deleting data.
// prepare command string
string deleteString = @"
delete from Categories
where CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
This example uses the SqlCommand constructor with no parameters. Instead, it explicitly sets the CommandText and Connection properties of the SqlCommand object, cmd.
Getting Single values
- Sometimes we need single value from the database which could be a count, Sum, average, or other aggregated value.
- For getting single value from the database you can use ExecuteScaler method of the sqlCommand object.
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
Putting it All Together
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Demonstrates how to work with SqlCommand objects
/// </summary>
class SqlCommandDemo
{
SqlConnection conn;
public SqlCommandDemo()
{
// Instantiate the connection
conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
}
// call methods that demo SqlCommand capabilities
static void Main()
{
SqlCommandDemo scd = new SqlCommandDemo();
Console.WriteLine();
Console.WriteLine("Categories Before Insert");
Console.WriteLine("------------------------");
// use ExecuteReader method
scd.ReadData();
// use ExecuteNonQuery method for Insert
scd.Insertdata();
Console.WriteLine();
Console.WriteLine("Categories After Insert");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery method for Update
scd.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories After Update");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery method for Delete
scd.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories After Delete");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteScalar method
int numberOfRecords = scd.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records: {0}", numberOfRecords);
}
/// <summary>
/// use ExecuteReader method
/// </summary>
public void ReadData()
{
SqlDataReader rdr = null;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query results
rdr = cmd.ExecuteReader();
// print the CategoryName of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Insert
/// </summary>
public void Insertdata()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string insertString = @"
insert into Categories
(CategoryName, Description)
values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Update
/// </summary>
public void UpdateData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string updateString = @"
update Categories
set CategoryName = 'Other'
where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Delete
/// </summary>
public void DeleteData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string deleteString = @"
delete from Categories
where CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// use ExecuteScalar method
/// </summary>
/// <returns>number of records</returns>
public int GetNumberOfRecords()
{
int count = -1;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteScalar to send command
count = (int)cmd.ExecuteScalar();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return count;
}
}
No comments:
Post a Comment