Retrieve Multiple fields from SQL Server in a web service?

Question

I m testing how to retrieve data from SQL Server in Web Service. I m using SQL Server 2008 R2, asp.net web service application project template in VS 2010.

Let's say I have a table that has 4 columns and w/o any constaints(for the sake of conversation).

• FirstName
• LastName
• Email
• University

I want to be able to get all the values of my SQL table if a user inputs value for FirstName. Later I would change FirstName to NTID or some meaningful column.Right now my web service just returns single value let's say LastName if a user types in FirstName.

Being a new to web services, I m trying to learn as much as I can and would greatly appreciate your time and effort in helping me out. TIA.

Where/how do I make changes at my code below

Here's is my Data helper class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace EmployeeRecs
{
    public class DataHelper
    {
        //create new method to get Employee record based on First Name
        public static string GetEmployee(string firstName)
        {
            string LastName = "";

            //Create Connection
            SqlConnection con = new SqlConnection (@"Data Source=myDBServer;Initial Catalog=MyDataBase;Integrated Security=true;");

            //Sql Command
            SqlCommand cmd = new SqlCommand("Select LastName from Employees where FirstName ='" + firstName.ToUpper() + "'", con);

            //Open Connection
            con.Open();

            //To Read From SQL Server
            SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    LastName = dr["LastName"].ToString();
                }

            //Close Connection
                dr.Close();
                con.Close();

            return LastName;


        }

    }
}

And here's my asmx.cs class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace EmployeeRecs
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

               //Create new web method to get Employee last name
        [WebMethod]
        public string GetEmployee(string firstName)
        {
            return DataHelper.GetEmployee(firstName);

        }
    }
}

Solution

Besides the SQL injection, a few things:

Create a DataContract and create a model for the data you want to return

[DataContract]
public class Employee
{
    [DataMember]
    public int NTID { get; set; }

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

    [DataMember]
    public int FirstName { get; set; }
}

Fill that model with your SQL Query results and return it from your service

    //create new method to get Employee record based on First Name
    public static List<Employee> GetEmployee(string firstName)
    {
        //Create Connection
        SqlConnection con = new SqlConnection (@"Data Source=myDBServer;Initial Catalog=MyDataBase;Integrated Security=true;");

        //Sql Command
        SqlCommand cmd = new SqlCommand("Select NTID, LastName, FirstName from Employees where FirstName ='" + firstName.ToUpper() + "'", con);

        //Open Connection
        con.Open();

        List<Employee> employees = new List<Employee>();

        //To Read From SQL Server
        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            var employee = new Employee { 
                       NTID = dr["NTID"].ToString();
                       LastName = dr["LastName"].ToString();
                       FirstName = dr["FirstName"].ToString();
                    };
            employees.Add(employee);
        }
        //Close Connection
        dr.Close();
        con.Close();
        return employees;
}

Expose it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace EmployeeRecs
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

               //Create new web method to get Employee last name
        [WebMethod]
        public List<Employee> GetEmployee(string firstName)
        {
            return DataHelper.GetEmployee(firstName);

        }
    }
}

Full Code from OP for posterity:

This might be useful to other folks struggling with the same situation. So I m posting my code for the solution: Create a new WCF PRoject in VS 2010, I used .net version 3.5 and selected WCF Service Library under WCF template.

Here's my code under IService1.cs

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Runtime.Serialization; 
using System.ServiceModel; 
using System.Text; 

namespace WcfServiceLibrary1 
{ 
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together. 
    [ServiceContract] 
    public interface IService1 
    { 
        [OperationContract] 
        List<Employee> GetEmployee(string firstName); 


        [OperationContract] 
        CompositeType GetDataUsingDataContract(CompositeType composite); 

        // TODO: Add your service operations here 
    } 


    //Custon Data contract 

    [DataContract] 
    public class Employee 
    { 
        [DataMember] 
        public string FirstName { get; set; } 

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

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

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

    }  



    // Use a data contract as illustrated in the sample below to add composite types to service operations 
    [DataContract] 
    public class CompositeType 
    { 
        bool boolValue = true; 
        string stringValue = "Hello "; 

        [DataMember] 
        public bool BoolValue 
        { 
            get { return boolValue; } 
            set { boolValue = value; } 
        } 

        [DataMember] 
        public string StringValue 
        { 
            get { return stringValue; } 
            set { stringValue = value; } 
        } 
    } 
} 

And here's my code under Service1.cs

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Runtime.Serialization; 
using System.ServiceModel; 
using System.Text; 
using System.Data; 
using System.Data.SqlClient; 

namespace WcfServiceLibrary1 
{ 
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in both code and config file together. 
    public class Service1 : IService1 
    { 
        public List<Employee> GetEmployee(string firstName)  

        { 
            //Create Connection  
            SqlConnection con = new SqlConnection(@"Data Source=gsops4;Initial Catalog=MultiTabDataAnalysis;Integrated Security=true;"); 

            //Sql Command  
            SqlCommand cmd = new SqlCommand("Select LastName, FirstName, Email, University from Employees where FirstName ='" + firstName.ToUpper() + "'", con); 

            //Open Connection  
            con.Open(); 

            List<Employee> employees = new List<Employee>(); 

            //To Read From SQL Server  
            SqlDataReader dr = cmd.ExecuteReader();  


            while (dr.Read())  
        {  
            var employee = new Employee {   

                       FirstName = dr["FirstName"].ToString(),  
                       LastName = dr["LastName"].ToString(), 
                       Email = dr["Email"].ToString(), 
                       University = dr["University"].ToString() 



                    };  
            employees.Add(employee);  
        }  
        //Close Connection  
        dr.Close();  
        con.Close();  
        return employees; 

        } 

        public CompositeType GetDataUsingDataContract(CompositeType composite) 
        { 
            if (composite == null) 
            { 
                throw new ArgumentNullException("composite"); 
            } 
            if (composite.BoolValue) 
            { 
                composite.StringValue += "Suffix"; 
            } 
            return composite; 
        } 
    } 
} 

Comments

[Nemo]

@J Torres-Thanks for your prompt response. Since I m new to the web service stuff, how/where do I start #1 to begin with? TIA.

[Jaime Torres]

Updated with code examples. I didn't even attempt to build this, so I take absolutely 100% credit if it works, and 0% blame if it doesn't ;-)

[Nemo]

@J Torres- Thanks. I m using .NET 3.5. I think [Data contract] is WCF based on 4.0, which I m not familiar with. So creating [DataContract] and [Data member] is giving me error "The type or namespace name 'DataContract'couldn't be found.. Are you missing a using directive or ref to assembly... Any solution to my scenario please?

[Jaime Torres]

It's available in 3.5. Pro-tip: Hit CTRL-SPACE when on top of a class name like that, and it should autoresolve if it's valid. In this case, you need to make sure you include the System.Runtime.Serialization namespace.

[Nemo]

@J Torres- THANKS a loooot. You made my day! :) I have updated my version with your help. Thanks again! You are such an INSPIRATION! :D

[Nemo]

I will vote you and mark it as useful post once I hear your feedback.

[Jaime Torres]

It looks like your edit was rejected by somebody else. I removed some of the text and included your listing as I feel it is valuable for others to see should somebody be searching for a solution to a similar problem and run across this post. Good luck with the rest of the project.