I have renamed the Interface & implanted class as shown above. First I made the IEmployeeService & defined OperationContract, DataContract & DataMember. Now for the newbie, a question will come into the mind what are these hacks, so let me give a brief overview of these terminologies:
http://msdn.microsoft.com/en-us/library/ms735119%28VS.90%29.aspx
Then I have to remove the code available with in EmployeeService.cs by default & to implement IEmployeeService.cs I will do as follows:
As I need to get data from AdventureWorks DB, do definitely I need my application to talk to SQL Server, so I follow it like this: I choose properties of EmployeeWCFServiceLibraryProject & then choose settings. As there is no setting file is there so I have choose to create one option.
I gave it name AdventrueWorksConnectionString & choose type as connection string. From the value section, I choose my DB.
Now it is time to do a little bit of coding. I have to implement the ListEmployee method of IEmployeeService Interface to fetch data from Employee table of AdventureWorks DB.
Moreover I have to add System.Data & System.Data.SqlClient namespace to talk to DB. Following code snipets I used to fetch data for employee:
public ListEmployee> ListEmployee()
{
employees = new ListEmployee>();
using (var cnn = new SqlConnection(
Properties.Settings.Default.AdventureWorksConnectionString))
{
using (var cmd = new SqlCommand(
"Select EmployeeID, Title " +
"From Employee Order by EmployeeID", cnn))
{
cnn.Open();
using (SqlDataReader EmployeeReader =
cmd.ExecuteReader())
{
while (EmployeeReader.Read())
{
employee = new Employee();
employee.EmployeeID=
EmployeeReader.GetString(0);
employee.EmployeeTitle =
EmployeeReader.GetString(1);
employees.Add(employee);
}
}
}
}
return employees;
}
Then I use following code to fetch contact details for each employee:
public EmployeeDetail GetEmployeeDetails(string contactID)
{
employeeDetail = new EmployeeDetail();
using (var cnn = new SqlConnection(
Properties.Settings.Default.AdventureWorksConnectionString))
{
using (var cmd = new SqlCommand(
"select ContactID, FirstName, " +
"LastName, EmailAddress from Person.Contact " +
"where ContactID= @contactID order by ContactID", cnn))
{
cmd.Parameters.Add(new SqlParameter("@contactID", contactID));
cnn.Open();
using (SqlDataReader ContactReader =
cmd.ExecuteReader())
{
while (ContactReader.Read())
{
employeeDetail.ContactID =
ContactReader.GetString(0);
employeeDetail.EmployeeFirstName =
ContactReader.GetString(1);
employeeDetail.EmployeeLastName =
ContactReader.GetString(2);
employeeDetail.EmployeeEmail =
ContactReader.GetString(3);
}
}
}
}
return employeeDetail;
}
Then finally I implement the SaveChanges method as follows:
public bool SaveChanges(string contactID, string employeeFirstName, string employeeLastName,
string employeeEmail)
{
using (var cnn = new SqlConnection(
Properties.Settings.Default.AdventureWorksConnectionString))
{
using (var cmd = new SqlCommand(
"update Person.Contact " +
"set FirstName= @FirstName, " +
"LastName= @LastName, " +
"EmailAddress= @EmailAddress " +
"where ConatctID= @conatctID", cnn))
{
cmd.Parameters.Add(new SqlParameter(
"@FirstName", employeeFirstName));
cmd.Parameters.Add(new SqlParameter(
"@LastName", employeeLastName));
cmd.Parameters.Add(new SqlParameter(
"@EmailAddress", employeeEmail));
cmd.Parameters.Add(new SqlParameter(
"@conatctID", contactID));
cnn.Open();
numRowsChanged = (int)cmd.ExecuteNonQuery();
}
}
return (numRowsChanged != 0);
}
Now all set to go, its time to test our WCF Service, If I run the application, wcf service host will host our service & show us wcf test client where we can test our service.
If I click ListEmployee() function & then click invoke, I will get a formatted list of employee table.
Now our WCF Service is ready to be used & can be hosted in IIS, Windows service or WAS.
Last week during a training session, one of trainee raised a simple question. “Is there any
easy method to export data from sql server to notepad file”, b/c that poor fellow has been copy pasting it in the recent past. So I thought might be some more guys are there to find that simple answer. So I follow these simple steps:
As clearly shown in below picture I opened Import & export Wizard
From Choose data source section I choose AdventureWorks db. For the sake of simplicity I don’t change the default attributes.
In choose destination step, I have choosen flat file destination from destination drop down list, give it name C:\HumanResouceShift.txt and checked column names checkbox to get columan names as well.
In specify table copy or query, I choose first option due to make it simple enough for a power use to export the date, else by choosing second option, I can write my own tsql query to extarct data from destination
In next step in the wizard which is to configure flat file destination I choose HumanResources.Shift table.
I can choose from available option to put delimeters within column as well as b/w rows. But in this case I choose default options.
In final step which is Save & Run package, I choose run immediately. (In future blogs I will wirte in detail about saving it as SSIS pacakge & its protection level.
If everything goes well execution successful then I should have a file at my C: drive which I choose as my destination
