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
