XML DATA Need to store in db and Create table and Data types

Hi i want get xml data and Create table and that data need to store in sql db i having issue all the columns values data types or string var(max) only i want date date data type and int int data type want to create in db any body help for this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Xml;
public partial class Default2 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
}
protected void UploadXML(object sender, EventArgs e)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string filePath = Server.MapPath("~/Uploads/") + fileName;
FileUpload1.SaveAs(filePath);
string xml = File.ReadAllText(filePath);
string XMlFile = filePath;
//if (XMlFile=="")
// {
// Conversion Xml file to DataTable
DataTable dt = CreateDataTableXML(XMlFile);
if (dt.Columns.Count == 0)
dt.ReadXml(XMlFile);
// Creating Query for Table Creation
string Query = CreateTableQuery(dt);
con.Open();
// Deletion of Table if already Exist
SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." + dt.TableName + "', 'U') IS NOT NULL DROP TABLE dbo." + dt.TableName + ";", con);
cmd.ExecuteNonQuery();
// Table Creation
cmd = new SqlCommand(Query, con);
int check = cmd.ExecuteNonQuery();
if (check != 0)
{
using (SqlTransaction transaction =
con.BeginTransaction())
{
SqlBulkCopy sbc = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, transaction);
foreach (DataColumn col in dt.Columns)
{
sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
sbc.BulkCopyTimeout = 600;
sbc.DestinationTableName = dt.TableName;
sbc.WriteToServer(dt);
}
// MessageBox.Show("Table Created Successfully");
}
con.Close();
//}+
}
// Getting Table Name as Per the Xml File Name
public string GetTableName(string file)
{
FileInfo fi = new FileInfo(file);
string TableName = fi.Name.Replace(fi.Extension, "");
return TableName;
}
// Getting Query for Table Creation
public string CreateTableQuery(DataTable table)
{
string sqlsc = "CREATE TABLE " + table.TableName + "(";
//progressBar1.Maximum = table.Columns.Count;
// progressBar1.Value = 0;
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "[" + table.Columns[i].ColumnName + "]";
string columnType = table.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint";
break;
case "System.Byte":
sqlsc += " tinyint";
break;
case "System.Decimal":
sqlsc += " decimal ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.String":
default:
sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
break;
}
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
// Progress();
}
return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
}
// Conversion Xml file to DataTable
public DataTable CreateDataTableXML(string XmlFile)
{
XmlDocument doc = new XmlDocument();
doc.Load(XmlFile);
DataTable Dt = new DataTable();
try
{
Dt.TableName = GetTableName(XmlFile);
XmlNode NodoEstructura = doc.DocumentElement.ChildNodes.Cast().ToList()[0];
// progressBar1.Maximum = NodoEstructura.ChildNodes.Count;
//progressBar1.Value = 0;
foreach (XmlNode columna in NodoEstructura.ChildNodes)
{
Dt.Columns.Add(columna.Name, typeof(String));
//Progress();
}
XmlNode Filas = doc.DocumentElement;
// progressBar1.Maximum = Filas.ChildNodes.Count;
// progressBar1.Value = 0;
foreach (XmlNode Fila in Filas.ChildNodes)
{
List<string> Valores = Fila.ChildNodes.Cast().ToList().Select(x => x.InnerText).ToList();
Dt.Rows.Add(Valores.ToArray());
// Progress();
}
}
catch (Exception ex)
{
}
return Dt;
}
// Show Progress Bar
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mario Scheeren5y ago
Have an XSD describing the XML, so you can
a) use XSD.exe to generadte classes / datatables
b) have types defined what type they are.
Since you do this:
foreach (XmlNode columna in NodoEstructura.ChildNodes)
{
  Dt.Columns.Add(columna.Name, typeof(String));
}
it is nothing strange that you only have Columns with strings creating varchar in Sql.
Btw: Disposing is nothing you should avoid ... sorry, but this is crappy code ...