Cross Join

SELECT DISTINCT A.UserName,Articles FROM @myTable A
CROSS APPLY
(
-- Now get all the articles for each author in XML
SELECT ArticleName + ', ' FROM @myTable B WHERE A.UserName = B.UserName
FOR XML Path('')
) AS C (Articles)

Accessing Session Value in JavaScript

< script type="text/javascript" >

function ShowName()

{

var mySessionvalue='< %= Convert.ToString(Session["name"]) % >';

document.getElementById('txtName').value = '< %= Convert.ToString(Session["name"]) % >';

}

< /script >

ACID Properties

ACID Properties

• Atomicity—The transaction is all or nothing.
• Consistency—All constraints and other data integrity rules have been adhered to, and all related objects (data pages, index pages) have been updated completely.
• Isolation—Each transaction is completely isolated from any other transaction. The actions of one transaction cannot be interfered with by the actions of a separate transaction.
• Durability—After a transaction is completed, its effects are permanently in place in the system.

Query for Max Salary and empname with managername

--Show employeename with manager name
select a.id,a.name,b.name as 'mgrname',a.salary from
emp a left join emp b on(b.id=a.mgrid)

--Nth max salary
select * from emp e1 where (N-1)=(select count(distinct e2.salary) from emp e2
where e2.salary>e1.salary)

View State for TextBoxes, CheckBoxes, DropDownLists

There is a common misunderstanding among developers that view state is somehow responsible for having TextBoxes, CheckBoxes, DropDownLists, and other Web controls remember their values across postback. I was one of them.

This is not the case, because the values are identified via posted back form field values, and assigned in the LoadPostData() method for those controls that implement IPostBackDataHandler.

In the page lifecycle, LoadPostBackData happens after LoadViewState stage. They are independent. The value of a textbox is restored in LoadPostBackData stage. If the view state for this textbox is enabled, then what happens in LoadViewState? Yes, the textbox could got a value, but that could be the wrong one, because the information in the view state represents the state before submission. For example, if the textbox’s value is “Hello my world” before you take any action to the page; at this moment, in the viewstate, it is “Hello my world”. Then from the browser, you change it to “Hello your world”, and submit the page. A new trip of the page life cycle happens. As we have known now, LoadViewState happens before LoadPostBackData. Guess what is the textbox’s value in the LoadViewState stage gotten from view state? It is “Hello my world” rather than “Hello your world”. Fortunately, LoadViewState follows, so that you can get the correct “Hello your world”. Untill the moment before the control is rendered, the viewstate is updated to “Hello your world”. Have you seen enabling the ViewState for such a control only wastes the resource if your application cares performance a lot?

Here we only talk about changing the value for this textbox. If we need to change it’s BackColor, for example, then viewstate is useful; or use other method to remember the changed BackColor if ViewState is not enabled.

Here is the list of controls which could be form fields meanwhile implement IpostBackDataHandler. CheckBox, DropDownList, ImageButton, ListBox, RadioButton, TextBox.

SQL Table Caching

Step 1:

Enabling the Database and Table

aspnet_regsql.exe -S "192.168.0.181" -U sa -P sa123 -d DMS -ed

aspnet_regsql.exe -S "192.168.0.181" -U sa -P sa123 - d DMS -t Userlogin -et

Step 2:

< connectionStrings >

< add name="GetitDMConnectionString2" connectionString="Data Source=192.168.0.181;Initial Catalog=DMS;Persist Security Info=True;User ID=sa;Password=sa123;Connect Timeout=900; pooling='true';Max Pool Size=200" providerName="System.Data.SqlClient"/>

< /connectionStrings >

< system.web >

< caching >

< sqlCacheDependency enabled="true" >

< databases >

<add name="DMS" connectionStringName="GetitDMConnectionString2" pollTime="500" />

< /databases >

< /sqlCacheDependency >

< /caching >

Step 3:

using System.Data.SqlClient;

using System.Web.Caching;

DataSet mycustomers;

mycustomers = (DataSet)Cache["firmCustomers"];

if (mycustomers == null)

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GetitDMConnectionString2"].ConnectionString);

SqlDataAdapter da = new SqlDataAdapter("Select * from userlogin", conn);

mycustomers = new DataSet();

da.Fill(mycustomers);

SqlCacheDependency myDependency = new SqlCacheDependency("DMS", "userlogin");

Cache.Insert("firmCustomers", mycustomers, myDependency);

Label1.Text = "Producing From database";

}

else

{

Label1.Text = "Produced From Cache";

}

GridView1.DataSource = mycustomers;

GridView1.DataBind();

Directives in ASP.net

Directives specify settings that are used by the page and user-control compilers when the compilers process ASP.NET Web Forms pages (.aspx files) and user control (.ascx) files.

1. @ Assembly : Links an assembly to the current page or user control declaratively.
2. @ Control : Defines control-specific attributes used by the ASP.NET page parser and compiler and can be included only in .ascx files (user controls).
3. @ Implements :Indicates that a page or user control implements a specified .NET Framework interface declaratively.
4. @ Import : Imports a namespace into a page or user control explicitly.
5. @ Master : Identifies a page as a master page and defines attributes used by the ASP.NET page parser and compiler and can be included only in .master files.
6. @ MasterType : Defines the class or virtual path used to type the Master property of a page.
7. @ OutputCache : Controls the output caching policies of a page or user control declaratively.
8. @ Page :Defines page-specific attributes used by the ASP.NET page parser and compiler and can be included only in .aspx files.
9. @ PreviousPageType : Creates a strongly typed reference to the source page from the target of a cross-page posting.
10. @ Reference : Links a page, user control, or COM control to the current page or user control declaratively.
11. @ Register : Associates aliases with namespaces and classes, which allow user controls and custom server controls to be rendered when included in a requested page or user control.

On Deletion/Updation Of a Primary Key, Foreign Key will be Deleted Automatically

create table a(ID int Primary key , MyName varchar(100))

create table ab(ID int foreign key references a(ID) on update cascade, Address1 varchar(100))

create table abc(ID int foreign key references a(ID) on update cascade, Address2 varchar(100))

insert into a(ID,MyName) values(1,'Bikash')
insert into ab(ID,Address1) values(1,'Getit')
insert into abc(ID,Address2) values(1,'Tej Building')

drop table a
drop table ab
drop table abc

delete from a where id=1
update a set id=4

In the above query, there is more synchronisation between master and Transaction. When we will delete/Update in Master table, Transaction will be updated/deleted Automatically

Create XML File from Query

1) SELECT remarkid,remark FROM callstatus FOR XML RAW

2) SELECT remarkid,remark FROM callstatus FOR XML AUTO

3)SELECT remarkid,remark FROM callstatus FOR XML path
Select 3rd query and press Ctrl+D and then run the query

JIT Compiler and Its Type

JIT compiler is a part of the runtime execution environment.
In Microsoft .NET there are three types of JIT compilers:
111
√ Pre-JIT :- Pre-JIT compiles complete source code into native code in a single
compilation cycle. This is done at the time of deployment of the application.
√ Econo-JIT :- Econo-JIT compiles only those methods that are called at runtime.
However, these compiled methods are removed when they are not required.
√ Normal-JIT :- Normal-JIT compiles only those methods that are called at runtime.
These methods are compiled the first time they are called, and then they are stored in
cache. When the same methods are called again, the compiled code from cache is
used for execution.

SessionState modes

The available session state modes are:

InProc mode, which stores session state in memory on the Web server. This is the default.

StateServer mode, which stores session state in a separate process called the ASP.NET state service. This ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm.

SQLServer mode stores session state in a SQL Server database. This ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm.

Custom mode, which enables you to specify a custom storage provider.

Off mode, which disables session state.

Cursor

Cursor is a database object used to manipulate data in a set on a row-by-row basis.

Step used in Cursors are:

=> Declare cursor
=> Open cursor
=> Fetch row from the cursor
=> Process fetched row
=> Close cursor
=> Deallocate cursor

Add Tooltip in Drop Down List or Combo Box

Write Below code after binding the drop down list

for (int i = 0; i < ddlArea.Items.Count; i++)
{
ddlArea.Items[i].Attributes.Add("Title", ddlArea.Items[i].Text);
}