Get details on the table contigencies

DBCC Showcontig will give a detailed insight on the data pages(OS level) for the tables.

This command will scan the table and provide the below details,

- Pages Scanned......................... - Number of pages scanned (Denotes the number of pages occupied by this table )
- Extents Scanned..............................: Number of Extents scanned (Denotes the number of Extents occupied by this table )
- Extent Switches..............................: Mixed extents
- Avg. Pages per Extent........................
- Scan Density [Best Count:Actual Count].......: How the data is compacted in the page.
- Extent Scan Fragmentation ...................: Fragmentation from Extent point of view
- Avg. Bytes Free per Page.....................
- Avg. Page Density (full) ...................... (High page density denotes the data is intact and your search will be faster. If the value is less, it indicates your data is scattered.

DBCC SHOWCONTIG --- This command will provide the details for the whole database tables.

DBCC SHOWCONTIG ('Murli_table') -- This command will provide the necessary information for that particular table.

Get Databasename from Database ID in SQL Server

DB_Name(DB_ID) will provide you the database name. The hierarchy is like system tables will have the initial values followed by user databases.

select DB_NAME(1) --- returns Master database
select DB_NAME(2)--- returns Tempdb database
select DB_NAME(3)--- returns Model database
select DB_NAME(4)--- returns MSDB database
select DB_NAME(5)--- returns User database1 etc..,

To find recently executed queries in SQL Server

To find recently executed queries,

Select dmStats.last_execution_time as 'Last Executed Time',dmText.text as 'Executed Query' from sys.dm_exec_query_stats as dmStats Cross apply sys.dm_exec_sql_text(dmStats.sql_handle) as dmText Order By
dmStats.last_execution_time desc

Update records form diff tables

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:

I.    TableA
a    b    c    d
1    x    y    z
2    a    b    c
3    t    x    z
 
II.    TableB
a1    b1    c1    d1    e1
1    x1    y1    z1    40
2    a1    b1    c1    50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:
 
a    b    c    d
————————————
1     x          y           z
2     a1         b1         c1
3     t          x          z
 

SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update:
 
a    b    c    d
————————————
1     x          y          z
2     a1         b1         c1
3     t          x          z
 

DB2 LUW:

–Same as Oracle–

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);

Results after the update:
 
a    b    c    d
————————————
1     x         y           z
2     a1        b1         c1
3     t         x         z
 

NOTE:

It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon. If it is incorrect, then you can get wrong results. The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.

In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:

a    b    c    d
————————————
1     Null      Null      Null
2     a1        b1         c1
3     Null     Null      Null 
 

Configure SQL Server to Store ASP.NET Session State

For configure the Sql Server session state you must need to follow these steps first :-
1. First you need to execute script file i.e. InstallSqlState.sql and this script file automatically create database and required procedure to stores the session values
Note:- While execution of script it gives an error like "The specified @name ('[Uncategorized (Local)]') already exists" ignore this error
Note:- This InstallSqlState.sql you can get it from your pc it's in your windows folder dir Ex : C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322

2. You need to set the connection string in your configuration file ex:-
< mode="SQLServer" sqlConnectionString="data source=INDIGO62\SQLEXPRESS2008;user id=sa;password=murli" cookieless="false" timeout="20" />

Now you can access session objects as you used in In Proc state same as use here
Ex : Session["Name"] = "Murli";

For testing the records are in available or not in your database
check two tables ASPStateTempApplications and ASPStateTempSessions
in your tempdb database

for start world wide web publishing dir type this command in command prompt
net start w3svc
for strop this
net stop w3svc

Access Viewstate from another page

Yes, we can access the viewstate variables across pages. This is only possible if Cross Page Posting or Server.transfer is used to redirect the user to other page. If Response.redirect is used, then ViewState cannot be accessed across pages.

Before you continue reading this article, please read these articles on Cross Page Posting and Server.transfer.

Ok, so all set now... I will demonstrate this using the demo created by me. You can download the demo from the link at the top of this article.

I have created two *.aspx pages named:

1. ViewStateContainer.aspx: This page sets the ViewState variable and transfers the user to another page using Server.transfer.
2. AccessViewState.aspx: This page accesses the ViewState variable of ViewStateContainer.aspx page.

This is the code of ViewStateContainer.aspx page:
?

 public partial class ViewStateContainer : System.Web.UI.Page
 {
   protected void Page_Load(object sender, EventArgs e)
     {
       ViewState["Page1"] = "Page1 ViewState";
       Server.Transfer("AccessViewState.aspx");
     }

   public StateBag ReturnViewState()
    {
      return ViewState;
     }
  }

As you can see, I have set a ViewState variable in Page Load and transfer the user to AccessViewState.aspx page using the Server.transfer() method.

This page also contains a method ReturnViewState() which actually returns the ViewState of this page to the calling function. The return type of the method is StateBag class.

StateBag class: This class is the primary storage mechanism for all HTML and Web server controls. It stores attribute/value pairs as strings associated with the control. It tracks changes to these attributes only after the OnInit method is executed for a page request, and saves the changes to the page's or control's viewstate.

Now let's take look at AccessViewState.aspx Page code:
?

public partial class AccessViewState : System.Web.UI.Page
{
  private StateBag PreviousPageViewState
   {
     get
      {
        StateBag returnValue = null;
        if (PreviousPage != null)
        {
            Object objPreviousPage = (Object)PreviousPage;
            MethodInfo objMethod = objPreviousPage.GetType().GetMethod  ("ReturnViewState");
            return (StateBag)objMethod.Invoke(objPreviousPage, null);
        }
        return returnValue;
      }
   }

  protected void Page_Load(object sender, EventArgs e)
   {
    if (PreviousPage != null)
      {
        if (PreviousPageViewState != null)
        {
            Label1.Text = PreviousPageViewState["Page1"].ToString();
        }
      }
   }
 }

Whenever we use Server.transfer or Cross Page Posting, We can get the previous page object via PreviousPage property. Using Previous Page, we can find the controls of the previous page. For example, one can access Label control placed in ViewStateContainer Page in current Page.

Looking at the code, I have created a PreviousPageViewState property in this page, which returns the previous page's ViewState. It first checks whether PreviousPage is null or not, if it's not null, then it creates an object of the previous page. Now using Reflection, we can invoke the method of the previous class. Using MethodInfo class, I have invoked the ReturnViewState() method of ViewStateContainer Page.

In Page_Load event, I am able to access the ViewState variable of ViewStateContainer Page. You can access all the viewstate variables set in ViewStateContainer Page.

Find Postback Control ID in Asp.net c#

string controlID = Page.Request.Params["__EVENTTARGET"];
Control postbackControl = null;
 if (controlID != null && controlID != String.Empty)
  {
     postbackControl = Page.FindControl(controlID);
  }
 else
  {
     foreach (string ctrl in Page.Request.Form)
      {    //Check if Image Button
        if (ctrl.EndsWith(".x") || ctrl.EndsWith(".y"))
          {
            postbackControl = Page.FindControl(ctrl.Substring(0, ctrl.Length - 2));
            break;
          }
        else
          {
            postbackControl = Page.FindControl(ctrl);
            //Check if Button control    
            if (postbackControl is Button)
               {
                  break;
               }
           }
       }
   }
 Response.Write(postbackControl.ID);