CRUD Operation in Master and Detail Radgrid
Do you know how to use RadGrid in .NET? Read this article to know more about one of the Telrik controls RadGrid. This article will help you to learn how to insert, delete, edit the data from parent and Child RadGrid.
In this article we will see one of the telerik controls which is a third party tool. We will see the working of master and detail radgrid. We will see the demo of inserting, updating and deleting the parent and the child radgrid using SQL database and c# language.
For this first we will create 2 tables employee and employeedetails.Below is its table structure.
//Employee table
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](200) NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_M_LabTestAttributes] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_M_LabTestAttributes_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
CREATE TABLE [dbo].[EmployeeDetails](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[SalaryStartRange] [varchar](50) NULL,
[SalaryEndRange] [varchar](50) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_M_LabTestAttributeDetails] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmployeeDetails] WITH CHECK ADD CONSTRAINT [FK_M_LabTestAttributeDetails_M_LabTestAttributes] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO
ALTER TABLE [dbo].[EmployeeDetails] CHECK CONSTRAINT [FK_M_LabTestAttributeDetails_M_LabTestAttributes]
GO
ALTER TABLE [dbo].[EmployeeDetails] ADD CONSTRAINT [DF_M_LabTestAttributeDetails_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
Now in the design table we have taken a radgrid with below format.
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<table>
<tr>
<td colspan="2">
<telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel2" runat="server">
</telerik:RadAjaxLoadingPanel>
<telerik:RadGrid ID="rgdEmployee" runat="server" GridLines="None" AllowAutomaticDeletes="false"
OnItemCreated="rgdEmployee_ItemCreated" Visible="true" AllowAutomaticInserts="false"
PageSize="5" AllowAutomaticUpdates="false" AllowPaging="True" AutoGenerateColumns="False"
OnDetailTableDataBind="rgdEmployee_DetailTableDataBind" EnableEmbeddedSkins="true"
OnItemCommand="rgdEmployee_ItemCommand" OnItemDataBound="rgdEmployee_ItemDataBound"
OnNeedDataSource="rgdEmployee_NeedDataSource" OnUpdateCommand="rgdEmployee_UpdateCommand">
<PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
<MasterTableView Width="100%" CommandItemDisplay="Top" InsertItemPageIndexAction="ShowItemOnCurrentPage"
Name="Master" DataKeyNames="Id" HorizontalAlign="NotSet" EditMode="EditForms"
AutoGenerateColumns="False" CommandItemSettings-ShowRefreshButton="false">
<Columns>
<telerik:GridBoundColumn DataField="EmployeeName" HeaderText="EmployeeName" SortExpression="EmployeeName"
UniqueName="EmployeeName" ColumnEditorID=" GridTextBoxColumnEditor1" MaxLength="200"
Visible="true" ItemStyle-CssClass="rgEditForm">
</telerik:GridBoundColumn>
<telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditEmployee"
HeaderText="Edit" ItemStyle-Width="200px" ItemStyle-Wrap="false">
</telerik:GridEditCommandColumn>
<telerik:GridButtonColumn ConfirmText="" ConfirmDialogType="RadWindow" ConfirmTitle="Delete"
CommandName="DeleteEmployee" ButtonType="ImageButton" UniqueName="DeleteEmployee"
HeaderText="Delete">
</telerik:GridButtonColumn>
</Columns>
<CommandItemSettings ShowRefreshButton="false" />
<DetailTables>
<telerik:GridTableView Name="Detail" Width="100%" runat="server" CommandItemDisplay="Top"
Visible="true" AllowPaging="false" DataKeyNames="Id" CommandItemSettings-ShowRefreshButton="false">
<Columns>
<telerik:GridBoundColumn DataField="SalaryStartRange" HeaderText="Salary StartRange"
MaxLength="100" Visible="true" SortExpression="SalaryStartRange" UniqueName="SalaryStartRange"
ItemStyle-CssClass="rgEditForm" ColumnEditorID="GridTextBoxColumnEditor2">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="SalaryEndRange" HeaderText="Salary EndRange"
MaxLength="100" Visible="true" SortExpression="NormalEndRange" UniqueName="SalaryEndRange"
ItemStyle-CssClass="rgEditForm" ColumnEditorID="GridTextBoxColumnEditor3">
</telerik:GridBoundColumn>
<telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditEmployeeDetail"
HeaderText="Edit" ItemStyle-Width="200px" ItemStyle-Wrap="false">
</telerik:GridEditCommandColumn>
<telerik:GridButtonColumn ConfirmText="" ConfirmDialogType="RadWindow" ConfirmTitle="Delete"
CommandName="DeleteEmployeeDetail" ButtonType="ImageButton" UniqueName="DeleteEmployeeDetail"
HeaderText="Delete">
</telerik:GridButtonColumn>
</Columns>
<CommandItemSettings ShowRefreshButton="false" />
</telerik:GridTableView>
</DetailTables>
</MasterTableView>
</telerik:RadGrid>
<telerik:GridTextBoxColumnEditor ID="GridTextBoxColumnEditor1" runat="server" TextBoxStyle-Width="200px" />
<telerik:GridTextBoxColumnEditor ID="GridTextBoxColumnEditor2" runat="server" TextBoxStyle-Width="200px" />
<telerik:GridTextBoxColumnEditor ID="GridTextBoxColumnEditor3" runat="server" TextBoxStyle-Width="200px" />
<telerik:RadWindowManager ID="RadWindowManager1" runat="server">
</telerik:RadWindowManager>
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</td>
</tr>
</table>
<input type="hidden" runat="server" id="hdnIsAddEdit" />
</div>
Note: GridTextBoxColumnEditor is used while editing the columns of grid like we use textbox in normal gridview.
Note: 1. EmployeeNameGridBind method will bind the master grid.
2. EmployeeDetailGridBind method binds the child grid.
3. We have kept the name of master grid as "master" and child grid as "detail".
4. For deleting the records we are setting IsActive column of table to false and by default it is set true.
5. After bindinf the grid with datasource we are not calling databind method as all these
actions is performed by NeedDatSource event automatically.
And in code behind we use below code:
string connection = ConfigurationManager.ConnectionStrings["Databaseconnstring"].ToString();
SqlConnection con = null;
public bool? isdetail = false;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand();
con.Open();
if (!IsPostBack)
{
EmployeeNameGridBind();
}
}
//this event will bind the detail grid.
protected void rgdEmployee_DetailTableDataBind(object sender, Telerik.Web.UI.GridDetailTableDataBindEventArgs e)
{
try
{
GridDataItem dataItem = (GridDataItem)e.DetailTableView.ParentItem;
int EmployeeId = Convert.ToInt32(dataItem.GetDataKeyValue("Id"));
string query = "select * from EmployeeDetails where isactive=1 and EmployeeId = " + EmployeeId + "";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
da.Fill(dt);
e.DetailTableView.DataSource = dt;
if (dt.Rows.Count == 0)
{
this.rgdEmployee.MasterTableView.NoDetailRecordsText = "No detail record";
}
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
}
protected void rgdEmployee_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
try
{
if (e.CommandName == "Update")
{
GridEditableItem editedItem = e.Item as GridEditableItem;
GridEditManager editMan = editedItem.EditManager;
if (e.Item.OwnerTableView.Name == "Detail")
{
isdetail = true;
int EmployeeDetailsId = Convert.ToInt32(((GridEditFormItem)e.Item).GetDataKeyValue("Id"));
int EmployeeId = Convert.ToInt32(editedItem.OwnerTableView.ParentItem.OwnerTableView.DataKeyValues[editedItem.OwnerTableView.ParentItem.DataSetIndex]["Id"]);
string SalaryStartRange = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("SalaryStartRange"))).Text);
string SalaryEndRange = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("SalaryEndRange"))).Text);
SqlCommand mySqlUpdate = new SqlCommand("update M_LabTestEmployeeDetails set SalaryStartRange = @ssr, SalaryEndRange = @ser where EmployeeId = @empid and Id = @id", con);
mySqlUpdate.Parameters.Add("@ssr", SqlDbType.VarChar).Value = SalaryStartRange;
mySqlUpdate.Parameters.Add("@ser", SqlDbType.VarChar).Value = SalaryEndRange;
mySqlUpdate.Parameters.Add("@empid", SqlDbType.Int).Value = EmployeeId;
mySqlUpdate.Parameters.Add("@id", SqlDbType.Int).Value = EmployeeDetailsId;
int i = mySqlUpdate.ExecuteNonQuery();
if (i > 0)
{
e.Item.OwnerTableView.ClearEditItems();
EmployeeDetailGridBind1(EmployeeId);
Label1.Text = "updated";
}
else
{
Label1.Text = "not updated";
rgdEmployee.MasterTableView.EditMode = GridEditMode.InPlace;
e.Canceled = true;
}
}
else
{
int Employeeid = Convert.ToInt16(((GridEditFormItem)e.Item).GetDataKeyValue("Id"));
SqlCommand mySqlUpdate = new SqlCommand("update Employee set EmployeeName = @en where Id = @id", con);
mySqlUpdate.Parameters.Add("@en", SqlDbType.VarChar).Value = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text);
mySqlUpdate.Parameters.Add("@id", SqlDbType.Int).Value = Employeeid;
int i = mySqlUpdate.ExecuteNonQuery();
if (i > 0)
{
rgdEmployee.MasterTableView.ClearEditItems();
EmployeeNameGridBind();
rgdEmployee.Rebind();
Label1.Text = "Record updated";
}
else
{
((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text = ((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text;
Label1.Text = "record not updated";
rgdEmployee.MasterTableView.EditMode = GridEditMode.EditForms;
rgdEmployee.Rebind();
}
}
}
else if (e.CommandName == "DeleteEmployeeDetail" && e.Item.OwnerTableView.Name == "Detail")
{
GridDataItem item = (GridDataItem)e.Item;
int Employeedetailids = Convert.ToInt32(item.GetDataKeyValue("Id"));
SqlCommand mySqlDelete = new SqlCommand("update EmployeeDetails set IsActive=0 where Id=@id", con);
mySqlDelete.Parameters.Add("@id", SqlDbType.Int).Value = Employeedetailids;
int i = mySqlDelete.ExecuteNonQuery();
if (i > 0)
rgdEmployee.Rebind();
else
Label1.Text = "Employee detail not delated";
}
else if (e.CommandName == "DeleteEmployee" && e.Item.OwnerTableView.Name == "Master")
{
GridDataItem item = (GridDataItem)e.Item;
int EmployeeIds = Convert.ToInt32(item.GetDataKeyValue("Id"));
SqlCommand mySqlDelete = new SqlCommand("update Employee set IsActive=0 where Id=@id", con);
mySqlDelete.Parameters.Add("@id", SqlDbType.Int).Value = EmployeeIds;
int i = mySqlDelete.ExecuteNonQuery();
if (i > 0)
{
EmployeeNameGridBind();
rgdEmployee.Rebind();
}
else
Label1.Text = "Employee not deleted";
}
else if (e.CommandName == "InitInsert" && e.Item.OwnerTableView.Name == "Detail")
{
hdnIsAddEdit.Value = "true";
foreach (GridDataItem item in rgdEmployee.MasterTableView.Items)
{
item.ChildItem.NestedTableViews[0].ClearEditItems();
item.ChildItem.NestedTableViews[0].Rebind();
}
}
else if (e.CommandName == "InitInsert" && e.Item.OwnerTableView.Name == "Master")
{
hdnIsAddEdit.Value = "true";
rgdEmployee.MasterTableView.ClearEditItems();
}
else if (e.CommandName == "PerformInsert" && e.Item.OwnerTableView.Name == "Detail")
{
GridEditableItem editedItem = e.Item as GridEditableItem;
GridEditManager editMan = editedItem.EditManager;
int EmployeeId = 0;
GridDataItem parentItem = (GridDataItem)(e.Item.OwnerTableView.ParentItem);
if (parentItem != null)
EmployeeId = Convert.ToInt16((parentItem.OwnerTableView.DataKeyValues[parentItem.ItemIndex]["Id"]));
string SalaryStartRange = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("SalaryStartRange"))).Text);
string SalaryEndRange = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("SalaryEndRange"))).Text);
SqlCommand mySqlUpdate = new SqlCommand("Insert into EmployeeDetails(EmployeeId,SalaryStartRange,SalaryEndRange) values(@employeeid,@ssr,@ser)", con);
mySqlUpdate.Parameters.Add("@ssr", SqlDbType.VarChar).Value = SalaryStartRange;
mySqlUpdate.Parameters.Add("@ser", SqlDbType.VarChar).Value = SalaryEndRange;
mySqlUpdate.Parameters.Add("@employeeid", SqlDbType.Int).Value = EmployeeId;
int i = mySqlUpdate.ExecuteNonQuery();
if (i > 0)
{
e.Item.OwnerTableView.IsItemInserted = false;
int Employeeid = Convert.ToInt32(parentItem.OwnerTableView.DataKeyValues[parentItem.ItemIndex]["Id"]);
string query = "select * from EmployeeDetails where isactive=1 and EmployeeId = " + Employeeid + "";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
da.Fill(dt);
rgdEmployee.DataSource = dt;
Label1.Text = "Employee detail inserted";
}
else
{
Label1.Text = "Employee detail not inserted";
rgdEmployee.MasterTableView.EditMode = GridEditMode.InPlace;
e.Canceled = true;
}
}
else if (e.CommandName == "PerformInsert" && e.Item.OwnerTableView.Name == "Master")
{
GridEditableItem editedItem = e.Item as GridEditableItem;
GridEditManager editMan = editedItem.EditManager;
string EmployeeName = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text);
SqlCommand mySqlUpdate = new SqlCommand("Insert into Employee(EmployeeName) values(@an)", con);
mySqlUpdate.Parameters.Add("@an", SqlDbType.VarChar).Value = EmployeeName;
int i = mySqlUpdate.ExecuteNonQuery();
if (i > 0)
{
e.Item.OwnerTableView.IsItemInserted = false;
EmployeeNameGridBind();
Label1.Text = "Employee inserted";
}
else
{
((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text = ((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text;
Label1.Text = "Employee not inserted";
rgdEmployee.MasterTableView.EditMode = GridEditMode.EditForms;
rgdEmployee.Rebind();
}
}
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
}
protected void rgdEmployee_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
EmployeeNameGridBind();
}
protected void rgdEmployee_ItemCreated(object sender, Telerik.Web.UI.GridItemEventArgs e)
{
try
{
if (e.Item is GridCommandItem)
{
GridCommandItem commandItem = (GridCommandItem)e.Item;
if (commandItem.NamingContainer is GridTHead)
{
switch (e.Item.OwnerTableView.Name)
{
case "Master":
{
e.Item.OwnerTableView.NoMasterRecordsText = "No record";
break;
}
case "Detail":
{
e.Item.OwnerTableView.NoDetailRecordsText = "No record";
break;
}
}
}
}
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
}
protected void rgdEmployee_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
{
}
private void EmployeeNameGridBind()
{
string query = "select * from Employee where isactive=1";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
da.Fill(dt);
rgdEmployee.DataSource = dt;
}
private void EmployeeDetailGridBind(Telerik.Web.UI.GridCommandEventArgs e)
{
int Employeeid = Convert.ToInt32(e.Item.OwnerTableView.ParentItem.OwnerTableView.DataKeyValues[e.Item.OwnerTableView.ParentItem.DataSetIndex]["Id"]);
string query = "select * from EmployeeDetails where isactive=1 and EmployeeId = " + Employeeid + "";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
da.Fill(dt);
rgdEmployee.DataSource = dt;
if (rgdEmployee.DataSource == null)
this.rgdEmployee.MasterTableView.NoDetailRecordsText = "No record";
}
private void EmployeeDetailGridBind1(int id)
{
string query = "select * from EmployeeDetails where isactive=1 and EmployeeId = " + id + "";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
da.Fill(dt);
rgdEmployee.DataSource = dt;
if (rgdEmployee.DataSource == null)
this.rgdEmployee.MasterTableView.NoDetailRecordsText = "No record";
}
protected void rgdEmployee_UpdateCommand(object sender, GridCommandEventArgs e)
{
try
{
GridEditableItem editedItem = e.Item as GridEditableItem;
GridEditManager editMan = editedItem.EditManager;
if (e.Item.OwnerTableView.Name == "Detail")
{
isdetail = true;
int EmployeeDetailsId = Convert.ToInt32(((GridEditFormItem)e.Item).GetDataKeyValue("Id"));
int EmployeeId = Convert.ToInt32(editedItem.OwnerTableView.ParentItem.OwnerTableView.DataKeyValues[editedItem.OwnerTableView.ParentItem.DataSetIndex]["Id"]);
string SalaryStartRange = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("SalaryStartRange"))).Text);
string SalaryEndRange = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("SalaryEndRange"))).Text);
SqlCommand mySqlUpdate = new SqlCommand("update EmployeeDetails set SalaryStartRange = @ssr, SalaryEndRange = @ser where EmployeeId = @empid and Id = @id", con);
mySqlUpdate.Parameters.Add("@ssr", SqlDbType.VarChar).Value = SalaryStartRange;
mySqlUpdate.Parameters.Add("@ser", SqlDbType.VarChar).Value = SalaryEndRange;
mySqlUpdate.Parameters.Add("@empid", SqlDbType.Int).Value = EmployeeId;
mySqlUpdate.Parameters.Add("@id", SqlDbType.Int).Value = EmployeeDetailsId;
int i = mySqlUpdate.ExecuteNonQuery();
if (i > 0)
{
e.Item.OwnerTableView.ClearEditItems();
EmployeeDetailGridBind1(EmployeeId);
Label1.Text = "updated";
}
else
{
Label1.Text = "not updated";
rgdEmployee.MasterTableView.EditMode = GridEditMode.InPlace;
e.Canceled = true;
}
}
else
{
int Employeeid = Convert.ToInt16(((GridEditFormItem)e.Item).GetDataKeyValue("Id"));
SqlCommand mySqlUpdate = new SqlCommand("update Employee set EmployeeName = @an where Id = @id", con);
mySqlUpdate.Parameters.Add("@an", SqlDbType.VarChar).Value = Convert.ToString(((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text);
mySqlUpdate.Parameters.Add("@id", SqlDbType.Int).Value = Employeeid;
int i = mySqlUpdate.ExecuteNonQuery();
if (i > 0)
{
rgdEmployee.MasterTableView.ClearEditItems();
EmployeeNameGridBind();
rgdEmployee.Rebind();
Label1.Text = "des";
}
else
{
((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text = ((GridTextBoxColumnEditor)(editMan.GetColumnEditor("EmployeeName"))).Text;
Label1.Text = "des1";
rgdEmployee.MasterTableView.EditMode = GridEditMode.EditForms;
rgdEmployee.Rebind();
}
}
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
}