I have been teaching Entity Framework,LINQ to SQL,LINQ to objects,LINQ to XML for some time now. I am huge fan of LINQ to Entities and I am using Entity Framework as my main data access technology.

Entity framework is in the second version right now and I can accomplish most of the things I need. I am sure the guys in the ADO.Net team will implement many more features in the future. I am a strong believer that you cannot really understand the benefits of LINQ to SQL or LINQ to Entities unless you have a good grasp of previous ADO.Net 2.0 technologies.

In my classes I have people that come from the Java world. They do know NHibernate but have never heard of the DataReader object. I have some younger people who have just come out of the university and are eager to find out about LINQ but do not know what a dataset is.

I have been asked to do a series of posts covering the "connected" database access scenario. I am going to demonstrate that in this post.So we will see the main objects in order to retrieve data from the database using two ways

• Pure T-SQL
• Stored procedures
  

I am going to demonstrate this with a hands on example. Lets look at the first approach thus using pure t-sql to query the table.

3) I am going to explain what I am doing here. First of all make sure that in the top of your Default.aspx.cs file you have this

using System.Data.SqlClient;

I am using an object (myconn) of type SqlConnection to create the handshake with the database. Please note that you must change this line of code to reflect your sql connection string

new SqlConnection("Data Source=FOFO-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

Then I use the Open() method to open that connection.

myconn.Open();

Then I create a command object.

 SqlCommand comm = myconn.CreateCommand();

Then I use the CommandText property of the comm object to define the T-SQL statement.Then I use the CommandType property to indicate that I am using T-SQL.

comm.CommandText = "SELECT * FROM Customers";
comm.CommandType = System.Data.CommandType.Text;

Then I declare a SQLDataReader object and use the ExecuteReader() of the command object to build the SQLDataReader.

        SqlDataReader myDataReader;

        myDataReader = comm.ExecuteReader();

Then I loop through the SQLDataReader object and print the results out in the screen.

    while (myDataReader.Read())
        {
            Response.Write(myDataReader[1]);
            Response.Write("</br>");
        }

Finally I close my connection object.

myconn.Close();

4) Run your application and see the "CompanyName" data appearing in the screen for all the customers in the Customers table.

5) Now, let's try and query the database using a stored procedure. This is the stored procedure that we will create in the Northwind database.It i called ListCustomers.

USE [Northwind]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ListCustomers]
AS
BEGIN

SELECT * FROM dbo.Customers

end

GO

6) Comment out all the code in the Page_Load event handling routine. In the Page_Load event handling routine of the Default.aspx page type

    SqlConnection myconn = new SqlConnection("Data Source=FOFO-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

        myconn.Open();

        SqlCommand comm = myconn.CreateCommand();

        comm.CommandText = "ListCustomers";
        comm.CommandType = System.Data.CommandType.StoredProcedure;

        SqlDataReader myDataReader;

        myDataReader = comm.ExecuteReader();


        while (myDataReader.Read())
        {
            Response.Write(myDataReader[1]);
            Response.Write("</br>");
        }

        myconn.Close();

The only thing I am changing compared to the first approach is this bit of code.

        comm.CommandText = "ListCustomers";
        comm.CommandType = System.Data.CommandType.StoredProcedure;

Please note that while we do all of that retrieving of data, we work in connected mode. That means I have a connection live to the database. So we must keep the connection short.

That means , open the connection as late as possible and close it at the earliest time. 

Email me if you need the source code.

Hope it helps.