Dynamically generate and display QR code Image in ASP.Net

In this article I will explain how to dynamically generate and display barcode image using ASP.Net in C# and VB.Net.

For generating QR Codes I will make use of QRCoder which is an Open Source Library QR code generator.

QR Code Library

You will need to download the QR code library from the following location and open the project in Visual Studio and build it. Once it is build, you can find the DLL in the Debug folder.

Download QRCoder

 

HTML Markup

I have a simple ASP.Net page with a TextBox where the user will type in the barcode to be generated and a Button to trigger. The generated QR code image will be displayed in the Placeholder.

<form id="form1" runat="server">

<asp:TextBox ID="txtCode" runat="server"></asp:TextBox>

<asp:Button ID="btnGenerate" runat="server" Text="Generate" onclick="btnGenerate_Click" />

<hr />

<asp:PlaceHolder ID="plBarCode" runat="server" />

</form>

 

Namespaces

You will need to import the following namespaces.

C#

using QRCoder;

using System.IO;

using System.Drawing;

 

VB.Net

Imports QRCoder

Imports System.IO

Imports System.Drawing

 

 

Generating and displaying QR code image in ASP.Net

The following code is of the Button Click event handler. The Text from the TextBox is passed to the CreateQRCode method of the QRCoder library which returns a Bitmap image.

The Bitmap image is then saved as PNG image in MemoryStream which later is converted to a base64 string and displayed on the page using an Image control.

C#

protected void btnGenerate_Click(object sender, EventArgs e)

{

    string code = txtCode.Text;

    QRCodeGenerator qrGenerator = new QRCodeGenerator();

    QRCodeGenerator.QRCode qrCode = qrGenerator.CreateQrCode(code, QRCodeGenerator.ECCLevel.Q);

    System.Web.UI.WebControls.Image imgBarCode = new System.Web.UI.WebControls.Image();

    imgBarCode.Height = 150;

    imgBarCode.Width = 150;

    using (Bitmap bitMap = qrCode.GetGraphic(20))

    {

        using (MemoryStream ms = new MemoryStream())

        {

            bitMap.Save(ms, System.Drawing.Imaging.ImageFormat.Png);

            byte[] byteImage = ms.ToArray();

            imgBarCode.ImageUrl = "data:image/png;base64," + Convert.ToBase64String(byteImage);

        }

        plBarCode.Controls.Add(imgBarCode);

    }

}

 

VB.Net

Protected Sub btnGenerate_Click(sender As Object, e As EventArgs)

    Dim code As String = txtCode.Text

    Dim qrGenerator As New QRCodeGenerator()

    Dim qrCode As QRCodeGenerator.QRCode = qrGenerator.CreateQrCode(code, QRCodeGenerator.ECCLevel.Q)

    Dim imgBarCode As New System.Web.UI.WebControls.Image()

    imgBarCode.Height = 150

    imgBarCode.Width = 150

    Using bitMap As Bitmap = qrCode.GetGraphic(20)

        Using ms As New MemoryStream()

            bitMap.Save(ms, System.Drawing.Imaging.ImageFormat.Png)

            Dim byteImage As Byte() = ms.ToArray()

            imgBarCode.ImageUrl = "data:image/png;base64," + Convert.ToBase64String(byteImage)

        End Using

        plBarCode.Controls.Add(imgBarCode)

    End Using

End Sub

 

Securing Data With Application Role in SQL Server 2008

This article introduces the fundamental concepts behind SQL Server 2008 Application Role and illustrates its use in securing data and database objects.

1. Introduction

This article introduces the concepts behind SQL Server 2008 Application Role. Besides the concepts, the article presents the following aspects around creating and using application roles.

• Creating Application Role and associating database schema and granting permissions to the application role
• Create a Windows Form Application for illustration of the following
  • Enabling and Disabling Application Role
  • Using cookie (introduced starting with SQL Server 2005) to unset an application role
  • Life Cycle of an Application Role

2. Background

Application role is a database level principal. It may own one or more database schema and be granted specific permissions. The application accesses data with the permissions of the application role irrespective of who is connected to the database. The application role needs to be enabled for it to be active. This provides a level of security for the data and other database objects. Any one wishing to access data using the application as a gateway will be restricted by the application role that lives during the life of the current application instance.

3. Sample Application - An Illustrative Example

The best way to understand the application role is to work with an actual application that uses application role to access the database. So, let me present an actual Windows Forms based application that controls read-only access to the HumanResources.Employee table in the AdventureWorks database. Note that HumanResources is the schema under which the Employee table exists. I used AdventureWorks database, but you may create your own database and table and use it if you like. The sample application I present is simple enough to be modified for your SQL Server instance, database and tables.

3.1 Create an Application Role

Open SQL Server Enterprise Manager console connecting as administrator and expand the AdventureWorks sample database (You may download and install AdventureWorks sample database available online, if you do not already have it on your SQL Server instance). Expand Roles node under Security. Right click the Application Roles node and click New Application Role... menu. The dialog under the General tab to create the role is shown in the figure below:

I have named the role as HRUPDATER. Select the schema as HumanResources and the database schemas owned by the role as db_datareader and db_datawriter. Now, select the Securables tab below the General tab. This displays the dialog for you to select the database object you want to secure and grant necessary permission to theHRUPDATER role on the object. Select the Employee table under HumanResources schema as shown in the dialog below by clicking the Search... button. Then check the Select permission under the Grant column.

Close the dialogs by clicking Ok button and verify that the HRUPDATER role is created under the Application Role node.

Create a user under AdventureWorks database from an existing login. For this example, I created a login calledsqluser and used the same name to create a database user. Note that this user does not have any permission at all on any of the AdventureWorks database objects. Add this login as a user under the master database, name it as sqluser and give it execute permission for the two system stored procedures namely, sys.sp_setapprole andsys.sp_unsetapprole. This is done to allow the same user who is logged in to the database to enable and disable the application role. This is done for illustration only.

3.2 Create a Windows Form Application

Create a simple Windows Forms application. I used Visual Studio 2008, C# and .NET 3.5 to create an application as shown in the figure below. The simple form includes a DataGridView control and three buttons as shown.

The form on load event opens an SQL connection to the AdventureWorks database. The connection string is configured in the app.config file of the application. Once the connection is open, it is cached in a form level variable. Once the form is launched, do the following to verify the functioning of the application role.

Click the Display button. The click event of the Display button uses the current connection of the user and tries to fetch the Employee records from the Employee table. Recall that the user sqluser does not have any read access to the Employee table. Thus, the following access prohibited dialog is displayed.

Now, click Ok on the error dialog and click the Enable AppRole button. This button click event executes thesys.sp_setapprole procedure with required parameters and stores the returned cookie in a byte array in a form scoped variable. This cookie is used to disable the application role. After enabling the application role, click the Display button again and this time the grid view will be populated with the results of the SQL query.

Now, click the Disable AppRole button. The button click event will clear the grid view of data and then disable the application role using the cookie. Now, if you click the Display button, the access to the Employee table data will be prohibited and an error will be generated. In this example, application role provides more access than the user permission. A reverse situation can also be tested where the user can access and display the data but the application role will prohibit access.

4. Code Discussion

The following are code snippets for the discussion in this article. You can also download the complete solution, modify the connection parameters and test on your local computer.

// Reading the connection string variable from app.config
private string ReadConfigurationString()
{
    string configName = "ADWorksConnectionString";
    string connStr = "";
    try
    {
        connStr = ConfigurationManager.ConnectionStrings[configName].ConnectionString;
    }
    catch (System.Configuration.ConfigurationException ex)
    {
        connStr = "";
    }
    catch(Exception ex)
    {
        connStr = "";
    }
    return connStr;
}

// Fetching the HumanResources.Employee data
private DataSet GetAdventureEmployees(out string msg)
{
    msg = "";

    string sqlCommandString = "SELECT EmployeeID, ManagerID, Title, 
    VacationHours FROM HumanResources.Employee";
    SqlCommand cmd = new SqlCommand(sqlCommandString);
    if ((this._sqlConn == null) || (this._sqlConn.State != ConnectionState.Open))
    {
        this.OpenSqlConnection();
    }
    cmd.Connection = this._sqlConn;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;

    DataSet ds = new DataSet();
    try
    {
        da.Fill(ds);
    }
    catch (Exception ex)
    {
        ds = null;
        msg = "Could not fetch employee records." + Environment.NewLine + ex.Message;
    }

    return ds;
}

// Display button click event handler
private void btnFetchData_Click(object sender, EventArgs e)
{
    // Read the configuration string and fetch AdventureWorks data for
    // display.
    string errMsg = "";
    DataSet ds = GetAdventureEmployees(out errMsg);
    if (ds == null)
    {
        MessageBox.Show(errMsg);
    }
    else
    {
        this.dgvAdventureData.DataSource = ds.Tables[0].DefaultView;
    }
}

// Method to enable the HRUPDATER application role.
private bool ExecuteEnableAppRole(string procName, out string msg)
{
    msg = "";
    bool result = true;
    SqlCommand cmd = new SqlCommand(procName);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = _sqlConn;
    SqlParameter paramAppRoleName = new SqlParameter();
    paramAppRoleName.Direction = ParameterDirection.Input;
    paramAppRoleName.ParameterName = "@rolename";
    paramAppRoleName.Value = "HRUPDATER";
    cmd.Parameters.Add(paramAppRoleName);

    SqlParameter paramAppRolePwd = new SqlParameter();
    paramAppRolePwd.Direction = ParameterDirection.Input;
    paramAppRolePwd.ParameterName = "@password";
    paramAppRolePwd.Value = "manu1962";
    cmd.Parameters.Add(paramAppRolePwd);

    SqlParameter paramCreateCookie = new SqlParameter();
    paramCreateCookie.Direction = ParameterDirection.Input;
    paramCreateCookie.ParameterName = "@fCreateCookie";
    paramCreateCookie.DbType = DbType.Boolean;
    paramCreateCookie.Value = 1;
    cmd.Parameters.Add(paramCreateCookie);

    SqlParameter paramEncrypt = new SqlParameter();
    paramEncrypt.Direction = ParameterDirection.Input;
    paramEncrypt.ParameterName = "@encrypt";
    paramEncrypt.Value = "none";
    cmd.Parameters.Add(paramEncrypt);

    SqlParameter paramEnableCookie = new SqlParameter();
    paramEnableCookie.ParameterName = "@cookie";
    paramEnableCookie.DbType = DbType.Binary;
    paramEnableCookie.Direction = ParameterDirection.Output;
    paramEnableCookie.Size = 1000;
    cmd.Parameters.Add(paramEnableCookie);

    try
    {
        cmd.ExecuteNonQuery();
        SqlParameter outVal = cmd.Parameters["@cookie"];
        // Store the enabled cookie so that approle  can be disabled with the cookie.
        _appRoleEnableCookie = (byte[]) outVal.Value;
    }
    catch (Exception ex)
    {
        result = false;
        msg = "Could not execute enable approle proc." + Environment.NewLine + ex.Message;
    }

    return result;
}

// Method to enable application role
private void btnEnableAppRole_Click(object sender, EventArgs e)
{
    // Enable the Application role on the database.
    string errMsg = "";
    this.dgvAdventureData.DataSource = null;
    bool result = ExecuteEnableAppRole("sys.sp_setapprole", out errMsg);
    if (!result)
    {
        MessageBox.Show(errMsg);
    }
}

// Enable AppRole button click event.
private bool ExecuteDisableAppRole(string procName, out string msg)
{
    msg = "";
    bool result = true;
    SqlCommand cmd = new SqlCommand(procName);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = _sqlConn;
    SqlParameter paramEnableCookie = new SqlParameter();
    paramEnableCookie.Direction = ParameterDirection.Input;
    paramEnableCookie.ParameterName = "@cookie";
    paramEnableCookie.Value = this._appRoleEnableCookie;
    cmd.Parameters.Add(paramEnableCookie);

    try
    {
        cmd.ExecuteNonQuery();
        _appRoleEnableCookie = null;
    }
    catch (Exception ex)
    {
        result = false;
        msg = "Could not execute disable approle proc." + Environment.NewLine + ex.Message;
    }

    return result;
}

// Disable AppRole button click event.
private void btnDisableAppRole_Click(object sender, EventArgs e)
{
    // Enable the Application role on the database.
    string errMsg = "";
    this.dgvAdventureData.DataSource = null;
    bool result = ExecuteDisableAppRole("sys.sp_unsetapprole", out errMsg);
    if (!result)
    {
        MessageBox.Show(errMsg);
    }
}

// Method to open sql connection
private void OpenSqlConnection()
{
    string connStr = ReadConfigurationString();
    if (string.IsNullOrEmpty(connStr))
    {
        return;
    }
    this._sqlConn = new SqlConnection(connStr);
    try
    {
        _sqlConn.Open();
    }
    catch (Exception ex)
    {
        this._sqlConn = null;
        MessageBox.Show("Could open connection." + Environment.NewLine + ex.Message);
    }
}

// Method to close sql connection.
private void CloseSqlConnection()
{
    if ((this._sqlConn != null) && (this._sqlConn.State == ConnectionState.Open))
    {
        this._sqlConn.Close();
        this._sqlConn = null;
    }
}

5. Points of Interest

• Application Role provides a level of security at the application to control access to database objects.
• Application Role once set lives for the life of the current connection to the database.
• The system stored procedures sys.sp_setapprole and sys.sp_unsetapprole are provided to enable and disable application role.
• sys.sp_setapprole returns a cookie that can be used to unset the application role.

Encrypting and Decrypting Configuration Sections in ASP.NET

You can use the ASP.NET IIS Registration Tool (Aspnet_regiis.exe) to encrypt or decrypt sections of a Web configuration file. ASP.NET will automatically decrypt encrypted configuration elements when the Web.config file is processed.

Note
The Aspnet_regiis.exe tool is located in the %windows%\Microsoft.NET\Framework\versionNumber folder.

You can also use the protected configuration classes in the System.Configuration namespace to encrypt and decrypt sections of a Web configuration file, sections of a configuration file for an executable (.exe), or sections in the machine-level and application-level configuration files. For more information, see the ProtectSection method of the SectionInformation class. For information on referencing a section of a Web.config file, see the WebConfigurationManager class. For information on referencing configuration sections of files other than the Web.config file, see the ConfigurationManager class.

Encrypting a Web Configuration Section

To encrypt configuration file contents, use the Aspnet_regiis.exe tool with the –pe option and the name of the configuration element to be encrypted.

Use the –app option to identify the application for which the Web.config file will be encrypted and the -site option to identify which Web site the application is a part of. The Web site is identified using the site number from the Internet Information Services (IIS) metabase. You can retrieve the site number from the INSTANCE_META_PATH server variable in the ServerVariables collection. For example, when IIS is installed, a Web site named "Default Web Site" is created as site 1. In pages served from that site, the INSTANCE_META_PATH server variable returns "/LM/W3SVC/1". If you do not specify a -site option, site 1 is used.

Use the –prov option to identify the name of the ProtectedConfigurationProvider that will perform the encryption and decryption. If you do not specify a provider using the -prov option, the provider configured as the defaultProvider is used.

Note
If you are using an RsaProtectedConfigurationProvider instance that specifies a custom key container, you must create the key container before running the Aspnet_regiis.exe tool. For more information, see Importing and Exporting Protected Configuration RSA Key Containers.

The following command encrypts the connectionStrings element in the Web.config file for the application SampleApplication. Because no -site option is included, the application is assumed to be from Web site 1 (most commonly Default Web Site in IIS). The encryption is performed using the RsaProtectedConfigurationProvider specified in the machine configuration.

aspnet_regiis -pe "connectionStrings" -app "/SampleApplication" -prov "RsaProtectedConfigurationProvider"

When a page or other ASP.NET resource in the application is requested, ASP.NET calls the provider for the protected configuration section to decrypt the information for use by ASP.NET and your application code.

Note
To decrypt and encrypt a section of the Web.config file, the ASP.NET process must have permission to read the appropriate encryption key information. For more information, see Importing and Exporting Protected Configuration RSA Key Containers.

Decrypting a Web Configuration Section

To decrypt encrypted configuration file contents, you use the Aspnet_regiis.exe tool with the -pd switch and the name of the configuration element to be decrypted. Use the –app and -site switches to identify the application for which the Web.config file will be decrypted. You do not need to specify the –prov switch to identify the name of the ProtectedConfigurationProvider, because that information is read from the configProtectionProvider attribute of the protected configuration section.

The following command decrypts the connectionStrings element in the Web.config file for the ASP.NET application SampleApplication:

aspnet_regiis -pd "connectionStrings" -app "/SampleApplication"

Build Master Detail GridView using jQuery in ASP.Net

In this article I will explain how to build Master / Detail GridView using jQuery in ASP.Net.

The detail GridView can be expanded and collapsed using jQuery when button is clicked in the Master GridView.

Database

I’ll make use of Customers and Orders Table of Microsoft’s Northwind Database which you can easily download using the link provided below

Download Northwind Database

 

HTML Markup

The HTML Markup contains a simple ASP.Net GridView with a child ASP.Net GridView in the ItemTemplate of TemplateField of ASP.Net GridView

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="Grid"

    DataKeyNames="CustomerID" OnRowDataBound="OnRowDataBound">

    <Columns>

        <asp:TemplateField>

            <ItemTemplate>

                <img alt = "" style="cursor: pointer" src="images/plus.png" />

                <asp:Panel ID="pnlOrders" runat="server" Style="display: none">

                    <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" CssClass = "ChildGrid">

                        <Columns>

                            <asp:BoundField ItemStyle-Width="150px" DataField="OrderId" HeaderText="Order Id" />

                            <asp:BoundField ItemStyle-Width="150px" DataField="OrderDate" HeaderText="Date" />

                        </Columns>

                    </asp:GridView>

                </asp:Panel>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />

        <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />

    </Columns>

</asp:GridView>

 

 

Binding the Customers records to the Parent GridView

Below is the code to bind the parent ASP.Net GridView with the records of Customers table from the Northwind Database.

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        gvCustomers.DataSource = GetData("select top 10 * from Customers");

        gvCustomers.DataBind();

    }

}

 

private static DataTable GetData(string query)

{

    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection con = new SqlConnection(strConnString))

    {

        using (SqlCommand cmd = new SqlCommand())

        {

            cmd.CommandText = query;

            using (SqlDataAdapter sda = new SqlDataAdapter())

            {

                cmd.Connection = con;

                sda.SelectCommand = cmd;

                using (DataSet ds = new DataSet())

                {

                    DataTable dt = new DataTable();

                    sda.Fill(dt);

                    return dt;

                }

            }

        }

    }

}

 

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    If Not IsPostBack Then

        gvCustomers.DataSource = GetData("select top 10 * from Customers")

        gvCustomers.DataBind()

    End If

End Sub

 

Private Shared Function GetData(query As String) As DataTable

    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As New SqlConnection(strConnString)

        Using cmd As New SqlCommand()

            cmd.CommandText = query

            Using sda As New SqlDataAdapter()

                cmd.Connection = con

                sda.SelectCommand = cmd

                Using ds As New DataSet()

                    Dim dt As New DataTable()

                    sda.Fill(dt)

                    Return dt

                End Using

            End Using

        End Using

    End Using

End Function

 

 

Binding the Child GridView with the Orders for each Customer in the Parent GridView

On the RowDataBound event of the Parent GridView I am first searching the Child GridView in the corresponding GridView Row and then populating it with the records from the Orders table of the Northwind Database based on Customer Id stored in the DataKey property.

Note:GetData is a generic function and the same function discussed above is used here.

C#

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)

{

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

        string customerId = gvCustomers.DataKeys[e.Row.RowIndex].Value.ToString();

        GridView gvOrders = e.Row.FindControl("gvOrders") as GridView;

        gvOrders.DataSource = GetData(string.Format("select top 3 * from Orders where CustomerId='{0}'", customerId));

        gvOrders.DataBind();

    }

}

 

VB.Net

Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)

    If e.Row.RowType = DataControlRowType.DataRow Then

        Dim customerId As String = gvCustomers.DataKeys(e.Row.RowIndex).Value.ToString()

        Dim gvOrders As GridView = TryCast(e.Row.FindControl("gvOrders"), GridView)

        gvOrders.DataSource = GetData(String.Format("select top 3 * from Orders where CustomerId='{0}'", customerId))

        gvOrders.DataBind()

    End If

End Sub

 

 

Client side Expand Collapse functionality using jQuery and JavaScript

For Expand and Collapse of the Child GridViews I have made use of jQuery

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

<script type="text/javascript">

    $("[src*=plus]").live("click", function () {

        $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")

        $(this).attr("src", "images/minus.png");

    });

    $("[src*=minus]").live("click", function () {

        $(this).attr("src", "images/plus.png");

        $(this).closest("tr").next().remove();

    });

</script>