Crystal Reports | Passing parameters from a .Net Application

Hi guys

This is the 2nd part of my experiences with SAP Crystal Reports for Visual Studio. I’ve posted about installing & creating a simple Crystal Report earlier, if you haven’t read it yet, please find it here.

One of the major confusions around passing values to a Crystal Report was contributed by multiple questions and answers available on StackOverFlow. The ones which came nearest to my requirements had only “teasers”, not real solutions! Finally on the 3rd or 4th day, I realized that, the parameters were called based on the index numbers and the finding the index numbers for parameters were as simple as counting the 1st parameter as “0” & incrementing the index number with 1 for subsequent parameters.

Example Scenario:

I am using a stored procedure with the crystal report that accepts more than 1 parameter to select data from different tables

Here with this example, I am trying to list the salaries for employees based on the year, month & company section in which they are working as filters. An end user will pick up those filters from a web form and pass to the crystal report before the final report is generated.

Crystal report has a very simple method to pass the parameters & I am limiting this post to “Stored Procedure” based reports. The same should apply to table/dataset based reports as well until you have sub-reports involved.

In this example, you could see that I have @pProcessYear, @pProcessMonth & @pProcessSection as parameters for the report. Notice the order they are listed (refer the picture)

@pProcessYear is listed first, followed by @pProcessMonth & @pProcessSection. So the index number for @pProcessYear is 0 and @pProcessMonth is 1 & @pProcessSection is 2!

Now, let us see how we will pass values to these parameters from a WebForm.

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Table = CrystalDecisions.CrystalReports.Engine.Table;

namespace insert_demo
{
    public partial class ShowEmp : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ReportDocument reportDocument = new ReportDocument();
                reportDocument.Load(Server.MapPath(@"~/CrystalReport2.rpt"));
                reportDocument.SetParameterValue(0, 2020);
                reportDocument.SetParameterValue(1, 1);
                reportDocument.SetParameterValue(2, 3);

                reportDocument.DataSourceConnections[0].SetConnection("RAJESH-PC", "MenaSS", true);
                CrystalReportViewer1.ReportSource = reportDocument;

            }
        }
	}
}

Notice the intellisense suggestion for Crystal Reports document parameter. Definitely “ReportDocument.SetParameterValue” expects an integer value for the index, when much of the examples available with StackOverFlow were passing report parameter names in the place of index, which didn’t work at all. I am forced to believe, Crystal Reports used to accept this for previous versions of Crystal Reports for Visual Studio & may be not many are using Crystal Reports with Visual Studio anymore…? Interestingly most of the questions related to the subject were many years old.

So, it is as simple is going through the order of the parameters as they are listed on the report design form (the order never changes, unless you delete and recreate them), starting with the index number 0 for the first parameter, increase by 1 until the last parameter & pass the values accordingly & you are done!

With my next post, I will try to talk about sub-reports and complex formatting using available Crystal Report tools.

regards,

rajesh

SAP Crystal Reports for Visual Studio

Hello guys

My .Net developments were ALWAYS at risk! Whenever I am doing it good, the project gets cancelled & I return to the Oracle world.

Anyway, for the last project that is about be shelved, I chose SAP Crystal Reports in addition to Microsoft’s RDLC for few reasons. Microsoft has stopped shipping Report components with their IDE Visual Studio & getting it work by installing Nuget packages and extension methods are not going to be very easy for most of the newbies (I am a life long newbie when it is all about .NET development)

On the other hand, installing & going online with SAP Crystal Reports for Visual Studio is pretty straight forward. Download the package from SAP, install it and you are all set to go. Well definitely not!

Let us quickly see how to install & get the most out of SAP Crystal Reports.

  1. Installation
  2. Server Runtime
  3. Your 1st Crystal Report
  4. POST BACK and hacks

Installation

Register & download SAP Crystal Reports for Visual Studio. Make sure you install the runtime engine which is prompted during the installation. Simple as it is.

Server Runtime

If you are publishing your application with SAP reports from another computer/Server make sure you install the Runtime package.

If you’ve installed the runtime, this will create the below structure under inetpub\wwwroot folder

Your 1st Crystal Report

If you are a .Net developer, already familiar with dragging and dropping controls from the Toolbox node(s). Crystal Reports is not different. Just drag and drop “CrystalReportViewer” control to your page & you are all set to go.

Dropping the control to the page adds many references to your project. You may scroll through the references to identify them. All the references have “Crystal” in the name.

Now let us create a sample report & view the report on the web.

If this is your 1st attempt to create a report, select the default & continue. Based on the connections already available in the environment, you will be provided existing connections or an option to create a new connection.

Here is the catch. If the database (MSSQL or other) is configured for integrated security & you prefer to continue using the same for your project, you must make sure that from your webform/page you will be sticking to the same authentication method. Said, you cannot use integrated authentication during design and username/password login during the runtime. So be careful when you are creating connections.

We will create a new connection using username and password to local MS SQL server.

You don’t have to change anything here. Just click the “Finish” button to complete the connection.

That’s it. This creates a new connection and the same will be available under “My Connections”.

Now from the connection, you can select a table, view or stored procedure for your report as source. I have selected a table “bal2020” as you could see with the below image.

Once you click the OK button, you will be taken back to object browser window.

You can see that the table you have selected is shown under “Database Fields” node and expanding the Table node will show you the available columns those you could add to the report.

Drag and drop the columns you want to add in the “Section 3 (Details)” area and the act creates relevant titles in the “Section 2 (Page Header)” area. You can always modify the titles.

Unlike RDLC, you can immediately preview your reports from the design window itself by switching to “Main Report Preview” tab.

Let us see how this report viewed from a webform/page.

As we are trying to create a report for the first time, I suggest you to use the GUI for linking your newly created report with the webform/page.

Select “New Report Source” form “Choose Report Source” and select your newly created report. Leave the names as seen for this exercise.

Prior attaching the report, the Crystal Report Viewer control code was minimal like below

<body>
    <form id="form1" runat="server">
        <div>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>

and after linking the report to the viewer, you will notice that a number of elements are added to the viewer.

 <form id="form1" runat="server">
        <div>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True" 
                GroupTreeImagesFolderUrl="" Height="1202px" ReportSourceID="CrystalReportSource1" 
                ToolbarImagesFolderUrl="" ToolPanelWidth="200px" Width="1104px" />
            <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
                <Report FileName="CrystalReport1.rpt">
                </Report>
            </CR:CrystalReportSource>
        </div>
    </form>

As there are not complex code involved as in with stored procedures, your page will load the report. You can save the webform & try to see whether it truly gets loaded on the browser.

Save everything & build the solution & debug the page that you have just created. Do not use Internet Explorer for debugging. You may end up with “N” number of errors by using IE.

Something is wrong, right? let us see why the report is not loading. With this specific case, an empty page in the place of report viewer is due to missing runtime binaries. If you remember, I asked you to install the runtime while installing the report developer. So there is something wrong…

I don’t have the technical knowledge to confidently say it is a bug , however looks like one, with certain solutions, the symbolic link to rootdrive:\inetpub\wwwroot\aspnet_client is not created within the solution folder after Crystal Report Viewer control is added. Failing to find “aspnet_client” symbolic link to the actual path where the runtime binaries are kept results in failing to load the report on the browser, unfortunately without generating visible errors. While missing the symbolic link being one of the major reasons, there could be few other reasons as well like application pool configured for both 32 Bit & 64 Bit. I have noticed that if your default application pool is configured to cater both 32 & 64 Bit, the 64 Bit runtime will not load & errors will be generated while trying to load reports. If you have installed 64 Bit SAP components, create a new application pool exclusively for 64 Bit only.

To resolve the issues due to runtime binaries, we can either create a symbolic link to “rootdrive:\inetpub\wwwroot\aspnet_client” or copy the entire aspnet_client folder to project, which is approximately 17-18 MBs in size.

I will always prefer the 1st option. So let us see how we can create a symbolic link to “rootdrive:\inetpub\wwwroot\aspnet_client” from our project.

The above symbolic link is explained as below.

Your project is created under user’s home folder. For example, my username is Rajesh. Hence Visual Studio has created a path “sources\repos” where all new solutions will be created and stored (unless I change the VS options)

As I have chosen “CrystalReportsDemo” as my solution name, a folder with the same name is created inside “sources\repos” and the solution related files and folders are kept within a subfolder with the solution name. Finally, the path will look like below once after the solution is created.

"C:\Users\Rajesh\source\repos\CrystalReportsDemo\CrystalReportsDemo"

We are expected to create the symbolic link to “aspnet_client” folder inside the “CrystalReportsDemo” subfolder.

The symbolic link must have a name, hence you will provide “aspnet_client” as the link name (no other names please!) and refer the original path of aspnet_client as source.

mklink /D "C:\Users\Rajesh\source\repos\CrystalReportsDemo\CrystalReportsDemo\aspnet_client" "C:\inetpub\wwwroot\aspnet_client"

I know it sounds bit complex and I hope you will get a hang of it with practice. A successfully created symbolic link will look like below. If you have grouping enabled for the files in explorer, the newly created symbolic link will be listed under folders.

Let us try to view the webform once again after creating the symbolic link.

Basically, when you add a crystal report to your solution, it is treated as an “Embedded Resource” & not copied to output directory. Unless you change these properties, your published application will not able to access the reports. Let us see quickly how these changes are made.

Now the output directory will copy the report files each time the solution is built.

PostBack & hacks

What is PostBack? PostBack is the name given to the process of submitting an ASP.NET page to the server for processing. This has a big effect on how Crystal Reports behave.

Consider a case when you have a webform that has few user choices & a crystal report attached to it. By default much of the ASP.Net controls support “AutoPostBack” property & very useful when a developer wants to refer to other controls values programmatically. Well, each instance of AutoPostBack causes the Crystal Report to refresh itself.

As you are already aware ASP.Net is stateless and Crystal Report will loose all functionalities like progressing to next page or previous page etc when associated buttons on the toolbar is pressed immediately after a postback. In addition to losing these functionality, you may be prompted to enter the database connection details. SAP recommends using Page_Init() instead of Page_Load() method in addition to using session variables to load the report after postback. We will see a complex sample now, which is from a production environment.

Scope of the report

Generate a report for Finance department that lists all salary elements for a chosen company, department & few other selections by the end user.

So this particular report has a total of 5 parameters received from the user & clicking the “Print Report” should show the report with relevant data.

The above report uses multiple stored procedures to fetch the relevant data for the report & copying the script here will defy the intend. Hence I will copy a sample that I posted as an answer to my own question over StackOverFlow & explain it.

You may refer to the stackoverflow thread here.

The below example generates the same report sample you have seen above, however much of the parameters are hardcoded & the only user interactable control is the button. This report uses database stored procedures for report data.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="c4.aspx.cs" Inherits="CrystalTest.c4" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Print Report" OnClick="Button1_Click" />
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>
</html>

Code behind for the webform is like below:

using CrystalDecisions.CrystalReports.Engine;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CrystalTest
{
    public partial class c4 : System.Web.UI.Page
    {
        protected void Page_Init(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                CrystalReportViewer1.ReportSource = (ReportDocument)Session["Report"];
            }
        }
        private void ShowReport1()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GETMONTHSALARY", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@pProcessSection", SqlDbType.VarChar).Value = "9";
                    cmd.Parameters.Add("@pProcessSite", SqlDbType.VarChar).Value = "1";
                    cmd.Parameters.Add("@pProcessCatg", SqlDbType.VarChar).Value = "1";
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "SalaryDT");
                    ReportDocument oRpt = new ReportDocument();
                    oRpt.Load(Server.MapPath(@"~/dataset/CrystalReport1.rpt"));
                    oRpt.DataSourceConnections.Clear();
                    oRpt.SetDataSource(ds);
                    oRpt.Subreports[0].SetDataSource(FillOverTime());
                    CrystalReportViewer1.Visible = true;
                    CrystalReportViewer1.ReportSource = oRpt;
                    Session["Report"] = oRpt;
                }
            }
        }
        private DataSet FillOverTime()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetEmployeeOverTime", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pEmployeeCode", SqlDbType.VarChar).Value = DBNull.Value;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds1 = new DataSet();
                    adapter.Fill(ds1, "OverTimeDT");
                    return ds1;
                }
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowReport1();
        }
    }
}

The above sample uses 2 different stored procedures to generate data for main report and a sub report. ShowReport1() method is triggered when the button is clicked. Once the report is generated, it is saved into a session object & whenever a post back happens, this saved object is assigned to report source. Simple as it is. Each button click on the reports toolbar is treated as post back, triggering the report source being assigned again and again. You might notice that I don’t have a Page_Load() method with the code behind!

I know this is just a beginning & including more detailing might make the post irrelevant. With my next post, I will try to explain Crystal Reports parameters, sub-reports etc.

2nd Part. Passing parameters to Crystal Reports

regards,

rajesh

MS SQL | Backup remote database on local machine

Hi guys

At times we have to have a recent backup of the SQL database on a development machine to insure that our testing is false-proof prior the same is pushed to a production instance. As SSMS (SQL Server Management Studio) doesn’t allow remote backup functionality out of the box, we can try the following (applies to both home networks and windows domain networks)

From your local machine, setup a shared folder, giving full read/write access to the entity “Everyone”

Now, prior attempting below instructions, be sure of your privileges against the target database. Are you a sys admin? Does your database account have the rights to make a backup? If yes, go ahead

Now, start SSMS & proceed with the backup task

Depending upon the security constraints, you may disable the sharing of local folder immediately after the backup completion.

regards,

rajesh

MS SQL 2019 | Upgrade/Fresh Installation

Hi guys

My laptop has many software development components installed, say few of them are there from last many years (regardless whether I still need them) & such get me in to complicated situations (most of the times)

I’ve had Visual Studio 2013 Professional edition installed for last many years & recently I have switched to the community edition (which is as good as pro edition & costs nothing)

After upgrading my SQL Server 2017 (Developer Edition) on my home computer using 2019 ISO mounted media, I decided to upgrade the SQL Server 2017 on my laptop and it failed, miserably!

So I decided to do a fresh installation, that also failed. Luckily I came across the following thread:

https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-2019-installation-error-an-error-occurred-for-a/ba-p/998033#

While, there could be more reasons for the failure to install/upgrade your existing SQL server, make sure whether you have “Microsoft SQL Server 2012 Native Client” already installed, if yes, remove it (Which might popup a warning stating the dependency of Local DB 2016 on the Native client, hence make sure that you know what you are doing)

Once the 2012 Native client uninstalled, try again to upgrade/fresh installation of SQL Server 2019.

You should be through!

regards,

rajesh

ASP.Net | Freeze GridView header row using java script & CSS

Hi guys

As I started developing a web application for our business, One of the toughest requirements from end users were to “freeze” the header row of data grid views. I left .Net development almost 11 years back and was struggling to catch up with the whole set of changes .Net has came up with.

I’ve scavenged through dozen’s of articles explaining different hacks to freeze/lock the header row of grid views & was surprised to see that Microsoft didn’t address this requirement throughout their .Net iterations. Few of the workarounds those I tried out were ONLY applicable to static set of data, not at all applicable to data grids those have more than three or four columns and failed while column contents exceeded certain number of characters.

Finally, after a number of days’ search & trying out various solutions I came across a post at https://www.aspsnippets.com/Articles/Dynamically-freeze-ASP.Net-Gridview-header-using-JavaScript.aspx

The post has a VS solution download (Dated 21st February 2011) which was giving me multiple reference errors while opened with VS 2019 Community edition. So I decided to open the .aspx and code behind pages manually and create a new solution using VS 2019

Although the java script does freeze the header rows (while no themes applied to the grid & has limited columns), the moment a theme applied to the grid, the header row alignment with the data columns go for a toast. You will able to see how it works with attached example that you can download from the provided link by the bottom of the post.

I’ve created a huge employee list with 20 columns, where few of the columns have varying data lengths (like employee name, department, section name & job positions) as an XML source and throughout the examples I will be using the same data source to demonstrate how each page adjusts the GridView with minor hacks using both java script and CSS styling.

Example one, no java script, no themes gridview

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="FreezeGridViewHeader.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                <Columns>
<asp:BoundField DataField="EmployeeNumber" HeaderText="Code" />
<asp:BoundField DataField="fullname" HeaderText="Name"  />
<asp:BoundField DataField="department" HeaderText="Department"  />
<asp:BoundField DataField="section" HeaderText="Section"  />
<asp:BoundField DataField="position" HeaderText="Position"  />
<asp:BoundField DataField="basicsalary" HeaderText="Salary"  />
<asp:BoundField DataField="other" HeaderText="other"  />
<asp:BoundField DataField="petrol" HeaderText="petrol"  />
<asp:BoundField DataField="mobile" HeaderText="mobile"  />
<asp:BoundField DataField="car" HeaderText="car"  />
<asp:BoundField DataField="transport" HeaderText="transport"  />
<asp:BoundField DataField="sign" HeaderText="sign"  />
<asp:BoundField DataField="house" HeaderText="house"  />
<asp:BoundField DataField="acco" HeaderText="acco"  />
<asp:BoundField DataField="driving" HeaderText="driving"  />
<asp:BoundField DataField="monthly" HeaderText="monthly"  />
<asp:BoundField DataField="oncall" HeaderText="oncall"  />
<asp:BoundField DataField="engineer" HeaderText="engineer"  />
<asp:BoundField DataField="special" HeaderText="special"  />
<asp:BoundField DataField="total" HeaderText="total"  />
</Columns>
            </asp:GridView>

        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }

          
        }
    }
}

Executing Default.aspx brings up a page like below

Now, we will use the java script to see how far the intended results are achieved.

Example Two, Calling java script function to freeze the header row

Gridjs.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridjs.aspx.cs" Inherits="FreezeGridViewHeader.Gridjs" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="FreeGrid.js" type="text/javascript"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
                <AlternatingRowStyle BackColor="#DCDCDC" />
                <Columns>
                    <asp:BoundField DataField="EmployeeNumber" HeaderText="Code" />
                    <asp:BoundField DataField="fullname" HeaderText="Name" />
                    <asp:BoundField DataField="department" HeaderText="Department" />
                    <asp:BoundField DataField="section" HeaderText="Section" />
                    <asp:BoundField DataField="position" HeaderText="Position" />
                    <asp:BoundField DataField="basicsalary" HeaderText="Salary" />
                    <asp:BoundField DataField="other" HeaderText="other" />
                    <asp:BoundField DataField="petrol" HeaderText="petrol" />
                    <asp:BoundField DataField="mobile" HeaderText="mobile" />
                    <asp:BoundField DataField="car" HeaderText="car" />
                    <asp:BoundField DataField="transport" HeaderText="transport" />
                    <asp:BoundField DataField="sign" HeaderText="sign" />
                    <asp:BoundField DataField="house" HeaderText="house" />
                    <asp:BoundField DataField="acco" HeaderText="acco" />
                    <asp:BoundField DataField="driving" HeaderText="driving" />
                    <asp:BoundField DataField="monthly" HeaderText="monthly" />
                    <asp:BoundField DataField="oncall" HeaderText="oncall" />
                    <asp:BoundField DataField="engineer" HeaderText="engineer" />
                    <asp:BoundField DataField="special" HeaderText="special" />
                    <asp:BoundField DataField="total" HeaderText="total" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#0000A9" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#000065" />
            </asp:GridView>
            <script type="text/JavaScript">
                window.onload = function () {
                    this.FreezeGrid("<%=GridView1.ClientID %>",500);

                }
            </script>
        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Gridjs : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

I’ve applied one of the built-in themes available for GridView control with the above example, which causes the header and data rows misalignment.

However, the java script has done what it was meant to do! Freezing the header row and providing a scrolling function without breaking much sweat. I have noticed one thing, if the theme is removed from the GridView control, everything goes back to as expected, header row is perfectly aligned with the data row & the scrolling works as expected. Please note, if your grid is populated with huge volume of data, then it will take few moments before the java script manages to bring up the effects in place. I’ve tested this solution using Microsoft Edge Chromium, Mozilla Firefox & Internet Explorer. Both the modern browsers deal with the java script much efficiently while Internet Explorer experiences were NOT that good.

Example Three, fixing the misalignment issues by using some simple CSS styling.

Gridjscss.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridjscss.aspx.cs" Inherits="FreezeGridViewHeader.Gridjscss" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="FreeGrid.js" type="text/javascript"></script>
    <style>
        .Colsmall {
            width: 60px;
            max-width: 60px;
            min-width: 60px;
        }

        .Colmedium {
            width: 80px;
            max-width: 80px;
            min-width: 80px;
        }

        .Colbig {
            width: 150px;
            max-width: 150px;
            min-width: 150px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
                <AlternatingRowStyle BackColor="#DCDCDC" />
                <Columns>
                    <asp:BoundField DataField="EmployeeNumber" HeaderText="Code" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" />
                    <asp:BoundField DataField="fullname" HeaderText="Name" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="department" HeaderText="Department" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="section" HeaderText="Section" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="position" HeaderText="Position" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="basicsalary" HeaderText="Salary" ItemStyle-CssClass="Colmedium" HeaderStyle-CssClass="Colmedium" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="other" HeaderText="other" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="petrol" HeaderText="petrol" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="mobile" HeaderText="mobile" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="car" HeaderText="car" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="transport" HeaderText="transport" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="sign" HeaderText="sign" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="house" HeaderText="house" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="acco" HeaderText="acco" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="driving" HeaderText="driving" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="monthly" HeaderText="monthly" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="oncall" HeaderText="oncall" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="engineer" HeaderText="engineer" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="special" HeaderText="special" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="total" HeaderText="total" ItemStyle-CssClass="Colmedium" HeaderStyle-CssClass="Colmedium" ItemStyle-HorizontalAlign="Right" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#0000A9" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#000065" />
            </asp:GridView>
            <script type="text/JavaScript">
                window.onload = function () {
                    this.FreezeGrid("<%=GridView1.ClientID %>", 500);

                }
            </script>
        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Gridjscss : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

Now, everything looks fine! A theme is applied, header and data rows are in perfect sync & the header row is locked when the data rows are scrolled up and down.

There are many alternatives, modern approaches using jQuery and other, which I do not understand at this point of learning. Once I am confident enough to, will sure try them and get back to you guys. Happy coding!

Download VS Solution

Please note, this solution has been developed using .Net 4.7.2 & I have modified the java script in a way that I can call the script from any page by passing couple of parameters to it. The whole credit for the java script goes to original coder.

regards,

rajesh

OLE DB provider “OraOLEDB.Oracle” for linked server “” returned message “New transaction cannot enlist in the specified transaction coordinator.”

Hi guys

A pretty long title? Well, recently I came across a situation where I needed a trigger with MS SQL server table to insert some information into our Oracle database.

The MS SQL Server is hosted in a Windows 64 bit OS, with Oracle 11g 64Bit client installed (For 64Bit OS, you must install Oracle client 64Bit for the Oracle OLEDB provider)

I did some sample inserts using the Management studio and created a trigger like following with one of the sample tables:

create trigger addRecordsToERPTable on [UNIS].[dbo].[tRajesh]
after insert
as
begin
  insert into [XYZ].[APPS].[XXFPPUNCHM] 
  (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
  Select PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE
  FROM inserted
end
go

So the idea was pretty simple, like an audit, as soon as the SQL table “rRajesh” has a new row inserted, the after insert trigger should sent the same row to underlying table over Oracle. Instead I started getting the following error:

OLE DB provider “OraOLEDB.Oracle” for linked server “XYZ” returned message “New transaction cannot enlist in the specified transaction coordinator. “.
Msg 7391, Level 16, State 2, Procedure addRecordsToERPTable, Line 5
The operation could not be performed because OLE DB provider “OraOLEDB.Oracle” for linked server “XYZ” was unable to begin a distributed transaction.

I’m not very familiar with MS SQL or the complexities related to Linked Server environments. So, started my next series of Google searches. I referred tons of discussions, however was not getting anywhere with the dreaded situation. During the frantic search for a solution, I executed the instructions available over different links.

https://stackoverflow.com/questions/6999906/distributed-transaction-error

https://community.oracle.com/thread/2265534

Even after making changes as mentioned with the above threads, I still kept on receiving the same errors while a row was inserted into my SQL sample table. So I continued searching for a solution and came across a thread

https://microsoft.public.sqlserver.security.narkive.com/WDGBVTrk/msdaora-was-unable-to-begin-a-distributed-transaction-why-sql-oracle

This thread was pointing towards a Microsoft’s post addressing this particular situation.

http://support.microsoft.com/kb/280106

Although the article addresses pretty Old OS and Oracle environments, the solution is still applicable on later OS and Oracle clients. For example, My MS SQL Server is installed over Windows 2008 R2 and the Oracle client I am using with the server is 11G R2 64Bit.

Let us see quickly what Microsoft provides as a solution.

I checked the registry of my server and found something pretty interesting like below:

Now, Oracle names almost all their major dll files in a particular fashion. Most of the times you will find the dll files having the major version numbers by the end of the filename, for example, if your Oracle database is 8.0, your client dll file will be “Oraclient8.dll” and if you are using Oracle 11g, the filename would be “Oraclient11.dll”

After taking a full backup of the registry, I modified the values with 11g specific & restarted the Server (as per the instructions available for Oracle 8.1 in the Microsoft document.)

Once the server started, I went ahead and tried to insert a new row into my sample table and that was it. No more errors and the row was inserted to both MS SQL table and Oracle table at the same time.

So if you were frantically searching for a solution, this post may help you to resolve it.

regards,

rajesh

Classic ASP with Oracle database 12c 64Bit

Hi guys

Yesterday I was contacted by one of the visitors after referring my posts about Classic ASP connection to Oracle databases in general & I revisited this “area” after a long time. It took a while for me to setup everything, however the results were pretty awesome. So here comes one more post about Classic ASP with Oracle database, this time Oracle 12c 64Bit.

OS: Windows 10/64Bit Windows Server OS

Prerequisites

  • Setup IIS for publish classic ASP with Oracle 12c database

Software requirement(s):

  • Oracle client 32Bit, 12c (for best connectivity)

Launch IIS and create a new application pool, as shown with the image.

clip_image002

Now go to the advanced settings for the application pool that you have created & switch “Enable 32-Bit Applications” from “false” to “True”

clip_image004

Connection possibilities

There are two ways to connect to Oracle database from Classic ASP

  1. Using DSN (Data Source Name)
  2. DSN less connections. Here we will use the Oracle tnsnames.ora file to identify and connect to the database. We have to insure that the TNS_HOME directory is set at the environment level, or the client that is used is 1st entry in the environment variable “PATH” for Oracle products in case of multi Oracle homed hosts.

Create a DSN

Classic ASP could interact with only 32Bit environment, hence the IIS server hosting machine must have the latest Oracle 32-Bit client installed (older clients may not connect to later databases properly). As I am writing this post, Oracle 12c 12.2.0 is the latest client available.

Logged with an account having “Administrator” privileges, Open ODBC Data Source (32-bit)

clip_image005

Please note, my box has multiple Oracle products, hence don’t get confused by the names in next few screens.

clip_image007

If your installation for the Oracle client was correct, you should able to see an entry for the Oracle driver like the one you could see the image above.

Click “Finish”. This will pop up another window. Under the “System DSN” tab we have to create our new DSN

clip_image008

Provide a name of your choice for the “Data Source Name” and “Description”

Make sure you have the “TNS Service Name” already available with the listener.ora file. Oracle 12c passwords are case sensitive. Hence make sure you are going to provide the case sensitive password while testing the connection.

If you don’t have previous experiences with creating net service names, please follow the instructions below.

clip_image009

Start “Net Configuration Assistant” from the Oracle Home and follow the images below.

clip_image010

Read the help texts available, especially if you are new to Oracle

clip_image011

Provide the service name, which is your database name usually.

clip_image012

 

clip_image013

Provide the FQDN (fully qualified domain name), ie, your computer name like “rajesh-pc.abc.com” in case if you are connected to a domain, else just the name of your computer, like “rajesh-pc”

clip_image014

I would suggest doing a test, if you are sure that all the details provided are correct & no need to test, you can skip this step

clip_image015

clip_image016

By default the configuration tool would suggest you the Oracle service name as new net service name, which you can change to any name. Just make sure that you will remember it.

clip_image017

clip_image018

Now let us test the new service name we have just created.

clip_image019

Once the Net Service Name is created, we will see both the scenarios using 2 different asp files, both using different connection approaches

(ASP sample was copied from here)

1. Connecting to 12c using DSN

[code language=”vb” gutter=”false”]

<html>
<head>
<title>Connecting to an Oracle database using ODBC and DSN connection</title>
</head>
<body>
<%
SET myConn=SERVER.createobject("adodb.connection")
myConn.Open "DSN=BAC;" & _
"Uid=APPS;" & "Pwd=APPS"
SQLStr="SELECT BANK_ID, BANK_NAME, BANK_TYPE FROM BAC_BANKS"
SET result=adoCon.execute(SQLStr)
IF NOT result.EOF thEN
response.write("<h2>Oracle ASP Example</h2>")
response.write("<p>Connecting to the Oracle11g database using ODBC & without a DSN</p>")
response.write("<table BORDER=3 BGCOLOR=#0099CC><tr><th>BANK ID</th>" & _
"<th>Name</th><th>TYPE</th>")
WHILE NOT result.EOF
response.write("<tr><td>" & result("BANK_ID") & "</td>")
response.write("<td>" & result("BANK_NAME") & "</td>")
response.write("<td>" & result("BANK_TYPE") & "</td></tr>")
result.movenext()
WEND
response.write("</table>")
ELSE
response.write("<p>Error retrieving bank data!!</p>")
END IF
adoCon.Close()
%>
</body>
</html>

[/code]

2. Connecting to 12c without DSN

[code language=”vb” gutter=”false”]

<%
Dim adoCon ‘Holds Connection
Dim rsViewRecords ‘Holds Record Set

‘ Initiate connection

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "provider=oraoledb.oracle;data source=SCT;user id=APPS;password=APPS"

if Err.Number <> 0 then
Response.Clear()
response.Write "<hr>ORASESSION Error<br>" & err.number & " — " & err.Description & "<hr>"
response.End
end if

SQLStr="SELECT BANK_ID, BANK_NAME, BANK_TYPE FROM BAC_BANKS"
SET result=adoCon.execute(SQLStr)
IF NOT result.EOF thEN
response.write("<h2>Oracle ASP Example</h2>")
response.write("<p>Connecting to the Oracle11g database using ODBC & without a DSN</p>")
response.write("<table BORDER=3 BGCOLOR=#0099CC><tr><th>BANK ID</th>" & _
"<th>Name</th><th>TYPE</th>")
WHILE NOT result.EOF
response.write("<tr><td>" & result("BANK_ID") & "</td>")
response.write("<td>" & result("BANK_NAME") & "</td>")
response.write("<td>" & result("BANK_TYPE") & "</td></tr>")
result.movenext()
WEND
response.write("</table>")
ELSE
response.write("<p>Error retrieving bank data!!</p>")
END IF
adoCon.Close()
%>

[/code]

Hope this helps few “Classic ASP” guys out there ;)

regards,

rajesh

Featured

Active Directory Listing Software

Hi guys

Today I am going to share a compact software that could generate moderately detailed information about the Windows active directory member computers, servers and users from a single console, that I started developing many years back.

This software helps you to export the fetched details to an excel sheet for future references. While there are no restrictions exclusively attached to the usage of this software, the source code for the application is ONLY available against written requests. Refer the readme.txt file for more details.

Please download the zip file from this link and extract to a single folder. Double click open “WpfWMI” to run the software (No installations required). We are using open source EPPlus.dll for the excel export and you must insure that the dll file is within the same folder of the executable file (WpfWMI.exe)

Pre-requisites: The computer you are running this software MUST have .Net framework 4.5 installed.

Expected bugs:

  • When you are not connected to a domain, software will show you a message “Not connected to Domain, Aborting”. However, the main application will load and trying to load objects will crash. I’ll fix this the earliest possible.
  • If your internal & external domains have same name, for example internal domain name is “abc.com” and your public website is “www.abc.com”, you may experience the above mentioned crash while connected to your network over a VPN connection.
  • Poorly designed About, help sections. I apologize for the same as I never had intensions to release it for public usage. Will try to accommodate as much with next public release.

Test it & post your comments and suggestions.

regards,

rajesh

Learning WPF, CSharp(C#) with me | Part #2

Part #2

Subject: Why each trainer stick to console based development when my intention is to learn how to make “real” applications?

Hi guys,

Have you taken a training for development recently? If you are an established developer in any technology stack, the most frustrating thing could be the “basics”, you would groan and stir loads in the seat when a junior trainer goes through the concepts, basic commands set etc & would try to make the logic behind the approach

For example, if you want to learn C#, that you could use Web applications or desktop applications, you have a preset mind that wants to develop the applications those a user could interact with visibly. Not something that open a console and shows you few outputs and closes down.

I have asked the same question to many junior trainers during the courses and none was ever able to give me satisfactory answer. During my last attempt to refresh my C# skills I came to a conclusion towards this irritating question and it is simple like this

A console application when you want to print something on the screen, you just write a code snippet like this

static void Main(){

Console.WriteLine(“Hello World”);

}

And if you want to do the same with a Desktop application, the efforts are like this

You design a form (I will not use the real technical terms here, not at this stage)

You will add textboxes or labels to show “Hello World” (and you don’t really know how to make them at the first level)

Then you will develop the code part that should show the “Hello World” finally when the application starts

So for a trainer it becomes easy to deal with code part, which is the most important element of an application, rather than trying to teach a student the GUI development.

I will try to explain what I have assumed using real examples

Hellow World console application

[code language=”csharp” gutter=”false”]
using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace HellowWorldConsole

{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hellow World");
}
}

}
[/code]

Running the application will open a console window and show you the following

image

Now let us develop the same Hello World application using WPF

My GUI part has the following XAML Code

[code language=”xml” gutter=”false”]
<Window x:Class="HelloWorldWPF.MainWindow"
xmlns="<a href="http://schemas.microsoft.com/winfx/2006/xaml/presentation"">http://schemas.microsoft.com/winfx/2006/xaml/presentation"</a&gt;
xmlns:x="<a href="http://schemas.microsoft.com/winfx/2006/xaml"">http://schemas.microsoft.com/winfx/2006/xaml"</a&gt;
Title="MainWindow" Height="350" Width="525" Loaded="Window_Loaded">
<Grid>
<TextBox x:Name="HWorld" Margin="95,150,117,124"/>
</Grid>

</Window>
[/code]
and the code behind is

[code language=”csharp” gutter=”false”]

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Data;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Imaging;

using System.Windows.Navigation;

using System.Windows.Shapes;

namespace HelloWorldWPF

{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}

private void Window_Loaded(object sender, RoutedEventArgs e)
{
this.HWorld.Text = "Hello World";
}
}

}
[/code]

and the output of both portion is seen as below

image

Now, it’s a common tendency of a seasoned programmer to perfect the GUI, because it creates the initial appeal. As you could see my textbox and the text appearing is not in sync, the height of the textbox is much bigger than the text that it looks somewhat mismatch…the time spent to perfect the GUI could become a real hurdle to progress with the training which must be limited certain hours per course!

Instead the trainers would approach the course, trying to teach the student the maximum coding part, then once the students are familiar with the technology, letting them explore the GUI development (Unless the course has the GUI development exclusively listed under the curriculum)

Once the above idea settled within me comfortably, I managed to sit patiently and learn the “basics” better than ever & was able to map what I learned to GUI much easily. Interested? However, my approach with this series will be developing GUI elements along with the basic coding. Do you have a different approach? do let me know through comments.

with the 1st part of this series I have posted a complete WPF solution to demonstrate what C# with WPF could do. That solution has many issues, like I am not handling folder/file access permissions or threading which is a must requirement when a program is going to run for unprecedented periods.

Be with me and we will be looking at much of them in coming parts.

regards,

rajesh

Learning WPF, CSharp(C#) with me | Part #1

Part #1

Subject: A Simple File Listing Utility using WPF & CSharp (C#)

Okay guys

Here I am once again! This time trying to learn WPF with C# to build my own small utilities to make my “Admin work” a lot easier. Further, I use my own blog as a reference guide most of them time! So, you are most welcome to use my “notes”

A WPF project always has two parts, XAML and the code behind. While XAML maintains the GUI part of it, Code part is where you write supporting code.

[code language=”xml” gutter=”false”]
<Window x:Class="WpflisttoGrid.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation&quot; xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml&quot; Title="MainWindow" Height="513" Width="877" Loaded="Window_Loaded">
<DockPanel>
<Menu DockPanel.Dock="Top" Height="24"></Menu>

<StatusBar DockPanel.Dock="Bottom" Height="24">
<StatusBarItem x:Name="StatusItem" Content="Ready" Width="Auto" HorizontalAlignment="Left" VerticalAlignment="top"></StatusBarItem>
</StatusBar>

<Grid>
<GroupBox x:Name="MyParameters">
<Grid x:Name="MyParameters_Grid">
<Label x:Name="lblPath" Content="Folder" HorizontalAlignment="Left" Margin="6,19,0,0" VerticalAlignment="Top"/>
<Label x:Name="lblFileType" Content="File Type" HorizontalAlignment="Left" Margin="6,47,0,0" VerticalAlignment="Top"/>
<TextBox x:Name="txtPath" HorizontalAlignment="Left" Height="23" Margin="83,22,0,0" TextWrapping="NoWrap" VerticalAlignment="Top" Width="258"/>
<TextBox x:Name="txtType" HorizontalAlignment="Left" Height="23" Margin="83,49,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
<Button x:Name="buttonFldrOpen" HorizontalAlignment="Left" Margin="346,22,0,0" VerticalAlignment="Top" Height="23" Click="buttonFldrOpen_Click" >
<StackPanel Orientation="Horizontal">
<Image Source="/Resources/folder_Open_32xMD.png" Stretch="Uniform" Height="18" Width="22"/>
</StackPanel>
</Button>
<Button Content="Process" HorizontalAlignment="Left" Margin="83,84,0,0" VerticalAlignment="Top" Width="75" Click="Button_Click"/>
</Grid>
</GroupBox>

<DataGrid x:Name="Grid1" Margin="68,159,89,40" IsReadOnly="True" SelectionChanged="Grid1_SelectionChanged" MouseDoubleClick="Grid1_MouseDoubleClick" SelectionUnit="Cell"/>

</Grid>

</DockPanel>

</Window>
[/code]

and the CSharp code portion as below

[code language=”csharp” gutter=”false”]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.IO;
using System.Data;
//Required for folder browswer & so
using System.Windows.Forms;

namespace WpflisttoGrid
{
///
<summary>
/// Interaction logic for MainWindow.xaml
/// </summary>

public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
//Setup the gridview to generate columns automatically against the file lists
this.Grid1.AutoGenerateColumns = true;
//We will store the text box values into local variables for easy understanding
//You may refer them straight away as defined columns
string filepath = this.txtPath.Text;
string filetype = this.txtType.Text;
//We will build a LIST of class myFiles
//Which will have chosen columns from the directory info file info results
List<myFiles> newList = new List<myFiles>();
try
{
foreach (FileInfo myfile in (new DirectoryInfo(@filepath).GetFiles(filetype, SearchOption.AllDirectories)))
//SearchOption.TopDirectoryOnly lists files from the top folder, ie, if a folder has sub-folders within
//SearchOption.TopDirectoryOnly will isolate the top folder & list all files from it
//SearchOption.AllDirectories will recursively pick all files from all folders those match type of files PDF
//As a developer who is learning
//I will leave enhancements to your logic
//This application could have
//Files those matching certain "contents"
//You can change the SearchOption.AllDirectories to SearchOption.TopDirectoryOnly programmatically…etc
{
//Calculate the time difference between the file creation and as on this moment
double _fileAge = Math.Round((DateTime.Now – myfile.CreationTime).TotalDays, 0);
//Add rows to the new list using new instance of the "Constructor" myFiles by passing the file details
newList.Add(new myFiles(myfile.FullName.ToString(), myfile.CreationTime, _fileAge));
}
//The following you could use for showing additional details
//this.StatusItem.Content = myfileList.Count().ToString();
//this.Grid1.ItemsSource = myfileList;
//System.Diagnostics.Debug.WriteLine(myfileList.Count());
this.Grid1.ItemsSource = newList;
this.Grid1.Columns[0].Header = "File Name";
this.Grid1.Columns[1].Header = "Creation Time";
this.Grid1.Columns[2].Header = "File Age(Days)";
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message + ex.Source);
}
}

private void Grid1_SelectionChanged(object sender, SelectionChangedEventArgs e)
{

}
private void Grid1_MouseDoubleClick(object sender, MouseButtonEventArgs e)
{
// System.Windows.MessageBox.Show()
//https://stackoverflow.com/questions/47309073/c-sharp-wpf-gridview-will-not-show-data-unless-clicked
try
{
var cellInfo = Grid1.SelectedCells[0];
var content = (cellInfo.Column.GetCellContent(cellInfo.Item) as TextBlock).Text;
//System.Windows.MessageBox.Show(content);
System.Diagnostics.Process.Start(@content);
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message + ex.Source);
}
}

private void buttonFldrOpen_Click(object sender, RoutedEventArgs e)
{
FolderBrowserDialog folderDlg = new FolderBrowserDialog();
folderDlg.ShowNewFolderButton = false; //Don’t allow users to create new folders from this dialogue
folderDlg.RootFolder = Environment.SpecialFolder.MyComputer;
// Show the FolderBrowserDialog.
DialogResult result = folderDlg.ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
this.txtPath.Text = folderDlg.SelectedPath;
Environment.SpecialFolder root = folderDlg.RootFolder;
}
}

private void Window_Loaded(object sender, RoutedEventArgs e)
{
this.txtType.Text = "*.*";
}
}

public class myFiles
{
public string fName { get; set; }
public DateTime fCreationDate { get; set; }
public double fAge { get; set; }

public myFiles(string fname, DateTime fcdate, double fage)
{
fName = fname;
fCreationDate = fcdate;
fAge = fage;

}

}
}
[/code]

This project should be used as a pilot to learn both WPF & CSharp (C#) rather than a commercial product as I am not using threading or channeling the background works. So once the Process button clicked, the GUI become irresponsive until the file lists populated, and could make a user feel the system is stuck.

Once the file list is populated in the GridView, one can double click the file name to start a fresh instance of associated program. So be careful, you may be running an executable that could cause unexpected problems.

Optionally you may download the Visual Studio Solution from here

regards,

rajesh