SQL DML Trigger

Sometimes DML trigger will save you from LOT of manual coding. I had a Situation where I had to save the row to another table before I change the row in original table, the situation was kind of auditing feature on database table. So I used the following DML trigger to make my work done.

---- create a temporary table
--CREATE TABLE project_maintenance_log (
--pj_id  int not null, 
--log_date DATETIME, 
--pj_Start DateTime,
--pj_End DateTime,
--pj_active bit );


ALTER TRIGGER pmaintenanceTrigger
ON project_maintenance -- create a trigger
  AFTER UPDATE 
  AS
  --WHEN (OLD.pj_active < 8000)
	BEGIN
	     INSERT INTO project_maintenance_log (pj_id, log_date,pj_Start, pj_End, pj_active)
		 --VALUES (Inserted.pj_id, getdate(), Inserted.pj_Start,Inserted.pj_End, Inserted.pj_active)
		 Select pj_id, getdate(), pj_startdate,pj_enddate, pj_active FROM Deleted
		 
	END;

Recursive function to populate treeview

I found the following recursive function which populate treeview in one go. I implemented it on my “Empresso” project and it makes my 500 line previous function down to 100 lines and also increase a lot of performance…wow, So I thought I should keep it on my blog so that I can implement it later on any project if needed.

  private void BindData()
	        {
	         

                string connection = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;
                SqlConnection DBConn = new SqlConnection(connection);


                //Create DataAdaptor for Customers and Orders
                //Table and fetch record from the Database

                //Fetching data from 1 datatable in self referencing manner

                string sqlString = @"Select D.FolderID,isnull(D.ParentID,0) as ParentID,D.FolderName
                                  from DOC_TB_FolderPermission P
                                  INNER JOIN DOC_TB_PrmDocFolder D ON P.FolderID = D.FolderID 
                                  Where P.COMPANY_ID='" + strCompanyID + @"' AND P.UserID='" + strUserID + @"'";

                SqlDataAdapter DBCustAdap = new SqlDataAdapter(sqlString, DBConn);
    

                //Create DataSet object and fill DataAdapter

                DataSet DSCustOrder = new DataSet();

                DBCustAdap.Fill(DSCustOrder, "Parent");

	            TreeNode treeNode = new TreeNode();

                // Row[0] contains the root node
                treeNode.Text = DSCustOrder.Tables[0].Rows[0]["FolderName"].ToString();
                treeNode.Value = DSCustOrder.Tables[0].Rows[0]["FolderID"].ToString();

                Session.Add("directory", DSCustOrder.Tables[0]);
            
                BuildDirectoryTree(treeNode.Value, treeNode);  //Calling the recursive function

                this.tvwDocFolder.Nodes.Add(treeNode);
                this.tvwDocFolder.DataBind();

                Session.Remove("directory");
	        }



 private void BuildDirectoryTree(string folderID, TreeNode treeNode)
	        {
                if (String.IsNullOrEmpty(folderID)) return;
	 
	            TreeNode tnAdd = null;

               
                DataTable dt = (DataTable)Session["directory"];

                DataRow[] drArray = dt.Select("ParentID=" + folderID + "");

	            foreach(DataRow dr in drArray)
	            {
	                tnAdd = new TreeNode();
                    tnAdd.Text = dr["FolderName"].ToString();
                    tnAdd.Value = dr["FolderID"].ToString();

                    BuildDirectoryTree(tnAdd.Value, tnAdd);
	 
	                if(tnAdd != null)
	                {
	                    treeNode.ChildNodes.Add(tnAdd);
	                }
	            }
	        }

DDL Trigger to keep database change history

This trigger will act like version control on your database, After each release you have to set a Release Label, before release you can query by Release Label and date descending to populate all the changes done

/****** Object: Table [dbo].[dbChangeLog] Script Date: 06/16/2010 17:32:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[dbChangeLog](
[AutoID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NULL,
[EventType] [varchar](90) NULL,
[LoginName] [varchar](50) NULL,
[UserName] [varchar](50) NULL,
[DatabaseName] [varchar](50) NULL,
[SchemaName] [varchar](80) NULL,
[ObjectName] [varchar](90) NULL,
[ObjectType] [varchar](90) NULL,
[TSQLCommand] [nvarchar](max) NULL,
[DbVersion] [varchar](50) NULL,
[MostRecent] bit NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

and the Trigger should be something like this

/****** Object:  DdlTrigger [DDL_on_Table_and_PROC]    Script Date: 06/16/2010 17:34:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE TRIGGER [DDL_on_Table_and_PROC]
ON DATABASE 
FOR CREATE_TABLE,DROP_TABLE, 
  ALTER_TABLE,CREATE_PROCEDURE,
  ALTER_PROCEDURE,DROP_PROCEDURE
AS 
BEGIN


declare	@CreatedDate datetime
declare @EventType  varchar(50)
declare @LoginName	varchar(50)
declare @UserName varchar(50)
declare @DatabaseName varchar(50)
declare @SchemaName varchar(150)
declare @ObjectName varchar(150)
declare @ObjectType  varchar(50)
declare @CommandText nvarchar(max);



set @EventType=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/EventType)[1]',
  'varchar(50)'));

set @LoginName=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/LoginName)[1]',
  'varchar(100)'));

set @UserName=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/UserName)[1]',
  'varchar(100)'));

set @DatabaseName=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/DatabaseName)[1]',
  'nvarchar(150)'));

set @SchemaName=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/SchemaName)[1]',
  'varchar(150)'));

set @ObjectName=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/ObjectName)[1]',
  'varchar(100)'));


set @ObjectType=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/ObjectType)[1]',
  'varchar(50)'));


set @CommandText=(SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
  'nvarchar(max)'));

--set @statusReport=(SELECT convert(varchar(500),EVENTDATA()));

--
--  
--select @CommandText 'TSQL Coomand'
--select @objectType 'Table/Stored Proc'
--select @databaseName 'database Name'
----select @EventType '@EventType Name'
--select @ObjectName '@Object Name'

----------------------------------------------------------------------------------------------------
-- This will update all the old entry so that before release you can get all the updates procedure and table
------------------------------------------------------------------------------------------------------
UPDATE dbChangeLog  
SET MostRecent = 0
WHERE ObjectType = @ObjectType
AND ObjectName =  @ObjectName
AND DbVersion = 'Rel-D'


----------------------------------------------------------------------------------------------------
-- This will Insert a new Row in Log Table
------------------------------------------------------------------------------------------------------
INSERT INTO [dbChangeLog]
           ([CreatedDate],[EventType]
           ,[LoginName]
           ,[UserName]
           ,[DatabaseName]
           ,[SchemaName]
           ,[ObjectName]
           ,[ObjectType]
           ,[TSQLCommand]
		   ,[DbVersion]
		   ,[MostRecent])
     VALUES
           (GETDATE(),@EventType
           ,@LoginName
           ,@UserName
           ,@DatabaseName
           ,@SchemaName
           ,@ObjectName
           ,@ObjectType
           ,@CommandText
			,'Rel-D'
			,1)

END



GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [DDL_on_Table_and_PROC] ON DATABASE
GO

ENABLE TRIGGER [DDL_on_Table_and_PROC] ON DATABASE
GO

I hope you don’t need the select command which will populate the most updated changed on database. In case you can’t make the query let me know.

Google like search in asp.net textbox

When you will search on google, to search exact text on any page you have to write like “search text”. This will populate all the pages which have Search + text side by side. If you type – search text – only(without enclosing the text with semicolon ) and press search button it will show you the result of pages which have search and/or text on pages. I had to do the same functionality in one of my project. The logic is easy, I am writing the post so that if I have to do the same work again I don’t have to re think the logic. Here is the code

 string like_string = "";
        String[] likeArray = new String[10] { "", "", "", "", "", "", "", "", "", "" };
        string likeDescription =like.Value.Trim();
        char[] cAnd = new char[]{'A','N','D'};
        char[] cOR = new char[]{'O','R'};

        if (likeDescription.Contains("\""))
        {
            like_string = likeDescription.Substring(1, likeDescription.Length - 2);          
            //like_string = likeDescription.Replace("'", "''");
            like_string = like_string.Replace("[", "[[]");
            like_string = like_string.Replace("%", "[%]");
            like_string = like_string.Replace("_", "[_]");
        }
        else
        {
            like_string = likeDescription.Replace("'", "''");
            like_string = like_string.Replace("[", "[[]");
            like_string = like_string.Replace("%", "[%]");
            like_string = like_string.Replace("_", "[_]");

            likeArray = like_string.Split(' ');

        }

        string desc_clause = "";
        if (likeDescription.Contains("\""))
        {
            desc_clause = " bg_short_desc like";
            desc_clause += " N'%" + like_string + "%'";
        }
        else
        {
                   

            foreach (string word in likeArray)
            {
                desc_clause += " bg_short_desc like";    
                desc_clause += " N'%" + word + "%' OR";
            }
        }

        desc_clause = desc_clause.TrimEnd(cOR);
        desc_clause = desc_clause + " \n";

It can be optimized more, but I didn’t have the time for it. So if you Implement the code, please optimize few line. After formatting the desc_clause string I have used that on a SQL query to return the result set.

Thank you

Bandwidth simulation for web testing

Following are few wonderful tool for web simulation and test you web site/application under lower bandwidth. This will help developer to tune the page and make loading faster, specially for the client browsing using lower speed/bandwidth.Simulator home

Personally I like sloppy, its a small tool but will do your work perfectly.You can download sloppy from clicking on the link. Download Sloppy

Once you are done downloading sloppy, You need JVM to run sloppy on windows pc. To run your site you have to put url and select speed. Then start your testing in selected bandwidth.

For Number of various testing tool visit: Testing tool

Post asp.net form inside thickbox

To substitute ajax modal pop-up, I started to use jQuery thickbox which is a nice alternative to the ModalPopup extender. But one of the problem I faced then submit button was not working inside a thickbox pop-up. Suppose you have a textbox and a submit button inside thickbox. When you click the button to submit the form, you will find out that the form was not submitting. I used to solve this problem by creating 1 extra page iniside TB_IFAME, and calling that, but it can be easily solve if you add OnClientClick attribute to your asp button and paste the javaScript needed.

OnClientClick="doPostBack(this);"

and the following javaScript will do the submit work


function doPostBack(element) {
tb_remove();
setTimeout('__doPostBack(\'' + element.name + '\',\'\')', 500);
}


Reference: http://mwtech.blogspot.com/2009/05/using-thickbox-with-server-side-buttons.html

Using jQuery UI Autocomplete with asp.net ashx file

I was trying to implement autocomplete textbox for long. I even implemented few using jQuery and ajaxControlToolkit. I didn’t like the ajaxToolkit autocomplete extender. Specially when you will need to get id on selected text, the format was not soothing. So I was experimenting with other autocomplete, finally I liked the jQueryUI one. It is easy to implement and feature reach. I am always a big fan of jQueryUI and the simplicity of jQuery syntext. To implement that autocomplete you will need the following thing.

1. ASP.NET website/web application project.
2. Download jqueryUI 1.8.2 from their website with jQuery.

Now,Lets start building.
Add a new page to your project. and reference your jQuery files.[note: go to jQueryui.com site and see examples of how to add reference of those files, if you can not set reference]

Paste the following code in your page head section. If you are using contenet page, paste the code in content page.

     $(function() {
            function log(message) {
                $("
").text(message).prependTo("#log");
                $("#log").attr("scrollTop", 0);
                alert(message);
                //fetchPartyInformation(message);
            }

            $(".partyName").autocomplete({
                // TODO doesn't work when loaded from /demos/#autocomplete|remote
                source: "WebService/PartyInfo.ashx",
                minLength: 1,
                select: function(event, ui) {
                    //log(ui.item ? ("Selected: " + ui.item.value + " aka " + ui.item.id) : "Nothing selected, input was " + this.value);
                    dateParseandAdd(ui.item.id);
                    //alert(ui.item.id);
                }
            });
        });

Add a textbox and give cssClass name to “partyName”.[You can set anything you want, then change the partyName class name that is referenced by autocomplete.]if you are not familiar with jQuery selector go to jQuery Tutorial and pick anyone you like. Those are short tutorial and you will learn jQuery basic in an hour.

I put my .ashx file in webService directory. Following code is for my .asmx file. Which has SqlCacheDepency used[I will cover SqlCacheDependency in my next post]. The beauty of this code is, it will go to database only once to fetch all Party name and will cache it. It will search the typed character for match on datatable[Cache/Non-Cached], and send the filtered dataTable to a function which will generate response text for autocomplete. The autocomplete need 3 column “id, label,value” for mapping. So I have to change the original column name to id,label,column on last line of GetJSONString() function before sending that back to response[calling function].Following is the code.

using System;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Data;


public class PartyInfo : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
       
        //-------------------- =Declaration Section ----------------------------
        context.Response.ContentType = "text/plain";
        string returnText = "";
        string prefixText = context.Request.QueryString["term"];
        DataTable dt = new DataTable();


        //-------------------- =Cache Checking ---------------------------------
        if (context.Cache["partyname"] != null)
        {
            dt = (DataTable)context.Cache["partyname"];

        }
        else
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "  SELECT Convert(varchar,DirID)+'-'+isnull(Convert(varchar,CreditPeriod),'') as DirID, FirstName +' '+isnull(MiddleName,'') +' '+ isnull(LastName,'') as NameVal, FirstName +' '+isnull(MiddleName,'') +' '+ isnull(LastName,'') as NameLabel " +
                                        " FROM dbo.[Dir_Info]";
                    //cmd.Parameters.AddWithValue("@SearchText", prefixText);
                    cmd.Connection = conn;
                    //StringBuilder sb = new StringBuilder();

                    conn.Open();
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))                    
                    {
                            sda.Fill(dt);    
                            System.Web.Caching.SqlCacheDependency sqlDep = new System.Web.Caching.SqlCacheDependency(cmd);                            
                            context.Cache.Insert("partyname", dt, sqlDep);
                    }
                    //dt.Dispose();
                    conn.Close();

                }
            }

        }


      
        //---------------- =It can be filled by cache or from database ---------------------
        if (dt.Rows.Count > 0)
        {
            DataTable dtnew = dt.Clone();
            dtnew.Clear();
            //string expression="NameVal ="
            DataRow[] drArray = dt.Select(" NameVal like '" + prefixText + "%'");
            foreach (DataRow dr in drArray)
            {
                DataRow drnewrow = dtnew.NewRow();
                drnewrow["DirID"] = dr["DirID"];
                drnewrow["NameVal"] = dr["NameVal"];
                drnewrow["NameLabel"] = dr["NameLabel"];
                dtnew.Rows.Add(drnewrow);
            }
            returnText = GetJSONString(dtnew);
        }
        else
        {
            returnText = "";
        }

        dt.Dispose();
        //------------------- =Sending data to caller ------------------
        context.Response.Write(returnText);

    }










    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    
    
    
    


    public static string GetJSONString(DataTable Dt)
    {

        string[] StrDc = new string[Dt.Columns.Count];

        string HeadStr = string.Empty;
        for (int i = 0; i < Dt.Columns.Count; i++)
        {

            StrDc[i] = Dt.Columns[i].Caption;
            HeadStr += "\"" + StrDc[i] + "\" : \"" + StrDc[i] + i.ToString() + "¾" + "\",";

        }

        //[ { "id": "Upupa epops", "label": "Eurasian Hoopoe", "value": "Eurasian Hoopoe" }, { "id": "Jynx torquilla", "label": "Eurasian Wryneck", "value": "Eurasian Wryneck" }, { "id": "Ficedula hypoleuca", "label":

        HeadStr = HeadStr.Substring(0, HeadStr.Length - 1);
        StringBuilder Sb = new StringBuilder();

        //Sb.Append("{\"" + Dt.TableName + "\" : [");
        Sb.Append("[");
        for (int i = 0; i < Dt.Rows.Count; i++)
        {

            string TempStr = HeadStr;

            Sb.Append("{");
            for (int j = 0; j < Dt.Columns.Count; j++)
            {

                TempStr = TempStr.Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[i][j].ToString());

            }
            Sb.Append(TempStr + "},");

        }

        Sb = new StringBuilder(Sb.ToString().Substring(0, Sb.ToString().Length - 1));

        Sb.Append("]");

        string jqText = Sb.ToString();
        jqText = jqText.Replace("DirID", "id");
        jqText = jqText.Replace("NameLabel", "label");
        jqText = jqText.Replace("NameVal", "value");

        return jqText;

    }


}

I was not allowed to use LINQ or ToJSON(), so I had to convert my datatable to JSON format string. I got the GetJosnString function in Navid Akter blog DataTable conversion to JSON.
Thanks to him for creating this excellent datatable to JSON converter function. It took me almost 6/7 days to find this function which served my purpose once. So I used that same function with little modification. You will get an excellent looking autocomplete drodown in output. And with changing of jQueryUI theme the look will change(Cool hah!!!). Selecting an item from list will give you the id also(That’s what I need:). I will talk about SqlCacheDependency on my next post. Feel free to ask me anything about this post.

Custom error handeling inside updatepanel

I just saw a video tutorial on how to handle custom error inside updatepanel. Video showed 3 way to handle error. Following is the link of that video

http://www.asp.net/learn/ajax-videos/video-9184.aspx

The code snippets to handle this error from javaScript file is below:


function pageLoad()
{
Sys.WebForms.PageRequestManager.getInstance().add_endRequest(onEndRequest)
}
function OnEndRequest(sender, args) {
var error = args.get_error();
if (error != undefined) {
var label = $get('');
label.outerText = error.message;
args.set_errorHandled(true);
}

I am just saving this so that in the future it will save some of my time if I need something like this.

Using jQGrid with ado.net in asp.net

I made this post before, but as Firefox update the previous version of code was not working, so I had to change few thing. To work with jQGrid you need to implement the following code. First Take reference of js and stylesheet file in your code.

<script src=”../jqGrid/jquery.js” type=”text/javascript”></script>
<link rel=”stylesheet” type=”text/css” media=”screen” href=”../jqGrid/themes/coffee/grid.css” />
<script src=”../jqGrid/jquery.jqGrid.js” type=”text/javascript”></script>

If you use asp.net webservice calling method you have to paste the following code in your head section


function populateJSONData() {
var icboMachineName = document.getElementById("cboMachineName").selectedIndex;
var j_icboMachineName = document.getElementById("cboMachineName").options[icboMachineName].value;
//alert(j_icboMachineName);
WebServiceJSONTest.GridViewJSON(j_icboMachineName, ajaxSuccess,ajaxFailure);
return false;
}


This will call the ajaxSuccess function with the json String returned from code behind

Static DataTable used with cache

I often need to use static DataTable. Mostly for caching purpose. It has huge advantage. Following is the code that I use.

 private DataTable CreateDataTable()
    {
        DataTable myDataTable = new DataTable();

        DataColumn myDataColumn;

        myDataColumn = new DataColumn();
        myDataColumn.DataType = Type.GetType("System.String");
        myDataColumn.ColumnName = "id";
        myDataTable.Columns.Add(myDataColumn);

        myDataColumn = new DataColumn();
        myDataColumn.DataType = Type.GetType("System.String");
        myDataColumn.ColumnName = "itemname";
        myDataTable.Columns.Add(myDataColumn);

        myDataColumn = new DataColumn();
        myDataColumn.DataType = Type.GetType("System.String");
        myDataColumn.ColumnName = "itemstock";
        myDataTable.Columns.Add(myDataColumn);

        myDataColumn = new DataColumn();
        myDataColumn.DataType = Type.GetType("System.String");
        myDataColumn.ColumnName = "itemquantity";
        myDataTable.Columns.Add(myDataColumn);

        return myDataTable;
    }