An Experimental Performance Comparison on the Methods to Pass Structured Data to SQL Server Stored Procedures from ADO.NET

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Windows.Forms;

namespace SQLSPPerformanceComparison
{
    public partial class frmMain : Form
    {
        private DataTable DataToUpload;

        public frmMain()
        {
            InitializeComponent();

            DataToUpload = DataSourceToUpload.GetInstance();
        }

        private DataTable TestWithTextType()
        {
            char RowSeparator = System.Convert.ToChar(0);
            char ItemSeparator = System.Convert.ToChar(1);

            StringBuilder SB = new StringBuilder();
            foreach (DataRow row in DataToUpload.Rows)
            {
                int ID = System.Convert.ToInt32(row["ID"]);
                string Name = System.Convert.ToString(row["Name"]);
                int Score = System.Convert.ToInt32(row["Score"]);

                SB.Append(ID.ToString());
                SB.Append(ItemSeparator);
                SB.Append(Name);
                SB.Append(ItemSeparator);
                SB.Append(Score.ToString());
                SB.Append(RowSeparator);
            }

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "UploadByTextType";
            cmd.Parameters.Add("@DataInTextType", SqlDbType.Text).Value = SB.ToString();
            cmd.CommandType = CommandType.StoredProcedure;
            DataTable aTable = SQLServerUtility.GetADataTable(cmd);

            return aTable;
        }

        private void btnGoText_Click(object sender, EventArgs e)
        {
            DateTime StartTime = System.DateTime.Now;
            DataTable aTable = TestWithTextType();
            DateTime EndTime = System.DateTime.Now;
            System.TimeSpan TimeSpent = EndTime - StartTime;

            lblTimeSpentText.Text = "Total " + aTable.Rows.Count.ToString() + " Rows Sent, Time Spent = " + TimeSpent.TotalSeconds.ToString() + " Seconds.";
            dgText.DataSource = aTable;
        }

        private DataTable TestWithXMLType()
        {
            System.Text.UTF8Encoding AEncoder = new UTF8Encoding();
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            System.Xml.XmlTextWriter tw = new System.Xml.XmlTextWriter(ms, new System.Text.ASCIIEncoding());

            tw.WriteStartDocument();
            tw.WriteStartElement("DATAS");

            foreach (DataRow row in DataToUpload.Rows)
            {
                int ID = System.Convert.ToInt32(row["ID"]);
                string Name = System.Convert.ToString(row["Name"]);
                int Score = System.Convert.ToInt32(row["Score"]);

                tw.WriteStartElement("DATA");
                tw.WriteElementString("ID", ID.ToString());
                tw.WriteElementString("Name", Name);
                tw.WriteElementString("Score", Score.ToString());
                tw.WriteEndElement();
            }

            tw.WriteFullEndElement();
            tw.WriteEndDocument();
            tw.Flush();
            tw.Close();

            string strXML = AEncoder.GetString(ms.ToArray());

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "UploadByXMLType";
            cmd.Parameters.Add("@DataInXMLType", SqlDbType.Text).Value = strXML;
            cmd.CommandType = CommandType.StoredProcedure;
            DataTable aTable = SQLServerUtility.GetADataTable(cmd);


            return aTable;
        }

        private void btnGoXML_Click(object sender, EventArgs e)
        {
            DateTime StartTime = System.DateTime.Now;
            DataTable aTable = TestWithXMLType();
            DateTime EndTime = System.DateTime.Now;
            System.TimeSpan TimeSpent = EndTime - StartTime;

            lblTimeSpentXML.Text = "Total " + aTable.Rows.Count.ToString() + " Rows Sent, Time Spent = " + TimeSpent.TotalSeconds.ToString() + " Seconds.";
            dgXML.DataSource = aTable;
        }

        private DataTable TestWithSQLTableType()
        {
            DataTable TableToUpload = new DataTable();
            TableToUpload.Columns.Add("ID", System.Type.GetType("System.Int32"));
            TableToUpload.Columns.Add("Name", System.Type.GetType("System.String"));
            TableToUpload.Columns.Add("Score", System.Type.GetType("System.Int32"));

            foreach (DataRow row in DataToUpload.Rows)
            {
                int ID = System.Convert.ToInt32(row["ID"]);
                string Name = System.Convert.ToString(row["Name"]);
                int Score = System.Convert.ToInt32(row["Score"]);

                DataRow aNewRow = TableToUpload.NewRow();
                aNewRow["ID"] = ID;
                aNewRow["Name"] = Name;
                aNewRow["Score"] = Score;

                TableToUpload.Rows.Add(aNewRow);
            }

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "UploadByTableType";
            cmd.Parameters.AddWithValue("@DataInTableType", TableToUpload);
            cmd.CommandType = CommandType.StoredProcedure;
            DataTable aTable = SQLServerUtility.GetADataTable(cmd);

            return aTable;
        }

        private void btnGoTable_Click(object sender, EventArgs e)
        {
            DateTime StartTime = System.DateTime.Now;

            DataTable aTable = TestWithSQLTableType();
            DateTime EndTime = System.DateTime.Now;
            System.TimeSpan TimeSpent = EndTime - StartTime;

            lblTimeSpentTable.Text = "Total " + aTable.Rows.Count.ToString() + " Rows Sent, Time Spent = " + TimeSpent.TotalSeconds.ToString() + " Seconds.";
            dgTable.DataSource = aTable;
        }

        private void btnClearText_Click(object sender, EventArgs e)
        {
            dgText.DataSource = null;
            lblTimeSpentText.Text = "";
        }

        private void btnClearXML_Click(object sender, EventArgs e)
        {
            dgXML.DataSource = null;
            lblTimeSpentXML.Text = "";
        }

        private void btnClearTable_Click(object sender, EventArgs e)
        {
            dgTable.DataSource = null;
            lblTimeSpentTable.Text = "";
        }

        private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("An Experimental Performance Comparison on Methods to Send Tabular Data to SQL Server Paramaters. - Song LI, 12/20/2009");
        }

        private void viewToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmViewSourceData frm = new frmViewSourceData(DataToUpload);
            frm.ShowDialog();
        }

    }
}