1
Answer

How to get xml documnet columns data types in c#

Thulasiram pakala

Thulasiram pakala
3y
1.1k
1
Hi
 
I have one xml file that xml file when i upload that xml data and columns need to create and store in sql server
hear i am using in string create table like that in for lopp columns i added but in this code what happend na that columns data type all are consider varchar(max) i want to get xml file data types and nedd to create table and store that data hear i am use my code
  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