Subscribe

RSS Feed (xml)

Execute SQL Command or Stored Procedure

The IDbCommand interface represents a database command, and each data provider includes a unique implementation. Here is the list of IDbCommand implementations for the five standard data providers.

  • System.Data.Odbc.OdbcCommand

  • System.Data.OleDb.OleDbCommand

  • System.Data.OracleClient.OracleCommand

  • System.Data.SqlServerCe.SqlCeCommand

  • System.Data.SqlClient.SqlCommand

To execute a command against a database you must have an open connection and a properly configured command object appropriate to the type of database you are accessing. You can create command objects directly using a constructor, but a simpler approach is to use the CreateCommand factory method of a connection object. The CreateCommand method returns a command object of the correct type for the data provider and configures it with basic information obtained from the connection you used to create the command. Before executing the command, you must configure the properties described in Table, which are common to all command implementations.

Table: Common Command Object Properties

Property

Description

CommandText

A string containing the text of the SQL command to execute or the name of a stored procedure. The content of the CommandText property must be compatible with the value you specify in the CommandType property.

CommandTimeout

An int that specifies the number of seconds to wait for the command to return before timing out and raising an exception. Defaults to 30 seconds.

CommandType

A value of the System.Data.CommandType enumeration that specifies the type of command represented by the command object. For most data providers, valid values are StoredProcedure, when you want to execute a stored procedure, and Text, when you want to execute a SQL text command. If you are using the OLE DB Data Provider, you can specify TableDirect when you want to return the entire contents of one or more tables; refer to the .NET Framework SDK documentation for more details. Defaults to Text.

Connection

An IDbConnection instance that provides the connection to the database on which you will execute the command. If you create the command using the IDbConnection.CreateCommand method, this property will be automatically set to the IDbConnection instance from which you created the command.

Parameters

A System.Data.IDataParameterCollection instance containing the set of parameters to substitute into the command.

Transaction

A System.Data.IDbTransaction instance representing the transaction into which to enlist the command. (See the .NET Framework SDK documentation for details about transactions.)

Once you have configured your command object, there are a number of ways to execute it depending on the nature of the command, the type of data returned by the command, and the format in which you want to process the data.

To execute a command, such as INSERT, DELETE, or CREATE TABLE, that doesn't return database data, call ExecuteNonQuery. For the UPDATE, INSERT, and DELETE commands, ExecuteNonQuery method returns an int that specifies the number of rows affected by the command. For other commands, such as CREATE TABLE, ExecuteNonQuery returns the value -1. Here is an example that uses UPDATE to modify a record.

public static void ExecuteNonQueryExample(IDbConnection con) {

    // Create and configure a new command.
    IDbCommand com = con.CreateCommand();
    com.CommandType = CommandType.Text;
    com.CommandText = "UPDATE Employees SET Title = 'Sales Director'" +
        " WHERE EmployeeId = '5'";

    // Execute the command and process the result.
    int result = com.ExecuteNonQuery();

    if (result == 1) {
        Console.WriteLine("Employee title updated.");
    } else {
        Console.WriteLine("Employee title not updated.");
    }
}

To execute a command that returns a result set, such as a SELECT statement or stored procedure, use the ExecuteReader method. ExecuteReader returns an IDataReader instance through which you have access to the result data. Most data providers also allow you to execute multiple SQL commands in a single call to the ExecuteReader method. This code excerpt uses the ExecuteReader method to execute the Ten Most Expensive Products stored procedure from the Northwind database and display the results to the console.

public static void ExecuteReaderExample(IDbConnection con) {

    // Create and configure a new command.
    IDbCommand com = con.CreateCommand();
    com.CommandType = CommandType.StoredProcedure;
    com.CommandText = "Ten Most Expensive Products";

    // Execute the command and process the results
    using (IDataReader reader = com.ExecuteReader()) {

        Console.WriteLine("Price of the Ten Most Expensive Products.");

        while (reader.Read()) {

            // Display the product details.
            Console.WriteLine("  {0} = {1}", 
                reader["TenMostExpensiveProducts"],
                reader["UnitPrice"]);
        }
    }
}

If you want to execute a query but only need the value from the first column of the first row of result data, use the ExecuteScalar method. The value is returned as an object reference that you must cast to the correct type. Here is an example.

public static void ExecuteScalarExample(IDbConnection con) {

    // Create and configure a new command.
    IDbCommand com = con.CreateCommand();
    com.CommandType = CommandType.Text;
    com.CommandText = "SELECT COUNT(*) FROM Employees";

    // Execute the command and cast the result.
    int result = (int)com.ExecuteScalar();

    Console.WriteLine("Employee count = " + result);
}

21 comments:

  1. hello... hapi blogging... have a nice day! just visiting here....

    ReplyDelete
  2. the food is delicious!. hello... hapi blogging... have a nice day! just visiting here....

    DRESS FOR SUCCESS FASHION SHOW

    ReplyDelete
  3. Well done! Pleasant post! This truly helps me to discover the solutions for my inquiry. Trusting, that you will keep posting articles having heaps of valuable data. You're the best! 

    angularjs6-Training in chennai

    angularjs Training in online

    angularjs4-Training in online

    angularjs-Training in tambaram

    ReplyDelete
  4. I recently came across your blog and have been reading along. I thought I would leave my first comment.
    java training in omr | oracle training in chennai

    java training in annanagar | java training in chennai

    ReplyDelete
  5. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts read this.
    python online training
    python training in OMR

    ReplyDelete
  6. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.

    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram
    DevOps online Training

    ReplyDelete
  7. The post is written in very a good manner and it entails many useful information for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept.
    Blueprism training in tambaram

    Blueprism training in annanagar

    ReplyDelete
  8. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa training in chennai | rpa training in chennai
    rpa training in pune | rpa online training | rpa training in bangalore

    ReplyDelete
  9. Thank you for sharing such great information with us. I really appreciate everything that you’ve done here and am glad to know that you really care about the world that we live in

    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in btm

    ReplyDelete
  10. This is such a good post. One of the best posts that I\'ve read in my whole life. I am so happy that you chose this day to give me this. Please, continue to give me such valuable posts. Cheers!

    angularjs Training in bangalore

    angularjs Training in btm

    angularjs Training in electronic-city

    angularjs online Training

    angularjs Training in marathahalli

    ReplyDelete
  11. Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials…
    python training in velachery | python training institute in chennai

    ReplyDelete
  12. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.

    AWS Interview Questions And Answers

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    AWS Training in Pune | Best Amazon Web Services Training in Pune

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    AWS Online Training | Online AWS Certification Course - Gangboard

    ReplyDelete
  13. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.

    AWS Interview Questions And Answers

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    AWS Training in Pune | Best Amazon Web Services Training in Pune

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    AWS Online Training | Online AWS Certification Course - Gangboard

    ReplyDelete
  14. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    https://www.slainstitute.com/linux-training-in-chennai/
    https://www.slainstitute.com/python-training-in-chennai/
    https://www.slainstitute.com/data-science-training-in-chennai/
    https://www.slainstitute.com/rpa-training-in-chennai/
    https://www.slainstitute.com/devops-training-in-chennai/

    ReplyDelete
  15. There's definately a lot to learn about this topic. I really like all of the points you've made site.

    ReplyDelete

LocalsAdda.com-Variety In Web World

Fun Mail - Fun in the Mail