Friday, December 28, 2012

Important SQL Queries Examples

·         Queries for Database Details:


·         To know Details about database and tables

sp_helpdb

o   Displays Details about all the databases.

select *from master.dbo.sysdatabases

o   It shows List of all databases.

sp_help employee

o   Displays the description about the tables

select *from sysobjects where type='u'

o   It shows How many indexes created on a table and their details.

sp_helpindex employee

o   shows the details about all users.

sp_who

·         Queries Related to Indexes

o   creates index as indexempname on empname column.

    create index indexempname on Employee(EmployeeCode)
o   create index as indexnm on empname which doesnt allow duplicate values.

create unique index indexempnm on Employee(EmployeeCode)
o   It drops the index indexempnm.

drop index indexempname on Employee 
  
o   Creation of index on two columns.

     create index indexcommon on
Employee(EmployeeCode,DeptCode) 
o   Assigning particular index for retrieving records.

    select EmployeeCode,DeptCode from Employee with(Index=indexempnm) 

o   To see the Details about indexes.

        exec sp_helpindex employee

Explain Web Server and Web Browser


Web Server


          Responsible for receiving and handling requests from the browsers via HTTP.
          Each Web server handled the request and sent a response back to the Web browser.
          After that, the Web server closed the connection and released all resources that were involved in the request .
          In addition to serving static HTML files, the Web servers can also handle requests for pages that contain code that will execute at the server 



Web Browser

         
          The Web browser provides a platform-independent means of displaying Web pages that were written with HTML.
          Execute code such as Java-Script and to support plug-ins which improve the user’s experience.
          Technologies such as Asynchronous JavaScript and XML (AJAX) allow the Web browsers to talk to the Web servers without clearing the existing Web pages from the browser window

Thursday, December 27, 2012

Assemblies in Detail




Assemblies:
·         An Assembly is a unit of file that contains IL Code corresponding to a project when it was compiled.
·         The name of an assembly will be same as project name from which it was created.
·         Assemblies were known as units of deployment because once the application development is done what we install on client machines is assemblies only.
·         An assembly file can have an extension of either exe or dll.
·         exe: executable  dll: dynamic link library
Exe assemblies are in-process components which were capable of running on their own as well as provide the support for others to execute. When we use project templates like Windows Forms, WCF, Console Applications & Windows Services they generate an exe assembly when compiled.
Dll assemblies are out-process components which were not capable of running on their own they can only support others to execute. Project templates like Class & Control Library will generate an dll assembly when compiled.
Assemblies are of 2 types:
·         Private Assemblies
·         Shared Assemblies
Private Assemblies:
By default every assembly is private, if reference of these assemblies was added to any project, a copy of the assembly is created and given to the project, so each project maintains a private copy of the assembly.
Shared Assemblies:
An assembly which sits under the GAC (Global Assembly Cache) to provide resources for multiple applications was known as shared assembly.
If an assembly was shared multiple copies will not be created even if being consumed by multiple projects, only a single copy under GAC serves all the projects.
GAC was a special folder under the OS:
             :\Windows\assembly   <- folder="folder" gac="gac" span="span">
Note: all the BCL were Shared dll Assemblies, so we can find them under GAC.
Creating a Private Assembly:
·         Open a new project of type "Class Library" & name it as "PAssembly", which will by default come with a class as Class1.
·         Note: a class library is a collection of types like  classes, interfaces, structures etc., but cannot be executed, can only be consumed as they were dll's.
·         Now under the class write following code:
   public string SayHello()
   {
      return "Hello from private assembly";
   }
·         To compile the project open Solution Explorer, right click on the project and select "Build" which will generate an assembly as PAssembly.dll.
·         Note: we can find path of the assembly which was created in the output window present at bottom of the studio after build.

Consuming the Private Assembly:
·         Open a new project of type Windows, name it as "TestPAssembly", place a button on Form & set the text as "Call SayHello of PAssembly".
·         Now add the reference of PAssembly.dll from its physical location & write the following code under click of button:
PAssembly.Class1 obj = new PAssembly.Class1();
MessageBox.Show(obj.SayHello());
·         Run the project to test it, then go and verify under bin/debug folder of current project where we can find a copy of PAssembly.dll as it was private.
Note: the advantage of a private assembly is faster execution as it was in the local folder, where as the drawback was multiple copies gets created when multiple projects add's the reference to consume it.

Creating a Shared Assembly:
Step 1: Generate a key file. Open VS command prompt, go into your folder and generate a key file as following:
        C:\CSharp6> sn -k key.snk
Step 2: Create a project & associate the key file to it before compilation so that the assembly which is generated will be Strong Named.
·         Open a new project of type Class Library & name it as "SAssembly".
·         Under the class Class1 write following code:
public string SayHello()
{
   return "Hello from shared assembly 1.0.0.0";
}

·         To associate the key file we have generated with project, open project properties window, select Signing tab on LHS, which displays a CheckBox as "Sign the Assembly" select it, in the ComboBox below select browse, select the key.snk file from its physical location which adds the file under solution explorer, then compile the project using "Build" option that will generate SAssembly.dll which is Strong Named.
Step 3: Copying the Assembly into GAC.
·         To copy the assembly into GAC .Net provides an utility known as "gacutil" which is a command line tool that has to be used as following:
        gacutil -i | -u
        -i      Install
        -u     Uninstall
·         Open VS command prompt, go into the location where SAssembly.dll was present and write the following:
C:\CSharp6\SAssembly\SAssembly\bin\Debug> gacutil -i SAssembly.dll
Step 4: Testing the Shared Assembly:
·         Open a new project of type Windows & name it as "TestSAssembly", place a button on form & set the text as "Call SayHello of SAssembly 1.0.0.0".
·         Add reference of SAssembly.dll from its physical location & write following code under button click:
SAssembly.Class1 obj = new SAssembly.Class1();
MessageBox.Show(obj.SayHello());

·         Run the project, test it and verify under bin/debug folder of current project where we will not find any copy of SAssembly.dll as it was shared.
Note: when a shared assembly is used in multiple applications there will not be multiple copies of the assembly, where as it's not faster when compared with private assembly.

Wednesday, December 26, 2012

Difference Between UNION and UNION ALL in SQL


UNION

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

Basic rules for using UNION: 

·         The number and the order of the columns must be the same in all queries.
·         The data types must be compatible

UNION ALL 

Incorporates all rows into the results. This includes duplicates.
If not specified, duplicate rows are removed.