//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(); }
Here you will find about .net technology. also can get example about MVC.net and jquery.
Thursday, January 16, 2014
Fastest way to Import data from XML to SQL asp.net
Subscribe to:
Post Comments (Atom)
Opps Part 1 : Abstraction
Abstraction in C# is a fundamental concept of object-oriented programming (OOP) that allows developers t...
-
<html> <body> <table> <tr><td>Text to Save:</td></tr> <tr> <td colspan="3...
-
SQL Server has several fixed database roles such as db_datareader and db_datawriter , which grants the user read and write access res...
-
Get Yahoo Contact for C# Create new Website Create AppCode Folder Create OAuthBase.cs in AppCode Folder like us...
No comments:
Post a Comment