locked
Insert XML data in Database using WCF RRS feed

  • Question

  • User1216489373 posted

    Hi,

    I want to get Data from XML that will be uploaded by User.
    Then upload that data in Database.
    Please provide the code for the same.

    Thanks,
    Rahul

    Tuesday, February 26, 2013 7:00 AM

Answers

  • User1216489373 posted
           /// <summary>
        /// Data Contract in Business Logic which type of data u have (I have used Employee as example)
        /// </summary>
        [DataContract]
        public class Employee
        {
            [DataMember]
            public int EmpCode { get; set; }
            [DataMember]
            public string EmpName { get; set; }
            [DataMember]
            public string EmpAddress { get; set; }
            [DataMember]
            public DateTime EmpDOJ { get; set; }
            [DataMember]
            public int EmpSalary { get; set; }
    
        }
    
    // Lets see my xml file content
    
    <?xml version="1.0"?>
    -<Employees> -
    <Employee>
       <Code>1</Code>
       <Name>Atma</Name>
       <Address>Gorakhpur</Address>
       <DOJ>12/2/2012</DOJ>
       <Salary>1000</Salary>
     </Employee>
    <Employee>
       <Code>2</Code>
       <Name>Ashish</Name>
       <Address>Kanpur</Address>
      <DOJ>11/5/2012</DOJ>
      <Salary>2000</Salary>
     </Employee>
    <Employee>
      <Code>3</Code>
      <Name>Rahul</Name>
      <Address>Delhi</Address>
      <DOJ>3/12/2012</DOJ>
      <Salary>3000</Salary>
     </Employee>
    </Employees>
    
    
    
    
    
             /// <summary>
            /// Reading XML File and storing data in to List
            /// </summary>
            /// <param name="filePath"></param>
            /// <returns></returns>
              [OperationContract]
            public  List<Employee> ReadXML(string filePath)
            {
                List<Employee> lstEmp = new List<Employee>();
                XmlDocument xDoc = new XmlDocument();
               // xDoc.Load("Your Xml File Path");
                xDoc.Load(filePath);
                XmlNodeList xEmpList = xDoc.GetElementsByTagName("Employee");
                foreach (XmlNode xNode in xEmpList)
                {
                    Employee emp = new Employee();
                    emp.EmpCode = Convert.ToInt32(xNode["Code"].InnerText);
                    emp.EmpName = xNode["Name"].InnerText;
                    emp.EmpAddress = xNode["Address"].InnerText;
                    emp.EmpDOJ =Convert.ToDateTime( xNode["DOJ"].InnerText);
                    emp.EmpSalary = Convert.ToInt32(xNode["Salary"].InnerText);
                    lstEmp.Add(emp);
    
                }
                return lstEmp;
            }
    
    
    
            /// <summary>
            /// Utilising the List retured by ReadXMl method for storing in database
            /// </summary>
            /// <param name="lstEmployee"></param>
            /// <returns></returns>
    
             [OperationContract]
            public int SaveEmployee(List<Employee> lstEmployee)
            {
                int isCommited = 0;
                try
                {
                    foreach (Employee emp in lstEmployee)
                    {
                        InsertEmployee(emp);
                         
                    }
    
                    isCommited = 1;
                    return isCommited;
                }
                catch (Exception)
                {
                    isCommited = 0;
                    return isCommited;
                    throw;
                }
            }
    
    
    
             /// <summary>
            /// Method which u would define in DAL
            /// </summary>
            /// <param name="emp"></param>
            public void InsertEmployee(Employee emp)
            {
                try
                {
                    SqlConnection Con = new SqlConnection("Your Connection String");
                    SqlCommand cmd = new SqlCommand("Usp_Insert_Employee", Con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empCode", emp.EmpCode);
                    cmd.Parameters.AddWithValue("@empName", emp.EmpName);
                    cmd.Parameters.AddWithValue("@empAddress", emp.EmpAddress);
                    cmd.Parameters.AddWithValue("@empDOJ", emp.EmpDOJ);
                    cmd.Parameters.AddWithValue("@empSalary", emp.EmpSalary);
                    Con.Open();
                    cmd.ExecuteNonQuery();
                    Con.Close();
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
    
    
    
    
    ServiceClient client=new ServiceClient();
    
     List<Employee>empList= client.ReadXML("C:\\Employees.XML");
    client.SaveEmployee(empList);
    
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 27, 2013 1:10 AM

All replies

  • User1216489373 posted
           /// <summary>
        /// Data Contract in Business Logic which type of data u have (I have used Employee as example)
        /// </summary>
        [DataContract]
        public class Employee
        {
            [DataMember]
            public int EmpCode { get; set; }
            [DataMember]
            public string EmpName { get; set; }
            [DataMember]
            public string EmpAddress { get; set; }
            [DataMember]
            public DateTime EmpDOJ { get; set; }
            [DataMember]
            public int EmpSalary { get; set; }
    
        }
    
    // Lets see my xml file content
    
    <?xml version="1.0"?>
    -<Employees> -
    <Employee>
       <Code>1</Code>
       <Name>Atma</Name>
       <Address>Gorakhpur</Address>
       <DOJ>12/2/2012</DOJ>
       <Salary>1000</Salary>
     </Employee>
    <Employee>
       <Code>2</Code>
       <Name>Ashish</Name>
       <Address>Kanpur</Address>
      <DOJ>11/5/2012</DOJ>
      <Salary>2000</Salary>
     </Employee>
    <Employee>
      <Code>3</Code>
      <Name>Rahul</Name>
      <Address>Delhi</Address>
      <DOJ>3/12/2012</DOJ>
      <Salary>3000</Salary>
     </Employee>
    </Employees>
    
    
    
    
    
             /// <summary>
            /// Reading XML File and storing data in to List
            /// </summary>
            /// <param name="filePath"></param>
            /// <returns></returns>
              [OperationContract]
            public  List<Employee> ReadXML(string filePath)
            {
                List<Employee> lstEmp = new List<Employee>();
                XmlDocument xDoc = new XmlDocument();
               // xDoc.Load("Your Xml File Path");
                xDoc.Load(filePath);
                XmlNodeList xEmpList = xDoc.GetElementsByTagName("Employee");
                foreach (XmlNode xNode in xEmpList)
                {
                    Employee emp = new Employee();
                    emp.EmpCode = Convert.ToInt32(xNode["Code"].InnerText);
                    emp.EmpName = xNode["Name"].InnerText;
                    emp.EmpAddress = xNode["Address"].InnerText;
                    emp.EmpDOJ =Convert.ToDateTime( xNode["DOJ"].InnerText);
                    emp.EmpSalary = Convert.ToInt32(xNode["Salary"].InnerText);
                    lstEmp.Add(emp);
    
                }
                return lstEmp;
            }
    
    
    
            /// <summary>
            /// Utilising the List retured by ReadXMl method for storing in database
            /// </summary>
            /// <param name="lstEmployee"></param>
            /// <returns></returns>
    
             [OperationContract]
            public int SaveEmployee(List<Employee> lstEmployee)
            {
                int isCommited = 0;
                try
                {
                    foreach (Employee emp in lstEmployee)
                    {
                        InsertEmployee(emp);
                         
                    }
    
                    isCommited = 1;
                    return isCommited;
                }
                catch (Exception)
                {
                    isCommited = 0;
                    return isCommited;
                    throw;
                }
            }
    
    
    
             /// <summary>
            /// Method which u would define in DAL
            /// </summary>
            /// <param name="emp"></param>
            public void InsertEmployee(Employee emp)
            {
                try
                {
                    SqlConnection Con = new SqlConnection("Your Connection String");
                    SqlCommand cmd = new SqlCommand("Usp_Insert_Employee", Con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empCode", emp.EmpCode);
                    cmd.Parameters.AddWithValue("@empName", emp.EmpName);
                    cmd.Parameters.AddWithValue("@empAddress", emp.EmpAddress);
                    cmd.Parameters.AddWithValue("@empDOJ", emp.EmpDOJ);
                    cmd.Parameters.AddWithValue("@empSalary", emp.EmpSalary);
                    Con.Open();
                    cmd.ExecuteNonQuery();
                    Con.Close();
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
    
    
    
    
    ServiceClient client=new ServiceClient();
    
     List<Employee>empList= client.ReadXML("C:\\Employees.XML");
    client.SaveEmployee(empList);
    
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 27, 2013 1:10 AM
  • User-1000095884 posted

    Hi,

    I'm glad to hear that you resolve this issue and thanks for sharing solution here, it will be very beneficial for other community members who have similar questions.

    Best Regards.

    Wednesday, February 27, 2013 1:59 AM
  • User1216489373 posted

    Check my Code

    //DAO
    
    namespace QDAO
    {
        public class UploadDAO:IUpload
        {
            string strConnection = DBUtil.getConnectionString();
    
            #region IUpload Members
    
            //public List<AddTravelDetails> ReadXML(DateTime StartTime, string CCNo, string CreditCardType, string CCExpDate, string AuthorizationCode, string Secure, string AvsIndicator, string IPViking, DateTime EDeliveryDate, string CHolderName, string Email, string Phone, string Mobile, string Street, string PostCode, string City, string State, string CountryCode, string IPAddress, string PCurrency, string PAmount, string BReference, DateTime BookingDate, string TName, string TNumber, string TClass, string TBerth, string TOStation, string TDestination, int Adults, int Children, string TicketType, string TUserId, int Quota, string TravelAgency, DateTime DeDate, DateTime ArDate, string Ctimezone, double COffset, double PortOffset, string PortTimezone, string MerchantId, string MerchantCC, string ClientRefId, string RecipientSame, string Shipsame, string ShipPhone, string ShipMobile, string ShipEmail, string ShipStreet, string ShipPostCode, string ShipCity, string ShipState, string ShipCountry, string IspCountry, string CountryTimeZone)
            public List<AddTravelDetails> ReadXML(XmlDocument xDoc)
            {
                List<AddTravelDetails> lstTravel = new List<AddTravelDetails>();
                // Only for testing
                xDoc = new XmlDocument();
                string filepath = @"D:\Travel.xml";
                // Only for testing
                xDoc.Load(filepath);
                XmlNodeList xTrvlList = xDoc.GetElementsByTagName("ExcelInfo");
                foreach (XmlNode xNode in xTrvlList)
                {
                    AddTravelDetails TrvlDetails = new AddTravelDetails();
                    TrvlDetails.StartTime = Convert.ToDateTime(xNode["eTransactionStartTime"].InnerText);
                    TrvlDetails.CCNo = xNode["eCustomerCardNumber"].InnerText;
                    TrvlDetails.CreditCardType = xNode["eCardType"].InnerText;
                    lstTravel.Add(TrvlDetails);
                }
    
                return lstTravel;
            }
    
            #endregion
    
            #region IUpload Members
    
            public int SaveTransaction(List<AddTravelDetails> lstTravel)
            {
                int isCommited = 0;
                try
                {
                    foreach (AddTravelDetails AddTrvlDetail in lstTravel)
                    {
                        InsertTrvlDetails(AddTrvlDetail);
    
                    }
    
                    isCommited = 1;
                    return isCommited;
                }
                catch (Exception)
                {
                    isCommited = 0;
                    return isCommited;
                    throw;
                }
    
            }
    
    
            public void InsertTrvlDetails(AddTravelDetails AddTrvlDetail)
            {
                try
                {
                    SqlConnection con = new SqlConnection(strConnection);
                    con.Open();
                    SqlCommand cmd = new SqlCommand("uspTxAddTravel", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PT_START_TIME", AddTrvlDetail.StartTime);
                    cmd.Parameters.AddWithValue("@PT_CC_NUMBER", AddTrvlDetail.CCNo);
                    cmd.Parameters.AddWithValue("@PT_CC_TYPE", AddTrvlDetail.CreditCardType);
    
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                catch (Exception)
                {
    
                    throw;
                }
            }
    
    
            #endregion
        }
    }
    
    

     

    //Business Entities
    
    namespace BusinessEntities
    {
    
        [DataContract]
        public class AddTravelDetails
        {
            DateTime startTime;
            string sCCNo, sCreditCardType;
    
    
            [DataMember]
            public DateTime StartTime
            {
                get { return startTime; }
                set { startTime = value; }
            }
            [DataMember]
            public string CCNo
            {
                get { return sCCNo; }
                set { sCCNo = value; }
            }
            [DataMember]
            public string CreditCardType 
            {
                get { return sCreditCardType; }
                set { sCreditCardType = value; } 
            }
    
    }
    }
    

     

        [ServiceContract]
        public interface IUpload
        {
    
            [OperationContract, XmlSerializerFormat]
            List<AddTravelDetails> ReadXML(XmlDocument xDoc);
    
            [OperationContract]
            int SaveTransaction(List<AddTravelDetails> lstTravel);
    
         }

     

    //Service.cs
    namespace WCFService
    {
        
        public class Upload : IUpload
        {
    
    
    
            public List<AddTravelDetails> ReadXML(XmlDocument xDoc)
            {
                return new List<AddTravelDetails>();
            }
    
    
    
            public int SaveTransaction(List<AddTravelDetails> lstTravel)
            {
                return 1;
            }
    
    
        }
    }





    Wednesday, February 27, 2013 5:01 AM