Beginning .Net: Execute SQL query or Stored Procedure on database in C# .Net Programming and VB.Net Programming

 This is very useful article for .Net Beginners Who want to querying database server using programming .
You can create a command object appropriate to the type of database that you use. All command objects implement the System.Data.IDbCommand interface. Configure the command object by setting its CommandType and CommandText properties. Execute the command using the ExecuteNonQuery, ExecuteReader, or ExecuteScalar method, depending on the type of command and its expected results.

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

• System.Data.SqlClient.SqlCommand
• System.Data.Odbc.OdbcCommand
• System.Data.OleDb.OleDbCommand
• System.Data.OracleClient.OracleCommand
• System.Data.SqlServerCe.SqlCeCommand

To execute a command on a database, you must have an open connection and a properly configured command object appropriate to the type of database you are accessing.

Here are some common command object properties.

Property	Description
CommandText	Command text contains SQL command or stored procedure name to execute. The content of the CommandText property must be compatible with the value you specify in the CommandType property.
CommandTimeout	An integer that specifies the number of seconds to wait for the command to return before timing out and raising an exception. Defaults is 30 seconds.
CommandType	A value of the System.Data.CommandType enumeration that specifies the type of command represented by the command object.For stored procedure you specify StoredProcedure and when you want to execute a SQL text command than you specify Text value. 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.
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. You can also create connection object of specific connection class like SQLConnection.
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.

Once you have configured your command object details, you can execute it in a various ways, 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 that does not return database data Like INSERT, DELETE, or CREATE TABLE etc.. , call ExecuteNonQuery. For the INSERT, UPDATE and DELETE commands, the ExecuteNonQuery method returns an int that specifies the number of rows affected by the command. For other commands, like CREATE TABLE, ExecuteNonQuery returns the value -1.
• To execute a command that returns a result set, such as a SELECT statement or stored procedure, use the ExecuteReader or other DataAdapter 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. 
• 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 are example of ExecuteNonQuery , ExecuteReader and ExecuteScalar Methods.

C# Example :

    public void ExecuteNonQueryOnDatabase(IDbConnection objCon)
    {
        // Create and configure a new command.
        IDbCommand objCom = objCon.CreateCommand();
        objCom.CommandType = CommandType.Text;
        /////Create Update command for update product name of product_id = 8
        objCom.CommandText = "UPDATE product_master SET product_name = 'KeyBoard'" +
        " WHERE Product_id = 8";
        // Execute the command and process the result.
        int result = objCom.ExecuteNonQuery();
        if (result == 1)
        {
            Response.Write("Product title updated.");
        }
        else
        {
             Response.Write("Product title not updated.");
        }
    }
    public void ExecuteReaderOnDatabase(IDbConnection objCon)
    {
        // Create and configure a new command.
        IDbCommand objCom = objCon.CreateCommand();
        objCom.CommandType = CommandType.StoredProcedure;
        objCom.CommandText = "SP_GET_ALL_PRODUCTS";
      

        // Execute the command and process the results using reader.
        using (IDataReader reader = objCom.ExecuteReader())
        {
            Response.Write("Get All Products.");
            while (reader.Read())
            {
                // Display the product details.
                 Response.Write(reader["product_id"] + " = " + reader["product_name"]);
            }
        }
    }
    public  void ExecuteScalarOnDatabase(IDbConnection objCon)
    {
        // Create and configure a new command.
        IDbCommand objCom = objCon.CreateCommand();
        objCom.CommandType = CommandType.Text;
        objCom.CommandText = "SELECT COUNT(*) FROM product_master";
        // Execute the command and cast the result.
        int result = (int)objCom.ExecuteScalar();
         Response.Write("Product count = " + result);
    }
    protected void Page_Load(object sender, EventArgs e)
    {

        // Create a new SqlConnection object.
        using (SqlConnection objCon = new SqlConnection())
        {
            // Configure the SqlConnection object's connection string.
            objCon.ConnectionString =@"Data Source=.\SQLEXPRESS;" + // SQL Server instance , You can also set remote database
                                         "Initial Catalog=TempDatabase;" + // the sample TempDatabase DB
                                         "User ID=sa;Password=sa;";  //Set Credential
                                 
            // Open the database connection and execute the example
            // commands through the connection.
            objCon.Open();
            ExecuteNonQueryOnDatabase(objCon);
            Response.Write(Environment.NewLine);
            ExecuteReaderOnDatabase(objCon);
            Response.Write(Environment.NewLine);
            ExecuteScalarOnDatabase(objCon);
        };
    }

VB.net Example :

    Public Sub ExecuteNonQueryOnDatabase(ByVal objCon As IDbConnection)
        ' Create and configure a new command.
        Dim objCom As IDbCommand = objCon.CreateCommand()
        objCom.CommandType = CommandType.Text
        ''''Create Update command for update product name of product_id = 8
        objCom.CommandText = "UPDATE product_master SET product_name = 'KeyBoard'" & " WHERE Product_id = 8"
        ' Execute the command and process the result.
        Dim result As Integer = objCom.ExecuteNonQuery()
        If result = 1 Then
            Response.Write("Product title updated.")
        Else
            Response.Write("Product title not updated.")
        End If
    End Sub
    Public Sub ExecuteReaderOnDatabase(ByVal objCon As IDbConnection)
        ' Create and configure a new command.
        Dim objCom As IDbCommand = objCon.CreateCommand()
        objCom.CommandType = CommandType.StoredProcedure
        objCom.CommandText = "SP_GET_ALL_PRODUCTS"

        ' Execute the command and process the results using reader.
        Using reader As IDataReader = objCom.ExecuteReader()
            Response.Write("Get All Products.")
            While reader.Read()
                ' Display the product details.
                Response.Write(reader("product_id") + " = " + reader("product_name"))
            End While
        End Using
    End Sub
    Public Sub ExecuteScalarOnDatabase(ByVal objCon As IDbConnection)
        ' Create and configure a new command.
        Dim objCom As IDbCommand = objCon.CreateCommand()
        objCom.CommandType = CommandType.Text
        objCom.CommandText = "SELECT COUNT(*) FROM product_master"
        ' Execute the command and cast the result.
        Dim result As Integer = CInt(objCom.ExecuteScalar())
        Response.Write("Product count = " & result)
    End Sub


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ' Create a new SqlConnection object.
        Using objCon As New SqlConnection()
            ' Configure the SqlConnection object's connection string.
            ' SQL Server instance , You can also set remote database
            ' the sample TempDatabase DB
            objCon.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                "Initial Catalog=TempDatabase;" & _
                "User ID=sa;Password=sa;" 
            objCon.Open()
            ExecuteNonQueryOnDatabase(objCon)
            Response.Write(Environment.NewLine)
            ExecuteReaderOnDatabase(objCon)
            Response.Write(Environment.NewLine)
            ExecuteScalarOnDatabase(objCon)
        End Using

    End Sub

In this article we are using both Text and StoredProcedure as command type. 

Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.