Sukumar Raju's Blog

Configuring WCF service to utilise Enterprise Library: Logging application to log data to database

sukumarraju — Mon, 07 Nov 2011 18:22:45 GMT

This article walks through the 'Enterprise Library: Logging application' configuration process to log data to database using WCF service.

Note that regardless of Web service type i.e., SOAP or RESTful the configuration process is same.

Technical environment:-

Visual Studio 2010 with .NET 4.0
Enterprise library 5.0
WCF

ASP.NET Web application on Client end.

Open Service Web.config using Enterprise library configuration tool

Open the Service Web configuration file using Enterprise library configuration tool, which can be found at C:\Program Files\Microsoft Enterprise Library 5.0\Bin [assuming that you chosen C: drive for Enterprise library installation process]

[Image: Enterprise library configuration tool location]

Note: Make sure that Service Web.config contains database connection string in connectionStrings section.

Step 1: Open Enterprise library configuration tool

Below screenshot shows the configuration tool after opening the service web.config file.

[Service web.configuration with in Enterprise Library configuration tool]

Step 2: _Choosing the Default database instance with in Database settings section

Choose the Default Database Instance from drop down list, in this example Logging is chosen as default one as shown below.

[Default Database Instance]

Step 3: Add Logging block block

Choose 'Logging Settings' to add logging application block from Blocks menu as shown below screenshot.

[Add_Logging_Block]

Step 4: Expand Logging Settings section

In order to configure Logging block, expand logging settings by clicking the icon on the left hand side as marked in red below image.

[Expand_Logging_Section_Image]

Step 5: Add Database Trace listener

From Logging Target Listeners selecting the + opens 'Add Logging Target Listeners'.

Choose 'Add Database Trace Listener' from context menu that is opened from
'Add Logging Target Listeners' as shown below screenshot.

[Add-database-trace-listener]

Step 6: Choose Database Trace Listener as default listener

Make sure that 'Database Trace listener' is chosen as default listener in Logging settings --> General section as shown in below image.

[Choose-db-trace-listener]

Note:- By selecting right mouse button on particular configuration section it is feasible to Validate particular section as shown in below image.

[Configuration-Sections-Validate]

Step 7: Enterprise library application block DLL references

Make sure that below mentioned references from Enterprise library API are added to
the service WCF service as shown in below image.

These DLLs are available from Enterprise Library installation at

C:\Program Files\Microsoft Enterprise Library 5.0\Bin

[Required DLLs]

Step 8: Add required 'using' statements

   1: using Microsoft.Practices.EnterpriseLibrary.Logging;

   2: using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

   3: using Microsoft.Practices.EnterpriseLibrary.Logging.ExtraInformation;

   4: using Microsoft.Practices.EnterpriseLibrary.Logging.Filters;

   5: using Microsoft.Practices.EnterpriseLibrary.Data;

   6: using System.Data;

Step 9: Create a method that utilises Logging application block to log data to database.

Create a private LogWriter variable as below;

   1: private LogWriter writer = EnterpriseLibraryContainer.Current.GetInstance<LogWriter>();

Logger method can be similar to the one below or it can have more parameters.

   1: ///Create a logEntry object and write to database

   2: public void  DbLogger(string _Title, string _Message, string _MachineName)

   3: {

   4:     LogEntry logEntry = new LogEntry();

5:

   6:     logEntry.Title = _Title;

   7:     logEntry.Message = _Message;

   8:     logEntry.MachineName = _MachineName;

   9:     logEntry.TimeStamp = DateTime.Now;

10:

  11:     writer.Write(logEntry);

  12: }

Step 10: Test the functionality with a client instance

   1: protected void Page_Load(object sender, EventArgs e)

   2:         {

   3:             //Create an instance of client proxy

   4:             Service1Client client = new Service1Client();

   5:             client.Open();

6:

   7:             try

   8:             {

   9:                 client.DbLogger("Logger service consumer", "Testing the Logger db functionality", "My machine");

  10:             }

11:

  12:             catch (Exception ex)

  13:             {

  14:                 Response.Write(ex.InnerException.Message.ToString());

  15:             }

  16:         }

Running the client app with above should log data to Logging database, which can be installed from scripts provided with Hands-on-Labs referenced below.

Creating Logging database

Note that Logging database can be created by running the SQL script provided with Enterprise Library 5.0 Hands on labs, which can be downloaded at
http://www.microsoft.com/download/en/details.aspx?id=6932

Reference

http://msdn.microsoft.com/en-us/library/ff632023.aspx

http://www.microsoft.com/download/en/details.aspx?id=6932

http://msdn.microsoft.com/en-us/library/ff650510.aspx

Visual Studio 2011 LightSwitch released

sukumarraju — Wed, 27 Jul 2011 20:02:00 GMT

VS LightSwitch 2011 is released today to get the development/deployment process up and running in few minutes..

Multi select List Box ‘OR’ search using Stored procedure

sukumarraju — Wed, 20 Jul 2011 21:19:26 GMT

It is common requirement to allow multiple List Items selection and selecting the ‘Search’ button returns all the records where each List Item Text matches with record as explained below.

Example: Multiple selection List Box

User Interface

Selecting the Button should return all records WHERE VB.NET ‘OR’ ASP.NET ‘OR’ ‘VBA’ matches using IN clause as this is OR search rather than AND search.

Database:

Say ‘Technology’ column consists of below rows in database table.

Search results:- Search should return all the 5 rows from above table.

Lets explore further to achieve the above requirement using Stored procedure.

I. ASP.NET

<asp:ListBox ID="ddlTechnology" runat="server"

SelectionMode="Multiple">
<asp:ListItem Value="" Text="Select single or multiple Systems" />
<asp:ListItem Value="ASP.NET" Text="ASP.NET" />
<asp:ListItem Value="VB.NET" Text="VB.NET" />
<asp:ListItem Value="VBA" Text="VBA" />
</asp:ListBox>

II. In code behind build a string with selected List Items Text delimited with a comma as below.

//Loop through List Items list and add selected items
// with comma in between
foreach (ListItem item in ddlTechnology.Items)
{
  if (item.Selected)
  {
  selectedTechnologies +=  "," + item.Text.Trim();
  selectedTechnologiesList.
Add(ddlTechnology.Items.IndexOf(item));
            }
        }
if (selectedTechnologies.Length != 0)
  {
selectedTechnologies = 
selectedTechnologies.Substring(1);
        }
//Store in session
Session["Technologies"] = selectedTechnologies;

//In this example selectedTechnologies String 
//consists of "VB.NET,ASP.NET,VBA"

III. Pass to Stored procedure as parameterized query

/// <summary>Pass search parameters to Stored procedure and ///return DataTable
/// </summary>
/// <returns>DataTable</returns>
private DataTable Search()
    {
string procedure = "spSearchTechnologies";

        SqlConnection con = new SqlConnection(ConnectionString);
//DataTable 
DataTable dtTechnologies = new DataTable();
try
{
SqlCommand cmd = new SqlCommand(procedure, con);
cmd.CommandType = CommandType.StoredProcedure;
//Pass search criteria with parameters
cmd.Parameters.AddWithValue("@Technologies", 
Convert.ToString(Session["Technologies"]));
//Data adapter
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//fill the data table with results
adapter.Fill(dtTechnologies);
//Display number of records returned
if (dtTechnologies.Rows.Count != 0)
{
lblRecCount.Text = "Total Number of records returned:" 
+ " " + dtTechnologies.Rows.Count.ToString() + "\n";
}
else
{
lblRecCount.Text = "No records matched your search criteria";
}
}

catch (Exception ex)
{
lblRecCount.Text = ex.InnerException.Message;
}

finally
{
if (con != null)
{
con.Close();
}
}
//return the data table
return dtTechnologies;
}
#endregion

IV. Stored Procedure source

ALTER PROCEDURE [dbo].[spSearchTechnologies]
(
	--parameters
	@Technologies nvarchar(100)= NULL;
	
)
AS BEGIN

If @Technologies	IS NOT NULL AND 
Len(@Technologies)=0 Set @Technologies  = NULL

--Select
SELECT id,technologies	
FROM technology t 
WHERE

--StringSplit is T-SQL function that splits passed
--string into separate ones where COMMA (,) appears
--StringSplit function source is copied below
(@Technologies IS NULL OR t.technology
COLLATE DATABASE_DEFAULT IN (SELECT technology
FROM dbo.StringSplit(@Technologies,',') t1,
technology t WHERE 
CHARINDEX(t1.items,t.technology) > 0 ))
)
ORDER BY t.technology

END

V. StringSplit T-SQL Function

FUNCTION [dbo].[StringSplit](@String varchar(8000),
@Delimiter char(1))      
returns @temptable TABLE (items varchar(8000))      
as      
begin      
declare @idx int      
declare @slice varchar(8000)      
     
select @idx = 1      
if len(@String)<1 or @String is null  return      
     
while @idx!= 0      
begin      
set @idx = charindex(@Delimiter,@String)      
if @idx!=0      
set @slice = left(@String,@idx - 1)      
else      
set @slice = @String      
                
if(len(@slice)>0) 
insert into @temptable(Items) values(@slice)      
 
set @String = right(@String,len(@String) - @idx)      
if len(@String) = 0 break      
end  
return      
end

Thats it. Search returns DataTable object with accurate results.

There are number of approaches to handle the requirement, one of the good resources that I come across is at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Microsoft Community contributor award

sukumarraju — Mon, 30 May 2011 20:16:53 GMT

Good to enhance knowledge by sharing and being awarded ‘Microsoft Community contributor award 2011’.

Recvd email on 27th April 2011.

Exporting DataTable to CSV file using Binary order markup

sukumarraju — Tue, 05 Apr 2011 11:37:32 GMT

Exporting Data from DataTable object to .CSV is discussed here.

The Issue

While working with european characters, exporting data into .csv file (Excel 2003) replacing eastern european characters with funny characters. Due to lack of support in Excel 2003.

Resolution

In order to resolve the issue, it is required to use BOM(Binary Order markup). The BOM gives the producer of the text a way to describe the text stream's endianness to the consumer of the text without requiring some contract or metadata outside of the text stream itself.

Excel 2003 cannot recognize the encoding correctly, BOM can help it recognize.

   1: System.Text.StringBuilder sb = new System.Text.StringBuilder();

   2: foreach (DataColumn col in dtResults.Columns)

   3:             {

   4:                 sb.Append(col.ColumnName.ToUpper() + ",");

   5:             }

6:

   7:             sb.Remove(sb.Length - 1, 1);

   8:             sb.Append(Environment.NewLine);

9:

  10:             foreach (DataRow row in dtResults.Rows)

  11:             {

  12:                 for (int i = 0; i < dtResults.Columns.Count; i++)

  13:                 {

  14: sb.Append(row[i].ToString().Replace("\n", " ")

  15: .Replace("\n\r", ";").Replace("\r", " ").Replace(",", ";").Replace("0", "0") + ",");

  16:                 }

17:

  18:                 sb.Append(Environment.NewLine);

  19:             }

  20:             Response.Clear();

  21:             Response.ContentType = "text/csv";

  22:             Response.AppendHeader("Content-Disposition", "attachment; filename="

  23:             + fileName + ".csv");

24:

  25:             byte[] BOM = { 0xEF, 0xBB, 0xBF };

  26:             Response.BinaryWrite(BOM);

27:

  28:             Response.Write(sb.ToString());

  29:             Response.End();

30:

Encrypt and Decrypt connectionStrings section in Web.config using ASPNET_REGIIS utility

sukumarraju — Fri, 18 Feb 2011 16:50:00 GMT

Encrypting and decrypting connectionStrings section progrmatically is explained in this article from my blog.

Using aspnet_regiis.exe utility from Microsoft .NET framework it is quite easy and straight forward to encrypt and decrypt connectionStrings section in Web.config to protect sensitive data.

1. Open Visual studio command prompt window from Programs

2. Navigate to the folder where the application stored with in File system

3. Utilising aspnet_regiis.exe utility use below shown command to encrypt connectionStrings section.

c:/>aspnet_regiis -pef connectionStrings .

In the above command P stands for protection, E stands Encryption and F file system. . [DOT] for current location

Note: Make sure to navigate inside the website/web application project in file system.

That is it. ConnectionStrings section gets encrypted using RSA pubic key encryption to encrypt and decrypt data.

Note that in order to decrypt the same section use >aspnet_regiis –def connectionStrings .

References

http://msdn.microsoft.com/en-us/library/k6h9cz8h%28v=VS.90%29.aspx

Video: http://msdn.microsoft.com/en-us/security/bb977434

Access controls with in Change Password control

sukumarraju — Sun, 13 Feb 2011 12:03:21 GMT

In order to access controls with in Change Password control

1. Change the control to Template by going to design mode then smart tag
2. Choose Convert to Template

//Access the Cancel button which is with in ChangePassworTemplateContainer as below, it would be similar for other controls

//Get a reference to button, which is in ChangePasswordTemplateContainer
Button btnCancel = (Button)
ChangePassword1.ChangePasswordTemplateContainer.FindControl("Control_ID”);
            if (btnCancel != null)
            {
               //Action to perform
            }

References

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.changepassword.aspx

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/login/changepassword.aspx

Find Control with in LoginView control

sukumarraju — Mon, 31 Jan 2011 15:51:10 GMT

After going thorough repeated number of posts and responding at ASP.NET security forms this article has been published. Note that it is required to use LoginView.FindControl method to get reference of any control with in LoginView as discussed below.

In below snippet content page consists of Login control with in LoginView and content page inherits MasterPage.

   1: <%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master"

   2: AutoEventWireup="true"

   3: CodeFile="Default.aspx.cs" Inherits="_Default" %>

4:

   5: <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">

6:

   7: </asp:Content>

   8: <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1"

   9: Runat="Server">

  10:     <asp:LoginView id="LoginView1" runat="server">

  11:         <AnonymousTemplate>

  12:                     Welcome: Guest

  13:             <asp:Login  ID= "Login1" runat="server">

  14:             </asp:Login>

  15:                 </AnonymousTemplate>

  16:                 <LoggedInTemplate>

  17:                     Welcome:

  18:                     <asp:LoginName ID="LoginName1" runat="server" />

  19:                 </LoggedInTemplate>

  20:     </asp:LoginView>

  21: </asp:Content>

Note that it is not feasible to get a reference of any control with in LoginView directly. It is required to find control using LoginView.FindControl method as shown below. It is important to note that the controls with in Logged In template of LoginView are not accessible until the developer is authenticated.

   1: protected void Page_Load(object sender, EventArgs e)

   2:     {

   3:    // Gets a reference to content place holder

   4: ContentPlaceHolder cp = (ContentPlaceHolder) Master.FindControl

   5: ("ContentPlaceHolder1");

6:

   7:    if (cp != null)

   8:    {

   9:    //Find LoginView1 control with in Content place holder

  10:    LoginView lv = (LoginView)cp.FindControl("LoginView1");

11:

  12:    if (lv != null)

  13:    {

  14:    //Find LoginName control with in AnonymousTemplate of LoginView control

  15:    //Note that in order to find the control in LoggedIn template developer

  16:    //should have been authenticated ELSE it fails

  17:    LoginName ln = (LoginName)lv.FindControl("LoginName1");

18:

  19:    if (ln != null)

  20:    {

  21:     Response.Write("LoginName control found");

  22:    }

  23:   }

  24:  }

25:

Few examples with other controls below:

   1: //Finding DIV with in LoginView

   2: HtmlContainerControl div = (HtmlContainerControl) LoginView2.FindControl

   3: ("divGrid");

   4:         if (div != null)

   5:         {

   6:             div.Visible = false;

   7:         }

References

http://msdn.microsoft.com/en-us/library/xxwa0ff0(v=VS.85).aspx

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.loginview.findcontrol.aspx

Replace DBNull with Zero while exporting to CSV or Excel file

sukumarraju — Fri, 03 Dec 2010 12:38:40 GMT

Exporting data from DataTale to CSV file is explained in this article.

When the data contains NULL values, rows in CSV or Excel file prints nothing. In order to replace DBNull with zero or desired text it is feasible to check the row value and assign desired text as shown below.

   1: //Loop through data rows

   2: foreach (DataRow row in dtResults.Rows)

   3:             {

   4:                 for (int i = 0; i < dtResults.Columns.Count; i++)

   5:                 {

   6:                    //check if the row value is DBNull

   7:                     if (row[i] is DBNull)

   8:                     {

   9:                        \\Assigning zero to row value

  10:                         row[i] = "0";

  11:                     }

  12:                     context.Response.Write(row[i].ToString().Replace("\n", " ") + “,”);

  13:                 }

  14:                 context.Response.Write(Environment.NewLine);

  15:             }

Email exception stack using Logging Application Block: Email Trace Listener

sukumarraju — Sat, 21 Aug 2010 00:49:22 GMT

In application trouble shooting and bug fixing exception stack provides relevant information to handle the exception in efficient manner.

Prerequisites

Note that in order to implement consistent exception handling throughout the application Enterprise Library: Exception Handling Block configuration and logging the exceptions to windows events are discussed in the article ‘Enterprise Library:Exception Handling Application Block’ If it is the first time you are using application blocks from Enterprise Library I would suggest to go through this article first before configuring email trace listener.

Note that this article assumes that you have already added required Enterprise Library application blocks DLLs references to your web application, which is explained here.

Introduction

This article discusses configuring Email Trace Listener from Enterprise Library: Logging Application Block to email exception stack to the configured email address. Email trace listener writes an email message, formatting the output with an ILogFormatter.

When there is an exception with an application relevant person must be notified. One of the best ways is to send email to a distribution mailbox using the email trace listener.

1. Configuring Email Trace Listener

Create an Email Trace Listener from Trace listeners section by selecting context menu. Enterprise Library configuration tool opens Email trace listener to configure as shown below.

ToAddress, FromAddress, SmtpServer and SmtpPort properties allow to configure the EmailTraceListener class.

In order to send an email it is required to know SmtpServer and SmtpPort. These properties initially default to localhost IP address and 25 respectively. Make sure to configure mandatory properties SmtpServer, SmtpPort, ToAddress and ToAddress. Note that SubjectLinerHeader and SubjectLineEnder are optional properties and they represent email subject line suffix and prefix.

Picture 1:- Email Trace Listener configuration

2. Add Email Trace Listener to Category Sources

Create new Trace Listener reference under desired category source by selecting the context menu from the Category Source and choose Email TraceListener as referenced Trace Listener.

In below configuration Email Trance Listener is the referenced trace listener for category source Error. When an error occurs with in the application an email is sent to configured address using Email trace listener.

Note that using Enterprise Library application blocks configuration tool updates Web.config

Email trace listener configuring looks similar to the one below.

   1: <listeners>

   2:             <add toAddress="xyz@xyz.com"

   3:                 fromAddress="xyz@xyz.com"

   4:                 subjectLineStarter=""

   5:                 subjectLineEnder=":"

   6:                 smtpServer="SMTP Server"

   7:                 smtpPort="SMTP Port No"

   8:                 formatter="Text Formatter"

   9:                 listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.

  10:                 Configuration.EmailTraceListenerData, Microsoft.Practices.

  11:                 EnterpriseLibrary.Logging,

  12:                 Version=4.1.0.0,

  13:                 Culture=neutral, PublicKeyToken=31bf3856ad364e35"

  14:                 traceOutputOptions="None"

  15:                 filter="All"

  16:                 type="Microsoft.Practices.EnterpriseLibrary.Logging.

  17:                 TraceListeners.EmailTraceListener,

  18:                 Microsoft.Practices.EnterpriseLibrary.Logging,

  19:                 Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

  20:                 name="Email TraceListener"/>

  21: </listeners>

Category Sources configuration looks similar to the one below

   1: <categorySources>

   2:             <add switchValue="All" name="General">

   3:                 <listeners>

   4:                     <add name="Email TraceListener"/>

   5:                 </listeners>

   6: </categorySources>

Conclusion:- That is it. Enterprise Library: Logging application block Email trace listener configuration is done. When the exception is caught your web application will send an email with exception stack.

Testing

In order to test this copy the below code snippet in Page_Load or your desired event, making sure that you are using correct exception policy configured. Note that exception policy configuration is discussed in step by step article Exception Handling Application Block with Logging Application Block. You should receive an email message with exception stack just in few seconds.

References:-

Hands-On Labs are best resources to experiment further. Note that these labs consists of step by step tutorial for all Enterprise Library Application Blocks. Choose the Logging Application Block, which is relevant to this article.

Role based authorization using LoginView control

sukumarraju — Wed, 28 Jul 2010 15:16:52 GMT

This article disusses LoginView control, which is part of System.Web.UI.WebControls namespce , can be found with in Visual studio under ASP.NET Login tab.

LoginView control allows to display different content based on user's authentication status and role. This control contains three templates which can be used based on application requirement as outlined below.

AnonymousTemplate:- Specifies the template to display to users who are not logged into the web site. In other words unauthenticated users. Authenticated users or logged in Users will never see this template.

LoggedInTemplate:- Displays the template for authenticated users or loggedIn users.

RoleGroups:- Specifies the template to display to logged in users who are members of roles with defined role-group templates as shown below. Here content templates are bind to particular roles or role.

   1: <asp:LoginView ID="MasterLoginView" runat="server">

   2:                 <AnonymousTemplate>

   3:                     Welcome: Guest

   4:                 </AnonymousTemplate>

   5:                 <LoggedInTemplate>

   6:                     Welcome:

   7:                     <asp:LoginName ID="MasterLoginName" runat="server" />

   8:                 </LoggedInTemplate>

   9:             </asp:LoginView>

AnnonymousTemplate

In the above LoginView control content with in AnonymousTemplate is shown to unauthenticated users with a message Welcome:Guest as specified with in template. The above snippet usually used on Master pages in combination of Login.aspx

LoggedInTemplate

Contents or controls with in LoggedIn Template are shown to all users who are logged into website. In other words ‘to all authenticated users’.

In the above example LoginName control is used, so the message Welcome :<LoginName> is shown to logged users.

RoleGroups

   1: <asp:LoginView ID="lvUserRecord" runat="server">

   2:             <RoleGroups>

   3:                 <asp:RoleGroup Roles="Admin">

   4:                     <ContentTemplate>

   5:                         <asp:Button ID="btnEditUser" runat="server" Text="Edit" OnClick="btnEditUser_Click" />

   6:                         <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click"

   7:                             OnClientClick="return confirm('Are you sure you want to update this record?');" />

   8:                         <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />

   9:                     </ContentTemplate>

  10:                 </asp:RoleGroup>

  11:             </RoleGroups>

  12:         </asp:LoginView>

  13:         <asp:Button ID="btnPrint" runat="server" Text="Print" OnClick="btnPrint_Click" />

  14:         <asp:Button ID="btnDownLoad" runat="server" Text="DownLoad" OnClick="btnDownLoad_Click" />

Example:- It is required to display a button or set of buttons to authorized users only.

Solution:- Can be called as control based authorization, where authorized users with a role can view the button controls that are specified with in RoleGroup contentTemplate.

How it works?
When the above LoginView is used on a page, button controls (Edit, Update and Cancel) are shown to only users who are with in Admin role. When the other users get to the page these button controls are hidden consequently the functionality is limited/authorized to users in Admin role only.

LoginView control provides granular level of authorization with minimum effort from developer. It is quite feasible to implement content and control level authorization in web application.

Resources

Custom data method - GridView Sorting and Paging

sukumarraju — Wed, 21 Jul 2010 18:05:24 GMT

One of the common task when you use custom data methods is sorting and paging. There is an article here on sorting and paging using custom data. Based on the feed back received from developers to elaborate the source code and better explain this article is written.

Note that custom method expected to return a data table object in this approach. In this article Search() is custom method that returns a DataTable object.

   1: #region GridView Page Index Changing

2:

   3:   /// <summary>

   4:   /// Handle Gridview paging event

   5:   /// and bind Search results data to GridView

   6:   /// </summary>

   7:   /// <param name="sender"></param>

   8:   /// <param name="e"></param>

   9:   protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

  10:   {

  11:        GridView1.PageIndex = e.NewPageIndex;

  12:       //Bind search reuslts

  13:       GridView1.DataSource = SortDataTable(Search() as DataTable, true);

  14:       GridView1.DataBind();

15:

  16:   }

17:

  18:   #endregion

19:

  20:   #region Properties SortDirection and SortExpresssion

21:

  22:   /// <summary>

  23:   /// property GridView Sort Direction

  24:   /// </summary>

  25:   private string GridViewSortDirection

  26:   {

  27:       get { return ViewState["SortDirection"] as string ?? "ASC"; }

  28:       set { ViewState["SortDirection"] = value; }

29:

  30:   }

  31:   /// <summary>

  32:   /// GridView sort expression property

  33:   /// </summary>

  34:   private string GridViewSortExpression

  35:   {

  36:       get { return ViewState["SortExpression"] as string ?? string.Empty; }

  37:       set { ViewState["SortExpression"] = value; }

  38:   }

  39:   #endregion

40:

  41:   #region SortDirection method

  42:   /// <summary>

  43:   /// Get current sortDirection and switch between

  44:   /// ascending and decending order

  45:   /// </summary>

  46:   /// <returns></returns>

  47:   private string GetSortDirection()

  48:   {

  49:       switch (GridViewSortDirection)

  50:       {

  51:           case "ASC":

  52:               GridViewSortDirection = "DESC";

  53:               break;

  54:           case "DESC":

  55:               GridViewSortDirection = "ASC";

  56:               break;

  57:       }

58:

  59:       return GridViewSortDirection;

  60:   }

61:

  62:   #endregion

63:

  64:   #region SortDataTable

65:

  66:   /// <summary>

  67:   /// Sort Data Table when Page Index changing

  68:   /// </summary>

  69:   /// <param name="dataTable"></param>

  70:   /// <param name="isPageIndexChanging"></param>

  71:   /// <returns></returns>

72: protected DataView SortDataTable(DataTable dataTable,

bool isPageIndexChanging)

  73:   {

  74:       if (dataTable != null)

  75:       {

76:

  77:           DataView dataView = new DataView(dataTable);

  78:           if (GridViewSortExpression != string.Empty)

  79:           {

  80:               if (isPageIndexChanging)

  81:               {

  82:                   dataView.Sort = string.Format("{0} {1}", 
                        GridViewSortExpression, GridViewSortDirection);

83:

  84:               }

  85:               else

  86:               {

  87:                   dataView.Sort = string.Format("{0} {1}", 
                        GridViewSortExpression, GetSortDirection());

88:

  89:               }

90:

  91:           }

  92:           return dataView;

  93:       }

  94:       else

  95:       {

  96:           return new DataView();

  97:       }

98:

99:

 100:   }

 101:   #endregion

 102:

 103:

 104:   #region GridView columns sorting

 105:

 106:

 107:   /// <summary>

 108:   /// Handle page sorting

 109:   /// </summary>

 110:   /// <param name="sender"></param>

 111:   /// <param name="e"></param>

 112:   protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)

 113:   {

 114:

 115:       //Get sortExpression

 116:       GridViewSortExpression = e.SortExpression;

 117:       int pageIndex = GridView1.PageIndex;

 118:       //Bind GridView with search results

 119:       GridView1.DataSource = SortDataTable(Search() as DataTable, false);

 120:       GridView1.DataBind();

 121:       //Set the page Index

 122:       pageIndex = GridView1.PageIndex;

 123:

 124:

 125:   }

 126:

 127:   #endregion

Let me know any comments. I make sure to better explain or resolve any bugs related to this article.

Retrieving and writing into BLOB type column

sukumarraju — Sat, 23 Jan 2010 18:21:34 GMT

This article explains ‘how to store and retrieve BLOB (Binary Large Objects) type data into BLOB data type column in database table.

BLOB is collection of ‘binary data’ stored as a single entity in database. BLOBs are session objects or images or audio files or multi media objects.

Note that when session object is stored into BLOB type column, you need to Serialize the session object and insert into database as shown in below source code. Serialization is the process of converting an object or data structure into sequence of bytes.

Retrieve BLOB type data

Note that in this example session object retrieval from BLOB type column is explained.

Below is the source code to read BLOB type data from database column. Note that ORACLE is the database management system in this example.

// <summary>Reading blob Type from Database example</summary>
// <param name="sender"></param>
// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings
[Your ConnString key].ConnectionString;

//Connection object
OracleConnection conn = new OracleConnection(oradb);
//Open connection
conn.Open();

//Command 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM YourTable";

//Error Handler
try
{
//OracleDataReader object
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
//Get your blob type column which is Index 4 
OracleBlob blob = dr.GetOracleBlob(4);
//Test 
Response.Write(blob.Value);
}

catch( Exception ex )
{
throw new Exception("error Reading data from blob Types Table", ex);
}

//Close connection
conn.Dispose();
}

Insert into BLOB type column

Session object should be serialized before inserting into BLOB type column. In the source code below how to Serialize a session object and then inserting into BLOB type column is shown.

// <summary>
//on button click event to insert values 
//into BLOB Type column </summary>
// <param name="sender"></param>
// <param name="e"></param>
protected void button2_Click(object sender, EventArgs e)
{
//Connection string
string oradb = Read from web.config
//Connection 
OracleConnection conn = new OracleConnection(oradb);
//Open connection
conn.Open();

// Start a transaction
OracleTransaction txn = conn.BeginTransaction();
//create Command object 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

// Lock the result set using the "FOR UPDATE" clause
// Running this SQL Statement in SQL editor locks the row, 
causing code Step out

cmd.CommandText = "SELECT object FROM your Table 
WHERE <condition> FOR UPDATE ";

//Error Handler
try
{
//Reader
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
		
//Read BLOB from DataReader
OracleBlob blob = dr.GetOracleBlob(0);

//byte array
byte[] bytes;

	
//get the byte array
MemoryStream memStream1 = new MemoryStream();
BinaryFormatter formatter = new BinaryFormatter();

//Serializing the object
formatter.Serialize(memStream1, Your object);
bytes = memStream1.GetBuffer();
memStream1.Close();
//write blob object to Database 
blob.Write(bytes, 0, bytes.Length);

//Commit data and close connection
txn.Commit();
Response.Write("updated to new data");
}

catch( Exception ex )
{
throw new Exception("error writing session data to Databse", ex);
}
conn.Close();
}

NB:- To improve performance it is highly advisable to use ODP.NET (Oracle Data Provider for .NET)

//ODP.NET namespce
using Oracle.DataAccess.Types;

References

Reading blob Type from Database example
http://msdn2.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx

Transaction Lock with Blobs example http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/rlock/index.html

Serialization Example:
http://progtutorials.tripod.com/C_Sharp.htm#_Toc65227888

Configuring web application to utilise ASP.NET Application Services database

sukumarraju — Sun, 17 Jan 2010 21:01:07 GMT

In my article Install ASP.NET Application Services database step by step guide is provided to install ASP.NET Application Services database regard less of SQL Server version. Installing / configuring Application services database allows multiple applications to utilise the database to implement application authentication and authorization.

After installation of services database, it is required to configure ASP.NET application to utilise the ASP.NET Services database(called as Membership database as well).

I come across multiple number of repeating postings at www.asp.net/forums with exceptions when post holders try to utilise ASP.NET application services database to authenticate or authorise end users. Common exceptions noted are ‘sql exceptions’ or ‘Invalid end user credentials when tried to use Login control’ or similar exception.

The reason behind most of these exceptions are not configuring web application to utilise SQL Server installed ASP.NET Application Services database. When responding to those repeating postings on asp.net forums i decided to write an article explaining this process instead of repeating the same info multiple times.

This article explains step by step process of configuring ASP.NET Web application to utilise SQL Server installed ASP.NET Application Services database.

Copying and configuring connection String
Copying and configuring Membership, Roles and Profile sections
Using ASP.NET Web application Configuration tool to choose providers

Hope you find it helpful.

Step 1

Copying and configuring connection String

Note that configuration settings in Web.config file are inherited from machine.config file on your machine. In order to configure web application to utilise Application Services database it is required to copy related sections from machine.config file, which is located at

C:\windows\Microsoft.NET\Framework\v2.0.50727\CONFIG

NB:- Make sure that you are not making any changes to your machine.config file.

To Do:- Copy connection string from machine.config file as shown below.

<connectionStrings>
	<add name="LocalSqlServer" 
	connectionString="data source=.\SQLEXPRESS;
	Integrated Security=SSPI;
	AttachDBFilename=|DataDirectory|aspnetdb.mdf;
	User Instance=true" 
	providerName="System.Data.SqlClient"/>
</connectionStrings>

To Do:- Paste above connection string from machine.config into Web.config and

change required properties as shown below.

<add name="<Connection String Name>" 
connectionString="Server=<SQL Server NAME>;
Database=<ASP.NET Application services Database NAME>;
User ID=<user ID>;
Password=<password>"
providerName="System.Data.SqlClient" 
/>

Step 2

Copying and configuring Membership, Roles and Profile sections

Note that depending on your application requirements you need either or combination

or all of these three sections.

Membership
Roles
Profile

To Do:- Copy Membership, Roles and Profile sections from machine.config into Web.config and configure required properties.

<membership>
	<providers>

	<add name="AspNetSqlMembershipProvider"

        type="System.Web.Security.SqlMembershipProvider,
	System.Web, Version=2.0.0.0, Culture=neutral, 
	PublicKeyToken=b03f5f7f11d50a3a" 
	connectionStringName="LocalSqlServer" 
	enablePasswordRetrieval="false" 
	enablePasswordReset="true" 
	requiresQuestionAndAnswer="true" 
	applicationName="/" 
	requiresUniqueEmail="false" 
	passwordFormat="Hashed" 
	maxInvalidPasswordAttempts="5" 
	minRequiredPasswordLength="7" 
	minRequiredNonalphanumericCharacters="1" 
	passwordAttemptWindow="10" 
	passwordStrengthRegularExpression=""/>
	
	</providers>
</membership>

<profile>
	<providers>
	
	<add name="AspNetSqlProfileProvider" 
	connectionStringName="LocalSqlServer" 
	applicationName="/" 
	type="System.Web.Profile.SqlProfileProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	
	</providers>
</profile>

<roleManager>
	<providers>

	<add name="AspNetSqlRoleProvider" 
	connectionStringName="LocalSqlServer" 
	applicationName="/" 
	type="System.Web.Security.SqlRoleProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

	PublicKeyToken=b03f5f7f11d50a3a"/>
	
	</providers>
</roleManager>

To Do:- After copying above sections into Web.config, make sure you modify

minimum required attributes such as name, connectionStringName and

ApplicationName

Note that depending on your application requirements you may modify other attributes mostly in Membership section.

After modifying minimum attributes in Membership, Roles and Profile sections, these sections looks similar as shown below.

<membership defaultProvider="AspNetMembershipProvider">
	<providers>
	<add connectionStringName="<AspNetServices 
	Connectionstring Name from connectionstrings
	section" 
	enablePasswordRetrieval="false" 
	enablePasswordReset="true" 
	requiresQuestionAndAnswer="true" 
	applicationName="<WEB APP NAME>" 
	requiresUniqueEmail="false" 
	passwordFormat="Clear" 
	maxInvalidPasswordAttempts="5" 
	minRequiredPasswordLength="7" 
	minRequiredNonalphanumericCharacters="0" 
	passwordAttemptWindow="10" 
	passwordStrengthRegularExpression="" 
	name="AspNetMembershipProvider"

        type="System.Web.Security.SqlMembershipProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	</providers>
</membership>

<roleManager enabled="true" defaultProvider="AspNetRoleProvider">
	<providers>
	<add connectionStringName="<AspNetServices 
	Connectionstring Name from connectionstrings
	section" 
	applicationName="<ASP.NET application NAME>" 
	name="AspNetRoleProvider" 
	type="System.Web.Security.SqlRoleProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	</providers>
</roleManager>

<profile>
	<providers>
	
	<add name="<ProfileProvider Name>" 
	connectionStringName="<From above connectionStirngs
	section that is pointing to Servies database>" 
	applicationName="/<Your ASP.NET app Name>" 
	type="System.Web.Profile.SqlProfileProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	
	</providers>
</profile>

NB:- Make sure that application Name property is set at all the time as

suggested by Scott Guthrie here

Step 3

Using ASP.NET Web application Configuration tool to choose providers

After adding and modifying required sections as explained above, save your Web.config and configure your web application to utilise ASP.NET Application Services Database as explained below.

To Do:- Start ASP.NET Configuration tool from Website menu (shown below). Note that ASP.NET configuration tool can be initiated from Solution Explorer menu as well.

Selecting ASP.NET Configuration opens Web Site Administration Tool in browser.

To Do:-Select Provider Configuration hyperlink as shown below.

Selecting Provider Configuration navigates to Provider page where you can choose either Single provider or different provider for each feature as shown below.

To Do:- You can choose either of the above options available. For this tutorial choosing ‘Select a different provider each feature’ option. By doing so each providercan choose different data sources.

Selecting Select a different provider for each feature (advanced) hyperlink navigates to next page where you can select a provider for each feature as shown below.

On this screen you can see provider name(from provider section), choose a provider

for each feature i.e., Membership, Roles and Profile and select Ok.

Thats it. You are ready to fly! Configuration is done.

Testing:- In order to make sure your ASP.NET application services database is configured to be used by web application, you can create a new user by selecting Security tab with in Web Site Administration tool, then Create User hyperlink. After creating the user make sure that you see the same information in ASP.NET Application Services database aspnet_Users

References

Populating Drop Down List Selected Item while DetailsView in Edit mode

sukumarraju — Wed, 23 Dec 2009 10:47:38 GMT

Populating Drop down list when DetailsView is in Edit mode is explained in another article Binding Drop down list when DetailsView is in edit mode.

It is common requirement when you are redirecting from Master page to details page where DetailsView control is used to display single record on second page or GridView and DetailsView are used on a single page. When the user selects Edit button to edit data within DetailsView control, it is common requirement to populate the previous value as selected item in drop down list. So that end user can either change previous value in drop down list or stick to current value being populated.

This article explains how to achieve this requirement from code behind.

ASPX

Below is the ASPX code to display country. When DetailsView in in Read-only mode Label control is used to display the relevant record country from “country” database column, when DetailsView is in edit mode controls within EditItemTemplate are used to populate data.

Here Drop Down List control is used to populate list of countries when DetailsView in edit mode.

<asp:TemplateField HeaderText="Country">
 <ItemTemplate>
   <asp:Label ID="lblCountry" runat="server" 
   Text='<%# bind("country")%>' />    
 </ItemTemplate>

 <EditItemTemplate>
    <asp:DropDownList ID="ddlCountry" runat="server"  
       AppendDataBoundItems="True" >
       <asp:ListItem Value="" Text="" />
    </asp:DropDownList>   
 </EditItemTemplate>
</asp:TemplateField>

Code behind

Details view Data Bound event is used to populate drop down list selected item as shown below.

//Populate previous value as selected item
protected void dvUserRecord_DataBound(object sender, EventArgs e)
{
   //check for DetailsView mode is Edit
   if (dvUserRecord.CurrentMode == DetailsViewMode.Edit)
    { 
            
      //Get reference to Country drop down list
    DropDownList ddlCountry = 
    dvUserRecord.FindControl("ddlCountry") as DropDownList;
    
     if (ddlCountry != null)
      {
      //Data binding Drop down list source code here
      //This is discussed in another article 
         
      //Then populate SelectedItem
        
      //Get current record country and set as 
      //selected item in Country drop down list
      DataRowView row = dvUserRecord.DataItem as 
      DataRowView;
      if (row != null)
      {  
         //Get current record country
          ListItem liCountry = ddlCountry.Items.
          FindByText(row["country"].ToString());
      
      //Set as selected List item in Drop down list
        if (liCountry != null)
         {
            ddlCountry.Items.FindByText(row["country"].
            ToString()).Selected = true;
        
           }
         }
      }
     
}

That’s it! you are ready to fly…