1
Answer

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

Thulasiram pakala

Thulasiram pakala
3y
505
1
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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.IO;  
  8. using System.Data;  
  9. using System.Configuration;  
  10. using System.Data.SqlClient;  
  11. using System.Xml;  
  12.   
  13. public partial class Default2 : System.Web.UI.Page  
  14. {  
  15.     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString);  
  16.     protected void Page_Load(object sender, EventArgs e)  
  17.     {  
  18.     }  
  19.     protected void UploadXML(object sender, EventArgs e)  
  20.         {  
  21.           string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
  22.         string filePath = Server.MapPath("~/Uploads/") + fileName;  
  23.         FileUpload1.SaveAs(filePath);  
  24.         string xml = File.ReadAllText(filePath);  
  25.         string XMlFile = filePath;  
  26.         //if (XMlFile=="")  
  27.         //    {  
  28.                 // Conversion Xml file to DataTable  
  29.                 DataTable dt = CreateDataTableXML(XMlFile);  
  30.                 if (dt.Columns.Count == 0)  
  31.                     dt.ReadXml(XMlFile);  
  32.                 // Creating Query for Table Creation  
  33.                 string Query = CreateTableQuery(dt);  
  34.                 con.Open();  
  35.                 // Deletion of Table if already Exist  
  36.                 SqlCommand cmd = new SqlCommand("IF OBJECT_ID('dbo." + dt.TableName + "', 'U') IS NOT NULL DROP TABLE dbo." + dt.TableName + ";", con);  
  37.                 cmd.ExecuteNonQuery();  
  38.                 // Table Creation  
  39.                 cmd = new SqlCommand(Query, con);  
  40.                 int check = cmd.ExecuteNonQuery();  
  41.                 if (check != 0)  
  42.                 {  
  43.                     using (SqlTransaction transaction =  
  44.                    con.BeginTransaction())  
  45.                     {  
  46.                         SqlBulkCopy sbc = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, transaction);  
  47.                         foreach (DataColumn col in dt.Columns)  
  48.                         {  
  49.                             sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName);  
  50.                         }  
  51.                         sbc.BulkCopyTimeout = 600;  
  52.                         sbc.DestinationTableName = dt.TableName;  
  53.                         sbc.WriteToServer(dt);  
  54.                     }  
  55.                    // MessageBox.Show("Table Created Successfully");  
  56.                 }  
  57.                 con.Close();  
  58.             //}+  
  59.         }  
  60.         // Getting Table Name as Per the Xml File Name  
  61.         public string GetTableName(string file)  
  62.         {  
  63.             FileInfo fi = new FileInfo(file);  
  64.             string TableName = fi.Name.Replace(fi.Extension, "");  
  65.             return TableName;  
  66.         }  
  67.         // Getting Query for Table Creation  
  68.         public string CreateTableQuery(DataTable table)  
  69.         {  
  70.             string sqlsc = "CREATE TABLE " + table.TableName + "(";  
  71.             //progressBar1.Maximum = table.Columns.Count;  
  72.            // progressBar1.Value = 0;  
  73.             for (int i = 0; i < table.Columns.Count; i++)  
  74.             {  
  75.                 sqlsc += "[" + table.Columns[i].ColumnName + "]";  
  76.                 string columnType = table.Columns[i].DataType.ToString();  
  77.                 switch (columnType)  
  78.                 {  
  79.                     case "System.Int32":  
  80.                         sqlsc += " int ";  
  81.                         break;  
  82.                     case "System.Int64":  
  83.                         sqlsc += " bigint ";  
  84.                         break;  
  85.                     case "System.Int16":  
  86.                         sqlsc += " smallint";  
  87.                         break;  
  88.                     case "System.Byte":  
  89.                         sqlsc += " tinyint";  
  90.                         break;  
  91.                     case "System.Decimal":  
  92.                         sqlsc += " decimal ";  
  93.                         break;  
  94.                     case "System.DateTime":  
  95.                         sqlsc += " datetime ";  
  96.                         break;  
  97.                     case "System.String":  
  98.                     default:  
  99.                         sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());  
  100.                         break;  
  101.                 }  
  102.                 if (table.Columns[i].AutoIncrement)  
  103.                     sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";  
  104.                 if (!table.Columns[i].AllowDBNull)  
  105.                     sqlsc += " NOT NULL ";  
  106.                 sqlsc += ",";  
  107.                // Progress();  
  108.             }  
  109.             return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";  
  110.         }  
  111.         // Conversion Xml file to DataTable  
  112.         public DataTable CreateDataTableXML(string XmlFile)  
  113.         {  
  114.             XmlDocument doc = new XmlDocument();  
  115.             doc.Load(XmlFile);  
  116.             DataTable Dt = new DataTable();  
  117.             try  
  118.             {  
  119.                 Dt.TableName = GetTableName(XmlFile);  
  120.                 XmlNode NodoEstructura = doc.DocumentElement.ChildNodes.Cast<XmlNode>().ToList()[0];  
  121.                // progressBar1.Maximum = NodoEstructura.ChildNodes.Count;  
  122.                //progressBar1.Value = 0;  
  123.                 foreach (XmlNode columna in NodoEstructura.ChildNodes)  
  124.                 {  
  125.                     Dt.Columns.Add(columna.Name, typeof(String));  
  126.                     //Progress();  
  127.                 }  
  128.                 XmlNode Filas = doc.DocumentElement;  
  129.               //  progressBar1.Maximum = Filas.ChildNodes.Count;  
  130.                // progressBar1.Value = 0;  
  131.                 foreach (XmlNode Fila in Filas.ChildNodes)  
  132.                 {  
  133.                     List<string> Valores = Fila.ChildNodes.Cast<XmlNode>().ToList().Select(x => x.InnerText).ToList();  
  134.                     Dt.Rows.Add(Valores.ToArray());  
  135.                    // Progress();  
  136.                 }  
  137.             }  
  138.             catch (Exception ex)  
  139.             {  
  140.             }  
  141.             return Dt;  
  142.         }  
  143.         // Show Progress Bar  
  144.     }  
  145. }  
Answers (1)
Next Recommended Forum