Friday, April 29, 2011

How to serialize .NET object to SQL Server

NOTE: PDF version of this article can be downloaded from here (How to serialize .NET object to SQL Server)

While .NET serialization to a file is pretty common, serialization to SQL database might not be popular scenario. I just got into the situation where I need to do this unpopular(?) scenario. Basically the principal of serialization and deserialization of .NET object is the same. but there sure are some additional steps to save/retrieve to/from database.

Depending on which serialization formatter one uses, the serialization data can be stored into XML or VARBINARY data field.

(1) How to Save .NET Serailization to XML data field

In order to save .NET serialziation data to XML field, not to mention, you have to define a column whose data type is XML.











For .NET serialization, let's use the following simple class. Note that [id] field won't be serialzied since [NonSerialized] attribute is specified.















Now here is the code for Xml serialization using SoapFormatter. First we serialize an object to memory stream using SoapFormatter and then convert bytes to string. Once we got SOAP XML, we save it to XML data field (SettingValue in this example) by using LINQ to SQL.








































Afte saving it to SQL, the sample code also explain how to deserialize .NET object from SQL Server. First, it search for a record by using SettingName field and then move data in SettingValue (XML data type) to memory stream. Once the xml data is in memory, first rewind pointer and then call Deserialize() method of SoapFormatter.

(2) How to save .NET serialization to VARBINARY data field

If one wants to use BinaryFormatter, the serialzation data can be stored in varbinary column. If binary data is expected to exceed more than 8000 bytes, one should specifies varbinary(max).












With the same serialization object, the following code illustrates how to (de)serialize an object to/from SQL table.


Thursday, April 28, 2011

LINQ: Insert data into PK-FK tables

As already said in previous post, inserting data into SQL tables using LINQ is not that difficult. In this post, I will explain a little different case, kind of. We have 2 simple tables - MainData and AdditionalData. AdditionalData table has a primary key [DataLinkID] whose type is uniqueidentifier. [AdditionalData] table has a foreign key relationship with [OtherDataLinkID] of [MainData] table.

CREATE TABLE [dbo].[MainData](
     [Name] [nchar](10) NOT NULL,
     [Data] [nvarchar](50) NULL,
     [OtherDataLinkID] [uniqueidentifier] NOT NULL,
     CONSTRAINT [PK_MainData] PRIMARY KEY CLUSTERED
([Name] ASC)
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_DataLinkID] ON [dbo].[MainData]
(  [OtherDataLinkID] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AdditionalData](
     [DataLinkID] [uniqueidentifier] NOT NULL,
     [OtherData] [nvarchar](50) NULL,
CONSTRAINT [PK_AdditionalData] PRIMARY KEY CLUSTERED
([DataLinkID] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AdditionalData] WITH CHECK ADD CONSTRAINT [FK_AdditionalData_MainData] FOREIGN KEY([DataLinkID])
REFERENCES [dbo].[MainData] ([OtherDataLinkID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AdditionalData] CHECK CONSTRAINT [FK_AdditionalData_MainData]
GO
Once the tables are created in SQL Server, the following Linq To SQL diagram (DataClasses1.dbml) can be created in Visual Studio by drag and drop tables from Server Explorer. Diagram shows PK [OtherDataLinkID] and FK [DataLinkID] relationship.

By the way, I used DELETE CASCADE in this example, so if MainData record is deleted, related row in AdditionalData table is automatically deleted in SQL Server. This is a convenient feature. And I used uniqueidentifier column type to ensure that the record is uniquely identified. Sure, identitiy column can be used instead but uniqueidentifier is safter than identitiy since identitiy has max limit even if we typically don't hit the max.

Once LINQ classes are generated (by saving .dbml), we can insert a row into each table by using LINQ. And here is an example of inserting data. Red part is important since it enables the relationship between main and additional table. If this red part is removed, insertion will only occur to MainData table.

public void InsertData()
      {
         Guid linkID = Guid.NewGuid();

         DataClasses1DataContext db = new DataClasses1DataContext();
         MainData main = new MainData();
         main.Name = "Alex";
         main.Data = "data1";
         main.OtherDataLinkID = linkID;

         AdditionalData additional = new AdditionalData();
         additional.DataLinkID = linkID;
         additional.OtherData = "other data";

         main.AdditionalData = additional;

         db.MainDatas.InsertOnSubmit(main);
         db.SubmitChanges();
      }