Creating new tables and loading data in SQL server by reading an Xml file

  • Article

This kind of requirement may come if VSTS is used for test automation and the test results have to be loaded to a Database. Since the output of test automation is an xml file, let us look at how we can create tables and load them with data by reading an xml file.

The logic at a high level is as follows.

1. Load the Xml file into a DataSet

2. Use SMO objects to access the target SQL Server and DB

3. Read the DataTables one by one from DataSet and create corresponding tables in target Server

4. Use SMO's BulkCopy to load the data from DataTable to the newly created table in target server

Now, create a windows form with three textboxes for capturing target DB server name, DB name and Xml file name. Add a button to proceed with the execution.

Use the following SMO name spaces in the code. Make sure the assemblies "Microsoft.SqlServer.Smo" and "Microsoft.SqlServer.ConnectionInfo" are added to project references in Visual Studio 2005.

using

Microsoft.SqlServer.Management;

using

Microsoft.SqlServer.Management.Smo;

using

Microsoft.SqlServer.Management.Common;

using

Microsoft.SqlServer.Server;

using

Microsoft.SqlServer;

Add the following code to the button click event.

private void button1_Click(object sender, EventArgs e)

{

DataSet ds = new DataSet();

//ds.ReadXmlSchema("E:\\Temp\\SampleWT1.xsd");

ds.ReadXml(txtXmlLoc.Text);

//ds.ReadXml("E:\\Temp\\Sample.xml");

Server objServer = new Server(txtServer.Text);

Database objDB = objServer.Databases[txtDatabase.Text];

string strCon = "Data Source=" + txtServer.Text + ";Initial Catalog=Rnd;Integrated Security=SSPI";

SqlConnection objCon = new SqlConnection(strCon);

objCon.Open();

int intTotalTables = ds.Tables.Count;

MessageBox.Show("Total Tables = " + intTotalTables.ToString());

Table T;

for (int intTables = 0; intTables < intTotalTables; intTables++)

{

T =

new Table(objDB, "sqlCC_" + ds.Tables[intTables].TableName);

Column C = new Column();

//MessageBox.Show(ds.Tables[intTables].Rows.Count.ToString());

foreach (DataColumn dc in ds.Tables[intTables].Columns)

{

C =

new Column(T, dc.ColumnName);

C.DataType = MapDataType(dc.DataType.ToString(), dc.MaxLength);

T.Columns.Add(C);

}

T.Create();

SqlBulkCopy objBulkcopy = new SqlBulkCopy(objCon);

objBulkcopy.DestinationTableName =

"sqlCC_" + ds.Tables[intTables].TableName;

objBulkcopy.WriteToServer(ds.Tables[intTables]);

objBulkcopy.Close();

}

objCon.Close();

MessageBox.Show("All tables are successfully loaded");

}

Add a routine for mapping the data types.

public DataType MapDataType(string dataType, int dataTypeLength)

{

DataType DTTemp = null;

switch (dataType)

{

case ("System.Decimal"):

DTTemp =

new DataType(SqlDataType.Decimal, 10, 2);

break;

case ("System.String"):

DTTemp =

new DataType(SqlDataType.VarChar, dataTypeLength);

break;

case ("System.Int32"):

DTTemp =

new DataType(SqlDataType.Int);

break;

case ("System.Byte"):

DTTemp =

new DataType(SqlDataType.Bit);

break;

case ("System.DateTime"):

DTTemp =

new DataType(SqlDataType.DateTime);

break;

case ("System.Boolean"):

DTTemp =

new DataType(SqlDataType.Int);

break;

case ("System.SByte"):

DTTemp =

new DataType(SqlDataType.Bit);

break;

case ("System.UInt32"):

DTTemp =

new DataType(SqlDataType.Int);

break;

}

return DTTemp;

}

I am just using a prefix "sqlCC" while creating the target table. This is just for easy identification of the newly created tables in my target DB.