Click here to Skip to main content
15,868,141 members
Articles / Database Development / SQL Server

Database table editor framework

Rate me:
Please Sign up or sign in to vote.
4.43/5 (4 votes)
23 Sep 2009CPOL6 min read 28.1K   696   20   2
This article consists of a data layer and forms for database tables editing.

Image 1

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:

  1. Create a Stored Procedure for selection:
  2. SQL
    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
  3. Create a class that implements the ISelectCommandProvider interface. It initializes the command for entity selection. Set the Stored Procedure name and the command type:
  4. C#
    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;
            }
        }
    }
  5. 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:
  6. Form with Grid and Refesh button on it

    Then, choose 'View code' and add a few code lines:

    C#
    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 
            }
        }
    }
  7. Add a call to our form creator somewhere:
  8. C#
    /// <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:

C#
/// <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:

  1. Here are the four Stored Procedures for Select, Insert, Update, and Delete:
  2. SQL
    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
  3. 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:
  4. C#
    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"));
            }
    
        }
  5. 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:

    Form with DataGridView on it and 'Save' button and 'Refresh' button

  6. 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):
  7. C#
    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);
            }
        }
    }
  8. Now, add a call to the form creation in your code. It is similar as for the viewer:
  9. C#
    /// <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:

C#
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):

C#
/// <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):

C#
/// <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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer Freelancer
Ukraine Ukraine
MS SQL Server Database Developer with 7+ years experience

Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.

Feel free to contact me for rates and details.

Comments and Discussions

 
QuestionSame code for two articles? Pin
Paul Selormey23-Sep-09 15:27
Paul Selormey23-Sep-09 15:27 
AnswerRe: Same code for two articles? Pin
db_developer29-Sep-09 13:28
db_developer29-Sep-09 13:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.