Display Decrypted Data on Report.rdlc

Requirement : Creating a .rdlc report to display data in SQL database, some fields of which are in encrypted form.

Solution : Binding dataset to report at runtime.

Design Project to insert data into database in encrypted form.

To know how to use encryption please see my blog post http://encryptdecryptstring.blogspot.in/

    Now if you want to display this data on .rdlc Report you will have to decrypt the data which is fetched from database & is in encrypted form. So we cannot write function (.NET function to decrypt data) to decrypt data inside our SQL query, and if we try to write that function in a separate assembly and refer it in your report then it might possible but I was not able to do so and I was getting so many error while referring the dll inside report.

       So here I am explaining the solution where we bind the data set at run-time.

       We will create a report with a dataset created at design time but this dataset will have data in encrypted form, so we will replace this dataset with the new dataset having decrypted data(And since we are replacing this runtime dataset will have the same name which we used at design time).

Right click on your project and select "Add New Item…"Select Report.rdlc and click Add.

 

This will create a report and now we ‘Add New Dataset’ as shown below.

Select database connection (create new if required or select existing) and dataset properties as shown below.

Now we will update/reset this DataSet at runtime(so note the Name provided above(in my case EmpSalaryInfo_DS) which we will use in code).

Now add new windows form to display this report.

From Toolbar add ReportViewer control (available under Reporting section) to the form.

Select reportviewer control and select its properties, select Local Report’s property and set ReportEmbeddedResource to point to above created report path as shown below.

Now select code behind of above added form(Form1.cs)

Add Following function to your class which retrieves data from your database and decrypts the encrypted column. Don’t forget to replace connectionString variable with your database connection string.

         public DataSet GetDetails()

        {

SqlConnection connection;

            try

            {

                string connectionString = "Data Source=.;Initial Catalog=SalaryDetails;Persist Security Info=True;User ID=sa;Password=root@123";

                connection = new SqlConnection(connectionString);

                connection.Open();

                SqlCommand selectCommand = new SqlCommand("select * from EmpSalaryInfo", connection);

                SqlDataAdapter da = new SqlDataAdapter(selectCommand);

                DataSet dsSalaryDetails = new DataSet();

                da.Fill(dsSalaryDetails);

                // Bind the encrypted data to datagridview.

                DataTable dtUserSalaryDetails = new DataTable();

                dtUserSalaryDetails.Load(dsSalaryDetails.CreateDataReader());

                dataGridView1.DataSource = dtUserSalaryDetails;

                // Before returning the dataset decrypt all the columns which are encrypted.

                // Here i am decrypting "AnnualSalary" column which is only encrypted column in my case.

                for (int i = 0; i < dsSalaryDetails.Tables[0].Rows.Count; i++)

                {

                    dsSalaryDetails.Tables[0].Rows[i]["AnnualSalary"] = Crypto.DecryptData(Convert.ToString(dsSalaryDetails.Tables[0].Rows[i]["AnnualSalary"]));

                }

                return dsSalaryDetails;

            }

            catch (Exception ex)

            {

                throw;

            }

            finally

            {

                connection.Close();

            }

        }

Add Following function which Loads data from database and binds the Data Datasource(which in turn consist of dataset) to the report. Here we clear the existing datasources and binds the new one at runtime.

         private void LoadReport()

        {

            try

            {

                DataSet ds = GetDetails();

                ReportDataSource rds = new ReportDataSource("EmpSalaryInfo_DS", ds.Tables[0]);

                reportViewerControl.LocalReport.DataSources.Clear();

                reportViewerControl.LocalReport.DataSources.Add(rds);

                reportViewerControl.RefreshReport();

            }

            catch (Exception ex)

            {

                throw;

            }

        }

Call this function in constructor of form so that it gets called when form loads.

         public Form1()

        {

            InitializeComponent();

            LoadReport();

        }

Output:

Please let me know if you need source code for the same.. You can write to me on narenkedari@gmail.com