I'm working on a ASP.Net Web Forms Application.Currently SqlDataReaders directly bind to the two grid views. I need to change the data set before binding it to the grid view .There are two datasets coming from Stored Procedure (Two select Queries). I need to edit both of them and replace some data before binding to grids .What is the best way to achieve this.?
private void BindTable()
{
LiteralMessage.Text = "";
RecentLiteralMessage.Text = "";
ErrorLiteralMessege.Text = "";
var isStandbySelected = SelectedDatabase.SelectedValue == "stats";
using (var db = new Database(isStandbySelected))
{
try
{
//db.BeginTransaction(IsolationLevel.Snapshot);
db.BeginTransaction(); //Need a transaction block to stop from closing connection
db.StoredProcedure = "dbo.NewExportList";
if (!string.IsNullOrEmpty(TextBoxNameFilter.Text))
db.AddParameter("@nameFilter", TextBoxNameFilter.Text);
db.AddParameter("@excludedExports", CheckBoxExcludedExports.Checked);
db.AddParameter("@RunOnReportingServer", SelectedDatabase.SelectedValue == "stats" );
if(CheckBoxRecentlyRun.Checked)
db.AddParameter("@recentExports", true);
System.Data.SqlClient.SqlDataReader reader = db.GetDataReader();
GridViewExports.DataSource = reader;
GridViewExports.DataBind();
if (GridViewExports.Rows.Count > 0)
{
GridViewExports.Visible = true;
}
else
{
GridViewExports.Visible = false;
LiteralMessage.Text = "No Results";
}
GridViewRecentExports.DataSource = null; //clear any exsisting data
if (reader.NextResult()) //Get the second data set if any
{
GridViewRecentExports.DataSource = reader;
}
GridViewRecentExports.DataBind();
if (GridViewRecentExports.Rows.Count > 0)
{
GridViewRecentExports.Visible = true;
}
else
{
GridViewRecentExports.Visible = false;
RecentLiteralMessage.Text = "No Results";
}
db.CloseConnection();
//db.CommitTransaction();
}
catch (Exception ex)
{
}
}
CodePudding user response：
Ok, there are two approaches common used here.
First, lets take a sample GV, and work though the two common apporaches here.
Our simple markup:
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="ID" CssClass="table">
<Columns>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="HotelName" HeaderText="HotelName" />
<asp:BoundField DataField="Description" HeaderText="Description" />
<asp:BoundField DataField="Nights" HeaderText="Nights" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="Price" HeaderText="Price" DataFormatString="{0:c2}"
ItemStyle-HorizontalAlign="Right" />
</Columns>
</asp:GridView>
Ok, and our code to load:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadGrid();
}
void LoadGrid()
{
DataTable rstData = new DataTable();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
string strSQL = "SELECT * FROM tblHotels ORDER BY HotelName";
using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn)) {
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
GridView1.DataSource = rstData;
GridView1.DataBind();
}
Ok, and we now have this:
so, with above, we say want the the total price for each row booking to show.
So, lets use the "common" approach here. We first add our extra control to the grid view. Lets use a plane jane text box:
eg: this:
<asp:BoundField DataField="Price" HeaderText="Price" DataFormatString="{0:c2}"
ItemStyle-HorizontalAlign="Right" />
<asp:TemplateField HeaderText="Total">
<ItemTemplate>
<asp:Label ID="lblTotal" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
so, we just added a label to the gv.
So, the event we use is the row data bound event This even is great, since it gives us the one data row, and the one grid view row to work with. This event also great for changing the color of a row, or text box, and of course also doing some calculation or setting up the value of a un-bound control - such as our label called
So, now in our row data bound event, we can do this:
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DataRowView gData = (DataRowView)e.Row.DataItem;
Label lblTotal = e.Row.FindControl("lblTotal") as Label;
decimal MyPrice = (int)gData["Nights"] * (decimal)gData["Price"];
lblTotal.Text = string.Format("{0:c2}", MyPrice);
}
}
And now our gird is this:
Ok, so above is the common approach.
However, a interesting approach?
Once you fill a data table, then you are free to process that table, and that EVEN includes adding new columns to the table!!!
So, lets dump (remove) our data bound row event.
lets CHANGE the label to use bound data from the table. So, our markup is now:
<asp:TemplateField HeaderText="Total">
<ItemTemplate>
<asp:Label ID="lblTotal" runat="server"
Text = '<%# string.Format("{0:c2}", Eval("MyTotal")) %>'
></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Now, we don't' have a column in data table called MyTotal, do we?
but, we can AFTER getting the table from the query or stored procedure ADD the table.
so, our grid load code now becomes this:
DataTable rstData = new DataTable();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
string strSQL = "SELECT * FROM tblHotels ORDER BY HotelName";
using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn)) {
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
rstData.Columns.Add("MyTotal", typeof(decimal));
foreach (DataRow MyRow in rstData.Rows)
{
MyRow["MyTotal"] = (int)MyRow["Nights"] * (decimal)MyRow["Price"];
}
GridView1.DataSource = rstData;
GridView1.DataBind();
}
Note how we just added a new column, and then did a table process loop to setup that value. The results of the GV are thus the same.
Last but not least?
FEW PEOPLE realize that a data table supports expressions!!! And when you modify values etc. the update automatic. So, in place of that row processing loop above? We could in fact do this:
DataColumn MyCol = new DataColumn("MyTotal", typeof(decimal));
MyCol.Expression = "[Nights] * [Price]";
rstData.Columns.Add(MyCol);
GridView1.DataSource = rstData;
GridView1.DataBind();
So, in most cases, I often just use the row data bound. And this event is nice since you don't write a loop, and for conditional format such as a row color change, or a column/cell format of color etc., or the new setting of the new text box? row data bound is nice.
But, you can also as noted, add columns, and loop process the data table, and you can even add columns that are based on expressions of other columns. You then send that updated and pre-processed table to the gridview as normal.
Also note that while I used a query, a store procedure would work the same way:
eg:
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (SqlCommand cmdSQL = new SqlCommand("MyStoreProcedure", conn)) {
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.Parameters.Add("@Active", SqlDbType.Bit).Value = 1;
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
DataColumn MyCol = new DataColumn("MyTotal", typeof(decimal));
MyCol.Expression = "[Nights] * [Price]";
rstData.Columns.Add(MyCol);
GridView1.DataSource = rstData;
GridView1.DataBind();