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.