Archive

Archive for June, 2009

Creating and Using Table-Valued Parameter in C# and T-SQL

June 21, 2009 2 comments

With the growing complexity of database applications, we often come across a problem when we need to send multiple rows to database table(s). When SqlBulkCopy class was introduced in .Net 2.0, it was a great relief for all. But if the same requirement arises at back-end processing and mostly in Stored Procedures, programmers often feel something lacking in stored procedure parameters. Workaround solutions are temporary tables, cursors, etc. to overcome.
SQL Server 2008 has a new feature called Table-Valued parameter where we can pass a table valued parameter to stored procedure just like we pass Entity or DTO class object as parameter to Data Access Layer in C# or VB.Net.
Now we can directly pass table like object instead of processing row by row ranging from few to many rows. We used SqlBulkCopy class to post many rows, but still it was not able to pass a table as a parameter.
In this example, we will see how to create a table-value type object, pass it to a stored procedure as a parameter in Sql Server 2008. Also, how to use it programmatically in C# 3.5 as well.

–Step 1: Create a User-defined Table Type
USE AdventureWorks
GO
CREATE TYPE PublisherTableType AS TABLE
(
PublisherID bigint,
PublisherName varchar(50),
PublisherAddress varchar(50),
PublisherContactNo varchar(15)
);
GO

–Step 2: Create a destination table
CREATE TABLE [dbo].[BookPublisher](
[PublisherId] [bigint] NULL,
[PublisherName] [varchar](50) NULL,
[PublisherAddress] [varchar](50) NULL,
[PublisherContactNo] [varchar](15) NULL
) ON [PRIMARY]

–Step 3: Create a procedure to receive data for the table-valued parameter
CREATE PROCEDURE usp_InsertPublisherInfo
@PublisherInfo dbo.PublisherTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO dbo.BookPublisher(PublisherID,PublisherName,PublisherAddress,PublisherContactNo)
SELECT * FROM @PublisherInfo;
GO

–Step 4: Declare a variable of type PublisherTableType
Declare @PubInfo dbo.PublisherTableType;
Insert into @PubInfo
values      (1, ‘BPB’, ‘Delhi, India’, ‘1234567890’   ),
(2, ‘PHI’, ‘Mumbai, India’, ‘7892345678’),
(3, ‘Apress’, ‘Chennai, India’, ‘467890034’),
(4, ‘Wrox’, ‘Kolkata, India’, ‘7463643847’);

–Step 5: Execute SP by passing table value type
Exec usp_InsertPublisherInfo @PublisherInfo = @PubInfo;

–Step 6: Select the newly inserted records
Select * from dbo.BookPublisher;
GO

I think steps mentioned in the above example are quite clear. One can see this newly created table value type object under “User-Defined Table Types”. Now, let’s look into another example to exploit the newly created PublisherTableType.

private DataTable PublisherList()
{
//
DataTable TblPub = new DataTable();
TblPub.Columns.Add(“PublisherId”, typeof(Int64));
TblPub.Columns.Add(“PublisherName”, typeof(String));
TblPub.Columns.Add(“PublisherAddress”, typeof(String));
TblPub.Columns.Add(“PublisherContactNo”, typeof(String));
DataRow PubRow;
//Fill the collection values
for (Int16 RwCnt = 0; RwCnt < 10; RwCnt++)
{
//
PubRow = TblPub.NewRow();
PubRow[0] = 100 + RwCnt;
PubRow[1] = “BookPub” + ” – ” + RwCnt.ToString();
PubRow[2] = “PubAddress” + ” – ” + RwCnt.ToString();
PubRow[3] = “0091” + ” – ” + “4567” + RwCnt.ToString();
TblPub.Rows.Add(PubRow);
}
return TblPub;
}
private void SaveDataUsingTableValueType(DataTable PubList)
{
//
String ConString = @”Data Source=DINESH_PC\DINESHPC;Initial Catalog=AdventureWorks;Integrated Security=True”;
SqlConnection SqlCon = new SqlConnection(ConString);
String SpName = “usp_InsertPublisherInfo”;
SqlCommand SqlCmd = new SqlCommand(SpName, SqlCon);
SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
//Parameter type is SqlDbType.Structured for table valued type parameter
SqlParameter SqlParam = SqlCmd.Parameters.Add(“@PublisherInfo”, System.Data.SqlDbType.Structured);
SqlParam.Value = PubList;
SqlCon.Open();
//Execute the command
SqlCmd.ExecuteNonQuery();
SqlCon.Close();
}

Next, call the save method as: SaveDataUsingTableValueType(PublisherList());
This way we see how this new feature is a bliss.
We have to remember some points with table value type. The READONLY qualifier after table value parameter in stored procedure is required here to mark parameter object as a pointer to the original table value. This way the original content passed cannot be modified inside the stored procedure. Once a table type object is created, it cannot be altered other than dropping and recreating it again with modified definition. Performance wise it is well suited for table rows less than 1000.

Have a nice coding day!

ASP.Net Ajax Callbacks to: Web Service and ASP.Net Page’s Web Methods

Today we will see how ASP.Net Ajax callback feature can help us call methods defined in Web Service and ASP.Net page. ScriptService and ScriptMethod are two new attributes in ASP.Net Ajax framework library that helps calling web service and web page web methods respectively.
Let’s see it one by one in the following examples.

First, create a simple web service project called ASPAjaxService.asmx. Define a web method as given below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
namespace ASPAjaxService
{
/// <summary>
/// Summary description for ASPServiceMethods
/// </summary>
[WebService(Namespace = “http://tempuri.org/&#8221;)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script,
// using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class ASPServiceMethods : System.Web.Services.WebService
{
//
[WebMethod(true)]
public string WebServiceMethod()
{
return “Example: Consuming a web service using ASP.Net Ajax.”;
}
}
}
See the ScriptService attribute that is applied to Class declaration ASPServiceMethods. This indicates that a web service can be invoked from script. In fact, it provides a JavaScript proxy class corresponding to the web service in order to call the web service web methods. Now to test this service, let’s view our *.asmx in browser. Probably, we may see url as: http://localhost:49487/ASPAjaxService.asmx. The port number may vary, but we are now ready to test  WebServiceMethod web method. Before this, change the url as: http://localhost:49487/ASPAjaxService.asmx/js and hit [Enter]. It will return a proxy code of JavaScript. We may save it to our project folder to see its details. This is result of ScriptService attribute.

Now, create a simple ASP.Net application and add this existing web service project to this application. Modify the Default.aspx markup as:

<head runat=”server”>
<title>ASP.Net Ajax CallBack</title>
<script type=”text/javascript” language=”javascript” src=”javascript_ajax_asp_net.js”></script>
</head>
<body>
<form id=”form1″ runat=”server”>
<asp:ScriptManager runat=”server” ID=”ScriptManager1″ EnablePageMethods=”true”>
<Services>
<asp:ServiceReference InlineScript=”false” Path=”~/ASPAjaxService.asmx”/>
</Services>
</asp:ScriptManager>
<div>
[Invoke Web Method] – Make a call to Web Method in web service: ASPAjaxService.asmx<br /><br />
<input type=”button” id=”BtnCallWebMethod” value=”Invoke Web Method” onclick=”return CallWebServiceMethod();” /><br /><br />
[Invoke Web Method defined in UI.Page] – Make a call to Page Method in Default.aspx<br /><br />
<input type=”button” id=”BtnPageMethod” value=”Invoke Web Method defined in UI.Page” onclick=”return CallPageMethods();” />
</div>
</form>
</body>
</html>

See the <asp:ScriptManager/> tag section. ServiceReference registers the web service mentioned in Path attribute for use in a web page.  Path can only point to local web service, i.e.; in the same domain.

InlineScript value can be either true or false. Toggle its value, view Default.aspx in browser, view its html source and save it. Compare the size of two files. You will see a remarkable difference. When InlineScript is set to true, the proxy javascript code is generated and rendered directly in the page for each request. Due to this, the page can not be cached for similar request and page size is also larger.

But in our example, we have set this property to false and added src=”javascript_ajax_asp_net.js”.  It has required functions to fulfil our purpose. But one may compare and see both the proxy and current *.js file. In this case, we can definitely benefit from browser caching and shorter page load time!

The javascript file has following code.

//Webservice web methods
function CallWebServiceMethod() {
ASPAjaxService.ASPServiceMethods.set_timeout(1000);
ASPAjaxService.ASPServiceMethods.WebServiceMethod(onSuccess, onFailed, “Caller Context”);
}
function onSuccess(result, usercontext) {
alert(result + ” – ” + usercontext);
}
function onFailed(result, usercontext) {
var failed_message = “Some error occured while calling the web method.”;
alert(failed_message + ” : ” + usercontext);
}
//PageMethods
function CallPageMethods() {
PageMethods.WebMethodInPage(onSuccessfulPageCall, onFailed, “Caller Context:System.Web.UI.Page”);
}
function onSuccessfulPageCall(result, usercontext) {
alert(result + ” – ” + usercontext);
}
We have not done yet.  We are still one step away from calling a web method in ASP.Net page.

Add this method in code behind of our ASP.Net page as given below.

[WebMethod(true)]
[System.Web.Script.Services.ScriptMethod(UseHttpGet = true)]
public static string WebMethodInPage()
{
//
return “I am in Page Method.”;
}
Run the page and call each web method.  We are able to call ASP.Net page method due to ScriptMethod attribute for WebMethodInPage() method and EnablePageMethods=”true” in ScriptManager class property. ScriptManager.EnablePageMethods property when set to true enables public static web method in page to be called by javascript.

That’s all! Happy coding!

New Feature In SQL Server 2008: Row Constructor

I think we all are familiar with INSERT statement in Sql Server 7.0/2000/2005, and may be in Oracle, MySQL, etc. In Sql Server, we have come across INSERT INTO TableName (columns) VALUES (literalValues),  or INSERT INTO TableName (columns) SELECT columns FROM TableNameX statement many times. The first type of statement is used when we have to insert one record at a time, while the second statement helps us to insert one or more records depending upon the number of rows returned by SELECT statement. Some even use UNION clause to select multiple rows.

We also know how painful it was to insert multiple records using  INSERT INTO TableName (columns) VALUES (literalValues) from DataAccessLayer although different programmers have different approaches to it.

But Sql Server 2008 has now come with a rescue for all programmers with a new feature called “Row Constructor“. Yes, a Class like initialization constructor!

Let’s see it by example.

Example 1:
–Create a new table [BusinessClients] with row values from the list [WHERE Age <= 30]
SELECT Name, Email, Profession, ContactNo
INTO dbo.BusinessClients
FROM
(
VALUES(‘Dinesh’, ‘dkm@hotmail.com’,’Software Engineer’, ‘9899144254’, 28),
(‘Ravindra’, ‘rg@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),
(‘Rosan’, ‘rs@who.com’, ‘Social Activist’, ‘9891234560’, 30),
(‘Hari’, ‘ha@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)
) as Employee(Name, Email, Profession, ContactNo, Age)
WHERE Age <= 30;
Go

–Fetch the inserted records from a newly created table
Select * from dbo.BusinessClients;
Go

Example 2:
–Select records from the row constructors
SELECT Name, Email, Profession, ContactNo
FROM
(
VALUES(‘DineshMandal’, ‘dinesh.kumar.mandal@hotmail.com’,’Software Engineer’, ‘9899144254’, 28),
(‘RavindraGhimire’, ‘ravindra@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),
(‘RosanSapkota’, ‘rosan@who.com’, ‘Social Activist’, ‘9891234560’, 30),
(‘HariAdhikari’, ‘hari@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)
) as EmployeeDetails(Name, Email, Profession, ContactNo, Age)
WHERE Age <= 30;
Go

See the VALUES clause in parenthesis and separated by comma. This is what we call “Row Constructor”. In example 2, see the derived table name EmployeeDetails and its column names inside the parenthesis. And of course the list of column names we like to select out of VALUES parameter values. These are few things we should take care when using row constructor.

Result of Example 2:
SQL Insert

I think this feature is really praiseworthy.

<!–[if gte mso 9]> Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4 <![endif]–><!–[if gte mso 9]> <![endif]–> <!–[endif]–>

I think we all are familiar with INSERT statement in Sql Server 7.0/2000/2005, and may be in Oracle, MySQL, etc. In Sql Server, we have come across INSERT INTO TableName (columns) VALUES (literalValues), or INSERT INTO TableName (columns) SELECT columns FROM TableNameX many times. The first type of statement is used when we have to insert one record at a time, while the second statement helps us to insert one or more records depending upon the number of rows returned by SELECT statement. Some even used UNION clause to select multiple rows.

We also know how painful it was to insert multiple records using INSERT INTO TableName (columns) VALUES(literalValues) from DataAccessLayer although different programmers have different approaches to it.

But Sql Server 2008 has now come with a rescue for all programmers with a new feature called “Row Constructor”. Yes, a Class like initialization constructor!

Let’s see it by example.

Example 1:

–Create a new table [BusinessClients] with row values from the list [WHERE Age <= 30]

SELECT Name, Email, Profession, ContactNo

Into dbo.BusinessClients

FROM

(

VALUES(‘Dinesh’, ‘dkm@hotmail.com’,‘Software Engineer’, ‘9899144254’, 28),

(‘Ravindra’, ‘rg@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),

(‘Rosan’, ‘rs@who.com’, ‘Social Activist’, ‘9891234560’, 30),

(‘Hari’, ‘ha@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)

) as Employee(Name, Email, Profession, ContactNo, Age)

WHERE Age <= 30;

Go

–Fetch the inserted records from a newly created table

Select * from dbo.BusinessClients;

Go

Example 2:

–Select records from the row constructors

SELECT Name, Email, Profession, ContactNo

FROM

(

VALUES(‘DineshMandal’, ‘dinesh.kumar.mandal@hotmail.com’,‘Software Engineer’, ‘9899144254’, 28),

(‘RavindraGhimire’, ‘ravindra@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),

(‘RosanSapkota’, ‘rosan@who.com’, ‘Social Activist’, ‘9891234560’, 30),

(‘HariAdhikari’, ‘hari@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)

) as EmployeeDetails(Name, Email, Profession, ContactNo, Age)

WHERE Age <= 30;

Go

See the VALUES clause in parenthesis and seperated by comma. This is what we call “Row Constructor”. In example 2, see the derived table name EmployeeDetails and its column names inside the parenthesis. And of course the list of column names we like to select out of VALUES parameter values. These are few things we should take care when using row constructor.

Result of Example 2:

I think this feature is really praiseworthy.