Tuesday, May 8, 2012

How to pass multiple records to stored procedure in Asp.net?

There are mainly three ways you can pass multiple records to stored procedure.
  • Comma delimited string:  It is suitable for the scenario where you need to pass single column value
  • XML document : You need to support SQL Server 2005 and you’ve multiple column values.
  • Table valued parameter : You’ve SQL Server 2008 and you don’t want to get into parsing stuff at the backend and want the best performance.
To know what is table valued parameter and how to pass datatable to stored procedure and implement upsert (update + insert) logic using Merge Statement, check out an old post here.

We’ll see in this post how you can pass XML document to stored procedure and parse it in stored procedure and insert into table.

First of all let’s check out how you can generate XML Document in Asp.net using XmlSerializer.

Let’s say we’ve list of Person and we want to serealize it to xml then do as below:
List<Person> people = new List<Person> {
     new Person { FirstName = "Scott", LastName = "Guthrie", Age = 32 },
     new Person { FirstName = "Bill", LastName = "Gates", Age = 50 },
     new Person { FirstName = "Susanne", LastName = "Guthrie", Age = 32 }
};

string strXML = SerializeObject<List<Person>>(people);
And the function that serialize the object to XML is as below :
public string SerializeObject<T>(T Obj)
{
    string strxml = string.Empty;
    using (StringWriter sw = new StringWriter())
    {
        XmlSerializer xs = new XmlSerializer(typeof(T));
        xs.Serialize(sw, Obj);
        strxml = sw.ToString();
    }
    return strxml;
}
And the resultant XML would be as below :
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfPerson xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Person>
<FirstName>Scott</FirstName>
<LastName>Guthrie</LastName>
<Age>32</Age>
</Person>
<Person>
<FirstName>Bill</FirstName>
<LastName>Gates</LastName>
<Age>50</Age>
</Person>
<Person>
<FirstName>Susanne</FirstName>
<LastName>Guthrie</LastName>
<Age>32</Age>
</Person>
</ArrayOfPerson>

Generated XML is having encoding UTF-16. Now pass above xml to your stored procedure as shown below:
string connectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["XMLDBConnString"]);          

using (SqlConnection cn = new SqlConnection(connectionString))
{
    using (SqlCommand cm = new SqlCommand("SaveUser", cn))
    {
        cm.CommandType = CommandType.StoredProcedure;

        cm.Parameters.Add("@UsersXml", SqlDbType.NVarChar, -1).Value = strXML;
        
        cn.Open();
        
        cm.ExecuteNonQuery();
    }
}
If you see the parameter datatype, it is Nvarchar as generated XML is having encoding UTF-16. If you need to support Unicode characters then you don’t need to change anything or you need to encode to UTF-8, then you can use UTF8StringWriter class  shown below:
public class Utf8StringWriter : StringWriter
{
    public override Encoding Encoding
    {
        get { return Encoding.UTF8; }
    }
}
Note : If you pass XML having enconding UTF-16 with varchar datatype to  stored procedure, it will throw error : unable to switch the encoding

To avoid error either use Utf8StringWriter class instead of StringWriter and use varchar datatype or use nvarchar datatype and pass xml with encoding UTF-16.

Now to parse generated element centric xml we need to use Xquery. If you’re new to Xquery, I would suggest you go through below links or you can skip them if you wish.

Xpath Syntax
Get Table From XML

Check out sample stored procedure :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SaveUser]
    @UsersXml AS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @XML AS XML
       
    SELECT @XML = @UsersXml
    
    INSERT INTO Person (FirstName, LastName, Age)      
    select M.Item.value('FirstName[1]','VARCHAR(50)'),
    M.Item.value('LastName[1]','Varchar(50)'),
    M.Item.value('Age[1]','INT')
    FROM @xml.nodes('/ArrayOfPerson/Person') AS M(Item)
END
Note : XQuery is case-sensitive, hence check your tsql statements for typo if you don’t get expected output.

That’s all you need, I hope it helps! Your comments are most welcome. :)

Sunday, March 4, 2012

Edit, share, execute and debug code within browser - make a fiddle

Many times we want to execute some ideas with JavaScript/Jquery and CSS to see whether we are on the right track?  We want to share HTML, CSS and JavaScript/Jquery code with others and allow them to run it, specially on the forums. If so, make a fiddle. It not only solves problem of sharing but also #9 mentioned in Mike’s post here.

JSFiddle, an online editor for web snippets.

Rather than posting entirety of all the files into forum post, just do the following :
  1. Go to JSFiddle (better to bookmark it if you can not remember it)
  2. Add your code to appropriate section : HTML, CSS, JavaScript
  3. Choose JS Framework if you’re using any.
  4. Press run and try to reproduce the problem you’re facing.
  5. Save to get a public URL.
  6. Share the URL on respective forums.
As Mike said in his post, it should have *relevant code* (as little code as possible, which reproduce the issue). Most of the time doing this, you’ll solve the problem yourself. ;) And If not, it will help a lot to people who’re going to help you. :)

It’s absolutely not necessary to use JSFiddle, you can use JS Bin, CSSDesk etc whatever you’re comfortable with.

For SQL related issue, you can use SQLFiddle (Courtesy : Phil_Factor’s tweet), a tool for database developers to test out their queries.If you do not know SQL or basic database concepts, this site is not going to be very useful to you. In case you’re wondering in what cases it can help you out and how to use it, then you should read this. Check out the sample fiddle here.

Thanks to developers of JSFiddle and SQLFiddle, it’s definitely a solution of sharing a code on public forums and able to run it.

Monday, February 13, 2012

Tip: Schema qualify database objects

When working in a team, it may happen that few of the team mates follow best practices and others don’t give damn about it. They just completes the tasks on the name of ‘getting things done’. It may create nightmares if all the team members don’t follow the best practices. Either do it or don’t.

Let’s see what can happen with below example.

In one of the project, an SP was created with prefix .dbo and it was working fine until changes came. Now one of the team member made changes and whole team had the feeling “all is well” until that SP was not returning the expected output. So you start debugging your app and consider the following could be the issue.
  • Being a big Ajax app, you’re getting some Ajax error
  • You’re not passing appropriate parameter and you check it through your code and SQL profiler whether parameters are passed in appropriate manner and with correct values.
  • Check your respective tables for the values.
  • You’re executing SP from SSMS it self to see whether it is working by passing the required parameter values.
You delete the SP and see whether it is returning any value and yes, it is. Later on you found out that SP has no prefix and it is executed to default schema name other than .dbo. So there’re two SPs with different schema name. Great!

Let’s say you’re qualifying schema name for your SP but nothing for query inside that SP, it will default to the schema name of the stored procedure. What books online says about it :
Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not schema-qualified default to the schema of the stored procedure. If a user who creates a stored procedure does not qualify the name of the tables or views referenced in SELECT, INSERT, UPDATE, or DELETE statements within the stored procedure, access to those tables through the stored procedure is restricted by default to the creator of the procedure.
Make sure you always qualify your database objects with specific schema name or you’re life is doomed.

Monday, January 16, 2012

Specify size for character data type

Recently I was referring books online to confirm one strange behavior in SQL Server. It was like in one of the legacy application, string was passed to stored procedure as parameter and it was storing only 30 characters. To my surprise when I saw the code, everything seem to be ok. Later on found out the problem and fault was while converting parameter to varchar there was no size defined.
As per books online,
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
Check out the below script which will return only first character of parameter being passed instead of parameter value :
CREATE PROCEDURE GetEmployeeName
    @Name varchar
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Name
END
GO
Now if you define the parameter length, but using cast or convert function, it will return first 30 characters.
ALTER PROCEDURE GetEmployeeName
    @Name varchar(40)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Convert(varchar,@Name) as Name
END
GO
--The longest one-word name of Hawaiian boy
exec GetEmployeeName Kananinoheaokuuhomeopuukaimanaalohilo
Make sure length is defined for all stored procedure parameters and variables to avoid unexpected behavior.
Source : Books Online

Monday, January 2, 2012

How to display message box in Asp.net?

Every new beginner from windows form development background uses MsgBox.Show method but unfortunately it executes server side and will display the message box on server. Then they ask it on different forums seeking help, how to display message box in Asp.net then? (one of the most frequent and common question on asp.net forum)
There are various ways to do it.
  • Use JavaScript alert to display messagebox, confirm and prompt
  • Download alert control from codeplex and use it.
  • Use Modal Popup or Confirm Button from Ajax Toolkit
  • Use Jquery UI Dialog
You can use any Jquery based dialog box. I’ve been using Jalert for quite a long time. It is easy to use and you can quickly customize as per the need. One of the best part of Jalert is that it allows you to perform certain activity by using Callback function. Before using Jalert, make sure you’ve referenced Jquery in your project.

Note: Jalert is no longer actively maintained and they recommend to use Jquery UI Dialog.

Now, how do you display it from server side?

You can use following code in your class file under App_Code folder or You can include it in Base Class, being one of the useful feature as suggested by Scott Mitchell here

public enum EnmMsgType
{
      ALERT,
      PROMPT,
      SUCCESS,
      ERROR,
      WARNING
}

/// <summary>
/// Displays Message box 
/// </summary>
/// <param name="strMsg">Message to be displayed</param>
/// <param name="strTitle">Title of the message</param>
/// <param name="strMsgType">Type of the message to be displayed</param>
/// <param name="strCallBack">Function to be executed after message has been displayed</param>
public static void MsgBoxAlert(string strMsg, string strTitle, EnmMsgType strMsgType, string strCallBack)
{
    Page mypage = default(Page);
    string strScript = string.Empty;
   
    try
    {
        string cleanMessage = strMsg.Replace("'", "\\'");

        strScript = GetMessageBoxScript(strTitle, strCallBack, strMsgType, cleanMessage);

        //Gets currently executing page
        mypage = HttpContext.Current.CurrentHandler as Page;
        // Checks if the handler is a Page and that 
        //if the startup script is already registered.
        if (mypage != null && !mypage.ClientScript.IsStartupScriptRegistered("alert"))
        {
            mypage.ClientScript.RegisterStartupScript(mypage.GetType(), "alert", strScript);
        }
    }
    catch (Exception ex)
    {
      //Your exception handling code here
    }
}

You can name function whatever you want. As shown in code strCallBack parameter accepts name of the function which will be called in case you want to perform certain action after Messagebox or Confirmation dialog has been displayed to user and user presses Ok button. If you don’t want to call any function, just pass an empty string to function in above code.
private static string GetMessageBoxScript(string strTitle, string strCallBack, EnmMsgType MsgType, string cleanMessage)
{
    string strClass =  GetMessageboxClass(MsgType);
    StringBuilder sbMessageScript = new StringBuilder();
    sbMessageScript.Append("<script type='text/javascript'>");
    sbMessageScript.Append("$(document).ready(function(){");
    sbMessageScript.Append("jAlert('");
    sbMessageScript.Append(cleanMessage);
    sbMessageScript.Append("','");
    sbMessageScript.Append(strClass);
    sbMessageScript.Append("','");
    sbMessageScript.Append(strTitle);
    sbMessageScript.Append("'");
    if (strCallBack != string.Empty)
    {
        sbMessageScript.Append(",");
        sbMessageScript.Append(strCallBack);
    }
    sbMessageScript.Append(");});");
    sbMessageScript.Append("</script>");

    return sbMessageScript.ToString();
}

Below function will return appropriate class based on Message Type being passed. You have to have those classes added in your CSS file. As per the class respective image will be displayed in message box, just like windows message box.

/// <summary>
/// Gets the messagebox class.
/// </summary>
/// <param name="strMsgType">Type of the Message</param>
/// <returns></returns>
private static string GetMessageboxClass(EnmMsgType strMsgType)
{
    string strClass = String.Empty;

    if (strMsgType == EnmMsgType.ALERT)
    {
        strClass = "alert";
    }
    else if (strMsgType == EnmMsgType.SUCCESS)
    {
        strClass = "success";
    }
    else if (strMsgType == EnmMsgType.ERROR)
    {
        strClass = "error";
    }
    else if (strMsgType == EnmMsgType.WARNING)
    {
        strClass = "warning";
    }
    return strClass;
}

Check out the below example :

jAlert

I hope it will be helpful to every beginner from Windows Form background. If you have any query/suggestion, please comment. :)