Database table editor framework
4.43/5 (4 votes)
This article consists of a data layer and forms for database tables editing.
Introduction
This article is about retrieving data from the database to an application and representing it to the user for review and modifications, and putting the modified data back from the application to the database again. The general routine of data dealing consists of the following parts (this division is relative):
- Stored Procedures. Server-side logic. This is a standard set of Stored Procedures that select data from the database as a table, make insertions for new rows, update changes of corrected fields, and delete rows from the table. If the entity is read only, it is enough to have a Stored Procedure for the row selection only.
- Entity editor form. Graphic user interface. A form with a grid that allows to show data in a table form and to make modifications to the data. The form must support a 'dirty' flag, refresh and save buttons, exception handling, and informing the user about changes. There must be only one form with data to edit at any moment of time to avoid collisions. Therefore, a singleton form must be implemented for each entity.
- Data layer. Objects that support mounting of connection, entities modifications management, and storage of data in memory. This is an intermediate layer between the physical data storage and the Windows Forms presentation.
Each developer does it in his/her own manner. And a sufficiently large project consists of all of them. It must be simple to understand and flexible to be applied in different situations. Here is my version of a general logic to allow users to deal with database entities.
Using the code
Simple viewer
You can use this stuff just to view what a database table consists of at a certain time. To add a simple entity viewer to your application, do the following steps:
- Create a Stored Procedure for selection:
- Create a class that implements the
ISelectCommandProviderinterface. It initializes the command for entity selection. Set the Stored Procedure name and the command type: - Create a descendant window form with a
DataGridViewand a Button on it. The grid serves for data visualization, and the button serves for data updating from a data source. Put a status line at the bottom of the form. You will have something like below: - Add a call to our form creator somewhere:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Major League
-- Create date: 14/09/2009
-- Description: gets sequence number for table
-- =============================================
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE
id = object_id(N'[dbo].[SelectSequence]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[SelectSequence]
RETURN
GO
CREATE
--ALTER
PROCEDURE [dbo].[SelectSequence]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--select sequence
SELECT
table_name AS [TableName],
sequence_value AS [SequenceValue]
FROM
dbo.sequence
END
GO
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataModifier.DataLayer; //ISelectCommandProvider
using System.Data.Common; //DbCommand
namespace DataModifier.CommandProviders
{
/// <summary>
/// Commands provider for Sequence number
/// </summary>
public class SequenceNumberCmdsProvider : ISelectCommandProvider
{
/// <summary>
/// Inits select command
/// </summary>
/// <param name="command">command to init</param>
public void InitSelectCommand(DbCommand command)
{
command.CommandText = "SelectSequence";
command.CommandType = System.Data.CommandType.StoredProcedure;
}
}
}
Then, choose 'View code' and add a few code lines:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DataModifier.CommandProviders; //SequenceNumberCmdsProvider
namespace DataModifier.Forms
{
/// <summary>
/// Form that shows last values of sequence numbers
/// </summary>
public partial class SequenceForm : EntityEditorForm
{
/// <summary>
/// Default .ctor
/// </summary>
public SequenceForm()
{
InitializeComponent();
//set refresh button
RefreshButton = btnRefresh;
// initialze data objects
currContext.DataTable = new DataTable();
currContext.SelectCommandProvider = new SequenceNumberCmdsProvider();
}
/// <summary>
/// Inits grid
/// </summary>
protected override void InitGrid()
{
gridSequence.AutoGenerateColumns = true;
gridSequence.ReadOnly = true;
bsrcSequence.DataSource = currContext.DataTable;
gridSequence.AllowUserToAddRows = false; //hide new row line
gridSequence.AllowUserToDeleteRows = false; //disable row deleting
}
}
}
/// <summary>
/// Opens form with current sequence numbers
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnShowIdentifiers_Click(object sender, EventArgs e)
{
//be only one
SequenceForm.OpenForm(typeof(SequenceForm), this);
}
Actually that is all. Now compile the code, push the button, and enjoy.
One more thing. We have to disable column ID initialization because the 'sequence' entity has not got one. We can do this by overriding the initialization method:
/// <summary>
/// Setups virtual id column.
/// </summary>
/// <param name="column">column to make id column</param>
/// <remarks>calls from LoadDataSchema()</remarks>
protected override void SetupVirtualIDColumn(DataColumn column)
{
//disable virtual id column initialization
//because sequence table does not contain it.
}
It is needed to be done only if your entity does not have an integer column ID.
Entity editing form
Creating an entity editing form is not so hard. The steps are similar to that of the viewer creation:
- Here are the four Stored Procedures for Select, Insert, Update, and Delete:
- Create a descendant of the
SqlCommandsProviderBaseclass that implements the general logic for theISIUDCommandsProviderinterface. In the descendant class, you have to implement the default constructor that calls the constructor of the base class with the name of the entity as a parameter. The base constructor concatenates the prefix "Select" to the entity name for theSELECTStored Procedure, the prefix "Insert" to the entity name for theINSERTStored Procedure, the prefix "Update" for theUPDATEone, and "Delete" forDELETE. For instance, if your entity name is Subject, you have to have these Stored Procedures:SelectSubject,InsertSubject,UpdateSubject,DeleteSubjectin your database. And you have to implement a method also. This is a protected overridden method that takesDbParameterCollectionas a parameter and initializes it by entity fields (you may call the base method to initialize the ID field). A sample is shown below: - Then, you create a GUI for entity modifications. These is a form with a
DataGridViewon it, andButtons to save the changes made and to refresh fresh data from the database. It is similar to the previous form, except for the presence of the Save button:
- Next, we need to add code to the created form. This is a constructor where
EntityEditorFormproperties are initialized and the overridden methodInitGrid()initializes the grid (grid data binding and adding grid columns): - Now, add a call to the form creation in your code. It is similar as for the viewer:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Major League
-- Create date: 19/09/2009
-- Description: gets subjects
-- =============================================
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE
id = object_id(N'[dbo].[SelectSubject]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[SelectSubject]
RETURN
GO
CREATE
--ALTER
PROCEDURE [dbo].[SelectSubject]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--select subjects
SELECT
subject_id AS [ID],
subject_name AS [Name],
subject_available AS [Available]
FROM
dbo.[subject]
END
GO
-- =============================================
-- Author: Major League
-- Create date: 19/09/2009
-- Description: updates subjects
-- =============================================
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[UpdateSubject]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[UpdateSubject]
RETURN
GO
CREATE
--ALTER
PROCEDURE [dbo].[UpdateSubject]
@ID bigint,
@Name varchar(50),
@Available bit,
@Original_ID bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE dbo.[subject]
SET
subject_id = @ID,
subject_name = @Name,
subject_available = @Available
WHERE
subject_id = @Original_ID
END
GO
-- =============================================
-- Author: Major League
-- Create date: 19/09/2009
-- Description: inserts subjects
-- =============================================
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[InsertSubject]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[InsertSubject]
RETURN
GO
CREATE
--ALTER
PROCEDURE [dbo].[InsertSubject]
@ID bigint,
@Name varchar(50),
@Available bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.[subject](
subject_id,
subject_name,
subject_available
)
VAlUES(
@ID,
@Name,
@Available
)
END
GO
-- =============================================
-- Author: Major League
-- Create date: 19/09/2009
-- Description: deletes subjects
-- =============================================
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[DeleteSubject]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[DeleteSubject]
RETURN
GO
CREATE
--ALTER
PROCEDURE [dbo].[DeleteSubject]
@Original_ID bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE
FROM
dbo.[subject]
WHERE
subject_id = @Original_ID
END
GO
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; //SqlDbTypes
using System.Data.Common; //DbParameterCollection
using System.Data.SqlClient; //SqlParameter
namespace DataModifier.CommandProviders
{
/// <summary>
/// Provides commands to select, update, insert and delete
/// Subject entities
/// </summary>
public class SubjectCmdsProvider : SqlCommandsProviderBase
{
/// <summary>
/// .ctor
/// </summary>
public SubjectCmdsProvider()
: base ("Subject")
{
}
/// <summary>
/// Sets command parameters
/// </summary>
/// <param name="parameters">command parameters</param>
protected override void AddAllColumns(DbParameterCollection parameters)
{
base.AddAllColumns(parameters); // add ID column
/*
@Name varchar(50),
@Available bit,
*/
parameters.Add(new SqlParameter("Name",
SqlDbType.VarChar, 50, "Name"));
parameters.Add(new SqlParameter("Available",
SqlDbType.Bit, 0, "Available"));
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DataModifier.CommandProviders; //SubjectCmdsProvider
namespace DataModifier.Forms
{
/// <summary>
/// Form to edit subjects
/// </summary>
public partial class SubjectForm : EntityEditorForm
{
private const string SUBJECT_TABLE = "Subject";
private const string SUBJECT_ID = "ID";
private const string SUBJECT_NAME = "Name";
private const string SUBJECT_AVAILABLE = "Available";
/// <summary>
/// Default .ctor
/// </summary>
public SubjectForm()
{
InitializeComponent();
//set entity editor buttons
SavingButton = btnSave;
RefreshButton = btnRefresh;
MessageArea = lblMessageArea;
//set from data objects
currContext.DataTable = new DataTable(SUBJECT_TABLE);
currContext.CommandsProvider = new SubjectCmdsProvider();
}
/// <summary>
/// Init grid to edit subjects
/// </summary>
protected override void InitGrid()
{
gridSubjects.AutoGenerateColumns = false;
//data binding
gridSubjects.DataSource = currContext.DataTable;
//add columns to grid
DataGridViewTextBoxColumn colmnID = new DataGridViewTextBoxColumn();
colmnID.Name = colmnID.DataPropertyName = SUBJECT_ID;
colmnID.HeaderText = SUBJECT_ID; //[TEXT]
colmnID.ValueType = typeof(Int64);
colmnID.ReadOnly = true;
gridSubjects.Columns.Add(colmnID);
DataGridViewTextBoxColumn colmnName = new DataGridViewTextBoxColumn();
colmnName.Name = colmnName.DataPropertyName = SUBJECT_NAME;
colmnName.HeaderText = "Subject"; //[TEXT]
colmnName.ValueType = typeof(string);
gridSubjects.Columns.Add(colmnName);
DataGridViewCheckBoxColumn colmnAvailable = new DataGridViewCheckBoxColumn();
colmnAvailable.Name = colmnAvailable.DataPropertyName = SUBJECT_AVAILABLE;
colmnAvailable.HeaderText = SUBJECT_AVAILABLE; //[TEXT]
colmnAvailable.ValueType = typeof(Boolean);
gridSubjects.Columns.Add(colmnAvailable);
}
}
}
/// <summary>
/// Opens form to edit subjects
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSubjects_Click(object sender, EventArgs e)
{
//be only one
SubjectForm.OpenForm(typeof(SubjectForm), this);
}
Editing two or more entities in the same form
When you intend to edit two or more entities in the same form, you have to come down on one step. In addition to all the stuff that you would do for the entity editing form, you have to implement each step of the standard loading routine and the SaveFormData() method. To illustrate what I mean, I cite the body of the ExecuteLoadingConsecution() method that is called each time you open the entity editor form:
LoadFormDataSchema();
InitGrid(); //in general data displayed with aid of grid
LoadFormData();
SubscribeToEvents();
//do this at the end to avoid excessive events handling
Each method of this consecution does its own part of the loading routine. If you want to change something, you may just override the respective method.
I have to describe one more feature. All the methods SaveFormData, LoadFormData, and LoadFormDataSchema themselves handle the exceptions that occur inside them except if they are inside the calls of the paired methods BeginLongOperation and EndLongOperation, in which case, the exception goes on. I did it to allow these methods to display an error message and then to terminate the loading process. This feature may be utilized when it is needed to save two or more entities (example for three grids on the form, see the source code):
/// <summary>
/// Saves form data
/// </summary>
protected override void SaveFormData()
{
// We want to terminate saving if any exception occurs. This is
// possible with BeginLongOperation()/EndLongOperation logic.
BeginLongOperation();
try
{
base.SaveFormData(DepartmentTable, DepartmentCmds);
base.SaveFormData(GroupTable, GroupCmds);
base.SaveFormData(StudentTable, StudentCmds);
Changed = false;
}
catch //we want just to catch exception. All messages are displayed already
{
}
finally
{
EndLongOperation();
}
}
If saving of any table fails, the saving process is terminated.
Be careful because LoadFormData(), instead of SaveFormData(), is called alone as well as from the BeginLongOperation/EndLongOperation pair. Therefore, it must transmit the exception if it occurs. I will illustrate what I meant by a method sample (from the same example of the three grids on the form):
/// <summary>
/// Loads form data
/// </summary>
protected override void LoadFormData()
{
BeginLongOperation();
try
{
base.LoadFormData(DepartmentTable, DepartmentCmds);
base.LoadFormData(GroupTable, GroupCmds);
base.LoadFormData(StudentTable, StudentCmds);
Changed = false;
EndLongOperation();
}
catch (Exception ex)
{
EndLongOperation(); //terminate loading long operation first
TransmitException(ex);
}
}
To get some more information about how to use this stuff to create forms with two or more grids on it, see the source code.
Key framework elements
This paragraph consists of the elements description that allows to create the entity editing form easy.
AppBaseForm. Base form for all application forms that consists of the Windows Form singleton logic. When you edit an entity, it is important to have only one form for each entity, to avoid collisions. This logic implements this principle.EntityEditorForm. Base form for entity editing forms. This form consists of the general logic to execute the loading process, to process saving and refreshing data. It processes form closing, correctly prompting the user to save if changes are made. It provides form data context and the implementation of theLoadFormDataandSaveFormDatamethods. And a lot of other things.SqlCommandsProviderBase. Base class that implements the general logic for all commands providers based on Stored Procedures.SqlDataSource. SQL data source. It implementsIDataTableLoader/IDataTableSaver. It consists of the data adapter logic: creation, filling, and updating of the database table.
History
- 22/09/2009 - First created.