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:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
PROCEDURE [dbo].[SelectSequence]
AS
BEGIN
SET NOCOUNT ON;
SELECT
table_name AS [TableName],
sequence_value AS [SequenceValue]
FROM
dbo.sequence
END
GO
- Create a class that implements the
ISelectCommandProvider interface. It initializes the command for entity selection. Set the Stored Procedure name and the command type:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataModifier.DataLayer;
using System.Data.Common;
namespace DataModifier.CommandProviders
{
public class SequenceNumberCmdsProvider : ISelectCommandProvider
{
public void InitSelectCommand(DbCommand command)
{
command.CommandText = "SelectSequence";
command.CommandType = System.Data.CommandType.StoredProcedure;
}
}
}
- Create a descendant window form with a
DataGridView and 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: 
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;
namespace DataModifier.Forms
{
public partial class SequenceForm : EntityEditorForm
{
public SequenceForm()
{
InitializeComponent();
RefreshButton = btnRefresh;
currContext.DataTable = new DataTable();
currContext.SelectCommandProvider = new SequenceNumberCmdsProvider();
}
protected override void InitGrid()
{
gridSequence.AutoGenerateColumns = true;
gridSequence.ReadOnly = true;
bsrcSequence.DataSource = currContext.DataTable;
gridSequence.AllowUserToAddRows = false;
gridSequence.AllowUserToDeleteRows = false;
}
}
}
- Add a call to our form creator somewhere:
private void btnShowIdentifiers_Click(object sender, EventArgs e)
{
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:
protected override void SetupVirtualIDColumn(DataColumn column)
{
}
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:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
PROCEDURE [dbo].[SelectSubject]
AS
BEGIN
SET NOCOUNT ON;
SELECT
subject_id AS [ID],
subject_name AS [Name],
subject_available AS [Available]
FROM
dbo.[subject]
END
GO
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
PROCEDURE [dbo].[UpdateSubject]
@ID bigint,
@Name varchar(50),
@Available bit,
@Original_ID bigint
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.[subject]
SET
subject_id = @ID,
subject_name = @Name,
subject_available = @Available
WHERE
subject_id = @Original_ID
END
GO
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
PROCEDURE [dbo].[InsertSubject]
@ID bigint,
@Name varchar(50),
@Available bit
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.[subject](
subject_id,
subject_name,
subject_available
)
VAlUES(
@ID,
@Name,
@Available
)
END
GO
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
PROCEDURE [dbo].[DeleteSubject]
@Original_ID bigint
AS
BEGIN
SET NOCOUNT ON;
DELETE
FROM
dbo.[subject]
WHERE
subject_id = @Original_ID
END
GO
- Create a descendant of the
SqlCommandsProviderBase class that implements the general logic for the ISIUDCommandsProvider interface. 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 the SELECT Stored Procedure, the prefix "Insert" to the entity name for the INSERT Stored Procedure, the prefix "Update" for the UPDATE one, and "Delete" for DELETE. For instance, if your entity name is Subject, you have to have these Stored Procedures: SelectSubject, InsertSubject, UpdateSubject, DeleteSubject in your database. And you have to implement a method also. This is a protected overridden method that takes DbParameterCollection as a parameter and initializes it by entity fields (you may call the base method to initialize the ID field). A sample is shown below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace DataModifier.CommandProviders
{
public class SubjectCmdsProvider : SqlCommandsProviderBase
{
public SubjectCmdsProvider()
: base ("Subject")
{
}
protected override void AddAllColumns(DbParameterCollection parameters)
{
base.AddAllColumns(parameters);
parameters.Add(new SqlParameter("Name",
SqlDbType.VarChar, 50, "Name"));
parameters.Add(new SqlParameter("Available",
SqlDbType.Bit, 0, "Available"));
}
}
- Then, you create a GUI for entity modifications. These is a form with a
DataGridView on it, and Buttons 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
EntityEditorForm properties are initialized and the overridden method InitGrid() initializes the grid (grid data binding and adding grid columns):
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;
namespace DataModifier.Forms
{
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";
public SubjectForm()
{
InitializeComponent();
SavingButton = btnSave;
RefreshButton = btnRefresh;
MessageArea = lblMessageArea;
currContext.DataTable = new DataTable(SUBJECT_TABLE);
currContext.CommandsProvider = new SubjectCmdsProvider();
}
protected override void InitGrid()
{
gridSubjects.AutoGenerateColumns = false;
gridSubjects.DataSource = currContext.DataTable;
DataGridViewTextBoxColumn colmnID = new DataGridViewTextBoxColumn();
colmnID.Name = colmnID.DataPropertyName = SUBJECT_ID;
colmnID.HeaderText = SUBJECT_ID;
colmnID.ValueType = typeof(Int64);
colmnID.ReadOnly = true;
gridSubjects.Columns.Add(colmnID);
DataGridViewTextBoxColumn colmnName = new DataGridViewTextBoxColumn();
colmnName.Name = colmnName.DataPropertyName = SUBJECT_NAME;
colmnName.HeaderText = "Subject";
colmnName.ValueType = typeof(string);
gridSubjects.Columns.Add(colmnName);
DataGridViewCheckBoxColumn colmnAvailable = new DataGridViewCheckBoxColumn();
colmnAvailable.Name = colmnAvailable.DataPropertyName = SUBJECT_AVAILABLE;
colmnAvailable.HeaderText = SUBJECT_AVAILABLE;
colmnAvailable.ValueType = typeof(Boolean);
gridSubjects.Columns.Add(colmnAvailable);
}
}
}
- Now, add a call to the form creation in your code. It is similar as for the viewer:
private void btnSubjects_Click(object sender, EventArgs e)
{
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();
LoadFormData();
SubscribeToEvents();
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):
protected override void SaveFormData()
{
BeginLongOperation();
try
{
base.SaveFormData(DepartmentTable, DepartmentCmds);
base.SaveFormData(GroupTable, GroupCmds);
base.SaveFormData(StudentTable, StudentCmds);
Changed = false;
}
catch
{
}
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):
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();
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 the LoadFormData and SaveFormData methods. 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 implements IDataTableLoader/IDataTableSaver. It consists of the data adapter logic: creation, filling, and updating of the database table.
History
- 22/09/2009 - First created.
