Create Your First RDLC (Local SSRS) Report in ASP.NET using DataSet C#

Following steps demonstrate how to create your first RDLC Report. This blog is divided into 2 parts:-

  1. Prepare the report from a single table and to solve the Report Viewer Configuration Error, if encountered.
  2. Prepare a single report from two different tables (Not-Related).

The database used here is Microsoft’s Northwind database.

Prepare a Report from a Single Table

# Add DataSet.

# Add DataTable
# Add Columns
# Add RDLC Report
# Choose DataSet
# Choose Fields 
# Choose Layout
# Choose Style
# Finish the Wizard. The report will be then displayed
# Add ReportViewer to the ASP.NET page
# Following is the designer code snippet after adding the ReportViewer control
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head runat="server">
		<title></title>
	</head>
	<body>

<form id="form1" runat="server">
			<asp:ScriptManager ID="ScriptManager1" runat="server">
			</asp:ScriptManager>
			<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="600">
			</rsweb:ReportViewer>
		</form>

	</body>
</html>
# Modifying the Code Behind section
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ReportViewer1.ProcessingMode = ProcessingMode.Local;
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
        Customers dsCustomers = GetData("select top 20 * from customers");
        ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
    }
}

private Customers GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["NorthWindConnectionString"]
                                              .ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (Customers dsCustomers = new Customers())
            {
                sda.Fill(dsCustomers, "DataTable1");
                return dsCustomers;
            }
        }
    }
}

# Next Compile the code. There’s a chance that you might encounter the following error

and then when you click Continue, you’ll be finally redirected to your page which will be displaying the following error message.

The error is pretty straight-forward. Just as the message suggests add the following code to the Web.Config file

<system.webServer>
	<validation validateIntegratedModeConfiguration="false" />
	<handlers>
		<add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
	</handlers>
</system.webServer>

 

Plz make sure that your PublicKeyToken is same as that of the Microsoft.ReportViewer.WebForms in the assemblies section of system.web.

# Next again run the code. You’ll be able to see the following result

You can see that field, Country, has appeared twice. This is because of the fact that I had done a GroupBy this field.

That’s it from this blog. Next as a continuation of it, I’ll be demonstrating how to display multiple tables(2) in a single report. You can find the link of that blog very shortly.
Thanks…

Execute DataBase Query for Inserted, Modified, & Deleted Rows only with the help of DataSet

Introduction

Here, what I am trying to do is that there will be a textbox in which the User will enter a unique CustomerId and then when he’ll click the Add button, the value should be displayed in a GridView (The GridView will be displaying the existing Customers for that User which, will be loaded from the database).

Similarly, if he wishes, he can add multiple Customers which will be displayed in the GridView.

 

And, he can edit the existing Customer Details also.

Finally, when he’ll click the update button the database query should be executed for only the Added, Modified, & Deleted rows.

Solution

When the Page is loaded for the very first time I will get the Customers details for the current User in a DataSet (which will be the source of the GridView) which I have written the DataSet in the ViewState of that page.


ViewState["customertData"] = ds;

Now whenever user is adding a new Customer by giving its Customer Id I will do the following


DataSet ds = new DataSet();
DataTable dt;

if (ViewState["customertData"] == null)  //If the ViewState is not defined
{
	dt = new DataTable();
	DataColumn uniqueID = new DataColumn("uniqueID");
	DataColumn customerId = new DataColumn("customerId");
	DataColumn excluded = new DataColumn("IsExcluded");
	dt.Columns.Add(customerId);
	dt.Columns.Add(excluded);
	ds.Tables.Add(dt);
}
else
{
	ds = ViewState["customertData"] as DataSet;
	dt = ds.Tables[0];
}

DataRow dRow = dt.NewRow();
dRow["uniqueID"] = System.Guid.NewGuid();
dRow["customerId"] = txtcustomerId.Text;
dRow["IsExcluded"] = false;

dt.Rows.Add(dRow);
grdDsClient.DataSource = ds;
grdDsClient.DataBind();

//store the current changes in the ViewState
ViewState["customertData"] = ds;
txtClientNo.Text = String.Empty;

Similarly In the RowUpdating & RowDeleting event of the gridView, I am modyfying the DataSet value with the updated records & accordingly writing it into the ViewState. NOTE: To Delete a row from the DataSet I am using the DataRow.Delete() method & not DataRow.Remove() OR DataRow.RemoveAt() methods.

RowDeleting 


protected void grdDsClient_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
  DataSet ds = ViewState["customertData"] as DataSet;
  //To get the deleted row in the DataSet.GetChanges method we need to use Delete method
  ds.Tables[0].Rows[e.RowIndex].Delete();

  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();
  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

 

RowUpdating 


protected void grdDsClient_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  string key = e.Keys["uniqueID"].ToString();
  string dsClient = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
  DataSet ds = ViewState["customertData"] as DataSet;

  DataView dv = ds.Tables[0].DefaultView;
  dv.Sort = "customerId";
  DataRowView[] drv = dv.FindRows(dsClient);

  if (drv.Count() > 0)
  {
	foreach(DataRowView dvTemp in drv)
	{
	  if (dvTemp["uniqueID"].ToString() != key)
	  {
		lblMessage.Text = "Duplicate value for the field DS-Client# is not allowed.";
		lblMessage.ForeColor = System.Drawing.Color.FromArgb(170, 46, 46);//red
		return;
	  }
	}
  }

  dv.Sort = "uniqueID";
  drv = dv.FindRows(key);
  drv = dv.FindRows(key);

  if (drv.Count() > 0)
  {
	drv[0]["customerId"] = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
	drv[0]["IsExcluded"] = ((CheckBox)(grdDsClient.SelectedRow.FindControl("chkIsExcluded"))).Checked;
  }

  grdDsClient.EditIndex = -1;
  grdDsClient.SelectedIndex = -1;
  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();

  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

Now finally, when the user clicks the Update Button, the application will fire the database query for only those rows of the DataSet which has been either added, modified, or deleted.


try
{
	foreach (DataRow dr in dsClient.Tables[0].Rows)
	{
		if (dr.RowState == DataRowState.Deleted)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET IsDeleted = 1, ModifiedBy = @ModifiedByDel, ModifiedOn = @ModifiedOnDel WHERE customerId = @customerIdDel";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdDel", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Original];
			cmd.Parameters.Add("@ModifiedByDel", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnDel", SqlDbType.DateTime).Value = DateTime.Now;cmd.ExecuteNonQuery();RecordUserActivity(varProviderUserKey, "VX", dr["customerId",DataRowVersion.Original].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Added)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "INSERT INTO [ManageCustomer] (uniqueID, customerId, VarID, [IsExcluded], [AddedBy], [AddedOn]) VALUES (@uniqueIDAdd, @customerIdAdd, @VarIDAdd, @IsExcludedAdd, @AddedByAdd, @AddedOnAdd)";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add(new SqlParameter("@uniqueIDAdd", dr["uniqueID"]));
			cmd.Parameters.Add(new SqlParameter("@customerIdAdd", dr["customerId"]));
			cmd.Parameters.Add(new SqlParameter("@VarIDAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@IsExcludedAdd", dr["IsExcluded"]));
			cmd.Parameters.Add(new SqlParameter("@AddedByAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@AddedOnAdd", DateTime.Now));cmd.ExecuteNonQuery();
			RecordUserActivity(varProviderUserKey, "VA", dr["customerId"].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Modified)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET customerId = @customerIdNew, IsExcluded = @IsExcludedNew, ModifiedBy = @ModifiedByUpdt, ModifiedOn = @ModifiedOnUpdt WHERE uniqueID = @uniqueIDUpdt";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdNew", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Current];
			cmd.Parameters.Add("@IsExcludedNew", SqlDbType.Bit).Value = dr["IsExcluded"];
			cmd.Parameters.Add("@ModifiedByUpdt", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnUpdt", SqlDbType.DateTime).Value = DateTime.Now;
			cmd.Parameters.Add("@uniqueIDUpdt", SqlDbType.UniqueIdentifier).Value = dr["uniqueID", DataRowVersion.Original];

			cmd.ExecuteNonQuery();

			if(dr["IsExcluded",DataRowVersion.Original] != dr["IsExcluded",DataRowVersion.Current] &&
			((bool)(dr["IsExcluded",DataRowVersion.Current])) == true)
				RecordUserActivity(varProviderUserKey, "VE", dr["customerId"].ToString(), con, tran);
		}
	}
	tran.Commit();
}
catch (Exception ex)
{
	tran.Rollback();
}
finally
{
	con.Close();
}

 

Explanation

Here, when the page is loaded for the very first time, I have loaded the information from the DataBase into a DataSet. This DataSet is then getting added in the ViewState of the page. Now whenever the User is doing some modifications, it will get updated in the ViewState of that Page only. Once the User clicks the Update button, I am checking, inside a seperate function(which is being called from the click event of the Update button), the RowState of each DataRow from the DataSet. More info regarding the enum DataRowState can be found here.

So for the modified rows only I am firing the database query. I have also kept the entire queries in the same transaction tran, so that I can Roll Back all the changes for any errors.

Finally in the click event of the Update Button, I am calling the DataBase.AcceptChanges() method to clear the RowState of all the rows of the DataBase before adding it again to the ViewState of the page.