Sukumar Raju's Blog

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

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

Read more at http://www.microsoft.com/visualstudio/en-us/lightswitch

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

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

Recvd email on 27th April 2011.

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:             

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

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

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

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:             }

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.

• http://msdn.microsoft.com/en-us/library/ff647242.aspx
• http://msdn.microsoft.com/en-us/library/ff648941.aspx#trace_email