Blog Archives
asp .net grid control common gridview operations update edit cancel sort paging
There are many ways to accomplish the end result of what is displayed below.
To display multiple records of data on the same page, you may alternatively use Repeater (my favorite of the dataview controls), DataList or ListView (also great).
FormView and DetailsView are similar, but should be limited to smaller sets of data IMO or in a system where limited results are only displayed in this control after selection from a search or query filter.
The control on the HTML design side:
<asp:UpdatePanel ID="upnlContent" runat="server" UpdateMode="Conditional" ChildrenAsTriggers="false">
<ContentTemplate>
<div id="modal" style="display: none;" title="Application Message">
<asp:Label ID="lblError" runat="server"></asp:Label>
</div>
<asp:GridView ID="gvDataList" runat="server" AllowPaging="True" OnRowEditing="gvDataList_EditRecord"
OnRowCancelingEdit="gvDataList_CancelRecord" OnRowUpdating="gvDataList_UpdateRecord"
AutoGenerateColumns="False" AllowSorting="true" PageSize="50" DataKeyNames="TreeID"
OnRowCommand="gvDataList_RowCommand" OnSorting="gvDataList_Sorting" OnPageIndexChanging="gvDataList_PageIndexChanging">
<AlternatingRowStyle BackColor="#EFEFEF" />
<Columns>
<asp:BoundField DataField="TreeID" HeaderText="TreeID" InsertVisible="False" SortExpression="TreeID"
ReadOnly="True" />
<asp:TemplateField HeaderText="ActiveStatus" SortExpression="ActiveStatus">
<ItemTemplate>
<asp:Label ID="lblActiveStatus" runat="server" Text='<%# Eval("ActiveStatus")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="chkActiveStatus" runat="server" Checked='<%# Convert.ToBoolean(Eval("ActiveStatus")) %>'
Enabled='<%# GetEditPermission("activestatus") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name" SortExpression="Name">
<ItemTemplate>
<asp:HyperLink ID="Name" runat="server" NavigateUrl='<%# "~/Pages/ExistingOrange.aspx?TreeID=" + Eval("TreeID")%>'>
<%# Eval("Name")%>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tbtreename" runat="server" Text='<%# Eval("Name") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Description" SortExpression="Description">
<ItemTemplate>
<asp:HyperLink ID="hltreedescription" runat="server" NavigateUrl='<%# "~/Pages/ExistingOrange.aspx?TreeID=" + Eval("TreeID")%>'>
<%# Eval("Description")%>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tbtreedescription" runat="server" Text='<%# Eval("Description") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" SortExpression="CountryID" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="lblCountryID" runat="server" Text='<%# getcountryname(Eval("CountryID"))%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlCountryID" runat="server" Enabled='<%# GetEditPermission("allowregion") %>'
DataSource='<%# countriesdropdown %>' DataTextField='Description' DataValueField="CountryID" />
</EditItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="LastUpdated" HeaderText="Last Updated" SortExpression="LastUpdated"
ReadOnly="true" />
<asp:BoundField DataField="ExpirationDate" HeaderText="Expiration Date" InsertVisible="False"
SortExpression="ExpirationDate" ReadOnly="True" />
<asp:BoundField DataField="runcount" HeaderText="Pick Count" InsertVisible="False"
SortExpression="PickCount" ReadOnly="True" />
<asp:CommandField ButtonType="Button" EditText="Edit" UpdateText="Update" CancelText="Cancel"
HeaderText="Edit Row" ShowEditButton="true" />
<asp:ButtonField CommandName="NewOrange" ButtonType="Button" Text="Create" HeaderText="New Orange" />
</Columns>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
The Events and functions attached to the control on the code behind side:
private void LoadData(string sort, bool asc)
{
DAL.AC_DataClassesDataContext dc = new DAL.AC_DataClassesDataContext();
var data = (from Trees in dc.Trees
join Oranges in dc.Oranges on new { TreeID = Trees.TreeID } equals new { TreeID = Convert.ToInt32(Oranges.TreeID) } into Oranges_join
from Oranges in Oranges_join.DefaultIfEmpty()
group new { Oranges, Trees } by new
{
Trees.TreeID,
Trees.ActiveStatus,
Trees.Name,
Trees.Description,
Trees.CountryID,
Trees.LastUpdated
} into g
select new
{
TreeID = (System.Int32?)g.Key.TreeID,
ExpirationDate = (System.DateTime?)g.Max(p => p.Oranges.PickDate),
PickCount = g.Count(),
ActiveStatus = (System.Byte?)g.Key.ActiveStatus,
g.Key.Name,
g.Key.Description,
g.Key.CountryID,
LastUpdated = (System.DateTime?)g.Key.LastUpdated
});
data = data.OrderByField(sort, asc); //OrderByField uses dynamic linq library which is a free additional open source download from MS
try
{
data = data.Where(g => g.TreeID == Convert.ToInt32(ddlTreeIDs.SelectedValue));
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
utils.ShowJQueryModal(this, ex); //static custom class I created to show dialog containing lblerror text
}
gvDataList.DataSource = data;
gvDataList.DataBind();
upnlContent.Update();
}
protected void gvDataList_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "NewOrange")
{
string redirecturl = "~/Pages/NewOrange.aspx?TreeID={0}"; //this page should be pretty self explanatory
string TreeID = (((GridView)sender).Rows[Convert.ToInt32(e.CommandArgument)].Cells[0]).Text;
redirecturl = String.Format(redirecturl, TreeID);
Response.Redirect(redirecturl);
}
}
//example of a mechanism that can be used to change style or visibility of controls specific to user access
public bool GetEditPermission(string permtype)
{
switch (permtype.ToLower())
{
case "other":
if (utils.usergroup == "admin")
{
return true;
}
else return false;
case "allowregion":
if (utils.usergroup == "admin")
{
return true;
}
else return false;
default:
return true;
}
}
protected void gvDataList_UpdateRecord(object sender, GridViewUpdateEventArgs e)
{
DAL.AC_DataClassesDataContext dc = new DAL.AC_DataClassesDataContext();
int TreeID, activestatus, CountryID;
string Name, Description;
try
{
TreeID = Convert.ToInt32(e.Keys[0]); //should be 0
activestatus = Convert.ToInt32(((CheckBox)gvDataList.Rows[e.RowIndex].FindControl("chkActiveStatus")).Checked);
Name = Convert.ToString(((TextBox)gvDataList.Rows[e.RowIndex].FindControl("tbName")).Text);
Description = Convert.ToString(((TextBox)gvDataList.Rows[e.RowIndex].FindControl("tbDescription")).Text);
CountryID = Convert.ToInt32(((DropDownList)gvDataList.Rows[e.RowIndex].FindControl("ddlCountryID")).SelectedValue);
}
catch (Exception ex)
{
//throw new Exception("Error retrieving grid values for update.");
lblError.Text = "Error retrieving grid values for update. Details: " + ex.ToString();
utils.ShowJQueryModal(this);
upnlContent.Update();
return; //unassigned local variables if this is skipped or exception now thrown
}
int result = dc.ExecuteCommand("update Trees set ActiveStatus={1}," +
"Name={2},Description={3}," +
"CountryID={4} where TreeID={0}",
TreeID, activestatus, Name, Description,
CountryID);
if (result == 1)
{
}
else
{
lblError.Text = "Record failed to update.";
utils.ShowJQueryModal(this); //static custom class I created to show modal dialog containing lblerror text
}
gvDataList.EditIndex = -1;
LoadData();
}
protected void gvDataList_EditRecord(object sender, GridViewEditEventArgs e)
{
gvDataList.EditIndex = e.NewEditIndex;
LoadData();
}
protected void gvDataList_CancelRecord(object sender, GridViewCancelEditEventArgs e)
{
gvDataList.EditIndex = -1;
LoadData();
}
private string ConvertSortDirectionToSql(SortDirection sortDirection)
{
string newSortDirection = String.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}
protected void gvDataList_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDataList.PageIndex = e.NewPageIndex;
gvDataList.DataBind();
}
protected void gvDataList_Sorting(object sender, GridViewSortEventArgs e)
{
bool asc = true; //default to true per usual .net behavior
if (ViewState[e.SortExpression] != null)
{
asc = (!(bool)ViewState[e.SortExpression]);
ViewState[e.SortExpression] = asc;
}
else
{
ViewState[e.SortExpression] = true;
}
LoadData(e.SortExpression, asc);
}
//Show modal dialog using jquery. Requires javascript reference/link to jquery in the page.
public static void ShowJQueryModal(Page PageInstance, Exception ex)
{
ClientScriptManager ClientScript = PageInstance.ClientScript;
AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(PageInstance, PageInstance.GetType(), "preparemodal",
"<script type=\"text/javascript\">$('#modal').dialog({autoOpen: false, modal: true });</script>", false);
AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(PageInstance, PageInstance.GetType(), "loadmodal",
"<script type=\"text/javascript\">$('#modal').dialog('open');</script>", false);
}
Links and references to Dynamic LINQ may change over time (no pun intended).
At the time of writing this they can be found on MS and Scott Gu’s blog. If you cannot find it, send me an email and I can send it to you or you can refer to my post on OrderBy Column Name for the exact extension method.
OrderBy Column Name as String using Linq C# .Net Dynamic Sorting of Anonymous Types
If you’re familiar with Linq, you have undoubtedly used the popular “OrderBy” extension method. Unfortunately, this method does not accept a string value with the column name.
var data = from i in db.tablename
select i;
repeatername.datasource = data.OrderBy(i=>i.columnname); //this works
repeatername.datasource = data.OrderBy("columnname"); //this does not
To resolve, you can add the following small class or just the method to a generic DLL you use in your web application projects or create a new class or project for your extension methods.
Kudos to R. Prestol for help researching this one.
using System.Linq;
using System.Linq.Expressions;
using System;
namespace YourAppName.Web
{
public static class extensionmethods
{
public static IQueryable<T> OrderByField<T>(this IQueryable<T> q, string SortField, bool Ascending)
{
var param = Expression.Parameter(typeof(T), "p");
var prop = Expression.Property(param, SortField);
var exp = Expression.Lambda(prop, param);
string method = Ascending ? "OrderBy" : "OrderByDescending";
Type[] types = new Type[] { q.ElementType, exp.Body.Type };
var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
return q.Provider.CreateQuery<T>(mce);
}
}
}
Returning to our first code snippet, you can now do:
repeater.datasource = data.OrderByField("columnname");
Since this is an extension method, and exists in a class within the same namespace as your project or other referenced assembly, you can call the method directly from the IQueryable Linq data object without requiring any other inherits; this works similar to override functionality.
Read up about extension methods on MSDN or Google for other cool tricks you can do. Enjoy. 😉
References
Extension Methods (MSDN), http://msdn.microsoft.com/en-us/library/bb383977.aspx
Winforms Databinding
Snippets below have been condensed from their original sources for brevity. See references for original articles.
Dataset usage:
using System;
using System.Data;
using System.Data.SqlClient;
namespace Microsoft.AdoNet.DataSetDemo
{
class NorthwindDataSet
{
static void Main()
{
string connectionString = GetConnectionString();
ConnectToData(connectionString);
}
private static void ConnectToData(string connectionString)
{
//Create a SqlConnection to the Northwind database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();
// A table mapping names the DataTable.
adapter.TableMappings.Add("Table", "Suppliers");
// Open the connection.
connection.Open();
Console.WriteLine("The SqlConnection is open.");
// Create a SqlCommand to retrieve Suppliers data.
SqlCommand command = new SqlCommand(
"SELECT SupplierID, CompanyName FROM dbo.Suppliers;",
connection);
command.CommandType = CommandType.Text;
// Set the SqlDataAdapter's SelectCommand.
adapter.SelectCommand = command;
// Fill the DataSet.
DataSet dataSet = new DataSet("Suppliers");
adapter.Fill(dataSet);
// Create a second Adapter and Command to get
// the Products table, a child table of Suppliers.
SqlDataAdapter productsAdapter = new SqlDataAdapter();
productsAdapter.TableMappings.Add("Table", "Products");
SqlCommand productsCommand = new SqlCommand(
"SELECT ProductID, SupplierID FROM dbo.Products;",
connection);
productsAdapter.SelectCommand = productsCommand;
// Fill the DataSet.
productsAdapter.Fill(dataSet);
// Close the connection.
connection.Close();
Console.WriteLine("The SqlConnection is closed.");
// Create a DataRelation to link the two tables
// based on the SupplierID.
DataColumn parentColumn =
dataSet.Tables["Suppliers"].Columns["SupplierID"];
DataColumn childColumn =
dataSet.Tables["Products"].Columns["SupplierID"];
DataRelation relation =
new System.Data.DataRelation("SuppliersProducts",
parentColumn, childColumn);
dataSet.Relations.Add(relation);
Console.WriteLine(
"The {0} DataRelation has been created.",
relation.RelationName);
}
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=Northwind;"
+ "Integrated Security=SSPI";
}
}
}
Binding DataGridView:
private void GetData(string selectCommand)
{
try
{
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
String connectionString =
"Integrated Security=SSPI;Persist Security Info=False;" +
"Initial Catalog=Northwind;Data Source=localhost";
// Create a new data adapter based on the specified query.
dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
catch (SqlException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
References:
MSDN, “DataSet Class”, http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx
MSDN, “How to: Bind Data to the Windows Forms DataGridView Control”, http://msdn.microsoft.com/en-us/library/fbk67b6z.aspx
Out of Office Reply 