Tuesday, September 20, 2016

Ado.net - Populate DataGridControl using DataTable and DataAdapter

1. Introduction


In this Article, we will see an example which pulls data from the database and displays that in the form using “DataTable”, “DataAdapter” and “DataGridControl”.


1.1 Ado.net DataAdapter


A DataAdapter can hold one or more SQLCommand(s) as well as connection information to the database. Also, it can fill the DataTable or Dataset with the data pulled from the database. It can also be used for updating the database.  It acts as a bridge between the database and the data container objects like DataTable, DataSet, etc. The movement of data is controlled by two important methods called “Fill” and “Update”. The Fill method moves the data from the database to the Ado.net data objects (Ex: DataTable). Whereas, the Update method moves the data from the Ado.net data objects to the underlying database.

1.2 Ado.net DataTable


The Ado.net DataTable is an In-Memory representation of Table Rows and Table Columns. A data Adapter is used to fill the data of DataTable. One can use DataTable as a standalone object or it can participate as part of “Ado.net DataSets”.  Simply, a collection of DataTables with the relationship in between them (PrimaryKey & ForeignKey pairs) is called DataSets.

1.3 DataGridControl


This is a Dotnet control used to present the table of data to the user. In our example, we are going to use this control to display the data which is taken from the author's table of the Microsoft supplied pubs database.


2. About the Example


Have a look at the example screenshot below:


Fig 1. DataTable, DataGrid and DataAdapter Example
Fig 1. DataTable, DataGrid and DataAdapter Example

The control at the top of the form is what called “DataGridControl”. This control is used to display the data in the DataTable. In this example, we are going to load the data to the DataTable through DataAdapter and then going to display it in the DataGridControl. This action happens twice in this example and one is at the form load and another one is at the Reload button click.

Note that this article is limited to populating the DataTable and we will see updating the database in another article.


3. Source Code – Explanation


1) First, all the required namespaces are included in the code module:

//Sample 00: NameSpace Required for the Example
using System.Data.SqlClient;

2) A function called LoadData is written to populate the DataGridControl and the function shown below:

Fig 2. Code Explained
Fig 2. Code Explained


01) First, an SQL Query which retrieves the data from the SQL Server Database is defined and the string is stored in a string variable called AuthorsSql (Marked as 1).  The author's table is available in the Microsoft supplied Pubs database. You can download the Database script from SQL 2005 Section of this website.

02) After the SQL String creation, a “SqlDataAdapter” object AuthTable_adapter is created (Marked as 2). This object creation takes two parameters asking what needs to be retrieved and from where it needs to retrieve.  We are passing the SQL String (Marked as 2.1) created in the first step to this function and this will tell the adapter what needs to be retrieved. For the second parameter, we are passing Connection String which is stored as application property and which is explained in the video link https://youtu.be/MBUkXDjH0Fc.

03) Now, the pipeline to the water source is ready and we need a pot to fill it. Right, now we are creating the “DataTable” data container called Authors and then supplying that to the SqlDataAdapter object by calling the method “Fill()” (Marked as 3). This will populate the DataTable Authors with authors found in the author's table.

04) Finally, this populated DataTable object is assigned to the DataSource property of the DataGridControl, which is placed on the form. The DataGridControl takes the responsibility of displaying the “authors table” content in the form. The code for this entire function is given in Listing 1
  
5) The above function LoadData is called on the “Form Load Event” as well as the in the “Click Event” of reload button. The code for this is shown in the Listing 2.

How this sample work is shown in the below video:



4. Code Listings

4.1 Listing 1


//Sample 01: Load data to the DataGrid
public void LoadData()
{
    //Sample 1.1: Create Data Adapter
    string AuthorsSql = "Select au_lname,city,State from authors";

    SqlDataAdapter AuthTable_adapter  = new
        SqlDataAdapter(AuthorsSql, Properties.Settings.Default.ConPubs);

    //Sample 1.2: Create and Fill Data Table through Adapter
    DataTable Authors = new DataTable();
    AuthTable_adapter.Fill(Authors);

    //Sample 1.3: Supply the Populated Data Table to the DataGrid
    DataGridControl.DataSource = Authors;
}

4.2 Listing 2


//Sample 02a: Populate the Grid while displaying the form
private void frmDataTableEx_Load(object sender, EventArgs e)
{
    LoadData();
}

//Sample 02b: Reload the Data from Database
private void btnReload_Click(object sender, EventArgs e)
{
    LoadData();
}
private void btnClose_Click(object sender, EventArgs e)
{
    Application.Exit();
}
Source Code: Download

Wednesday, July 20, 2016

Asp.Net - Filter DataGridControl records based on DropDownList selected item

1. Introduction

A DataGrid is a column and row based table like control.  We can use this control to display data that needs to be organised in the form of rows and columns. In this article, we will explore using the Data Grid control with a combo box. OK, let us start.


2. About the example

Have a look at the below-given screenshot:

DataGrid Control and SqlDataSource
Fig 1. DataGrid Control and SqlDataSource

Here, in this example, we have two “SQL Data Source” controls and they both tied to Authors table of the Pubs database. The first data source control (Marked as 1) supplies data for the grid control (Marked as 3). The second data source control (Marked as 2) supplies the data to the combo box control (Marked as 4). When a user selects a state in the combo box, the authors listed in the DataGrid will be filtered.


3. Create Data source and Data Grid

First, we need to create the data source and data grid control to display the data from the authors table of pubs database. The Microsoft supplied pubs database can be downloaded from the SQL 2005 section of this website.

We can create both the controls in a single stretch. First, establish a connection to the pubs database through server explorer as shown in the screenshot below (Right Click the Data Connection node and create one) or Watch the video at the end of this section.

Setting-Up the Data Connection
Fig 2. Setting-Up the Data Connection

Once the connection to the database is established, drag the Authors table from the Table node and drop it to the default.aspx page. This action will create a “DataGrid Control” and an “SQLDataSource Control”. Now if we run the application, we can see the authors pulled from the Pubs database and displayed in the DataGrid Control.

Drag and Drop the Authors Table
Fig 3. Drag and Drop the Authors Table

This is the basic steps required to display data in the GridView and further customization is shown in the below video:

Video 1: Displaying data in DataGrid Control



4. Create Date source and combo box

Once DataGrid control is placed and configured on the form, Place the “SQLDataSource Control” and “DropDownList Control” on the form as shown in the below picture:

Add DropdownList to Display States
Fig 4. Add DropdownList to Display States

Remember, this second data source control is going to supply data for the DropDownList control and it queries the State column of the authors table. First, we have to configure the SQLDataSource control so that it can retrieve the State column from the Authors table of the Pubs database. The below screen shows how the SQLDataSource and DropDownList are configured:

Configure DropdownList to Display States
Fig 5. Configure DropdownList to Display States

First, the SQLDataSource control (the second one added to the form) is configured to retrieve the state from authors table. Follow the steps to configure the second SqlDataSource control:
  1. From SQLDataSource Quick Access Menu, Select “Configure Data Source …” (Marked as 1)
  2. Select the Table name as “Authors” as marked in 2
  3. Then pick the “state” column alone (Marked as 3) and click OK or move next to check the query output before hitting OK.


Once the data source is configured, the DropDownList Control can refer the data from it. To link the SqlDataSource with the DropDownList control, follow the below-given steps:
  1. From Quick Access Menu of the DropDownList, Select “Choose Data Source…” (Marked as 4)
  2. Then, select the column “State” for the both Display and actual value. (Marked as 5 and 6)
  3. Click Ok and we are done with the DropDownList configuration.


Now when we launch the web page, we can see the DropDownList populated with the State Column. However, we see a lot of repetition in the state column. To avoid, we have to use a customised query that retrieves state column without any repetition. The steps are given below for configuring the sqlDataSource2 in such a way that it will not return a state twice or more:


Setting the DataSource for DropDownList Control
Fig 6. Setting the DataSource for DropDownList Control

  1. First, Select the first Radio option to specify our own query (Marked as 1). Click the next button.
  2. A Tabbed dialog with 4 tabs displayed. Go to Select tab (Marked as 2) and write a select query (Marked as 3) that returns only unique values for the state column. Click the Next button.
  3. In this page, we can check our result (Marked as 5) by clicking the “Test Query” button (Marked as 6). Once observing the result as expected, the Finish button is clicked (Marked as 6).


Now, the drop DropDownList box shows state columns from Authors table without any redundant values. You can watch the video below to know how the DropDownList is configured with the DataSource.

Video 2: Adding Second Data Source and Linking that with DropDownList Box





5. Link the Combo box to Where clause of Data Source 1

At present, we have two SqlDataSource controls on the form and one control supplies data for the DataGrid Control and other one supplied data for the DropDownList Control. Let we link the DropDownList’s selected item to the Where Clause of the SqlDataSource1. Once this is done, the DataGrid control which receiving the data from the SqlDataSource1 will show the filtered result. For Example, if UT State is selected in the DropDownList control, then DataGrid will show only the authors who are from the CA State. Choose Configure Data Source option from the quick access menu and then click the “Where” button. Follow Screenshot Chart for the sequence of steps for linking the ComboBox to the where condition of the SqlDataSource1 which supplied data to the DataGrid Control:


Fig 7. Linking DropDownList to where clause of SqlDataSource Control

Here, we selected the Control as the Where Clause source (Marked as 4). Once we do that, the combo box (Marked as 5) is populated with controls in the form which are valid for supplying the data for Where Condition. We picked our “DropDownList Control” and then the “Add” button (Marked as 6) is clicked. Finally, the OK button is clicked to accept the changes made the DataSource Control. Now run the sample application and we will see the DataGrid Control filters the displayed author based on the selection that we make in the DropDownList Control. Configuring the DropDownList Control with the Where Clause of the SqlDataSource1 Control is shown in the below video.

Video 3: Using a Control is the Where Clause of SqlDataSource Control



Source code: Download
You need link SqlDataSource with your database after downloading the code.


Thursday, June 23, 2016

C# - Declarative and Imperative techniques for security actions

1. Introduction


In the previous article, we looked into “Assembly level security”. In this article, we are going to explore method level security actions with the help of permission to access environment variable.

In windows operating system, permissions are first monitored by the Operating system through its user account and role(s). For Example, a user account that belongs to administrators group can have access to the system32 folder and can read sensitive information such as system environment variable whilst same cannot be done by a Guest User. Assembly level security (Discussed in the previous article) comes next to this OS security. The third level of security can be achieved through methods. In dotnet application world, to access a secured resource, first we should have access to the resource from Operating System (Decided by the logged in user and his/her membership with a role like admin or guest) standpoint, then the dotnet assembly should have the proper grant(s) for the access and finally comes the method which can claim for the access or even reject the access. This kind of layering is shown in the below picture:

Fig 1. Code Access Security - Layers
Fig 1. Code Access Security - Layers

The “Method Level Security” comes with different security actions and those are listed below:
  1. Demand Action
  2. Link Demand Action
  3. Inheritance Demand Action
  4. Deny Action
  5. PermitOnly Action
  6. Assert Action

All the above said security actions can be employed through two different methods. One is “Declarative Method” and the another one is “Imperative Method”. In this article, we will explore each method with the help of sample application and demonstration videos.

2. The example

Have a look at the example application screenshot which is given below:

Fig 2. Example - CAS Method Level Security
Fig 2. Example - CAS Method Level Security

The textboxes (Marked as 1, 2) are used to display the value of the environment variables UserName and SecTest. The two read buttons (Marked as 3, 4) try to read those environment variables and sets the retrieved values in the corresponding textboxes 1 and 2.  

The Radio button options (Marked as 5) invoke the methods which had applied with the corresponding security attributes. We are going to debug the methods, by picking an option here (marked as 5) and hitting the Read buttons. 

The check box (Marked as 6) is used to explain how the security actions like Deny and Demand are invoked through imperative method. One can use the same imperative technique for other security actions also.

3. Declarative Method


In the declarative method, security restrictions are enforced through security attributes. These attributes can be applied at the assembly level, class level and also at methods level. In the previous article, we saw the security attributes applied at the assembly level. In this article, we will learn how to apply "Environment Permission" at the class method and class level. We are going to explore above said six security actions in this article. Like the previous article, we are going to use the "EnvironmentPermission" to read the UserName and SecTest environment variables. Let us start one by one.

3.1 Demand and Deny Security Actions


The “Demand Security Action” looks for the permission grant, in all the methods which participate in the call stack. For example, if Function “fx” demands the permission to read a particular environment variable say; X, then all the functions in the call stack should have the demanded permission. The “Deny Action” is used to explicitly refuse any security permission. Before we explore these, first we need to add the required using directives as shown in the below code:

//Sample 01: Required Using Directive
using System.Security;
using System.Security.Permissions;
using System.Diagnostics;

After adding the required using directive, in the read button handler below piece of codes are added. Both the click event handlers create the object of SecMethodsCheck and call the function GetEnv_DemandLevel1 by passing the corresponding Environment variable names.

//Sample 02: Get Environment Variables
private void btnReadUName_Click(object sender, EventArgs e)
{
//2.1: Demand Security action
if (radDemand.Checked == true)
{
    SecMethodsCheck obj = new SecMethodsCheck();
    txtUserName.Text = obj.GetEnv_DemandLevel1("UserName");
}
.
.

private void btnReadSecTest_Click(object sender, EventArgs e)
{
if (radDemand.Checked == true)
{
    SecMethodsCheck obj = new SecMethodsCheck();
    txtSecTest.Text = obj.GetEnv_DemandLevel1("SecTest");
}
.
.

Below is the code for the class SecMethodsCheck which examines the Demand as well as Deny security actions:

public class SecMethodsCheck
{
    //Sample 03: DEMAND & Deny SECURITY ACTION
    //Sample 3.1: Deny Sectest Permission
    [EnvironmentPermission(SecurityAction.Deny, Read = "SecTest")]
    public string GetEnv_DemandLevel1(string EnVarName)
    {
        string EnvVal = "None";
        try
        {
            EnvVal = GetEnv_DemandLevel2(EnVarName);
        }
        catch (System.Security.SecurityException Ex)
        {
            Debugger.Log(1, "Information", Ex.Message);
        }
        return EnvVal;
    }

    //Sample 3.2: Catch the Exception
    private string GetEnv_DemandLevel2(string EnVarName)
    {
        string EnvVal = "None";
        try
        {
            EnvVal = GetEnvDemand(EnVarName);
        }
        catch (System.Security.SecurityException Ex)
        {
            Debugger.Log(1, "Information", Ex.Message);
        }
        return EnvVal;
    }

    //Sample 3.3: Make sure all the callers in the call stack has permission
    //              for accessing Sectest, UserName Env. Variables
    [EnvironmentPermission(SecurityAction.Demand, Read = "SecTest")]
    [EnvironmentPermission(SecurityAction.Demand, Read = "UserName")]
    private string GetEnvDemand(string EnVarName)
    {
        return Environment.GetEnvironmentVariable(EnVarName);
    }
};

Here, we have three member functions. The function GetEnv_DemandLevel1 calls GetEnv_DemandLevel2 and which in turn calls GetEnvDemand. At the very first method in the call stack, we denied the permission to read the SecTest environment variable.  The "EnvironmentPermission Attribute" is used to deny the reading of SecTest environment variable by specifying the Security action "SecurityAction.Deny". In the last function, we are demanding the read access to both the environment variables SecTest and UserName. The demand action is specified using the enumeration constant "SecurityAction.Demand".

The first two methods invoke the other methods inside the Try block. In the catch block, the "SecurityException" is caught and the message from it is printed to the debugger output window.

In this example, we will get the exception in the method GetEnv_DemandLevel2. This is because; GetEnvDemand, demands the Read permission for both SecTest and UserName. But, in the call stack, the method GetEnv_DemandLevel1 denies the read access to the environment variable SecTest. This makes calling the function GetEnvDemand, which is marked with Demand action, a failure. The Demand and Deny security actions are explained in the below video.


Video 1: SecurityAction.Demand and SecurityAction.Deny





3.2 Permit Only and LinkDemand Security Actions


The security action, ”Permit only” ensures to allow only the requested permission and it also denies all the other permissions to make sure it is permitting only the requested permission. The security action, “Link Demand” makes sure just the previous function in the call stack has the requested permission.  Unlike Demand action, the link demand checks the permission only one level deep in the call stack. Simply, it ensures the function, which called the current function to have the requested security permission.

In the SecMethodsCheck class, we added three functions to explore the usage of the PermitOnly and LinkDemand security actions. Now, have a look at the function given below:

//Sample 04: PERMIT & LINK DEMAND SECURITY ACTION
//Sample 4.1: By Saying permit only SecTest, we are skipping the default
//            Username permission given by the assembly

[EnvironmentPermission(SecurityAction.PermitOnly, Read = "SecTest")]
public string GetEnv_PermitOnlyLevel1(string EnVarName)
{
    string EnvVal = "None";
    try
    {
        EnvVal = GetEnv_PermitOnlyLevel2(EnVarName);
    }
    catch (System.Security.SecurityException Ex)
    {
        Debugger.Log(1, "Information", Ex.Message);
    }
    return EnvVal;
}

//Sample 4.2: Allow both SecTest and UserName
[EnvironmentPermission(SecurityAction.PermitOnly, Read = "UserName")]
[EnvironmentPermission(SecurityAction.PermitOnly, Read = "SecTest")]
private string GetEnv_PermitOnlyLevel2(string EnVarName)
{
    string EnvVal = "None";
    try
    {
        EnvVal = GetEnv_PermitOnly(EnVarName);
    }
    catch (System.Security.SecurityException Ex)
    {
        Debugger.Log(1, "Information", Ex.Message);
    }
    return EnvVal;
}

//Sample 4.3: Demand Sectest permission and Link Demand UserName Permission
[EnvironmentPermission(SecurityAction.Demand, Read = "SecTest")]
[EnvironmentPermission(SecurityAction.LinkDemand, Read = "UserName")]
//[EnvironmentPermission(SecurityAction.Demand, Read = "UserName")]
private string GetEnv_PermitOnly(string EnVarName)
{
    return Environment.GetEnvironmentVariable(EnVarName);
}

In the above example, the function GetEnv_PermitOnlyLevel1 is allowing only the Read access to the environment variable SecTest. As the security action is “Permit Only”, and hence the function automatically denies all other permissions. This means that it will deny the permission to read the UserName environment variable here even though it has the grant from Zone (Remember, our application is configured as Intranet Zone).

The second function, GetEnv_PermitOnlyLevel2 allows access to both the environment variable UserName and SecTest. Note that the PermitOnly security action grants the permission to enter the function only and it does not have the capability of removing the denied access. For Example, the deny set in the UserName environment variable by the previous function (GetEnv_PermitOnlyLevel1) in the call stack cannot be removed.

The third function GetEnv_PermitOnly is demanding the permission to read SecTest environment variable and the demand will succeed as all the functions in the call stack is having demanded permission. If a demand succeeds, the caller is allowed to enter the function. In the case of Link Demand, the check will be stopped at the immediate caller of the function. In our example, this last function is marked with “LinkDemand” for the UserName environment variable. Hence, the username permission check will be stopped at the immediate caller say, GetEnv_PermitOnlyLevel2Since the immediate caller has the Demanded Permission for UserName, and the call to the function GetEnv_PermitOnly is allowed (But Reading the Environment variable is not allowed. This is explained in the video).

Video 2: SecurityAction.PermitOnly and SecurityAction.LinkDemand



3.3 InheritanceDemand Security Action


The base class can enforce security permission on all its derived class by making use of the “InheritanceDemand Security Action”. Say, for example, let us consider a class called “BaseA”, which marks the entire Class with FileIOPermission to claim Read access to C:\system32\Users.txt. Furthermore, we also consider that the class sets the Inheritance Demand security action to the FileIOPermission. Now, all the classes derived from the BaseA should also have the grant for FileIOPermission to read the text file. Let us explore this with our sample application.

The application uses a class library project to explain the InheritanceDemand security action. Before we dig into the code, look at the below screenshot:

Fig 3. Inheritance Demand Example - Class Relations
Fig 3. Inheritance Demand Example - Class Relations

The project, InhDemand is a VC# class library project (Marked as 1). It has a class called InhDemandBase (Marked as 3), which sets InheritanceDemand Security action on the EnvironmentPermission. This environment permission claims read access to SecTest environment variable. Our SecMethods windows form project (Marked as 2) defines a class called InhDemandDerived (Marked a 4), which is derived from the class InhDemandBase (Marked as 5). The below screen shot shows creating the class library project:

Fig 4. Adding Class Library Project

Once the class library project is created, we can add a method to the class so that it can be accessed outside of this class library project. Have a look at the code below:

//Sample 06: Required Using Directive
using System.Security;
using System.Security.Permissions;
using System.Diagnostics;
namespace InhDemand
{
    //Sample 07: Inheritance Demand
    [EnvironmentPermission(SecurityAction.InheritanceDemand, Read = "SecTest")]
    public class InhDemandBase
    {
        public string ReadEnvBase(string envName)
        {
            Debugger.Log(1, "Information", Environment.GetEnvironmentVariable(envName));
            Debugger.Log(1, "Information", Environment.NewLine);
            return Environment.GetEnvironmentVariable(envName);
        }
    }
}


Here, we claimed the Environment permission to read the SecTest environment variable. Also, we specified the InheritanceDemand security action by using the enumeration constant SecurityAction.InheritanceDemand in the Security attribute. The method ReadEnvBase, just like our other examples, reads the SecTest environment variable and returns that to the caller.

After defining the class, add the class library project to our executable project. The steps are shown in the below screenshot:

Fig 5. Adding a reference to the Class Library Project

After the reference is added to the project, a using directive is added to the SecMethods.cs project. That code is shown below:

//Sample 08: Referring different Assembly
using InhDemand;

Now, look at the below code which written in our Exe project. The class InhDemandDerived is derived from class InhDemandBase which is defined in the class library project.

public class InhDemandDerived : InhDemandBase
{
    public string ReadEnv(string EnvName)
    {
        Debugger.Log(1, "Information", Environment.GetEnvironmentVariable(EnvName));
        Debugger.Log(1, "Information", Environment.NewLine);
        return Environment.GetEnvironmentVariable(EnvName);
    }
}

Here, we derived the class InhDemandDerived from our base class InhDemandBase. In this class, we are accessing the base class method GetEnvironmentVariable

While the client code creates the instance of InhDemandDerived, the security action enforced by the base class is checked. If the class InhDemandDerived had granted the permission for reading the SecTest environment variable, then we don’t have any issues. Otherwise, an exception is thrown when we try to enter a method which tries to create the instance of InhDemandDerived. This securityAction is explained in the below video:

Video 3: Inheritance Demand Security Action



3.4 Assert Security Action


The “Assert Action” is a security relaxing action. With this security action, a denied permission can be relaxed to make an entry to the function. Consider the below code now:

//sample 8.0: ASSERT SECURITY ACTION
[EnvironmentPermission(SecurityAction.Deny, Read = "UserName")]
[EnvironmentPermission(SecurityAction.Deny, Read = "SecTest")]
public string GetEnv_AssertLevel1(string EnVarName)
{
    string EnvVal = "None";
    try
    {
        EnvVal = GetEnv_AssertLevel2(EnVarName);
    }
    catch (System.Security.SecurityException Ex)
    {
        Debugger.Log(1, "Information", Ex.Message);
    }
    return EnvVal;
}

[EnvironmentPermission(SecurityAction.Assert, Read = "SecTest")]
public string GetEnv_AssertLevel2(string EnVarName)
{
    string EnvVal = "None";
    try
    {
        EnvVal = GetEnv_Assert(EnVarName);
    }
    catch (System.Security.SecurityException Ex)
    {
        Debugger.Log(1, "Information", Ex.Message);
    }
    return EnvVal;
}

//Sample 8.1: First Try Demanding Only UserName and then try with only SecTest
//            Here, Assert overrides the deny permission
[EnvironmentPermission(SecurityAction.Demand, Read = "SecTest")]
//[EnvironmentPermission(SecurityAction.Demand, Read = "UserName")]
public string GetEnv_Assert(string EnVarName)
{
    return Environment.GetEnvironmentVariable(EnVarName);
}

In the above example, read access to both the environment variables SecTest and UserName are denied at the function GetEnv_AssertLevel1. Then, in the next function GetEnv_AssertLevel2, the security action denied in the previous function call is revoked by setting the “Assert Security Action” on the security permission EnvironmentPermission.

In the final function call, we can attempt our claim for demanding only the  UserName access permission and then make an attempt to claim only the Read access to SecTest. How an “Assert Permission” behaves is shown in the below-given video.

Video 4: Assert Security Action






4. Security Actions through Imperative Method

 
In this method, the security actions are carried out by Dotnet Framework’s API function calls. First, we create a permission objects and then we will carry out the security actions through a function call. For Example, in the imperative method, to deny the “Read Access” to UserName environment variable, we need to create EnvironmentPermission object and then perform the required action like deny. Have a look at the below C-Sharp code:

//Sample 9.0: Demand and Deny Imperative Technique
public string GetEnv_Imperative_DemandLevel1(string EnVarName)
{
    string EnvVal = "None";
    try
    {
        //9.1: Deny the Sectest permission - Imperative
        EnvironmentPermission envP = new EnvironmentPermission(EnvironmentPermissionAccess.Read, "SecTest");
        envP.Deny();
        EnvVal = GetEnv_Imperative_DemandLevel2(EnVarName);
    }
    catch (System.Security.SecurityException Ex)
    {
        Debugger.Log(1, "Information", Ex.Message);
    }
    return EnvVal;
}

private string GetEnv_Imperative_DemandLevel2(string EnVarName)
{
    string EnvVal = "None";
    try
    {
        EnvVal = GetEnvDemand_Imperative(EnVarName);
    }
    catch (System.Security.SecurityException Ex)
    {
        Debugger.Log(1, "Information", Ex.Message);
    }
    return EnvVal;
}

private string GetEnvDemand_Imperative(string EnVarName)
{
    //9.2: Demand the Sectest permission - Imperative
    EnvironmentPermission envP = new EnvironmentPermission(EnvironmentPermissionAccess.Read, EnVarName);
    envP.Demand();
    return Environment.GetEnvironmentVariable(EnVarName);
}
}

In the above code, we can see how the read access to the SecTest Environment variable is denied. First, an object of type EnvironmentPermission is created by making use of enumeration constant EnvironmentPermissionAccess.Read. After the object is created, a call to “Deny() method” is performed and this will deny the permission to Read value from the SecTest Environment variable. Similarly, in the final function GetEnvDemand_Imperative, We create Permission Object and then we make a call to "Demand() method". Note how the “Demand Security Action” is performed at runtime and the demand may be for SecTest or UserName depending what is passed to the function. Applying the security demand like this is called "Imperative Method".

In the above code example, one cannot read the username environment variable as it is denied in the function GetEnv_Imperative_DemandLevel1. Furthermore, the call to Demand() throws an exception when the demand is for SecTest. This is explained in the below video.

Video 5: Security Actions through Imperative Method





Source Code: Download

   

Sunday, June 12, 2016

[ MFC ] - Selecting a folder using Folder Browser Dialog

1. Introduction


In MFC Application, many times we end up browsing the folder structure of windows operating system and picking a folder from it. In this article, we will create a sample application that picks a folder from a tree structure.


2. About the example


The sample application is shown below:

Folder Browser Dialog Example
Fig 1. Folder Browser Dialog Example

When we click on the “Browse Folder” button, a dialog is displayed with a tree structure. This tree structure actually lists the windows folders. Once a folder is picked from the dialog, the picked folder name is displayed in the first read only text box. The second textbox displays fully qualified path to the folder.


3. BROWSEINFO struct & SHBrowseForFolder


A Folder browser dialog can be created by making a call to the global function called "SHBrowseForFolder". This function expects a structure called BROWSEINFO and the same structure is used to get the folder selected in the folder browser dialog. The BROWSEINFO structure is given below:

 typedef struct _browseinfoA {
    HWND        hwndOwner;
    LPCITEMIDLIST pidlRoot;
    LPSTR        pszDisplayName;        // Return display name of item selected.
    LPCSTR       lpszTitle;                     // text to go in the banner over the tree.
    UINT         ulFlags;                       // Flags that control the return stuff
    BFFCALLBACK  lpfn;
    LPARAM       lParam;                        // extra info that's passed back in callbacks
    int          iImage;                        // output var: where to return the Image index.
} BROWSEINFOA, *PBROWSEINFOA, *LPBROWSEINFOA;


4 SHBrowseForFolder code - explained


1) First handle the Button click message for “Browse Folder” button. The message handler function is shown below:

void CFolderBrowseDlg::OnBnClickedButtonBrowse()
{
}

2) In the function body, two char buffers called foldername and folderpath are created by specifying the maximum capacity through the MAX_PATH constant.  Below is the code:

//Sample 01: Buffer to get the selected folder
char foldername[MAX_PATH];
char folderpath[MAX_PATH];

3) Next the BROSWEINFO structure is created and populated. Here, the member hwndOwner expects the owner dialog from which the folder browser dialog will be invoked. The member lpszTitle specifies the description that will get displayed above the folder tree structure. The ulFlags member is used to set the folder browser dialog options. In our example, BIF_RETURNONLYFSDIRS is used tell the browser dialog that only the folders that belongs to the file system needs to be selected. The member pszDisplayName returns selected folder name. Below is the structure member filled with required data before making call to the Folder browser dialog:

//Sample 02: Prepare BROWSEINFO structure
BROWSEINFO binfo;
binfo.hwndOwner = this->m_hWnd;
binfo.iImage = NULL;
binfo.lParam = NULL;
binfo.lpfn = NULL;
binfo.lpszTitle = "Browse and select a specific folder";
binfo.pidlRoot = NULL;
binfo.ulFlags = BIF_RETURNONLYFSDIRS  ;
binfo.pszDisplayName = foldername;

4) The folder browser dialog is displayed by calling the function SHBrowseForFolder. This function takes the BROSEINFO structure as parameter and it uses that for displaying the dialog. This dialog returns ID list of folder that corresponds to the selected node (Folder) of tree structure. If the ID list returned by the function call is empty, then user clicked the cancel button in the dialog. Below is the code:

//Sample 03: Now make a call to SHBrowseForFolder
LPITEMIDLIST item_list = SHBrowseForFolder(& binfo);
if (item_list == NULL )
       return;

5)  The LPITEMIDLIST is passed to the function SHGetPathFromIDList to get the full path to the folder. Below is the function call which returns full path in the second parameter:

//Sample 04: Get Full Folder Name from ID List
SHGetPathFromIDList(item_list,folderpath);

6) At the end of the message handler, foldername and fully qualified path to the folder name is displayed in the corresponding text boxes. Below is code:

//Sample 05: Show the Selected Folder and folder path
m_edit_only_folder.SetWindowText(foldername);
m_edit_path.SetWindowText(folderpath);

The Sample application is shown in the below video.



Source Code: Download




Wednesday, March 30, 2016

SQL 2005 - Creating a SQLCLR Function in C-Sharp

1. Introduction

One can write a function or procedure in SQL server to perform a task. However, writing those in SQL has very limited scope as the SQL deals with restricted number of in-built functions, targeting mainly towards the financial area. In Contrast, the dot.net framework has big scope and one can write nearly any kind of function and/or procedure. We claim a function as “SQL-CLR” function when we write it using dotnet framework and deploy it to run on SQL Server database engine. In this article, we are going to see how to write a SQLCLR function.



2. Loading CLR binary to Database

First, database developers write the functions in Dotnet framework and then expose that through a dll. The SQL server, then, loads the dll into database engine and starts accessing functions exposed by it. In native SQL language, one can write functions, stored procedures, and triggers. In addition to those, one can write Aggregate Function and can even delineate own “User defined Types” in dotnet framework that will later used by the SQL Engine.

Using the “Surface Area Configuration” tool, one can demand SQL server to allow the injection of Dotnet Framework code in the SQL Server Database engine. This tool ships with the SQL Server installation and the below given steps show enabling the SQL-CLR integration:

Watch Video 1 (or) Follow steps given below it:

Video 1: Surface Area Configuration – Turn-on SQLCLR



1) First, invoke the “Surface Area Configuration” tool from the windows start menu. This is shown below:

Surface-Area Configuration
Fig 1. Surface-Area Configuration from Start Menu

2) From the displayed window, Select “Surface Area Configuration for Features” link as shown in the below screen shot:

Surface Area Configuration
Fig 2. Surface Area Configuration

3) In the Surface Area Configuration dialog box, select the node “CLR integration”, which comes under the Database Engine group. Now, from the right pane turn-on the checkbox, which says, “Enable CLR Integration”. After that, click the apply button and close the dialog by hitting OK.

Enabling CLR Integration
Fig 3. Enabling CLR Integration

Now our SQL server is ready to load DLLs written in Dotnet Framework. Let, we explore writing a dotnet c# function and deploying that in SQL Server.



3. SQLCLR versus SQL datatypes

When we write functions in Dotnet framework and try to use that like SQL functions in the SQL server environment, the first trouble gets along in the form of datatypes. Because, the datatypes space allotment may be different in these two different domains. Therefore, to overwhelm this problem, Microsoft put in special CLR datatypes to communicate with the SQL Server database engine. The below table shows some of the important “SQL-CLR-DataTypes”:


SQL Server
Dotnet Framework
CLR Object Types
Binary
byte array
SQLBinary
NChar, NVarchar
char array or string
SQLString
Bit
boolean
SQLBoolean
Int
Int32
SQLInt32
Money, Decimal
Decimal
SQLMoney
DateTime
datetime
SQLDatetime
Table 1: Some Frequently used SQLCLR Data Types

Note that null value in SQL Server cannot match with any primitive datatype of core dotnet. The matching CLR object for that is “DBNull”. The above table shows some crucial data types rather than listing them all. Refer MSDN for knowing the variety of other datatypes.



4. Creating SQLCLR Function

In this section, we will create two functions. The first function does not accept any parameter and returns a string to the caller. The second function takes an integer value, computes square of it, and then returns that value to the caller. Here, the caller is SQL Server engine and the function, which we are going to write using “Dotnet Frame Work”. Let us start now.


4.1 Creating a Project for SQLCLR

To create project, open “Microsoft Visual Studio 2005” from start menu. Then open the “New Project” dialog using the “FileèNewèProject” menu. Select database “project type” from the tree view by navigating to “Visual C#èDatabase” and from the displayed right pane pick “SQL Server Project”. Provide the project name as “SQLCLR_Func” and then click ok. The screen shot below shows the steps involved:

Creating SQLCLR Project in VC#
Fig 4. Creating SQLCLR Project in VC#


4.2 Adding the function

After opening the project, Right click on the project name to invoke the context menu and select “AddèUser-Defined function” as shown in the screen shot below. This will open an “Add New Item” dialog box displaying the all the installed templates. From the template list, select “User-Defined Function”, provide the C #filename (Say testfunction.cs) in the name textbox at the bottom, and then click OK.

Adding a Default User-Defined Function
Fig 5. Adding a Default User-Defined Function

Now we are in a code window and we have some default code, which looks like below one. We can see that the “Add New Item” dialog already placed adequate “using statements” required for writing the function (Marked as 1). Similarly, the IDE takes the function name from given file name (Marked as 2). We are going to change this default function soon. Also, note the attribute “SqlFunction” added to the function name and this tells us that the SQL Server engine will invoke this Dotnet CLR function.

The Default .cs File Content
Fig 6. The Default .cs File Content

Now have a look at the couple of examples written by deleting the existing function SQLCLR_Func. The below screen-shot shows the example:

C# SQL CLR Function Example - Refer Code Listing 1
Fig 7. C# SQL CLR Function Example - Refer Code Listing 1

We already saw the details of item marked as one and two in the earlier sections of this article. You can notice that we changed the function name as TestFunction and changed the return string value (Marked as three). Next, we wrote a new function called “SquareOf”, which accepts an integer as parameter (Marked as 4), squares that parameter, and returns the computed value (Marked as 5).

You can also note the usage of the CLR datatypes such as SqlInt32 and SqlString. Refer MSDN for a complete list SQL Server CLR datatypes. With these two new functions, we can now create and deploy the dll into the SQL Server engine.


4.3 Deploying CLR Function in SQL Engine

Once the function is ready, we can deploy that function using the menu option “BuildèDeploy <name>”. The deploy menu once clicked, deploys the two functions (In our case) in SQL Server. The picture below shows the menu option required for the deployment of CLR functions.

Deploy C# CLR Function to SQL Server
Fig 8. Deploy C# CLR Function to SQL Server

We should make sure that visual studio is launched as administrator. Because, deployment of CLR function in SQL Server requires administration privilege.


4.4 Testing the Function

OK, we wrote couple of functions in C# and deployed those functions in SQL Server. Let, we see how we can check these functions working as expected. Have a look at the below picture. The item marked as two shows that the IDE created test.sql file. We can use this file to write our SQL Scripts and execute those written scripts in the Visual Studio Development Environment itself. Remember, the function we wrote is inside the TestFunctions.cs file, which is marked as one in the below picture. In the Test.sql we can write SQL Script to call the CLR functions and the call made in such a way is marked as three in the below picture. Writing SQL scripts in visual studio itself is helpful in case if we want to debug the scripts and you can see how the breakpoint is placed in the very first statement (Marked as 4). When you execute these functions in visual studio, you can see the results in the output window.

Testing the SQLCLR Function in Visual Studio
Fig 9. Testing the SQLCLR Function in Visual Studio

The screenshot below shows the output of the function in Visual Studio Debugging output window:

Test Result in the Output Window
Fig 10. Test Result in the Output Window


Video 2: Deploying the Function

Video 2:



5. Usage of T-SQL Script for deployment

In the previous section, we deployed our SQL CLR function using visual studio IDE. Sometime, it may require deploying the application through SQL scripts. Because, the setup software have more control over these TSQL-Scripts. Have a look at the below scripts:

Configure SQL CLR and Create Assembly using T-SQL
Fig 11. Configure SQL CLR and Create Assembly using T-SQL - Refer Code Listing 2


The script enables the CLR inclusion (Marked as 2) in the SQL Server engine and the same was done using the “surface area configuration tool” as shown in section 2 of this article. To set the flag ”clr_enabled” we set the “Show Advanced Flag” to one (Marked as two). When this flag is zero, SQL Server engine will not allow changes to the clr_enbaled. After setting the flag, we included the Dotnet DLL, which exposes our two new functions to SQL server engine. Moreover, this was done by making use of the “Create Assembly” TSQL statement as shown in the above script. It looks for the Assembly Name (Marked as 3) and the path to the dotnet dll (Marked as 4). Once we execute the above statements, we have successfully set the dotnet assembly.

Now, have a look at the script below:

Registering DotNet function through T-SQL
Fig 12. Registering DotNet function through T-SQL - Refer Code Listing 3

Here, we registered the Dotnet Assembly functions as SQL Server functions. If you have a close look at it, we are actually creating a SQL Server function by linking it with the functions created in Dotnet C# (In our case) language. Here, we are creating two functions named CLRTestFunc (marked as 1) and ClrSquare (Not markedJ). In the above script marked as 2 and 3 shows the return type of the SQL Function. This return type should match with the return type of the return type of the C-Sharp version of the function. In the second function, we can see how the SQL sends the parameter to C# function. Here also the Datatype should match with c#. Note that we used smallint (SqlServer side) to match with Int16 of C# function.

Code snippet marked as 4, 5, 6 and 7 links the Dotnet CLR function with the SQL function that we are about the create. Here, the first one denotes the Assembly Name of the SQL (Note: Assembly name given in Create Assembly Statement), the second part denotes (4 and 5) the C# class name that exposes the function and final one denotes the name of the function (6 and 7) in c# side. Now, when we make a call to the function, say ClrSquare, SQL Server know that the function body and business logic defined in the external assembly and it know which function to call from that assembly as well.

When you want to remove the assembly from SQL Server, first remove all the functions registered through it. In our case, to remove the assembly MyCLRFunctions, we should first remove the SQL Functions CLRTestFunc and ClrSquare, which refers the assembly.



6. Code Listings

Listing 1:


//Sample 01: Functions return string modified
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString TestFunction()
{
    // Put your code here
    return new SqlString("Test Function invoked");
}

//Sample 02: New function added
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 SquareOf(SqlInt16 number)
{
    return new SqlInt32(number.Value * number.Value);
}

Listing 2:


sp_configure 'show advanced options', 1;
reconfigure;
go

sp_configure 'clr_enabled', 1
reconfigure;
go

use pubs;
Create Assembly MyCLRFunctions From
'E:\Temp\SQLCLR_Func.dll' ;
go

Listing 3:


Create Function CLRTestFunc()
       returns nvarchar(50) external name
MyClrFunctions.UserDefinedFunctions.TestFunction;
Go

Create Function ClrSquare(@param smallint )
       returns int external name
MyClrFunctions.UserDefinedFunctions.SquareOf;
Go
Source Code: Download
Like this site? Tell it to your Firend :)