We use stored procedures to encapsulate business logic on the database server side. This gives us the advantage of modularity, performance, and security to name a few. We can use these stored procedures with LINQ to SQL.
LINQ to SQL maps these database-defined abstractions to code-generated client objects, so that you can access them in a strongly typed manner from client code. This gives us the advantage of using Visual Studio's IntelliSense feature to expose them as methods together with the strongly-typed parameters which might be needed to call the stored procedures.
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
Execute the below stored procedure in northwind database.
CREATE PROCEDURE dbo.Sp_CustomerData AS BEGIN SELECT Customers.CompanyName,Customers.ContactName, Customers.City,Customers.Country,Customers.Phone FROM Customers END
Get all customer data from database.
Step 4
Create a Web application and give solution name as SolLinqToSqlStoredProc.
Create a Web application and give solution name as SolLinqToSqlStoredProc.
Step 5
Add a gridview on page,it is look like this
<asp:ScriptManager ID="ScriptManager" runat="server"> </asp:ScriptManager> <asp:UpdatePanel ID="UpdatePanel" runat="server"> <ContentTemplate> <asp:GridView ID="GvCustomer" runat="server" CellPadding="4" EnableModelValidation="True" ForeColor="#333333" GridLines="None"> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <EditRowStyle BackColor="#999999" /> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> </asp:GridView> </ContentTemplate> </asp:UpdatePanel>
Step 6
Add a ConnectionString in web.config file,it is look like this
<connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=shree\shree;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings>
Step 7
Add app_code folder in the solution and add a new folder inside the app_code folder and give folder name as ORD,it is look like this
Click on image for better view
Step 8
Add a Linq to Sql class,Select the ORD folder,right click on 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 9
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 10
To map stored procedure in DataContext class.go to the server explorer,select northwind database,go to the Stored Procedure and select Sp_CustomerData stored procedure ,it is look like this
Click on image for better view
Drag and drop Sp_CustomerData 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
Step 11
Create a Customer static class in app_code folder for retriving a customer data from database,it is look like this
/// <summary> /// Get a Customer Data from Northwind database /// </summary> /// <returns>List</returns> public static List<ORD.Sp_CustomerDataResult> GetCustomerData() { try { // Create a object of DataContext and specify the connectionstring in datacontext constructor ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); // Call the mapped Stored procedure return DC.Sp_CustomerData().ToList<ORD.Sp_CustomerDataResult>() ; } catch (Exception ex) { throw new Exception(ex.Message); } }
Step 12
Bind a customer data in gridview,it is look like this
protected void Page_Load(object sender, EventArgs e) { try { if (IsPostBack == false) { BindData(); } } catch (Exception) { } } #region Methods /// <summary> /// Bind customer data into a gridview /// </summary> private void BindData() { try { List<ORD.Sp_CustomerDataResult> ListCustomer = Customer.GetCustomerData(); if (ListCustomer != null) { if (ListCustomer.Count > 0) { GvCustomer.DataSource = ListCustomer; GvCustomer.DataBind(); } } } catch (Exception ex) { throw new Exception(ex.Message); } } #endregion
Input Parameter
Stored procedure that returns rows of customers and uses an input parameter to return only those rows that list "London" as the customer city
Execute the below Stored Procedure,it is look like this
CREATE PROCEDURE DBO.Sp_CustomerByCity ( @City varchar(50) ) AS BEGIN SELECT Customers.CompanyName,Customers.ContactName, Customers.City,Customers.Country,Customers.Phone FROM Customers WHERE Customers.City=@City END GO
Drag and drop Sp_CustomerByCity stored procedure from Server explorer onto the design surface of the O/R Designer and call map stored procedure in a customer class,it is look like this
/// <summary> /// Get a customer Data by city /// </summary> /// <param name="City">Specify name of the city</param> /// <returns>List</returns> public static List<ORD.Sp_CustomerByCityResult> GetCustomerByCityData(String City) { try { ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); return DC.Sp_CustomerByCity(City).ToList<ORD.Sp_CustomerByCityResult>(); } catch (Exception ex) { throw new Exception(ex.Message); } }
Bind the data into gridview,it is look like this
protected void Page_Load(object sender, EventArgs e) { try { if (IsPostBack == false) { // BindData(); BindCustomerByCity(); } } catch (Exception) { } } /// <summary> /// Bind Customer data by city name /// </summary> private void BindCustomerByCity() { try { List<ORD.Sp_CustomerByCityResult> ListCustomer = Customer.GetCustomerByCityData("London"); if (ListCustomer != null) { if (ListCustomer.Count > 0) { GvCustomer.DataSource = ListCustomer; GvCustomer.DataBind(); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
Output Parameter
LINQ to SQL maps "out" parameters in stored procedure as reference parameters (ref keyword), and for value types declares the parameter as nullable.
Sp_GetCompanyNameByCustomerID stored procedure which takes a CustomerID as an input parameter, and which returns the company name as an output parameter.
Execute the below stored procedure in database,it is look like this
CREATE PROCEDURE DBO.Sp_GetCompanyNameByCustomerID ( @CustomerID nchar(50), @CompanyName nvarchar(50) OUTPUT ) AS BEGIN SELECT @CompanyName=Customers.CompanyName FROM Customers WHERE Customers.CustomerID=@CustomerID END
Drag and drop Sp_GetCompanyNameByCustomerID stored procedure from Server explorer onto the design surface of the O/R Designer and call map stored procedure in a customer class,it is look like this
/// <summary> /// get only company name by customer ID /// </summary> /// <param name="CustomerID">Specify customer ID</param> /// <returns>String</returns> public static String GetCompanyName(String CustomerID) { String CompanyName = String.Empty; try { ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); DC.Sp_GetCompanyNameByCustomerID(CustomerID, ref CompanyName); return CompanyName; } catch (Exception ex) { throw new Exception(ex.Message); } }
Write a Company Name
Response.Write("Company Name - "+Customer.GetCompanyName("FRANK"));
Run the project.
Full Code
1 .aspx
<%@ 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> <asp:ScriptManager ID="ScriptManager" runat="server"> </asp:ScriptManager> <asp:UpdatePanel ID="UpdatePanel" runat="server"> <ContentTemplate> <asp:GridView ID="GvCustomer" runat="server" CellPadding="4" EnableModelValidation="True" ForeColor="#333333" GridLines="None"> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <EditRowStyle BackColor="#999999" /> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> </asp:GridView> </ContentTemplate> </asp:UpdatePanel> </div> </form> </body> </html>
2. Customer Static Class
using System; using System.Collections.Generic; using System.Linq; using System.Web; public static class Customer { #region Methods /// <summary> /// Get a Customer Data from Northwind database /// </summary> /// <returns>List</returns> public static List<ORD.Sp_CustomerDataResult> GetCustomerData() { try { // Create a object of DataContext and specify the connectionstring in datacontext constructor ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); // Call the mapped Stored procedure return DC.Sp_CustomerData().ToList<ORD.Sp_CustomerDataResult>() ; } catch (Exception ex) { throw new Exception(ex.Message); } } /// <summary> /// Get a customer Data by city /// </summary> /// <param name="City">Specify name of the city</param> /// <returns>List</returns> public static List<ORD.Sp_CustomerByCityResult> GetCustomerByCityData(String City) { try { ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); return DC.Sp_CustomerByCity(City).ToList<ORD.Sp_CustomerByCityResult>(); } catch (Exception ex) { throw new Exception(ex.Message); } } /// <summary> /// get only company name by customer ID /// </summary> /// <param name="CustomerID">Specify customer ID</param> /// <returns>String</returns> public static String GetCompanyName(String CustomerID) { String CompanyName = String.Empty; try { ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); DC.Sp_GetCompanyNameByCustomerID(CustomerID, ref CompanyName); return CompanyName; } catch (Exception ex) { throw new Exception(ex.Message); } } #endregion }
3. .aspx.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { try { if (IsPostBack == false) { // BindData(); // BindCustomerByCity(); Response.Write("Company Name - "+Customer.GetCompanyName("FRANK")); } } catch (Exception) { } } #region Methods /// <summary> /// Bind customer data into a gridview /// </summary> private void BindData() { try { List<ORD.Sp_CustomerDataResult> ListCustomer = Customer.GetCustomerData(); if (ListCustomer != null) { if (ListCustomer.Count > 0) { GvCustomer.DataSource = ListCustomer; GvCustomer.DataBind(); } } } catch (Exception ex) { throw new Exception(ex.Message); } } /// <summary> /// Bind Customer data by city name /// </summary> private void BindCustomerByCity() { try { List<ORD.Sp_CustomerByCityResult> ListCustomer = Customer.GetCustomerByCityData("London"); if (ListCustomer != null) { if (ListCustomer.Count > 0) { GvCustomer.DataSource = ListCustomer; GvCustomer.DataBind(); } } } catch (Exception ex) { throw new Exception(ex.Message); } } #endregion }
Download
Download Source Code
No comments:
Post a Comment