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/

What is New in ASP.NET 4.0, Visual Studio 2010 IDE

Introduction

This article is an introduction to some of the new features in ASP.NET 4.0 and Visual Studio 2010 IDE.

Code Snippets

Code snippets are pre-developed code templates which can save time spent on thinking about the syntax. There are already a lot of built-in code snippets in VS2005 and VS2008. However, those are only available for the code-behind. Code snippets in VS2010 are introduced for JScript, HTML, and ASP.NET markup as well. In the screenshots below, we can see different snippet context menus for JScript and HTML.
Inside the script tag, it would be

And inside the HTML:

New Profiles

We have seen different development profiles in previous versions of .NET. Like VB, C#, Web Development, and General Development. We select a profile based on our priorities. These are selected after the first installation of Visual Studio or from the Import Export option.

In VS2010, two more profiles are introduced and both support HTML developers. Those are:

1. Web Development
2. Web Development (Code Optimized)

The Web Development profile hides the client objects and events bar on top in HTML mode.

In Code Optimized, you will find the HTML editor without code, and the designer tabs. So, it provides a more bigger area on the screen to play with HTML.

You can choose a profile after installation of Visual Studio on first use. You can also change the existing working profile from the Tools > Import & Export Settings > Reset all settings option. You will have to open and close Visual Studio if it does not show the changes in the IDE.

Generate From Usage

In previous versions of ASP.NET, Microsoft introduced code refactoring to generate methods and identifiers from existing code. In ASP.NET 4.0, there is a new concept of Generate From Usage - generates properties, methods, classes, and other types based on existing code.
Write some code, select it and right click on the left most character, and you will get options to change it to a property or method etc. This option is shown only if you do not define an identifier. For example, in the following example intellisense, it will not show the options to extract a property if you right click on the variable i.

Multi-Targeting

In VS2008, it is possible to create a project in version 2.0 or 3.0, rather than developing in default 3.5. Also, there is an option to change a developed project's target framework version.
The same option is available in VS2010, but with one improvement. In the previous versions, if you create a project in framework 2.0, intellisense will still show you the types and members of 3.5 version as well, so there are more chances of error if you cannot identify the member of the chosen framework. But in VS2010, the intellisense will show you options for the appropriate framework only.

Multi-Monitor

Visual Studio 2010 provides us facility to move windows of the IDE outside of the Visual Studio IDE and place them on the desktop area. It also supports having different IDE windows on multiple monitors. If we close Visual Studio and open it again, we will find all the windows on the same places where we finished them last time.

Code Navigation in Class Files

Code Navigation

A new Navigate To option gives us facility to search a term in a file more quickly. It searches the text as you type, but it works for class files only, i.e., it doesn't work for HTML or code-behind files. In the following example, it shows the function name as I type it in the Navigate To window; on selection of any, it will navigate to the function definition.



See full details: http://www.codeproject.com/KB/aspnet/Whatis_New_ASP_Net_4.aspx

Programming Speech in WPF - Speech Synthesis

New Microsoft Speech API (SAPI) version 5.3, which is an integral part of Windows Vista, is a managed API that allows developers to write speech-enable applications in .NET Framework 3.0.  This speech functionality is defined in the System.Speech and its five sub namespaces. Physically, the speech API resides in System.Speech.Dll assembly.

Here is a list of five namespaces that defines Speech related functionality.

• System.Speech.Audioformat
• System.Speech.Recognition
• System.Speech.Recognition.SrgsGrammar
• System.Speech.Synthesis
• System.Speech.Synthesis.TtsEngine

To access Speech API in WPF, you must add System.Speech.Dll assembly reference to a project. Right click on the project name in Solution Explorer, select Add Reference and select System.Speech on the .NET Tab and select OK button as shown in Figure 1.

Figure 1.

This action will add System.Speech assembly reference and copy System.Speech.dll to the bin folder of your project.  Now you can import System.Speech related namespaces in your application.

Speech Synthesis

Speech Synthesis, known as text-to-speech in previous versions of SAPI, is a process of converting text to speech.

Windows Vista comes with a default voice called Microsoft Anna. Let's take a look at it. Go to Control Panel and click on Text to Speech. You will see Speech Properties dialog with two tabs - Text to Speech and Speech Recognition as you can see in Figure 2 and Figure 3.

Figure 2.

On Text to Speech dialog box, you will see Voice Selection dropdown showing Microsoft Anna. On this dialog, you may also test the voice and audio output. If you have more voices installed, you will see them in the dropdown list as well. You can install more voices when you install Microsoft Speech SDK. 5.1.

Figure 3.

Table 1 describes the classes available in System.Speech.Synthesis namespace.

Class	Description
FilePrompt	Represents a prompt spoken from a file.
InstalledVoice	Represents an installed Voice object.
Prompt	Plays a prompt from text or from a PromptBuilder.
PromptBuilder	Creates an empty Prompt object and provides methods for adding content.
PromptStyle	Defines a style of prompting that consists of settings for emphasis, rate, and volume.
SpeechSynthesizer	Supports the production of speech and DTMF output.
VoiceInfo	Represents a text-to-speech (TTS) voice.

In this article, our focus is on SpeechSynthesizer class and its methods and properties.

SpeechSynthesizer     

The SpeechSynthesizer generates text to speech.

The Speak method speaks the text synchronously. The following code creates a SpeechSynthesizer object and calls Speak method that says "Hello WPF.". By default, the SpeechSynthesizer uses Microsoft Mary voice.

SpeechSynthesizer ss = new SpeechSynthesizer();

ss.Speak("Hello WPF.");

SpeechSynthesizer Properties

The SpeechSynthesizer has four properties - Rate, State, Voice, and Volume that are used to get and set rate, state, voice, and volume of the speech. The value of rate is between -10 to 10 and value of Volume is between 0 and 100. The Voice is the VoiceInfo object and State is SynthesizerState object. I will discuss these properties in more details in my forthcoming articles.

Asynchronous Speech

The SpeakAsync method speaks asynchronously and takes a Prompt, PromptBuilder or string as input text.

SpeechSynthesizer ss = new SpeechSynthesizer();

ss.SpeakAsync("Hello WPF");

The Application

Based on above class, properties, and methods, I build an application that allows you to browse a text file, opens it in a RichTextBox control, set the volume and rate of the speech and speaks it for you.

The application UI looks like Figure 4.

 

Figure 4.

The XAML code for controls looks like following:

See full details: http://www.c-sharpcorner.com/UploadFile/mahesh/SpeechWPF09302008223352PM/SpeechWPF.aspx

ASP.NET MVC Web Application using LINQ to SQL Classes

In this article we'll see how to use with LINQ to SQL Classes.

Get Started - After installing MVC framework SDK you will see a new template in installed visual studio templates 'ASP.NET MVC Web Application'.

Create a new project using ASP.NET MVC Web Application :

Figure1.

Next step if you want create unit test project or not? When you create a new ASP.NET MVC application, the Create Unit Test Project dialog appears (see Figure 2). This dialog enables you to create a separate project in your solution for testing your ASP.NET MVC application. Select the option No, do not create a unit test project and click the OK button.

Figure2.

After the new ASP.NET MVC application is created, you will see several folders and files in the solution explorer. There are five folders by default Content, Controllers, Models, Scripts, and Views.

Figure3.

After execution output will look like this.

Figure4.

Now time to add a new item 'LINQ to SQL Classes' template.

Figure5.

In this MVC application I am using NORTHWND database you can copy that from App_Data folder.

This data class will look like this.

Figure6.

Now drag and drop your database table from Server Explorer.

Figure7.

Now add a new Controller in Controllers folder and check that Add action for Create, Update and Details scenario and click Add.

Figure8.

HomeController.cs will show some methods like this.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Web.Mvc.Ajax;

 

namespace MVCUsingLINQToSQL.Controllers

{

    public class HomeController : Controller

    {

        //

        // GET: /Home/

 

        public ActionResult Index()

        {

            return View();

        }

 

        //

        // GET: /Home/Details/5

 

        public ActionResult Details(int id)

        {

            return View();

        }

 

        //

        // GET: /Home/Create

 

        public ActionResult Create()

        {

            return View();

        }

 

        //

        // POST: /Home/Create

 

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Create(FormCollection collection)

        {

            try

            {

                // TODO: Add insert logic here

 

                return RedirectToAction("Index");

            }

            catch

            {

                return View();

            }

        }

 

        //

        // GET: /Home/Edit/5

 

        public ActionResult Edit(int id)

        {

            return View();

        }

 

        //

        // POST: /Home/Edit/5

 

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Edit(int id, FormCollection collection)

        {

            try

            {

                // TODO: Add update logic here

 

                return RedirectToAction("Index");

            }

            catch

            {

                return View();

            }

        }

    }

}

 

First of all add a namespace on controller class.

using MVCUsingLINQToSQL.Models;

Now right click on Index method and click Add View and select View data class name view content.

Figure9.

Add some code to show data in list on Index method.

public ActionResult Index()

        {

            var dataContext = new ProductsDataContext();

            var products = (from m in dataContext.Products

                       select m).ToList();

            return View(products);

        }

And run your application you will see all records.

Now right click on Create method and click on Add View.

Figure10.

From Create.aspx you have to remove ProductID paragraph because this is auto generated column.

Now add this code in controller class for Create method.

//

        // GET: /Home/Create

 

        public ActionResult Create()

        {

            return View();

        }

 

        //

        // POST: /Home/Create

 

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Create(Product product)

        {          

            if (ModelState.IsValid)

            {

                try

                {

                    // TODO: Add insert logic here

                    var dataContext = new ProductsDataContext();

                    dataContext.Products.InsertOnSubmit(product);

                    dataContext.SubmitChanges();

                    return RedirectToAction("Index");

                }

                catch

                {

                    return View(product);

                }

            }

            return View(product);

          

        }

 

Here is your Edit method code:

//

        // GET: /Home/Edit/5

        public ActionResult Edit(int id)

        {

            var dataContext = new ProductsDataContext();

            var products = dataContext.Products.SingleOrDefault(x => x.ProductID == id);

            return View(products);           

        }

 

        //

        // POST: /Home/Edit/5

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Edit(int id, FormCollection collection)

        {

            var dataContext = new ProductsDataContext();

            var products = dataContext.Products.SingleOrDefault(x => x.ProductID == id);

            try

            {

                // TODO: Add update logic here

                UpdateModel(products);

                dataContext.SubmitChanges();

                return RedirectToAction("Index");

            }

            catch

            {

                return View(products);

            }

        }

Delete Method Code: 

See full details: http://www.c-sharpcorner.com/UploadFile/raj1979/MVCUsingLINQSQL10092009060837AM/MVCUsingLINQSQL.aspx