How to make a query with the where clause of the WCF service when retrieving data from the SQL Server database

advertisements

I am creating a WCF service accessing data from a SQL Server database. Since I am new to this, I am not able to add a where clause in the query. Please tell me how can I retrieve data for some particular parameter. Posting my code below

IServices1.cs:

    [OperationContract]
    [WebInvoke(Method = "GET", BodyStyle = WebMessageBodyStyle.Wrapped,
               UriTemplate = "GetAllCustomers")]
    List<Consumer> GetAllCustomers();

Service.svc.cs:

 namespace JSONWebService
 {
    public class Service1 : IService1
    {
       public List<Consumer> GetAllCustomers()
       {
           NorthwindDataContext dc = new NorthwindDataContext();
           List<Consumer> results = new List<Consumer>();
           Consumer consumer = new Consumer();

           foreach (Output_Master cust in dc.Output_Masters)
           {
               results.Add(new Consumer()
               {
                   Record_ID = cust.Record_ID,
                   MeterCycle = cust.MeterCycle,
                   Agency = cust.Agency,
                   WorkDate = cust.WorkDate
               }

               return results;
           }
      }

Consumer.cs:

namespace JSONWebService
{
    [DataContract]
    public class Consumer
    {
       [DataMember]
       public string Record_ID { get; set; }

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

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

       [DataMember]
       public decimal? WorkDate { get; set; }
    }


Your GetAllCustomers method should be more like this:

public List<Consumer> GetAllCustomers() {
   return
      new NorthwindDataContext()
      .Output_Masters
      .Where(consumer => consumer.SomeParameter == someValue)
      .ToList();
}

This does assume that Output_Masters is a collection of Consumer. You might actually need to be referencing a Customers or Consumers property instead, but it depends on the design of your NorthwindDataContext class.

If NorthwindDataContext implements IDisposable, which it probably does, then the method should actually look more like this:

public List<Consumer> GetAllCustomers() {
   using (var northwind = new NorthwindDataContext())
      return
         northwind
         .Output_Masters
         .Where(consumer => consumer.SomeParameter == someValue)
         .ToList();
}