How to pass in String property to WCF and return a dataset?

Question

The program returns System.Data.DataSet. I want the items of the data set returned.

I have a database with 4 tables: Bands, Festivals, Stages; and a junction table - Band_Festival.

The sql queries all work when executed and a DataGridView on the form displays the correct records.

I'm trying to have WCF retrieve the data and return a DataSet or list<DataSet> to the Form.

A selected ComboBox item on the form should be passed to wcf when a call is made, which is used as a parameter for the SQL query.

String Festivals {get; set;} ComboBox.SelectedItem = Festivals

is used to pass in the parameter to the sql query.

When the sql query's WHERE condition is festival_name = 'Boomtown Fair' the call returns "System.Data.DataSet", I want the list of items. When the condition is festival_name = @festival with Parameters.AddValue("@festival", Festivals) An Exception is thrown with "Parameter not supplied"

Here's some code:

Form

String Festivals { get; set; }

    FestivalsBandsServiceReference.FestivalBandsServiceClient ws = null;
    public Form1()
    {
        InitializeComponent();
        ComBoxFestivals.SelectedItem = Festivals;
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        ws = new FestivalsBandsServiceReference.FestivalBandsServiceClient();
    }
    private void comBoxFestivals_SelectedIndexChanged(object sender, EventArgs e)
    {

        List<String> festival = ws.GenerateNewBandList(Festivals);

        Console.WriteLine(String.Join("," ,festival));
    }

FestivalBandsService.svc.cs

public List<DataSet> GenerateNewBandList(String Festivals)
    {

        List<DataSet> bands = new List<DataSet>();

        string dataSource = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = " +
                       @"D:\WCFFestivalBands\WCFFestivalBands\App_Data\FestivalBands.mdf; Integrated Security = True; Connect Timeout = 30";


        //Create new instance of sql connection, pass in the connection string for FestivalBands.mdf to connect to database.
        SqlConnection conn = new SqlConnection(dataSource);

        string sqlQuerySelectBands = "SELECT Bands.Band_Name FROM Band_Festival JOIN dbo.Bands ON Bands.Band_ID =" +
                                  "Band_Festival.Band_ID JOIN dbo.Festival ON Festival.Festival_ID " +
                                  "= Band_Festival.Festival_ID WHERE Festival_Name = @festival";

        SqlCommand cmd = new SqlCommand(sqlQuerySelectBands, conn);
        cmd.Parameters.AddWithValue("@festival", Festivals);

        conn.Open();

        //Create new instance of DataAdpater to retrieve the data pass in Sql command
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        //Create new instance of dataSet to hold the data retrieved from sql query
        DataSet ds = new DataSet();

        //using DataAdapter fill in dataSet wiht data if user input and stored data matches
        da.Fill(ds);

        bands.Add(ds);

        return bands;
    }
}

IFestivalBandsService.cs

     [ServiceContract]
public interface IFestivalBandsService
{ 
    [OperationContract]
    List<DataSet> GenerateNewBandList(String Festivals);
}

This is pretty new. Only just learnt how to pass and return values using WCF. Passing a list from a dataSet is next level. Any help is greatly appreciated.

I haven't added the tables colums or rows, not sure if its relevent?

Thanks in advance.

Answer 1

According to your code snippets, I copy and test it locally, it seems that there are no errors in your WCF server end. Otherwise, it will not succeed when you hard-code SQL statement to query your database. But I could not find the process you initialize your Festivals property when you consuming the service in the client-side.

String Festivals { get; set; }
ComBoxFestivals.SelectedItem = Festivals;

these code snippest may be the reason caused your issue.
In .net 4.6+ you could refer to the following code to set up the default of the property.

public int Price { get; set; } = 100;

or you could refer to the following code.

    class Product
    {
        private string name = "Apple";
        public int ID { get; set; }
        public string Name
        {
            get { return name; }
            set { this.name = value; }
        }
}