TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Thulasiram pakala
868
868
112.1k
How to get xml documnet columns data types in c#
Apr 17 2021 6:02 AM
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
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<XmlNode>().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<XmlNode>().ToList().Select(x => x.InnerText).ToList();
Dt.Rows.Add(Valores.ToArray());
// Progress();
}
}
catch
(Exception ex)
{
}
return
Dt;
}
// Show Progress Bar
}
}
Reply
Answers (
1
)
Html select option Multi select option
load partial view in same index page