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);

Sort values in XSL

Distinct Year

<xsl:key name="distinctYear" match="state/elements/element[@type='Custom']/content" use="substring(date,7,10)"></xsl:key>

I have used three attributes here i.e.
1. name :- this is a key name which will use further.
2. match :- this is the route of content data
3. use :- this is attrubute value / name which we want to sort

<select id="ddYear" name="ddYear" onchange="javascript:fnOnchange()">
<xsl:for-each select="state/elements/element[@type='Custom']/content[generate-id() = generate-id(key('distinctYear', substring(date,7,10)))]">
<xsl:sort select="substring(date,7,10)" data-type="number" order="ascending" />
<option>
<xsl:value-of select="substring(date,7,10)"/>
</option>
</xsl:for-each>
</select>

here i used for-each loop to find all elements with distinct values

ex:-
generate-id() :- The generate-id() function returns a string value that uniquely identifies a specified node.
key(Param1,Param2) :- Param1 :- this is key name which assigned
Param2 :- this is the string value which want to be destinected

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Using SqlBulkCopy to Import Excel Spreadsheet Data into SQL Server

Let's take an Excel Workbook with a worksheet, called Data, that contains 1000 rows of nonsense data broken into 2 columns, ID and Data.

I want to copy this data into a SQL Server Database Table, called ExcelData, with the same schema.

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}

GridView Row Edit, Delete and Update

In this Post I am going to explain a simple light weight GridView Edit, Delete, and Update functionality. We are going to apply this functionality on Northwind Database, Employee table.

Following are the screen shots.

First of all I am going to explain about creating a Connection String.Connection String has mainly 3 properties.

DataSource — DataSource is your SQL Server name.
Initial Catalog — Your Database name (Northwind in this case).
Integrated Security – True

Integrated Security will access the SQL Server with the current windows identity established on the Operating System. For more information about Integrated Security refer this. We usually place the connection string in the Web.config file under “Configuration ==> ConnectionStrings” section.

Here is my connection string.

<connectionStrings>
<add name=“NorthwindConnectionString“ connectionString=“Data Source=*****(SQL Server Name);Initial Catalog=NorthWind;Integrated Security=True;“/>
</connectionStrings>

To learn more about different type of connection strings refer this.

Then, after we are done with your connection string in the Web.config file let’s move on to the C# code.

The Following Snippet is to bind GridView.

private void BindGridData() {
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))

{

conn.Open();

using (SqlCommand comm = new SqlCommand(“select E.EmployeeID,E.FirstName,E.LastName,E.Title,E.Country from Employees E”, conn))

{
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}

The First Line using statement allows the programmer to specify when objects that use resources should release them. The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object’s resources. Refer this for Complete Reference.

The following is how we write asp.net GridView web server control code in aspx page. I guess most of the people will be aware of the GridView events. For complete Reference refer this.

In the Code you can see I put AutoGenerateColumns=”false”. That means I have to write column names whatever I want to show to the user. If we put AutoGenerateColumns=”True”, that means u don’t have to write any custom code on your page and user can see each and every column in your dataset or datatable whatever you are binding to your gridview.

It is always good to have some control on these instead of giving up everything to it. I want to hide EmployeeID for the user. But I need it for querying the databse while updating and deleting the user stuff. So

How can we hide a column in a GridView and access it in the Code behind ?

We can do this in several ways!!! , But depends on what column we want to hide. I always follow this method.

Writing a Template Field and making it invisible. In the template field I will Place a label or some controls, So that it’s very easy to access those controls in the code behind by using FindControl method.

Suppose if we are trying to hide primary key of our table, there is actually no need to hide. GridView has a Property called DataKeyNames. We have to give our primary key column name to it. It will do the rest. we can see how we can access that in the code behind in the GridView_Rowupdating Event which I am going to explain later part of this article.

What is TemplateField, ItemTemplate, and EditItemTemplate?

We will use Template Field, whenever we want to define some custom control in the data bound controls like GridView, DataGrid or DataRepeater.

ItemTemplate is the content to display for the items in the TemplateField.

EditItemTemplate as the name itself it is the content to display for the items in the TemplateField when it is in Edit mode. Refer this for complete reference.

Take this simple Snippet.

<asp:TemplateField HeaderText=”LastName”>
<ItemTemplate>

<asp:Label runat=”server” ID=”LastName” Text=’<%#Eval(“LastName”) %>‘ />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat=”server” ID=”txtLastName” Text=’<%#Eval(“LastName”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdLastName” ControlToValidate=”txtLastName” ValidationGroup=”var1″ ErrorMessage=”*” />
</EditItemTemplate>
</asp:TemplateField>

In the above Snippet you are seeing a label in Item Template that means whenever we are showing GridView to the user Label will be visible to the user. If he clicks edit (or) in the edit mode he can be able to see the Textbox.

<asp:BoundField HeaderText=”FirstName” DataField=”FirstName” ReadOnly=”false”/>

The above piece of code will also do the same as label in ItemTemplate and Textbox in the EditItemTemplate. By default all the boundfields will be trasferred as Textboxes in Edit Mode. To avoid this we need to keep the property ReadOnly as false, Then in the edit mode nothing will happenes. It just visible like a label.

The reason I use TemplateField instead of BoundField is that, it is very easy to grab those controls in the codebehind if we user labels or textboxes. In case of the bound field’s we need to check what column it is and need to use index of that cloumn, which is little hard and even in future if we change the column order we have to change the code too.

<asp:GridView ID=”GridView1″ runat=”server” GridLines=”None” AutoGenerateColumns=”false” AlternatingRowStyle-BackColor=”#EEEEEE” EditRowStyle-BorderColor=”Red” onrowcancelingedit=”GridView1_RowCancelling” onrowcommand=”GridView1_RowCommand” onrowdeleting=”GridView1_RowDeleting” onrowediting=”GridView1_RowEditing” onrowupdating=”GridView1_RowUpdating” DataKeyNames=”EmployeeID”>

<Columns>

<asp:TemplateField Visible=”false” HeaderText=”EmployeeID”> <ItemTemplate>

<asp:Label runat=”server” ID=”EmployeeID” Text=’<%#Eval(“EmployeeID”)%>‘ />

</ItemTemplate>

</asp:TemplateField>

<%–<asp:BoundField HeaderText=”FirstName” DataField=”FirstName” />–%>

<asp:TemplateField HeaderText=”LastName”>
<ItemTemplate>
<asp:Label runat=”server” ID=”LastName” Text=’<%#Eval(“LastName”) %>‘ />
</ItemTemplate>

<EditItemTemplate>

<asp:TextBox runat=”server” ID=”txtLastName” Text=’<%#Eval(“LastName”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdLastName” ControlToValidate=”txtLastName” ValidationGroup=”var1″ ErrorMessage=”*” />

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Title”>

<ItemTemplate>
<asp:Label runat=”server” ID=”Title” Text=’<%#Eval(“Title”) %>‘ />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat=”server” ID=”txtTitle” Text=’<%#Eval(“Title”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdTitle” ControlToValidate=”txtTitle” ValidationGroup=”var1″ ErrorMessage=”*” /> </EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Country”>

<ItemTemplate>

<asp:Label runat=”server” ID=”Country” Text=’<%#Eval(“Country”) %>‘ />

</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat=”server” ID=”txtCountry” Text=’<%#Eval(“Country”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdCountry” ControlToValidate=”txtCountry” ValidationGroup=”var1″ ErrorMessage=”*” />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Action”>
<ItemTemplate>
<asp:LinkButton ID=”btnEdit” Text=”Edit” runat=”server” CommandName=”Edit” /> <br />
<asp:LinkButton ID=”btnDelete” Text=”Delete” runat=”server” CommandName=”Delete” />
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID=”btnUpdate” Text=”Update” runat=”server” CommandName=”Update” />
<asp:LinkButton ID=”btnCancel” Text=”Cancel” runat=”server” CommandName=”Cancel” />
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

If you see the Last TemplateField part in the above code, I am using the link buttons for edit, update, delete and cancel. May be you may think why am I using link buttons as we are provided with some command field buttons by GridView.

1) If we use command buttons we have less control on them while doing validations, i.e. while assigning validation groups.

2) Secondly if you want to include a custom field (some button or link) in the same column it’s not possible.

If we use proper command names for the buttons like “EDIT”, “DELETE”, “UPDATE”, “CANCEL” these will also trigger the appropriate GridView Events.

Ok let’s move on to Code behind what we have to do when user clicks Edit, Delete, Update and Cancel

When we click on Edit the OnRowEditing event will be fired. You can see the C# code below

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGridData();
}

In the above code snippet as you see GridViewEditEventArgs will give the row number whatever you are editing by NewEditIndex property. So GridView will put the appropriate row into the Edit Mode by assigning row number to its EditIndex property. Then again we have to call that BindGridData () method to bind data for the textboxes.

So once after done with editing data, if we click update the following method will be triggered. It will call OnRowUpdating Event.

1) The First line is to get the Primary Key of the table using DataKeyNames Property of GridView.

2) The Second line is to access the value of the invisible column.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string s = GridView1.DataKeys[e.RowIndex].Value.ToString();
Label EmployeeID = GridView1.Rows[e.RowIndex].FindControl(“EmployeeID”) as Label; TextBox LastName = GridView1.Rows[e.RowIndex].FindControl(“txtLastName”) as TextBox; TextBox Title = GridView1.Rows[e.RowIndex].FindControl(“txtTitle”) as TextBox;
TextBox Country = GridView1.Rows[e.RowIndex].FindControl(“txtCountry”) as TextBox; String UpdateQuery = string.Format(“UPDATE Employees SET LastName=’{0}’, Title=’{1}’,Country=’{2}’ WHERE EmployeeID = {3}”,LastName.Text, Title.Text,Country.Text, Convert.ToInt32(EmployeeID.Text));

GridView1.EditIndex = -1;

BindGridData(UpdateQuery);

}

Once we access the required fields in the code behind, we need to update the data in the database and show the updated data to the user. So am calling the method BindGridData method which is overloaded . I am passing Update Query as a parameter to it. You can see that method in the following snippet.

private void BindGridData(string Query)

{

string connectionstring = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
using (SqlCommand comm = new SqlCommand(Query + “;select E.EmployeeID,E.FirstName,E.LastName,E.Title,E.Country from Employees E”, conn))
{
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}

If the user click cancels the following event (OnRowCancelling) will be fired. When you are setting the value of the EditIndex to -1 that means you are exiting from the editmode. So i.e. when user clicks cancel, here we are exiting from the editmode and rebinding the data to the GridView by calling BindGridData method.

protected void GridView1_RowCancelling(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGridData();
}

If the user clicks the Delete button OnRowDeleting event will be fired. The following is the code for that event. Going into that code we are trying to get the primary key of the employee table(EmployeeID) by using GridView DataKeyNames and then pass the Query to the BindGridData method to bind the updated data to the GridView.

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string EmployeeID = GridView1.DataKeys[e.RowIndex].Value.ToString();
string Query = “delete Employee where Employee.EmployeeID = “ + EmployeeID;
BindGridData(Query);
}

Happy Coding .

Validation Expressions

Metacharacter	Match
\	the escape character - used to find an instance of a metacharacter like a period, brackets, etc.
. (period)	match any character except newline
x	match any instance of x
^x	match any character except x
[x]	match any instance of x in the bracketed range - [abxyz] will match any instance of a, b, x, y, or z
| (pipe)	an OR operator - [x|y] will match an instance of x or y
()	used to group sequences of characters or matches
{}	used to define numeric quantifiers
{x}	match must occur exactly x times
{x,}	match must occur at least x times
{x,y}	match must occur at least x times, but no more than y times
?	preceding match is optional or one only, same as {0,1}
*	find 0 or more of preceding match, same as {0,}
+	find 1 or more of preceding match, same as {1,}
^	match the beginning of the line
$	match the end of a line

POSIX Class	Match
[:alnum:]	alphabetic and numeric characters
[:alpha:]	alphabetic characters
[:blank:]	space and tab
[:cntrl:]	control characters
[:digit:]	digits
[:graph:]	non-blank (not spaces and control characters)
[:lower:]	lowercase alphabetic characters
[:print:]	any printable characters
[:punct:]	punctuation characters
[:space:]	all whitespace characters (includes [:blank:], newline, carriage return)
[:upper:]	uppercase alphabetic characters
[:xdigit:]	digits allowed in a hexadecimal number (i.e. 0-9, a-f, A-F)

Character class	Match
\d	matches a digit, same as [0-9]
\D	matches a non-digit, same as [^0-9]
\s	matches a whitespace character (space, tab, newline, etc.)
\S	matches a non-whitespace character
\w	matches a word character
\W	matches a non-word character
\b	matches a word-boundary (NOTE: within a class, matches a backspace)
\B	matches a non-wordboundary

• \
  The backslash escapes any character and can therefore be used to force characters to be matched as literals instead of being treated as characters with special meaning. For example, '\[' matches '[' and '\\' matches '\'.
• .
  A dot matches any character. For example, 'go.d' matches 'gold' and 'good'.
• { }
  {n} ... Match exactly n times
  {n,} ... Match at least n times
  {n,m} ... Match at least n but not more than m times
• [ ]
  A string enclosed in square brackets matches any character in that string, but no others. For example, '[xyz]' matches only 'x', 'y', or 'z', a range of characters may be specified by two characters separated by '-'. Note that '[a-z]' matches alphabetic characters, while '[z-a]' never matches.
• [-]
  A hyphen within the brackets signifies a range of characters. For example, [b-o] matches any character from b through o.
• |
  A vertical bar matches either expression on either side of the vertical bar. For example, bar|car will match either bar or car.
• *
  An asterisk after a string matches any number of occurrences of that string, including zero characters. For example, bo* matches: bo, boo and booo but not b.
• +
  A plus sign after a string matches any number of occurrences of that string, except zero characters. For example, bo+ matches: boo, and booo, but not bo or be.
• \d+
  matches all numbers with one or more digits
• \d*
  matches all numbers with zero or more digits
• \w+
  matches all words with one or more characters containing a-z, A-Z and 0-9. \w+ will find title, border, width etc. Please note that \w matches only numbers and characters (a-z, A-Z, 0-9) lower than ordinal value 128.
• [a-zA-Z\xA1-\xFF]+
  matches all words with one or more characters containing a-z, A-Z and characters larger than ordinal value 161 (eg. ä or Ü). If you want to find words with numbers, then add 0-9 to the expression: [0-9a-zA-Z\xA1-\xFF]+

Typical examples

• (bo*)
  will find "bo", "boo", "bot", but not "b"
• (bx+)
  will find "bxxxxxxxx", "bxx", but not "bx" or "be"
• (\d+)
  will find all numbers
• (\d+ visitors)
  will find "3 visitors" or "243234 visitors" or "2763816 visitors"
• (\d+ of \d+ messages)
  will find "2 of 1200 messages" or "1 of 10 messages"
• (\d+ of \d+ messages)
  will filter everything from the last occurrence of "2 of 1200 messages" or "1 of 10 messages" to the end of the page
• (MyText.{0,20})
  will find "MyText" and the next 20 characters after "MyText"
• (\d\d.\d\d.\d\d\d\d)
  will find date-strings with format 99.99.9999 or 99-99-9999 (the dot in the regex matches any character)
• (\d\d\.\d\d\.\d\d\d\d)
  will find date-strings with format 99.99.9999
• (([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+))
  will find all e-mail addresses