Thursday, May 7, 2015

Bulk Insert C# DataTable , DataSet to SQL Using XML

Are you looking for the code to Bulk insert Data to SQL from DataTable or DataSet Using XML then this article will surely help you learn how to Bulk insert Data from DataTable , DataSet to SQL Tables . This article will be discussed along with source code in detail to perform Bulk Insert using XML.


Header files To Be Included:

using System.Xml.Serialization;
using System.Xml;
 

DataTable To DataSet:

If you have the data in DataTable then you need to add all the DataTable to DataSet as DataSet holds the property of converting the XML function of data i.e "Getxml()".

Dt_Table_1.TableName = "Dt_Table_1";
Dt_Table_2.TableName = "Dt_Table_2";

Ds.Tables.Add(
Dt_Table_1);
Ds.Tables.Add(Dt_Table_2);

Now your DataTables would have been added to your DataSet in the above code Dt_Table_1 and 
Dt_Table_2 are the two DataTables first you need to specify a name for your DataTable and then you have to add the specific DataTable to your DataSet.

DataSet To XML:
Below i will give the code to convert the available DataSet to XML and then i will explain how to pass the XML as arguments to SQL Procedure. In the class file ClsProgenieTractorDetails we will have many variables and functions we will create an object for the class file i.e ObjTractorLandDetails  and then we will assign values to the class file.

            ClsProgenieTractorDetails ObjTractorLandDetails = new ClsProgenieTractorDetails();           
            ObjTractorLandDetails.Agri_Type_Others = Txt_Others.Text;
            ObjTractorLandDetails.No_Yrs_Agri = Txt_No_of_yrs_agri.Text;
            ObjTractorLandDetails.Own_Tractors = DDL_Own_Tractors.SelectedValue;
            ObjTractorLandDetails.Free_Nos = Txt_Free_Nos.Text;
            ObjTractorLandDetails.Loan_Nos = Txt_Loan_Nos.Text;
            ObjTractorLandDetails.Tax_Return_Filed = DDL_Tax_Return.SelectedValue;

XmlDocument xmlDoc = new XmlDocument();

XmlDocument xmlDoc_Grid = new XmlDocument();

XmlSerializer xmlSerializer = new XmlSerializer(ObjTractorLandDetails.GetType());


using (MemoryStream xmlStream = new MemoryStream())

            {

                xmlSerializer.Serialize(xmlStream, ObjTractorLandDetails);
                xmlStream.Position = 0;
                xmlDoc.Load(xmlStream);
            }
            string Grid_Xml;
            Grid_Xml = Ds_Land_Grid_Details.GetXml();
            xmlDoc_Grid.LoadXml(Grid_Xml);

            string Non_Grid_Result;
            Non_Grid_Result = xmlDoc.InnerXml;

            string Grid_Result;            Grid_Result = xmlDoc_Grid.InnerXml;

XML Value To SQL Procedure:

Below i will explain how to pass the XML parameters to Procedure and get the XML values in Procedure and bulk insert them to the SQL DataBase.

 SqlConnection ComConnection = Con.SqlCon("ConString")ComConnection.Open();SqlCommand Cmd = new SqlCommand(); Cmd.CommandType = CommandType.StoredProcedure;  Cmd.CommandText = StoredProcedureName;       Cmd.Connection = ComConnection; try  {     SqlParameter param1 = new SqlParameter("@XMLValue_NonGrid", SqlDbType.Xml);  param1.Value = XMLValue_NonGrid;         Cmd.Parameters.Add(param1);        SqlParameter param2 = new SqlParameter("@XMLValue_Grid", SqlDbType.Xml);    param2.Value = XMLValue_Grid;     Cmd.Parameters.Add(param2);      Cmd.ExecuteNonQuery();         return "Success";     }      catch (Exception ex)    {          return ex.Message;     }         finally      {        ComConnection.Close();    }           


 SQL Procedure To Table Bulk Insert:

Below i will explain how to read the XML values in procedure split it to according fields and bulk insert to the Tables.
CREATE procedure DP_Save_Agri_Land_Details@XMLValue_NonGrid XML,@XMLValue_Grid XML
SELECT

CAST(colx.query('data(Own_Tractors) ') As Varchar(MAX)) AS Own_Tractors,

CAST(colx.query('data(Free_Nos) ') AS VarChar(MAX)) AS Free_Nos  , CAST(colx.query('data(Loan_Nos) ') AS VarChar(MAX)) AS Loan_Nos ,

CAST(colx.query('data(Tax_Return_Filed) ') AS Varchar(MAX)) AS Tax_Return_Filed , CAST(colx.query('data(Saral_Date) ') AS VarChar(MAX)) AS Saral_Date ,
CAST(colx.query('data(Member_With_PACS) ') AS Varchar(MAX)) AS Member_With_PACS CAST(colx.query('data(Name_Of_PACS) ') AS VarChar(MAX)) AS Name_Of_PACS
Into #Temp_NonGrid
FROM @XMLValue_NonGrid.nodes('ClsProgenieTractorDetails') AS Tabx(Colx)

SELECT
CAST(colx.query('data(Txt_Net_Income_Harvest) ') AS VarChar(MAX)) AS Net_income_Harvest_Cycle,CAST(colx.query('data(Txt_Land) ') AS VarChar(MAX)) AS Land_No,
CAST(colx.query('data(Lbl_Area_Owned_Tot) ') AS Varchar(MAX)) AS Area_Owned,CAST(colx.query('data(Lbl_Area_Under_Mortage_Tot) ') AS VarChar(MAX)) AS Area_Under_Mortgage,
CAST(colx.query('data(Lbl_Lessor_Name) ') AS Varchar(MAX)) AS Lessor_Name,CAST(colx.query('data(Lbl_Lease_Rate_Annually) ') AS VarChar(MAX)) AS Lease_Rate
Into #Temp_Land_Details
FROM @XMLValue_Grid.nodes('NewDataSet/Dt_Land_Details') AS Tabx(Colx)

Insert Into TBT_Progenie_Agri_Dt(Application_No,Agri_Farmer,Agri_Landlord,Agri_Dairy_Farm,Agri_Poultry,Agri_Aquaculture,Agri_Others,Agri_Type_Others,No_Yrs_Agri)

Select A.AppNo,A.Agri_Farmer,A.Agri_Landlord,A.Agri_Dairy_Farm,A.Agri_Poultry,A.Agri_Aquaculture,A.Agri_Others,A.Agri_Type_Others,A.No_Yrs_Agri 
From #Temp_NonGrid A

No comments :

Post a Comment