Home Page
Blog

Easily store /retrieve objects in a database using C# / .NET

Google
Download source code

Introduction

The aim of this project is to easily allow any object to be saved to a database. Key points are:

Design Strategy

The main motivation for this class library was to create something that could allow any object to be stored in a database with as little effort as possible (so no creating new tables for instance for each separate object / type that needed to be stored). I also wanted to store the data as text, so that it could easily be inspected and queried. The assembly contains the following types:

Single Entry Example Usage

The easiest way to use this class is on simple types such as ints and strings. For example, the following code writes an int with the name Integer1 to the database:

using (IObjectTable obTab = new SqlObjectTable(connectionString, tableName))
{
   int val = 3;
   obTab.SetValue("Integer1", val);
}

...and this code reads it back in again:

using (IObjectTable obTab = new SqlObjectTable(connectionString, tableName))
{
   int? val = (int?)obTab.GetValue("Integer1");
}

Note that the reason for using int? rather than int is that if the object is not found, GetValue returns null.

Any object can be written with these methods.

Regarding the format of the data stored in the database, the data is always stored in a textual format. However, if the object has a static Parse() method, then the value of object.ToString() is stored. If a Parse() method does not exist, the the object is serialized and deserialized using the XML serializer. Note that the XML serializer will only serialize public fields and properties.

Calling SetValue on an item with the same name will overwrite the old value (if the type is different, the type will be changed). Also, the database entry can be deleted with:

using (IObjectTable obTab = new SqlObjectTable(connectionString, tableName))
{
   obTab.DeleteValue("Integer1");
}

After the code above has executed, calling GetValue("Integer1") will return null.

Multiple Entry Example Usage

If an array of items is required (e.g. a list of names / telephone numbers), then the DatabaseObjectList must be used. Rather than the get/set interface, the DatabaseObjectList uses CRUD (create, read, update, and delete) methods, defined as:

public void AddRecord(T rec)
public T[]  GetRecords()
public void UpdateRecord(T rec)
public void DeleteRecord(T rec)
public void DeleteRecord(int primaryKey)

The following code will write two objects to the database with the key name ObjectList1:

IObjectTable obTab = new SqlObjectTable(connectionString, tableName);
using (DatabaseObjectList<ArbitraryObject>obList = new DatabaseObjectList<ArbitraryObject>(obTab, "ObjectList1"))
{
   obList.AddRecord(arbOb1);
   obList.AddRecord(arbOb2);
}

The following code will read the records back from the database:

IObjectTable obTab = new SqlObjectTable(connectionString, tableName);
using (DatabaseObjectList<ArbitraryObject>obList = new DatabaseObjectList<ArbitraryObject>(obTab, "ObjectList1"))
{
   ArbitraryObject[] arr = obList.GetRecords();
}

Unlike the single entry methods which can save and retrieve any type, objects used for the multiple entry methods must be derived from DatabaseObject. This is because each value must have a unique ID associated with it in order to be able to update or delete specific entries.

Using the DatabaseObjectList with the ASP.NET GridView

One obvious use of the multiple entry methods is for the ASP.NET GridView, using an ObjectDataSource to hook up the GridView to the database. Unfortunately, the designer for the ObjectDataSource doesn't support generic types, so we have to edit the ASPX file, and declare the DataObjectTypeName as follows:

<asp:ObjectDataSource ID="ArbyListObjectDataSource"
    TypeName="DatabaseObjects.DatabaseObjectList`1[[GenericDBWebApp.ArbitraryObject, GenericDBWebApp]]"
    runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetRecords"
    DataObjectTypeName="GenericDBWebApp.ArbitraryObject" DeleteMethod="DeleteRecord" 
    InsertMethod="AddRecord" UpdateMethod="UpdateRecord"
    OnObjectCreating="ArbyListObjectDataCreating">
    <DeleteParameters>
        <asp:Parameter Name="primaryKey" Type="Int32" />
    </DeleteParameters>
</asp:ObjectDataSource>

If you are not familiar with the type definition syntax used in DataObjectTypeName, you can find a description of it here: MSDN: assemblyqualifiedname

We can then use this for the GridView (either by editting the code or using the designer). The code for this will look something like:

<asp:GridView ID="ArbyListGridView" runat="server" AutoGenerateColumns="False" 
              DataSourceID="ArbyListObjectDataSource" DataKeyNames="PrimaryKey">
    <Columns>
        <asp:BoundField DataField="PrimaryKey" HeaderText="Primary Key"
                        SortExpression="PrimaryKey" Visible="False" />
        <asp:BoundField DataField="ArbString" HeaderText="Arb String"
                        SortExpression="ArbString" />
        <asp:BoundField DataField="ArbInteger" HeaderText="Arb Int"
                        SortExpression="ArbInteger" />
        <asp:BoundField DataField="ArbDouble" HeaderText="Arb Double"
                        SortExpression="ArbDouble" />
        <asp:CommandField ShowEditButton="True" />
        <asp:CommandField ShowDeleteButton="True" />
    </Columns>
</asp:GridView>

SQL script to create object table

The following script will create the table in the database. You can create as many tables with this as you like, or simply store all of the objects in the one table.

USE [ObjectDatabase]
GO
/****** Object:  Table [dbo].[ObjectTable]
   Script Date: 05/17/2008 18:57:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ObjectTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Value] [varchar](max) NOT NULL,
    [Type] [varchar](256) NOT NULL,
 CONSTRAINT [PK_ObjectTable] 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
SET ANSI_PADDING OFF

/****** Object:  Index [IX_Name] 
   Script Date: 05/17/2008 18:58:36 ******/
CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[ObjectTable] 
(
    [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Revision History