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.
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
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.
More Posts
« Previous page -
Next page »