WCF Ria Services only return first row of Stored procedure

I'm using SL4 and VS2010 to develop a SL4 business app.

I'm trying to use WCF ria services to get data from my stored procedure. I've managed to do this after days of failure, by using ADO.NET Entity Data Model and a Domain Service class.

I had to create a dummy SP and Table to get around an issue with the Designer not recognising the columns of my SP, but this seems ok now and I have re-populated the SP with the correct SQL code.

When I query the SP from the silverlight UI, everything seems to run ok, but I only get the first row. I've tested this in SQL studio, with the same params, and the SP returns 31 rows.

All I have done in this project is:

1) Add the ADO.NET Entity, and link to my SP, and a single table which has the same fields as the SP returns in it's resultset.

2) Mapped the SP to the table using the Function import feature and made sure a primary key exists on the table.

3) Added the IQueryable function to the domain service as shown below.

4) Added the call from the silverlight page as shown underneath the domain service code.

When the ExecuteSP method is run, I get the first row of the SP results ONLY.

Hope someone can help.
public class EnergyDomainService : LinqToEntitiesDomainService<EnergySuiteDBEntities4>
    {
        public IQueryable<myTable> GetCarbonData(string sDate, string Month, string MeterInstancee)
        {
            return this.ObjectContext.GetCarbonData2(0, 0, 0, Convert.ToDateTime("8:00"), Convert.ToDateTime("21:00"), Convert.ToDateTime(sDate), 42, 0, 0, 0, 0, 0, 0, 0).AsQueryable();
            
        }
}

------------------------------------------------------------------------------
Silverlight client side
---------------------------------------------------------------------------------

protected void ExecuteSP(string sDate,string Month,string MeterInstance)
        {
            ctx = new EnergyDomainContext();
            try
            {
                var LoadItems = ctx.Load(ctx.GetCarbonDataQuery(Convert.ToDateTime(Calendar.SelectedDate).ToLongDateString(),"1","42"));
                LoadItems.Completed+=new EventHandler(LoadItems_Completed);
                
            }
            catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }

        }

private void LoadItems_Completed(object sender, EventArgs e)
        {
            var dbItems = ctx.myTables;
            dataGrid1.ItemsSource = ctx.myTables;
            
            if (dbItems.Count == 0)
            {
                MessageBox.Show("Unable to retrieve items from the database.");
                return;
            }
            foreach (var dbItem in dbItems)
            {
                MessageBox.Show(dbItem.Carbon_data.ToString() + " " + dbItem.Date.ToString() + " " + dbItem.CO2_data.ToString());
                

            }
            

        }

Open in new window

LVL 1
wint100Asked:
Who is Participating?
 
luisx25Commented:
You can't return a dataset or a dattable to a silverlight client. Try to follow these instructions:
1. Create a Silverlight Enabled WCF service.
2.Create the following method in the service you just created:

[OperationContract]
internal List<DataClass> getData(string sDate, string Month, string MeterInstance)
{
          // manipulate your parameters here. sDate, Month, MeterInstance
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetCarbonData", conn);
            cmd.CommandType = CommandType.StoredProcedure;
         // add the SP parameters here

            List<MyData> sp_data_list = new List<MyData>(); //create a list of type MyData

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MyData sp_data = new MyData(); // instantiate the MyData class
                    sp_data.Col1 = reader["col1"];
                    sp_data.Col2 = reader["col2"];

 // replace col1 and col2 with the actual columns from your SP


                    sp_data_list.Add(sp_data);   // add an instance of the class to the list
                }
            }
            finally
            {
                conn.Close();
            }

            return sp_data_list;
        }
}


3. Create this class at the end of the service code

[DataContract]
public class MyData   //this is the class that will hold your SP results
{
        private string  _Col1, _Col2;  // replace these with the actual columns returned from your SP


        [DataMember]
        public string Col1
        {
            get { return _Col1; }
            set { _Col1 = value; }
        }

        [DataMember]
        public string Col2
        {
            get { return _Col2; }
            set { _Col2 = value; }
        }

        public MyData()
        {
        }
}


4. In the silverlight client include a reference to the service:

using SL_BT.DataAccessService;
// change SL_BT with the name of the namespace you have in your service
// Change DataAccessService with the actual name of your service


5. Instantiate the service:

private DataAccessClient dbAccess = new DataAccessClient();
// DataAccessClient is the name I gave to the service when it was added to the solution.


6. In the constructor just before the InitializeComponent() line, add the event handler:

dbAccess. getData += new EventHandler< getDataCompletedEventArgs>(getDataCompleted);


7. On the method ExecuteSP add this:


            dbAccess.getDataCompleted += getSearchResultsCompleted;
            dbAccess.getDataAsync(/*your parameters*/);


8. Create a method getDataCompleted

        private void getDataCompleted(object sender, getSearchResultsCompletedEventArgs e)
        {
             dataGrid1.ItemsSource = e.Result;
            // e.Result is the list with all the results resturned from your SP
       }


I hope this helps. If you have more questions, just ask.
0
 
SriVaddadiCommented:
Have you mapped the results of your SP to a complex type?
0
 
SriVaddadiCommented:
Does LoadItems_Completed display(MessageBox) all records?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wint100Author Commented:
I have a function import that maps the SP to the myTable. I've done nothing with complex types, as I only saw an option for this when using LINQtoSQL classes no ADO.NET Entity models.

LoadItems_Completed displays a count of 1 and only a single messagebox is shown for the first row of the SP results.
0
 
wint100Author Commented:
The SP is mapped to an Entity created from a table in the database, and the table has exactly the same columns as the SP returns.
0
 
luisx25Commented:
I would suggest creating a class in the web service to hold the data returned by the SP, then return a list<new class> with all the rows returned by the SP.
Silverlight client: on LoadItems_Completed you load the returned list to the grid with something like this dataGrid1.ItemsSource = e.Result
0
 
wint100Author Commented:
e.result? I on't have an option for this in the loadItems_completed
0
 
wint100Author Commented:
Also, as you can tell I'm new to this, so could you give an exanple fo your suggestion of adding a class to my domain service?

I'm confused by your last post.
0
 
wint100Author Commented:
I have the code below that I use in the ASP.NET app to populate a datatable, can I use this?
public DataTable Single_Carbon_Data(string sDate, string Month, string MeterInstance)
        {

            string year = Convert.ToDateTime(sDate).Year.ToString();
            string SDate = "01 " + Month + " " + year;
            using (LINQDataContext db = new LINQDataContext())
            {
                var Carbon = from s in db.MeterLists
                             where s.MeterInstance == Convert.ToInt32(MeterInstance)
                             select new
                             {
                                 s.CarbonMultiplier,
                                 s.CO2Multiplier
                             };
                string CO2Multiplier = string.Empty;
                string CarbonMultiplier = string.Empty;
                foreach (var d in Carbon)
                {
                    CO2Multiplier = d.CO2Multiplier.ToString();
                    CarbonMultiplier = d.CarbonMultiplier.ToString();
                }

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    // Initialize DataTable class
                    using (DataTable dt = new DataTable())
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            try
                            {

                                cmd.Connection = con;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = "GetCarbonData";
                                SqlParameter[] param = new SqlParameter[13];

                                param[0] = new SqlParameter("@CostValueWeekday", SqlDbType.Money);
                                param[1] = new SqlParameter("@CostValueWeeknight", SqlDbType.Money);
                                param[2] = new SqlParameter("@CostValueWeekend", SqlDbType.Money);
                                param[3] = new SqlParameter("@sTime", SqlDbType.DateTime);
                                param[4] = new SqlParameter("@eTime", SqlDbType.DateTime);
                                param[5] = new SqlParameter("@sDate", SqlDbType.DateTime);
                                param[6] = new SqlParameter("@Meter", SqlDbType.Int);
                                param[7] = new SqlParameter("@ClChRate", SqlDbType.Money);
                                param[8] = new SqlParameter("@ChCap", SqlDbType.Int);
                                param[9] = new SqlParameter("@CapRate", SqlDbType.Money);
                                param[10] = new SqlParameter("@GasRate", SqlDbType.Int);
                                param[11] = new SqlParameter("@CO2", SqlDbType.Int);
                                param[12] = new SqlParameter("@Carbon", SqlDbType.Int);

                                param[0].Value = "0";

                                param[1].Value = "0";

                                param[2].Value = "0";

                                param[3].Value = "00:00:01";

                                param[4].Value = "23:59:59";

                                param[5].Value = Convert.ToDateTime(sDate);
                                param[6].Value = Convert.ToInt32(MeterInstance);

                                param[7].Value = "0";

                                param[8].Value = "0";

                                param[9].Value = "0";

                                param[10].Value = "0";
                                if ((CO2Multiplier != "") && (CO2Multiplier != null))
                                {
                                    param[11].Value = decimal.Parse(CO2Multiplier);
                                }
                                else
                                {
                                    param[11].Value = "0.537";
                                }
                                if ((CarbonMultiplier != "") && (CarbonMultiplier != null))
                                {
                                    param[12].Value = decimal.Parse(CarbonMultiplier);
                                }
                                else
                                {
                                    param[12].Value = "0.537";
                                }


                                for (int i = 0; i < param.Length; i++)
                                {
                                    cmd.Parameters.Add(param[i]);
                                }






                                // Define the data adapter and fill the dataset 
                                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                                {
                                    da.Fill(dt);
                                }

                            }
                            catch (System.Data.SqlClient.SqlException ex)
                            {
                                string msg = "Carbon Emissions Chart Data Collection Error: ";
                                msg += ex.Message;
                                throw new Exception(msg);

                            }
                            finally
                            {
                                con.Close();
                            }



                        }
                     return dt   
                    }
                }
            }
        }

Open in new window

0
 
wint100Author Commented:
I found a way to use my existing code, and then used the code bwlow to query and iterate though each rows to add to myTable. But I still only get the first row in my SL client!?
 public IQueryable<myTable> GetCarbonData(string sDate, string Month, string MeterInstance)
        {
            // return this.ObjectContext.GetCarbonData3(0, 0, 0, Convert.ToDateTime("8:00"), Convert.ToDateTime("21:00"), Convert.ToDateTime(sDate), 42, 0, 0, 0, 0, 0, 0, 0).AsQueryable();
            DataTable DT= this.Single_Carbon_Data(sDate, Month, MeterInstance);
            List<myTable> col = new List<myTable>();
            for (int i = 0; i < DT.Rows.Count; i++)
            {

                col.Add(new myTable() { Date=DT.Rows[i].ItemArray[0].ToString(), CO2_data = DT.Rows[i].ItemArray[1].ToString(), Carbon_data = DT.Rows[i].ItemArray[2].ToString(),kWh_data=DT.Rows[i].ItemArray[3].ToString() });
            }
            
            return col.AsQueryable();
        }

Open in new window

0
 
wint100Author Commented:
Should: internal List<DataClass> read internal List<MyData>??
0
 
wint100Author Commented:
Also, I can't seem to add Steps 4 or 5, I've added the using namespace, but namespace.SilverlightService is not found by intellisense.

Do I need to add a service reference to the SL project?
0
 
luisx25Commented:
You/re right it should be internal List<MyData>

You need to add a web service reference to the silverlight project.
0
 
wint100Author Commented:
Ok I did all that but it gives an error when trying to instanstiate the ref:

Error      10      Cannot create an instance of the abstract class or interface 'ServiceReference.EnergySuiteV2.Web.Services.SilverlightService.SilverlightService'      C:\Users\Mark Wint.GB\documents\visual studio 2010\Projects\EnergySuiteV2\EnergySuiteV2\Views\ReportMenu.xaml.cs      21      39      EnergySuiteV2
0
 
wint100Author Commented:
Ok, so I rebuilt and that cleared, I know get:

Error      8      The type or namespace name 'getSearchResultsCompletedEventArgs' could not be found (are you missing a using directive or an assembly reference?)      C:\Users\Mark Wint.GB\documents\visual studio 2010\Projects\EnergySuiteV2\EnergySuiteV2\Views\ReportMenu.xaml.cs      58      54      EnergySuiteV2


0
 
luisx25Commented:
Sorry I copied from my code.

Line dbAccess.getDataCompleted += getSearchResultsCompleted should read

dbAccess.getDataCompleted += getDataCompleted


and line private void getDataCompleted(object sender, getSearchResultsCompletedEventArgs e)

should read private void getDataCompleted(object sender, getDataCompletedEventArgs e
0
 
wint100Author Commented:
Back to the error about the service:

Error      13      Cannot create an instance of the abstract class or interface 'ServiceReference.EnergySuiteV2.Web.Services.SilverlightService.SilverlightService'      C:\Users\Mark Wint.GB\documents\visual studio 2010\Projects\EnergySuiteV2\EnergySuiteV2\Views\ReportMenu.xaml.cs      21      39      EnergySuiteV2
0
 
wint100Author Commented:
My silverlight client code is below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Windows.Navigation;
using EnergySuiteV2.Web.Services;
using ServiceReference.EnergySuiteV2.Web.Services.SilverlightService;


namespace EnergySuiteV2
{
    public partial class ReportMenu : Page
    {
        private SilverlightService dbAccess = new SilverlightService();
        private EnergyDomainContext ctx;

       
        
        
        public ReportMenu()
        {
            dbAccess.getData += new EventHandler<getDataCompletedEventArgs>(getDataCompleted);
            InitializeComponent();
        }
        
        // Executes when the user navigates to this page.
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
        }

        protected void ExecuteSP(string sDate,string Month,string MeterInstance)
        {
            dbAccess.getDataCompleted += getDataCompleted;
            dbAccess.getDataAsync(/*your parameters*/);
            ctx = new EnergyDomainContext();
            try
            {
                var LoadItems = ctx.Load(ctx.GetCarbonDataQuery(Convert.ToDateTime(Calendar.SelectedDate).ToLongDateString(), "1", "42"));


                //dataGrid1.ItemsSource = ctx.myTables; 
                LoadItems.Completed+=new EventHandler(LoadItems_Completed);
                
            }
            catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }

        }
        private void getDataCompleted(object sender, getDataCompletedEventArgs e)
        {
            dataGrid1.ItemsSource = e.Result;
            // e.Result is the list with all the results resturned from your SP
        }
        private void tLDataDomainDataSource_LoadedData(object sender, System.Windows.Controls.LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }
       
        private void meterListDomainDataSource_LoadedData(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void meterListDomainDataSource_LoadedData_1(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void Calendar_SelectedDatesChanged(object sender, SelectionChangedEventArgs e)
        {
            ExecuteSP(Calendar.SelectedDate.ToString(), Convert.ToDateTime(Calendar.SelectedDate).Month.ToString(), "42");
        }

        private void meterListDomainDataSource_LoadedData_2(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void CompareEnable_Checked(object sender, RoutedEventArgs e)
        {
            btnAdd.Visibility = Visibility.Visible;
            btnRemove.Visibility = Visibility.Visible;
            btnGo.Visibility = Visibility.Visible;
            MeterList.Visibility = Visibility.Visible;

        }

        private void CompareEnable_Unchecked(object sender, RoutedEventArgs e)
        {
            btnAdd.Visibility = Visibility.Collapsed;
            btnRemove.Visibility = Visibility.Collapsed;
            btnGo.Visibility = Visibility.Collapsed;
            MeterList.Visibility = Visibility.Collapsed;
        }

        private void btnAdd_Click(object sender, RoutedEventArgs e)
        {
            if ((MeterList.Items.Count < 4) && ((Calendar.SelectedDate) < DateTime.Now) && (Calendar.SelectedDate > DateTime.Now.AddYears(-100)))
            {
                MeterList.Items.Add((Convert.ToDateTime(Calendar.SelectedDate).ToLongDateString() + "-" + comboMeters.SelectedItem.ToString()));
                
                
            }
        }

        private void meterListDomainDataSource1_LoadedData(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void meterListDomainDataSource_LoadedData_3(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void LoadItems_Completed(object sender, EventArgs e)
        {
            
            //foreach (var c in ctx.myTables)
            //{
            //    MessageBox.Show(c.CO2_data.ToString());
            //}

            dataGrid1.ItemsSource = ctx.myTables ;
            
           // if (dbItems.Count == 0)
           // {
           //     MessageBox.Show("Unable to retrieve items from the database.");
           //     return;
           // }
           // foreach (var dbItem in dbItems)
           // {
            //    MessageBox.Show(dbItem.Carbon_data.ToString() + " " + dbItem.Date.ToString() + " " + dbItem.CO2_data.ToString());
                

            //}
            

        }

        private void myTableDomainDataSource_LoadedData(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void meterListDomainDataSource_LoadedData_4(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void meterListDomainDataSource_LoadedData_5(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void myTableDomainDataSource1_LoadedData(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void myTableDomainDataSource_LoadedData_1(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }

        private void meterListDomainDataSource_LoadedData_6(object sender, LoadedDataEventArgs e)
        {

            if (e.HasError)
            {
                System.Windows.MessageBox.Show(e.Error.ToString(), "Load Error", System.Windows.MessageBoxButton.OK);
                e.MarkErrorAsHandled();
            }
        }
    }
}

Open in new window

0
 
wint100Author Commented:
And my Service code, something is wrong here, it's one error after another:
using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using EnergySuiteV2.Web.Properties;

namespace EnergySuiteV2.Web.Services
{
    [ServiceContract(Namespace = "EnergySuiteV2.Web.Services")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class SilverlightService
    {
        private string connectionString=Settings.Default.EnergySuiteCS;
        [OperationContract]
internal List<MyData> getData(string sDate, string Month, string MeterInstance)
{
    string CO2Multiplier = string.Empty;
    string CarbonMultiplier = string.Empty;
    using (LINQDataContext db = new LINQDataContext())
    {

        var Carbon = from s in db.MeterLists
                     where s.MeterInstance == Convert.ToInt32(MeterInstance)
                     select new
                     {
                         s.CarbonMultiplier,
                         s.CO2Multiplier
                     };
       
        foreach (var d in Carbon)
        {
            CO2Multiplier = d.CO2Multiplier.ToString();
            CarbonMultiplier = d.CarbonMultiplier.ToString();
        }
    }

          // manipulate your parameters here. sDate, Month, MeterInstance 
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetCarbonData", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] param = new SqlParameter[13];

            param[0] = new SqlParameter("@CostValueWeekday", SqlDbType.Money);
            param[1] = new SqlParameter("@CostValueWeeknight", SqlDbType.Money);
            param[2] = new SqlParameter("@CostValueWeekend", SqlDbType.Money);
            param[3] = new SqlParameter("@sTime", SqlDbType.DateTime);
            param[4] = new SqlParameter("@eTime", SqlDbType.DateTime);
            param[5] = new SqlParameter("@sDate", SqlDbType.DateTime);
            param[6] = new SqlParameter("@Meter", SqlDbType.Int);
            param[7] = new SqlParameter("@ClChRate", SqlDbType.Money);
            param[8] = new SqlParameter("@ChCap", SqlDbType.Int);
            param[9] = new SqlParameter("@CapRate", SqlDbType.Money);
            param[10] = new SqlParameter("@GasRate", SqlDbType.Int);
            param[11] = new SqlParameter("@CO2", SqlDbType.Int);
            param[12] = new SqlParameter("@Carbon", SqlDbType.Int);

            param[0].Value = "0";

            param[1].Value = "0";

            param[2].Value = "0";

            param[3].Value = "00:00:01";

            param[4].Value = "23:59:59";

            param[5].Value = Convert.ToDateTime(sDate);
            param[6].Value = Convert.ToInt32(MeterInstance);

            param[7].Value = "0";

            param[8].Value = "0";

            param[9].Value = "0";

            param[10].Value = "0";
            if ((CO2Multiplier != "") && (CO2Multiplier != null))
            {
                param[11].Value = decimal.Parse(CO2Multiplier);
            }
            else
            {
                param[11].Value = "0.537";
            }
            if ((CarbonMultiplier != "") && (CarbonMultiplier != null))
            {
                param[12].Value = decimal.Parse(CarbonMultiplier);
            }
            else
            {
                param[12].Value = "0.537";
            }


            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }

            List<MyData> sp_data_list = new List<MyData>(); //create a list of type MyData

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MyData sp_data = new MyData(); // instantiate the MyData class
                    sp_data.Col1 = reader["Date"].ToString(); 
                    sp_data.Col2 = reader["CO2_data"].ToString();
                    sp_data.Col3 = reader["Carbon_data"].ToString();
                    sp_data.Col4 = reader["kWh_data"].ToString();

 // replace col1 and col2 with the actual columns from your SP


                    sp_data_list.Add(sp_data);   // add an instance of the class to the list
                }
            }
            finally
            {
                conn.Close();
            }

            return sp_data_list;
        }


        // Add more operations here and mark them with [OperationContract]
    }
    [DataContract]
    public class MyData   //this is the class that will hold your SP results
    {
        private string Date,CO2_data,Carbon_data,kWh_data;  // replace these with the actual columns returned from your SP


        [DataMember]
        public string Col1
        {
            get { return Date; }
            set { Date = value; }
        }

        [DataMember]
        public string Col2
        {
            get { return CO2_data; }
            set { CO2_data = value; }
        }
        [DataMember]
        public string Col3
        {
            get { return Carbon_data; }
            set { Carbon_data = value; }
        }
        [DataMember]
        public string Col4
        {
            get { return kWh_data; }
            set { kWh_data = value; }
        }


        public MyData()
        {
        }
    }
}

Open in new window

0
 
wint100Author Commented:
Finally got it running, I ha to change SilverlightService to SilverlightServiceclient, and also change:

dbAccess.getData += new EventHandler<getDataCompletedEventArgs>(getDataCompleted);

to

dbAccess.getDataCompleted += new EventHandler<getDataCompletedEventArgs>(getDataCompleted);

BUT I STILL ONLY GET THE FIRST ROW FROM THE SP!?!?
0
 
luisx25Commented:
That's because you're calling the code I suggested  and then you're calling your old code, see below

protected void ExecuteSP(string sDate,string Month,string MeterInstance)
        {
            dbAccess.getDataCompleted += getDataCompleted;
            dbAccess.getDataAsync(/*your parameters*/);
//********* You must pass the correct parameters to the getData method, replace /*your parameters*/
//************with your parametesr: sdate, month, meterinstance


// don't execute the rest of this code ***********************************************************************

            ctx = new EnergyDomainContext();
            try
            {
                var LoadItems = ctx.Load(ctx.GetCarbonDataQuery(Convert.ToDateTime(Calendar.SelectedDate).ToLongDateString(), "1", "42"));


                //dataGrid1.ItemsSource = ctx.myTables;
                LoadItems.Completed+=new EventHandler(LoadItems_Completed);
               
            }
            catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
//***************************************************************************************************************


        }



Put a breakpoint in the getData method in the service and run the code, be sure that the list is being populated with the results of your SP.

Put another breakpoint in dataGrid1.ItemsSource = e.Result;
This line should execute after getData is executed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.