Return multiple rows of data from SQL DB? : The Official Microsoft ASP.NET Forums

Reply

shawn.bordea...

Member

122 Points

Return multiple rows of data from SQL DB?

Dec 31, 2011 05:45 AM|LINK

I am working on a basic WCF REST project, using the REST template in VS2010. On the call I am making I am expecting multiple rows of results to be returned from the SQL database. I am using a dataobject and currently it is only returning the first row. Can someone point out how i can return multiple, structured, rows? Here is my code;

IService1.cs

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

namespace WcfRestService
{
    [DataContract]
    public class clsVehicles
    {
        [DataMember]
        public string invNo { get; set;}
         [DataMember]
        public string carNo { get; set;}
        [DataMember]
        public string saleDate { get; set;}
        [DataMember]
        public string sold { get; set;}
        [DataMember]
        public string year { get; set;}
        [DataMember]
        public string make { get; set;}
        [DataMember]
        public string model { get; set;}
        [DataMember]
        public string mileage { get; set;}
        [DataMember]
        public string style { get; set;}
    }
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        clsVehicles getVehicles();
    }

}

service1.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace WcfRestService
{
    // Start the service and browse to http://<machine_name>:<port>/Service1/help to view the service's generated help page
    // NOTE: By default, a new instance of the service is created for each call; change the InstanceContextMode to Single if you want
    // a single instance of the service to process all calls.	
    

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
    // NOTE: If the service is renamed, remember to update the global.asax.cs file
    public class Service1 : IService1
    {
        private const string sConn = "MYDATABSECONNECTION";
        // TODO: Implement the collection resource that will contain the SampleItem instances

        [WebGet(UriTemplate = "getVehicles")]
        public clsVehicles getVehicles()
        {
            List<string> list = new List<string>();
            clsVehicles objCustomer = new clsVehicles();
            try
            {
            SqlConnection objConnection = new SqlConnection();
            objConnection.ConnectionString = sConn;
            DataSet ObjDataset = new DataSet();
            SqlDataAdapter objAdapater = new SqlDataAdapter();
            SqlCommand objCommand = new SqlCommand
            ("mydatabase.[dbo].[SelectVehicles]");
            objConnection.Open();
            objCommand.Connection = objConnection;
            objAdapater.SelectCommand = objCommand;
            objAdapater.Fill(ObjDataset);
            objCustomer.invNo = ObjDataset.Tables[0].Rows[0][0].ToString();
            objCustomer.carNo = ObjDataset.Tables[0].Rows[0][1].ToString();
            objCustomer.saleDate = ObjDataset.Tables[0].Rows[0][2].ToString();
            objCustomer.sold = ObjDataset.Tables[0].Rows[0][3].ToString();
            objCustomer.year = ObjDataset.Tables[0].Rows[0][4].ToString();
            objCustomer.make = ObjDataset.Tables[0].Rows[0][5].ToString();
            objCustomer.model = ObjDataset.Tables[0].Rows[0][6].ToString();
            objCustomer.mileage = ObjDataset.Tables[0].Rows[0][7].ToString();
            objCustomer.style = ObjDataset.Tables[0].Rows[0][8].ToString();
            objConnection.Close();
            }
            catch (SqlException ex)
            {
                objCustomer.invNo = "DB ERROR " + ex;
            }
            catch (Exception e)
            {
                objCustomer.invNo = "DB ERROR " + e;
            }
            finally
            {

            } return objCustomer;
        }



    }
}

When I run the URI call getVehicles I recieve this;

- <clsVehicles xmlns="http://schemas.datacontract.org/2004/07/WcfRestService" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <carNo>A1</carNo> 
  <invNo>262702</invNo> 
  <make>TOYOTA</make> 
  <mileage>193780</mileage> 
  <model>TACOMA PRERUNNER</model> 
  <saleDate>12/23/2011 12:00:00 AM</saleDate> 
  <sold>False</sold> 
  <style /> 
  <year>1999</year> 
  </clsVehicles>

While I am wanting to return the clsVehicles node for each vehicle returned from the stored proc call I am making.

Any help/guidance is greatly appreciated. Regards, Shawn

Edited by shawn.bordeaux on Dec 31, 2011 05:45 AM

Reply

adeelehsan

All-Star

18255 Points

2731 Posts

Re: Return multiple rows of data from SQL DB?

Dec 31, 2011 07:22 AM|LINK

Its because the return type of the method getVehicles() is clsVehicles, ONE OBJECT. And you are returing one object from the implemented method.

I would recommend to change the return type like a list:

public interface IService1
    {
        [OperationContract]
        List <clsVehicles> getVehicles();
    }

Then in the implementation, fillup the data set and populate a list, then return the list.

Please Mark As Answer if it helped.

MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011

Reply

shawn.bordea...

Member

122 Points

167 Posts

Re: Return multiple rows of data from SQL DB?

Dec 31, 2011 04:20 PM|LINK

Thank you! You have me going in the right direction now. Can you recomend a method to populate the list and then return it? Here is where I am going with this but what I am just having trouble grasping how to convert my results for by objCustomer return into an array list. Below I am looping through a datatable but I'm not assigning the records to a list, so the objCustomer is still only returning a single row of data. Can you help provide a decent way to accomplish returning a list? Thank you so much!

SqlConnection objConnection = new SqlConnection();
            objConnection.ConnectionString = sConn;
            DataSet ObjDataset = new DataSet();
            DataTable ObjDataTable = new DataTable();
            DataRow dr = null;
            SqlDataAdapter objAdapater = new SqlDataAdapter();
            SqlCommand objCommand = new SqlCommand
            ("myDB.[dbo].[SelectRunList]");
            objConnection.Open();
            objCommand.Connection = objConnection;
            objAdapater.SelectCommand = objCommand;
            objAdapater.Fill(ObjDataset);
            //objAdapater.Fill(ObjDataTable);
            ObjDataTable = ObjDataset.Tables[0];
            foreach (DataRow dr_loopVariable in ObjDataTable.Rows)
            {
                dr = dr_loopVariable;
                objCustomer.invNo = dr[0].ToString();
                objCustomer.carNo = dr[1].ToString();
                objCustomer.saleDate = dr[2].ToString();
                objCustomer.sold = dr[3].ToString();
                objCustomer.year = dr[4].ToString();
                objCustomer.make = dr[5].ToString();
                objCustomer.model = dr[6].ToString();
                objCustomer.mileage = dr[7].ToString();
                objCustomer.style = dr[8].ToString();
            }

Reply

Dan Bracuk

Contributor

3970 Points

1096 Posts

Re: Return multiple rows of data from SQL DB?

Dec 31, 2011 04:32 PM|LINK

List<T>.Add()

Reply

shawn.bordea...

Member

122 Points

167 Posts

Re: Return multiple rows of data from SQL DB?

Dec 31, 2011 04:34 PM|LINK

So would I add the objCustomer to the list for each loop ?as in;

List<T>.ADD(objCustomer);

Then just return List?

Reply

shawn.bordea...

Member

122 Points

167 Posts

Re: Return multiple rows of data from SQL DB?

Dec 31, 2011 07:48 PM|LINK

I've tried quite a few things but can't seem to get this to actually return a list back. I've tried adding a new list but it keeps throwing a NULL error.

Here is the updated code;

IService1.cs

namespace WcfRestService
{
    [DataContract]
    public class clsVehicles
    {
        [DataMember]
        public string invNo { get; set;}
        [DataMember]
        public string carNo { get; set;}
        [DataMember]
        public string saleDate { get; set;}
        [DataMember]
        public string sold { get; set;}
        [DataMember]
        public string year { get; set;}
        [DataMember]
        public string make { get; set;}
        [DataMember]
        public string model { get; set;}
        [DataMember]
        public string mileage { get; set;}
        [DataMember]
        public string style { get; set;}
    }
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
       //clsVehicles getVehicles();
	List<clsVehicles> getVehicles();
    }

}

Service1.cs

        [WebGet(UriTemplate = "getVehicles")]
        public List<clsVehicles> getVehicles()
        {
            List<clsVehicles> list = new List<clsVehicles>();
                        
            clsVehicles objCustomer = new clsVehicles();          
            try
            {
            SqlConnection objConnection = new SqlConnection();
            objConnection.ConnectionString = sConn;
            DataSet ObjDataset = new DataSet();
            DataTable ObjDataTable = new DataTable();
            DataRow dr = null;
            SqlDataAdapter objAdapater = new SqlDataAdapter();
            SqlCommand objCommand = new SqlCommand
            ("mydb.[dbo].[SelectRunList]");
            objConnection.Open();
            objCommand.Connection = objConnection;
            objAdapater.SelectCommand = objCommand;
            objAdapater.Fill(ObjDataset);
            //objAdapater.Fill(ObjDataTable);
            ObjDataTable = ObjDataset.Tables[0];
            int counter = 0;
            foreach (DataRow dr_loopVariable in ObjDataTable.Rows)
            {
                dr = dr_loopVariable;
                //set the list items in the loop?
                list[counter].invNo = dr[0].ToString();
                list[counter].carNo = dr[1].ToString();
                list[counter].saleDate = dr[2].ToString();
                list[counter].sold = dr[3].ToString();
                list[counter].year = dr[4].ToString();
                list[counter].make = dr[5].ToString();
                list[counter].model = dr[6].ToString();
                list[counter].mileage = dr[7].ToString();
                list[counter].style = dr[8].ToString();
                counter += 1;
            }
            objConnection.Close();
            }
            catch (Exception e)
            {
                objCustomer.invNo = "DB ERROR " + e;
                list[1].carNo = "error";
            }
           //return our list
        return list;
        }

Reply

adeelehsan

All-Star

18255 Points

2731 Posts

Re: Return multiple rows of data from SQL DB?

Jan 02, 2012 08:05 AM|LINK

Although there are lot of other improvements that can be done in your code but first, modify the getVehicales() method like the following and see if it works:

[WebGet(UriTemplate = "getVehicles")]
        public List<clsVehicles> getVehicles()
        {
            List<clsVehicles> list = new List<clsVehicles>();
                        
                     
            try
            {
            SqlConnection objConnection = new SqlConnection();
            objConnection.ConnectionString = sConn;
            DataSet ObjDataset = new DataSet();
            DataTable ObjDataTable = new DataTable();
            DataRow dr = null;
            SqlDataAdapter objAdapater = new SqlDataAdapter();
            SqlCommand objCommand = new SqlCommand
            ("mydb.[dbo].[SelectRunList]");
            objConnection.Open();
            objCommand.Connection = objConnection;
            objAdapater.SelectCommand = objCommand;
            objAdapater.Fill(ObjDataset);
            //objAdapater.Fill(ObjDataTable);
            ObjDataTable = ObjDataset.Tables[0];
           
            foreach (DataRow dr_loopVariable in ObjDataTable.Rows)
            {
                clsVehicles objCustomer = new clsVehicles();
                dr = dr_loopVariable;
                //set the list items in the loop?
                objCustomer.invNo = dr[0].ToString();
                objCustomer.carNo = dr[1].ToString();
                objCustomer.saleDate = dr[2].ToString();
                objCustomer.sold = dr[3].ToString();
                objCustomer.year = dr[4].ToString();
                objCustomer.make = dr[5].ToString();
                objCustomer.model = dr[6].ToString();
                objCustomer.mileage = dr[7].ToString();
                objCustomer.style = dr[8].ToString();

                list.Add(objCustomer);
            }
            objConnection.Close();
            }
            catch (Exception e)
            {
              clsVehicles errObj = new clsVehicles();
              errObj.invNo = "DB ERROR " + e;
               list.Add(errObj);
            }
           //return our list
        return list;
        }

Please Mark As Answer if it helped.

MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011

Edited by adeelehsan on Jan 02, 2012 08:08 AM
Marked as answer by shawn.bordeaux on Jan 02, 2012 10:27 PM

Reply

shawn.bordea...

Member

122 Points

167 Posts

Re: Return multiple rows of data from SQL DB?

Jan 02, 2012 10:27 PM|LINK

Thank you, I actually got it to work using pretty much the same method. Could you help point out some of the other improvments that could be made though? Here is my code, mind you I am wanting to return the results in JSON format, thank you!

[WebGet(UriTemplate = "getVehicles",
        BodyStyle = WebMessageBodyStyle.WrappedRequest,
        ResponseFormat = WebMessageFormat.Json,
        RequestFormat = WebMessageFormat.Json)]
        //new code starts
        List<clsVehicles> IService1.getVehicles()
        {
            List<clsVehicles> vehicleList = new List<clsVehicles>();
            clsVehicles newVehicle;
            try
            {

                SqlConnection objConnection = new SqlConnection();
                objConnection.ConnectionString = sConn;
                objConnection.Open();

                SqlCommand objCommand = new SqlCommand("myDB.[dbo].[SelectRunList]");
                objCommand.Connection = objConnection;
                objCommand.CommandType = System.Data.CommandType.StoredProcedure;

                SqlDataReader sqlDataReader = objCommand.ExecuteReader();

                while (sqlDataReader.Read())
                {
                    newVehicle = new clsVehicles();
                    newVehicle.carNo = sqlDataReader["carNo"].ToString();
                    newVehicle.invNo = sqlDataReader["invNo"].ToString();
                    newVehicle.make = sqlDataReader["make"].ToString();
                    newVehicle.mileage = sqlDataReader["mileage"].ToString();
                    newVehicle.model = sqlDataReader["model"].ToString();
                    newVehicle.saleDate = "none";
                    newVehicle.sold = sqlDataReader["sold"].ToString();
                    newVehicle.style = sqlDataReader["style"].ToString();
                    newVehicle.year = sqlDataReader["year"].ToString();
                    vehicleList.Add(newVehicle);
                }

            }
            catch (Exception e)
            {
                clsVehicles errObj = new clsVehicles();
                errObj.invNo = "DB ERROR " + e;
                vehicleList.Add(errObj);
            }
            return vehicleList;
            
        }

Return multiple rows of data from SQL DB? RSS

7 replies

shawn.bordea...

Return multiple rows of data from SQL DB?

adeelehsan

Re: Return multiple rows of data from SQL DB?

shawn.bordea...

Re: Return multiple rows of data from SQL DB?

Dan Bracuk

Re: Return multiple rows of data from SQL DB?

shawn.bordea...

Re: Return multiple rows of data from SQL DB?

shawn.bordea...

Re: Return multiple rows of data from SQL DB?

adeelehsan

Re: Return multiple rows of data from SQL DB?

shawn.bordea...

Re: Return multiple rows of data from SQL DB?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft