drupal hit counter
Jerry Huang | apps and developing apps

Jerry Huang apps and developing apps

SqlCommand to Sql statement

18. July 2010 22:31 by Jerry in Old blog posts

Recently I have a requirement that log down the message when an error occurs on the server. If the error was caused by execution of SQL statement, log that as well.

It came to a problem that when the SQL is carried by a SqlCommand object, there is no any built-in function to get back the SQL statement in a string format. I wrote a small VB module to do the job.

The primary idea is going through the CommandText, record every place (replace index) that need to replace the parameter name (starts with “@"), and then replace them.

 

The code is written in VB.NET, but it should not be difficult to convert to C# by using http://www.developerfusion.com/tools/convert/vb-to-csharp/

Usage:

smply call SqlCommandToSql and pass in a SqlCommand object.

Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "update abcd set a=@a,b=@ab, c=@abc where cd=@a"
cmd.Parameters.Add("@a", SqlDbType.VarChar).Value = "a@aa"
cmd.Parameters.Add("@ab", SqlDbType.Bit).Value = False
cmd.Parameters.Add("@abc", SqlDbType.DateTime).Value = Now
MsgBox(SqlCommandToSql(cmd)) 

 

source code:

Imports System.Data.SqlClient

Friend Module Module1
Private Class ReplaceIndex
Implements IComparable

Public Index As Integer
Public ParaName As String
Public Sub New(ByVal idx As Integer, ByVal para As String)
Index = idx
ParaName = para
End Sub

Public Function CompareTo(ByVal obj As Object) As Integer Implements System.IComparable.CompareTo
Dim r As ReplaceIndex = DirectCast(obj, ReplaceIndex)
Return Me.Index - r.Index
End Function
End Class

Private Function GetParaValue(ByVal para As SqlParameter) As String
Dim value As String = ""
If para.Value Is Nothing Then
'it should throw an error if an parameter had not set value
'but here just make it through
value = "Nothing"
End If
If para.Value Is DBNull.Value Then
value = "NULL"
End If
value = para.Value.ToString()
'special handle for some types
Select Case para.SqlDbType
Case SqlDbType.VarChar, SqlDbType.Char, SqlDbType.Text, SqlDbType.NChar, SqlDbType.NText, SqlDbType.NVarChar
value = "'" & value.Replace("'", "''") & "'"
Case SqlDbType.Date, SqlDbType.DateTime, SqlDbType.DateTime2, SqlDbType.Time
value = "'" & value & "'"
Case SqlDbType.Binary, SqlDbType.Image, SqlDbType.VarBinary, SqlDbType.Timestamp
value = "0x" & BitConverter.ToString(TryCast(para.Value, Byte())).Replace("-", "")
Case SqlDbType.Bit
If TypeOf para.Value Is Boolean Then
value = IIf(para.Value, "1", "0")
End If
End Select
Return value
End Function

Private Sub Replace(ByRef source As String, ByVal startIdx As Integer, ByVal paraName As String, ByVal paraType As SqlDbType, ByVal value As String)
Dim len As Integer = paraName.Length
source = source.Remove(startIdx, len)
source = source.Insert(startIdx, value)

End Sub




Public Function SqlCommandToSql(ByVal cmd As SqlCommand) As String
'loop through the parameters
'record the start index where need to replace every para name
'loop the index array, replace para name one by one
'when replace one, need to adjust the rest indexs, since the string length might be changed
Dim index As New Hashtable()

Dim sql As String = cmd.CommandText

For Each p As SqlParameter In cmd.Parameters
FindIndex(index, sql, 0, p)
'index.Add(p, idx)
Next

Dim keyCollection As ICollection = index.Keys
Dim keys As ReplaceIndex() = New ReplaceIndex(keyCollection.Count - 1) {}
keyCollection.CopyTo(keys, 0)
'sort the index first
Array.Sort(keys)

Dim offset As Integer = 0
'loop through each replace point(index)
For i As Integer = 0 To keys.Length - 1
Dim r As ReplaceIndex = DirectCast(keys(i), ReplaceIndex)
Dim p As SqlParameter = DirectCast(index(r), SqlParameter)
Dim value As String = GetParaValue(p)
r.Index += offset
Replace(sql, r.Index, p.ParameterName, p.SqlDbType, value)
'calc the total offset
offset += value.Length - p.ParameterName.Length
Next

Return sql
End Function

Private Sub FindIndex(ByVal ht As Hashtable, ByVal source As String, ByVal startIdx As Integer, ByVal para As SqlParameter)
Dim idx As Integer = source.IndexOf(para.ParameterName, startIdx)
If idx < 0 Then
'no more match is found
Exit Sub
End If
Dim idx2 As Integer = source.LastIndexOf(para.ParameterName)
If idx = idx2 Then
'only one match in the remaining string
idx2 += para.ParameterName.Length
If idx2 >= source.Length Then
Dim r As New ReplaceIndex(idx, para.ParameterName)
ht.Add(r, para)
Else
Dim nextChar As String = source.Substring(idx2, 1)
'if the next char is special charater, this is the match we are looking for
If " ~!@#$%^&*()+-={}[]\|;:,./?'""".Contains(nextChar) Then
Dim r As New ReplaceIndex(idx, para.ParameterName)
ht.Add(r, para)

End If
End If

Exit Sub
End If

'more than once match the parameter name
'e.g. @a will match @a, @ab and @abc, etc.
'store each start index
Dim index As New List(Of Integer)
index.Add(idx)
'index.Add(idx2)
idx = source.IndexOf(para.ParameterName, idx + para.ParameterName.Length)
While idx <> idx2
index.Add(idx)
idx = source.IndexOf(para.ParameterName, idx + para.ParameterName.Length)
End While
'find the actual match index
For Each i As Integer In index
idx2 = i + para.ParameterName.Length
startIdx = idx2
If idx2 = source.Length Then
'this match is the last one (reach the end of source string)
Dim r As New ReplaceIndex(i, para.ParameterName)
ht.Add(r, para)
Exit Sub
Else
Dim nextChar As String = source.Substring(idx2, 1)
'if the next char is special charater, this is the match we are looking for
If " ~!@#$%^&*()+-={}[]\|;:,./?'""".Contains(nextChar) Then
Dim r As New ReplaceIndex(i, para.ParameterName)
ht.Add(r, para)

FindIndex(ht, source, startIdx, para)
Exit Sub
End If
End If
Next
FindIndex(ht, source, startIdx, para)
End Sub
End Module

How to be compatible with UpdatePanel or ASP.NET AJAX

18. July 2010 22:26 by Jerry in Old blog posts

Suppose you have following function in your project:

C# Code:

    [code language=C#]    public void MessageBox(string msgText)
        {
            string scriptKey = "Message";

            if (!ClientScript.IsStartupScriptRegistered(scriptKey))
            {
                ClientScript.RegisterStartupScript(this.GetType(), scriptKey, "<script>alert('" + msgText + "')</script>");
            }
        }[/code]

VB Code:

   [code language=vb.net] Protected Sub MessageBox(ByVal strMsg As String)

        Dim key As String = "Message"

            If Not ClientScript.IsStartupScriptRegistered(key) Then
                ClientScript.RegisterStartupScript(Me.GetType(), key, "<script>alert('" + strMsg + "');</script>")
            End If
        End If
    End Sub [/code]

This function is to show a client-side message box by using javascript. You might utilize this function like this:

C# Code:

        [code language=C#]protected void btnSearch_Click(object sender, EventArgs e)
        {

            ......code omitted
            MessageBox("Search completed!");

        }[/code]

VB Code:

  [code language=vb.net]  Protected Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        ......code omitted

        MessageBox("Search completed!")

    End Sub[/code]

The code above has no problem at all if not using AJAX. With Ajax extension however, the button "btnSearch" will be placed inside an UpdatePanel control to avoid the page being post-back.  The MessageBox will not work as normal any more afterward. In such case, you might need to do some extra work on the MessageBox method.

C# Code:

       [code language=C#] public void MessageBox(string msgText)
        {
            string scriptKey = "Message";

            if (ScriptManager.GetCurrent(this.Page)!=null && ScriptManager.GetCurrent(this.Page).IsInAsyncPostBack {

               ScriptManager.RegisterStartupScript(this, this.GetType(), scriptKey , "alert('" + msgText+ "');", true);


            }else {

               if (!ClientScript.IsStartupScriptRegistered(scriptKey))
               {
                 ClientScript.RegisterStartupScript(this.GetType(), scriptKey, "<script>alert('" + msgText + "')</script>");
               }

            }
        }[/code]

VB Code:

    [code language=vb.net]Protected Sub MessageBox(ByVal strMsg As String)

        Dim key As String = "Message"

        If ScriptManager.GetCurrent(Me.Page) IsNot Nothing AndAlso ScriptManager.GetCurrent(Me.Page).IsInAsyncPostBack Then
            'to support ajax post back
            ScriptManager.RegisterStartupScript(Me, Me.GetType(), key, "alert('" + strMsg + "');", True)
        Else
            'normal call
            If Not ClientScript.IsStartupScriptRegistered(key) Then
                ClientScript.RegisterStartupScript(Me.GetType(), key, "<script>alert('" + strMsg + "');</script>")
            End If
        End If
    End Sub[/code]

Simply use RegisterStartupScript from ScriptManager (System.Web.Extension.dll) to generate javascript code instead of that from Page object.

Hope you enjoy with ASP.NET AJAX again!

Make StaticSiteMapProvider dynamically

18. July 2010 22:18 by Jerry in Old blog posts

The term "dynamic" has 2 meanings:
1) dynamically generate menu data when user click on the menu; and
2) generate menu in per user basis.
In this paper I only discuss the second one.
 
In ASP.NET 2.0,  together with Menu or TreeView control, you can use StaticSiteMapProvider to generate a "static" hierarchical navigation menu to the end user.  Most of the StaticSiteMapProvider sub-classes look like this:
 [code language=C#]
 public class SiteMapProvider : StaticSiteMapProvider {
        private SiteMapNode root = null;
        public override SiteMapNode BuildSiteMap()
        {
            if (root != null) return root;
            lock (this)
            {
                base.Clear();
                ...generate your menu data here, e.g. get data from database
                root = new SiteMapNode(.......
            }
            return root;
        }
..............................
}//end of class[/code]
The BuildSiteMap function will run once and once only when the first visitor was opening the website, the root object will then be saved persistently and being reused during the lifetime of the web application. You may consider the root object as an application variable if you feel difficult to understand. As a result, other visitors will get exactly the same menu as the first one. That's why it named itself "Static".
 
In practice however, we are more prefer to get different menu items according to the login user's role or access right settings, rather than the stupid static items. A simple solution to make the SiteMapProvider dynamically is to discard the "root" - comment the first line of code, and that's it.
[code language=C#]        public override SiteMapNode BuildSiteMap()
        {
            //if (root != null) return root;[/code]

It works as you expected, but this is extremely inefficient; "as multiple concurrent page requests can result indirectly in multiple calls to load site map information" (i.e. to run BuildSiteMap) - the MSDN explains. Set a breakpoint inside BuildSiteMap function, you will find what MSDN said is true. Loading one single page in the web project will call BuildSiteMap many times. Alright, if that is the case, the only thing we need is a session variable to make sure every users only run the BuildSiteMap once.  The final code:

[code language=C#]public class DynamicSiteMapProvider : StaticSiteMapProvider {
//a kindly reminder that all private fields inside this class will be persistently available during the lifetime
//please remember to do extra init with these fields in the BuildSiteMap method

        private SiteMapNode root = null;
        public override SiteMapNode BuildSiteMap()
        {
            if (root != null&&HttpContext.Current.Session["AlreadySet"]!=null) return root;
            lock (this)
            {
                //next line is better to put on the first statement after "if", for concurrent reason
                HttpContext.Current.Session["AlreadySet"] = true;
                base.Clear();
                root = null;//init
                ...get "per-user" menu data here
                root = new SiteMapNode(......
 
            }
            return root;
        }
.................other code omitted....
}//end of class[/code]
 The solution above is trivial, a bit informal yet feasible. Actually SiteMapProvider has a property "securityTrimmingEnabled" to specify if the Provider equip with role. In that case, you need to override the "IsAccessibleToUser" method. More information please visit:
http://fredrik.nsquared2.com/viewpost.aspx?PostID=272&showfeedback=true
http://blogs.msdn.com/dannychen/archive/2006/03/16/553005.aspx

In order to finally solved the problem with a formal solution, we need to work with the ASP.NET 2.0 Role Manager, and Membership Provider if you want to.

First of all, define a series of roles that what menu/urls in your project they can access to, then modify your data structure to place your role information. E.g. add a "roles" field in your sitemap database table if you are using database storage. Using the following format if you are using xml file to store the sitemap:

[code language=XML] <siteMapNode title="Home" description="Home" url="~/default.aspx" roles="*">
  <siteMapNode title="menu 1" description="" roles="Admin,User" >
   <siteMapNode  roles="Admin" title="Admin Item" description="" url="~/admin.aspx" />
   <siteMapNode roles="User" title="User Item" description="" url="~/user.aspx" />
  </siteMapNode>
 </siteMapNode>[/code]

the menu will look like:
Home  (can be accessed by all users)
  - menu 1 (can be accessed by Admin and User
    - Admin Item (Can be accessed by Admin only)
    - User Item (can be accessed by User only)

*note: if you are not going to write your own xml SiteMapProvider, the ASP.NET 2.0 already has a simple one for you - System.Web.XmlSiteMapProvider. BUT, please be aware that the XmlSiteMapProvider only supports security trimming on menu/node who has child-node. In this case, if using XmlSiteMapProvider, the security trimming setting only works for "menu 1". In other word, "Admin Item" and "User Item" will be accessed by both "Admin" and "User" as "menu 1" specify.

Then, modify the Login.aspx:

[code language=C#]....After verifying the login username and password
.... and get the roleName of login user
//init the roles, clean login user's role
string[] allRoles={"Admin","User"};
foreach (string role in allRoles)
{
    if (!Roles.RoleExists(role)) Roles.CreateRole(role);
    if (Roles.IsUserInRole(user, role)) Roles.RemoveUserFromRole(user, role)
}
//add the user to the role s/he belongs to
Roles.AddUserToRole(user, roleName);[/code]

Finally, modify the BuildSiteMap method of  SiteMapProvider, and override the IsAccessibleToUser. The main difference from previous version is that in the BuildSiteMap function, instead of loading "per-user" menu data, we just load a full set of menu, leave the IsAccessibleToUser to determine if a node should be shown or access to. 

[code language=C#]public override SiteMapNode BuildSiteMap()
        {
            if (root != null) return root;

            lock (this)
            {
                base.Clear();
                ...get a full set of menu data here, but don't forget attaching role information to a node
                ... for example
                string role = .....get the roles of this menu, from database or xml, e.g "Admin,User"
                IList roles = new ArrayList();
                if (!string.IsNullOrEmpty(role)) {
                   string[] r=role.Split(",");
                   foreach (string item in r)
                       roles.Add(item)
               }
               SiteMapNode node = New SiteMapNode(this, menukey, url, title, desc, roles, null, null, null)
               ......other code omitted....
            }
            return root;
        }
public override bool IsAccessibleToUser (System.Web.HttpContext context, System.Web.SiteMapNode node)
        {
            if (!this.SecurityTrimmingEnabled) return true;
            if (node ==null || context==null || context.User==null) return false;
            if (node.Roles==null || node.Roles.Count<=0) return false;
            foreach(string role in node.Roles)
                if (role.Equals("*") || context.User.IsInRole(role))
                    return true;
            return false;
        }
 [/code]
Last step, don't forget to set SecurityTrimmingEnabled to true in web.config file.

   [code language=XML] <siteMap defaultProvider="DynamicSiteMapProvider" enabled="true">
      <providers>
        <add name="DynamicSiteMapProvider" type="myProject.DynamicSiteMapProvider"  securityTrimmingEnabled="true" />
      </providers>
    </siteMap>[/code]

Welcome to my new blog

18. July 2010 14:32 by Admin in

finally and hopefully will be settled down this time..