Dot Net Thoughts

Today, Aneesh (the guy who blogged about SQL Encryption few months back in dot net thoughts), told me about the Multiple Active Result Sets (MARS), a new option available in SQL Server 2005, which helps developers to maintain Multiple active Statements on a connection. When using SQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

The MARS feature is disabled by default. It can be enabled by adding the “MultipleActiveResultSets=True” keyword pair to your connection string, as below:

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=True";

Without MARS

string sql1 = "SELECT * FROM gotMenus";
string sql2 = "SELECT * FROM gotUsers";

using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;  Initial Catalog=GotCms;Integrated Security=SSPI;"))
{
conn.Open();
using (SqlCommand cmd1 = new SqlCommand(sql1, conn))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
}

using (SqlCommand cmd2 = new SqlCommand(sql2, conn))
{
SqlDataReader dr2 = cmd2.ExecuteReader();
}
}

The above code will throw an invalid operation exception(There is already an open DataReader associated with this Command which must be closed first),unless you close the dr1 using dr1.Close();

Or you can modify the connection string like the below.

With MARS

string sql1 = "SELECT * FROM gotMenus";
string sql2 = "SELECT * FROM gotUsers";

using(SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;  Initial Catalog=GotCms;Integrated Security=SSPI;MultipleActiveResultSets=True"))
{
conn.Open();
using (SqlCommand cmd1 = new SqlCommand(sql1, conn))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
}

using (SqlCommand cmd2 = new SqlCommand(sql2, conn))
{
SqlDataReader dr2 = cmd2.ExecuteReader();
}
}

It will work fine without any issue.

You can get more detailed information about MARS from these links

http://technet.microsoft.com/en-us/library/ms345109
(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms131686.aspx

Tip : The server connection attribute, used in SQL Server connection string is optional. Only Database name and Security attributes required as mandatory for establishing a connection to SQL Server.

Few months back, one of my colleague from UI team, introduced JQuery to me, our purpose was to show some nice dialog boxes using JQuery. After some time I moved in to some Windows projects. And now I am back in web development, few days back I got a task to implement a Auto suggest textbox for US Cities. Initialy I was planning to implement it via ASP.Net AJAX, then I thought about simple
implementations. After some googling I found one solution, I modified the code slightly, and implemented it in a Custom user control.

Here is the C# custom control (AutoSuggestTextBox) code.

[DefaultProperty("Text")]
[ToolboxData("<{0}:AutoSuggestTextBox runat=server></{0}:AutoSuggestTextBox>")]
public class AutoSuggestTextBox : WebControl
{
protected override void RenderContents(HtmlTextWriter output)
output.WriteLine("<script type=\"text/javascript\">");
output.WriteLine("$().ready(function() {");
output.WriteLine(string.Format("$(\"#{0}TextControl\").autocomplete(\"{0}.ashx\");", this.ID));
output.WriteLine("});</script>");
output.WriteLine(string.Format("<input type=\"text\" id=\"{0}TextControl\" name=\"{0}TextControl\" />", this.ID));
}
}

In the implementation, I need to give the output from the database as string, so I implemented the code in a asp.net handler(ASHX) file. And in the ProcessRequest method, I wrote the following code.

if (!string.IsNullOrEmpty(context.Request.QueryString["q"])) // remember, 'q' is the query the autosuggest will pass
        {
            SqlDataReader reader;
            string query = string.Format("SELECT [CityName] FROM [City] WHERE [CityName] LIKE '%{0}'", context.Request.QueryString["q"].ToString());
            using (SqlConnection connection = new SqlConnection("Server=mydbserver; User Id=sa;Password=mypassword;Database=mylookups;"))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        context.Response.Write(reader["CityName"].ToString() + Environment.NewLine);
                    }
                    reader.Close();
                }
                connection.Close();
            }
        }

Note: I am not promising the above code is optimised. [:)]

And the ASPX Page you need to add reference to following JS Files and Style.

    <script type="text/javascript" language="javascript" src="jquery.js"></script>
    <script src="jquery.autocomplete.js" type="text/javascript"></script>
    <style type="text/css">
        .ac_odd {background:#dddddd;}
        .ac_results { padding: 0px; border: 1px solid black; background-color: white; overflow: hidden; z-index: 99999; }
        .ac_results ul { width: 100%; list-style-position: outside; list-style: none; padding: 0; margin: 0; }
        .ac_results li { margin: 0px; padding: 2px 5px; cursor: default; display: block; font: menu; font-size:12px; line-height: 16px; overflow: hidden; }
        .ac_loading { background:url(loading.jpg) right no-repeat; }
        .ac_over { background-color: #0A246A; color: white; }
    </style>

We have completed almost everything, then compile the project, drag the Custom control from toolbox to ASPX page, where you refering the JS Files and CSS. The ASHX File name should be the name of the control. Like if the control’s name is AutoSuggestCity, the ASHX Filename should be AutoSuggestCity.ashx. Please modify the control code if you want to change it.

After running you will get a Textbox, type something there, you will get the suggestions. Here is the one I got.

You can download JQuery from JQuery website. – www.jquery.com. And autosuggest plugin from AutoSuggest plugin page.

Are you tired of using the command-line version of STSADM? Then, download STSADMWin, the GUI for the powerful command line utility STSADM, from here .
I found STSADMWin a very helpful tool. It lists all the STSADM commands in dropdown, and as you select a command, all the parameters for the command show up. You won’t miss any, even if you want to. Drop the downloaded file to \12\BIN directory and run the executable.
Rock it!

Thanks to my colleague Meera for this useful information.