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

MVC in ASP.Net,C# : Insert and Validate your program

Introduction:

Here in this post I will show you how to make a form  and how to save the inputted value of the form into database  and how to validate your textbox before saving the data using MVC4.0 using Razor view engine.

So first create or choose an existing database and create a table named MVC_Details. Give any name to table .

Now start a MVC project and choose Razor as a view engine. 

Create a Class in Model Folder whose name is ColumnSetGet.cs (give any name according to your project).In this class you just provide all the column name with set and get method.This class will reflect the table columns.

After creation of set and get method in a class , now you have to create one another class in Model folder whose name is InsertMVCModel.cs ( give any name according to you ). In this class you jsut write the SQL Query to Insert, Update ,Delete aur any SQL Query to interact with Database.

Now do some coding work in Controller .

So create a Controller class within the Controller folder named InsertController( give any name according to you ).Add the namespace using PracticeMVC.Models; [ PracticeMVC is my project name and Model is a Model folder name, give your project name.model in controller class ]  for accessing the model folder into your Controller class. If you put the classes in any other folder add that folder name as namespace.

I took InsertView as the ActionMethod, so there will be two Action method named InsertView. The first one for just showing the form and second one is for getting the value after submit button click. We don't need to write any code within the first method, But in the second method write down the code.

using PracticeMVC.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace PracticeMVC.Controllers

{

    public class InsertController : Controller

    {

        public ActionResult InsertView()

        {

            return View();

        }

        [HttpPost]

        [ValidateAntiForgeryToken]

        public ActionResult InsertView(FormCollection frm)

        {

                ColumnSetGet csg = new ColumnSetGet();

        

                if (string.IsNullOrEmpty(frm["Name"]))

                {

                    ModelState.AddModelError("Name", "Enter Your Name");

                    if (string.IsNullOrEmpty(frm["Msg"]))

                    {

                        ModelState.AddModelError("Msg", "Enter Your Message");

                    }

                }

                else if (ModelState.IsValid)

                {

                    csg.Name = frm["Name"];

                    csg.Msg = frm["Msg"];

                    InsertMVCModel inm = new InsertMVCModel();

                    if (inm.AddDetails(csg) == 1)

                    {

                        ViewBag.Status = "Success!!";

                    }

                }

              

            return View("InsertView");

        }

    }

}

Now create the View part. To create the View right_click on the Action Method and keep the name as it is. Just one change you have to do. Check the Create Strongly typed view and choose ColumnSetGet as your Modal class. Now click on the Add  button to make your View.

In the view we have to make form first. To create a form we have to use Html.BeginForm() and within that you have to put labels, textboxes and buttons.

So lets create our own form.

Now your form is ready to Insert data into Database .

Now follow the some more Images below to know how to Validate your Textbox fields before Insertion
In Controller Class you have to call  the method IsValid with refrence variable ModelState in if else condition like ..

//  If TextBox is not Empty
if(ModelState.IsValid)   

{

       // your code;

}

// If TextBox is Empty

else if(string.IsNullOrEmpty(frm["Name"]))

{

      ModelState.AddModelError("Name", "Enter Your Name");

}

for more see the Image below....

Now in View Page add One Attribute @Html.ValidationSummary()

Now  Your Form is ready with Insertion SQL Query with Textbox Validation .Run your program

Read More