Archive

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.

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.

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.

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:

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!

[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.

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!

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.

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]

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.