Introduction
A lot of the time in our daily coding we need to implement bulk insert update data from UI or Business Layers where most of the time we use looping
to insert data, but bulk insert is a very useful trick to use. Another useful thing which we need most of the time is serialization which I implement here for generating XML.
This a simple application where I have used collection of a class and created XML from it using serialization which we can
do most of the time using datasets, like this:
StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
XmlTextWriter xw1 = new XmlTextWriter(sw);Dataset ds=new Dataset();
ds.WriteXml(xw);
strXML = sw.ToString();
But serialization and desalinization from a collection is the most important part which we need for some cases like this.
This is the SP for Bulk Insert into a table from XML, which takes an XML string as parameter and inserts rows into table using OpenXML.
Following is an example where ArrayOfUser
is the root node which is generated from
a collection of classes or an IList
object through
the SerializeToXML<T>(List<T> source)
method.
-----------------------------------------------------------------------------------------
Sample Xml generates from collectin of User.
---------------------------------------------------------
<ArrayOfUser>
<User>
<Name>Test 1</Name>
<Age>10</Age>
<DOB>2012-07-20T12:35:17.203125+05:30</DOB>
</User>
<User>
<Name>Test 2</Name>
<Age>20</Age>
<DOB>2012-07-20T12:35:17.203125+05:30</DOB>
</User>
<User>
<Name>Test 3</Name>
<Age>30</Age>
<DOB>2012-07-20T12:35:17.203125+05:30</DOB>
</User>
</ArrayOfUser>
Sample Stored Procedure:
CREATE Procedure [dbo].[sp_Bulk_Insert_User]
@strXML NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @XMLDocPointer INT
begin
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO [User]
([Name]
,[Age]
,[DOB]
)
select
[Name]
,[Age]
,[DOB]
from
OPENXML(@XMLDocPointer,'/ArrayOfUser/User',2)with(
[Name] VARCHAR(50)
,[Age] INT
,[DOB] DATETIME
)
IF @@Error<> 0
Begin
Raiserror('Not saved. Please try again',16,1)
Rollback Transaction
End
Else
COMMIT transaction
EXEC sp_xml_removedocument @XMLDocPointer
end
END
GO
"SerializeToXML
" is a generic method that takes a list objects of any class and parses it into XML and returns
the XML as a string in the Utility
class.
public static string SerializeToXML<T>(List<T> source)
{
XmlSerializer serializer = new XmlSerializer(source.GetType());
StringWriter _StringWriter = new StringWriter();
XmlTextWriter _XmlTextWriter = new XmlTextWriter(_StringWriter);
var settings = new XmlWriterSettings();
settings.Indent = true;
settings.OmitXmlDeclaration = true;
XmlWriter _XmlWriter = XmlWriter.Create(_StringWriter, settings);
var emptyNs = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
serializer.Serialize(_XmlWriter, source, emptyNs);
return _StringWriter.ToString();
}
From the Button Click event we create a list for User
class and sends
the list to the Business Layer class SaveUserData(IList<User> ItemListUser)
.
public static string SerializeToXML<T>(List<T> source)
{
XmlSerializer serializer = new XmlSerializer(source.GetType());
StringWriter _StringWriter = new StringWriter();
XmlTextWriter _XmlTextWriter = new XmlTextWriter(_StringWriter);
var settings = new XmlWriterSettings();
settings.Indent = true;
settings.OmitXmlDeclaration = true;
XmlWriter _XmlWriter = XmlWriter.Create(_StringWriter, settings);
var emptyNs = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
serializer.Serialize(_XmlWriter, source, emptyNs);
return _StringWriter.ToString();
}
From the Button Click event of UI we create a list for User
class
and sends the list to the Business Layer class SaveUserData(IList<User> ItemListUser)
.
protected void btnData_Add_Click(object sender, EventArgs e)
{
try
{
List<User> Userlist;
Userlist = new List<User>();
for (int i = 0; i < int.Parse(txtcnt.Text); i++)
{
User Userobj = new User();
Userobj.Name = "Test " + (i + 1).ToString();
Userobj.DOB = DateTime.Now;
Userobj.Age = i + 1;
Userlist.Add(Userobj);
}
BussinessUtility _BussinessUtility = new BussinessUtility();
if (_BussinessUtility.SaveUserData(Userlist))
lblMessage.Text = "Saved";
else
lblMessage.Text = "Error";
}
catch
{
}
}
This is the method of the Business Layer which calls the method of serialization from
the Utility
class and sends to the data access class.
public bool SaveUserData(IList<User> ItemListUser)
{
DataAccessUtility DA = new DataAccessUtility();
bool j = false;
try
{
j = DA.SavaBulkData(WebUtility.SerializeToXML(ItemListUser));
}
catch
{
}
return j;
}
The Save
method in the DataAccessUtility
class calls the Stored Procedure
sp_Bulk_Insert_User
and takes an XML string as parameter.
public bool SavaBulkData(string strXML)
{
SqlConnection _conn = null;
SqlCommand _cmd = null;
SqlTransaction _tran = null;
bool _retVal = false;
try
{
DBAccess.DBConnection(ref _conn);
_cmd = new SqlCommand("sp_Bulk_Insert_User", _conn);
_cmd.CommandTimeout = 0;
_cmd.CommandType = CommandType.StoredProcedure;
_tran = DBAccess.OpenTransaction(_conn, _tran, _cmd);
_cmd.Parameters.Add(new SqlParameter("@strXML", SqlDbType.NVarChar));
_cmd.Parameters["@strXML"].Value = strXML;
int rowsAffected = _cmd.ExecuteNonQuery();
DBAccess.EndTransaction(ref _tran, false);
DBAccess.CloseConnection(ref _conn, ref _cmd);
_retVal = true;
}
catch (SqlException ex)
{
_retVal = false;
DBAccess.EndTransaction(ref _tran, true);
DBAccess.CloseConnection(ref _conn, ref _cmd);
throw ex;
}
catch (Exception ex)
{
_retVal = false;
DBAccess.EndTransaction(ref _tran, true);
DBAccess.CloseConnection(ref _conn, ref _cmd);
throw ex;
}
return _retVal;
}
Conclusion
In this article I have tried to cover a few topics like serialization to XML,
bulk insertion, etc.