Thursday, January 16, 2014

Fastest way to Import data from XML to SQL asp.net

//create dataset which will have all data
 DataSet ds1 = new DataSet("XML data1");
 
 //load schema first, this is updates schema will attch with email ds1.ReadXmlSchema(@"D:\userdocs\Ken_XML\FAC_SSIS_Identities\output.xsd");
 
 ds1.EnforceConstraints = false;
 
 //load data in dataset
 ds1.ReadXml(@"<XML file>");
 
 //connect to sql server and appropriate database see if table already present, if not present create new table after that load data from dataset to sql using bulkcopy
 try
 {
 SqlConnection conn = new SqlConnection("Server=localhost;Initial Catalog= XML_data;Trusted_Connection=True;");
 conn.Open();
 foreach (DataTable dt in ds1.Tables)
 {
 //check if table is present or not
 string exists = null;
 try
 {
 SqlCommand cmd = new SqlCommand("SELECT * FROM sysobjects where name = '" + dt.TableName + "'", conn);
 exists = cmd.ExecuteScalar().ToString();
 }catch (Exception exce)
 {
 exists = null;
 }
 // selecting each column of the datatable to create a table in the database
 Console.WriteLine("Bulk Insert Started table:" + dt.TableName);
 SqlBulkCopy bulk = new SqlBulkCopy(conn);
 bulk.DestinationTableName = "[" + dt.TableName + "]";
 foreach (DataColumn dc in dt.Columns)
 {
 bulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
 string type = "";
 //Getting right data type for column is very importatnt as it can create problem later if wrong data type is chosen
 //for mapping we are using below key value pair
 //Dictionary<string, string> typemappings = new Dictionary<string, string>();
 //typemappings.Add("Decimal", "Numeric");
 //typemappings.Add("String", "varchar(255)");
 //typemappings.Add("Int32", "Int");
 typemappings.TryGetValue(dc.DataType.FullName.Split('.')[1], out type);
 if (type == null)
 type = "varchar(255)";
 if (exists == null)
 {
 SqlCommand createtable = new SqlCommand("CREATE TABLE [" + dt.TableName + "] ([" + dc.ColumnName + "] " + type + ")", conn);
 createtable.ExecuteNonQuery();
 exists = dt.TableName;
 }
 else
 {
 try
 {
 SqlCommand addcolumn = new SqlCommand("ALTER TABLE [" + dt.TableName + "] ADD [" + dc.ColumnName + "] " + type, conn);
 addcolumn.ExecuteNonQuery();
 }catch (Exception ex2) { }
 }
 }
 //load data in sql
 bulk.WriteToServer(dt);
 Console.WriteLine("Bulk Insert completed table:" + dt.TableName);
 }
 conn.Close()
 }catch (Exception ex)
 {
 Console.WriteLine(ex.Message.ToString() + "\n" + ex.StackTrace.ToString());
 }
 finally
 {
 ds1.Clear();
 ds1.Dispose();
 }

No comments:

Post a Comment

Opps Part 1 : Abstraction

  Abstraction in C# is a fundamental concept of object-oriented programming (OOP) that allows developers t...