Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Articles » ASP.NET/Web Applications »
Linq Quries in ASp.net
|
Linq Quries in ASp.net:
LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.
LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.
Modeling Databases Using LINQ to SQL:
Visual Studio "Orcas" ships with a LINQ to SQL designer that provides an easy way to model and visualize a database as a LINQ to SQL object model. My next blog post will cover in more depth how to use this designer.
Displaying SQL Server Data using a Linq Data Source
Linq to SQL is an object relational model that you can leverage in Visual Studio 2008. Your connection to SQL Server 2008 database is through a new control in Visual Studio 2008, the LinqDataSource control. This control needs a data context. The data context is provided by the Linq to SQL classes, a class generator that maps SQL server objects to the model. The class files generated support CRUD operations.
Understanding the Data Context Class:
When you press the "save" button within the LINQ to SQL designer surface, Visual Studio will persist out .NET classes that represent the entities and database relationships that we modeled. For each LINQ to SQL designer file added to our solution, a custom Data Context class will also be generated. This Data Context class is the main conduit by which we'll query entities from the database as well as apply changes. The Data Context class created will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we added.
use procedure..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GetUsers]
(@RowIndex INT ,
@MaxRows INT)
AS
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = (@RowIndex+1)
SET @EndRow = @StartRow + @MaxRows
SELECT * FROM (
SELECT FirstName, LastName, ROW_NUMBER() OVER (ORDER BY UserID) AS ROW
FROM CD_Users) As NumberedUsers
WHERE ROW BETWEEN @StartRow AND @EndRow
this code is done by usin three layer architecture..
uses 2,3 classes that are bussiness layer for entity setting,data layer and other class use for connection seting..
datalayer 4 procedure and its parametres pass through it..
UserDAO.cs
-------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
///
/// Summary description for UserDAO
///
public class UserDAO
{
public UserDAO()
{
//
// TODO: Add constructor logic here
//
}
public int GetUserCount()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["HP_HamptonConnectionString"].ConnectionString);
con.Open();
SqlCommand com = new SqlCommand("select count(*) UserCOUNT from CD_Users", con);
SqlDataReader dr = com.ExecuteReader();
int count = 0;
while (dr.Read())
{
if (dr["UserCOUNT"] != null)
int.TryParse(dr["UserCOUNT"].ToString(), out count);
}
return count;
}
public DataTable BindUsers(int startRowIndex, int maximumRows)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["HP_HamptonConnectionString"].ConnectionString);
SqlCommand com = new SqlCommand("GetUsers", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@RowIndex", SqlDbType.Int, 4).Value = startRowIndex;
com.Parameters.Add("@MaxRows", SqlDbType.Int, 4).Value = maximumRows;
SqlDataAdapter ada = new SqlDataAdapter(com);
DataTable dt = new DataTable();
ada.Fill(dt);
return dt;
}
}
bussines layer:
BC.CS
---------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
[Serializable()]
///
/// Summary description for BC
///
public class BC:D_Base
{
UserDAO objdat = new UserDAO();
public BC()
{
//
// TODO: Add constructor logic here
//
}
public DataTable BindUsers(string FirstName, string LastName, int startRowIndex, int maximumRows)
{
DataTable dt = objdat.BindUsers(startRowIndex,maximumRows);
return dt;
}
public int GetUserCount(string FirstName, string LastName)
{
int UserCount = objdat.GetUserCount();
return UserCount;
}
}
-----------------------------------
i used this layer for connection:
-------------------------------------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
[Serializable]
///
/// This is the super class for Data Access Classes
///
public class D_Base
{
protected static string strConnect;
protected static string strConnectHR;
public D_Base()
{
}
///
/// Please see the web.config file
///
static D_Base()
{
strConnect = ConfigurationSettings.AppSettings["HP_HamptonConnectionString"];
//strConnectHR = ConfigurationSettings.AppSettings["connHR"];
}
///
/// Gets a SqlConnection to the local sqlserver
///
/// SqlConnection
protected SqlConnection GetConnection()
{
SqlConnection oConnection = new SqlConnection(strConnect);
return oConnection;
}
}
----------------
In aspx.cs
----------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.ServiceModel;
public partial class _Default : System.Web.UI.Page
{
BC obj = new BC();
protected void Page_Load(object sender, EventArgs e)
{
//DataTable ObjectDataSource1 = new DataTable();
//ObjectDataSource1 = obj.BindUsers("firstname","lastname",1,2);
////GridView1.DataSource = ObjectDataSource1;
//GridView1.DataBind();
}
In Html:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
-------------------------------------------------------------------
how to take or add linq in aspx..and its connection given below..:
--------------------------------------------------------------------
Create web site project and add LinqDataSource control
Open Visual Studio 2008 from its shortcut on the desktop. Click File | New | Web Site...(or Shift+Alt+N) to open the New Web Site window. Change the default name of the site to a name of your choice (herein LinqDemo)on your local web server as shown. Make sure you are creating a .NET Framework 3.5 web site as shown here.
************************
i cant add pic here..
********************************
Drag and drop a LinqDataSource control from Toolbox|Data shown in the next figure on to the Default.aspx
This creates an instance of the control LinqDataSource1 as shown. The figure also shows the smart tasks of this control as shown.
************************
i cant add pic here..
********************************
Create a data context for the LinqDataSource control
In order to use this control you also need to create a data context. Right click the localhost web site and choose Add New Item...to open the Add New Item - http://localhost/ LinqDemo window as shown.
************************
i cant add pic here..
********************************
In the Visual Studio installed templates highlight Linq to SQL Classes. Change the default name from DataClasses.dbml to a name of your choosing. Herein MyDC.dbml. Click Add. This pops-up a Microsoft Visual Studio warning message as shown. The preferred location for this file is in the App_Code folder of your project as suggested here.
************************
i cant add pic here..
********************************
Click on Yes. This adds a MyDC.dbml file to APP_Code folder as shown. MyDC.dbml consists of two components MyDC.dbml and MyDC.designer.vb to the App_Code folder as shown .
************************
i cant add pic here..
********************************
Double click the MyDC.dbml node in the APP_Code folder. This opens the ObjectRelational Designer and the designer surface with two panes as shown. Read the instructions in the windows. In the left pane you can drag and drop items from the Server Explorer in Visual Studio to create the appropriate classes and in the right pane you can drag and drop stored procedures. In this article we will be looking at just creating classes from table objects.
************************
i cant add pic here..
********************************
Configure the LinqDataSource control and bind it to data
We assume that you have created a connection to your SQL Server 2008 in Visual Studio 2008 (Add a new Data Connection by clicking on the icon in the Server Explorer and create a connection to SQL Server 2008's Northwind database). From View | Server Explorer you can access the objects on your SQL Server after expanding the nodes as shown.
Now drag and drop the Customers tables into the first pane, the Object Relational Designer pane as shown.
This activity creates code which you may review by opening the App_COde/MyDC.designer.vb. Highlight the Customer object in the ObjectRelational designer and right click to review its properties. You will see that it has created a Customer Data class using Customers table as shown.
You can also see that a data context has been created as shown. The connection string is stored in the web.config file.
Click on the Configure Data Source... in the LinqDataControl's smart tasks. This opens the "Choose a Context Object" of the Configure Data Source -LinqDataSource1 wizard. The MyDCDataContext should be visible as shown.
Click Next. In the "Configure Data Selection" page of the wizard choose a couple of items. Herein CustomerID, CompanyName, Address, City, and Country are chosen. You can also filter and order your selection using the Where... and OrderBy..buttons. Here the defaults are used.
Click Finish. This completes the LinqDataSource control. You can see these details in the source of Default.aspx as shown in the LinqDataSource control's declarative code.
Adding a GridView and associating it with data
Drag and drop a GridView from the Toolbox | Data on to the design surface of the Default.aspx page below the LinqDataSource1. Click on the Smart Tasks to associate the LinqDataSource as a source for the GridView
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|