Multiple Active Result Sets - Yet another powerful feature of SQL Server 2005

MARS [Multiple Active Result Sets ] is a new SQL Server 2005 feature that allows the user to run more than one SQL batch on an open connection at the same time.

If you for instance wanted to do some processing of the data in your data reader and updating the processed data back to the database you had to use another connection object which again hurts performance. There was no way to use the same opened connection easily for more than one batch at the time. There are of course server side cursors but they have drawbacks like performance and ability to operate only on a single select statement at the time.

SQL Server 2005 team recognized the above mentioned drawback and introduced MARS. So now it is possible to use a single opened connection for more than one batch. A simple way of demonstrating MARS in action is with this code:

string strConn = "Data Source=[DATASOURCE];Initial Catalog=[DATABASE];User ID=[UID];Password=[PWD];MultipleActiveResultSets=true";
string strSql = "select DoctorId, PatientId from [Patient] where DoctorId = {0}";
string strOutput = "<br/>DoctorId:{0} - PatientId{1}";

using (SqlConnection con = new SqlConnection(strConn))
{
    //Opening Connection
    con.Open();

    //Creating two commands form current connection
    SqlCommand cmd1 = con.CreateCommand();
    SqlCommand cmd2 = con.CreateCommand();

    //Set the comment type
    cmd1.CommandType = CommandType.Text;
    cmd2.CommandType = CommandType.Text;

    //Setting the command text to first command
    cmd1.CommandText = "select distinct DoctorId from [Doctor] where HospitalId = 8";



    //Execute the first command
    IDataReader idr1 = cmd1.ExecuteReader();

    while (idr1.Read())
    {
        //Read the first doctor from data source
        int intDoctorId = idr1.GetInt32(0);

        //create another command, which get patients of doctor
        cmd2.CommandText = string.Format(strSql, intDoctorId);

        //Execute the reader
        IDataReader idr2 = cmd2.ExecuteReader();

        while (idr2.Read())
        {
            //Read the doctor and patient
            Response.Write(string.Format(strOutput, idr2.GetInt32(0), idr2.GetInt32(1)));
        }
        //Dont forgot to close second reader, this will just close reader not connection
        idr2.Close();
    }
}

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.

Anonymous Delegate!!!

Here is one more powerful use of Delegate.

Till now I familiar with simple delegate and multicast delegate. Now one more and I found very good type of Delegate that is Anonymous Delegate.

In general Anonymous delegates are just a convenient way to declare a method without naming it.
Or
Passing a method to a method by typing in the method (including the curly braces), rather than the name of a method declared somewhere else.

A delegate is something like a function pointer, you can pass it to another method (as a parameter) and execute it remotely. Usually they are just a pointer to a method defined somewhere else in the class, but in the case of anonymous ones, they are defined in place. This makes it easier, because like this you don't need to look for a name

protected override void OnInit(EventArgs e)
{
    base.OnInit(e);
    btnLogin.Click += delegate { objClass.ValidateUser(); };
}

This is simple delegate with out any parameter. The click event of Login button will be handled by function ValidateUser of some class.

Now if you want any parameter in your delegate then before open curly braces you can add it just like simple function.

protected override void OnInit(EventArgs e)
{
    base.OnInit(e);
    this.btnLogin.Click += delegate(object sender, EventArgs args) { presenter.ValidateUser(); };
}

As its function without name, you can also write your code there.

protected override void OnInit(EventArgs e)
{
    base.OnInit(e);
    rptUserList.ItemCommand += delegate(object source, RepeaterCommandEventArgs ee)
    {
        Console.Write(int.Parse(ee.CommandArgument.ToString()));
        objClass.GetDetailsById(int.Parse(ee.CommandArgument.ToString()));
                    //display the details
        Console.Write(objClass.Name);

    };
}

That is the basic idea.

Delegates and Events in C# / .NET

I come accross a good link for how Delegates and Events works in C#.

What are delegats, what is multicast-delegate handling events with delegates... all this with simple and understandable example.

Read more Delegates and Events in C# / .NET

The 25 New Features in SSMS 2008

Check it out :

The 25 New Features in SSMS 2008

SQL Server TSQL Coding standards

Hello All,

I found some good articles about SQL Procedure coding standards.

Have a look at following links

SQL Server Best Practices
SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines
XML Best Practices for Microsoft SQL Server 2005

Cannot convert type 'System.Collections.Generic.List<

I have one problem while working with Generic collection. I am returning the generic collection of base class, which at the end assigned to child class.

List<Child> childs = SelectAll();

Here is the defination of SelectAll();

public override List<MyParent> SelectAll()

While doing this I got an error....

Cannot implicitly convert 'System.Collections.Generic.List<MyParent>' to 'System.Collections.Generic.List<Child>'
Because, the returning collection is of MyParent and I need to store it into collection of Child.

Here is the solution.

Generic collection List<> provider one Generic method called ConvertAll

ConvertAll: Converts the elements in the current List<(Of <(T>)>) to another type, and returns a list containing the converted elements.

Here is the code,

List childs = SelectAll().ConvertAll<child>(ParentToChild);

and here is the ParentToChild method.

public static Child ParentToChild(MyParent myParent)
{
        return (Child)myParent;
}

Read more..

Passing lists to SQL Server 2005 with XML Parameters

We frequently have requirement like

Passing IDs in comma saperated value to the sql server for further processing.

Generally what we do is just create loop and get the value from it and ...

But SQL 2005 comes up with XQuery. Using this you can easly pass such type of data also some complex thing too.

For example...

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>' 


SELECT ParamValues.ID.value('.','VARCHAR(20)')FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Which gives us the following three rows:

3
6
15

Read more..

How to know page validity from java script

Hello All,

I was having one requirement which is as follows.

- Once you click on submit button, the button must be disabled
- All the validator should work properly then and only then button get disabled [obvious thing]
- And the page needs to be submitted back to server as well.

I was looking for the method where I can find the page valid property or value which allow me to do what I want.

There is a property Page_IsValid in java script which let me know the validity of page. But it will always set to false first time.

So I found the solution which help me to fulfill my requirement.

Have a look at following code

<script language="javascript" type="text/javascript">

function btnSaveClientClick(objBtn)
{ 
var isPageValid = Page_ClientValidate();
if(isPageValid)
{ 
objBtn.disabled = true;
__doPostBack(objBtn.name,''); 
}
}

</script>

This is the server side control [submit button]

<asp:Button ID="btnSave" runat="server" SkinID="button_plain" OnClientClick="javascript:return btnSaveClientClick(this);" Text="Save" OnClick="btnSave_Click" />

How it works:

- On client click of save button btnSaveClientClick() method get executed with single parameter this which is button itself.
- Page_ClientValidate() method used for checking client site validation, will return true or false.
- objBtn.disabled, will disable the button because objBtn is reference to our Save Button
- And last need to postback to server, so call __doPostBack(objBtn.name,'');

That's it!

Web service architecture

Hello Friends,

What is webservice? What is WSDL? How it works?? and many more....

I found all the answere, its really nice topic where you can find all the answers.

Have a look at this Web service architecture delicios.

Regular Expression problem???

Hello friends,

I found good link which has almost all required regular expresions, have a look at this Regular Expressions delicios.