Applications using Linq to SQL

Introduction

In this article, I want to illustrate the principles, techniques and tools to develop applications using Linq to SQL after a deep research on this topic.

If you look for this topic on Internet, we can see that a lot of .NET developers are blogging and discussing about this new feature of the languages in Microsoft.NET platform and its support by Visual Studio.NET 2008.

Getting to know Linq

Linq stands for Language Integrated Query and it's a new approach to access efficiently any type of data, making querying data a first class concept on .NET languages. It enables creating queries using less code, so the resulting queries are easier to understand.

It's remarkable to say that Linq is not specific to data sources, so it does not matter where the data resides. In the actual implementation, Linq is divided into four common types: Linq to Objects, Linq to DataSet, Linq to SQL, and Linq to XML. You can also extend the Linq framework to support other data source such as LDAP, SAP, etc.

Linq is part of .NET Framework 3.5; consequently we need to use Visual Studio.NET 2008 to work effectively with Linq. Microsoft has also decided to focus attention on C# when using Linq, because this language provides extensions to make working with Linq easier, although most of the techniques in Linq can be applied in Visual Basic.NET as well.

In this article, we're going to focus on Linq to SQL. Linq to SQL seamlessly maps the object model in the application to the data model in the relational data source trying to reduce the object-relational mapping mismatch. There are two main methods for the creation of required business entities and relationships associated to the data model:

• SQLMetal, a command line tool.
• Object relational designer, an easy to use graphical user interface.

The mechanism to access to the relational database is through the DataContext class. So, we need to create a new class derived from DataContext class, and usually this derived class has the same name than the database which contains the data model. Linq to SQL also relies on classes for specific entities such as tables. In most cases, an entity class is associated to a particular table on the database. This entity class has attributes representing the fields in the underlying table as well as for the primary and foreign keys. You can also find properties that point to a collection of child elements, so representing parent-child relationship in the database.

Getting started with Linq to SQL examples

In order to illustrate the concepts, we're going to create a Windows Forms Application, an ASP.NET Web Applications and a Class Library projects in Microsoft Visual Studio.NET 2008 to test the features of Linq to SQL technology. As the back-end, we're going to use the AdventureWorks database shipped with Microsoft SQL Server 2005/2008 as well as the Production.Product and Production.ProductSubcategory tables.

Next step is to add a Linq to SQL artifact to the Class Library by selecting the Project | Add New Item option (see Figure 1).



Figure 1

Modeling the business entities

After that the Object Relational (O/R) design is launched which enables to model the classes that represent the underlying data model and the methods for each stored procedure. It will also create a strongly typed DataContext class. Unlike the DataSet/TableAdapter approach, when we're defining the entity classes in the designer, we don't have to specify the SQL queries; instead we have to focus on defining the entity classes and the mapping to the underlying data model. SQL to Linq framework will generate the appropriate SQL statements at runtime when you interact and use the business entities.

In order to create the business entities, the easy way is to open a database connection in the Server Explorer windows, select the tables and views you want to model and map from it, and drag and drop them onto the designer surface.

In this case, drag and drop the Production.Product and Production.ProductSubcategory tables on the designer surface (see Figure 2). After that you can see the business entities and their relationship.



Figure 2

Linq to SQL enables modeling stored procedures as methods of the DataContext class. Let's suppose we need to return a list of a product based on a given product subcategory id (see Listing 1).

create procedure spSelectProductBySubcategory
      @pSubcategoryId intas
begin      select *      from Production.Product
      where ProductSubcategoryID=@pSubcategoryId;end;

go

Listing 1

Now we can use the Server Explorer windows to drag and drop this stored procedure on the in the right pane of the designer surface (see Figure 3).



Figure 3

Executing CRUD operations

Now that we have defined our object model representing the data model, we are ready to execute CRUD (create, read, update, delete) operations over the relational data schema through the object model in C#. For example, to get the products whose makeflag is true (see Listing 2).

ProductionDataContext objDataContext = new ProductionDataContext();var objResultset = from objProduct in objDataContext.Products
                   where objProduct.MakeFlag == true

                   select objProduct;

Listing 2

Now let's create a new product subcategory item (see Listing 3).

ProductionDataContext objDataContext = new ProductionDataContext();

ProductSubcategory objProductSubcategory = new ProductSubcategory();
objProductSubcategory.ProductSubcategoryID = 38;
objProductSubcategory.ProductCategoryID = 4;
objProductSubcategory.Name = "My new category";
objProductSubcategory.ModifiedDate = DateTime.Today;

objDataContext.ProductSubcategories.InsertOnSubmit(objProductSubcategory);

objDataContext.SubmitChanges();

Listing 3

In the following step, we're going to retrieve the newly created product subcategory (its product subcategory id is 42) and update its name field (see Listing 4).

ProductionDataContext objDataContext = new ProductionDataContext();
var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories
                            where objProdSubc.ProductSubcategoryID == 42
                            select objProdSubc).First();

objProductSubcategory.Name = "Good product subcategory";
 objDataContext.SubmitChanges();

Listing 4

The last CRUD operation to test is the delete operation. Now we're going to delete the created product subcategory (its product subcategory id is 42) (see Listing 5).

ProductionDataContext objDataContext = new ProductionDataContext();

var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories
                            where objProdSubc.ProductSubcategoryID == 42
                            select objProdSubc).First();

objDataContext.ProductSubcategories.DeleteOnSubmit(objProductSubcategory);

objDataContext.SubmitChanges();

Listing 5

Now let's call for the stored procedure defined in the Listing 1 (see Listing 6).

ProductionDataContext objDataContext = new ProductionDataContext();var objProductSubcategory = objDataContext.spSelectProductBySubcategory(1);

Listing 6

In order to implement database paging, we need to use the Skip and Take methods (see Listing 7).
ProductionDataContext objDataContext = new ProductionDataContext();

ProductionDataContext objDataContext = new ProductionDataContext();

var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories
                            where objProdSubc.ProductSubcategoryID == 2

                            select objProdSubc).Skip(200).Take(10);

Listing 7

Data binding in Linq

Now let's add a Windows Form artifact to the Windows application and a DataGridView control (named m_dgvGridView) onto the form for binding purposes. Then add the following code as shown in Listing 8.

ProductionDataContext objDataContext = new ProductionDataContext();

var objResultset = from objProdSubc in objDataContext.ProductSubcategories
                   where objProdSubc.ProductCategoryID == 2
                   select objProdSubc;

this.m_dgvGridView.DataSource = objResultset;

Listing 8

When you run the application, we'll get the result as shown in Figure 4.



Figure 4
Let's suppose that we want to remove the productcategoryid and rowguid columns from the result in Figure 4, so we need to re-write the query as in the Listing 9.

ProductionDataContext objDataContext = new ProductionDataContext();

var objResultset = from objProdSubc in objDataContext.ProductSubcategories
                   where objProdSubc.ProductCategoryID == 2
                   select new { objProdSubc.ProductSubcategoryID, objProdSubc.Name, objProdSubc.ModifiedDate };
 this.m_dgvGridView.DataSource = objResultset;

Listing 9

The result is shown in Figure 5.



Figure 5

Now let's work on the Web counterpart of the solution. Let's open the Default.aspx page on the Web project and add a GridView control onto the Web page (see Listing 10).

See full details: http://www.c-sharpcorner.com/UploadFile/john_charles/148/

ResourceDictionary in WPF

The items in a ResourceDictionary are not immediately processed when application code is loaded by a XAML loader. Instead, the ResourceDictionary persists as an object, and the individual values are processed only when they are specifically requested.

The ResourceDictionary class is not derived from DictionaryBase. Instead, the ResourceDictionary class implements IDictionary but relies on a Hashtable internally.

In Extensible Application Markup Language (XAML), the ResourceDictionary class is typically an implicit collection element that is the object element value of several Resources properties, when given in property element syntax. For details on implicit collections in XAML, see XAML Syntax Terminology. An exception is when you want to specify a merged dictionary; for details, see Merged Resource Dictionaries.

Another possible XAML usage is to declare a resource dictionary as a discrete XAML file, and either load it at run time with Load or include it in a (full-trust) project as a resource or loose file. In this case, ResourceDictionary can be declared as an object element, serving as the root element of the XAML. You must map the appropriate XML namespace values (default for the WPF namespace and typically x: for the XAML namespace) onto the Resource Dictionary element if you plan to use it as the root element. Then you can add child elements that define the resources, each with an x:Key value.
Getting Started:

First of all make a new WPF Application and add a new ResourceDictionary file. I am putting my ResourceDictionary File in Themes directory. Like figure1



Figure1.

Here is my ResourceDictionary file xml code:


See full details: http://www.c-sharpcorner.com/UploadFile/raj1979/176/Default.aspx

Drag And Drop Item in ListBox in WPF

Introduction

In this article we will see how we achieve Drag and Drop behaviour for ListBox Item.

Creating WPF Project

Fire up Visual Studio 2008 and create a new WPF Project. Name it as DragDropListBoxSample.



Basic idea of our sample application is to have two ListBox and we would provide Drag item from First ListBox and Drop into the Second ListBox.

So let's have two ListBox and name the ListBoxes as lbOne, lbTwo.

The following is the basic design how our application would look like.



Here what we would list in the First ListBox. A list of TimeZones from the class TimeZoneInfo.

See full details: http://www.c-sharpcorner.com/UploadFile/dpatra/181/Default.aspx

Returning Large Volume of Records from SOAP based WCF Service

Objective

In this article I will explain; How to return large volume of data (around 50000 records) from SOAP based WCF service to any client. I will show; what service configuration setting and client configuration setting is needed to handle large volume of data returning for WCF service.
Follow the below steps,
Step 1: Create a WCF service

.To creates WCF service; select File -> New -> Project-> Web -> WCF Application.
Service will contain

1. One Operation contract. This function will pull large data from database using stored procedure.
2. One Data Contract. This class will act as Data Transfer object (DTO) between client and service.
3. basicHttpBinding is being used in the service. You are free to use any binding as of your requirement.

Data Transfer Class
[DataContract]
    public class DTOClass    {
        [DataMember]
        public string SystemResourceId
        {
            get;
            set;
        }
        [DataMember]
        public string SystemResourceName
        {
            get;
            set;
        }
        [DataMember]
        public string Created
        {
            get;
            set;
        }
        [DataMember]
        public string Creater
        {
            get;
            set;
        }
        [DataMember]
        public string Updated
        {
            get;
            set;
        }
        [DataMember]
        public string Updater
        {
            get;
            set;
        }


1. Name of DTO class is DTOClass. You can give any name of your choice.
2. There are 6 string properties.
3. All properties are attributed with DataMember.
Contract
   [ServiceContract]
    [ServiceKnownType(typeof(DTOClass))]
    public interface IService1    {

        [OperationContract]
         List<DTOClass> GetData();
      
    }
 

1. Service is returning List of DTOClass.
2. To get serialized at run time, making sure Data Contract is known to contract by using known type.

Service Implementation
public class Service1 : IService1    {

        string cs = @"Data Source=xxxserver;Initial Catalog=Sampledatabase;User=dhananjay;Password=dhananjay";

        List<DTOClass> restDto = new List<DTOClass>();
        DTOClass dto;

        public List<DTOClass> GetData()
        {
            SqlConnection con = new SqlConnection(cs);
            SqlCommand cmd = new SqlCommand("GetAllSystemResourceDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dto = new DTOClass();
                dto.SystemResourceId = dt.Rows[i][0].ToString();
                dto.SystemResourceName = dt.Rows[i][1].ToString();
                dto.Created = dt.Rows[i][8].ToString();
                dto.Updated = dt.Rows[i][10].ToString();
                dto.Creater = dt.Rows[i][9].ToString();
                dto.Updater = dt.Rows[i][11].ToString();               
                restDto.Add(dto);           
            }

            return restDto; ;
        }
} }

1. This is simple implementation. Where ADO.Net is being used to fetch data from Database.
2. GetAllSystemResourceDetails is name of the stored procedure.

Note: Purpose of this article is to show how to push large volume of data from WCF service. So, I am not emphasizing ADO.Net part here. See the other articles for detail explanation on ADO.Net
Configuration setting at service side

See full details: http://www.c-sharpcorner.com/UploadFile/dhananjaycoder/179/