Syed A. Rizvi - Developing Web-based Architectures: October 2008

Monday, October 27, 2008

LINQ to SQL - Building UI Layer using ASP:LinqDataSource Control

LINQ stands for Language Integrated Query which was introduced with .NET 3.5. LINQ provides the ability to write query as a first-class language construct using modern programming languages like C# and Visual Basic.

LINQ acts as a bridge between Objects and Data, classic data driven applications expected the programmer to be proficient in both the programming language (C#, Visual Basic etc) and the data source native language (SQL, XQuery etc) to extract the data.

LINQ is designed to work against all data sources possible which includes objects, relational, XML etc.

LINQ allows the developer to write type-safe queries against data sources with their choice of programming languages (C#, Visual Basic). LINQ enjoys full compile-time and intellisense support.

.NET 3.5 framework supports three flavors of LINQ supported by Microsoft:

. LINQ to Objects
. LINQ to XML
. LINQ to ADO.NET [LINQ to SQL, LINQ to Entities and LINQ to DataSet]

For more details on LINQ visit: MSDN

The example uses NorthWind database. You can download and install NorthWind database from the following location.

The article will demonstrate working of LINQ to SQL enabled UI by creating an ASP.NET web-form with GridView control bound to the newly introduced LinqDataSource control (.NET 3.5 required).

LinqDataSource control allows the ability to connect data from wide variety of sources like database, collections etc, while maintaining a uniform programming model for data access.

In our example we will see how LinqDataSource control interacts with Database, when LinqDataSource control is used to interact with database it communicates with the database through Entity classes which are generated by O/R Designer (Object Relational Designer).

O/R Designer (Object Relational Designer) can be accessed by adding 'LINQ to SQL classes' from Visual Studio 2008 IDE, the designer usually creates one class mapping the database and one class each for all the tables in the database. The generated classes are typically placed in the App_Code folder of the project.

Let us see the steps involved in adding a LINQ to SQL enabled UI.

a) Use O/R designer to map the database and tables, I have added 'Categories' table to the designer, the sample web-form will use 'Categories' table to demonstrate display/edit/delete functionality:

b) Add a GridView control and click on 'Choose Data Source':

c) Select LINQ from Data Source Type and then provide an ID for the DataSource selected:

d) Choose the DataContext from the drop-down list, this is the name for your O/R designer generated class:

e) Configure Data Source, Pick the 'Categories' table from the Table list, Select '*' for the Columns and then Click 'Advanced':

f) Select all three options to enable Insert, Update & Delete functionality:

g) Select options to Enable Insert, Update & Delete functionality in the GridView control:

h) Web-form is wired with all the controls and is being displayed in 'Display' mode:

i) Web-form is wired with all the controls and is being displayed in 'Edit' mode:

j) Web-form is displayed with warning message showing 'Category Name cannot be blank', we will visit this section in the code walk-through:

k) Web-form is displayed with successful update:

Code Walk-through:

a) Web-form declarative Markup:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="myGrid.aspx.cs" Inherits="myGrid" %>

<!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">
<asp:Literal runat=server ID="ErrMsg"></asp:Literal>
<asp:GridView ID="GridView1" runat="server" DataSourceID="DBLINQ" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="700px" AllowPaging="True" PagerSettings-Mode=NextPreviousFirstLast PageSize="5" AllowSorting="True"
Height="112px" AutoGenerateColumns="False" DataKeyNames="CategoryID">
<PagerSettings Mode="NumericFirstLast" />
<RowStyle BackColor="#EFF3FB"  Font-Names="Verdana" Font-Size=Small/>
<Columns>
<asp:BoundField DataField="CategoryID"  HeaderStyle-Wrap=false HeaderText="Category ID"
InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" >
</asp:BoundField>
<asp:BoundField DataField="CategoryName" HeaderStyle-Wrap=false HeaderText="Category Name"
SortExpression="CategoryName" >
</asp:BoundField>
<asp:BoundField DataField="Description"  HeaderStyle-Wrap=false HeaderText="Description"
SortExpression="Description" >
</asp:BoundField>
<asp:CommandField HeaderText="Action" ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
<FooterStyle BackColor="#507CD1"  Font-Names="Verdana" Font-Size=Small Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF"   Font-Names="Verdana" Font-Size=Small ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1"    Font-Names="Verdana" Font-Size=Small Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1"  Font-Names="Verdana" Font-Size=Small Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#FFFFFF"  Font-Names="Verdana" Font-Size=Small/>
<AlternatingRowStyle BackColor="White"  Font-Names="Verdana" Font-Size=Small/>
</asp:GridView>
<asp:LinqDataSource ID="DBLINQ" runat="server"
        ContextTypeName="DataClassesDataContext" EnableDelete="True"
        EnableInsert="True" EnableUpdate="True" TableName="Categories">
    </asp:LinqDataSource>
</form>
</body>
</html>

b) Web-form Code-behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


public partial class myGrid : System.Web.UI.Page
{
private Boolean IsError;


protected void Page_Load(object sender, EventArgs e)
{
//wire event handlers
GridView1.RowUpdating      += new
GridViewUpdateEventHandler(GridView1_RowUpdating);
DBLINQ.Updating            += new
EventHandler<LinqDataSourceUpdateEventArgs>(DBLINQ_Updating);
//ini
IsError                    = false;
ErrMsg.Visible             = false;
}


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//get the row being updated
GridViewRow currRow  = GridView1.Rows[e.RowIndex];
//check for null or empty value
string txtCatName    = ((TextBox)(currRow.Cells[1].Controls[0])).Text;
if (string.IsNullOrEmpty(txtCatName))
{
ErrMsg.Text            = "
<div style='font-family:Verdana;font-size:smaller;font-weight:bolder;
color:red'>* Category Name cannot be blank</br></br></div>";
ErrMsg.Visible         = true;
e.Cancel               = true;
//set the IsError variable to cancel LINQDataSource control update
IsError                = true;
}
}

protected void DBLINQ_Updating(object sender, LinqDataSourceUpdateEventArgs e)
{
//check if error occurred
if (IsError) e.Cancel = true;
}
}

Code highlighted with orange shows how we can add data validation logic to the update routine. This is just a basic example ideally we would have used ASP.NET RequiredFieldValidation control.

Key thing to notice is we need to add custom validation to both the GridView's RowUpdating and LinqDataSource Updating event handlers, as this gives us more granular control towards the validation.

Once the GridView's RowUpdating event handler detects an exception it sets the global variable 'IsError' to True, this variable is then checked by the LinqDataSource Updating event handler before performing the Database update. If the variable is False then the changes are saved to the Database.

Another way of handling the validation logic is to handle it in the LinqDataSource RowUpdated event handler but my personal choice is to use the approach as described above since this approach gives us more flexibility in both handling validation and providing custom feedback to the User.

I will try to provide more working examples of LINQ in the future posts!

Friday, October 17, 2008

ASP.NET/SQL Server 2008 – Using Table-Valued Parameters with .NET Application

With the release of SQL Server 2008, Microsoft introduced a new parameter type called 'Table-Valued Parameters', which provided the flexibility for the SQL developers to send multiple rows of data to a stored procedure/function or inline T-SQL code.

Table-Valued parameters can be used to transfer multiple rows of data without requiring the developer to construct custom string parsing routines, temporary table or multiple parameters which for a complex stored procedure can become difficult to maintain.

Table-Valued parameters are declared in series of steps:

Create a Table type and define the table structure based on the requirement
Declare a local variable to use this Table type
Initialize the local variable with the data
Pass the local variable to the stored procedure/function to accept this table parameter, make sure the input parameter is 'READONLY'

Table-Valued parameter goes out of scope once the stored procedure/function/T-SQL is executed. Table-Valued parameter can only be modified in the scope it was created, due to this criteria when the parameter is passed to a different stored procedure/function it is passed as READONLY.

For more details on Table-Valued parameters visit: MSDN

Let us consider a real-world example using NorthWind database. You can download and install NorthWind database from the following location.

The article will demonstrate working of Table-Valued Parameters by creating a custom stored-procedure and calling the stored-procedure code from the .NET application (.NET 3.5 required).

NorthWind database contains 'Shippers' table, this table stores shipping company contact details used in the Order fulfillment operation.

We are going to insert 2 new Shippers record in the table, using Table-Valued Parameter enabled stored procedure, the stored procedure will be invoked from .NET code.

a) Create User-Defined Table type

CREATE TYPE Shippers AS TABLE (CompanyName nvarchar(80), phone nvarchar(48))

b) Creating the Table-Valued Parameter enabled Stored procedure

CREATE Procedure CreateShippers(@Shippers Shippers READONLY)
AS
BEGIN
INSERT INTO Shippers SELECT CompanyName, Phone FROM @Shippers
END
GO

Notice @Shippers variable of Shippers type is declared with READONLY qualifier.

c) Calling the Stored procedure through .NET application

I have created a test web form to demonstrate how we can call the Table-Valued Parameter enable stored procedure from .NET application.

Web form displays original data in the Shippers table and a 'Load Shippers' button, once the button is clicked 2 new rows of data are inserted into the Shippers table and the web form reloads to display the newly inserted data (I am using hard coded values in the OnClick event handler of the button to initialize data, the code can be easily modified to accept user keyed data by adding text box controls to the web form).

Screen shot showing Web-form with Original and Inserted data:

ASP.NET Web form Code (C#):

protected void Button1_Click(object sender, EventArgs e)
{
string path = Request.CurrentExecutionFilePath;
path = path.Substring(0, path.LastIndexOf('/'));
Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration(path);
ConnectionStringSettings connString = rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];

//create DataTable
DataTable dt = new DataTable("MyData");
DataColumn dbCol1 = new DataColumn("CompanyName", System.Type.GetType("System.String"));
DataColumn dbCol2 = new DataColumn("Phone", System.Type.GetType("System.String"));
//add columns
dt.Columns.Add(dbCol1);
dt.Columns.Add(dbCol2);

//create data
DataRow dr;
dr = dt.NewRow();
dr["CompanyName"] = "ACME Express";
dr["Phone"] = "(503) 111-1111";
dt.Rows.Add(dr);
//new row
dr = dt.NewRow();
dr["CompanyName"] = "Courier Express";
dr["Phone"] = "(503) 222-2222";
dt.Rows.Add(dr);
//save
dt.AcceptChanges();

//open Database connection
using (SqlConnection dbConn = new SqlConnection(connString.ConnectionString))
{
dbConn.Open();
using (SqlCommand dbCommand = new SqlCommand("CreateShippers", dbConn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
//Create a parameter using the new type
SqlParameter param = dbCommand.Parameters.Add("@Shippers", SqlDbType.Structured);
//Set the value of the parameter
param.Value = dt;
//Execute the query
dbCommand.ExecuteNonQuery();
}
}

//get new inserted data
SqlDataSource1.ConnectionString = connString.ConnectionString;
SqlDataSource1.SelectCommand = "Select * from Shippers";
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.Text;
GridView1.DataSourceID = "SqlDataSource1";
//show confirmation
Msg.Visible = true;
//hide button
Button1.Visible = false;
}

Wednesday, October 8, 2008

ASP.NET - Efficiently Importing DataTable

ASP.NET developers often use DataTable to store data received from SQL stored procedure, web-service, data access layer (DAL) etc.

DataTable acts like a temporary container where the data is stored, refined (trimming white-spaces, number formatting), transformed (calculating a running total) and then consumed.

For more details on DataTable visit: MSDN

The article will demonstrate how to efficiently create a DataTable from an existing DataTable based on row by row comparison.

Suppose DataTable (A) has 1000 rows of Order data placed by multiple customers of the web-site, our goal is to efficiently create a new DataTable (B) using DataTable (A) to extract Orders having customer ID of 101 & 102.

DataTable (A) is populated by making a call to the DAL, which returns all the Orders placed to date [ideally DAL would provide a method which will return filtered data but for now we are going to go with the assumption of filtering data out of DataTable (A)].

Code:

//DataTable (B) is cloned from DataTable (A)
DataTable B = A.Clone();
//clear DataTable
B.Clear();
//save changes
B.AcceptChanges();

Note we are using DataTable 'Clone' method instead of 'Copy' method as our objective is to only retain the structure of DataTable (A) and not the data.

Now we need to populate DataTable (B) with Orders having customer ID of 101 & 102.

//iterate
foreach(DataRow dr in A.Rows)
{
int customerID = Convert.ToDouble(dr["CustomerID"]);
//match customer ID
if (customerID == 101 || customerID == 102)
{
//customerID matched add it to DataTable (B)
B.ImportRow(dr);
}
}
//save
B.AcceptChanges();

DataTable’s ImportRow method copies the row from DataTable (A) to DataTable (B), performance wise ImportRow is faster than individually adding rows specially when dealing with large amount of data.

Syed A. Rizvi - Developing Web-based Architectures

Monday, October 27, 2008

LINQ to SQL - Building UI Layer using ASP:LinqDataSource Control

Friday, October 17, 2008

ASP.NET/SQL Server 2008 – Using Table-Valued Parameters with .NET Application

Wednesday, October 8, 2008

ASP.NET - Efficiently Importing DataTable

About Me

Published Posts

Categories

Feed Me

Followers