Insert, Update using OpenXML in Sql Server

Me_Himanshu
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 33166 red flag

We were needed to insert basic details of a user through a registration page.The basic details being firstname, lastname, age, DOB, Address etc..Now inserting and updating the values in the database is very simple thing..But keeping in mind the performance, sometimes we need to adopt alternative methods of performing actions.Here We will Use OpenXML technology of SQL Server,i.e taking the bulk data in XML format and then inserting all the data in one or different tables


 Download source code for Insert, Update using OpenXML in Sql Server

Introduction

Before Moving Further,lets know more about OpenXML.

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.


Objective

When we talk about bulk insert of the data,we have many options.Inserting data in XML Pattern is one.Since XML is fast,openxml provides us a way to insert bulk data in a easier and faster way.We use dataset in our code,converts the data in the dataset object in xml pattern and then inserts the data using openxml 



Using the code

First create a table..In this article we are creating the table with 5 columns ,but you can have more

Create table emptable
(
pkid int identity(1,1) primary key not null,
Name varchar(15),
Age int,
Email varchar(20),
City varchar(50),
Country varchar(50)
) 
Now create a Stored Procedure for Insert.

create proc Inserttest
(
@DOC varchar(max)
)
as

Declare @idoc int
begin

begin try
execute sp_xml_preparedocument @idoc output,@DOC

insert into emptable(Name,Age,Email,City,Country)
select Name,Age,Email,City,Country from openxml(@idoc,'/NewDataSet/emptable',2)
with (Name varchar(20),Age int,Email varchar(20),City varchar(50),Country varchar(50))
end try
begin catch
if(@@trancount>0)
print 'error ocuured'
rollback
end catch
end


SP_XML_PrepareDocument is an inbuild procedure of sql server that Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on.

Now create a form asking input from the user.



Here goes the code....
 #region Property
    public string Firstname
    {
        get;
        set;
    }
    public int Age
    {
        get;
        set;
    }
    public string Email
    {
        get;
        set;
    }
    public string City
    {
        get;
        set;
    }
    public string Country
    {
        get;
        set;
    }
    public string DOC
    {
        get;
        set;
    }
    #endregion
    #region Method
    ArrayList names = new ArrayList();//to hold multiple vaules
    ArrayList values = new ArrayList();//use system.collections namespace for arraylist
    ArrayList types= new ArrayList();
 protected void btnsave_Click(object sender, EventArgs e)
    {
       
        this.Firstname = txtname.Text.Trim().ToString();
        this.Age = Convert.ToInt32(txtAge.Text.Trim().ToString());
        this.Email= txtemail.Text.Trim().ToString();
        this.City = txtcity.Text.Trim().ToString();
        this.Country = txtcountry.Text.Trim().ToString();
        
        string temp=this.Genrate();
        this.DOC=temp;
        if (temp != "")
        {
            Save();
        }

    }
  
  public string  Genrate()
    {
        DataSet ds = new DataSet();
        
        DataTable dt = new DataTable();
        dt.Columns.Add(new  DataColumn("Name",typeof(string)));
        dt.Columns.Add(new  DataColumn("Age",typeof(int)));
        dt.Columns.Add(new DataColumn("Email", typeof(string)));
        dt.Columns.Add(new DataColumn("City", typeof(string)));
        dt.Columns.Add(new DataColumn("Country", typeof(string)));
        
        ds.Tables.Add(dt);
        DataRow drr = ds.Tables[0].NewRow();
        drr["Name"] = txtname.Text.Trim().ToString();
        drr["Age"] = Convert.ToInt32(txtAge.Text.Trim().ToString());
        drr["Email"] = txtemail.Text.Trim().ToString();
        drr["City"] = txtcity.Text.Trim().ToString();
        drr["Country"] = txtcountry.Text.Trim().ToString();
        ds.Tables[0].Rows.Add(drr);
        ds.Tables[0].TableName = "emptable";
        return ds.GetXml();

    }
    public int Save()
    {
        names.Add("@DOC");values.Add(DOC);types.Add(SqlDbType.VarChar);
         Daoj.ExecuteTransaction("Inserttest", values, names, types);
    }
   

When the debugger hits generate method, we can see the data converted to XML

My Daoj.ExecuteTransaction is Dataacess layer method that takes 4 parameter for input.

//data access layer.-" 
public int ExecuteTransaction(string sp_name, ArrayList values, ArrayList names, ArrayList types)
        {
            int ret = 0;
            SqlConnection Conn = DataAccess.Connect();
            try
            {

                SqlCommand sqlCmd = new SqlCommand();

                for (int i = 0; i < Convert.ToInt32(values.Count); i++)
                {
                    SqlParameter IntPara = sqlCmd.Parameters.AddWithValue(names[i].ToString(), types[i]);
                    IntPara.Direction = ParameterDirection.Input;
                    IntPara.Value = values[i];
                }

                sqlCmd.Connection = Conn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = sp_name;
                sqlCmd.CommandTimeout = 100000;
                if (Conn.State == ConnectionState.Closed) { Conn.Open(); }
                ret = sqlCmd.ExecuteNonQuery();

                sqlCmd.Parameters.Clear();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
            }
            return ret;
        }

Note-One thing is needed to remember,the column name here you are taking in datarow, and the column name defining in 'With; keyword in stored procedure should be the same, even the case of both should be matching.

Now check the table


Same,with the case of update,run the update procedure.


Conclusion


Here is how we can insert the bulk data in database using OpenXML in SQL Server.


Page copy protected against web site content infringement by Copyscape

About the Author

Me_Himanshu
Full Name: Himanshu Pandey
Member Level: Starter
Member Status: Member
Member Since: 6/6/2012 2:28:47 AM
Country: India
Himanshu Pandey
not yet
I am a avid user if this website since i started my career ,always fantasize to post some useful articles into it.I am having 2.5 years of experience in Asp.Net,C#,Sql server,JavaScript and just started m working on WCF and Linq.

Login to vote for this post.

Comments or Responses

Posted by: vishwanathu9-27384 on: 7/3/2014 | Points: 25
Hi himanshu

Good one:)
But can you please define what is the use to take xml for that ,why we can't use simple data adapter thing for that.
Posted by: Me_Himanshu on: 7/3/2014 | Points: 25
Hi Vishwanath
Xml is for inserting bulk data in a faster way.It is better than dataadpter if we have bulk data to be inserted,
Second benefit of using XML is that if we have to add/reduce some parameter in our procedure,than we simply need to ad in xml,rather in appcode.Thus it avoids publishing of the application again

Login to post response

Comment using Facebook(Author doesn't get notification)