WCF Service to load data into the SQL Table

Hi,

I have an xml with the name "events.xml". This file need to be send as a parameter to wcf service. The respective data should be loaded into the multiple tables in the SQL server. (Table definitins in the file "events.sql")

The following is the requirement:

1. Address1, Address2, City, State and zip should be added into the "Event" table from the XML if it doesn't exist
2. Label should be mapped to Description, Lat to Latitude,  Lng to Longitude in the "Location" table for the Event Address (LocationType value is always "event")
3. scheduletype value is "event schedule"  and Description value is "event description" always in the "Schedule" table for the event addresss
4.Subject value is "Schedule subject", Label should be mapped to "Place", <date> values should be mapped to "Startdatetime" in the "ScheduleItem" table.
5.Service should return the count of new records created and count of existing records in each table.

I need help in writing WCF service. Can someone help me on this?
Events.xml
events.sql
mynetquestionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
WCF will send or receive data and is not the correct method for what you want to do with an existing .xml file  one would typically use SSIS to do what you want.
mynetquestionsAuthor Commented:
Hi David,

Basic intention to write a WCF service for the above requirement is, we want to implement one windows application to end user by consuming the above service when they load XML file.

I don't have any SSIS knowledge. Can you please help me and provide the steps to create SSIS package for the above requirement?
David Johnson, CD, MVPOwnerCommented:
Windows Communication Foundation (WCF) is a framework for building service-oriented applications. Using WCF, you can send data as asynchronous messages from one service endpoint to another. A service endpoint can be part of a continuously available service hosted by IIS, or it can be a service hosted in an application.http://bit.ly/1IDLCVD
What you want is to create a WPF (Windows Presentation Foundation and you can take the xaml and code behind from it and put it in a web application
MlandaTCommented:
1. Make a service that receives a string. This string will be the XML data. (It should be trivial to find examples of this online... google). If using Visual Studio, when you create a WCF project, it already creates the basic infrastructure that you need for a WCF service. Just rename a few things there and you're good to go.
2. The service will pass this data to the stored procedure which you will create in the next step.
3. Create a stored procedure as below. This stored procedure will receive the XML data and do the inserts into the various tables.

Example stored procedure:I have not made actual INSERTs, this just shows you how to go about parsing the xml input. I have created stubs for the INSERTs
create proc Insert_Event_From_XML (@input xml, @num_of_rec int output)
as
begin

	--insert into dbo.Event(Id, Address1, Address2, City, [State], [ZipCode])
	select
		id = cast(XCol.query('text()') as varchar),
		address1 = XCol.value('address1[1]','varchar(25)'),
		address2 = XCol.value('address2[1]','varchar(25)'),
		city = XCol.value('city[1]','varchar(25)'),
		state = XCol.value('state[1]','varchar(25)'),
		zip = XCol.value('zip[1]','varchar(25)')
	from @input.nodes('/markers/marker') AS XTbl(XCol)
	select @num_of_rec = @num_of_rec + @@ROWCOUNT

	--insert into dbo.Location(Id, LocationLat, LocationLng, [Description], LocationType)
	select
		id = cast(XCol.query('text()') as varchar),
		LocationLat = XCol.value('lat[1]','varchar(25)'),
		LocationLng = XCol.value('lng[1]','varchar(25)'),
		[Description] = XCol.value('label[1]','varchar(25)'),
		LocationType = 'Event'
	from @input.nodes('/markers/marker') AS XTbl(XCol)
	select @num_of_rec = @num_of_rec + @@ROWCOUNT

	--insert into ScheduleItem(fkEventId, ScheduleType, StartDateTime)
	select
		id = cast(XCol.query('text()') as varchar),
		ScheduleType = 'event schedule',
		EventDate = EventDate.value('.','varchar(25)')
	from @input.nodes('/markers/marker') AS XTbl(XCol)
		cross apply XTbl.XCol.nodes('./calendar/date') AS Schedule(EventDate)

end

Open in new window


Example code for calling the stored procedure. This would be in your WCF code. I have not tested this code, but it should work with a little tweaking. It gives you a very good idea how to go about your WCF method.
public int ProcessXmlInput(string input)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("Insert_Event_From_XML", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@input", input);
            cmd.Parameters.Add("@num_of_rec", SqlDbType.Int);
            cmd.Parameters["@num_of_rec"].Direction = ParameterDirection.Output;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            
             return cmd.Parameters["@num_of_rec"].Value;

        }
    }
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mynetquestionsAuthor Commented:
Hi MlandaT,

Thanks for your solution. I would try it and let you know my experience.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WCF

From novice to tech pro — start learning today.