Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

December 2010 - Posts

Retrieve data from an ASP.Net application using Ado.Net 2.0 disconnected model

This is the second post in a series of posts regarding to ADO.Net 2.0. Have a look at the first post if you like.

In this post I am going to investigate the "Disconnected" model. When I say "Disconnected" I mean Datasets. Datasets are in memory representations of tables in a particular database.

A Dataset contains a Table collection and each Table collection contains a Row collection and each Row collection contains a Columns collection.

So initially you connect to the database, get the data to the dataset, then disconnect and then you can select data from the Dataset and do all CRUD operations.

The Dataset obviously, has some sort of mechanism to keep track of all the changes so when the new connection is established to the database all changes are persisted to the data store.

We can use this model when we have multiple users using the same database and we want to avoid issues like blocking/locking tables while at the same time managing more efficiently resources like connections.

So it is good to know what datasets are and a lot of applications are implemented using datasets. I am not proposing to use Datasets for your main data access methodology but it always useful to know it.

I am going to demonstrate the use of datasets with a hands on example.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008/2005 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Before you start make sure you have included those two lines of code in the Default.aspx.cs file.

using System.Data;
using System.Data.SqlClient;

3) 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 myCommand = myconn.CreateCommand();
            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText = "SELECT * FROM Customers";

            SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);

            DataSet myDataSet = new DataSet();
            myDataAdapter.Fill(myDataSet);
       
            myconn.Close();

           foreach (DataRow myRow in myDataSet.Tables[0].Rows)
            {
                Response.Write(myRow["CompanyName"]);
                Response.Write("<br>");
                Response.Write(myRow["ContactName"]);
                Response.Write("<br>");
                Response.Write(myRow["City"]);
                Response.Write("<br>");
                Response.Write("----------------");
                Response.Write("<br>");
            }

4)   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 myCommand = myconn.CreateCommand();

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

myCommand.CommandText = "SELECT * FROM Customers";
myCommand.CommandType = CommandType.Text;

Then I create a SQLDataAdapter object and pass it as a parameter the Command object.

 SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);

Then I create a DataSet object, and use the Fill method of the Adapter object to fill the dataset. 

            DataSet myDataSet = new DataSet();
            myDataAdapter.Fill(myDataSet);

Then I close the connection object.

myconn.Close();

Then I loop through the rows in the datatable of the dataset.

 foreach (DataRow myRow in myDataSet.Tables[0].Rows)
            {
                Response.Write(myRow["CompanyName"]);
                Response.Write("<br>");
                Response.Write(myRow["ContactName"]);
                Response.Write("<br>");
                Response.Write(myRow["City"]);
                Response.Write("<br>");
                Response.Write("----------------");
                Response.Write("<br>");
            }

5) Run you application and see the results printed out in the screen. Please note that I have closed the connection to the database but was still able to loop through the data, because I am not querying the data but the data in memory.

6) We could write the code above slightly different.Comment out all the code in the Page_Load() event handling routine of the Default.aspx page. 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();

        SqlDataAdapter dap = new SqlDataAdapter();

        dap.SelectCommand = myconn.CreateCommand();
        dap.SelectCommand.CommandText = "SELECT * FROM Customers";

DataSet myDataSet = new DataSet();
        dap.Fill(myDataSet);

        myconn.Close();

        foreach (DataRow myRow in myDataSet.Tables[0].Rows)
        {
            Response.Write(myRow["CompanyName"]);
            Response.Write("<br>");
            Response.Write(myRow["ContactName"]);
            Response.Write("<br>");
            Response.Write(myRow["City"]);
            Response.Write("<br>");
            Response.Write("----------------");
            Response.Write("<br>");

         }

7) Run your application and see the results printed out in the screen. I will explain again what I do in the code.

 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.

I am creating a new instance of SQLDataAdapter object.Then I use the SelectCommand property to set the Transact-SQL statement used to select records in the data source. Then I use the CommandText property to

set the Transact-SQL statement to execute at the data source.

        SqlDataAdapter dap = new SqlDataAdapter();

        dap.SelectCommand = myconn.CreateCommand();
        dap.SelectCommand.CommandText = "SELECT * FROM Customers";


Then I create a DataSet object, and use the Fill method of the Adapter object to fill the dataset. 

          DataSet myDataSet = new DataSet();
        dap.Fill(myDataSet);

Then I close the connection object.

myconn.Close();

Then I loop through the rows in the datatable of the dataset.

 foreach (DataRow myRow in myDataSet.Tables[0].Rows)
        {
            Response.Write(myRow["CompanyName"]);
            Response.Write("<br>");
            Response.Write(myRow["ContactName"]);
            Response.Write("<br>");
            Response.Write(myRow["City"]);
            Response.Write("<br>");
            Response.Write("----------------");
            Response.Write("<br>");

         }

I will have more posts on inserts,updates and deletes using both the connected and disconnected model.

Email me if you need the source code. Hope it helps!!!

Retrieve data from an ASP.Net application using ADO.Net 2.0 connected model

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.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008/2005 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) 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 = "SELECT * FROM Customers";
        comm.CommandType = System.Data.CommandType.Text;

        SqlDataReader myDataReader;

        myDataReader = comm.ExecuteReader();


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

        myconn.Close();

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. 

Track updated/inserted entities in LINQ to SQL applications

In this post I would like to discuss in further detail the issue of track changing of entities in LINQ to SQL applications. I would like to show you how the DataContext object keeps track of all the items that are updated,deleted or inserted in the underlying data store.

If you want to have a look at my other post about LINQ to SQL and transactions click here.

I am going to demonstrate this with a hands on example. 

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Employees table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) Drag a Gridview web server control on the Default.aspx page.

6) In the Page_Load event handling routine of the Default.aspx page type

        NorthwindDataContext ctx = new NorthwindDataContext();

                   var emps = from myemp in ctx.Employees
                                     select myemp;
                        

        GridView1.DataSource = emps;
        GridView1.DataBind();


7) Run your application and see the employees data printed in your screen.

8) Now let's modify our application so we can insert data into the underlying table.

9) Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Insert".

10) In the Button1_Click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        Employee myemp = new Employee();
        myemp.LastName = "Kantzelis";
        myemp.FirstName = "Nikos";
        myemp.BirthDate = DateTime.Now.AddDays(-10000);
        myemp.HireDate = DateTime.Now.AddDays(-2000);
        myemp.HomePhone = "55555555";
        myemp.City = "Athens";
        myemp.Country = "Greece";

        ctx.Employees.InsertOnSubmit(myemp);

        ChangeSet mychanges =  ctx.GetChangeSet();

        foreach (var change in mychanges.Inserts)
        {
            Response.Write("Type changed: " + change.GetType().ToString());
            Response.Write("<br/>");
       

        if (change is Employee)
        {
            var changedEmployee = (Employee)change;

            Response.Write("Employee LastName: " + changedEmployee.LastName);
            Response.Write("<br/>");
            Response.Write("Employee FirstName: " + changedEmployee.FirstName);
            Response.Write("<br/>");
        }
        }
       
        try
        {
            ctx.SubmitChanges();
            Response.Write("Inserted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();
    }

    private void Load_Data()
    {
        NorthwindDataContext ctx = new NorthwindDataContext();

        var emps = from myemp in ctx.Employees
                   select myemp;


        GridView1.DataSource = emps;
        GridView1.DataBind();
    }

 

11) The code above is similar to what we have seen so far.

After the line ctx.Employees.InsertOnSubmit(myemp);

I am using a ChangeSet object and then in order to view the changes tracked by the DataContext we use the GetChangeSet method.

Then I am trying to find out what type is changed. In this case is Employee and then with a simple If statament I check if that particulat type was changed and then I print the information for the Employee type on the screen.Then I save the record in the table by calling the SubmitChanges() method.

Run your application and see the newly added record in the table.

12) We can do something similar when updating a record.Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Update".

13) In the Button2_Click() event handling routine type

NorthwindDataContext ctx = new NorthwindDataContext();
        var myemp = (from emp in ctx.Employees
                     where emp.EmployeeID == 16
                     select emp).Single();

        myemp.FirstName = "nick";
        myemp.LastName = "Kezerlis";

        ChangeSet mychanges = ctx.GetChangeSet();

        foreach (var change in mychanges.Updates)
        {
            Response.Write("Type changed: " + change.GetType().ToString());
            Response.Write("<br/>");


            if (change is Employee)
            {
                var changedEmployee = (Employee)change;

                Response.Write("Employee LastName: " + changedEmployee.LastName);
                Response.Write("<br/>");
                Response.Write("Employee FirstName: " + changedEmployee.FirstName);
                Response.Write("<br/>");
            }
        }

        try
        {
            ctx.SubmitChanges();
            Response.Write("Updated");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

14) Run your application and see the results printed in the screen and the record updated in the database.Wherever you think it is useful add a breakpoint and have a closer look at how the code works.

We do something similar like before.

I am using a ChangeSet object and then in order to view the changes tracked by the DataContext we use the GetChangeSet method.

Then I am trying to find out what type is changed. In this case is Employee and then with a simple If statament I check if that particular type was changed and then I print the information for the Employee type on the screen.Then I save the record in the table by calling the SubmitChanges() method.In this example we look for changes that are relevant to "Update" while in the previous example we look for changes that are relevant to "Insert"

Note that the changes will not be persisted to the database until the SubmitChanges() method executes.

That is all for now. Stay tuned for many posts on EF and LINQ.

Email me if you need the source code.

Hope it helps.

LINQ to SQL Inheritance model in an ASP.Net application

One of the most important concepts in Object Oriented Programming is Inheritance. In this post I would like to talk about LINQ to SQL and the inheritance type that it supports which is Table per Class Hierarchy.

In this type of hierarchy we have a single table/entity and its columns apply to all derived child tables/entities. Obviously the child tables/entities have columns that are only specific to them.

We can create this type of hierarchy in the Object Relational Designer. It is true that in Entity Framework we have more flexibility when it comes to inheritance types but we will talk about LINQ to SQL now.

We will demonstrate this with a hands on example. I am going to create a new database with new tables for this example.

The scenario we have in this case is derived from the institution I am delivering courses at. So we have different roles in there. We have instructors,students,marketing people,administration people.

So we will have this generic table/entity called Human. Well, we do have different roles and skills but we are alla humans after all.

You will need to create a database as well.I am going to name the database CPLS. CPLS is the kind of instution that is authorised by Microsoft to deliver Microsoft approved courses.

Obviously we need some sample data in our table.

So before you go any further make sure you have SQL Server installed and execute the T-SQL code you will find inside the CPLS.zip.

If you do not have SQL Server installed, you can download and install the free SQL Server Express edition from here.  

The Human table contains the following columns:

  [HumanID] ,[HumanType],[LastName],[FirstName],[BirthDate],[HireDate],[EnrollmentDate],[Address],[City],[Region],[PostalCode],[Country],[Phone]
  ,[Fax],[Wage],[SalesRegion],[ProgrammingSkills],[Bonus],[Grade]

We can see that we can derive 3 main entities:Instructor,Student and SalesPerson. These are the entities I am going to use in my model after I define the inheritance associations. Make a note that I will not change the underlying database table.

Let's proceed to our hands-on example.

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Human.dbml. Place this file in the App_Code special folder.

3) From the Server Explorer/Data Explorer window select the option "Connect to database" and then simply specify the instance of the SQL Server where you installed the CPLS database.

4) From the Server Explorer / Data Explorer window drag the Human table and drop it on the Human.dbml. When you do that you must have something similar like the picture below

 

5) Let's see how we can transform the entity above to implement inheritance and more specifically Table per class Hierarchy.In order to build this new model we should have defined what we call a Discriminator property.

In our case this property is "HumanType". HumanType=1 means Instructor , HumanType = 2 means Student and HumanType=3 means SalesPerson. Those are the 3 values for the HumanType column/property in our Human table/entity.

6) This is the new model. Have a look at the picture below.

 

7) The way I constructed this model was to drag and drop from the Server Explorer 3 more times the Human table.

Then I changed the names of the other entities to Instructor,Student,SalesPerson and Employee. Then I deleted the properties that were obsolete to that particular entity.

Then I dragged an Inheritance arrow from the Object Relational Designer and "joined" together the Student to Human and Employee to Human entities.

Then I dragged an Inheritance arrow from the Object Relational Designer and "joined" together the Instructor to Employee and SalesPerson to Employee entities.

Then I selected the Human entity and changed the Inheritance modifier to abstract.

Then I selected the Employee entity and changed the Inheritance modifier to abstract.

Then I selected the inheritance arrow from Student to Human and changed those properties

Discriminator Property:HumanType

Derived Class Discriminator : 2

Then I selected the inheritance arrow from Employee to Human and changed the property

Discriminator Property:HumanType

Then I selected the inheritance arrow from Instructor to Employee and changed those properties

Discriminator Property:HumanType

Derived Class Discriminator : 1

Then I selected the inheritance arrow from SalesPerson to Employee and changed those properties

Discriminator Property:HumanType

Derived Class Discriminator : 3

8) Let's try and get some data out from our new model as it is defined in the .dbml file. We want to get the students from our table.

In the Page_Load() event handling routine of the Default.aspx page type,

       HumanDataContext ctx = new HumanDataContext();

        var students = from stu in ctx.Humans.OfType <Student>()
                       select stu;

        foreach (var student in students)
        {
            Response.Write("First Name: " + student.FirstName);
            Response.Write("---");
            Response.Write("Last Name: " + student.LastName);
            Response.Write("---");
            Response.Write("Enrollment Date : " + student.EnrollmentDate);
            Response.Write("---");
            Response.Write("Grade: " + student.Grade);
        }

Before you run your application, you need to specify a default.Make sure that you do not choose an entity that is marked as abstract.You cannot create instances of classes that are marked as abstract.

One way to do it is to go to the Human.designer.cs file and find this bit of code and make the necessary changes.

LINQ needs to know that if for some reason cannot map an entity to one that exists already, needs to have a default one to map it to that.

[Table(Name="dbo.Human")]
[InheritanceMapping(Code="2", Type=typeof(Student))]
[InheritanceMapping(Code="1", Type=typeof(Instructor),IsDefault=true)]
[InheritanceMapping(Code="3", Type=typeof(SalesPerson))]

 9) Run your application and see the results. The main thing is to look carefully at this bit of code

var students = from stu in ctx.Humans.OfType <Student>()
                       select stu;

We use the OfType extension method. If you look more carefully at the properties available for the Student entity you will see that we have available all the properties defined in the parent entity Human (base class) and the specific properties of the Student entity.

10) If we wanted to get data out of our Instructors entity as well, we need to rewrite the code above.In the Page_Load() event handling routine of the Default.aspx page type,

 HumanDataContext ctx = new HumanDataContext();

       var students = from stu in ctx.Humans.OfType<Student>()
                      select stu;

        var instructors = from inst in ctx.Humans.OfType<Employee>().OfType<Instructor>()
                          select inst;

        foreach (var instructor in instructors)
        {
            Response.Write(instructor.ProgrammingSkills);
            Response.Write("<br/>");
        }

        foreach (var student in students)
        {
            Response.Write("First Name: " + student.FirstName);
            Response.Write("---");
            Response.Write("Last Name: " + student.LastName);
            Response.Write("---");
            Response.Write("Enrollment Date : " + student.EnrollmentDate);
            Response.Write("---");
            Response.Write("Grade: " + student.Grade);
        }

11) Run your application and see both the results from both entities printed out in the screen. If you look more carefully at the properties available for the Instructor entity you will see that we have available all the properties defined in the parent entity Human (base class) and the specific properties of the Instructor entity.

12) Now let's try and add a new Student record into the database.In the Page_Load()  comment out everything.

In the Page_Load() event handling routine of the Default.aspx page type,


  HumanDataContext ctx = new HumanDataContext();

 Student mystudent = new Student();

        mystudent.FirstName = "Grace";
        mystudent.LastName = "Jennings";
        mystudent.BirthDate = DateTime.Now.AddDays(-10000);
        mystudent.EnrollmentDate = DateTime.Now.AddDays(-234);
        mystudent.Address = "76 Blade Road";
        mystudent.City = "Coventry";
        mystudent.Region = "Midlands";
        mystudent.PostalCode = "SW4567";
        mystudent.Country = "England";
        mystudent.Phone = "493497294334";
        mystudent.Fax = "43243242442";
        mystudent.Grade = "A+";


        try
        {
            ctx.Humans.InsertOnSubmit(mystudent);
            ctx.SubmitChanges();
            Response.Write("saved!!!");
        }
        catch (Exception ex)
        {
           
            Response.Write(ex.Message);
        }

 

13) Run your application and see the "saved" message printed out in the screen. Have a look at your database table Human and see the new record added.Please note that we do not have to specify a value for the HumanType column  for the new record. LINQ to SQL will know what value to fill in for that column. In our case will be 2 which means Student.

Email me if you need the source code.Hope it helps.

Transactions in LINQ to SQL applications

In this post I would like to talk about LINQ to SQL and transactions.When I have a LINQ to SQL class I always get asked this question, "How does LINQ treat Transactions?".

When we use the DeleteOnSubmit() method or the InsertOnSubmit() method, all of those commands at some point are translated into T-SQL commands and then are executed against the database.

All of those commands live in transactions and they follow the basic rules of transaction processing. They do succeed together or fail together.

I am going to demonstrate this with a hands on example.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Region table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) We will try to update and insert some values using LINQ to SQL. We will make our application to fail in purpose so we see that all the transactions are rolled back.

6) Do a "Select * from Region"  and have a good look at the data.

6) In the Page_Load() event handling routine of the Default.aspx page type

 NorthwindDataContext ctx = new NorthwindDataContext();

      
        var myregion = (from reg in ctx.Regions
                        where reg.RegionID==1
                        select reg).Single();

        myregion.RegionDescription="Eastern Country";

  

        Region mynewregion = new Region();


        mynewregion.RegionID = 2;
        mynewregion.RegionDescription = "Midlands";   

        ctx.Regions.InsertOnSubmit(mynewregion);
  

        try
        {
            ctx.SubmitChanges();
            Response.Write("Saved in the database");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }


 

7) We have an update operation that should succeed.We update the first record from Eastern to Eastern Country. Then we do insert a value in the RegionID field that we know will cause a primary key violation.

8) Launch Profiler and start a new Trace. Make sure that you include the events Begin Tran Completed, Commit Tran Completed, Rollback Tran completed in that trace.

9) Run your application and you will receive the following error - "Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'dbo.Region'. The statement has been terminated."

10) Have a look at the Profiler Trace. It should look like my picture below

 

It is profound that the Transaction has failed. The Update statement was valid but the Insert statement was not.That is why both operations failed.

11)  Let's fix that.Comment out everything inside the Page_Load() event handling routine of the Default.aspx page and then type

NorthwindDataContext ctx = new NorthwindDataContext();

      
        var myregion = (from reg in ctx.Regions
                        where reg.RegionID==1
                        select reg).Single();

        myregion.RegionDescription="Eastern Country";

        Region mynewregion = new Region();

        mynewregion.RegionID = 5;
        mynewregion.RegionDescription = "Midlands";   

        ctx.Regions.InsertOnSubmit(mynewregion);
  

        try
        {
            ctx.SubmitChanges();
            Response.Write("Saved in the database");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

This is the same code as before , I just changed this line of code

 mynewregion.RegionID = 2;  wih this one mynewregion.RegionID = 5;

12) Run your application and also make a note of the Trace in the Profiler. The output you will see in the screen will be this one

Saved in the database 

13) Your Trace results should look like this

 

Now the transaction commits.  Do a "Select * from Region" again and have a good look at the new/updated data.

I always say to developers who build their data access layer or LINQ to SQL, that Profiler is their best friend.

Email me if you need the source code.

Hope it helps.

Select,Insert,Update and Delete data with LINQ to SQL in an ASP.Net application

As you might have guessed I am continuing my LINQ to SQL posts. I am teaching a course right now on ADO.Net 3.5 (LINQ & EF) and I know a lot of people who have learned through my blog and my style of writing.

I am going to use a step by step example to demonstrate how to select,update,insert,delete data through LINQ to SQL into the database.

If you want to have a look on how to return data from a database with LINQ to SQL and stored procedures click here. If you want to have a look on how to insert,update and delete data with LINQ to SQL and stored procedures click here.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Employees table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) Drag a Gridview web server control on the Default.aspx page.

6) In the Page_Load event handling routine of the Default.aspx page type

        NorthwindDataContext ctx = new NorthwindDataContext();

                   var emps = from myemp in ctx.Employees
                                     select myemp;
                       

        GridView1.DataSource = emps;
        GridView1.DataBind();


 7) Run your application and see the results printed in the screen.

 8) But what if wanted only a few columns of the Employees table back from the database?

we need to rewrite the code above, like that (comment out everything you have so far inside the )

 NorthwindDataContext ctx = new NorthwindDataContext();


        var emps = from myemp in ctx.Employees
                   select new
                              {
                              myemp.LastName,
                              myemp.FirstName,
                              myemp.BirthDate,
                              myemp.HireDate,
                              myemp.HomePhone,
                              myemp.City,
                              myemp.Country
                               

                              };
     


        GridView1.DataSource = emps;
        GridView1.DataBind();

 9) Run your application and see only the selected columns printed out on the screen.Now I am going to show you how to update the data.

Before we do that let's change the code a bit. Create a new method called Load_Data. Inside this method place all the code above.

So the whole method looks like this

private void Load_Data()
    {
        NorthwindDataContext ctx = new NorthwindDataContext();


        var emps = from myemp in ctx.Employees
                   select new
                              {
                                  myemp.LastName,
                                  myemp.FirstName,
                                  myemp.BirthDate,
                                  myemp.HireDate,
                                  myemp.HomePhone,
                                  myemp.City,
                                  myemp.Country
                               

                              };
     


        GridView1.DataSource = emps;
        GridView1.DataBind();
    }

 

The Page_Load() event handling routine becomes

 protected void Page_Load(object sender, EventArgs e)
    {
        Load_Data();
    }

10) Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Update".

11) In the Button1_Click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var myEmployee = ctx.Employees.Single(myemp => myemp.EmployeeID == 4);

        myEmployee.FirstName = "Julia";

        try
        {
            ctx.SubmitChanges();
            Response.Write("Updated");
        }
        catch (Exception ex)
        {

           Response.Write(ex.Message);
        }
        Load_Data();

    }

12) Run your application and you will see the LastName value for the particular record changing to the new value.Note that the change will not be persisted to the database until the SubmitChanges() method executes.

13) Now let's try and insert a new record in our Employees table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Insert".

 14) In the Button2_Click() event handling routine type

   NorthwindDataContext ctx = new NorthwindDataContext();

        Employee myemp = new Employee();
        myemp.LastName = "Kantzelis";
        myemp.FirstName = "Nikos";
        myemp.BirthDate = DateTime.Now.AddDays(-10000);
        myemp.HireDate = DateTime.Now.AddDays(-2000);
        myemp.HomePhone = "55555555";
        myemp.City = "Athens";
        myemp.Country = "Greece";

        ctx.Employees.InsertOnSubmit(myemp);

        try
        {
            ctx.SubmitChanges();
            Response.Write("Inserted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

Run your application and see the new record inserted into the table.

Note that the change will not be persisted to the database until the SubmitChanges() method executes.

15) Now let's try to delete the newly added record from our Employees table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Delete".

16) In the Button3_Click() event handling routine type

        NorthwindDataContext ctx = new NorthwindDataContext();

        var myEmployee = ctx.Employees.Single(myemp => myemp.EmployeeID == 10);

        ctx.Employees.DeleteOnSubmit(myEmployee);
       
        try
        {
            ctx.SubmitChanges();
            Response.Write("Deleted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

We get an instance of the object  that I want to delete. Then we pass it as a parameter in the DeleteOnSubmit() method.

Run your application and see the newly added record deleted from the table.

Note that the changes will not be persisted to the database until the SubmitChanges() method executes.

That is all for now. Stay tuned for many posts on EF and LINQ.

Email me if you need the source code.

Hope it helps.

Linq to SQL Lazy Loading in ASP.Net applications

In this post I would like to talk about LINQ to SQL and its native lazy loading functionality. I will show you how you can change this behavior. We will create a simple ASP.Net application to demonstrate this.

I have seen a lot of people struggling with performance issues when it comes to LINQ to SQL. That is mostly due to the lack of knowledge of how LINQ internally works.Imagine that we have two tables Products and Suppliers (Northwind database).

There is one to many relationship between those tables-entities. One supplier supplies many products. Let's say that we want to access a property from a related entity. We want to access a property from the Products entity through the Suppliers entity.The LINQ to SQL engine will create a query that will go and fetch the data at that very moment. This can be very bad for the performance of our application.Imagine the scenario that you want to retrieve rows of an entity and you need the related data as well. Let's say that you will return 1000 rows of the primary entity and its related data, that means another 1000 queries to the database.

So keep in mind that lazy loading is enabled by default and I will prove that with an example. Then as I said before I will show you how to change that behavior.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Suppliers and the Products tables and drop them on the Northwind.dbml.

4)  We want to demonstrate the default lazy loading behavior of LINQ to SQL.I want to get the Supplier's contact name and the number of the products he/she supplies us.The second thing I want returned, lives in the related table of Products.

In the Page_Load event handling routine of the Default.aspx type

      NorthwindDataContext ctx = new NorthwindDataContext();

        

            var mysuppliers = from suppl in ctx.Suppliers
                             select suppl;

          

          foreach (var supplier in mysuppliers)
            {
                Response.Write(supplier.ContactName);
                Response.Write("--");
                Response.Write(supplier.Products.Count().ToString());
                Response.Write("<br/>");
            }

 

 5) Before you run your application, in order to understand what hits your database and how often it does it, launch Profiler and start a new trace. Make sure you have included the events RPC:Starting,SQL:BatchStarting,SQL:BatchCompleted.

 6) Run your application and see the results appearing in your screen but at the same time notice the number of sql queries hitting your database. In my case it looks something like this

  • exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=1 

then I have a huge number of almost identical dynamic sql queries hitting my database.

  • exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=2

  • exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=3
  • exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=4

and the story goes on and on.They are all identical just passing in a new SupplierID value. This is not good for my application.

7) We can change this so we do not hit our database so many times.In the Page_Load event handling routine type (immediately after the NorthwindDataContext ctx = new NorthwindDataContext();) those two lines below:

            DataLoadOptions dlo = new DataLoadOptions();

            dlo.LoadWith<Supplier>(s => s.Products);

             ctx.LoadOptions = dlo;

 Do not forget to include the using System.Data.Linq;

We are basically telling LINQ engine that whenever it does fetch data from our database regarding Supplier entity/table, to fetch the Products related data as well.

8) Run your application again. Before you do that make sure you start a new trace with the events RPC:Starting,SQL:BatchStarting,SQL:BatchCompleted included.

You will see the results in your screen but have a look at the Trace output as well. We do not have so many sql statements anymore hitting our database.The one that appears in my case is this one

SELECT [t0].[SupplierID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t0].[HomePage], [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID] AS [SupplierID2], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], (
    SELECT COUNT(*)
    FROM [dbo].[Products] AS [t2]
    WHERE [t2].[SupplierID] = [t0].[SupplierID]
    ) AS [value]
FROM [dbo].[Suppliers] AS [t0]
LEFT OUTER JOIN [dbo].[Products] AS [t1] ON [t1].[SupplierID] = [t0].[SupplierID]
ORDER BY [t0].[SupplierID], [t1].[ProductID]

9) So now we have instead of lazy loading, eager loading.Hope it all makes sense now. The main point is to know how everything works ( in this case LINQ to SQL ) and how this affects the performance of our application.

Email me if you need the source code.

Hope it helps.

Retrieving data using stored procedures with LINQ to SQL in an ASP.Net application

In this post I would like to present a step by step example on how to use stored procedures with LINQ to SQL.

Many people will wonder why I am bothering talking about LINQ to SQL so much.

First of all I give a lot of seminars where people want to learn LINQ to SQL.A lot of people like and use LINQ to SQL in their projects. There are a  lot of people right now who use it extensively.

In this post I will use two stored procedures that return data from the database. If you want to check out how to use stored procedures to insert,update,delete data check this other post of mine.

Having said that I will write an extensive series of posts covering Entity framework 4.0 in the near future.

A lot of developers ask me this question:"Well nikos, we do not trust the dynamic SQL generated by the LINQ to SQL engine. We do not know how it will affect the performance of our application."

If you search the internet you will find many sites/forums where developers,DBAS argue in favor of using LINQ to SQL, LINQ to SQL and stored procs or use no LINQ to SQL at all.

My experience tells me that the generated SQL is good but if you are in a project where the DBA is very strict on what SQL hits the database you should use stored procedures.

The one thing we should point out is that LINQ to SQL and the generated classes works fine with dynamic SQL and stored procedures.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2)  We are going to create a stored procedure that returns all rows ( and columns ) from the Products table according to the CategoryID that we will pass it as an input parameter.

3) Fire up SQL Server and type this T-SQL code

USE Northwind

go

CREATE PROCEDURE [dbo].[uspProductsInCategory]
   
    @categoryID int

AS
BEGIN

    SET NOCOUNT ON;

    SELECT * FROM Products
    WHERE CategoryID = @categoryID
END

 

Now you will have the stored procedure under Programmability. Test the by passing as categoryID the value 3. You will see some rows coming back from the daabase.

4) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

5) From the Server Explorer / Data Explorer window drag the Products table and drop it on the Northwind.dbml.

6)  From the Server Explorer / Data Explorer window drag the uspProductsInCategory stored procedure and drop it on the Northwind.dbml. It will show up as a method in the .dbml file.

Select the  uspProductsInCategory method  from the .dbml file and in the Properties window in the Return Type select Product.

7) Have a look at the generated code from the Linq engine in Northwind.designer.cs.

8)  In the Page_Load event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var myproducts = ctx.uspProductsInCategory(3);

        foreach (Product product in myproducts)
        {
            Response.Write(product.ProductName);
            Response.Write("<br/>");
        }

9) Run your application and you will see the rows returned  from the database. It is very easy for someone to understand what I am doing here. I create the mandatory gateaway object (ctx) and then just call the method (stored proc) by passing it the the value 3 as the CategoryID.Then I just loop through the results.

10)  I can hear you people out there shouting that this is easy but please note that we return all the columns from the Product table and all these columns match the Product entity exactly. But what if this is not the case?

11)  We must create a new stored procedure in order to demonstrate this. We need to find the Order ID and the Customer's city, country, company name and contact name.

We will create a stored procedure that will return all these columns and respective rows by passing the country as a parameter.

In order to do that we must get data from different tables and the columns returned do not match any entity we might have in our model.This is the stored procedure. Test it in the SQL Server first in order to make sure that it works.

CREATE PROCEDURE [dbo].[uspCustomerInfoByCountry]

    @country VARCHAR(40)

AS
BEGIN

    SET NOCOUNT ON;


SELECT     Orders.OrderID, Customers.CompanyName, Customers.ContactName, Customers.Country, Customers.City
FROM         Orders INNER JOIN
                      Customers ON Orders.CustomerID = Customers.CustomerID
WHERE     (Customers.Country = @country)
end

 

12)  From the Server Explorer / Data Explorer window drag the uspCustomerInfoByCountry stored procedure and drop it on the Northwind.dbml. It will show up as a method in the .dbml file.

13) Add a button in the default.aspx page. In the button1_click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var mylist = ctx.uspCustomerInfoByCountry("USA");

        
        
        foreach (var customer in mylist)
        {
           
            Response.Write(customer.OrderID.ToString());
            Response.Write("--");
            Response.Write(customer.CompanyName);
            Response.Write("--");
            Response.Write(customer.ContactName);
            Response.Write("--");
            Response.Write(customer.City);
            Response.Write("--");
            Response.Write(customer.Country);
            Response.Write("<br/>");
        }

14) The main thing to understand is that when results of your stored procedure do not map the entities in your data model, LINQ will generate a type based on the columns that are returned by the stored procedure.

15) The code is very easy to follow.Run your application and hit the button. All the results will be printed on the screen. The mylist variable is of a type that the LINQ autogenerates.

 Email me if you need the source code.

 Hope it helps.

Using Custom Validation with LINQ to SQL in an ASP.Net application

A friend of mine is working in an ASP.Net application and using SQL Server as the backend. He also uses LINQ to SQL as his data access layer technology.

I know that Entity framework is Microsoft's main data access technology. All the money and resources are available for the evolution of Entity Framework.

If you want to read some interesting links regarding LINQ to SQL roadmap and future have a look at the following links.

http://blogs.msdn.com/b/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx

http://blogs.msdn.com/b/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx

LINQ to SQL is not dead though. It is used widely by many developers around the globe and it is perfect for RAD development.

There are updates regarding LINQ to SQL in .Net 4.0. The best blog post that describes all those changes can be found here.

My friend wants to add some custom logic in his LINQ to SQL project. I will try to demonstrate how I helped him to do so with a hands on example. I will not use his actual database or data.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose c# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Orders table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) We need to apply a business rule in our application. The first one and the only one I am going to demonstrate is this one

We cannot have the ShippedDate field to get a value that is prior to the value that the OrderDate field has. In plain english, it is impossible ( and we should not allow it to happen in our application ) to have an order shipped before it is ordered.In this example we will not insert a new record. Instead we will try to update an existing record.

6) Let's see how we can update a record from the Orders table.We will use an existing record with the OrderID =12  and we will update only the fields ShippedDate and OrderDate with incompatible values.

7) Add a label control in the Default.aspx page. In the Page_Load event handling routine add the following lines

       NorthwindDataContext ctx = new NorthwindDataContext();

        var myorderdate = (from myorder in ctx.Orders
                       where myorder.OrderID == 12
                       select myorder).Single();


        myorderdate.OrderDate = DateTime.Parse("10/10/2007");
        myorderdate.ShippedDate = DateTime.Parse("10/09/2006");

        try
        {
            ctx.SubmitChanges();
            Label1.Text = myorderdate.OrderID.ToString() + "-" + myorderdate.OrderDate.ToString() + "-" +
                          myorderdate.ShippedDate.ToString();
        }
        catch (Exception ex)
        {
          Response.Write(ex.Message);
        }

8) I am not doing something fancy here.I have written a very simple LINQ query that holds the value of the row with OrderID=12  in the myorderdate variable. Then I update the ShippedDate and OrderDate with the wrong values. Then I do save the values in the database.Run your application and you will see that you will not have a problem saving these values. That is a violation of the business rule. Now, let's see how to fix that.

9) Add a new class file to your site. Name it Order.cs. This is going to be a public partial class.I am going to write some code for the OnValidate partial method

 

public partial class Order
{

    partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        throw new NotImplementedException();
    }

}

10) Now we will make our checks inside this partial method.

partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        if (action == System.Data.Linq.ChangeAction.Update)
        if (OrderDate > ShippedDate)
        {
            throw new Exception("Order date cannot be greater than the shipped date");

        }
    }

11) The code above is easy to follow. I just check for the Update action then I make a simple check and finally I throw a simple message to the user.

12) Run your application ( make sure you have the wrong values in those two fields ) and notice that we get the exception information we have defined in the partial method OnValidate.

Partial classes are a fantastic concept and I use them a lot in my Linq to SQL applications.

You can take this example and extend it as you like.

Hope its helps. Email me, if you want the source code.

 

More Posts