What is the best way to make Database Connection in ASP.NET?

what is the best way to make Database Connection in ASP.NET?





Hello friends m gaurav arya


and I want to know what is the best way to make a connection in ASP.NET?





generally i make a connection at session_start event in global.asax file


and assign it to Session, and use it where ever it is required.


is it correct?


what are the other effective ways to make a connection?

What is the best way to make Database Connection in ASP.NET?
That is not a very good way to make a connection, because then you only have one instance of the connection for each session. What if you need to make asynchronous database calls?





The best way is to use the Enterprise Library's Data Application Block. You can download it from http://msdn2.microsoft.com/en-us/library... - the Enterprise Library is a collection of Microsoft's "recommended best practices" for common programming problems like how to connect to a database. It effectively abstracts the data source from your application. You do like so:





Database myDb = DatabaseFactory.Create("DatabaseName")


myDb.ExecuteQuery(command);





It handles connection opening and closing, pooling, etc. It also keeps the information about the database in an XML configuration file, so it can easily be changed - such as if your SQL Server password needs to be changed - without re-deploying the application.
Reply:Your way of using the connection is not the correct way to do it. The post above me makes a point, but you can keep things simpler without using any extra libraries.





The pattern I advise you to use is as follows (code commented). The code retrieves records, but its just an example. The same pattern can be used for updating or retrieving scalars.





try {





//the using disposes your connection object when done


//


using ( SqlConnection connection = new SqlConnection ( "server=.; uid=sa; pwd=; database=Northwind" ) )


{


//initialize your command object


//


SqlCommand command = new SqlCommand( "SELECT * FROM Employees", connection );


command.CommandType = CommandType.Text;





//open your connection


//


connection.Open();





//get a reference of the reader


SqlDataReader reader = command. ExecuteReader()





//loop through retrieved records


while ( reader.Read() ) {





//read your records here


}


connection.Close(); // close the connection


}


}


catch (Exception ex)


{


//handle your exception exception here (for example log it)


}





Note: The using keyword in C# makes sure that the connection object is disposed





Hope this helps.
Reply:The best way to keep the connection string is web.config with encrypted form.


If you are usingasp.net 1.1 then use web.config because if the connection string changes no need to compile the project again.