Connection Pool in Asp.net

In this artical we understand what is Connection Pool in Asp.net.

If we create a website for some service,definitely we look forward to having more customers,so if more customer log into the database web server perform slowly because of more multiple connections getting opened.To avoid this we have to use Connection pooling in our website.
By default connection string is enabled with Connection pool Max No. of pool is 100,Min is 0.

Example:

Suppose first time when user enter to our website and open one connection then 1st connection pool is created. and at the same time 2nd user entered to our website and open connection with same string then our pool checks whether any opened pools with same connection  string are free or available.If the 1st uer close the connection,that pool will be free then 2nd user using the same pool. If pool is not free means 1st user still working ,then it will create one more pool and use it. like this N number of user try to establish a connection.

Read More

Difference between Response.Redirect and Server.Transfer

Response.Redirect :

1.   It sends an HTTP request to the browser,then the browser sends the request to the server,then the server deliver a response to the web browser.
2. This method can be used for both .aspx and Html pages.
3. This method can be used to redirect a user to an external website.
4. While using Response.Redirect method,the previous page is removed from server memory and load a new page in memory.
5. Address of the URL will be changed along with the content.
6. This method also update history of the browser 

Server.Transfer :

1. This method sends request directly to the web server and web server delivers the response to the browser.
2. By using this method we get same URL but with different  content that means web page URL in the address bar will not changed.
3. It can only used for .aspx page and is specific to ASP.NET
4. While using Server.Transfer the previous page also exits in server memory.
5. It can be used only on sites running on the same server. we can not use to redirect the user to a page running on a different server.

Syntax for Both :

Response.Redirect ("Default.aspx");

Server.Transfer("Default.aspx");

Read More

Exception Handaling in SQL Server Stored Procedure with Try Catch

I will explain how to handle Exception in SQl Server for Stored Procedure by using Try Catch.


To handle Exception in SQL Server we can use Try Catch block.To use Try Catch we need to write the code as shown bellow.

BEGIN TRY

---Write Your Code

END TRY

BEGIN CATCH

---Write Code to handle errors

END CATCH

In Try block we write our queries and in Catch block we write a code to handle exceptions. In SQL statements if any error occurs automatically it will go to Catch block where we can handle error messages. To handle error messages we have defined Error Functions in Catch block those are...

ERROR_LINE() - This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() - This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - This function will return state number of error message which cause to raise error.

ERROR_PROCEDURE() - This function will return name of the procedure where an error occurred.

ERROR_MESSAGE() - This function will return the complete text of the error message which cause to raise error.

It will show output like this..

Read More

Difference between tostring() and convert.tostring()

The basic difference them is Convert.toString(Variable) handles null values even if variable value become null but variable.toString() will not handle null values it will throw a NULL reference exception error.

Convert is always safe as compair to toString().

example :

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; 

namespace csharpExamples

{
   class Program
   {
      static void Main(string[] args)
      {
          string abc = "" ;
          abc = null;
          Console.Write(abc.ToString());
          Console.ReadKey();
      }
 }

And if you use Convert.ToString() . then you need to change this line of code ..

Console.Write(abc.ToString());
          to 
Console.Write(Convert.ToString(abc));

Read More

Differeces Between ExecuteReader,ExecuteScalar,ExecuteNonQuery

ExecuteReader

ExecuteReader will be used to return the set of Rows,on execution of Sql query or Stored Procedure using command object.This  one is the forward only retrieval of records and it is used to read the table value from first to last.

ExecuteScalar

Execute Scalar will return single row single column value ( i.e single value ) on execution of Sql query or Stored Procedure using command object.It is very fast to retrieve single value form database.

example :

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB"))

{

con.Open();

SqlCommand cmd = new SqlCommand("Select Student_Name,Roll from student", con);

string result = (string)cmd.ExecuteScalar();

if (!string.IsNullOrEmpty(result))

{

lblDetails.Text = result;

}

else

{

lblDetails.Text =  "No value Selected" ;

}

con.Close();

}

}

ExecuteNonQuery

ExecuteNonQuery will return number of rows effected with Insert,Update,Delete operations.This method used only for Insert,Update,Delete,Create and Set Statement.

example

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

using (SqlConnection con=new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB"))

{

con.Open();

SqlCommand cmd = new SqlCommand("insert into student(Student_Name,Roll) values(@Name,@Roll)", con);

cmd.Parameters.AddWithValue("@Name", "Kapil");

cmd.Parameters.AddWithValue("@Roll", "12");

int count= cmd.ExecuteNonQuery();

if(count>=1)

{

lblDetails.Text =  count.ToString();

}

else

{

lblDetails.Text = "0" ;

}

con.Close();

}

}

Read More

Difference Between DataReader,DataSet,DataAdapter and DataTable in C#.

DataReader :

DataReader is used to read the data from database and it is a read and forward only connection oriented architecture during fetch the data from database. DataReader is used to iterate through Resultset that came from server and it will read one record at a time because of the memory consumption will be less and it will fetch tha very fast when compared with Dataset.

example :

Protected void BindGridview()

{

using (SqlConnection conn = new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB"))

{

con.Open();

SqlCommand cmd = new SqlCommand("Select Student_name,Roll,from student", conn);

SqlDataReader sdr = cmd.ExecuteReader();

gvUserInfo.DataSource = sdr;

gvUserInfo.DataBind();

conn.Close();

}

}

DataSet :

DataSet is a disconnected orient architecture that means there is no need of active connections during work with datasets and it is a collection of DataTables and relations between tables. It is used to hold multiple tables with data. You can select data form tables, create views based on table and ask child rows over relations. Also DataSet provides you with rich features like saving data as XML and loading XML data.

example :

protected void BindGridview()

{

    SqlConnection conn = new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB");

    conn.Open();

    SqlCommand cmd = new SqlCommand("Select Student_Name,Roll from student", conn);

    SqlDataAdapter sda = new SqlDataAdapter(cmd);

    DataSet ds = new DataSet();

    da.Fill(ds);

    gvUserInfo.DataSource = ds;

    gvUserInfo.DataBind();

}

DataAdapter :

DataAdapter will acts as a Bridge between DataSet and database. This dataadapter object is used to read the data from database and bind that data to dataset. Dataadapter is a disconnected oriented architecture. 

example :

protected void BindGridview()

{

    SqlConnection con = new SqlConnection("Data Source=ss;Integrated Security=true;Initial Catalog=MyDB");

    conn.Open();

    SqlCommand cmd = new SqlCommand("Select Student_Name,Roll from student", conn);

    SqlDataAdapter sda = new SqlDataAdapter(cmd);

    DataSet ds = new DataSet();

    da.Fill(ds);

    gvUserInfo.DataSource = ds;

    gvUserInfo.DataBind();

}

Read More