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
902
872
114.3k
XML DATA Need to store in db and Create table and Data types
Apr 16 2021 7:57 AM
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<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
)
CSOM Library of ContentType or Get list of ContentTypes of a Library
Delete Uploaded Image After Sending An Email