Default.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/ADMIN/ADMINMASTER.master" AutoEventWireup="true" CodeFile="XmlToDatabase.aspx.cs" Inherits="ADMIN_XmlToDatabase" %>
XmlToDatabase.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Serialization;
using System.Drawing;
public partial class ADMIN_XmlToDatabase : System.Web.UI.Page
{
#region Declare
DataSet ds;
BOXml objboxml;
BLLXml objbllxml;
#endregion
protected void Page_Load(object sender, EventArgs e) //btnReader_Click
{
string currentdate = "";
string Previous_Date = "";
if (!IsPostBack)
{
string month = DateTime.Today.Month.ToString();
if (month.Length == 1)
{
month = "0" + month;
}
string date = DateTime.Today.Day.ToString();
if (date.Length == 1)
{
date = "0" + date;
}
currentdate = DateTime.Today.Year.ToString() + month + date;
Previous_Date = DateTime.Now.AddDays(-1).ToShortDateString();
string[] Previous_Date_Values = Previous_Date.Split('/');
string Previous_Date_Year = "";
string Previous_Month = "";
string Previous_Date_Vale = "";
if (Previous_Date_Values[0].Length == 1)
{
Previous_Month = "0" + Previous_Date_Values[0].ToString();
}
else
{
Previous_Month = Previous_Date_Values[0].ToString();
}
if (Previous_Date_Values[1].Length == 1)
{
Previous_Date_Vale = "0" + Previous_Date_Values[1].ToString();
}
else
{
Previous_Date_Vale = Previous_Date_Values[1].ToString();
}
Previous_Date_Year = Previous_Date_Values[2].ToString();
Previous_Date_Vale = Previous_Date_Year + Previous_Month + Previous_Date_Vale;
for (int i = 0; i <= 1; i++)
{
if (Directory.Exists(System.Web.Hosting.HostingEnvironment.MapPath("~/Sapoutbound/ProductMaster/" + currentdate)))
{
string FolderDate = "";
FolderDate = currentdate;
//
}
currentdate = Previous_Date_Vale;
}
}
SaveXmlToDatabase("~/MainFolder/SubFolder1/Sub2/Sub3/Sub4/", currentdate);
// WriteToServer("~/FileServer/SubFolder1", currentdate);
}
public void SaveXmlToDatabase(string filepath, string CurrentDate)
{
try
{
string month = DateTime.Today.Month.ToString();
if (month.Length == 1)
{
month = "0" + month;
}
string date = DateTime.Today.Day.ToString();
if (date.Length == 1)
{
date = "0" + date;
}
string currentdate = DateTime.Today.Year.ToString() + month + date;
FileInfo[] fileInfos = null;
string Location_Filename = "Sitemaster" + currentdate;
int intCount = 0;
DirectoryInfo myDir = new DirectoryInfo(System.Web.Hosting.HostingEnvironment.MapPath(filepath));
fileInfos = myDir.GetFiles();
for (intCount = 0; intCount <= fileInfos.Length - 1; intCount++)
{
string OriginalFilename = fileInfos[intCount].Name.ToString();
string[] OriginalFilenames = OriginalFilename.Split('-');
if (Location_Filename == OriginalFilenames[0])
{
string myXMLfile = Server.MapPath("~/MainFolder/SubFolder1/Sub2/Sub3/Sub4/" + OriginalFilename);
DataSet ds = new DataSet();
System.IO.FileStream fsReadXml = new System.IO.FileStream(myXMLfile, System.IO.FileMode.Open);
ds.ReadXml(fsReadXml);
/////Save From DataSet to FileServer Folder
string myXMLfile2 = Server.MapPath("~/otherMainFolder/othersub1/" + OriginalFilename);
DataSet ds1 = new DataSet();
ds1 = ds;
ds1.WriteXml(myXMLfile2);
SqlConnection conn = new SqlConnection("Data Source=info;Initial Catalog=info;User ID=info;Password=info");
conn.Open();
int RowsCount = ds.Tables[0].Rows.Count;
for (int i = 0; i <= RowsCount - 1; i++)
{
objboxml = new BOXml();
objbllxml = new BLLXml();
objboxml.SITE_SITECODE_ID = ds.Tables[0].Rows[i][0].ToString();
objboxml.SITE_SITENAME_NM = ds.Tables[0].Rows[i][1].ToString();
objboxml.SITE_SITECATEGORY_TYPE = ds.Tables[0].Rows[i][2].ToString();
objboxml.SITE_SITEADDRESS = ds.Tables[0].Rows[i][3].ToString();
objboxml.CITY_CITYNAME_NM = ds.Tables[0].Rows[i][4].ToString();
objboxml.SITE_SITEPINCODE_CD = ds.Tables[0].Rows[i][5].ToString();
objboxml.STATE_STATEID_ID = Convert.ToInt32(ds.Tables[0].Rows[i][6]);
objboxml.SITE_SITECONTACTPERSON_NM = ds.Tables[0].Rows[i][7].ToString();
objboxml.SITE_SITETELEPHONENUMBER_NBR = ds.Tables[0].Rows[i][8].ToString();
objboxml.SITE_SITECREATED_DTTM = DateTime.Now.ToString();
int count = objbllxml.InsertXMLDatabase(objboxml);
fsReadXml.Close();
}
}
}
}
catch (Exception ex)
{
Label1.Text = ex.Message.ToString();
}
}
protected void btnReader_Click(object sender, EventArgs e) //btnReader_Click
{
}
}
BOXml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace POS.BO.ADMIN
{
public class BOXml
{
private string _SITE_SITECODE_ID;
private string _SITE_SITENAME_NM;
private string _SITE_SITECATEGORY_TYPE;
private string _SITE_SITEADDRESS;
private int _CITY_CITYID_ID;
private string _CITY_CITYNAME_NM;
private string _SITE_SITEPINCODE_CD;
private int _STATE_STATEID_ID;
private string _SITE_SITECONTACTPERSON_NM;
private string _SITE_SITETELEPHONENUMBER_NBR;
private string _SITE_SITECREATED_DTTM;
public string SITE_SITECODE_ID
{
get { return _SITE_SITECODE_ID; }
set { _SITE_SITECODE_ID = value; }
}
public string SITE_SITENAME_NM
{
get { return _SITE_SITENAME_NM; }
set { _SITE_SITENAME_NM = value; }
}
public string SITE_SITECATEGORY_TYPE
{
get { return _SITE_SITECATEGORY_TYPE; }
set { _SITE_SITECATEGORY_TYPE = value; }
}
public string SITE_SITEADDRESS
{
get { return _SITE_SITEADDRESS; }
set { _SITE_SITEADDRESS = value; }
}
public int CITY_CITYID_ID
{
get { return _CITY_CITYID_ID; }
set { _CITY_CITYID_ID = value; }
}
public string SITE_SITEPINCODE_CD
{
get { return _SITE_SITEPINCODE_CD; }
set { _SITE_SITEPINCODE_CD = value; }
}
public int STATE_STATEID_ID
{
get { return _STATE_STATEID_ID; }
set { _STATE_STATEID_ID = value; }
}
public string SITE_SITECONTACTPERSON_NM
{
get { return _SITE_SITECONTACTPERSON_NM; }
set { _SITE_SITECONTACTPERSON_NM = value; }
}
public string SITE_SITETELEPHONENUMBER_NBR
{
get { return _SITE_SITETELEPHONENUMBER_NBR; }
set { _SITE_SITETELEPHONENUMBER_NBR = value; }
}
public string SITE_SITECREATED_DTTM
{
get { return _SITE_SITECREATED_DTTM; }
set { _SITE_SITECREATED_DTTM = value; }
}
public string CITY_CITYNAME_NM
{
get { return _CITY_CITYNAME_NM; }
set { _CITY_CITYNAME_NM = value; }
}
}
}
DALXml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
public class DALXml
{
#region Declare
BOXml objboxml;
sqlhelper objsqlhelper;
DataSet ds;
#endregion
#region InsertXMLDatabase
public int InsertXMLDatabase(object ObjBOClass)
{
try
{
objboxml = ((BOXml)(ObjBOClass));
objsqlhelper = new sqlhelper();
objsqlhelper.ClearParameters();
objsqlhelper.CreateParameter("@SITE_SITEID_ID", 0);
objsqlhelper.CreateParameter("@SITE_SITECODE_ID", objboxml.SITE_SITECODE_ID);
objsqlhelper.CreateParameter("@SITE_SITENAME_NM", objboxml.SITE_SITENAME_NM);
objsqlhelper.CreateParameter("@SITE_SITECATEGORY_TYPE", objboxml.SITE_SITECATEGORY_TYPE);
objsqlhelper.CreateParameter("@SITE_SITEADDRESS", objboxml.SITE_SITEADDRESS);
objsqlhelper.CreateParameter("@CITY_CITYNAME_NM", objboxml.CITY_CITYNAME_NM);
objsqlhelper.CreateParameter("@SITE_SITEPINCODE_CD", objboxml.SITE_SITEPINCODE_CD);
objsqlhelper.CreateParameter("@STATE_STATEID_ID", objboxml.STATE_STATEID_ID);
objsqlhelper.CreateParameter("@SITE_SITECONTACTPERSON_NM", objboxml.SITE_SITECONTACTPERSON_NM);
objsqlhelper.CreateParameter("@SITE_SITETELEPHONENUMBER_NBR", objboxml.SITE_SITETELEPHONENUMBER_NBR);
objsqlhelper.CreateParameter("@SITE_SITECREATED_DTTM", objboxml.SITE_SITECREATED_DTTM);
return objsqlhelper.ExecuteNonQuery("HOP_MasterSite");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
}
}
BLLXml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
public class BLLXml
{
#region InsertXMLDatabase
public int InsertXMLDatabase(object ObjBOClass)
{
try
{
return (int)new DALXml().InsertXMLDatabase(ObjBOClass);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
}
Stored Procedures
CREATE TABLE [dbo].[ MASTERSITE_HDR] (
[SITE_SITEID_ID] int IDENTITY(1, 1) NOT NULL,
[SITE_SITECODE_ID] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SITE_SITENAME_NM] varchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SITE_SITECATEGORY_TYPE] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SITE_SITEADDRESS] varchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITY_CITYNAME_NM] varchar(70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SITE_SITEPINCODE_CD] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATE_STATEID_ID] int NOT NULL,
[SITE_SITECONTACTPERSON_NM] varchar(70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SITE_SITETELEPHONENUMBER_NBR] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SITE_SITECREATED_DTTM] datetime NULL,
CONSTRAINT [PK_ MASTERSITE_HDR] PRIMARY KEY CLUSTERED ([SITE_SITEID_ID])
)
ON [PRIMARY]
GO
CREATE procedure MasterSite
(
@SITE_SITEID_ID int,
@SITE_SITECODE_ID varchar(20),
@SITE_SITENAME_NM varchar(150),
@SITE_SITECATEGORY_TYPE varchar(20),
@SITE_SITEADDRESS varchar(150),
@CITY_CITYNAME_NM varchar(70),
@SITE_SITEPINCODE_CD varchar(20),
@STATE_STATEID_ID int,
@SITE_SITECONTACTPERSON_NM varchar(70),
@SITE_SITETELEPHONENUMBER_NBR varchar(20),
@SITE_SITECREATED_DTTM DateTime
)
As
IF EXISTS(SELECT * FROM MasterSite_HDR WHERE SITE_SITECODE_ID=@SITE_SITECODE_ID)
BEGIN
UPDATE MasterSite_HDR SET
SITE_SITECODE_ID = @SITE_SITECODE_ID, SITE_SITENAME_NM = @SITE_SITENAME_NM,
SITE_SITECATEGORY_TYPE = @SITE_SITECATEGORY_TYPE, SITE_SITEADDRESS = @SITE_SITEADDRESS,
CITY_CITYNAME_NM = @CITY_CITYNAME_NM, SITE_SITEPINCODE_CD = @SITE_SITEPINCODE_CD,
STATE_STATEID_ID = @STATE_STATEID_ID, SITE_SITECONTACTPERSON_NM = @SITE_SITECONTACTPERSON_NM,
SITE_SITETELEPHONENUMBER_NBR = @SITE_SITETELEPHONENUMBER_NBR,
SITE_SITECREATED_DTTM = @SITE_SITECREATED_DTTM
WHERE SITE_SITEID_ID = @SITE_SITEID_ID
END
ELSE
BEGIN
Insert Into MasterSite_HDR
(
SITE_SITECODE_ID, SITE_SITENAME_NM, SITE_SITECATEGORY_TYPE, SITE_SITEADDRESS,
CITY_CITYNAME_NM, SITE_SITEPINCODE_CD, STATE_STATEID_ID, SITE_SITECONTACTPERSON_NM,
SITE_SITETELEPHONENUMBER_NBR, SITE_SITECREATED_DTTM
)
Values
(
@SITE_SITECODE_ID, @SITE_SITENAME_NM, @SITE_SITECATEGORY_TYPE, @SITE_SITEADDRESS,
@CITY_CITYNAME_NM, @SITE_SITEPINCODE_CD, @STATE_STATEID_ID,@SITE_SITECONTACTPERSON_NM,
@SITE_SITETELEPHONENUMBER_NBR, @SITE_SITECREATED_DTTM
)
END
CREATE Procedure MasterProgram
(
@PROG_PROGARAMID_ID INT,
@PROG_PROGRAMNAME_NM varchar(300),
@PROG_PROGRAMDESCRIPTION_DESC varchar(300),
@PROG_PROGRAMTYPE_DESC varchar(300),
@PROG_PROGRAMSTARTDATE_DTTM DateTime = null,
@PROG_PROGRAMENDDATE_DTTM DateTime = null,
@PROG_PROGRAMCREATED_DTTM DateTime,
@USER_USERID_ID int,
@TranType varchar(60)
)
As
--declare @PROG_PROGRAMCREATED_DTTM datetime
--set @PROG_PROGRAMCREATED_DTTM = getDate()
IF @TRANTYPE = 'INSERT'
BEGIN
INSERT INTO MASTERPROGRAM_HDR
( PROG_PROGRAMNAME_NM, PROG_PROGRAMDESCRIPTION_DESC, PROG_PROGRAMTYPE_DESC,
PROG_PROGRAMSTARTDATE_DTTM, PROG_PROGRAMENDDATE_DTTM , PROG_PROGRAMCREATED_DTTM,
USER_USERID_ID
) VALUES
(@PROG_PROGRAMNAME_NM,@PROG_PROGRAMDESCRIPTION_DESC,@PROG_PROGRAMTYPE_DESC,
@PROG_PROGRAMSTARTDATE_DTTM, @PROG_PROGRAMENDDATE_DTTM, @PROG_PROGRAMCREATED_DTTM,
@USER_USERID_ID
)
END
IF @TRANTYPE = 'UPDATE'
BEGIN
UPDATE MASTERPROGRAM_HDR SET
PROG_PROGRAMNAME_NM = @PROG_PROGRAMNAME_NM, PROG_PROGRAMDESCRIPTION_DESC = @PROG_PROGRAMDESCRIPTION_DESC,
PROG_PROGRAMTYPE_DESC = @PROG_PROGRAMTYPE_DESC , PROG_PROGRAMSTARTDATE_DTTM = @PROG_PROGRAMSTARTDATE_DTTM,
PROG_PROGRAMENDDATE_DTTM = @PROG_PROGRAMENDDATE_DTTM, PROG_PROGRAMCREATED_DTTM = @PROG_PROGRAMCREATED_DTTM,
USER_USERID_ID = @USER_USERID_ID WHERE PROG_PROGARAMID_ID = @PROG_PROGARAMID_ID
END
IF @TRANTYPE = 'DELETE'
BEGIN
DELETE FROM MASTERPROGRAM_HDR WHERE PROG_PROGARAMID_ID = @PROG_PROGARAMID_ID
END
CREATE Procedure SelectProgram
Select * from MASTERPROGRAM_HDR
0 comments:
Post a Comment