Monday 11 April 2011

[DOT NET]Dynamic Repeater Control Binding


Below class are used for Dynamically add columns in a repeater control
public enum Alignment
    {
        Left,
        Center,
        Right
    }
    public class ReportGrid
    {
        internal Repeater oPerent
        {
            get;
            set;
        }
        public ReportGrid(ref Repeater _Repeater)
        {
            oPerent = _Repeater;
            ReportBinders = new List<ReportBinder>();
        }
        public void DataBind()
        {
            oPerent.HeaderTemplate = new RepeaterTemplate(ListItemType.Header, ReportBinders);
            oPerent.ItemTemplate = new RepeaterTemplate(ListItemType.Item, ReportBinders);
            oPerent.AlternatingItemTemplate = new RepeaterTemplate(ListItemType.AlternatingItem, ReportBinders);
            oPerent.FooterTemplate = new RepeaterTemplate(ListItemType.Footer, ReportBinders);
            oPerent.DataSource = DataSource;
            oPerent.DataBind();
        }
        public List<ReportBinder> ReportBinders
        {
            get;
            set;
        }
        public Object DataSource
        {
            get;
            set;
        }
    }
    public class ReportBinder
    {
        public ReportBinder()
        {
            Width = 100;
            Alignment = Alignment.Left;
        }
        public String DataField
        {
            get;
            set;
        }
        public String ColumnName
        {
            get;
            set;
        }
        public String DataFormat
        {
            get;
            set;
        }
        public Int32 Width
        {
            get;
            set;
        }
        public Alignment Alignment
        {
            get;
            set;
        }
    }
    public class RepeaterTemplate : System.Web.UI.ITemplate
    {
        System.Web.UI.WebControls.ListItemType templateType;
        List<ReportBinder> _ReportBinder;
        List<Label> _item1;
        public RepeaterTemplate(System.Web.UI.WebControls.ListItemType type,List<ReportBinder> ReportBinder)
        {
            templateType = type;
            _ReportBinder = ReportBinder;
        }
        public void InstantiateIn(System.Web.UI.Control container)
        {
            PlaceHolder ph = new PlaceHolder();
            _item1 = new List<Label>();
            foreach (ReportBinder rb in _ReportBinder)
            {
                Label item1 = new Label();
                item1.ID = rb.DataField;
                _item1.Add(item1);
            }

            switch (templateType)
            {
                case ListItemType.Header:
                    ph.Controls.Add(new LiteralControl("<table class=\"RepDisplay\">"));
                    ph.Controls.Add(new LiteralControl("<tr>"));
                    foreach (ReportBinder rb in _ReportBinder)
                    {
                        string Ali = "";
                        switch (rb.Alignment)
                        {
                            case Alignment.Left:
                                Ali = "text-align:left";
                                break;
                            case Alignment.Center:
                                Ali = "text-align:center";
                                break;
                            case Alignment.Right:
                                Ali = "text-align:right";
                                break;
                        }
                        ph.Controls.Add(new LiteralControl("<th style=\"width:" + rb.Width + " px;" + Ali + "\">" + rb.ColumnName + "</th>"));
                    }
                    ph.Controls.Add(new LiteralControl("</tr>"));
                    break;
                case ListItemType.Item:
                    ph.Controls.Add(new LiteralControl("<tr class=\"oddrow\">"));
                    foreach (Label bl in _item1)
                    {
                        ph.Controls.Add(new LiteralControl("<td>"));
                        ph.Controls.Add(bl);
                        ph.Controls.Add(new LiteralControl("</td>"));
                    }
                    ph.Controls.Add(new LiteralControl("</tr>"));
                    ph.DataBinding += new EventHandler(Item_DataBinding);
                    break;
                case ListItemType.AlternatingItem:
                    ph.Controls.Add(new LiteralControl("<tr class=\"evenrow\">"));
                    foreach (Label bl in _item1)
                    {
                        ph.Controls.Add(new LiteralControl("<td>"));
                        ph.Controls.Add(bl);
                        ph.Controls.Add(new LiteralControl("</td>"));
                    }
                    ph.Controls.Add(new LiteralControl("</tr>"));
                    ph.DataBinding += new EventHandler(Item_DataBinding);
                    break;
                case ListItemType.Footer:
                    ph.Controls.Add(new LiteralControl("</table>"));
                    break;
            }
            container.Controls.Add(ph);
        }
        void Item_DataBinding(object sender, System.EventArgs e)
        {
            PlaceHolder ph = (PlaceHolder)sender;
            RepeaterItem ri = (RepeaterItem)ph.NamingContainer;
            for (int i = 0; i < _ReportBinder.Count;i++ )
            {
                Object item1Value;
                if(_ReportBinder[i].DataFormat !=null && _ReportBinder[i].DataFormat!=string.Empty)
                    item1Value = (Object)DataBinder.Eval(ri.DataItem, _ReportBinder[i].DataField, _ReportBinder[i].DataFormat);
                else
                    item1Value = (Object)DataBinder.Eval(ri.DataItem, _ReportBinder[i].DataField);
                   
                ((Label)ph.FindControl(_ReportBinder[i].DataField)).Text = Convert.ToString(item1Value);
            }
            //Int32 item1Value = (Int32)DataBinder.Eval(ri.DataItem, "CategoryID");
            //String item2Value = (String)DataBinder.Eval(ri.DataItem, "CategoryName");
            //((Label)ph.FindControl("item1")).Text = item1Value.ToString();
            //((Label)ph.FindControl("item2")).Text = item2Value;
        }
    }

Uses:

<div style="overflow: scroll; height: 500px;">
                            <asp:Repeater ID="RepDisplay" runat="server">
                            </asp:Repeater>
                        </div>

In CS file:

ReportGrid oReportGrid = new ReportGrid(ref RepDisplay);
        for (int Icol = 0; Icol < dtData.Columns.Count; Icol++)
        {
            DataRow dr = fnMetadataByField(dtData.Columns[Icol].ColumnName);
            oReportGrid.ReportBinders.Add(new ReportBinder()
            {
                ColumnName = dr["DSPLNAME"].ToString(),
                DataField = dtData.Columns[Icol].ColumnName,
                Alignment = dr["DATATYPE"].ToString() == "2" ? Alignment.Right : Alignment.Left,
                DataFormat = dr["DATATYPE"].ToString() == "3" ? "{0:dd-MMM-yyyy}" : null,
                Width = Convert.ToInt32(dr["WIDTH"].ToString())
            });
        }
        oReportGrid.DataSource = pds;
        oReportGrid.DataBind();

Posted By: Mr. Palash Paul



Wednesday 6 April 2011

SQL Server Query Execution Plan Analysis

When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer. This information if very valuable when it comes time to find out why a specific query is running slow.

There are several different ways to view a query's execution plan. They include:

  • From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
  • If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
  • When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.
  • From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.

Of these options, I prefer using the "Show Execution Plan", which produces a graphical output and considers current server operations.

If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.

  • Index or table scans: May indicate a need for better or additional indexes.
  • Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
  • Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes.
  • Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer or Management Studio to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.

On the other hand, if you use a table variable instead of a temp table, you can use the "Display Estimated Execution Plan" option [7.0, 2000, 2005] Updated 8-5-2005

If you have a very complex query you are analyzing in Query Analyzer or Management Studio as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately.

The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:

  • In very complex query plans, the plan is divided into many parts, with each part listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer has to perform in order to get to the final results.
  • Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.
  • Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.
  • Eventually, all of the parts come together at the top left side of the screen.
  • If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.
  • If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.

 

Posted by: Mr. Joydeep Das

Tuesday 5 April 2011

Get full information about transaction locks

Intoduction

 

Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...

With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:

 

SELECT  L.request_session_id AS SPID,

        DB_NAME(L.resource_database_id) AS DatabaseName,

        O.Name AS LockedObjectName,

        P.object_id AS LockedObjectId,

        L.resource_type AS LockedResource,

        L.request_mode AS LockType,

        ST.text AS SqlStatementText,       

        ES.login_name AS LoginName,

        ES.host_name AS HostName,

        TST.is_user_transaction as IsUserTransaction,

        AT.name as TransactionName,

        CN.auth_scheme as AuthenticationMethod

FROM    sys.dm_tran_locks L

        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

        JOIN sys.objects O ON O.object_id = P.object_id

        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id

        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE   resource_database_id = db_id()

ORDER BY L.request_session_id

 

sys.dm_tran_locks:

Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.

sys.partitions:

Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID'  and 'Key'. With this join we get the object_id of our locked table.

sys.objects:

Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.

sys.dm_exec_sessions:

Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.

sys.dm_tran_session_transactions:

Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.

sys.dm_tran_active_transactions:

Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.

sys.dm_exec_connections:

Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.

sys.dm_exec_sql_text:

Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.

Posted By: Mr. Joydeep Das