Insert .NET Objects to SQL Server

Problem:

In ASP.NET, inserting objects into SQL Server database can be troublesome. If the objects are stored in an array, you have to loop through the array first, then for each object, loop through its properties. Finally, maybe add the values into parameters for the stored procedure and call the stored procedure multiple times. And if there is any error, you may want to roll back all the records insert. That is how I would do it a year ago. But now, there is a better way.

Solutions:

The following better and elegant way of solving the problem involves converting the object array into XML format first. Then, pass this XML string into stored procedure and tell SQL Server to get the values from this XML string and create new records. Finally, roll back if there is any error.

For the completed project with the source code and database of the example below, feel free to download and try it.

First, let’s talk about the Object structure of this example. The Object layer contains the definition of the Student object which is simply the first name and the last name.

InsertObjectToDB_StudentClassDiagram

Student Class Diagram

Then I create a simple web page that display all the Student in the database and a table to add 2 more students to the database.

Student Insert Form

Student Insert Form

When the user click the button to add, the following code will run. Basically, it creates 2 Student objects by calling the class constructor. Then put the objects into an array and pass it to another function call AddStudentsToDB. The Page.IsValid condition will make sure the all (required fields) validations on the page pass first before running the code.

protected void Button_Add_Click(object sender, EventArgs e)
{
    try
    {
        if (Page.IsValid)
        {
            StudentObject student1 = new StudentObject(this.TextBox_fname1.Text, this.TextBox_lname1.Text);
            StudentObject student2 = new StudentObject(this.TextBox_fname2.Text, this.TextBox_lname2.Text);
            List<StudentObject> students = new List<StudentObject>();
            students.Add(student1);
            students.Add(student2);

            AddStudentsToDB(students);
            Response.Redirect("StudentInsert.aspx");
        }
        else
            throw new Exception("Please fill in all required fields~!");
    }
    catch (Exception err)
    {
        this.Label_Msg.Text = err.Message;
        this.Label_Msg.Visible = true;
    }
}

The AddStudentsToDB function take the array and convert the array into XML string by calling another function (ObjectToXMLWithUTF8Encoding). Once it has the XML string, it simply pass it to the stored procedure as a parameter.

protected void AddStudentsToDB(List<StudentObject> students)
{
    using (SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolDBConnectionString"].ConnectionString))
    {
        DBConnection.Open();

        SqlCommand DBCommand = new SqlCommand("Students_Insert", DBConnection);
        DBCommand.CommandType = CommandType.StoredProcedure;

        String studentsXML = ObjectToXMLWithUTF8Encoding(students);
        DBCommand.Parameters.AddWithValue("@studentsXML", studentsXML);

        DBCommand.ExecuteNonQuery();
        DBConnection.Close();
    }
}

The ObjectToXMLWithUTF8Encoding function takes the array and returns it in UTF8 XML format.

Serialized XML

UTF8 XML format

protected String ObjectToXMLWithUTF8Encoding(Object obj)
{
    MemoryStream memoryStreamtxt = new MemoryStream();
    XmlSerializer serializer = new XmlSerializer(obj.GetType());
    String XmlizedString = "";
    System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(memoryStreamtxt, new UTF8Encoding(false));
    using (writer)
    {
        serializer.Serialize(writer, obj);
        memoryStreamtxt = (MemoryStream)(writer.BaseStream);
        UTF8Encoding encoding = new UTF8Encoding();
        XmlizedString = encoding.GetString(memoryStreamtxt.ToArray());
    }
    return XmlizedString.Trim();
}

The stored procedure is defined to take one input, the XML string. This XML string contains data from the array of objects. This stored procedure tells SQL Server to get the values from this XML string and create new records.

ALTER PROCEDURE dbo.Students_Insert
@studentsXML TEXT
AS
BEGIN
  DECLARE @documentHandle INT

  BEGIN TRAN StudentsTransaction
    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @documentHandle OUTPUT, @studentsXML

    INSERT INTO Students (student_id, student_fname, student_lname)
      SELECT NEWID(), fname, lname
      FROM OPENXML (@documentHandle, 'ArrayOfStudentObject/StudentObject',2)
      WITH (fname nvarchar(50), lname nvarchar(50))

    EXEC sp_xml_removedocument @documentHandle
    IF @@ERROR <> 0
    BEGIN
      ROLLBACK TRAN StudentsTransaction
      RETURN
    END
  COMMIT TRANSACTION StudentsTransaction
END

For the completed project with the source code and database of the example above, feel free to download and try it.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>