Tuesday 31 December 2013

EnableViewState vs ViewStateMode in asp.net 2010

With ASP.Net 4.0, a new property for View State was introduced, it was called ViewStateMode. Prior to ASP.Net 4.0, we used to use EnableViewState property. Now for an ASP.Net 4.0 application, both of these properties are available. So the question comes in which one we should use? And how are they different?

To answer the first question, you can use whichever you want, if it suits you.

Now lets see what the difference between these two.

ViewStateMode property allows you to Disable View State at parent level and Enable it selectively at child level.

EnableViewState property does not allow this. Simple.

Both of these properties allow you to Enable view state at parent level and Disable it at child level.

Lets learn by example. This is how my foo.aspx page looks (using ASP.net 4.0)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo" %>

<!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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    </form>
</body>
</html>

At Page Load, I am setting label’s Text property to current date time stamp.

        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                Label1.Text = DateTime.Now.ToString();
            }
        }

Now, if we run the application, this is how the foo.aspx page will look on browser.

First Request:

Post Back (button click):

So the label is showing the date time stamp as expected. Now if we click on the Button, page will be posted back and the result will be the same. Label is still showing the date time stamp but that is not the current time, instead, it is the time of the first request. Which means, the label text is getting populated from ViewState.

Now lets disable View State at the page level. Lets use ViewStateMode property. This is how foo.aspx page directive looks like.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo"

ViewStateMode="Disabled" %>

Now, if I re-run the application, first time when the page loads, label will show current date time (which is expected). And on post back (clicking the button), we will not see the date time stamp any more. Reason? ViewState is disabled now.

First request after disabling View State at page level (ViewStateMode = Disabled)

Post Back:

So far so good. Till this point, there is almost no difference between the two properties ViewStateMode and EnableViewState. Now what if I want to enable view state, but only for the label. This is what we do.

<asp:Label ID="Label1" runat="server" Text="Label" ViewStateMode="Enabled"></asp:Label>

Now lets run the application once again.

Since we have enabled View Sstate at label, we would expect the date time stamp to persist during post back.

First request: At page level –> ViewStateMode=Disabled; at control level(label) –> ViewStateMode=Enabled

Post back

THIS is what you could not do using EnableViewState property. If you try to use EnableViewState, once view state is disabled at page you can not selectively enable it at controls. Lets try it

I have changed my foo.aspx and to use EnableViewState now.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo"

EnableViewState="false" %>

And at label:

<asp:Label ID="Label1" runat="server" Text="Label" EnableViewState="true" ></asp:Label>

So lets run the application.

First Request: at page level –> EnableViewState=False; at control (label) –> EnableViewState=True

Post back:

So since the date time stamp does not persists, it indicates that View State is not enabled for label control. We can confirm this by enabling Tracing

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="foo.aspx.cs" Inherits="WebApplication4.DemoPages.foo"

EnableViewState="false" Trace="true" %>

This is relevant trace result for the post back

As we see, the ViewState size is 0 for label.

Now a note of caution: While using EnableViewState, we can still Enable view state at parent level and Disable it at control level.

To summarize:

Using ViewStateMode: Disable view state at Parent, Enable it at child level –> Works well

Enable view state at Parent, Disable it at child level –> Works well

Using EnableViewState: Disable view state at parent, Enable it at child –> Does not work

Enable view state at parent, Disable it at child –> Works well

Sunday 22 December 2013

Bind Dropdownlist with XML File

Here I am explaining how to bind an XML fine to ASP.Net DropDownList control.

XML file

Below is my Cities.xml which I contains the list of some Indian cities.

<?xmlversion="1.0"encoding="utf-8" ?>

<cities>

 <city>

    <id>1</id>

    <name>Mumbai</name>

 </city>

 <city>

    <id>2</id>

    <name>Delhi</name>

 </city>

 <city>

    <id>3</id>

    <name>Chennai</name>

 </city>

 <city>

    <id>4</id>

    <name>Kolkatta</name>

 </city>

</cities>

The name of the City will be assigned to the Text part and the id will be assigned to the Value

 part of the ASP.Net DropDownList Control.

Namespace

You will need to import the following namespace.

C#

using System.Data;

Reading the XML file and Binding the DropDownList

Below is the method that reads the XML file into a Dataset object and then binds the same to the ASP.Net DropDownList Control

private void BindXml()

{

    string filePath = Server.MapPath("~/Cities.xml");

    using (DataSet ds = new DataSet())

{

        ds.ReadXml(filePath);

        ddlCities.DataSource = ds;

        ddlCities.DataTextField = "name";

        ddlCities.DataValueField = "id";

        ddlCities.DataBind();

}

}

I am calling the above method in the Page Load event of the Page in the following way

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

{

        this.BindXml();

}

}

Sunday 15 December 2013

CSV function in sql server for divide a single string into multiple string through comma

execute this funtion in sql server after that call it,
it's return multiple string..

create function CSV_function
(
@CSVString varchar(8000) ,
@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
begin
declare @i int ,
@j int
select @i = 1
while @i <= len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select substring(@CSVString, @i, @j - @i)
select @i = @j + len(@Delimiter)
end
return
end

Thursday 12 December 2013

Select Find Nth Highest Salary Record In Sql Server

Select Find Nth Highest Record In Ms Sql Server

1st method

To select 2nd highest salary or record we can use following query.

SELECT TOP 1 [Salary]
FROM 
(
SELECT  DISTINCT TOP 2 [Salary]
FROM [dbo].[Employee]
ORDER BY [Salary] DESC
) temp
ORDER BY [Salary]

2nd method

To select 3rd highest salary or record we can use following query.

SELECT TOP 1 [Salary]
FROM ( SELECT  TOP 3 [Salary]
  FROM [dbo].[Employee] e1 GROUP BY e1.Salary
  ORDER BY [e1].[Salary] DESC) e2
  ORDER BY [Salary]

These queries holds good untill we are selecting only salary column and fails when we want to select all the columns or few more columns with salary as salary can be same for more then one employees or records.

For example if we change the first query to select 2nd highest salary with all the columns of table, output would be undesirable as shown below.

SELECT TOP 1 [Salary],[EmployeeName]
FROM 
(
SELECT  DISTINCT TOP 2 [Salary], [EmployeeName]
FROM [dbo].[Employee]
ORDER BY [Salary] DESC
) temp
ORDER BY [Salary]

To select all columns we can use queries mentioned below.

This query will give 4th highest salary record but will show only 1 highest record if even if there are multiple duplicate salary records.

SELECT TOP 1 * FROM [dbo].[Employee]
WHERE [Salary] NOT IN  
( 
  SELECT DISTINCT TOP 3 [Salary] FROM [dbo].[Employee]
  ORDER BY [Salary] DESC
)
ORDER BY [Salary] DESC

These 2 queries will select 4th highest salary with duplicate records.

SELECT * FROM [dbo].[Employee]
WHERE [Salary] = 
( 
  SELECT MAX([Salary]) FROM [dbo].[Employee] 
  WHERE [Salary] NOT IN
    ( 
      SELECT DISTINCT TOP (4-1) [Salary] FROM [dbo].[Employee] e1
      ORDER BY [Salary] DESC 
    )
)

SELECT *
FROM Employee E1
WHERE (4-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

We can also use sql ranking function to get desired result as follows.

SELECT * FROM 
(
  SELECT DENSE_RANK() OVER(ORDER BY [Salary] DESC)AS RowId, * 
  FROM [dbo].[Employee] 
) AS e1
  WHERE e1.RowId = 4

Remove Delete Duplicate Records Or Rows Sql Server

1) FIRST METHOD

Delete duplicate records/rows by creating identity column.

alter table TABLE NAME add id int identity(1,1)
delete from TABLE NAME where id in(select MIN(id) from counter group by cid,ccounter)

2) SECOND METHOD

Delete duplicate records using Row_Number()

WITH DuplicateRecords AS
(
SELECT *,row_number() OVER(PARTITION BY cid,ccounter ORDER BY cid) AS RowNumber FROM counter
)
DELETE FROM DuplicateRecords WHERE RowNumber>1

How To Reset Identity Column In Sql Server Table To Start Auto Increment Or Seed From Desired Number

DBCC CHECKIDENT('YouTableName', RESEED, 0)

Random Password Generation in asp.net

Enhancing the same password generation logic so that it would be useful for generating random password using C# code.

    public static string GetRandomPassword(int length)
    {
        char[] chars = "$%#@!*abcdefghijklmnopqrstuvwxyz1234567890?;:ABCDEFGHIJKLMNOPQRSTUVWXYZ^&".ToCharArray();
        string password = string.Empty;
        Random random = new Random();

        for (int i = 0; i < length; i++)
        {
            int x = random.Next(1,chars.Length);
            //Don't Allow Repetation of Characters
            if (!password.Contains(chars.GetValue(x).ToString()))
                password += chars.GetValue(x);
            else
                i--;
        }      
        return password;
    }

Its a simple logic instead by generating a random number between 1 and Length of characters. It also checks that same character is not repeated in generated password and finally return the randomly generated password string of desired length.

Export DataSet To Excel Using Asp.Net

I have been exporting data from dataset to excel using nested loops, excel object, accessing each cell of excel sheet and each cell of dataset and so on and so forth until I find this……
Before writing about it a special thanks to Peter A. Bromberg for writing such a nice article which really made at least my life easy….:). In his article he wrote a very simple function to export data without using excel object and other messy stuff. XSL Transformation is applied to dataset and XML for excel is generated.
Below is the complete function which will write an Excel file to your local disk. You have to pass it the DataSet to export and path to where file should be generated.
public static void CreateWorkbook(DataSet ds, String path)
{
   XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
   XslTransform xt = new XslTransform();
   StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
   XmlTextReader xRdr = new XmlTextReader(reader);
   xt.Load(xRdr, null, null);
   StringWriter sw = new StringWriter();
   xt.Transform(xmlDataDoc, null, sw, null);
   StreamWriter myWriter = new StreamWriter (path + “\\Report.xls“);
   myWriter.Write (sw.ToString());
   myWriter.Close ();
}

Delete All Tables ,Store Procedures,Views and Functions in SQL

Delete All Tables

--Delete All Keys

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

Delete All Stored Procedures

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'p'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName <> 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

Delete All Views

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'v'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop view ' + @procName)
                  fetch next from cur into @procName
      end
close cur
deallocate cur

Delete All Functions

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'fn'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop function ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

3 Tier Architecture in Dote Net

Basically 3-Tier architecture contains 3 layers

1. Application Layer or Presentation Layer

2. Business Access Layer(BAL) or Business Logic Layer(BLL)

3. Data Access Layer(DAL)

Here I will explain each layer with simple example that is User Registration

Application Layer or Presentation Layer

Presentation layer contains UI part of our application i.e., our aspx pages or input is taken from the user. This layer mainly used for design purpose and get or set the data back and forth. Here I have designed my registration aspx page like this

This is Presentation Layer for our project Design your page like this and double click on button save now in code behind we need to write statements to insert data into database this entire process related to Business Logic Layer and Data Access Layer.

Now we will discuss about Business Access Layer or Business Logic Layer

Business Access Layer (BAL) or Business Logic Layer (BLL)

This layer contains our business logic, calculations related with the data like insert data, retrieve data and validating the data. This acts as a interface between Application layer and Data Access Layer

Now I will explain this business logic layer with my sample

I have already finished form design (Application Layer) now I need to insert user details into database if user click on button save. Here user entering details regarding Username, password, Firstname, Lastname, Email, phone no, Location. I need to insert all these 7 parameters to database. Here we are placing all of our database actions into data access layer (DAL) in this case we need to pass all these 7 parameters to data access layers.

In this situation we will write one function and we will pass these 7 parameters to function like this

String Username= InserDetails (string Username, string Password, string Email, string Firstname, string Lastname, string phnno, string Location)

If we need this functionality in another button click there also we need to declare the parameters likestring Username, string Password like this write. If we place all these parameters into one place and use these parameters to pass values from application layer to data access layer by using single object to whenever we require how much coding will reduce think about it for this reason we will create entity layer or property layer this layer comes under sub of group of our Business Logic layer

Don't get confuse just follow my instructions enough

How we have to create entity layer it is very simple

Right click on your project web application---> select add new item ----> select class file in wizard --->give name as BEL.CS because here I am using this name click ok

Open the BEL.CS class file declare the parameters like this in entity layer

Don’t worry about code it’s very simple for looking it’s very big nothing is there just parameters declaration that’s all check I have declared whatever the parameters I need to pass to data access layer I have declared those parameters only

BEL.CS

#region Variables

/// <summary>

/// User Registration Variables

/// </summary>

private string _UserName;

private string _Password;

private string _FirstName;

private string _LastName;

private string _Email;

private string _Phoneno;

private string _Location;

private string _Created_By;

#endregion

/// <summary>

/// Gets or sets the _UserName attribute value.

/// </summary>

/// <value>The _UserName attribute value.</value>

public string UserName

{

get

{

return _UserName;

}

set

{

_UserName = value;

}

/// <summary>

/// Gets or sets the _Password attribute value.

/// </summary>

/// <value>The _Password attribute value.</value>

public string Password

{

get

{

return _Password;

}

set

{

_Password = value;

}

/// <summary>

/// Gets or sets the _FirstName attribute value.

/// </summary>

/// <value>The _FirstName attribute value.</value>

public string FirstName

{

get

{

return _FirstName;

}

set

{

_FirstName = value;

}

/// <summary>

/// Gets or sets the _LastName attribute value.

/// </summary>

/// <value>The _LastName attribute value.</value>

public string LastName

{

get

{

return _LastName;

}

set

{

_LastName = value;

}

/// <summary>

/// Gets or sets the _Email attribute value.

/// </summary>

/// <value>The _Email attribute value.</value>

public string Email

{

get

{

return _Email;

}

set

{

_Email = value;

}

/// <summary>

/// Gets or sets the _Phoneno attribute value.

/// </summary>

/// <value>The _Phoneno attribute value.</value>

public string Phoneno

{

get

{

return _Phoneno;

}

set

{

_Phoneno = value;

}

/// <summary>

/// Gets or sets the _Location attribute value.

/// </summary>

/// <value>The _Location attribute value.</value>

public string Location

{

get

{

return _Location;

}

set

{

_Location = value;

}

/// <summary>

/// Gets or sets the _Created_By attribute value.

/// </summary>

/// <value>The _Created_By attribute value.</value>

public string Created_By

{

get

{

return _Created_By;

}

set

{

_Created_By = value;

}

Our parameters declaration is finished now I need to create Business logic layer how I have create it follow same process for add one class file now give name called BLL.CS. Here one point don’t forget this layer will act as only mediator between application layer and data access layer based on this assume what this layer contains. Now I am writing the following BLL.CS(Business Logic layer)

#region Insert UserInformationDetails

/// <summary>

/// Insert UserDetails

/// </summary>

/// <param name="objUserBEL"></param>

/// <returns></returns>

public string InsertUserDetails(BEL objUserDetails)

{

DAL objUserDAL = new DAL();

try

{

return objUserDAL.InsertUserInformation(objUserDetails);

}

catch (Exception ex)

{

throw ex;

}

finally

{

objUserDAL = null;

}

#endregion

Here if you observe above code you will get doubt regarding these

what is

BEL objUserDetails

DAL objUserDAL = new DAL();

and how this method comes

return objUserDAL.InsertUserInformation(objUserDetails);

Here BEL objUserDetails means we already created one class file called BEL.CS with some parameters have you got it now I am passing all these parameters to Data access Layer by simply create one object for our BEL class file

What is about these statements I will explain about it in data access layer

DAL objUserDAL = new DAL();

return objUserDAL.InsertUserInformation(objUserDetails);

this DAL related our Data access layer. Check below information to know about that function and Data access layer

Data Access Layer(DAL)

Data Access Layer contains methods to connect with database and to perform insert,update,delete,get data from database based on our input data

I think it’s to much data now directly I will enter into DAL

Create one more class file like same as above process and give name as DAL.CS

Write the following code in DAL class file

//SQL Connection string

string ConnectionString = ConfigurationManager.AppSettings["LocalConnection"].ToString();

#region Insert User Details

/// <summary>

/// Insert Job Details

/// </summary>

/// <param name="objBELJobs"></param>

/// <returns></returns>

public string InsertUserInformation(BEL objBELUserDetails)

{

SqlConnection con = new SqlConnection(ConnectionString);

con.Open();

SqlCommand cmd = new SqlCommand("sp_userinformation", con);

cmd.CommandType = CommandType.StoredProcedure;

try

{

cmd.Parameters.AddWithValue("@UserName",objBELUserDetails.UserName);

cmd.Parameters.AddWithValue("@Password", objBELUserDetails.Password);

cmd.Parameters.AddWithValue("@FirstName", objBELUserDetails.FirstName);

cmd.Parameters.AddWithValue("@LastName", objBELUserDetails.LastName);

cmd.Parameters.AddWithValue("@Email", objBELUserDetails.Email);

cmd.Parameters.AddWithValue("@PhoneNo", objBELUserDetails.Phoneno);

cmd.Parameters.AddWithValue("@Location", objBELUserDetails.Location);

cmd.Parameters.AddWithValue("@Created_By", objBELUserDetails.Created_By);

cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);

cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

string strMessage = (string) cmd.Parameters["@ERROR"].Value;

con.Close();

return strMessage;

}

catch (Exception ex)

{

throw ex;

}

finally

{

cmd.Dispose();

con.Close();

con.Dispose();

}

#endregion

Here if you observe above functionality I am getting all the parameters by simply creating BELobjBELUserDetails. If we create one entity file we can access all parameters through out our project by simply creation of one object for that entity class based on this we can reduce redundancy of code and increase re usability

Observe above code have u seen this function before? in BLL.CS i said i will explain it later got it inDAL.CS I have created one function InsertUserInformation and using this one in BLL.CS by simply creating one object of DAL in BLL.CS.

Here you will get one doubt that is why BLL.CS we can use this DAL.CS directly into our code behind we already discuss Business logic layer provide interface between DAL and Application layer by using this we can maintain consistency to our application.

Now our Business Logic Layer is ready and our Data access layer is ready now how we can use this in our application layer write following code in your save button click like this

protected void btnsubmit_Click(object sender, EventArgs e)

{

string Output = string.Empty;

if (txtpwd.Text == txtcnmpwd.Text)

{

BEL objUserBEL = new BEL();

objUserBEL.UserName = txtuser.Text;

objUserBEL.Password = txtpwd.Text;

objUserBEL.FirstName = txtfname.Text;

objUserBEL.LastName = txtlname.Text;

objUserBEL.Email = txtEmail.Text;

objUserBEL.Phoneno = txtphone.Text;

objUserBEL.Location = txtlocation.Text;

objUserBEL.Created_By = txtuser.Text;

BLL objUserBLL = new BLL();

Output = objUserBLL.InsertUserDetails(objUserBEL);

}

else

{

Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "Password mismatch" +"');</script>");

}

lblErrorMsg.Text = Output;

}

Here if you observe I am passing all parameters using this BEL(Entity Layer) and we are calling the methodInsertUserDetails by using this BLL(Business Logic Layer)

Now run your applciation test with debugger you can get idea clearly.