SQL Server Jobs

In this post we are going to set up a SQL Job to monitor daily the sales table and produce statistical data in other table

We have the next SQL schema

And the next Stored proc to generate statistics

     
CREATE PROCEDURE P_GENERATE_STATISTICS
AS BEGIN

    DECLARE @HighestSale INT
    DECLARE @MostSoldProd INT
    DECLARE @SalesAverage MONEY
    
    SET @HighestSale =
        (SELECT TOP 1 IdSale
         FROM B_SALES WHERE CAST(SALEDATE AS DATE) = CAST({fn NOW()} AS DATE)
         ORDER BY Price DESC)
    
    SET @MostSoldProd =
        (SELECT TOP 1 IdProduct
         FROM B_SALES
         WHERE CAST(SALEDATE AS DATE) = CAST({fn NOW()} AS DATE)
         GROUP BY IdProduct
         ORDER BY COUNT(IdProduct) DESC)
        
    SET @SalesAverage =
        (SELECT AVG(Price)
         FROM B_SALES
         WHERE CAST(SALEDATE AS DATE) = CAST({fn NOW()} AS DATE))

    INSERT B_STATISTICS
    VALUES (DEFAULT, @HighestSale, @MostSoldProd, @SalesAverage)

END
GO

The first step to set up the Job is run the New Job Dialog at the SQL Server Agent folder. The main component of a job definition is the unique name that will be used to refer to the job

Go to the Steps tab and click New

In the Advanced tab, stablish the actions to perform in case of success and failure

In the Schedules tab, set up the triggering properties. In our case it's a recurring schedule type, with daily frequency

As a last step, we are going to set up a Notification to be sent when the Job completes with success, writing the execution information to Windows Application Event Log

The result of the Job is the next

The notification sent to Event Log can be checked through Event Viewer

<METHOD SOFTWARE © 2012>

DLLs

As a first post, we are going to treat a theme which can save us a lot of time when we have different applications to execute the same functionality, I´m talking about dynamic linking libraries, which also make our code redistributable to any other language

As a brief example, we will develop a Class Library for calculating Biorhytms

Byorhytm theory states that our lives are affected by three primary cycles: Physical (23 days), Emotional (28 days) and Intellectual (33 days) and the cycle starts at the midpoint at birth

     
	public class Biorhytms
    {
        public static double[] getBiorhytms(DateTime birthDate)
        {
            double[] rhytms = new double[3];

            double daysOnEarth = ((TimeSpan)(DateTime.Now - birthDate)).TotalDays;

            double PhysicalCycle = ((daysOnEarth/23) - ((int)daysOnEarth/23))*23;
            double EmotionalCycle = ((daysOnEarth/28) - ((int)daysOnEarth / 28))*28;
            double IntellectualCycle = ((daysOnEarth/33) - ((int)daysOnEarth/33)*33;

            rhytms.SetValue(PhysicalCycle, 0);
            rhytms.SetValue(EmotionalCycle, 1);
            rhytms.SetValue(IntellectualCycle, 2);

            return rhytms;
        }
    }
    

When we have coded our library, we should sign it with strong name key and register it in the GAC, for this simply drop it into the c:\windows\assembly folder and it will be added automatically to cache, or if your scenario needs more requisites to add your assembly to the global cache, you can use the gacutil.exe utility, type gacutil for display all options

The process is the next

Keep in mind that we have added to the GAC a .NET 4.0 assembly, so it will be displayed in the folder c:\windows\microsoft.net\assembly, instead of c:\windows\assembly . If you want to make it accessible from your .NET tab for comfort, you can distribute your assembly to the next folder, depending on the version of your .NET framework, 4.0 in this case

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client

The next step is adding our assembly to other project, written for example in Visual Basic.
A DLL is linked to your program at run time, so it will be part of the whole of our source code when we deploy our application

     
Imports Biorhytms.Biorhytms

Module Module1
    Sub Main()

        Dim bios As Double()
        bios = getBiorhytms(New Date(1999, 7, 4))

        For Each bio As Double In bios

            Console.WriteLine(String.Format("{0:#.###}", bio))

        Next

        Console.ReadKey()

    End Sub

End Module

We have exposed the case in you are the distributor of your code packed in a dll, but if you are the library consumer, and want to know about the guts of the dll you are implementing, you can use tools like Reflector .NET, and decompile it for browsing its source code

<METHOD SOFTWARE © 2012>

LINQ Join Operations

In this post we make a review of linq join operators, to perform different kind of join on two sets of related data

All data in this example is from Northwind database, distributed as example database by Microsoft, you can download it here: Northwind database for SQL Server

First, we add a set of LINQ to SQL Classes, with the next result

And we add a method to retrieve our sets of data

     
        using (NorthwindDataContext nwContext = new NorthwindDataContext())
        {
            customerList = (from cust in nwContext.Customers
                            select cust).ToList<Customer>();

            orderList = (from ord in nwContext.Orders
                            select ord).ToList<Order>();

            productList = (from prod in nwContext.Products
                            select prod).ToList<Product>();

            supplierList = (from supp in nwContext.Suppliers
                            select supp).ToList<Supplier>();

            categoryList = (from cat in nwContext.Categories
                            select cat).ToList<Category>();
        }

The first join operation we perform is a simple inner join of equivalence that produces a result ser of Customers that has a matching element in Orders

     
        var customer_orders = from cust in customerList
                              join ord in orderList 
                              on cust.CustomerID equals ord.CustomerID
                              orderby cust.ContactName ascending
                              select new { cust.ContactName, ord.OrderID };

        foreach (var cust_ord in customer_orders)
            Console.WriteLine("Customer = {0}, Order Id = {1}", 
                cust_ord.ContactName, cust_ord.OrderID);
                

The result of this query is the next

The second query is an inner join that produces a sequence of objects, each of which has a key and an inner sequence of all matching elements, we get this result set through the keyword into

     
        var customer_orders = from cust in customerList
                              join ord in orderList on cust.CustomerID equals ord.CustomerID
                              into cs
                              orderby cust.ContactName ascending
                              select new { Key = cust.CustomerID, Items = cs };

        foreach (var cust in customer_orders)
        {
            Console.WriteLine("Customer ID = {0} :", cust.Key);
            foreach (var order in cust.Items)
                Console.WriteLine(" - Order: {0}, {1}", order.OrderDate, order.OrderID);
        }
        

You can also select elements from the set produced by the into clause

     
        var prodByCategory = from cat in categoryList
                             join prod in productList 
                             on cat.CategoryID equals prod.CategoryID 
                             into ps
                             from p in ps
                             select new { CategoryName = cat.CategoryName, p.ProductName };

        foreach (var item in prodByCategory)
            Console.WriteLine("Category = {0}. Product Name = {1}", 
                item.CategoryName, item.ProductName);
                

With the next query, we perform a left outer join to produce a result set that includes all the left hand side elements at least once, even if they don't match any right side element

DefaultIfEmpty preserves left-hand elements that have no matches on the right side

     
        var productSupps = from prod in productList
                           join sup in supplierList on prod.SupplierID equals sup.SupplierID 
                           into ps
                           from p in ps.DefaultIfEmpty()
                           orderby prod.ProductName
                           select new
                           {
                               PName = prod.ProductName,
                               SName = p == null ? "--- No suppliers ---" : p.CompanyName
                           };

        foreach (var product in productSupps)
            Console.WriteLine("Product mame = {0} . Supplier name = {1}", 
                product.PName, product.SName);
                

<METHOD SOFTWARE © 2012>

WWF Workflow Activities

In this sample, we are going to develop a workflow with a custom code activity, this activity will read an xml file and return a list of Customers to be updated / inserted in our database

The first step is add a Sequence Activity in the designer. This activity enables you to construct a list of other activities, and when executed it will start with the first child activity and execute each child in turn

We have defined a class named Customer, with attributes corresponding to data, and three methods: one to check if exists in the database, one to update, and one to insert

Now we add a Code Activity to read our xml, and return a List of Customers. We call it ReadCustomers.cs

We also add a Code Activity named UpdateCustomer, and another one named InsertCustomer

The code for ReadCustomers activity is as follows

     
public class ReadCustomers : CodeActivity<List<Customer>>
{
    protected override List<Customer> Execute(CodeActivityContext context)
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.Load(@"c:\customers.xml");

        XmlNodeList customers = xDoc.GetElementsByTagName("Customer");

        List<Customer> listCustomers = new List<Customer>();
        foreach (XmlElement customer in customers)
        {
            listCustomers.Add(new Customer()
            {
                CustomerId = new Guid(customer.GetElementsByTagName("Id")[0].InnerText),
                FirstName = customer.GetElementsByTagName("Firstname")[0].InnerText,
                LastName = customer.GetElementsByTagName("Lastname")[0].InnerText,
                Telephone = customer.GetElementsByTagName("Telephone")[0].InnerText,
                Email = customer.GetElementsByTagName("Email")[0].InnerText
            });
        }

        return listCustomers;
    }
}

The next is the code for update

     
public sealed class UpdateCustomer : CodeActivity
{
    public InArgument<Customer> Customer { get; set; }

    protected override void Execute(CodeActivityContext context)
    {
        Customer customer = context.GetValue(this.Customer);
        customer.Update();
        Console.WriteLine("Customer " + customer.FirstName + " has been updated");
    }
}

And for insert

     
public sealed class InsertCustomer : CodeActivity
{
    public InArgument<Customer> Customer { get; set; }

    protected override void Execute(CodeActivityContext context)
    {
        Customer customer = context.GetValue(this.Customer);
        customer.Insert();
        Console.WriteLine("Customer " + customer.FirstName + " has been inserted");
    }
}

Now we have defined our Code Activities, we will proceed to complete our workflow diagram

First we drag the ReadCustomers activity into our sequence

Now we have to declare a variable with Sequence scope to take the return value of the activity. We select List type, and as element for the List we select our custom Customer type

At this point, we have implemented our activity to retrieve the list of Customers from the xml, so now we add the next elements to the diagram

- Foreach activity
- If activity
- Our custom activities in each branch of the if activity

The Foreach activity will iterates each Customer in the List, and the If activity will determines the Code activity to execute

With a correct execution, this is the result

<METHOD SOFTWARE © 2012>

Calling WCF Async

When our applications call a web service, either from desktop apps or web sites, we have to keep in mind its response times

There is a mechanism for calling the web service asynchronously. Let's take the example exposed in this link, WCF Services, and configure the service reference to generate asynchronous operations

There are two different ways to achieve the process

With the next code, we are assigning a handler to the GetElementsCompleted event, so the application will work on other tasks while is waiting for the event triggers

     
private void btnGetElements_Click(object sender, EventArgs e)
{
    try
    {
        client = new ChemicalServiceClient();
        client.GetElementsCompleted += 
         new EventHandler<GetElementsCompletedEventArgs>(client_GetElementsCompleted);
        client.GetElementsAsync();
    }
    catch (FaultException<AccessFault> ex)
    {
        lblResult.Text = ex.Message + ex.Detail.ExceptionType + ex.Detail.StackTrace;
    }
}

void client_GetElementsCompleted(object sender, GetElementsCompletedEventArgs e)
{
    dgvElements.DataSource = e.Result.ToList();
    lblResult.Text = e.Result.Length.ToString() + " elements were found";
}

The second way is an asynchronous call itself, with the implementation of a callback function, it generates a separated thread to manage the call

The code is as follows

     
private void btnInsertElement_Click(object sender, EventArgs e)
{
    try
    {
        client = new ChemicalServiceClient();
        Element elemToInsert = new Element()
        {
            AtomicSymbol = txtAtomicSymbol.Text,
            Name = txtName.Text,
            LatinName = txtLatinName.Text
        };

        client.BeginInsertElement(elemToInsert, InsertAsyncCallBack, null);
    }
    catch (FaultException<AccessFault> ex)
    {
        lblResult.Text = ex.Message + ex.Detail.ExceptionType + ex.Detail.StackTrace;
    }
}

private void InsertAsyncCallBack(IAsyncResult asyncResult)
{
    if (asyncResult.IsCompleted)
    {
        string elemKey = client.EndInsertElement(asyncResult);
        lblResult.Text = elemKey + " was added correctly";
    }
}

This second way is the recommended for web scenarios, because the site will renders immediatly after the function call, instead of wait for the event fires


<METHOD SOFTWARE © 2012>

T-SQL Case Sensitive Queries

During the process of installation of SQL Server, we have to set up the collation options. This step will determine the result of every query we launch

In the case that we had configured a case insensitive collation, we must be aware of many cases require a case sensitive query, for example a user/key value

There are four ways for achieving case sensitive queries despite of the configured collation

1. We can rebuild our databases specifying the new collation, so we get the change at server level

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

For more information, see msdn documentation --> MSDN: Set or Change the Server Collation

2. We can set up the collation only for our database, so we get the change at database level

To retrieve the actual collation of our database, we can launch the next query

     
    SELECT name, collation_name 
    FROM sys.databases
    WHERE name = 'Business'
    

To get a list of the available collations, we launch the next query, this query will also returns a description of the conditions for the collation

    SELECT * FROM ::fn_helpcollations()

The command to change the collation of our database is the next, where _CS_ indicates case sensitive

    ALTER DATABASE Business COLLATE Modern_Spanish_CS_AS   

3. In the same way as prior, we can change the collation at column level

    ALTER TABLE B_CUSTOMERS 
    ALTER COLUMN CustomerKey varchar(20)COLLATE Modern_Spanish_CS_AS NOT NULL

4. And finally, if we don't have access to release these kind of changes, we can query the column casting the varchar datatype to varbinary, so we will compare the varchar values through its binary code

     
CREATE PROCEDURE LOGIN_CUSTOMER
    (@nick VARCHAR(20), @customerkey VARCHAR(20), @res BIT OUTPUT)
AS BEGIN

    IF EXISTS 
        (SELECT 1 FROM B_CUSTOMERS
         WHERE Nick = @nick
         AND    
            CAST(CustomerKey AS VARBINARY(20)) 
             = 
            CAST(@customerkey AS VARBINARY(20)))

        SET @res = 1                
        
    ELSE    
        SET @res = 0

    RETURN @res
END
GO 

With this option, we can be sure that the query will perform correctly


<METHOD SOFTWARE © 2012>

Data Relations in XSD Schema

The most common way to display our data sets is in two grids in mode master-detail

In this example we will focus on this set of data of a company represented by the next diagram

We will display related data between Employees and Projects in two different grids, synchronizing both of them

To achieve this we will create a view with the data corresponding to the Projects of the Employee, and two stored procs, one for retrieve data from our Employees table, and the other one from our Projects view

    CREATE VIEW VIEW_PROJECTS AS
        SELECT E.IdEmployee, P.Name AS Project, P.Description, 
               EP.AssignmentDate, P.DueDate, C.Name AS Customer 
        FROM C_EMPLOYEES E 
             JOIN C_EMPLOYEES_PROJECTS EP 
               ON E.IdEmployee = EP.IdEmployee
             JOIN C_PROJECTS P 
               ON EP.IdProject = P.IdProject 
             JOIN C_CUSTOMERS C
               ON P.IdCustomer = C.IdCustomer
    GO

    CREATE PROCEDURE SELECT_EMPLOYEES
    AS BEGIN
        SELECT * FROM C_EMPLOYEES
        ORDER BY FirstName ASC, LastName ASC
    END
    GO

    CREATE PROCEDURE SELECT_PROJECT_DATA
    AS BEGIN
        SELECT * FROM VIEW_PROJECTS
    END
    GO

Now we will generate the XSD (XML Schema Definition) of our DataSet

The process includes setting up the connection, and adding two TableAdapters from the Toolbox

We include our database objects

And now we add the first TableAdapter, for Employees

We do the same process for adding the View of Projects, choosing the stored procedure SELECT_PROJECT_DATA

The last step is set up the Relation between tables

And this is the schema once complete

The XML Definition of the Relation in our Schema is the next

  <xs:annotation>
    <xs:appinfo>
      <msdata:Relationship 
   name="EMPLOYEES_PROJECTS_Relation" 
   msdata:parent="SELECT_EMPLOYEES" 
   msdata:child="SELECT_PROJECT_DATA" 
   msdata:parentkey="IdEmployee" 
   msdata:childkey="IdEmployee" 
   msprop:Generator_UserChildTable="SELECT_PROJECT_DATA" 
   msprop:Generator_ChildPropName="GetSELECT_PROJECT_DATARows" 
   msprop:Generator_ParentPropName="SELECT_EMPLOYEESRow" 
   msprop:Generator_UserRelationName="EMPLOYEES_PROJECTS_Relation" 
   msprop:Generator_RelationVarName="relationEMPLOYEES_PROJECTS_Relation" 
   msprop:Generator_UserParentTable="SELECT_EMPLOYEES" 
   />
    </xs:appinfo>
  </xs:annotation>

Finally, in our code, we will program the next method to initialize the grids

    private void InitializeDataSet()
    {
        try
        {
            DataSetCompany dsCompany = new DataSetCompany();                

            SELECT_EMPLOYEESTableAdapter daEmployees = new SELECT_EMPLOYEESTableAdapter();
            daEmployees.Fill(dsCompany.SELECT_EMPLOYEES);

            SELECT_PROJECT_DATATableAdapter daProjects = new SELECT_PROJECT_DATATableAdapter();
            daProjects.Fill(dsCompany.SELECT_PROJECT_DATA);

            gridEmployees.DataSource = dsCompany;
            gridEmployees.DataMember = "SELECT_EMPLOYEES";

            gridProjects.DataSource = dsCompany;
            gridProjects.DataMember = "SELECT_EMPLOYEES.EMPLOYEES_PROJECTS_Relation";

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

The result is as follows

<METHOD SOFTWARE © 2012>

LINQ and Lambda Expressions

LINQ is a powerful tool, which combined with lambda expressions becomes in many cases the best way to querying our data sets

In this example we have a database with data of a business and we have defined the next schema of LINQ to SQL Classes

Now we have fully typed our classes

These are the variables and methods exposed in this example, the approach of this example is fill the data sets with Linq queries, and since that moment make different operations on the obtained lists with lambda expressions, instead of keep querying the data sets through the context

     
    static List<B_CUSTOMER> AllCustomers;
    static List<B_SALE> Sales;
    static List<B_PRODUCT> Products;
    static List<B_PROVIDER> Providers;
    static List<B_PROVIDERS_PRODUCT> ProvidersProducts;
    static List<B_SALE> HighestSales;

    static void Main(string[] args)
    {
        // will query the context
        GetBusinessData();
        // will query the collections
        GetPurchases(); 
        GetCustomersWithoutSales(); 
        GetHighestSales();
        GetProvidersForHighestSales();
        Console.ReadKey();
    }
    

The first method we implement is GetBusinessData, which fills our typed lists

     
    using (BusinessClassesDataContext context = new BusinessClassesDataContext())
    {
        AllCustomers = (from cust in context.B_CUSTOMERs
                        orderby cust.FirstName ascending, cust.LastName ascending
                        select cust).ToList<B_CUSTOMER>();

        Sales = (from sale in context.B_SALEs
                    orderby sale.Cuantity descending
                    select sale).ToList<B_SALE>();

        Products = (from prod in context.B_PRODUCTs
                    orderby prod.Description ascending
                    select prod).ToList<B_PRODUCT>();

        Providers = (from prov in context.B_PROVIDERs
                        orderby prov.Name
                        select prov).ToList<B_PROVIDER>();

        ProvidersProducts = (from provprod in context.B_PROVIDERS_PRODUCTs
                                select provprod).ToList<B_PROVIDERS_PRODUCT>();

    }

This is the implementation of the GetPurchases method:
We select from the set AllCustomers, those who are in the Sales set, and after expose them in hierarchy with their purchases

     
    Console.WriteLine("\nPurchases:");

    foreach (B_CUSTOMER customer in Sales.Select(sd => sd.B_CUSTOMER)
        .Distinct())
    {
        Console.WriteLine(customer.FullName + " : ");
        foreach (string purchase in Sales
            .Where(s => s.B_CUSTOMER == customer)
            .Select(sd => sd.B_PRODUCT.Description + " : " +
                sd.Cuantity.ToString() + " unit(s) " +
                sd.Price.ToString() + " $"))
            Console.WriteLine(" - " + purchase);
    }
    

This is the method GetCustomersWithoutSales, which performs a fast set operation to discard from AllCustomers those who don't have made purchases

     
    List<B_CUSTOMER> customersWithoutSales = AllCustomers
        .Except(Sales.Select(sd => sd.B_CUSTOMER).Distinct())
        .ToList<B_CUSTOMER>();

    Console.WriteLine("\nCustomers without sales:");
    foreach (B_CUSTOMER customer in customersWithoutSales)
        Console.WriteLine(" - " + customer.FullName);

The implementation of the method GetHighestSales is as follows, notice that the condition might be dynamic

     
    HighestSales = Sales
        .Where(s => s.Price >= 400)
        .OrderBy(s => s.Price).ToList();

    Console.WriteLine("\nHighest Sales:");
    foreach (B_SALE saleData in HighestSales)
        Console.WriteLine(" - " + saleData.B_CUSTOMER.FullName + " : " +
            saleData.B_PRODUCT.Description + " : " + 
            saleData.Cuantity + " unit(s) " +
            saleData.Price.ToString() + " $");

Finally we have defined the method GetProvidersForHighestSales, which gets those providers which provide of the products with highest sales

     
    Console.WriteLine("\nProviders of products with highest sales:");
    foreach (B_PRODUCT product in HighestSales.Select(ht => ht.B_PRODUCT))
    {
        Console.WriteLine("- Providers of " + product.Description);
        foreach (B_PROVIDER provider in ProvidersProducts
            .Where(pr => product.IdProduct == pr.IdProduct)
            .OrderBy(pr => pr.B_PRODUCT.Description)
            .Select(pr => pr.B_PROVIDER).Distinct())
            Console.WriteLine("   - " + provider.Name);
    }
    

This is the output of our application, by combining LINQ and lambdas, we did not have to make a new Linq instruction for each query, taking adventage of the potential of Generics

<METHOD SOFTWARE © 2012>

WCF Services

When we have to develop a process to be accessed across multiple applications and plattforms, the choice is programming a web service

WCF enables you to encapsulate processes, and expose just the methods and data neccessary to share

In this example we will design a WCF service to filter the access to a database, we will expose two methods and a class

The first step is begin a new WCF Application project

This is the interface of our service, a service dedicated to retrieve and insert data in a chemical elements table, with every Contract defined, plus one for providing information about possible exceptions to the client side

     
namespace Chemistry
{
    [ServiceContract]
    public interface IChemicalService
    {
        [OperationContract]
        [FaultContract(typeof(AccessFault))]
        Element[] GetElements();

        [OperationContract]
        [FaultContract(typeof(AccessFault))]
        string InsertElement(Element element);
    }

    [DataContract]
    public class Element
    {
        [DataMember]
        public string AtomicSymbol { get; set; }

        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public string LatinName { get; set; }
    }

    [DataContract]
    public class AccessFault
    {
        [DataMember]
        public string ExceptionType { get; set; }

        [DataMember]
        public string StackTrace { get; set; }
    }
}

The code file of our service implements this interface, so we implement these methods in it

This is the code for retrieve data, the method GetElements()

     
    public Element[] GetElements()
    {
        try
        {
            SqlCommand retrieveCommand = new SqlCommand()
            {
                Connection = OpenConnection(),
                CommandType = CommandType.StoredProcedure,
                CommandText = "P_SELECT_ELEMENTS",
                CommandTimeout = 20
            };

            SqlDataReader reader = 
                retrieveCommand.ExecuteReader(CommandBehavior.CloseConnection);

            Element[] Elements = new Element[0];

            int index = 0;
            while (reader.Read())
            {
                Array.Resize<Element>(ref Elements, Elements.Length + 1);
                Element element = new Element()
                {
                    AtomicSymbol = reader[0].ToString(),
                    Name = reader[1].ToString(),
                    LatinName = reader[2].ToString()
                };
                Elements[index++] = element;
            }

            return Elements;
        }
        catch (Exception ex)
        {
            AccessFault fault = new AccessFault() 
                { ExceptionType = ex.GetType().ToString(), 
                  StackTrace = ex.StackTrace };
            throw new FaultException<AccessFault>
                (fault, new FaultReason(ex.Message));
        }
    }
    

And this is the method for inserting data

     
    public string InsertElement(Element element)
    {            
        try
        {
            SqlCommand insertCommand = new SqlCommand()
            {
                Connection = OpenConnection(),
                CommandType = CommandType.StoredProcedure,
                CommandText = "P_INSERT_ELEMENT",
                CommandTimeout = 20,
            };

            insertCommand.Parameters.AddRange(new SqlParameter[] {
                new SqlParameter("@asymbol", element.AtomicSymbol),
                new SqlParameter("@name", element.Name),
                new SqlParameter("@latinname", element.LatinName)});

            insertCommand.ExecuteNonQuery();
            insertCommand.Connection.Close();

            return element.AtomicSymbol;
        }
        catch (Exception ex)
        {
            AccessFault fault = new AccessFault() 
                { ExceptionType = ex.GetType().ToString(), 
                  StackTrace = ex.StackTrace };
            throw new FaultException<AccessFault>
                (fault, new FaultReason(ex.Message));
        }
    }
    

Now we will test the io of the service with WCF Test Client tool. Execute the service from Visual Studio and add the Url to WCF Test Client

The next step is deploying the service for its usage from different points. For this we run inetmgr, and add a new web site. Make sure your IIS is configured to allow ASP.NET v4.0 applications. You can set up this configuration with the next prompt command

After this we publish the service

Now we browse our service from IIS HostedChemicalService web site

While configuring the web site and the parameters for the service publication, we can choose any port, minding it's not busy by other application, in our case the deployment Url is

http://localhost:9789/ChemicalService.svc

The final step is calling the service layer for interacting with the database. With this purpose we create a client application, called ChemicalClient, and we add the Service Reference to the project

The code is as follows

     
    private void btnGetElements_Click(object sender, EventArgs e)
    {
        try
        {
            ChemicalServiceClient client = new ChemicalServiceClient();
            Element[] elements = client.GetElements();

            dgvElements.DataSource = elements.ToList();
            lblResult.Text = elements.Length.ToString() + " were found";
        }
        catch (FaultException<AccessFault> ex)
        {
            lblResult.Text = ex.Message + 
                ex.Detail.ExceptionType + ex.Detail.StackTrace;
        }
    }

    private void btnInsertElement_Click(object sender, EventArgs e)
    {
        try
        {
            ChemicalServiceClient client = new ChemicalServiceClient();
            Element elemToInsert = new Element()
            {
                AtomicSymbol = txtAtomicSymbol.Text,
                Name = txtName.Text,
                LatinName = txtLatinName.Text
            };

            string elemKey = client.InsertElement(elemToInsert);
            lblResult.Text = elemKey + " was added correctly";                
        }
        catch (FaultException<AccessFault> ex)
        {
            lblResult.Text = ex.Message + 
                ex.Detail.ExceptionType + ex.Detail.StackTrace;
        }
    }
    

With a correct execution, we get the next result

<METHOD SOFTWARE © 2012>