Sending List of Complex objects to Sql server 2005 and persisting it in the database
There are situations when you have to do multiple inserts on a single event there are multiple ways to handle this situation.The simplest and which most of the developers do and make a blunder is making individual calls to the database. One common example can be a grid which have facility of multiple Insert in such a situation on a single add button click you have to make hundreds of insert or even thousands,there are more genuine scenarios such as, suppose you are making a Silverlight CMS system and you have to save the state of the page containing various sections such as Header,MenuBar,MainContent and Footer which have all the things customizable, in such a scenario you have to save hundreds of settings on a single save button click and you again have to show the user his settings when he again loads the website. For such situations involving hundreds or thousands of db inserts and updates making individual calls over the network is not at all good,instead i will say that its the worst approach. There are other approaches,most common of them is sending a list of comma separated values.Ideally this can be said to be a workaround if you are sending just an array or a list of one or two field objects.But for complex objects its the worst solution which i have ever seen and this solution very well persist over the internet. But its has various disadvantages first one is that there is complex string manipulation associated with this approach,you will be sending something like this “5,6,9,8,45,78,5,6” and then splitting this string on the comma’s(,).Another disadvantage is that this approach is sql injection prone and above all string manipulation is never considered a good design paradigm. So here in this article i will discuss about the way which i think is the best way to persist changes to the database in such a situation. We will be making use of xml support of the sql server 2005 and xml serialization of objects in C# to achieve our goal. Example Description:-This example is an application in Silverlight 2.0 with WCF,Linq,
[Serializable]
[DataContract]
public class WebsiteContainer
{
[DataMember]
public int WebsiteContainerID { get; set; }
[DataMember]
public int UserID { get; set; }
[DataMember]
public double LogoHeight { get; set; }
[DataMember]
public double LogoWidth { get; set; }
[DataMember]
public double LogoTop { get; set; }
[DataMember]
public double LogoLeft { get; set; }
[DataMember]
public string LogoFileName { get; set; }
[DataMember]
public string BgColor { get; set; }
[DataMember]
public string Text { get; set; }
[DataMember]
public double TextFontSize { get; set; }
[DataMember]
public string TextFontFamily { get; set; }
[DataMember]
public double TextLeft { get; set; }
[DataMember]
public double TextTop { get; set; }
[DataMember]
public string TextColor { get; set; }
[DataMember]
public double TextScale { get; set; }
[DataMember]
public double Padding { get; set; }
[DataMember]
public int ContainerTypeID { get; set; }
[DataMember]
public int WebsiteID { get; set; }
[DataMember]
public double ContainerHeight { get; set; }
[DataMember]
public double ContainerWidth { get; set; }
}
Its important to note that you have to place a serializable attribute on top of the class declaration so that this class’s object can be serialized to XML while sending it to Sql server.
Now the second step is to write the C# code to serialize this object to XML to transmit over the network.
public static string SerializeObject<T>(List<T> objects)
{
StringBuilder sb = new StringBuilder();
foreach (var obj in objects)
{
try
{
XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.OmitXmlDeclaration = true;
writerSettings.Indent = true;
using (XmlWriter xmlWriter = XmlWriter.Create(sb, writerSettings))
{
XmlSerializer xs = new XmlSerializer(typeof(T));
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add(String.Empty, String.Empty);
xs.Serialize(xmlWriter, obj, ns);
}
}
catch
{
return string.Empty;
}
}
return sb.ToString();
}
Above function will take a list of type T and then iterate through each object inside the list and serialize the object and append the serialized object string to a stringbuilder ‘sb’
Here i would like to mention the real thing which you might miss is that , by default the writer settings are set to bring namespaces and xml dtd information which will result in error saying “XML parsing: line 1, character 841, text/xmldecl not at the beginning of input”
To rectify this error you have to add the writerSetting.OmitXmlDeclaration = true and also add empty namespaces using the XmlSerializerNamespaces this will give you a clean xml which can be parsed by the sql server 2005
Finally we return the string builder object after converting it to string.
After this step we have a serialized string of the whole list of objects as shown in given figure
Now the next step is to get this xml serialized object list into the stored proc.Given below is the code on how can you retrieve this list in sql server 2005
Create PROCEDURE [dbo].[SaveWebsite]
@WebsiteContainerXML XML
AS
BEGIN
DECLARE @WebsiteID INT
SET NOCOUNT ON;
SET @WebsiteID = (SELECT TOP 1 WebsiteContainerValues.ID.value('.','INT') FROM @WebsiteContainerXML.nodes('/WebsiteContainer/WebsiteID') as WebsiteContainerValues(ID) )
BEGIN TRANSACTION
DELETE FROM dbo.WebsiteContainer WHERE WebsiteID = @WebsiteID
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error deleting records', 16, 1)
RETURN
END
INSERT INTO dbo.WebsiteContainer (
UserID,
LogoHeight,
LogoWidth,
LogoTop,
LogoLeft,
LogoFileName,
BgColor,
[Text],
TextFontSize,
TextFontFamily,
TextTop,
TextLeft,
TextColor,
TextScale,
Padding,
ContainerTypeID,
WebsiteID,
ContainerHeight,
ContainerWidth,
IsRightAligned
)
SELECT
WebsiteContainerTab.WebsiteContainerCol.value('UserID[1]','int') AS UserID,
WebsiteContainerTab.WebsiteContainerCol.value('LogoHeight[1]','float') AS LogoHeight,
WebsiteContainerTab.WebsiteContainerCol.value('LogoWidth[1]','float') AS LogoWidth,
WebsiteContainerTab.WebsiteContainerCol.value('LogoTop[1]','float') AS LogoTop,
WebsiteContainerTab.WebsiteContainerCol.value('LogoLeft[1]','float') AS LogoLeft,
WebsiteContainerTab.WebsiteContainerCol.value('LogoFileName[1]','varchar(50)') AS LogoFileName,
WebsiteContainerTab.WebsiteContainerCol.value('BgColor[1]','varchar(50)') AS BgColor,
WebsiteContainerTab.WebsiteContainerCol.value('Text[1]','varchar(50)') AS TEXT,
WebsiteContainerTab.WebsiteContainerCol.value('TextFontSize[1]','float') AS TextFontSize,
WebsiteContainerTab.WebsiteContainerCol.value('TextFontFamily[1]','varchar(50)') AS TextFontFamily,
WebsiteContainerTab.WebsiteContainerCol.value('TextTop[1]','float') AS TextTop,
WebsiteContainerTab.WebsiteContainerCol.value('TextLeft[1]','float') AS TextLeft,
WebsiteContainerTab.WebsiteContainerCol.value('TextColor[1]','varchar(50)') AS TextColor,
WebsiteContainerTab.WebsiteContainerCol.value('TextScale[1]','float') AS TextScale,
WebsiteContainerTab.WebsiteContainerCol.value('Padding[1]','float') AS Padding,
WebsiteContainerTab.WebsiteContainerCol.value('ContainerTypeID[1]','int') AS ContainerTypeID,
WebsiteContainerTab.WebsiteContainerCol.value('WebsiteID[1]','int') AS WebsiteID,
WebsiteContainerTab.WebsiteContainerCol.value('ContainerHeight[1]','float') AS ContainerHeight,
WebsiteContainerTab.WebsiteContainerCol.value('ContainerWidth[1]','float') AS ContainerWidth,
WebsiteContainerTab.WebsiteContainerCol.value('IsRightAligned[1]','bit') AS IsRightAligned
FROM @WebsiteContainerXML.nodes('//WebsiteContainer') AS WebsiteContainerTab(WebsiteContainerCol)
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error Inserting in WebContainers', 16, 1)
RETURN
END
COMMIT
END
You can refer the syntax to retrieve and iterate through the serialized list and then inserting into the database.
Important to make a note that you don’t have to manually iterate through to get the count and then again run some loop inside the stored proc for storing the above syntax will automatically iterate through all the nodes and insert each record.
Happy Programming!!!!!!!!!!!!!!!!!!
Part 1:- Some useful scripts which come handy while working with sql server 2005
-
Getting all the primary key and foreign keys in a given database
select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, CASE WHEN XTYPE ='F' THEN 'FORIEGN KEY' ELSE 'PRIMARY KEY' END KEY_TYPE , NAME KEY_NAME from sysobjects where Xtype in ('F' , 'pK') ORDER BY XTYPE DESCThis script can be used to get all the primary key and foreign key relationships within a given database.It sometimes can be helpful on a project with a complex database having hundreds and thousands of relationships and in such a database if you have to perform some major changes so you can have a quick look and you can know which all tables and relationship will be effected by these changes.
2. Delete All the Stored Procs for a particular database
Alter Procedure dbo.DeleteAllProcedures
As
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
Go
Grant Execute On dbo.DeleteAllProcedures To Public
Go
This can only be handy when you want to delete all the user defined stored procs for a particular database
3.Change the default owner of a stored proc
USE YourDataBaseName
GO
DECLARE
@OldOwner sysname,
@NewOwner sysname
SET @OldOwner = 'dbo'
SET @NewOwner = 'dev'
DECLARE CURS CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'p'
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE 'dt%' FOR READ ONLY
DECLARE @ProcName sysname
OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= 'Microsoft SQL Server 2005'
BEGIN
EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
END
ELSE
EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')
FETCH CURS INTO @ProcName
END
CLOSE CURS
DEALLOCATE CURS
4.Finding any stored proc containing the text of your choice
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%yoursearchtext%'
AND ROUTINE_TYPE='PROCEDURE'
5.Reducing the size of your transaction log
dbcc shrinkfile (mydatabasename_log, 10);
This command will reduce the size of your database transaction log to 10MB. But if you are on production then you should not use this method to shrink your database, you should avoid shrinking the database without finding the probable reason why is it growing so fast.
Error 1045 when using MySQL or SQLyog
Yesterday i was playing with MySql and SqlYog.I m not much familier with both but as Mysql was needed on one of the project , so i was trying to learn it in a nutshell. Initially there were little setbacks and frustrations but later everything seemed easy.I find things a little different as i m from the Sql Server world.
As i keep on learning i will keep on sharing with you.So the first one is when i installed MySql and configured everything well but when i installed SqlYog and tried to connect them both i got this bad error saying “Error 1045.Access is denied for user abcdefgh@localhost………..” I was clueless of what went wrong after trying for sometime and googling to find nothing i tried some random things and wooola!! one of it did work just simple trick. Actually at every other place i found a solution where there was no password set for the users, so most of the posts told how to change the old password or revoke or grant permissions to the existing users. But i knew that mine was simple as i did everything right. So what happened was that when i installed mysql it asked me for username and password and i politely provided it with one of my favorite username and password. But after trying many times with sqlyog the things were still not looking good. So the simple thing here is while connecting with mysql from sqlyog it expects that you provide username as “root” not the one which you gave while installation and the password remains the same which you gave while installing mysql. 
Although a very small tip but almost most newbie's may face this problem. Never loose hope there is always a workaround for everything. Happy Programming !!!!!!!!!!!!!!!!!!