Monday, December 12, 2011

Linq - Multiple Result set of Procedure using LINQ to SQL

In this article i will show you how to return multiple result set of stored procedure using Linq to SQL.


As you probably know LINQ to SQL supports stored procedure (SP). You can drag & drop a SP in the LINQ to SQL Designer and the magic happens: a new method inside the DataContext class is generated and a new return type is defined by the designer to represent this result (the name of the type is composed by the SP name followed by Result).


It is possible to read multiple result set of procedure using Linq.In general scenario when you drag and drop procedure in .dbml its always returns ISingleResult and only consider first result, based of first result it will create data contracts.


Now let see how we can read multiple result set using LINQ


Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server.


Step 3
Lets Create a  stored procedure of  multiple results.Execute below stored procedure in Northwind Database 
CREATE PROC [dbo].[SPViewData]
 @ContactName Nvarchar(30)
AS
 /*Get Employee Data*/ /* No Parameter */
 SELECT TOP 5 Employees.FirstName,Employees.LastName,Employees.City FROM Employees
 
 /*  Get Customers Data */ /* With Parameter */
 SELECT Customers.ContactName,Customers.City FROM Customers
  WHERE Customers.ContactName=@ContactName
GO

Step 4
Create a Web Application and give the solution name as SolMultipleResultSetLinqToSQL.


Step 5
Add a App_Code Folder,Add Linq to SQL class,Right click on App_Code Folder and select Add New Item, select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this




Click on image for better view


Step 6
Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this




Click on image for better view




Click on image for better view



Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 7
To map Tables and stored procedure in DataContext class.go to the server explorer,select northwind database,go to the Stored Procedure and select SPViewData stored procedure and select Employees table and Customers table from Table ,it is look like this


Click on image for better view

Drag and drop Employees and Customers Table and SpViewData stored procedure from Server explorer onto the design surface of the O/R Designer,it is look like this


Click on image for better view

When you drag and drop this procedure you can see function in NorthwindDC.designer.cs file which look like following
[global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.SPViewData")]
    public ISingleResult<SPViewDataResult> SPViewData([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "ContactName", DbType = "NVarChar(30)")] string contactName)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), contactName);
        return ((ISingleResult<SPViewDataResult>)(result.ReturnValue));
    }


You can see it generates ISignleResult result and having only single entity which is SpViewDataResult and when we see the definition of SpViewDataResult class we can see that it only consider Employees results and exclude Customers result.


Click on image for better view

Step 8
To get  multiple results we have to create new file having same class name and added with partial keyword.The method should be in same namespace and with same class,it is look like this
using System;
using System.Collections.Generic;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Data.Linq;

/// 
/// Summary description for NorthwindDCDataContext
/// 
public partial class NorthwindDCDataContext
{
    [FunctionAttribute(Name = "dbo.SpViewData")]
    [ResultType(typeof(Employee))]
    [ResultType(typeof(Customer))]
    public IMultipleResults GetMultipleResultSet([System.Data.Linq.Mapping.ParameterAttribute(Name="ContactName", DbType="NVarChar(30)")] string ContactName)
    {
        try
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),ContactName);
            return (IMultipleResults)(result.ReturnValue);

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
    }
}

We have added Employee and Customer as ResultType as our procedure is returning these two result sets, this will going to use by MethodInfo in order to return IMultipleResults. In addition to this we have to change return type, it was before ISingleResult now we have to make it IMultipleResults and finally we add ParameterAttribute in method which user set a parameter value for stored procedure.Now our method is ready to return multiple results.


Step 9
Add a two GridView on page it's look like this
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>Employee Data</h2>
        <asp:GridView ID="GvEmployee" runat="server" AutoGenerateColumns="False" 
            EnableModelValidation="True">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
        </asp:GridView>

        <h2>Customer Data</h2>
        <asp:GridView ID="GvCustomer" runat="server" AutoGenerateColumns="False" 
            EnableModelValidation="True">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="ContactName" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
        </asp:GridView>

    </div>
    </form>
</body>
</html>

Step 10
Get the results form IMultipleResults and bind data to the GridViews,it is look like this
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                ViewData();
            }
        }
        catch (Exception)
        { 
        }
    }

    #region Methods

    private void ViewData()
    {
        try
        {
            //Create a Object of Northwind DataContext Class
            NorthwindDCDataContext DC = new NorthwindDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString.ToString());

            // Get Multiple Result Set and Store in IMultipleResults Interface
            IMultipleResults Results = DC.GetMultipleResultSet("Thomas Hardy");

            // Get Employee Data from IMultipleResults Interface Object
            var Employees = Results.GetResult<Employee>().ToList<Employee>();

            // Get Customer Data from IMultipleResults Interface Object
            var Customers = Results.GetResult<Customer>().ToList<Customer>();

            //Bind Employee Data to gridview
            if (Employees != null)
            {
                if (Employees.Count >= 0)
                {
                    GvEmployee.DataSource = Employees;
                    GvEmployee.DataBind();
                }
            }

            // Bind Customer Data to gridview
            if (Customers != null)
            {
                if (Customers.Count >= 0)
                {
                    GvCustomer.DataSource = Customers;
                    GvCustomer.DataBind();
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
    }

    

    #endregion
}

Run the Project.


Output
Click on image for better view


Download
Download Source Code

Tuesday, December 6, 2011

MS-SQL - Full Text Index in MS-SQL Server

Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns.


Let see how to create full text search index in MS-SQL Server.For this Example i will take Northwind Database.


Step 1
Download Northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a Northwind database into MS-SQL server.


Step 3
Create a Full Text Catalog,it is look like this




Click on Image for better view


Specify Full Text Catalog Name,it is look like this






Click on Image for better view


Now Full Text Catalog is Ready,it is look like this




Click on Image for better view


Step 4
Create a Full Text Index,it is look like this



Click on Image for better view

Full Text Index wizard will open, it is look like this




Click on Image for better view


Select Unique Index for the table,it is look like this.
Note. - In Northwind database there is no primary key on Employees table so create primary key on Empoyees table before selecting Unique Index for Full Text Index.




Click on Image for better view


Select a Table Columns for Full Text Queries,it is look like this




Click on Image for better view


Select Change Tracking,it is  look like this




Click on Image for better view


Select Catalog Name,it is look like this




Click on Image for better view


Define Population Schedules(Optional),it is look like this



Click on Image for better view


Full Text Index Description,it is look like this




Click on Image for better view




Click on Image for better view


Step 5
Populate the Index,it is look like this




Click on Image for better view




Click on Image for better view


As the full text index is created and populated,now we can write a query for searching records on desired table which provide better performance.


Step 6
For Example,we will find Employee Records who has 'Manager' in their Title.


FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.
  • Separates the string into individual words based on word boundaries (word-breaking).
  • Generates inflectional forms of the words (stemming).
  • Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.
USE Northwind

SELECT * FROM Employees
 WHERE FREETEXT(Employees.Title,'Manager')


Click on Image for better view


CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

USE Northwind

SELECT * FROM Employees
 WHERE CONTAINS(Employees.Title,'President OR Manager')
 
SELECT * FROM Employees
 WHERE CONTAINS(Employees.Title,'Sales AND Representative')


Click on Image for better view


Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators while LIKE Operator will search for the words that contain part of the search string.

Saturday, December 3, 2011

ASP.net - Google Map Version 3 in ASP.net

Google Map Version 3 API is especially designed to be faster and more applicable to mobile devices, as well as traditional desktop browser applications.


In this article i will show how to bind google map in asp.net project and some basic feature of google map.


Good News for web developer there is no more API registration key needed for Version 3.0.
Thanks to Google Corporation.


Step 1
Download JQuery Script from the following Link
JQuery 1.7.1


Step 2
Create a Web application and give the solution name as SolGoogleMap


Step 3
First we need to create a page,it is look like this
<body>
    <form id="form1" runat="server">
    <div>
        
       <table border="0" cellpadding="5"  cellspacing="5" width="100%" align="center">
            <tr>
                <td style="width:60%">
                    <asp:TextBox ID="txtAddress" runat="server" Height="32px" Width="530px"></asp:TextBox>
                </td>
                <td style="width:40%">
                    <asp:Button ID="btnSearch" runat="server" Text="Search Map" Height="32px" Width="200px"/>
                </td>
            </tr>
            <tr>
                <td colspan="2" style="height:100%">
                   <div id="GoogleMapCanvas" style="height:510px;"></div>
                </td>
            </tr>
       </table>

    </div>
    </form>
</body>


Click on Image for better view


Step 4
Add jQuery file Reference inside the head tag of the page,it is look like this
<script language="javascript" type="text/javascript" src="Scripts/jquery-1.7.1.min.js"></script>

Step 5
We need to add Google Map V3 API reference in head tag,it is look like this
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false&libraries=places&language=en-AU"></script>

Step 6
Bind Address to the textbox control,we can use Google API to have an autocomplete for address,it is look like this
<%--The Following Script to bind google places(address) to the textbox (AutoCompleteTextbox) --%>
    <script type="text/javascript">

        $(document).ready(function () {

            var autocomplete = new google.maps.places.Autocomplete($("#txtAddress")[0], {});

            google.maps.event.addListener(autocomplete, 'place_changed', function () {
                var place = autocomplete.getPlace();
            });


        }
        );
        
    </script>

My Home Town Address

Click on Image for better view


Step 7
Now Initialize Google Map on body onload event,it is look like this.
First write script to intialize google map.
<script type="text/javascript">

        var geocoder;
        var map;

        function InitializeGoogleMap() {

            try {

                geocoder = new google.maps.Geocoder();
                // Location of the Mumbai,Thane
                var LatitudeLongitude = new google.maps.LatLng(19.1969813, 72.9962491);
                var GoogleMapOptions = {
                    zoom: 10,
                    center: LatitudeLongitude,
                    mapTypeId: google.maps.MapTypeId.ROADMAP
                }
                map = new google.maps.Map($("#GoogleMapCanvas")[0], GoogleMapOptions);


            }
            catch (E) {
                alert(E.Message);
            }

        }
    
    </script>


Then call InitializeGoogleMap function on body onload event,it is look like this
<body onload="InitializeGoogleMap(); return false">


Location of Mumbai-Thane

Click on Image for better view


Step 8
Add a Marker and Info Window on Google Map,it is look like this
function AddMarkerWithInfoWindow() {
            try {

                var Address = $("#txtAddress").val();

                // Add Marker
                geocoder.geocode({ 'address': Address }, function (results, status) {
                    if (status == google.maps.GeocoderStatus.OK) {
                        map.setCenter(results[0].geometry.location);
                        var Marker = new google.maps.Marker({
                            map: map,
                            position: results[0].geometry.location
                        });

                        // Open Info Window
                        var infowindow = new google.maps.InfoWindow(
                        {
                            content: $("#txtAddress").val()
                        }
                        );
                        google.maps.event.addListener(Marker, 'click', function () {
                            infowindow.open(map, Marker);
                        });

                    } else {
                        alert("Geocode was not successful for the following reason: " + status);
                    }
                });

            }
            catch (E) {
                alert(E.Message);
            }
        }

Call AddMarkerWithInfoWindow function on btnsearch onclientclick event,it is look like this
<asp:Button ID="btnSearch" runat="server" Text="Search Map" Height="32px" Width="200px" OnClientClick="AddMarkerWithInfoWindow(); return false" />

Full Code
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Google Map Version 3 in ASP.net</title>

    <script language="javascript" type="text/javascript" src="Scripts/jquery-1.7.1.min.js"></script>

    <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false&libraries=places&language=en-AU"></script>

     <%--The Following Script to bind google places(address) to the textbox (AutoCompleteTextbox) --%>
    <script type="text/javascript">

        $(document).ready(function () {

            var autocomplete = new google.maps.places.Autocomplete($("#txtAddress")[0], {});

            google.maps.event.addListener(autocomplete, 'place_changed', function () {
                var place = autocomplete.getPlace();
            });


        }
        );
        
    </script>

    <script type="text/javascript">

        var geocoder;
        var map;

        function InitializeGoogleMap() {

            try {

                geocoder = new google.maps.Geocoder();
                // Location of the Mumbai,Thane
                var LatitudeLongitude = new google.maps.LatLng(19.1969813, 72.9962491);
                var GoogleMapOptions = {
                    zoom: 10,
                    center: LatitudeLongitude,
                    mapTypeId: google.maps.MapTypeId.ROADMAP
                }
                map = new google.maps.Map($("#GoogleMapCanvas")[0], GoogleMapOptions);


            }
            catch (E) {
                alert(E.Message);
            }

        }

        function AddMarkerWithInfoWindow() {
            try {

                var Address = $("#txtAddress").val();

                // Add Marker
                geocoder.geocode({ 'address': Address }, function (results, status) {
                    if (status == google.maps.GeocoderStatus.OK) {
                        map.setCenter(results[0].geometry.location);
                        var Marker = new google.maps.Marker({
                            map: map,
                            position: results[0].geometry.location
                        });

                        // Open Info Window
                        var infowindow = new google.maps.InfoWindow(
                        {
                            content: $("#txtAddress").val()
                        }
                        );
                        google.maps.event.addListener(Marker, 'click', function () {
                            infowindow.open(map, Marker);
                        });

                    } else {
                        alert("Geocode was not successful for the following reason: " + status);
                    }
                });

            }
            catch (E) {
                alert(E.Message);
            }
        }
    </script>

</head>
<body onload="InitializeGoogleMap(); return false">
    <form id="form1" runat="server">
    <div>
     <table border="0" cellpadding="5"  cellspacing="5" width="100%" align="center">
            <tr>
                <td style="width:60%">
                    <asp:TextBox ID="txtAddress" runat="server" Height="32px" Width="530px"></asp:TextBox>
                </td>
                <td style="width:40%">
                    <asp:Button ID="btnSearch" runat="server" Text="Search Map" Height="32px" Width="200px" OnClientClick="AddMarkerWithInfoWindow(); return false" />
                </td>
            </tr>
            <tr>
                <td colspan="2" style="height:100%">
                   <div id="GoogleMapCanvas" style="height:510px;"></div>
                </td>
            </tr>
       </table>
    </div>
    </form>
</body>
</html>


Run the Project.


Output
1. Initialize Google Map


Click on Image for better view


2. Auto-Complete Suggestion


Click on Image for better view


3. Add Marker


Click on Image for better view


4. Open Info Window When User Click on Marker


Click on Image for better view


Download
Download Source Code