SQL Command in asp.net

 

SqlCommand classis used to prepare an Sql statement or Stored procedure that we want to execute on a sql server database.

The Most commonly used methods of the Sqlcommand class

1-  ExecuteReader- Use when the T-SQL Statement returns more than a single value. For example, if the query returns rows of data.

2-  ExecuteNonQuery- use when you want to perform an insert, Update or delete Operation.

3-  ExecuteScalar- Use When the Query returns a single (scalar) value. For example, Queries that return the total number of rows in a table.

  Example: ExecuteReader Methods:

  

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

public partial class ExecuteReaderExamples: System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgse)

    {

        using (SqlConnectioncon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))

        {

            SqlCommand cmd = newSqlCommand("Select ProductId,ProductName,ProductImage,Remarks from ProductTable",con);

            con.Open();

           

           

            /*        SqlDataReader dr=cmd.ExecuteReader();   

                      GridView1.DataSource = dr;    // Frist Method

            */

           

            GridView1.DataSource = cmd.ExecuteReader();    // Second method Gvdatasource

            GridView1.DataBind();

        }

    }

}

Second Way of SqlCommand:

using System;

using System.Collections.Generic;  

usingSystem.Linq;

using System.Web; 

usingSystem.Web.UI;

using System.Web.UI.WebControls;

usingSystem.Data;

using System.Data.SqlClient;  

usingSystem.Configuration;

public partial class ExecuteReaderExamples: System.Web.UI.Page

{

    protected voidPage_Load(object sender, EventArgs e)

    {

    using (SqlConnectioncon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))

     {

    SqlCommand cmd = newSqlCommand();

    cmd.CommandText = "Select ProductId,ProductName,ProductImage,Remarks from ProductTable";

            cmd.Connection = con;

            con.Open();

            GridView1.DataSource = cmd.ExecuteReader();    // Second method Gvdatasource

            GridView1.DataBind();

      }

      }

  }

 

 

ExecuteScalar SqlCommand: Return  only one Row Examples:-

.

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Configuration;

public partial class ExcecuteScalarCommand: System.Web.UI.Page

{

    protected voidPage_Load(object sender, EventArgs e)

    {

        using (SqlConnectioncon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))

        {

            SqlCommand cmd = newSqlCommand();

            cmd.CommandText = "Select count(*)ProductId from ProductTable";

            cmd.Connection = con;

            con.Open();

            int TotalRows = (int)cmd.ExecuteScalar();

            Response.Write("<H1>Total Rows = " + TotalRows.ToString() + "</H1>");

       

   }  
 }

 }

ExecuteNonQuery Example:-( Using Insert Command) 

 

 

using System;                  

usingSystem.Collections.Generic; 

using System.Linq; 

usingSystem.Web;

using System.Web.UI;  

usingSystem.Web.UI.WebControls;

using System.Data.SqlClient; 

usingSystem.Configuration;

public partial class ExecuteNonQueryExample: System.Web.UI.Page

{

    protected voidPage_Load(object sender, EventArgs e)

    {

        using (SqlConnectioncon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))

   {

     SqlCommand cmd = newSqlCommand();

     cmd.CommandText = "insert into ProductTable values('Mouse','mouse.jpg','Rs 200/-')";

     cmd.Connection = con;

     con.Open();

     int TotalRowsAffected = cmd.ExecuteNonQuery();

     Response.Write("<H1>Total Rows Inserted = " + TotalRowsAffected.ToString() + "</H1>");

        }   

  } 

}

 

Using update command  

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))

 {

  SqlCommand cmd = newSqlCommand();

       cmd.CommandText = "update ProductTable set ProductName='Hard disk'  where ProductId=7";

       cmd.Connection = con;

       con.Open();

       int TotalRowsAffected = cmd.ExecuteNonQuery();

       Response.Write("<H1>Total Rows Updated = " + TotalRowsAffected.ToString() + "</H1>");

  }

 

Using Delete Command

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))

    {

      SqlCommand cmd = newSqlCommand();

      cmd.CommandText = "delete from  ProductTable  where ProductId=7";

      cmd.Connection = con;

      con.Open();

      int TotalRowsAffected = cmd.ExecuteNonQuery();

      Response.Write("<H1>Total Rows Deleted = " + TotalRowsAffected.ToString() + "</H1>");

    }