C# - The SqlCommand Class

The SqlCommand class is at the heart of the System.SqlClient namespace. It is used to execute operations on a database and retrieve data.

 

Properties and Methods of the SqlCommand Class

Item Description
Properties
CommandText Contains the text of a SQL query
CommandTimeout Contains the length of the timeout of a query, in seconds
CommandType Specifies the type of command to be executed
Connection Specifies the connection to the database
Parameters Specifies a collection of parameters for the SQL query
Transaction Specifies a transaction object, which enables developers to run queries in a transaction
Methods
Cancel() Cancels the running query
CreateParameter() Returns a new SQL parameter
ExecuteNonQuery() Executes the CommandText property against the database and does not return a result set
ExecuteReader() Executes the CommandText property and returns data in a DataReader object
ExecuteScalar() Executes the CommandText property and returns a single value
ExecuteXmlReader() Executes the CommandText property and returns data in an XMLDataReader object
ResetCommandTimeout() Resets the CommandTimeout property for the query
SqlCommand.CommandText

Syntax

String CommandText

Description

The CommandText property is a string that contains the text of a database query.

Example

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customers";
SqlCommand.CommandTimeout

Syntax

Int32 CommandTimeout

Description

The CommandTimeout property contains the number of seconds before the query will timeout. By default, CommandTimeout is set to 30 seconds.

Example

SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 50;
SqlCommand.CommandType

Syntax

CommandType CommandType

Description

The CommandType property contains the type of query being performed. CommandType is defined in the System.Data namespace. The CommandType property can be set to StoreProcedure, TableDirect, or Text.

Example

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
SqlCommand.Connection

Syntax

SqlConnection Connection

Description

The Connection property is set to a valid SqlConnection object against which the query is to be performed. The easiest way to set the connection for the SqlCommand object is to explicitly create a SqlConnection object and assign it to the Connection property.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
      User Id=sa; Password=;Initial Catelog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlCommand.Parameters

Syntax

SqlParameterCollection Parameters

Description

The Parameters property contains the names of a group of SQL parameters that are used to when calling a stored procedure that requires input or output parameters.

Example

//Create and Open Connection
SqlConnection conn = new SqlConnection("Data Source=localhost; 
     UID=sa; PWD=; Initial Catalog=northwind;");
conn.Open();

//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;

//Configure input parameters
SqlParameter param = new SqlParameter();
param = cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 20));
param.Direction = Parameter.Direction.Input;
param.Value = sName.Text;

//Configure output parameters
param = cmd.Parameters.Add(new SqlParameter("@retval", SqlDbType.Int, 4));
param.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
SqlCommand.Transaction

Syntax

SqlTransaction Transaction

Description

The Transaction property contains the transaction object (if any) for the current command set.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
        User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();


SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable, "RemoveCustomers");


cmd = new SqlCommand("", conn);
cmd.Transaction = trans;
SqlCommand.Cancel

Syntax

Void Cancel()

Description

The Cancel() method cancels a running query.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
        User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
cmd.ExecuteNonQuery();
cmd.Cancel();
SqlCommand.CreateParameter()

Syntax

SqlParameter CreateParameter()

Description

The CreateParameter() method returns a new instance of a SQL parameter.

Example

SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerID";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 5;
param.Value = "123";
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
SqlCommand.ExecuteNonQuery()

Syntax

Int32 ExecuteNonQuery()

Description

The ExecuteNonQuery() method executes the command text against the database specified in the Connection object. This method is optimized for queries that do not return any information (for example, DELETE and UPDATE queries).

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand("DELETE FROM Customers 
         WHERE LastName='Jones'", conn);
cmd.ExecuteNonQuery();
SqlCommand.ExecuteReader()

Syntax

SqlDataReader ExecuteReader()
SqlDataReader ExecuteReader( CommandBehavior behavior )

Description

The ExecuteReader() method executes the command text against the database specified in the Connection object and returns a SqlDataReader object with the results of the query.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
          User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);


SqlDataReader reader = cmd.ExecuteReader();
SqlCommand.ExecuteScalar()

Syntax

Object ExecuteScalar()

Description

The ExecuteScalar() method executes the command text against the database specified in the Connection object and returns a single object. The ExecuteScalar() method exists because it is wasteful to return a dataset for a single value. The overhead for the dataset would be much larger than the actual value being returned.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT count(*) FROM Customers", conn);
Int32 customerCount = (Int32)cmd.ExecuteScalar();


msg.Text = "There are "+customerCount.ToString()+" customers in the database.";
SqlCommand.ExecuteXmlReader()

Syntax

XmlReader ExecuteXmlReader()

Description

The ExecuteXmlReader() method executes the command text against the database specified in the Connection object and returns the result, set in an XmlReader object.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();


SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
XmlReader reader = cmd.ExecuteXmlReader();
SqlCommand.ResetCommandTimeout()

Syntax

Void ResetCommandTimeout()

Description

The ResetCommandTimeout() method resets the CommandTimeout property to the default value of 30 seconds.

Example

SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE LastName='Jones'", conn);
cmd.CommandTimeout = 45;
cmd.ResetCommandTimeout();
//CommandTimeout is now reset to 30
 
Visitor Review  Write a review
Search Review :

Divyesh on 10 Jul 2014 09:01 reply

good collection

 

TT on 18 Oct 2013 05:13 reply

Easy to understand to Easy

 

Harpal kaur on 19 Dec 2012 08:38 reply

hi,this is very easy and simple.
thanks for such type of article

 

Ganesh Vengurlekar on 31 Mar 2012 02:24 reply

Easy to learn and Execute.

 

Felix Augustin on 16 Feb 2012 16:57 reply

Find your article very useful for my SQL Dbase I am accessing from C#.

 

Hugh Mannity on 28 Oct 2011 10:43 reply

Really need to show how c# variables, such as the values of controls, are incorporated into the SQL syntax. A little variation in the examples could have provided so much more information.

 

Ashutosh on 01 Jun 2011 10:54 reply

Good source of information. Really helped.

 

Alex Sanyago on 17 May 2011 12:28 reply

Hi, thanks for your article this is mutch better than msdn....!!!

this information is very important for me...!!!!

 

Shahid on 04 May 2011 02:43 reply

Nice

 

Shaun on 15 Mar 2011 11:19 reply

The "Cancel()" code is incorrect. Unless the query is being executed on another thread, the "ExecuteNonQuery" will block the current thread until the command has been fully executed. Cancel() will only be called after the command has finished, at which point, there is nothing to cancel.

 

sree unplugged on 24 Feb 2011 05:29 reply

hey.........really gooooooooood

 

karishma on 09 Feb 2011 03:31 reply

nice

roli on 09 Feb 2011 03:34 reply

why only nice karishma?????????
 

umakant on 18 Aug 2010 05:35 reply

good

 

Amit Suthar on 02 Apr 2010 01:12 reply

thanks
I just solve all the queries

 

bala on 26 Aug 2009 09:09 reply

Hi,Thank you very much .

Better to cover all the methods.

 

Pal on 11 Jun 2009 12:52 reply

good work

 

Sasi on 05 Jun 2009 11:52 reply

Good one.
Easy to understand and grasp.
Thank you for the kind postings.

 

Rajeesh on 09 Apr 2009 23:13 reply

its one of the best article i ever read.
I appreciate your simplicity.

 

trivh on 11 Feb 2009 01:43 reply

hi .Thank you very much
details send email to trivh@ssi.com.vn

 

Fernando Jaruche on 22 Dec 2008 12:19 reply

Hi, thanks for your article this is mutch better than msdn.

Just one comment about CreateParameter sample: you must to cast SqlDbType.NVarChar to (DbType) before atributing to param.DbType.

Thanks!

TJ on 23 Dec 2008 04:41 reply

Thank for your comment :)
 

jaya on 21 Oct 2008 06:23 reply

Easy to understand

 
See all reviews
Write a review
Rating:
Your Message:
Name:
Email:
(optional)
(Your email that entered here will not show anywhere on website and will use only reference when someone reply your post)
  I want to receive a copy of email on this post (your email is needed)
 
Code:
 

Search this site