none
Stored procedures to view data in wcf RRS feed

  • Question

  • i want to retrieve some information from the data base using a stored procedure. 

    in the class which is inside the wcf service i have a code like this,

      public DataTable viewLabs()
            {
                DataTable dt = new DataTable();
                string vwlbs = "exec viewLabs @day,@time,@gid";

                SqlCommand cmd = new SqlCommand(vwlbs, con);
                cmd.Parameters.AddWithValue("day", _day);
                cmd.Parameters.AddWithValue("time", _time);
                cmd.Parameters.AddWithValue("gid", _gid);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                adp.Fill(dt);
              //  dt.TableName = "viewLabs";
                dt.TableName = "Labs";
                dt.TableName = "Practical_Sessions";
               
                return dt;

            }

    my question is i get a communication exception if i didn't mention the table name ,usually when i return a data table.but in this scenario i don't have a special table to mention because this is a stored procedure.so i have assign the table names which relates to my stored procedures.but still i get a empty table as the result.why?

    my stored procedure,

    ALTER procedure [dbo].[viewLabs] @day varchar(10),@time varchar(15),@gid varchar(20)
    as
    declare @no_of_std int

    set @no_of_std=(select no_of_students from groups where Group_id=@gid)

    select distinct l.Lab_id,capacity from Labs l,practical_sessions pr where status !='not ok'
    and l.lab_id  not in(

    select lab_id from practical_sessions where day=@day and time=@time 
    )
    and @no_of_std<=capacity

    Monday, March 13, 2017 6:32 AM

All replies

  • DataTable requires a name to be serializable. The default constructor does not give the table a name, so:

    return new DataTable();
    will not be serializable, while:

    return new DataTable("someTable");
    will name the table whatever is passed as the parameter.

    Note that a table can be given a name at any time by assigning a string to the TableName property of the DataTable.

    var table = new DataTable();
    table.TableName = "someTable";

    william xifaras

    Monday, March 13, 2017 6:03 PM
  • i know that we must give a table name. but in my scenario the data is retrieved through a stored procedure . so what table name should i give?
    Tuesday, March 14, 2017 1:33 AM
  • >>so what table name should i give?

    You could pass any string for table name which will make DataTable to be serializable.

    Based on your description, it seems you get an empty table even if you set TableName.

    Did you get the empty table in client side or service side? If it is in service side, I suggest you output vwlbs and execute it in SQL Server to check whether it will return you any rows.

    If dt contains rows when return from service, and you got empty rows at client side, could you share us how you call viewLabs() at client side?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 14, 2017 2:20 AM
  • exactly you are correct,when i check this in sql server it returns a table with relevant data. but here i am getting an empty table . here is my client side code ,

    string day = comboBox1.SelectedItem.ToString();
                string time = comboBox5.SelectedItem.ToString();
                string gid = comboBox2.SelectedItem.ToString();

                Service1Client obj = new Service1Client();
             dataGridView1.DataSource=obj.viewLabs(day,time,gid);

    Tuesday, March 14, 2017 4:36 AM
  • If you put a breakpoint at below line, is it empty?

     return dt;

    If you add below code at client side, and check the dt, is it empty?

    DataTable dt =obj.viewLabs(day,time,gid);


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 14, 2017 5:13 AM
  • you mean to do like this?


                Service1Client obj = new Service1Client();
             //dataGridView1.DataSource=obj.viewLabs(day,time,gid);

                DataTable dt = obj.viewLabs(day, time, gid);

                dataGridView1.DataSource = dt;

    but no resposnse,still empty

    Tuesday, March 14, 2017 5:49 AM
  • Do you know how to debug and Add Watch? I suggest you check whether dt is empty in service side and client side.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 14, 2017 5:57 AM
  • Have your issue been resolved? If you have any update about your issue, please feel free to let us know. We will try our best to help you.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 15, 2017 2:23 AM
  • still didn't solved,by the way i am trying to figure out whether the table is filled inside the server side.i will let you know

    Wednesday, March 15, 2017 3:53 AM
  • as you asked in the service the table is filled.in the client side it is emty
    Wednesday, March 15, 2017 5:21 AM
  • You can give it any name. In your case you can name it the same as your stored procedure.

    var table = new DataTable();
    table.TableName = "viewLabs";


    william xifaras

    Wednesday, March 15, 2017 3:11 PM
  • still i get an empty table,,,i don't know what is the error here
    Wednesday, March 15, 2017 7:19 PM
  • is your datatable empty after  you call fill?

    william xifaras

    Thursday, March 16, 2017 2:39 PM
  • If you change below code to a table, will you get any record in client?

    string vwlbs = "exec viewLabs @day,@time,@gid";
    to
    string vwlbs = " select * from [table name]";

    If you made a test with below code, will you get any record?

           public DataTable GetTable()
            {
                MakeParentTable();
                DataTable dt = dataSet.Tables[0];
                return dt;
            }
            private System.Data.DataSet dataSet;
            private void MakeParentTable()
            {
                // Create a new DataTable.
                System.Data.DataTable table = new DataTable("ParentTable");
                // Declare variables for DataColumn and DataRow objects.
                DataColumn column;
                DataRow row;
    
                // Create new DataColumn, set DataType, 
                // ColumnName and add to DataTable.    
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.Int32");
                column.ColumnName = "id";
                // Add the Column to the DataColumnCollection.
                table.Columns.Add(column);
    
                // Create second column.
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "ParentItem";
                // Add the column to the table.
                table.Columns.Add(column);
    
                // Instantiate the DataSet variable.
                dataSet = new DataSet();
                // Add the new DataTable to the DataSet.
                dataSet.Tables.Add(table);
    
                // Create three new DataRow objects and add 
                // them to the DataTable
                for (int i = 0; i <= 2; i++)
                {
                    row = table.NewRow();
                    row["id"] = i;
                    row["ParentItem"] = "ParentItem " + i;
                    table.Rows.Add(row);
                }
            }
    

    It is difficult to identity your issue without reproducing your issue, it would be helpful if you could share us your WCF client and Service.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 17, 2017 3:09 AM