SQL SERVER Questions And Answers -1

Q 1.How many maximum column can be in a table in sql server 2008?
Ans: Max row size is 8,060 bytes so its depends on column type in a table.

Q 2. How many maximum foreign key table reference per table?
Ans : 253

Q.3 How many maximum nested sub quires/store procedure /triggers ?
Ans: 32

Q.4 How many maximum parameters in user function / store procedure?
Ans : 2100

Q.5 How many maximum number of columns in per SELECT/INSERT/UPDATE statement?
Ans : 4096

Q.6 How can get all the table names with additional information?
SELECT * FROM sys.tables
OR
SELECT * FROM INFORMATION_SCHEMA.TABLES

Q.7 How can get all table columns with additional information
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Q.8 How can get all View information in database
SELECT * FROM INFORMATION_SCHEMA.VIEWS

Q.9 How can set trigger fire sequence on table?
Exec sp_settriggerorder @triggername=’trg_name’, @order=’FIRST|LAST|NONE’,
@stmttype=’INSERT|UPDATE|DELETE|CREATE_INDEX,ALTER_INDEX’,
@namespace=’DATABASE|SERVER|NULL’

Q.10 How can create comma seprated list of names in a table?
DECLARE @name varchar(max)
SELECT @name = COALESCE(@name+’,’,”)+name FROM Employee
PRINT @name

Finalize() and Dispose()

C# provides special methods that are used to release the instance of a class from memory, Finalize() and Dispose().

Finalize()

  • The Finalize method is used to perform cleanup operations on unmanaged resources held by the current object before the current object is destroyed.
  • In C# destructor are converted to finalize method by C# compiler.
  • The scope of the Object.Finalize method is protected.
  • The Finalize() destructor is called after the last reference to an object is released from the memory.
  • It is automatically called by garbage collector to release resource.
  • The exact time when the finalizer executes during garbage collection is undefined.
Illustrate by an example

Create a class FinalizeImplement which has a destructor ~FinalizeImplement()

using System;
using System.Diagnostics;

namespace CSharp
{
    class FinalizeImplement
    {
        Stopwatch sw;

        public FinalizeImplement()
        {
            sw = Stopwatch.StartNew();
            Console.WriteLine("Instantiated object");
        }

        public void ShowDuration()
        {
            Console.WriteLine("This instance of {0} has been in existence for {1}",this, sw.Elapsed);
        }

        ~FinalizeImplement()
        {
            Console.WriteLine("Finalizing object");
            sw.Stop();
            Console.WriteLine("This instance of {0} has been in existence for {1}", this, sw.Elapsed);
            Console.ReadLine();
        }
    }    
}

Create a program in which implemented finalize

namespace CSharp
{   
    class Program
    {
        static void Main(string[] args)
        {
            FinalizeImplement objFinalize = new FinalizeImplement();
            objFinalize.ShowDuration();

        }
    }
}
OUTPUT
finalize()

finalize()

Dispose()

  • The Dispose() method is called to release a resource, such as a database connection, as soon as the object using such a resources is no longer in use.
  • It is not automatically and you must explicitly call it from client application when an object is no longer needed.
  • The IDisposable interface contains the Dispose() method.
  • A Dispose method should call the GC.SuppressFinalize method for the object it is disposing. If the object is currently on the finalization queue, GC.SuppressFinalize prevents its Finalize method from being called.
  • Use this method to close or release unmanaged resources such as files, streams, and handles held by an instance of the class that implements this interface.
Illustrate by an example

Create a class DisposableResource. This class shows how to use a disposable resource. The resource is first initialized and passed to the constructor, but it could also be initialized in the constructor. The lifetime of the resource does not exceed the lifetime of this instance.This type does not need a finalizer because it does not directly create a native resource like a file handle or memory in the unmanaged heap.

using System;
using System.IO;

namespace DisposeExample
{
    public class DisposableResource : IDisposable
    {
        private Stream mResource;
        private bool mDisposed;

        // The stream passed to the constructor must be readable and not null. 
        public DisposableResource(Stream stream)
        {
            if (stream == null)
            {
                throw new ArgumentNullException("Stream in null.");
            }
            if (!stream.CanRead)
            {
                throw new ArgumentException("Stream must be readable.");
            }

            mResource = stream;
            mDisposed = false;
        }

        // Demonstrates using the resource. It must not be already disposed. 
        public void GetResourceSize()
        {
            if (mDisposed)
            {
                throw new ObjectDisposedException("Resource was disposed.");
            }

            // Show the number of bytes. 
            int numBytes = (int)mResource.Length;
            Console.WriteLine("Number of bytes: {0}", numBytes.ToString());
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        //Disposing resource if its not already dispose
        protected virtual void Dispose(bool disposing)
        {
            // If you need thread safety, use a lock around these operations, as well as in your methods that use the resource. 
            if (!mDisposed)
            {
                if (disposing)
                {
                    if (mResource != null)
                    {                                              
                        mResource.Dispose();                        
                    }
                    Console.WriteLine("Object disposed.");
                }

                // Indicate that the instance has been disposed.
                mResource = null;
                mDisposed = true;
            }
        }
    }
}

Create a program in which implemented dispose

using System;
using System.IO;

namespace DisposeExample
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Initialize a Stream resource to pass to the DisposableResource class.
                Console.Write("Enter filename with full path: ");
                string fileSpecification = Console.ReadLine();
                FileStream fStream = File.OpenRead(fileSpecification);
                DisposableResource objDisposableResource = new DisposableResource(fStream);

                // Use the resource.
                objDisposableResource.GetResourceSize();

                // Dispose the resource.
                objDisposableResource.Dispose();
                Console.Read();
            }
            catch (FileNotFoundException e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}
OUTPUT

Dispose()

Dispose()

Caching Application Pages and Data

Caching is the process of storing frequently used data on the server to fulfill subsequent requests. It uses to increases performance and scalability of site and mostly uses for static content or same data on site using many times.

Types of Caching:

There are 3 types of caching-

  1. Output Caching / Page Caching
  2. Partial Page Caching
  3. Data Caching

1. Output Caching

It caches an entire page. We can enable Page Output Caching by adding an <%@ OutputCache %> directive to a page. When page is cached then the .NET classes that corresponds to the page is not executed with each page request. The page is cached in multiple locations. By default, the page is cached on the web browser, any proxy servers, and on web server.
For example below page content caches for 60 seconds:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PageCache.aspx.cs" Inherits="PageCache" %>
<%@ OutputCache Duration="60" VaryByParam="none" VaryByCustom="browser" Location="Client" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<%: DateTime.Now.ToString() %>
<hr />
<%: Request.UserAgent %>
</div>
</form>
</body>
</html>

Parameters for output caching:

  1. VaryByParam : the ASP.NET engine will cache the dynamic page based on different query strings provided. If your page generates different content based on the query string, then you need to put that in the output cache directive or else all your users will see the same content.
  2. VaryByHeader : To create different cached version of a page when the value of particular browser header change.
  3. VaryByControl: This attribute enables to generate different versions of a page depending on the value of a particular control in the page. This attribute is useful when we need to create a single page Master/Detail form.
  4. VaryByCustom:In this we can specify a custom function that determines when a different cache version of a page is generated. For this we create the common function in the Global.asax.
    public override string GetVaryByCustomString(HttpContext context, string custom)
        {
            if (custom == "css")
            {
                return Request.Browser.SupportsCss.ToString();
            }
            return base.GetVaryByCustomString(context, custom);
        }

2. Partial Caching:

In this cache technique, Cache portion of page instead of whole page. There are two option for it.

  1. Control Caching: In control caching, also known as fragment caching, you can cache parts of the page output by creating user controls to contain the cached content and then marking the user controls as cacheable. This allows specific content within a page to be cached, while the overall page is recreated each time. In the user control source file, add an OutputCache directive specifying the Duration and VaryByParamattributes. When that user control is loaded into a page at runtime, it is cached. When we set Shared attribute true then all subsequent pages that reference that same user control will retrieve it from the same cache.
  2. Post Cache Substitution : It is the opposite. The page is cached, but fragments within the page are dynamic and should therefore not be cached. Post-cache substitution is used internally by the AdRotator control. Even when you use Page Output Caching to cache a page that contains an Adrotator control, the content rendered by the AdRotator control is not cached. For example:
    UI PartialPageCache.aspx

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="PartialPageCache.aspx.cs" Inherits="PartialPageCache" %>
    <%@ OutputCache VaryByParam="none" Duration="60" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-ransitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
    <title></title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    Cache Time : <%:DateTime.Now %><br />
    Non Cache Time :<asp:Substitution ID="subTime" runat="server" MethodName="GetTime"/>
    </div>
    </form>
    </body>
    </html>
    

    Code File PartialPageCache.aspx.cs

    using System;
    using System.Web;
    
    public partial class PartialPageCache : System.Web.UI.Page
    {
        public static string GetTime(HttpContext context)
        {
            return DateTime.Now.ToString();
        }
    }
    

3.Data Caching:

Data caching stores the required data in cache so that the web server will not send requests to the DB server every time for each and every request, which increases web site performance.

Write and Read Data in Cache:

private DataTable GetMovies()
    {
        if (Cache["Movie"] == null)
        {
            DataTable movieTable = new DataTable();
            using (SqlConnection conn = new SqlConnection("data source=sandeepss-PC; initial catalog= development; user id=sa; password=knowdev"))
            {
                SqlCommand cmd = new SqlCommand("Select * from Movies", conn);
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlDataReader dr = cmd.ExecuteReader();
                movieTable.Load(dr);
                dr.Close();
                //Cache["Movie"] = movieTable;
                Cache.Insert("Movie", movieTable);
            }
        }
        return (DataTable)Cache["Movie"];
    }

JQuery Required Field Validation In .NET

Here creating validation on input fields using Jquery. To validate input filed in this example I will use jquery validation plug in and Jquery script.

Create a project in VS2010 and it has following directory structure.

Jquery Validation directory structure

Jquery Validation directory structure

Now create a web form “RegisterForm.aspx” like:

JQuery Validation Form

JQuery Validation Form

Add script reference on web form

<script type="text/javascript" src="Jquery/jquery-1.8.2.min.js"></script>
<script type ="text/javascript" src="Jquery/jquery.validate.min.js"></script>

Add input fields on web form

<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Name</td>
<td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Email</td>
<td><asp:TextBox ID="txtMail" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Password</td>
<td><asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox></td>
</tr>
<tr>
<td colspan="2"><asp:Button ID="btnValidate" runat="server" Text="Validate" /></td>
</tr>
</table>
</div>
</form>

Create validate function to validate form

<script type="text/javascript">
$(document).ready(function ()
        {
            $("#form1").validate(
            {
                rules:
                {
                    txtName:
                    {
                        required: true
                    },
                    txtMail:
                    {
                        required: true,
                        email: true
                    },
                    txtPassword:
                    {
                        required: true
                    }
                },
                messages:
                {
                }
            }
            );
        }
        );
</script>

OUTPUT

Jquery Form Validation

Jquery Form Validation

What is Disco file ?

The DISCO file typically points to a WSDL source that in turn points to the actual Web Service.When one searches for the Web Services, it will go to the DISCO which gives actual information for the particular Web services.It has .disco file extension. It is an XML document which contains link to another resources.

Format of Disco File

<?xml version="1.0" encoding="utf-8"?>
<discovery xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.xmlsoap.org/disco/">
<contractRef ref="http://localhost:4261/WebSite1/ManagerConsole.asmx?wsdl" docRef="http://localhost:4261/WebSite1/ManagerConsole.asmx" xmlns="http://schemas.xmlsoap.org/disco/scl/" />
<soap address="http://localhost:4261/WebSite1/ManagerConsole.asmx" xmlns:q1="http://tempuri.org/" binding="q1:ManagerConsoleSoap" xmlns="http://schemas.xmlsoap.org/disco/soap/" />
</discovery>

Tags:

  1. <discovery> : It is root tag of .disco file and we can add as many references as we would like to publicly expose.
  2. <contractRef>:It Contains two main attributes which are ref and docRef
    1. ref : It refers to WSDL file.
    2. docRef : It refers human-readable document or actual web service where we define all methods.
  3. <soap> : It Contains an attribute called address that is the location or URI of a Web Service that has been defined in a WSDL document.

Encryption and Decryption string with .NET

Triple DES

Also referred to as 3DES, a mode of the DES encryption algorithm that encrypts data three times. Three 64-bit keys are used, instead of one, for an overall key length of 192 bits means TripleDES uses three successive iterations of the DES algorithm. It can use either two or three 56-bit keys. This algorithm supports key lengths from 128 bits to 192 bits in increments of 64 bits.

Terms:

  1. Plaintext- It is the original intelligible message or data that is fed into the algorithm as input.
  2. Secret key –It is also input to the encryption algorithm. The key is value independent of the plaintext.
  3. Ciphertext –This is the scrambled message produced as output. It depends on the plaintext and secret key.
  4. Encryption algorithm- It performs various substitution and transformation on the plaintext and produce ciphertext base on key.
  5. Decryption algorithm- This is essentially the encryption algorithm run in reverse. It takes the ciphertext and secret key and produces the original plaintext.

DES (Data Encryption standard) is symmetric key encryption technology. Triple DES uses a “key bundle” which comprises three DES keys, K1, K2 and K3, each of 56 bits (excluding parity bits).

The encryption algorithm is:

ciphertext = EK3(DK2(EK1(plaintext)))
I.e., DES encrypt with K1, DES decrypt with K2, then DES encrypt with K3.

Decryption is the reverse:

plaintext = DK1(EK2(DK3(ciphertext)))
I.e., decrypt with K3, encrypt with K2, then decrypt with K1.

Each triple encryption encrypts one block of 64 bits of data.In each case the middle operation is the reverse of the first and last. This improves the strength of the algorithm when using keying option 2, and provides backward compatibility with DES with keying option 3.

Workflow in this example:

  1. Create a class to encrypt and decrypt plaintext and ciphertext respectively.
  2. Create a web form which get input secret key and plaintext.
  3. Web form contains two button for encrypt and decrypt.

In .net System.Security.Cryptography namespace has encrypt and decrypt classes.

Steps:

Create a class for encryption and decryption.
1.Using namespace

using System;
using System.Security.Cryptography;
using System.Text;

2. In this class create a method which return TripleDES object and take secrete key as input. Set key and initialization vector (IV) to tripleDES object

private TripleDES CreateDES(string key)
{
    TripleDES tDes = new TripleDESCryptoServiceProvider();
    MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
    tDes.Key = hashmd5.ComputeHash(Encoding.Unicode.GetBytes(key));
    tDes.IV = new byte[tDes.BlockSize / 8];
    return tDes;
}

3.Create a method for encryption which takes two input parameter plaintext and secret key and return byte(ciphertext).

public byte[] Encryption(string plainText, string key)
{
TripleDES tDes = CreateDES(key);
ICryptoTransform ict = tDes.CreateEncryptor();
byte[] input = Encoding.Unicode.GetBytes(plainText);
return ict.TransformFinalBlock(input, 0, input.Length);
}

4.Create a method for decryption which takes two input parameter ciphertext and secret key and ciphertext and return plaintext.

public string Decryption(string cipherText, string key)
{
    byte[] cipherByteText = Convert.FromBase64String(cipherText);
    TripleDES tDes = CreateDES(key);
    ICryptoTransform ict = tDes.CreateDecryptor();
    byte[] output = ict.TransformFinalBlock(cipherByteText, 0, cipherByteText.Length);
    return Encoding.Unicode.GetString(output);
}

5.Create a Form which contain two textboxes, two labels and two buttons:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="EncDec.aspx.cs" Inherits="EncDec" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Encryption and Decryption</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td> Enter Key </td>
<td> <asp:TextBox ID="txtKey" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Enter Plain Text</td>
<td> <asp:TextBox ID="txtPlainText" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:Button ID="btnEncrypt" runat="server" Text="Encryption" OnClick="btnEncrypt_Click" />
</td>
</tr>
<tr>
<td>Encrypted Text</td>
<td><asp:Label ID="lblEncrypted" runat="server"></asp:Label></td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:Button ID="btnDecrypt" runat="server" Text="Decryption" OnClick="btnDecrypt_Click" />
</td>
</tr>
<tr>
<td>Decrypted Text</td>
<td><asp:Label ID="lblDecrypt" runat="server"></asp:Label></td>
</tr>
</table>
</div>
</form>
</body>
</html>

6.Create button event for encryption and decryption

using System;

public partial class EncDec : System.Web.UI.Page
{
    EncryptionDecryption objEncDec = new EncryptionDecryption();
    protected void btnEncrypt_Click(object sender, EventArgs e)
    {
        lblEncrypted.Text = Convert.ToBase64String(objEncDec.Encryption(txtPlainText.Text, txtKey.Text));
    }
    protected void btnDecrypt_Click(object sender, EventArgs e)
    {
        lblDecrypt.Text = objEncDec.Decryption(lblEncrypted.Text, txtKey.Text);
    }
}

OUTPUT

encryption and decryption string with .NET

Encryption and Decryption string with .NET

Client Side State Management

In Client Side State Management technique all data involves between client and server round trip. These are five types:

  1. Cookies
  2. Query String
  3. Hidden Field
  4. View State
  5. Control State

1. Cookies
It is text which stored on user’s computer. Its maximum size is 4kb.
Writing a Cookie
HttpCookie name = new HttpCookie(“userName”);//creating a cookie
if (Request.Cookies[“userName”] == null)//check for cookie already exists
{
name.Value = txtName.Text; // assign value to cookie
name.Expires = DateTime.Now.AddMinutes(5); // make cooke for 5 min
Response.Cookies.Add(name); //adding cookie
}

Reading a Cookie
if (Request.Cookies[“userName”] != null)
{
lblName.Text = “User Name is ” + Request.Cookies[“userName”].Value;
}
else
{
lblName.Text=”Cookie is Null”;
}

2. Query Strings

They are passed from one page to another page along with URL in clear text. Query string length 255 characters with URL.
Pass query string in URL
Response.Redirect(“QueryString.aspx?Name=”+txtName.Text+”&Address=”+txtAddress.Text);

Read Query String
string name = Request.QueryString[“Name”];
string address = Request.QueryString.Get(“Address”);
lblInfo.Text = “My Name is ” + name + ” address is ” + address;

3. Hidden Field

It uses data on page level. It does not render by browser. It can be clearly visible passed over network.
protected System.Web.UI.HtmlControls.HtmlInputHidden hiddenName = new System.Web.UI.HtmlControls.HtmlInputHidden();

protected void Page_Init(object sender, EventArgs e)
{
hiddenName.Value = “sandeep”;
}
protected void Page_Load(object sender, EventArgs e)
{
lblInfo.Text = hiddenName.Value;
}

4. View State

This is the default method that the page uses to preserve page and control property values between round trips. You can set View State on/off for each control using EnableViewState property. By default, EnableViewState property will be set to true. You can also disable View State for the entire page by addingEnableViewState=false to @page directive.
Its conver in hidden Field like

It also uses on page level as hidden filed as its converted in hidden field.
Write and Read view state:
ViewState[“Name”] = txtName.Text;
txtAddress.Text = (string)ViewState[“Name”];

Follow

Get every new post delivered to your Inbox.