Best SQL Server Blog

SQL server Code

LINQ to SQL - Mapping Stored Procedure with Multiple Results

SQL Part 

1. Create tables for getting result set.

CREATE TABLE [dbo].[Color](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Color] [varchar](50) NULL

) ON [PRIMARY]



CREATE TABLE [dbo].[Car](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Make] [varchar](50) NULL,

    [Model] [varchar](50) NULL,

    [NumOfDoors] [tinyint] NULL,

    [ModelYear] [smallint] NULL,

    [ColorId] [int] NULL

) ON [PRIMARY]

2. Insert data into tables.

Color Table

ID    Color
1    Red
2    White
3    Blue
4    Green

Car Table

ID Make Model NumOfDoors ModelYear ColorId 1 Ford Mustang 2 1964 1 2 ford F150 2 2010 1 3 Toyota Camry 4 2011 1 4 Ford Taurus 5 1995 2 5 ford F250 2 2010 3 6 Chevrolet Volt 4 2010 1 7 Ford Focus 4 2012 4 8 Chevrolet Aveo 4 2011 2 9 Chevrolet Camaro 4 1978 4 10 Honda Civic 4 2012 1 11 Chevrolet Cruse 4 2012 1 12 Toyota Rav4 5 2000 1

3. Create Stored Procedure to return multiple result.

CREATE PROCEDURE [dbo].[GetBoth] 

    @colorId int = 0    

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from Car where ColorId=@colorId

    select c.Color,ca.* from Color c join Car ca on ca.ColorId=c.ID where c.ID=@colorId

END

above SP return two result set, one is car table and second join from car and color table.

.NET Part

1. Create DMBL file and drag car & color table into file, also drag SP into dbml file.

Suppose my DBML file name is Test.dbml

it will create class named TestDataContext including datacontract for car & color tables.

by default when we drag SP into DBML file it will create method with ISingleResult interface to get result from SP.

2. First create Data Contract for join tables result.

public class Mix

    {

        public string Color { get; set; }

        public int ID { get; set; }

        public string Make { get; set; }

        public string Model { get; set; }

        public Nullable<byte> NumOfDoors { get; set; }

        public Nullable<short> ModelYear { get; set; }

        public Nullable<int> ColorId { get; set; }    

    }

3. Now we need to create method that produce result of multiple query, DBML doesn't provide such type of method internally.

we have to create method to get multiple result set.

public partial class TestDataContext

    {

        [Function(Name = "dbo.GetBoth")]

        [ResultType(typeof(Car))]

        [ResultType(typeof(Mix))]

        public IMultipleResults GetBothMix([Parameter(Name = "colorId", DbType = "Int")] System.Nullable<int> colorId)

        {

            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), colorId);

            //decimal res = ((decimal)(result.GetParameterValue(1)));

            return (IMultipleResults)result.ReturnValue;

        }

    }

In above snippet, i have create method that return multiple result set using IMultipleResults Interface.

I have used System.Data.Linq.Mapping namespace to map stored procedure,

like [Function(Name = "dbo.GetBoth")] - maps Stored Procedure named GetBoth.

[ResultType(typeof(Car))] - maps car table to first result set in stored procedure.

[ResultType(typeof(Mix))] - maps user defined generic type to second result set in stored procedure.
       


4. Now in CS file get result using DataContext object.

protected void Page_Load(object sender, EventArgs e)

        {

            TestDataContext context = new TestDataContext();

            context.ObjectTrackingEnabled = false;           

            IMultipleResults d = context.GetBothMix(1);

            IList<Car> cars = d.GetResult<Car>().ToList();

            IList<Mix> colors = d.GetResult<Mix>().ToList();

         }

Loading Image from External URL (http://...)

void LoadImage()
{

 string img = "http://localhost/Mycode/Images/HirenLogo.png";

 WebClient webClientImgDownloader = new WebClient();
            webClientImgDownloader.OpenReadCompleted            
            += new OpenReadCompletedEventHandler(webClientImgDownloader_OpenReadCompleted);
            webClientImgDownloader.OpenReadAsync(new Uri(img, UriKind.Absolute));

 

}

void webClientImgDownloader_OpenReadCompleted(object sender,
            OpenReadCompletedEventArgs e)  

{
            BitmapImage bitmap = new BitmapImage();
            bitmap.SetSource(e.Result);
            ImgHeaderLogo.Source = bitmap;

} 

ASP.Net MVC 3 Tutorial

Below are the links

ASp.Net MVC 3

Getting Started MVC 3

Silverlight & WCF RIA Service

Please go through following MSDN site.

WCFRIA Service

Using LINQ To SQL CompiledQuery

CompliedQuery increase performance by compiling the query one time and executing it several times with different parameters.

With CompliedQuery , you compile your query once and store it somewhere for later usage. This is achieved by static CompliedQuery.Compile method.

It provides for compilation and caching of queries for reuse.

To compile the query, we have to use the CompiledQuery.Compile method. This method takes an argument list as input and a result type.

Below is simple Example.

The following example compiles and then invokes a query that  returns a List of employees where employeeid=1.

public static Func<TestDataContext, IEnumerable<Employee>> GetEmployeeList
        {
            get
            {
                return CompiledQuery.Compile<TestDataContext, IEnumerable<Employee>>
                    ((TestDataContext context) => context.Employees.Where<Employee>(x => x.EmployeeID== 1));
            }
        }

In above code, i created DBML DataContext named TestDataContext that contains all tables.

I have created class named Employee to use in result set.

Tips to Improve LINQ to SQL Performance

Please follow below link.

LINQToSQLPerformance