Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

January 2011 - Posts

How to profile LINQ to Entities queries in your asp.net applications - part 2

In this post I will continue exploring ways on how to profile database activity when using the Entity Framework as the data access layer in our applications.

I will use a simple asp.net web site and EF to demonstrate this. If you want to read the first post of the series click here .

In this post I will use the Tracing Provider Wrappers which extend the Entity framework. You can download the whole solutions/samples project from here .The providers were developed from Jaroslaw Kowalski .

1) Unzip the .zip file and then load the EFProviderWrappers.sln . Build the solution and the projects inside it.

2) Then you can browse inside the various folders (EFProviderWrapperToolkit,EFTracingProvider) and locate inside there in the respective Bin folders the EFProviderWrapperToolkit.dll and the EFTracingProvider.dll. You will need those later on.

3) Inside the EFProviderWrappers.sln, there is another project - EFProviderWrapperDemo.

Locate the ExtendedNorthwindEntities.cs file and have a look in the first line

public partial class ExtendedNorthwindEntities : NorthwindEFEntities

and the tracing extensions (methods,events and properties) inside the file.We will use most of this code in our 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

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

5) Add a new item in your site. Add a ADO.Net Entity Data Model and name it Northwind.edmx.Place this file in the App_Code special folder.

6) From the wizard choose (create model from database) and select only the Customers, Orders and Categories tables to be included in our model.

Inside the Northwind.Designer.cs file we see the code

 public partial class NorthwindEntitiesObjectContext

This our main class NorthwindEntities that inherits and extends the ObjectContext class. This is the class that we normally instantiate in our client applications.

In this project this is not the case.We need to add the new class that extends our NorthwindEntities class. Add new special folder Bin in your site and drag and drop in there the EFProviderWrapperToolkit.dll , EFTracingProvider.dll.

7) Add another file in your site, ExtNorthwindEntities.cs. Inside this file the code, in my case anyway, looks like this

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using EFProviderWrapperToolkit;
using EFTracingProvider;
using NorthwindModel;

 

    public partial class ExtNorthwindEntities : NorthwindEntities
    {
        private TextWriter logOutput;

        public ExtNorthwindEntities()
            : this("name=NorthwindEntities")
        {
        }

public ExtNorthwindEntities(string connectionString)
 : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
                    connectionString,
                    "EFTracingProvider"
            ))
        {
        }

        #region Tracing Extensions

        private EFTracingConnection TracingConnection
        {
            get { return this.UnwrapConnection<EFTracingConnection>(); }
        }

    public event EventHandler<CommandExecutionEventArgs> CommandExecuting
    {
            add { this.TracingConnection.CommandExecuting += value; }
            remove { this.TracingConnection.CommandExecuting -= value; }
     }

     public event EventHandler<CommandExecutionEventArgs> CommandFinished
     {
            add { this.TracingConnection.CommandFinished += value; }
            remove { this.TracingConnection.CommandFinished -= value; }
        }

        public event EventHandler<CommandExecutionEventArgs> CommandFailed
        {
            add { this.TracingConnection.CommandFailed += value; }
            remove { this.TracingConnection.CommandFailed -= value; }
        }

        private void AppendToLog(object sender, CommandExecutionEventArgs e)
        {
            if (this.logOutput != null)
            {
                this.logOutput.WriteLine(e.ToTraceString().TrimEnd());
                this.logOutput.WriteLine();
            }
        }

        public TextWriter Log
        {
            get { return this.logOutput; }
            set
            {
                if ((this.logOutput != null) != (value != null))
                {
                    if (value == null)
                    {
                        CommandExecuting -= AppendToLog;
                    }
                    else
                    {
                        CommandExecuting += AppendToLog;
                    }
                }

                this.logOutput = value;
            }
        }


        #endregion


    }

 

Basically I copied out the code from  the EFProviderWrapperDemo and the ExtendedNorthwindEntities.cs file into my own class file,leaving out the caching information.

8)  Add a new item to your site, a web form. Leave the default name. Drag and drop a Gridview control on the form.

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

using (TextWriter logFile = File.CreateText("C:\\data\\mylogfile.txt"))
        {
          
            using (var ctx = new ExtNorthwindEntities())
            {
                ctx.Log = logFile;


                var query = from c in ctx.Customers
                            where c.Orders.Any(o => o.Freight > 500)
                            select c;

            

                GridView1.DataSource = query;

                GridView1.DataBind();


            }
        }

 

I created a text file in my hard disk and use the extended (with tracing info) class. Then I created an instance of this class and set the Log property of the object to the logfile object.

Let's try to add more functionality to our site by inserting a record to our Categories table.

9)  Add a new item to your site, a web form. Leave the default name-Default2.aspx. Drag and drop a Gridview control on the form.Add a button on the web form.In the Default.aspx page drop a Hyperlink control.

The Hypelink control points to the Default2.aspx page.

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Default2.aspx">
HyperLink</asp:HyperLink>

10) The code for the partial class follows:

 

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        InsertIntoCategories();
        LoadCategories();
    }


    void LoadCategories()
    {

        using (TextWriter logFile = File.AppendText("C:\\data\\mylogfile.txt"))
        {

            using (var ctx = new ExtNorthwindEntities())
            {
                ctx.Log = logFile;


                var myquery = from cat in ctx.Categories
                              select cat;

                GridView1.DataSource = myquery;

                GridView1.DataBind();


            }
        }
    }

    void InsertIntoCategories()
    {
        using (TextWriter logFile = File.AppendText("C:\\data\\mylogfile.txt"))
        {

            using (var ctx = new ExtNorthwindEntities())
            {
                ctx.Log = logFile;
                Category cat = new Category();

                cat.CategoryID = 10;
                cat.CategoryName = "Nice food";

                ctx.AddToCategories(cat);

                ctx.SaveChanges();

            }


        }

    }
}

 

11) Run your website and see the records being displayed in the Default.aspx page. Click the hyperlink and you will navigate to the Default2.aspx pag and click the button in that page. Observe the new record added to the Categories table.

12) When I navigate to the mylogfile.txt I see all the queries executed against the server logged into this file. In my case the contents of the log file are:

SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]
WHERE  EXISTS (SELECT
    1 AS [C1]
    FROM [dbo].[Orders] AS [Extent2]
    WHERE ([Extent1].[CustomerID] = [Extent2].[CustomerID]) AND ([Extent2].[Freight] > cast(500 as decimal(18)))
)

insert [dbo].[Categories]([CategoryName], [Description], [Picture])
values (@0, null, null)
select [CategoryID]
from [dbo].[Categories]
where @@ROWCOUNT > 0 and [CategoryID] = scope_identity()
-- @0 (dbtype=String, size=15, direction=Input) = "Nice food"

SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [Extent1]

 

Drop me an email If you want me to send you the source code.

Hope it helps!!!

How to profile LINQ to Entities queries in your asp.net applications - part 1

I have been teaching ASP.Net and EF in one of my classes and I have been asked on the various ways we can profile database activity. Everyone that I know that uses EF as its data access layer has the same question.

"How can I see the T-SQL code that the LINQ to Entities engine generates on the fly?"

I know a lot of people use VS studio built-in visualisers but that is not enough. A lot of developers use SQL Server Profiler. That is also a good solution since we can see the queries(generated from the EF enfgine) executed against our data store. In this post I will show you how to display the actual query in the .aspx page.More posts will follow on EF queries database profiling.

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 ADO.Net Entity Data Model and name it Northwind.edmx.Place this file in the App_Code special folder.

3) From the wizard choose (create model from database) and select only the Customers and Orders table to be included in our model.

4)  Add a new item to your site, a web form. Leave the default name. Drag and drop a Gridview control on the form.

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

 

   NorthwindEntities ctx = new NorthwindEntities();
       

        var query= from c in ctx.Customers
                        where c.Orders.Any(o => o.Freight > 1000)
                        select c;

        var myQuery = (ObjectQuery)query;
            
         
      
        Response.Write(myQuery.ToTraceString());

        Response.Write("<hr/>");

        GridView1.DataSource = query;

        GridView1.DataBind();

 5)  Run your application and see the results.You will see the results in the gridview and also the generated query.I am using the ObjectQuery.ToTraceString method that returns the commands executed against the data store.IN this case the query executed against the datasource will be something like this:

 SELECT [Extent1].[CustomerID] AS [CustomerID], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ContactName] AS [ContactName], [Extent1].[ContactTitle] AS [ContactTitle], [Extent1].[Address] AS [Address], [Extent1].[City] AS [City], [Extent1].[Region] AS [Region], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], [Extent1].[Phone] AS [Phone], [Extent1].[Fax] AS [Fax] FROM [dbo].[Customers] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Orders] AS [Extent2] WHERE ([Extent1].[CustomerID] = [Extent2].[CustomerID]) AND ([Extent2].[Freight] > cast(1000 as decimal(18))) )

 

Email me if you want the source code.

Hope it helps.

An abundance of LINQ queries and expressions using both the query and method syntax.

In this post I will be writing LINQ queries against an array of strings, an array of integers.Moreover I will be using LINQ to query an SQL Server database.

I can use LINQ against arrays since the array of strings/integers implement the IENumerable interface.

I thought it would be a good idea to use both the method syntax and the query syntax.

There are other places on the net where you can find examples of LINQ queries but I decided to create a big post using as many LINQ examples as possible.

We will start with very simple queries 

1) Fire up Visual Studio 2008/2010. Express editions will work fine.

2) Create an empty web site. Give it the name of your choice.Choose C# as the development language.

3) Add a web form item to your site. Leave the default name. We will start off with a very simple example.

4) In the Page_Load event handling routine type (Do not comment out the array declaration. Very often in this post you will have to comment out bits of code but not this one)

  string[] names = { "Hilary""George""Paul""Mary""Christine""Fred",
 "John", "Oliver""Ken""Jessy""Joddie""Helen""Wesley""Elvis" };


        foreach (string name in names)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }

 We have our single array and just loop through the items.Run your application.Then comment out the foreach loop.

5) If we want to find all the names in the array that start with the letter "M", we can type in the Page_Load event handling routine,

  foreach (string name in names)
        {
            if (name.StartsWith("M"))
                Response.Write(name);
            Response.Write("<br/>");
        }
Run your application and see the results.

6) Now let's have the same result by writing a LINQ query.Comment out the previous foreach loop and type

        var namesStartWithM = from name in names
                              where name.StartsWith("M")
                              select name;

        foreach (string item in namesStartWithM)
        {
            Response.Write(item);
            Response.Write("<br/>");
        }
 
Run your application and see the results.
7) Now let's rewrite the previous query using lambdas 
Comment out the previous foreach loop and the var statement  and type
 var namesStartWithM = names.Where(n => n.StartsWith("M"));

        foreach (string item in namesStartWithM)
        {
            Response.Write(item);
            Response.Write("<br/>");
        }

 Run your application again and see the results.

8) Now let's rewrite the previous query in a much simpler way.Comment out the previous foreach loop and the var statement  and type

        foreach (string name in names.Where(n => n.StartsWith("M")))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }

 This is a much simple way. Run your application again and see the results.

9)  Now let's say we want to find only the names that have more than 6 characters.

Comment out the previous foreach loop and in the Page_Load event handling routine
 foreach (string name in names.Where(n => n.Length>6))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
 Run your application again and see the results.We can rewrite the above query using the query syntax.
Comment out the previous foreach loop  and type
  var mynames = from name in names
                where name.Length > 6
                select name;

        foreach (string item in mynames)
        {
            Response.Write(item);
            Response.Write("<br/>");
        }
Run your application again and see the results. 
10)  Now let's say we want to order our array using the query syntax. 
Comment out the previous foreach loop and the var statement in the Page_Load event handling routine type
  var query = from name in names
                    orderby name
                    select name;

        foreach (string name in query)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
 
Run your application and see the results.
11)  Now let's say we want to order our array using the method syntax. 
Comment out the previous foreach loop and the var statement. In the Page_Load event handling routine type
    foreach (string name in names.OrderBy(n => n))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
 
Run your application and see the results.
We can also order our list descending. Change the first line of the loop with this line
foreach (string name in names.OrderByDescending(n => n))
12)  But what if we wanted to get only some elements of the array and ignore the first 4.
Comment out the previous foreach loop. In the Page_Load event handling routine type
   foreach (string name in names.Skip(4))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
Run your application and see the results. Comment out the previous foreach loop and type
var mynames = from name in names.Skip(4)
                      select name;

        foreach (string name in mynames)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
13)  But what if wanted to take the first four elements of the array only.
Comment out the previous foreach loop and var statement and type
    foreach (string name in names.Take(4))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
Run your application and see the results.Now let's do the same using the query syntax.Comment out the previous foreach loop and type
 var mynames = from name in names.Take(4)
                      select name;

        foreach (string name in mynames)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
14) Let's move on and see a more complicated example where we want to have a logical operator (e.g &&). We want to find all names that start with "J" and are more than 4 characters.
Comment out the previous foreach loop and var statement and in the Page_Load event handling routine type 
var mynames = from name in names
                      where name.StartsWith("J") && name.Length > 4
                      select name;


        foreach (string name in mynames)
        {
             Response.Write(name);
           Response.Write("<br/>");
        }
 
Run your application and see the results.  
15) Let's rewrite the above query using the method syntax.Comment out the previous foreach loop  and the var statement. In the Page_Load event handling routine type 
 
   var mynames = names.Where(n => n.StartsWith("J") && n.Length > 4);

        foreach (string name in mynames)
        {
            Response.Write(name);
           Response.Write("<br/>");
        }
 
Run your application and see the results.

16) Now let's see more examples on how to write LINQ queries. I will use LINQ to SQL to demonstrate that.

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

17) 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.

18) From the Server Explorer / Data Explorer window drag the Customers,Orders,Order Details tables and drop them on the Northwind.dbml.

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

20) Add a new web form to your site.Name it LINQtoSQL.aspx. Drag a Gridview web server control on the LINQtoSQL.aspx page.Let's say we want to return all the customers.

21) In the Page_Load event handling routine of the LINQtoSQL.aspx page type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    select mycust;


        GridView1.DataSource = query;
        GridView1.DataBind();
Note that we return all the fields,columns from the Customers table.If we want to use the method syntax, replace this line of code
var query = from mycust in ctx.Customers
                    select mycust;
with this line of code
var query = ctx.Customers.Select(c => c);
 
Run your application and see the results. 
22) But if we wanted to return only one field,column of the underlying table(Customers) ? Change this line of code
var query = from mycust in ctx.Customers
                    select mycust;
with this one
var query = from mycust in ctx.Customers
                    select mycust.ContactName;
Let's rewrite the query with the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
                    select mycust.ContactName;  
with this line of code
var query = ctx.Customers.Select(c => c.ContactName);
 
Run your application and see the results. 
23) But what if wanted to have 2 or more fields returned from the Customers table?Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
 NorthwindDataContext ctx = new NorthwindDataContext();

 var query = from mycust in ctx.Customers
 select new { mycust.ContactName, mycust.CompanyName, mycust.Country };


 GridView1.DataSource = query;
 GridView1.DataBind();
 
Run your application and see the results.
Let's rewrite the query with the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
        select new { mycust.ContactName, mycust.CompanyName, mycust.Country };
with this line of code
var query = ctx.Customers.Select(c => new { c.ContactName, c.CompanyName, c.Country });
 
Run your application and see the results.
What we did here was to create a new anonymous type.If you want to see what kind of anonymous type this is, after this line
GridView1.DataBind();
type
Response.Write(query.First().GetType());  
Run your application and see the results.In my case the anonymous type looks like this 
<>f__AnonymousType0`3[System.String,System.String,System.String]

24) Let's imagine we have a custom named type e.g Employee and we wanted to select data into this named type from the anonymous type we just demonstrated before.Add another item in your site, a class file. Name it Employee.cs.Inside the .cs file type 
class Employee
{
 
   
    public string Name { getset; }
 
    public string Company { getset; }

    public string Country { getset; }
 
}
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
 NorthwindDataContext ctx = new NorthwindDataContext();

 var query = from mycust in ctx.Customers
 select new Employee { Name = mycust.ContactName, Company= mycust.CompanyName,
 Country = mycust.Country };


        foreach (Employee item in query)
        {
            Response.Write(item.Name);
            Response.Write("<br/>");
            Response.Write(item.Company);
            Response.Write("<br/>");
            Response.Write(item.Country);
            Response.Write("<br/>");
        }
Have a look at the code above to see how we select data into a named type from an anonymous type.
Run your application and see the data selected into the Employee type printed out in the screen.
Let's rewrite the query with the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
select new Employee { Name = mycust.ContactName, Company= mycust.CompanyName,
 Country = mycust.Country };
with this line of code
var query = ctx.Customers.Select(mycust => new Employee 
        { Name = mycust.ContactName, Company = mycust.CompanyName, 
Country = mycust.Country }); 
Run your application and see the results.
25) Let's move on to something else. We have orders that are associated with customers. Let's say we want to get only the order date for each order regardless of the customer it is associated with.
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    from order in mycust.Orders
                    select order.OrderDate;

        GridView1.DataSource = query;
        GridView1.DataBind();
 
Run your application and see the results.
26) Let's move on with our example and see how we can filter our results and return a subset of the data. We want to get all the columns of the Customers table, only for them that they live in Germany.
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
 
 NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    where mycust.Country == "Germany"
                    select mycust;

        GridView1.DataSource = query;
        GridView1.DataBind();
Run your application and see the results.Now let's rewrite the query using the method syntax. Replace this line of code
var query = from mycust in ctx.Customers
                    where mycust.Country == "Germany"
                    select mycust;
with this line of code
var query = ctx.Customers.Where(mycust => mycust.Country=="Germany");
Run your application and see the results.
27) We want to get the customers that have more than 17 orders. 
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
   NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    where mycust.Orders.Count > 17
                    select mycust;

        GridView1.DataSource = query;
        GridView1.DataBind();
Run your application and see the results.Now let's rewrite the query using the method syntax. Replace this line of code
 var query = from mycust in ctx.Customers
                    where mycust.Orders.Count > 17
                    select mycust;
with this line of code
 
 var query = ctx.Customers.Where(mycust => mycust.Orders.Count > 17);
 
Run your application and see the results.
28) We want to get the customers that have more than 17 orders and live in Germany. 
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
        where mycust.Orders.Count > 17 && mycust.Country=="Germany"
        select mycust;

        GridView1.DataSource = query;
        GridView1.DataBind();
Run your application and see the results.If you want to use OR operator just replace the && with || 
Let's use the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
            where mycust.Orders.Count > 17 && mycust.Country=="Germany"
            select mycust;
with this line of code
var query = ctx.Customers.Where(mycust => mycust.Orders.Count > 17 && mycust.Country=="Germany");
 
Run your application and see the results.
29) We want to get the customers and their respective orders. We need to perform some sort of join.Comment out everything in the Page_Load event handling routine and type
 
  NorthwindDataContext ctx = new NorthwindDataContext();

        var query =
        from c in ctx.Customers
        join o in ctx.Orders on c.CustomerID equals o.CustomerID
            into orders
            select new { c.ContactName, OrderCount = orders.Count() };


        GridView1.DataSource = query;
        GridView1.DataBind();
  
Run your application and see the results.
30) Add another web form to your site, name it ArraysOfInt.aspx. Let's investigate more extension methods.
 In the Page_Load event handling routine type 
 
        int[] mynums = { 12, 45, 23, 34, 67, 45, 78, 11, 74, 81, 190, 99, 102,
 109, 112, 116, 27, 121, 65, 134, 19, 154 };

       
        var query = mynums.Any();
   
        
        Response.Write(query);
 
Run your application and see the results. You will get back True. The code above checks if the array has items in it or not.
31) Let's say that we want to check if all the items in the array are greater than 5.
Change the the previous var statement with this one
var query = mynums.All(p => p > 5);
 
Run your application and see the results. You will get back True.
32) If we wanted to check if the value 45 is included in this array. Change the previous var statement with this
 var query = mynums.Contains(45);
Run your application and see the results.
33) What if wanted to find the number of the items in the array? What if wanted to find the sum, the minimum, the maximum, the average, the first, the last values in the array?Comment out the previous var statement and type the following. After you finish with the first var statement, comment it out and follow with the next one.Only one var statement should be uncommented at each time.
 var query = mynums.Count();

var query = mynums.Sum();
var query = mynums.Min();

 var query = mynums.Max();

var query = mynums.Average();

 var query = mynums.First();

var query = mynums.Last();
 
Run your application and see the results.
34) What if wanted to get the first number in the sequence that is greater than 100?Comment out the previous var statement and type
 var query = mynums.First(p => p > 100);
Run your application and see the results.
35) What if wanted to get the last item that is greater than 100? Comment out the previous var statement and type
 var query = mynums.Last(p => p > 100); 
Run your application and see the results.
I could go on and on but I think you have a good view now on how LINQ works are what are the main extension methods.
 Email me if you need the source code.
 Hope it helps!!!
  
Investigating Lambda expressions in LINQ

With the introduction of LINQ and its many flavours, developers started using this ORM technology to perform queries against an xml document, an sql server database, a in-memory collection of objects.

They were fascinated by its general purpose and its unified approach which can be summed up as "Learn one API-one model and use it against various data sources."

I say to people that are still new to LINQ or they do not know exactly why LINQ works the way it works, to have look at the enhancements applied to the C# language in version 3.0.

By that I mean, collection initialisers,object intialisers,extensions methods,auto-implemented properties,anonymous methods,anonymous types, the "var" keyword.Search the net for information on those.

Make sure before you go on implementing LINQ based applications, that you have all this knowledge under your belt.

In this post I will explore the issue that puzzles most developers, Lambda expressions.

I have seen people using LINQ with the query syntax which is more T-SQL like and more familiar to most developers.

Lambdas are used extensively in LINQ queries which are by nature pretty functional.  So we must understand Lambdas if we want to use LINQ more efficiently.

Lambdas are a shorthand(shortcut) for anonymous methods/delegates. We all know that a delegate allow us to create a variable that points to a method.

Through that variable we can invoke the method at any time.So just to make sure that everyone follows along, Lambdas are essential in LINQ method syntax.

I will demonstrate that with a hands on example using an asp.net web site and c#.I will use a simple array of strings to do that.

 

1) Fire up Visual Studio 2008/2010. Express editions will work fine.

2) Create an empty web site. Give it the name of your choice.

3) Add a web form item to your site. Leave the default name

4) We will try something first with delegates and then we will move on to show the same example using Lambdas

5) Let's just say that we want to find all names from a list of names that contain the letter "A" and letter "B". We can solve that problem without using delegates but in this example I will use delegates.

6) Type this code inside the _Default class (the first line in the code below is just for reference)

public partial class _Default : System.Web.UI.Page
{


    public delegate bool MyUsefulStringFunctions(string myS);


    public static bool ContainsA(string s)
        {
            return s.Contains("A");
        }

        public static bool ContainsB(string s)
        {
            return s.Contains("B");
        }

   public static string[] ManipulateArray(string[] theStrings, 
MyUsefulStringFunctions theFunction)
   {
          ArrayList theList = new ArrayList();
            foreach (string s in theStrings)
            {
                if (theFunction(s))
                {
                    theList.Add(s);
                }
            }

            return (string[])theList.ToArray(typeof(string));

   }

  
 

 7) First I create a delegate that returns a type bool and defines an input parameter of type string.Remember, this is like defining an object type but what we actually see is a method signature for a function.

 public delegate bool MyUsefulStringFunctions(string myS);


 8) Then I create two instances of that delegate. Those two methods return bool and have an input parameter of type string.

 

        public static bool ContainsA(string s)
        {
            return s.Contains("A");
        }

        public static bool ContainsB(string s)
        {
            return s.Contains("B");
        }

 

 9) Now we can pass these methods as an input parameter to another method I have created.

 public static string[] ManipulateArray(string[] theStrings, 
MyUsefulStringFunctions theFunction)
   {
          ArrayList theList = new ArrayList();
            foreach (string s in theStrings)
            {
                if (theFunction(s))
                {
                    theList.Add(s);
                }
            }

            return (string[])theList.ToArray(typeof(string));

   }

 

This method accepts an array of strings and accepts an instance of the delegate.Then for each string in the array of strings that I pass to this method I will get this delegate (algorithm) applied to them.

All those strings that satisfy the algorithm (e.g contain the letter A) will be added to a new array of strings and returned back from the method.

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

 

 string[] names = { "Aidan""George""Bryony""Mary""Joy""Alastair"
"John""Oliver""Ken""Jessy""Joddie""Helen""Wesley""Elvis" };

 string[] mynamesA = ManipulateArray(names, ContainsA);

 string[] mynamesB = ManipulateArray(names, ContainsB);

    foreach (string s in mynamesA)
      {
           Response.Write(s);
           Response.Write("<br/>");
      }

 

11) I am defining an array of strings(names). Then I call the ManipulateArray method by passing the array of strings (names) and as a second parameter I pass ContainsA , which is an instance of our delegate declaration.

I save the return results of my method to another array(mynamesA,mynamesB) and I just loop through it. 

Run your application and see the names starting with A printed out on the screen.Make sure you add breakpoints so you can see the flow of the execution.Obviously we can achieve that end result without using delegates. I just wanted to tell/remind you what delegates are and how we use them.

12) Now I am going to rewrite my small application by using anonymous methods

  • comment out this block of code
 //public static bool ContainsA(string s)
    //{
    //    return s.Contains("A");
    //}

    //public static bool ContainsB(string s)
    //{
    //    return s.Contains("B");
    //}
  •  comment out everything inside the Page_Load event handling routine, and hen type

  string[] names = { "Aidan""George""Bryony""Mary""Joy""Alastair"
"John""Oliver""Ken""Jessy""Joddie""Helen""Wesley""Elvis" };

          

string[] mynamesA = ManipulateArray(names, delegate(string theS) 
return theS.Contains("A"); });



            foreach (string s in mynamesA)
            {
                Response.Write(s);
                Response.Write("<br/>");
            } 

Make sure your application runs as expected.We pass to the method (ManipulateArray) an array of strings like before and yes you guessed it, a delegate as a second input parameter. A delegate that if you look carefully is of type

  public delegate bool MyUsefulStringFunctions(string myS);

 

because it returns a boolean and accepts a string.Well, you must be thinking when I am going to learn about lambdas... First of all I am going to write more posts on LINQ quantifiers,operators using both the query syntax and the method syntax. In this example I am going to change our small application to include lambdas. But as I said again the lambdas are like a shorthand definition of anonymous methods. They are anonymous methods in disguise.

 I want you to just change this line of code

string[] mynamesA = ManipulateArray(names, delegate(string theS) 
return theS.Contains("A"); });

with this line of code.

string[] mynamesA = ManipulateArray(names, (theS =>theS.Contains("A")));

Run again your application.It will work.

theS is the input parameter. The => is the seperator and the theS.Contains("A") is just the algorithm applied to all of the strings in the names array.

We basically say "Evaluate every string we give you, and if it contains the letter A,return true otherwise return false."

Email me if you need the source code. Stay tuned for more posts with lambda expressions.

Hope it helps!!!!!


Creating a Data Access Layer in ASP.Net applications for inserting,selecting,deleting and updating data

In this post I will be continuing my series of posts regarding data access methodologies/technologies that as I call them are pro ORM. I will be demonstrating how to use the connected and the disconnected data access models to retrieve,insert,update,delete data from a database.This is going to be a huge post so embrace yourself.Our database will be an SQL Server database.I am urging you to read this post and this post of mine that are also in this blog.These posts explore the disconnected and connected model and the basic objects(classes) we use to retrieve data.

I will keep using LINQ to SQL and EF as my main data access technologies for my projects. But for anyone to be considered a serious ADO.Net Developer, must understand ADO.Net 2.0 and 1.1.

I have many posts in this blog regarding LINQ to SQL. I will be posting about 20 posts in Entity Framework until the end of this year. So stay tuned.

In this post I will be showing how to select,insert,update,delete data through an access layer. In my other two posts, all the data access code was written in the Presentation layer.

This is not the best way to do things. When the code gets larger and more complicated the code will be less maintainable and less extensible. We must have a separate place to place the data access code.Another layer that will be responsible for accessing the data store.

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) Add a new item to your site, a web form. Leave the default name, Default.aspx

3) Add ASP.Net folder in your site, App_Code. Add another folder inside the App_Code special folder and name it DataAccess.

4) Go to View - > Server Explorer (Database Explorer) and add a connection to the Northwind database.You have just to set the instance of the SQL Server and the name of the database.Test the connection.

5) In your web.config (which is pretty much empty) add a <connectionStrings>. Ιn my case it like the one below. In your case you must set the Data Source property to your SQL server instance.

 

 

<connectionStrings>
<add name="NorthwindConnectionString" 
connectionString="Data Source=FOFO-PC\SQLEXPRESS;Initial Catalog=Northwind;
Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

 

6) Add a class file inside the DataAccess folder. Name the file Connection.cs.So we have a Connection class. The code for the whole class is

 

public class Connection
{

public static SqlConnection GetDBConnection()
{
      
string connectionString = ConfigurationManager.
ConnectionStrings["NorthwindConnectionString"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);

      
connection.Open();
return connection;
    }

}

This is very simple static method. It returns an SQL connection.Inside this method I do the following.

 

  • I get the connection string from the configuration file

  • I create a new connection object

  • I open the connection and return the connection object.

Do not forget to add these lines of code in the top of the file.

 

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

7) In the Default.aspx page add a Label control. Leave the default name.

8) Do not forget to add this line of code in the top of the Default.aspx.cs file

 

using System.Data.SqlClient;

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

 

if (!IsPostBack)
    { 
        Label1.Text = "We will test the connection to the database.";

        try 
{         
        SqlConnection connection = Connection.GetDBConnection();
        connection.Close();
        Label1.Text = "We opened and closed the connection to the database";
}
catch (SqlException ex)
{

Label1.Text="We cannot connect to the database" + ex.Message;
}
    } 

10) Run your application and see the result. If you followed everything correctly you would be able to open and close a connection to the database.

The code above is easy to follow. I just get the connection object back from the GetDBConnection function that "lives" inside the Connection class, which is our data access layer.

I just place the code inside a try catch statement and I make sure the code runs the first time the page loads, so the whole code is inside an If statement.

11) Now let's retrieve some data from our database. We will add another file to the DataAccess folder.So add a class file inside that folder and call it DataAccess.cs.

For this example just imagine that we want to connect to the database and get all the prices for each product.

In the DataAccess.cs file we must create a static method.

 

public static SqlDataReader GetProductPrices()
    {
        string sql = "SELECT ProductName, UnitPrice FROM Products";

        SqlConnection connection = Connection.GetDBConnection();
        SqlCommand command = new SqlCommand(sql, connection);
        command.CommandType = CommandType.Text;

SqlDataReader reader = command.
ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection);
        return reader;
    }

The code above is very simple.I have a static method that returns a DataReader object.Inside the method

 

  • I define the SQL Statement

  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the sql statement and the connection object
  • I create a DataReader object and return it.I also specify that the query returns a single result set - CommandBehavior.SingleResult
  • Moreover I specify that the connection object must be closed when the DataReader object closes - CommandBehavior.CloseConnection  

Make sure you add these lines of code in the top of the file.

 

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

12) Add a new web form to your site. Name it GetProductPrices.aspx.Add a BulletedList control to the page.Leave the default name. Add a Label control as well. Leave the default name.

13) In the Page_Load event handling routine of the GetProductPrices.aspx page type

 

if (!IsPostBack)
     {
            Label1.Text = "We must get all the product prices";
           
       try
       {
          using (SqlDataReader reader = DataAccess.GetProductPrices())
          {
          while (reader.Read())
          {
          string productName = reader.GetString(0);
          decimal unitPrice = reader.GetDecimal(1);

          string item = String.Format("{0}, {1:C2}", productName, unitPrice);
          BulletedList1.Items.Add(item);
              }
           }
                Label1.Text = "All product prices are listed.";
        }
            catch (SqlException ex)
            {

                Label1.Text = "Cannot get the product price." + ex.Message;
            }
        }

 

The code above is very easy to follow. I create a DataReader object by calling the GetProductPrices() method from the DataAccess class.

Then I loop through the reader,format some values and add each item pair (product-price) to the bulleted list.

I just place the code inside a try catch statement and I make sure the code runs the first time the page loads, so the whole code is inside an If statement.

Add bookmarks to see how the code runs in debug mode.

Do not forget to add this line of code in the top of the GetProductPrices.aspx.cs file

 

 

using System.Data.SqlClient;
14) Run your application and see the products and their prices listed.
15) Now let's add another page to the site. We call it GetAveragePrice.aspx.We want to get the average price of all the products in the database.In the DataAccess.cs file add another static method. 
The code for this static method looks like this
 public static decimal GetProductsAveragePrice()
    {
        string sql = "SELECT AVG(UnitPrice) FROM Products";
        object result;
        

        using (SqlConnection connection = Connection.GetDBConnection())
        {
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            result = command.ExecuteScalar();
        }

        return (decimal)result;
    }
 

16) The code above is very simple.I have a static method that returns a decimal value.Inside the method

  • I define the SQL Statement

  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the sql statement and the connection object
  • I use the ExecuteScalar() method of the command object to return the average price
 
17) Add a Label control to the GetAveragePrice.aspx page. In the Page_Load event handling routine of the GetAveragePrice.aspx page type 
if (!IsPostBack)
{
Label1.Text = "We try to get the average price.";

try
{
decimal averagePrice = DataAccess.GetProductsAveragePrice();
Label1.Text = string.Format("Average price for all products: {0:C2}",
 averagePrice);
}
catch (SqlException ex)
{
              
         Label1.Text = "Cannot get average price." +ex.Message;
}
}
18) Run your application and see the average price for all products in the database printed out in the screen. 
19) Now, we need to update the prices of the products by value that the user specifies. Add another page in your site. Name it UpDateProductPrices.aspx.
Add a Button,Label and a TextBox control to that page. Leave the default names.
20) In the DataAccess.cs file add another static method. 
public static int UpdateAllPricesBy(decimal price)
{
string sql = "UPDATE Products SET UnitPrice=UnitPrice + " + price.ToString();
int rowsAffected;

  using (SqlConnection connection = Connection.GetDBConnection())
        {
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            rowsAffected = command.ExecuteNonQuery();
        }

        return rowsAffected;
    }
 

21) The code above is very simple.I have a static method that I pass a value that the user will enter. It also returns a integer value.Inside the method

  • I define the SQL Statement

  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the sql statement and the connection object
  • I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.
22) In the Button1_Click event handling routine of the UpDateProductPrices.aspx page type
 try
        {
            decimal price = decimal.Parse(TextBox1.Text);
            int rowsAffected = DataAccess.UpdateAllPricesBy(price);
            Label1.Text = string.Format("{0} rows affected.", rowsAffected);
        }
        catch (SqlException ex)
        {
     
            Label1.Text = "Cannot update prices." + ex.Message;
        }
 

Do not forget to add this line of code in the top of the UpDateProductPrices.aspx.cs file

using System.Data.SqlClient;
23) The  code above is very simple.I just call the UpdateAllPricesBy method and pass it as an input parameter the value the user entered in the textbox. Run your application (with bookmarks) and see the rows affected by the update statement. 
24) We move on with our example and let's say we want to delete a product. The user will specify a value (ProducItD) through the interface,click a button and then the product will be deleted. Add another page in your site. Name it DeleteProduct.aspx.Add a Button,Label and a TextBox control to that page. Leave the default names. 
25) I am going to use a stored procedure to delete a product. The stored procedure "DeleteProduct" code looks something like this
CREATE PROCEDURE dbo.DeleteProduct
(
@ProductID int
)
AS
BEGIN TRANSACTION

DELETE FROM [Order Details] WHERE ProductID=@ProductID
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
BEGIN
DELETE FROM Products WHERE ProductID=@ProductID
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END
GO
 
26) In the DataAccess.cs file add another static method and name it DeleteProduct().
public static int DeleteProduct(int productID)
  {
    int rowsAffected = 0;

    using (SqlConnection connection = Connection.GetDBConnection())
    {
    SqlCommand command = new SqlCommand("DeleteProduct", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@ProductID"SqlDbType.Int).Value = productID;

    rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }

27) The code above is very simple.I have a static method DeleteProduct() that I pass a value that the user will enter. It also returns a integer value.Inside the method

  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object
  • I pass the value entered by the user in the textbox as an input parameter to the stored procedure
  • I use the ExecuteNonQuery() method of the command object to return the rows affected by the delete statement.

 

28) In the Button1_Click event handling routine of the DeleteProduct.aspx page type
try
        {
            int productID = int.Parse(TextBox1.Text);
            int rowsAffected = DataAccess.DeleteProduct(productID);
            Label1.Text = string.Format("{0} rows affected.", rowsAffected);
        }
        catch (SqlException ex)
        {

            Label1.Text = "Cannot delete product." + ex.Message;
        }

 

Do not forget to add this line of code in the top of the DeleteProduct.aspx.cs file


using System.Data.SqlClient;

29) Run your application (with bookmarks) and try to delete a row. See the rows affected by the delete statement.
30) We move on with our example and let's say we want to edit our product. Add another page in your site. Name it EditProduct.aspx.Add a GridView control on the page. Leave the default name.
Add an ObjectDataSource control on the page. Leave the default name.Set the DataSourceID property of the GridView control to ObjectDataSource1. When you try to configure your ObjectdDataSource object and select as business object the DataAccess class you realise you must provide methods for Select,Insert,Update,Delete operations.
First we must provide the method for the Select method.Inside the DataAccess.cs file, add another static method GetProducts()
 public static SqlDataReader GetProducts()
    {
        string sql = "SELECT * FROM Products";

        SqlConnection connection = Connection.GetDBConnection();
        SqlCommand command = new SqlCommand(sql, connection);
        command.CommandType = CommandType.Text;

SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult
 | CommandBehavior.CloseConnection);
        return reader;
    }
31) Now, we must write the method to insert products in the database. I will use a stored procedure to do that-InsertProduct. The code for the stored procedure is following
 CREATE PROCEDURE dbo.InsertProduct
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
INSERT Products
(
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued
)
VALUES
(
@ProductName,
@SupplierID,
@CategoryID,
@QuantityPerUnit,
@UnitPrice,
@UnitsInStock,
@UnitsOnOrder,
@ReorderLevel,
@Discontinued
)

RETURN
GO
 
32) Inside the DataAccess.cs file, add another static method InsertProduct()
 public static int InsertProduct(
                             int productID,
                             string productName,
                             int supplierID,
                             int categoryID,
                             string quantityPerUnit,
                             decimal unitPrice,
                             int unitsInStock,
                             int unitsOnOrder,
                             int reorderLevel,
                             bool discontinued)
    {
        int rowsAffected = 0;

        using (SqlConnection connection = Connection.GetDBConnection())
        {
 SqlCommand command = new SqlCommand("InsertProduct", connection);
 command.CommandType = CommandType.StoredProcedure;
 command.Parameters.Add("@ProductName"SqlDbType.NVarChar, 40).Value = 
productName;

 command.Parameters.Add("@SupplierID"SqlDbType.Int).Value = supplierID;
 command.Parameters.Add("@CategoryID"SqlDbType.Int).Value = categoryID;
 command.Parameters.Add("@QuantityPerUnit"SqlDbType.NVarChar, 20).Value = 
quantityPerUnit;

 command.Parameters.Add("@UnitPrice"SqlDbType.Money).Value = unitPrice;
 command.Parameters.Add("@UnitsInStock"SqlDbType.SmallInt).Value = 
unitsInStock;
 command.Parameters.Add("@UnitsOnOrder"SqlDbType.SmallInt).Value = 
unitsOnOrder;
 command.Parameters.Add("@ReorderLevel"SqlDbType.SmallInt).Value = 
reorderLevel;
 command.Parameters.Add("@Discontinued"SqlDbType.Bit).Value = discontinued;

  rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }
 

33) The code above is very simple.I have a static method that I pass values as input parameters. It also returns a integer value.Inside the method

  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object
  • I pass the input parameter values of the method as input parameters to the stored procedure
  • I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.

34) Now, we must write the method to update products in the database. I will use a stored procedure to do that - UpDateProduct. The code for the stored procedure is following
CREATE PROCEDURE dbo.UpdateProduct
(
@ProductID int,
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
UPDATE Products SET
ProductName = @ProductName,
SupplierID = @SupplierID,
CategoryID = @CategoryID,
QuantityPerUnit = @QuantityPerUnit,
UnitPrice = @UnitPrice,
UnitsInStock = @UnitsInStock,
UnitsOnOrder = @UnitsOnOrder,
ReorderLevel = @ReorderLevel,
Discontinued = @Discontinued
WHERE
ProductID=@ProductID

RETURN
GO
 
 
35) Inside the DataAccess.cs file, add another static method UpdateProduct()
    public static int UpdateProduct(
                             int productID,
                             string productName,
                             int supplierID,
                             int categoryID,
                             string quantityPerUnit,
                             decimal unitPrice,
                             int unitsInStock,
                             int unitsOnOrder,
                             int reorderLevel,
                             bool discontinued)
    {
        int rowsAffected = 0;

        using (SqlConnection connection = Connection.GetDBConnection())
        {
            SqlCommand command = new SqlCommand("UpdateProduct", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ProductID"SqlDbType.Int).Value = productID;
command.Parameters.Add("@ProductName"SqlDbType.NVarChar, 40).Value =
 productName;

command.Parameters.Add("@SupplierID"SqlDbType.Int).Value = supplierID;
command.Parameters.Add("@CategoryID"SqlDbType.Int).Value = categoryID;
command.Parameters.Add("@QuantityPerUnit"SqlDbType.NVarChar, 20).Value = 
quantityPerUnit;

command.Parameters.Add("@UnitPrice"SqlDbType.Money).Value = unitPrice;
command.Parameters.Add("@UnitsInStock"SqlDbType.SmallInt).Value = 
unitsInStock;
command.Parameters.Add("@UnitsOnOrder"SqlDbType.SmallInt).Value = 
unitsOnOrder;
command.Parameters.Add("@ReorderLevel"SqlDbType.SmallInt).Value = 
reorderLevel;
command.Parameters.Add("@Discontinued"SqlDbType.Bit).Value = discontinued;

            rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }
 

36) The code above is very simple.I have a static method that I pass values as input parameters. It also returns a integer value.Inside the method

  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object
  • I pass the input parameter values of the method as input parameters to the stored procedure
  • I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.
37) Save everything you have implemented so far.Configure the ObjectDataSource object (by following the steps of the wizard) and set the select,insert,update,delete tabs with the appropriate methods.Set the DataKeyNames property to ProductID.Configure the GridView control to enable editing,deleting,selection.
38) Run your application and try to update values from the page.Also try to delete rows from the page.Everything should work fine.
39) We can write some code to intercept exceptions coming out from our database.Add a Label to the page. We will use some of the events of the GridView control.We want to display appropriate messages ot the user when he cancels the "Edit", when the deletion cannot go on and whether the "Update" is successful or not. The actual event and the code inside them follows.
 
 protected void GridView1_RowCancelingEdit(object sender, 
GridViewCancelEditEventArgs e)
    {
        Label1.Text= "Edit cancelled.";
    }
    protected void GridView1_RowDeleted(object sender, 
GridViewDeletedEventArgs e)
    {
        if (e.Exception != null)
        {
            Label1.Text = "Cannot delete record";
            e.ExceptionHandled = true;
        }
        else
        {
            Label1.Text = "Record deleted.";
        }
    }
    protected void GridView1_RowUpdated(object sender, 
GridViewUpdatedEventArgs e)
    {
        if (e.Exception != null)
        {
            Label1.Text = "Cannot update record.";
            e.ExceptionHandled = true;
        }
        else
        {
            Label1.Text = "Record updated.";
        }
    }
 
40) Now let's try to insert a record in the database.Create a new page, name it InsertProduct.aspx.
Add a DetailsView control on the page. Leave the default name.Add an ObjectDataSource control on the page. Leave the default name.Set the DataSourceID property of the DetailsView control to ObjectDataSource1. When you try to configure your ObjectdDataSource object, select as the business object the DataAccess class, and select the methods in the appropriate tabs (SELECT,INSERT) for Select,Insert operations.Set the DataKeyNames property of the DetailsView control to ProductID.
41) Run your application and see the first record of the database. Click New and insert a record.If you want to have the DetailsView control to be in insert mode, in the Page_Load event handling routine of the page, type 
  DetailsView1.ChangeMode(DetailsViewMode.Insert); 

Run your application again and observe that you are in Insert mode when the page loads.
42) Now let's add some static methods that use Datasets in our DataAccess class.This is called the disconnected data access model, since datasets are in memory representation of the data stored in the database. We work with that in memory representation of the data. Let's say we want to retrieve the products per category.Inside the DataAccess.cs file add this method
 
public static DataSet GetProductsPerCategory(int categoryID)
    {
        DataSet dataSet = new DataSet();

        using (SqlConnection connection = Connection.GetDBConnection())
        {
   string sql = "SELECT * FROM Products WHERE CategoryID=@CategoryID";
   SqlCommand command = new SqlCommand(sql, connection);
   command.Parameters.Add("@CategoryID"SqlDbType.Int).Value = categoryID;
   command.CommandType = CommandType.Text;

   SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
   dataAdapter.Fill(dataSet, "Products");
        }
        return dataSet;
    }
 

43) The code above is very simple.I have a static method that I pass a value as input parameter(CategoryID). It also returns a dataset.Inside the method

  • I create a Dataset object
  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class.I define the SQL statement
  • I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object
  • I set the input parameter value of the method as the input parameter to the stored procedure
  • I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.
  • I create a DataAdapter object and fill the dataset.Finally I return the dataset.
 
44) Add another page in your site. Name it ProductsPerCategory.aspx.Add a Button,BulletedList,Label and Textbox control on the page. leave the default names.
45) In the Button1_Click event handling routine of the ProductsPerCategory.aspx page type
 try
  {
    int categoryID = int.Parse(TextBox1.Text);

           
   DataSet dataSet = DataAccess.GetProductsPerCategory(categoryID);
   foreach (DataRow row in dataSet.Tables["Products"].Rows)
   {
   string mystring = string.Format("{0}, {1}, {2}", row["ProductID"], 
row["ProductName"], row["UnitPrice"]);
   BulletedList1.Items.Add(mystring);
                
   }


 Label1.Text= string.Format("{0} rows in Products table in DataSet.",
 dataSet.Tables["Products"].Rows.Count);
        }
        catch (SqlException ex)
        {
            
            Label1.Text = "Cannot get product data." + ex.Message;
        }
 
46) Do not forget to add these lines in the top of the ProductsPerCategory.aspx.cs file.
using System.Data.SqlClient;
using System.Data;  
Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products(only the ProductID,ProductName,UnitPrice) printed out in the screen.
47) I am going to use datasets to insert and delete records. Add a new page to your site,name it DatasetInsertDelete.aspx. 
Add a Button,Gridview,Label,Textbox and an objectdatasource control on the page. Leave the default names.Set the DataSourceID property of the GridView control to ObjectDataSource1.
When you try to configure your ObjectdDataSource object, select as the business object the DataAccess class, and select the methods in the appropriate tabs for (SELECT,UPDATE,DELETE)
SELECT= GetProductsPerCategory(categoryID) 
 UPDATE = UpdateProduct()
DELETE = DeleteProduct()
In the Define Parameters step set Parameter Source to Control and set ControlID to TextBox1. Then finish the wizard.
Configure the GridView control to enable paging,sorting,editing,deleting. 
Set the DataKeyNames property of the GridView control to ProductID. 
48) Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products.Edit and Delete products and make sure they are deleted in actual database.Notice that with a dataset we can have paging and sorting but this cannot be achieved through the data reader object.
49) Now I am going to use DataView controls to bind some data to a GridView. We want to find all products that their untiprice is less than 20 and are not discontinued.
Add a new page to your form,name it MyDataView.aspx. Add a Button,Gridview,Label,Textbox control on the page.Leave the default names.
50) In the Button1_Click event handling routine of the MyDataView.aspx page type
 try
        {
            int categoryID = int.Parse(TextBox1.Text);

            DataSet dataSet = DataAccess.GetProductsPerCategory(categoryID);

            DataView myview = new DataView(dataSet.Tables["Products"]);
         
            myview.Sort = "UnitPrice";
            myview.RowFilter = "UnitPrice < 20 AND Discontinued = 0";
            GridView1.DataSource = myview;

            DataBind();

           Label1.Text = "All rows for the criteria specified are returned.";
        }
        catch (SqlException ex)
        {
          
            Label1.Text = "Some error occured....." + ex.Message;
        }
 

51) The code above is very simple.Inside the method

  • I store in a variable the CategoryID value the user enters in the textbox
  • I create the dataset by calling the GetProductsPerCategory method from the DataAccess class
  • I create a DataView object object by passing as parameter the datatable
  • I set values for filtering and sorting on the DataView object. I set the DataSource property of the GridView to the view object and I call the DataBind() method
Do not forget to add these lines in the top of the MyDataView.aspx.cs file.

using System.Data.SqlClient;
using System.Data; 
52) Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products.
53) Let's now see how we can bind data to the gridview from related tables.We want the use to enter a category ID in the textbox and get the products for that category in the GridView control.
Then as the user selects records in the first gridview (Product data) to have the related data( order details ) for that product appearing in the second gridview.
Add a new page to your form,name it RelatedTables.aspx. Add a Button,Label,Textbox control on the page.Leave the default names.Add 2 GridView controls on the page. Leave the default names. 
Inside the GridView1(the gridview that will get the products data) add this bit of code. We will use this Select button to select values in the Gridview.
  <Columns>
   <asp:CommandField ShowSelectButton="True" />
  </Columns>

54) Now let's add another static method that use Datasets in our DataAccess class that gets data from the Product and Order Details tables.Inside the DataAccess.cs file add this method
GetProductsAndOrderDetails(int categoryID)
public static DataSet GetProductsAndOrderDetails(int categoryID)
    {
        DataSet dataSet = new DataSet();

        using (SqlConnection connection = Connection.GetDBConnection())
        {
      string sql = "SELECT * FROM Products WHERE CategoryID=@CategoryID";
      SqlCommand command = new SqlCommand(sql, connection);
   command.Parameters.Add("@CategoryID"SqlDbType.Int).Value = categoryID;
      command.CommandType = CommandType.Text;

      SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
      dataAdapter.Fill(dataSet, "Products");

      command.CommandText = "SELECT * FROM [Order Details]";
      dataAdapter.Fill(dataSet, "OrderDetails");

      DataRelation relation = new DataRelation(
      "Products_OrderDetails",
    dataSet.Tables["Products"].Columns["ProductID"],
     dataSet.Tables["OrderDetails"].Columns["ProductID"]);

        }
        return dataSet;
    }

55) The code above is very simple.I have a static method that I pass a value as input parameter(CategoryID). It also returns a dataset.Inside the method

  • I create a new DataSet.I define the SQL statement
  • I get the connection object back from the GetDBConnection function that "lives" inside the Connection class
  • I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object
  • I set the input parameter value of the method as the input parameter to the stored procedure
  • I create a DataAdapter object and fill the dataset.
  • I specify another SQL statement (command.CommandText = "SELECT * FROM [Order Details]";)
  • I use the DataAdapter object and fill the dataset.
  • I create a DataRelation object.
  • Finally I return the dataset.
 
56) In the Button1_Click event handling routine of the RelatedTables.aspx page type
try
        {
       int categoryID = int.Parse(TextBox1.Text);

        DataSet dataSet = DataAccess.GetProductsAndOrderDetails(categoryID);
        GridView1.DataSource = dataSet.Tables["Products"];
        DataBind();

            Label1.Text = string.Format("Success.");
        }
        catch (SqlException ex)
        {
             Label1.Text = "Cannot get product data." + ex.Message;
        }
Set the DataKeyNames property of the GridView1 control to ProductID,CategoryID. 
57) Do not forget to add these lines in the top of the RelatedTables.aspx.cs file.

using System.Data.SqlClient;
using System.Data;  
58) In the GridView1_SelectedIndexChanged event handling routine of the RelatedTables.aspx page type
try
        {
        int productID = (int)GridView1.SelectedDataKey.Values[0];
        int categoryID = (int)GridView1.SelectedDataKey.Values[1];

        DataSet dataSet = DataAccess.GetProductsAndOrderDetails(categoryID);
        GridView1.DataSource = dataSet.Tables["Products"];

        DataView myview = new DataView(dataSet.Tables["OrderDetails"]);
        myview.RowFilter = "ProductID=" + productID;
        GridView2.DataSource = myview;

        DataBind();

        Label1.Text = string.Format("Success!!!");
        }
        catch (SqlException)
        {

            Label1.Text = "Cannot get the data data.";
        }

59) The code above is very simple.Inside the method

  • I store in two variables the Category ID and Product ID
  • I create the dataset by calling the GetProductsAndOrderDetails method from the DataAccess class
  • I set the DataSource property of the GridView1 to the data table
  • I create a DataView object object by passing as parameter the datatable (OrderDetails)
  • I set the value for filtering for the DataView object. I set the DataSource property of the GridView2 to the view object and I call the DataBind() method
60) Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products.Select a product from the first gridview and see the related information in the second gridview. 
I know that this is a rather huge post. You can break it down and try only the bits you are interested in.
If you follow the steps carefully you will learn how to implement a data access layer in ADO.Net 2.0
Email me if you need the source code.
Hope it helps!!!!
A simple example on achieving polymorphism in ASP.Net application part 3

In this post we will continue exploring the meaning of Polymorphism and the various ways we can implement Polymorphism in ASP.Net applications.

In this post I demonstrated how to use Polymorphism through Inheritance and base instance classes. In this post I demonstrated how to use Inheritance through abstract base classes.

In this post I will show you how to use Polymorphism through Interfaces.

I assume that the people who will read on this post understand basic OOP concepts, like Inheritance and Encapsulation and Interfaces.

The main point to consider is what happens when we want to share a common method among classes but those classes do not fall within the same inheritance tree. In my previous example we used the custom types Animal and Person. But what if we had Person and Auto custom types?We cannot have a base class that those two classes can inherit from.They do not fit in the same class hierarchy tree.

Nevertheless we want both objects to have a DisplayInfo() method.Both of these classes inherit from different base classes. I will use Interfaces in this example to demonstrate how two different types can "subscribe" to the same method through Interfaces. An Interface is a contract and by implementing an interface as class must implement the methods of the interface.

Let's start with our example.

1) Launch Visual Studio 2010/2008/2005. Express editions work fine.

2) Create a new empty website and give it an appropriate name.Choose C# as the development language.

3)  Add a new item in your site, a web form item. Leave the default name, so it is called Default.aspx.

4) Add a new item in your site, a class file.Name the class file IDisplayable.cs.This is the Interface file.We just have the method header of the method. We cannot add implementation in this file.

5) The whole code for the IDisplayable.cs class follows

 

public interface IDisplayable
{
    string DisplayInfo();
}

 

6) Add a new item in your site, a class file.Name the class file Person.cs. This class will implement the IDisplayable interface.

Remember that you can inherit from multiple interfaces but only from a single parent class.Obviously you have to implement the DisplayInfo() method.If you do not do that the compiler will complain.

The whole code for the Person.cs class follows

 public class Person:IDisplayable
{

    #region  define public properties of my person class

    public string FirstName { getset; }

    public string LastName { getset; }

    public string Email { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }


    #endregion




public string DisplayInfo()
{

string info;
info = "FullName is: " + FirstName + " " + LastName + " ,his email is "
 + Email + " ,his Height is: " + Height + " and Weight is " + Weight;
        return info;

}
}

 

7) Add another class file to your site and name it Auto.cs.This class will implement the IDisplayable interface.

Remember that you can inherit from multiple interfaces but only from a single parent class.Obviously you have to implement the DisplayInfo() method.If you do not do that the compiler will complain.

The code inside the .cs file should be something like this

 public class Auto:IDisplayable
{
    
    public string Make { getset; }
    public string Model { getset; }
    public int Year { getset; }
    public int Miles { getset; }
    public double Height { getset; }
    public double Width { getset; }
    public string Color { getset; }

public string DisplayInfo()
{

 string info;
 info = "Specs for my car: " + Make + " " + Model + " " + Year + " " 
 + Miles + " " + Height + " " + Width + " " + Color ;
 return info;

}
}

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

 

 List<IDisplayable> customTypes = new List<IDisplayable>();


        Person p1 = new Person();
        p1.FirstName = "Nikos";
        p1.LastName = "Kantzelis";
        p1.Email = "nikolaosk@hotmail.com";
        p1.Height = 1.78m;
        p1.Weight = 88.5m;


        Person p2 = new Person();
        p2.FirstName = "James";
        p2.LastName = "Rowling";
        p2.Email = "jamesr@hotmail.com";
        p2.Height = 1.98m;
        p2.Weight = 98.25m;

        Person p3 = new Person();
        p3.FirstName = "George";
        p3.LastName = "Graham";
        p3.Email = "graham@yahoo.co.uk";
        p3.Height = 1.88m;
        p3.Weight = 81.5m;


        Auto myAuto = new Auto();

        myAuto.Make = "Fiat";
        myAuto.Model = "Sporting";
        myAuto.Year = 1999;
        myAuto.Miles = 3232;
        myAuto.Height = 1.41;
        myAuto.Width = 2.35;
        myAuto.Color = "Silver";


        Auto myOtherAuto = new Auto();

        myOtherAuto.Make = "BMW";
        myOtherAuto.Model = "Cabrio SL12";
        myOtherAuto.Year = 2009;
        myOtherAuto.Miles = 6232;
        myOtherAuto.Height = 1.71;
        myOtherAuto.Width = 2.85;
        myOtherAuto.Color = "Black";



        customTypes.Add(p1);
        customTypes.Add(p2);
        customTypes.Add(p3);
        customTypes.Add(myAuto);
        customTypes.Add(myOtherAuto);
       

        foreach (IDisplayable item in customTypes)
        {

            Response.Write(item.DisplayInfo());
            Response.Write("<br/>");

        }

 

10) I create a generic collection of type IDisplayable and I call it customTypes.

List<IDisplayable> customTypes = new List<IDisplayable>();

Then I create 3 instances of the Person custom object and fill in the properties with values.

Then I create 2 instances of the Auto custom object and fill in the properties with values.

Then I use the Add method to add those objects in my customTypes collection.

Then I just loop through the items in the list and displaying information about them by calling the DisplayInfo() method.


 foreach (IDisplayable item in customTypes)
        {

            Response.Write(item.DisplayInfo());
            Response.Write("<br/>");

        }
11) I can rewrite the foreach loop above like this
 
foreach (var item in customTypes)
        {

            Response.Write(item.DisplayInfo());
            Response.Write("<br/>");

        }
Have a look for the var keyword here. This code works only for c#3. Basically with the var keyword we let the compiler decide what type it is.

12) Build and run your application.If you followed everything correctly until now your application will compile.I urge you to add many breakpoints in the application and see the flow of the execution. We were able to get two different types to be added to the same collection of objects and make them call the same method DisplayInfo(). 
Note that the classes do not inherit from the same base class but instead they implement the same Interface - IDisplayable.
To recap in this post we demonstratesd how to use Polymorphism with the use of Interfaces.
Email me if you need the source code.
Hope it helps.

A simple example on achieving polymorphism in ASP.Net application part 2

In this post we will continue exploring the meaning of Polymorphism and the various ways we can implement Polymorphism in ASP.Net applications.

In my previous post I demonstrated how to use Polymorphism through Inheritance and base instance classes. In this example I will use Inheritance through abstract base classes.

This example is going to be very similar like the previous one on Polymorphism. The difference is that we do not want to implement the DisplayInfo() method in the base class.

I assume that the people who will read on this post understand basic OOP concepts, like Inheritance and Encapsulation.

Well, the first thing one must do is to read this post of mine where I talk about array of objects, arraylists and generic collections. You must understand the concepts explained in that post very well.

Let's start with our example.

1) Launch Visual Studio 2010/2008/2005. Express editions work fine.

2) Create a new empty website and give it an appropriate name.Choose C# as the development language.

3)  Add a new item in your site, a web form item. Leave the default name, so it is called Default.aspx.

4) Add a new item in your site, a class file.Name the class file Person.cs.

5) The whole code for the Person.cs class follows

public class Person
{

    #region  define public properties of my person class

    public string FirstName { getset; }

    public string LastName { getset; }

    public string Email { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }


    #endregion

  

  
public string DisplayInfo()
    {

      
        string info;
        info = "FullName is: " + FirstName + " " + LastName;
        return info;

    }
}

6) Add another class file to your site and name is Animal.cs. The code inside the .cs file should be something like this

public class Animal
{

    public bool isMammal { getset; }

    public string Species { getset; }

    public double Speed { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }

}
    public string DisplayInfo()
    {
      
        string info;
        info = "Species is: " + Species + " and its speed is: " + Speed;
        return info;

    }
 
7)  Add another class file to your site and name is Being.cs. This will be the base class that the other two classes will inherit from.We will make this class as an abstract class.
 The code inside the .cs file should be something like this. We provide no implementation for the DisplayInfo() method.
   public abstract class Being
   
    {
    
        public int BeingID { getset; }

        public abstract string DisplayInfo();

    
    }
 
The Person class must inherit from the Being abstract class and must implement the DisplayInfo() method. This method of the Person class must be marked with the override keyword.
Please see below for complete implementation of the Person class
public class Person : Being
{

    #region  define public properties of my person class

    public string FirstName { getset; }

    public string LastName { getset; }

    public string Email { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }



    #endregion

  

    public override string DisplayInfo()
    {

      
 string info;
 info = base.BeingID +" and "+"FullName is: "+ FirstName +" " + LastName;
 return info;

       

    }

  

}

The Animal class must inherit from the Being abstract class and must implement the DisplayInfo() method. This method of the Animal class must be marked with the override keyword.
  public class Animal:Being
{

public bool isMammal { getset; }

public string Species { getset; }

public double Speed { getset; }

public decimal Height { getset; }

public decimal Weight { getset; }



public override string DisplayInfo()
{
        
  
string info;
info =base.BeingID + " and " + "Species is: " +Species+ " and its speed is: "
+ Speed;
return info;

    }
}

 
 9) In the Page_Load() event handling routine of the Default.aspx page type
  List<Being> Living = new List<Being>();


        Person p1 = new Person();
        p1.BeingID = 0;
        p1.FirstName = "Nikos";
        p1.LastName = "Kantzelis";
        p1.Email = "nikolaosk@hotmail.com";
        p1.Height = 1.78m;
        p1.Weight = 88.5m;


        Person p2 = new Person();
        p2.BeingID = 1;
        p2.FirstName = "James";
        p2.LastName = "Rowling";
        p2.Email = "jamesr@hotmail.com";
        p2.Height = 1.98m;
        p2.Weight = 98.25m;

        Person p3 = new Person();
        p3.BeingID = 3;
        p3.FirstName = "George";
        p3.LastName = "Graham";
        p3.Email = "graham@yahoo.co.uk";
        p3.Height = 1.88m;
        p3.Weight = 81.5m;



        Animal myanimal = new Animal();
        myanimal.BeingID = 4;
        myanimal.isMammal = true;
        myanimal.Species = "Panthera";
        myanimal.Speed = 56.8;
        myanimal.Height = 0.78m;
        myanimal.Weight = 163;

        
        Living.Add(p1);
        Living.Add(p2);
        Living.Add(p3);
        Living.Add(myanimal);

        foreach (Being item in Living)
        {

            Response.Write(item.DisplayInfo());
            Response.Write("<br/>");

        }

10) I create a generic collection of type Being and I call it Living.

List<Being> Living = new List<Being>();
 

Then I create 3 instances of the Person custom object and fill in the properties with values.

Then I create 1 instance of the Animal custom object and fill in the properties with values.

Then I use the Add method to add those objects in my Living collection.

Then I just loop through the items in the list and displaying information about them by calling the DisplayInfo() method.

11) Build and run your application.If you followed everything correctly until now your application will compile.I urge you to add many breakpoints in the application and see the flow of the execution. We were able to get two different types to be added to the same collection of objects and make them call the same method DisplayInfo().
 I know this is a very simple example (and very similar to my other post in Polymorphism) but nevertheless it demonstrates how to use Polymorphism with a base abstract class and Inheritance.
Email me if you need the source code.
Hope it helps.

A simple example on achieving polymorphism in ASP.Net application part 1

In one of my ASP.Net seminars I have been asked to give an example of the possible ways we can achieve Polymorphism in our ASP.Net applications.

So I have decided to write a simple ASP.Net application that demonstrates the use of Polymorphism. I assume that the people who will read on this post understand basic OOP concepts, like Inheritance and Encapsulation.

Well, the first thing one must do is to read this post of mine where I talk about array of objects, arraylists and generic collections. You must understand the concepts explained in this post very well.

I will use the same custom types/classes in this example as well. I will try to solve the problem we originally had with Generic collections.

In the last paragraph of this post ,I tried to add an object of type Animal to the generic list people. This failed of course at compile time as we cannot add an animal object in the people collection.

Let's imagine that we need to add both objects in a collection and then a call the method on each object in that collection even though each object is of different type. If that does not make sense, please read on.

In order to achieve that we need to implement some sort of polymorphism.We will see how to achieve polymorphism through Inheritance from a base class.

1) Launch Visual Studio 2010/2008/2005. Express editions work fine.

2) Create a new empty website and give it an appropriate name.Choose C# as the development language.

3)  Add a new item in your site, a web form item. Leave the default name, so it is called Default.aspx.

4) Add a new item in your site, a class file.Name the class file Person.cs.

5) The whole code for the Person.cs class follows

public class Person
{

    #region  define public properties of my person class

    public string FirstName { getset; }

    public string LastName { getset; }

    public string Email { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }


    #endregion

  

  
public string DisplayInfo()
    {

      
        string info;
        info = "FullName is: " + FirstName + " " + LastName;
        return info;

    }
}

6) Add another class file to your site and name is Animal.cs. The code inside the .cs file should be something like this

public class Animal
{

    public bool isMammal { getset; }

    public string Species { getset; }

    public double Speed { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }

}
    public string DisplayInfo()
    {
      
        string info;
        info = "Species is: " + Species + " and its speed is: " + Speed;
        return info;

    }
 
7)  Add another class file to your site and name is Being.cs. This will be the base class that the other two classes will inherit from.
 The code inside the .cs file should be something like this
public class Being
{
    
        public int BeingID { getset; }

        public string DisplayInfo()
        {

            string info;
            info = "Being is: " + BeingID;
            return info;

        }
    
}
 
8) Now we must make some changes to the base and child classes. We should mark the DisplayInfo() method as virtual in the Being class. The complete class is this
 
public class Being
{
    
        public int BeingID { getset; }

        public virtual string DisplayInfo()
        {

            string info;
            info = "Being is: " + BeingID;
            return info;

        }
    
}
The Person class must inherit from the Being class. The DisplayInfo() method of the Person class must access the functionality of the base class DisplayInfo() method and also has its own implementation.We mark the DisplayInfo() method of the Person class with the override keyword.To access the functionality of the DisplayInfo() method of the base class we use this bit of code
base.DisplayInfo();
Please see below for complete implementation of the Person class
public class Person : Being
{

    #region  define public properties of my person class

    public string FirstName { getset; }

    public string LastName { getset; }

    public string Email { getset; }

    public decimal Height { getset; }

    public decimal Weight { getset; }



    #endregion

   

    public override string DisplayInfo()
    {

    string myinfo= base.DisplayInfo();
    string info;
    info = myinfo + " and " + "FullName is: " + FirstName + " " + LastName;
    return info;

    }

  

}

The Animal class must inherit from the Being class. The DisplayInfo() method of the Animal class must access the functionality of the base class DisplayInfo() method and also has its own implementation.
We mark the DisplayInfo() method of the Animal class with the override keyword.To access the functionality of the DisplayInfo() method of the base class we use this bit of code
base.DisplayInfo();
Please see below for complete implementation of the Animal class.

public class Animal:Being
{

public bool isMammal { getset; }

public string Species { getset; }

public double Speed { getset; }

public decimal Height { getset; }

public decimal Weight { getset; }


public override string DisplayInfo()
{
        
string myinfo = base.DisplayInfo();
string info;
info =myinfo + " and " + "Species is: "+Species+" and its speed is: "+Speed;
return info;

}
}
 
 9) In the Page_Load() event handling routine of the Default.aspx page type
  List<Being> Living = new List<Being>();


        Person p1 = new Person();
        p1.BeingID = 0;
        p1.FirstName = "Nikos";
        p1.LastName = "Kantzelis";
        p1.Email = "nikolaosk@hotmail.com";
        p1.Height = 1.78m;
        p1.Weight = 88.5m;


        Person p2 = new Person();
        p2.BeingID = 1;
        p2.FirstName = "James";
        p2.LastName = "Rowling";
        p2.Email = "jamesr@hotmail.com";
        p2.Height = 1.98m;
        p2.Weight = 98.25m;

        Person p3 = new Person();
        p3.BeingID = 3;
        p3.FirstName = "George";
        p3.LastName = "Graham";
        p3.Email = "graham@yahoo.co.uk";
        p3.Height = 1.88m;
        p3.Weight = 81.5m;



        Animal myanimal = new Animal();
        myanimal.BeingID = 4;
        myanimal.isMammal = true;
        myanimal.Species = "Panthera";
        myanimal.Speed = 56.8;
        myanimal.Height = 0.78m;
        myanimal.Weight = 163;

        
        Living.Add(p1);
        Living.Add(p2);
        Living.Add(p3);
        Living.Add(myanimal);

        foreach (Being item in Living)
        {

            Response.Write(item.DisplayInfo());
            Response.Write("<br/>");

        }

10) I create a generic collection of type Being and I call it Living.

List<Being> Living = new List<Being>();
 

Then I create 3 instances of the Person custom object and fill in the properties with values.

Then I create 1 instance of the Animal custom object and fill in the properties with values.

Then I use the Add method to add those objects in my Living collection.

Then I just loop through the items in the list and displaying information about them by calling the DisplayInfo() method.

11) Build and run your application.If you followed everything correctly until now your application will compile.I urge you to add many breakpoints in the application and see the flow of the execution. We were able to get two different types to be added to the same collection of objects and make them call the same method DisplayInfo().
 I know this is a very simple example but nevertheless it demonstrates how to use Polymorphism with a base class and Inheritance.
Email me if you need the source code.
Hope it helps.

The QueryExtender web server control

In this post I am going to present a hands on example on how to use the QueryExtender web server control.

It is built into ASP.Net 4.0 and it is available from the Toolbox in VS 2010.Before we move on with our example let me explain what this control does and why we need it. 

Its goal is to extend the functionality of the LINQ to Entities and LINQ to SQL datasources.Most of the times when we have data coming out from a datasource we want some sort of filtering. We do achieve that by using a Where clause. Unfortunately the Where property of e.g the LinqDataSource web server control does not provide us with the full expressiveness that LINQ has.By using the QueryExtender web server control way we have richer filtering expressions.

Basically it extends the LinqDataSource and EntityDataSource web server controls with capabilities

1) Launch VS 2010. I am using the Ultimate edition but the express edition will work fine.

2) Create an empty web site from the available templates..

3) Add a new web form to your website.

4) We will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.You can download the Pubs database from this link. If you need some help on how to install the database have a look here . 

5) Add a GridView web server control on the form. Add an EntityDataSource control on the form. Set the DataSourceID property to the EntityDataSource1  ( DataSourceID="EntityDataSource1").

6) Add a new item to your site. Add an ADO.Net Entity Data Model. Name it Pubs.edmx. On the wizard choose "Generate from database". Then click Next and use an existing connection to the Pubs database or create a new one.Then choose all the Pubs tables to be included in the model and click Finish to exit from the wizard.

7) Then you need to configure the EntityDataSource and bind to the Authors table.You can do that from the smart tag of the EntityDataSource control but  I have chosen to do the same thing from the markup.

So it looks like this

<asp:EntityDataSource ID="EntityDataSource1" runat="server" 
ConnectionString="name=pubsEntities" DefaultContainerName="pubsEntities" 
EnableDelete="True" EnableFlattening="False" EnableInsert="True" 
EnableUpdate="True" EntitySetName="authors">
</asp:EntityDataSource>

As you see I have enabled Insert,Update,Delete and set the EntitySetName to authors entity class-object.

8) Run your application and make sure that everything works as expected.

9) Drag and drop a TextBox control on the form. Set the ID of the control to "txtSearch".Add a button to the form. Leave the default name.

10) Drag and drop a QueryExtender control on the form. We will configure some of its properties to create a search expression.The markup I have for my own example is this

<asp:QueryExtender ID="QueryExtender1" runat="server" 
TargetControlID="EntityDataSource1">
<asp:SearchExpression DataFields="city" SearchType="Contains">
<asp:ControlParameter ControlID="txtSearch" />
</asp:SearchExpression>
</asp:QueryExtender>

11) We set the TargetControlID property to the EntityDataSource control.We want to have a search expression that is why we have a SearchExpression control.

We want to filter the data in the gridview according to the City field-DataFields="city".

We will get the filtering data from the textbox,  <asp:ControlParameter ControlID="txtSearch" />.

The search type will be Contains,SearchType="Contains".

12) Run your application, type "v" in the textbox and hit the button. You will see all the records being filtered where the city column contains cities that have "v" in their names.

13) Add a new web form in to your site. Add two textbox controls on the form. Add a button control on the form as well.

14) Add a GridView web server control on the form. Add an EntityDataSource control on the form. Set the DataSourceID property to the EntityDataSource1  ( DataSourceID="EntityDataSource1").

15) Then you need to configure the EntityDataSource and bind to the Sales table.You can do that from the smart tag of the EntityDataSource control but  I have chosen to do the same thing from the markup.

So it looks like this

<asp:EntityDataSource ID="EntityDataSource1" runat="server" 
ConnectionString="name=pubsEntities" DefaultContainerName="pubsEntities" 
EnableDelete="True" EnableFlattening="False" EnableInsert="True" 
EnableUpdate="True" EntitySetName="sales">
</asp:EntityDataSource>

 As you see I have enabled Insert,Update,Delete and set the EntitySetName to sales entity class-object.

16) Drag and drop a QueryExtender control on the form. We will configure some of its properties to create a range expression. We will use the 2 textboxes to allow users to set the minimum and maximum values for the "quantity" column.

The markup I have for my own example is this

 <asp:QueryExtender ID="QueryExtender1" runat="server"
 TargetControlID="EntityDataSource1">
 <asp:RangeExpression DataField="qty" MaxType="Inclusive" MinType="Inclusive">
 <asp:ControlParameter ControlID="TextBox1" />
 <asp:ControlParameter ControlID="TextBox2" />
        
 </asp:RangeExpression>
 </asp:QueryExtender>

 

17) We set the TargetControlID property to the EntityDataSource control.We want to have a range expression that is why we have a RangeExpression control.

We want to filter the data in the gridview according to the qty field-DataFields="qty".

We will get the filtering data from the two textboxes, 

<asp:ControlParameter ControlID="TextBox1" />
<asp:ControlParameter ControlID="TextBox2" />

18) Run your application and type two values in the textboxes e.g 20 and 40 and hit the button. See the results that match the criteria being returned to the grid.

Hope it helps!!!


The chart web server control

In this post I am going to present a hands on example on how to use the Chart web server control.

It is built into ASP.Net 4.0 and it is available from the Toolbox in VS 2010.It is a very rich feature control that supports many chart types, had support for 3-D chart types,supports smart data labels and client side ajax support.

 Let's move on with our example.

1) Launch VS 2010. I am using the Ultimate edition but the express edition will work fine.

2) Create an empty web site from the available templates and choose c# as the development language.

3) Add a new web form to your website.

4) In the Default.aspx page drag and drop a Chart web server control onto the form.

5) I am going to manually add some data to our chart. Have a look inside the <Points></Points> tags. Inside there I am manually adding some data for our chart.

So the full markup by now should look like this.

<div>
<asp:Chart ID="Chart1" runat="server">
<Series>
<asp:Series Name="Series1">
<Points>
<asp:DataPoint AxisLabel="June Orders" YValues="1234" Color="Cyan" />
<asp:DataPoint AxisLabel="July Orders" YValues="2134" Color="Cyan" />
<asp:DataPoint AxisLabel="August Orders" YValues="3234" Color="Cyan" />
<asp:DataPoint AxisLabel="September Orders" YValues="3354" Color="Cyan" />
<asp:DataPoint AxisLabel="October Orders" YValues="3735" Color="Cyan" />
<asp:DataPoint AxisLabel="November Orders" YValues="4234" Color="Cyan" />
                
</Points>

</asp:Series>
</Series>
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1">
                </asp:ChartArea>
            </ChartAreas>
        </asp:Chart>
    </div>

 

6) Run your application and see the chart with all its data appearing on the screen.

7) In this step we will make the chart 3D and will add a legend .

The snippet for this markup will look like this

  </Series>
  <ChartAreas>
  <asp:ChartArea Name="ChartArea1" BackColor="Gray">
  <Area3DStyle Enable3D="true" Rotation="20"
  IsRightAngleAxes="true" IsClustered="true"
  />
  </asp:ChartArea>
  </ChartAreas>
<Legends>
   <asp:Legend Name="MyLegend" Title="OrdersCount" Alignment="Far" 
ForeColor="Black" BackColor="Azure" />
</Legends>
</asp:Chart>

Run your application again and see the legend appearing and the Column chart to be 3D.

If you want to change the chart type to Bar you must change this line of markup

  <Series>
     <asp:Series Name="Series1" ChartType="Bar">

Run your application again and see the results.

8) Let's use some datasource and bind its data to the Chart control. Add a new web form to your site. Drag and drop a Chart control onto the form.

Obviously for this step we will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.You can download the Pubs database from this link. If you need some help on how to install the database have a look here .

9) Drag and drop a SqlDataSource control onto the web form. Set the DataSourceID = SqlDataSource1

10) Now we must configure the SqlDataSource. In the smart tag of the SqlDataSource control create a new connection (if you do not have already a connection pointing to the Pubs database).

In the configuration of the Select statement choose a custom SQL statement. We will want to find how many books were sold by title_ID where the total quantity is more than 35.

So the SQL statement is this one:

SELECT     SUM(qty) AS quantity, title_id
FROM         sales
GROUP BY title_id
HAVING  SUM(qty) >35

Click Next and Finish the exit the wizard.

The markup for the SqlDataSource control is 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>" 
SelectCommand="SELECT     SUM(qty) AS quantity, title_id
FROM         sales
GROUP BY title_id
HAVING  SUM(qty) &gt;35"
></asp:SqlDataSource>

Change the type of the chart to Pie.

Set the X Value members to title_id and Y Value members  to quantity.The markup should look like this.

 <asp:Series Name="Series1" XValueMember="title_id" YValueMembers="quantity" 
ChartType="Pie">

11) Run your application and see the Pie chart.

There are so many features and options that you can explore yourself and I urge you to do. The Chart control was missing from the previous versions of the framework and people at Microsoft identified that and provide us with an excellent control with lots of features.

Hope it helps.

More Posts Next page »