locked
Beginner Question on DataSet and TableAdapter RRS feed

  • Question

  • User260076833 posted

    Hello,

    I am beginning to use a DataSet to access a SQL database.

    There is a table of rooms:

    idx          int
    organisation int
    name         varchar(50)
    type         char(1)

    Now I would like to have a method that retrieves a set of rooms, like this:

    DataTable getRooms(int organisation,String type);

    I know that I can create a new query that uses parameters:

    SELECT * FROM rooms WHERE (organisation=@organisation AND type=@type);

    But in this case, the WHERE-condition always contains all attributes (organisation and type). But sometimes I would like to search all rooms with a certain type, where the organisation does not matter. In this case, I would pass -1 as the organisation.

    What I need is something like this:

    DataTable getRooms(int organisation,String type);
    {
     String condition = "";
    
     if (organisation != -1)
      condition += "organisation=" + organisation;
    
     if (type != null)
     {
      if (condition != "")
       condition += " AND ";
      condition += "type='" + type + "'";
     }
    
     DataTable t = GetDataByCondition (condition);
     return (t);
    }

    Can I do this somehow?

    Thank you

    Magnus

    Wednesday, July 29, 2015 8:45 AM

Answers

  • User260076833 posted

    Hi Dillon

    I assume from your answer that it is not possible to add a query to a DataSet and pass a boolean expression to it, like this:

    in TableAdapter:
    SELECT * FROM room WHERE (@condition);

    When you try this, the DataSet designer shows an error.

    However, I got the problem solved by using the FilterExpression property of the corresponding ObjectDataSource:

    private void updateSelection()
    {
     String c = getCondition();
     ObjectDataSource1.FilterExpression = c;
    }
    

    Magnus

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 31, 2015 1:59 AM

All replies

  • User37182867 posted

    Not at a place where I can easily test c# code so there may be some errors

    function string GetQuery(int orgId, int orgType){
      StringBuilder sb = new StringBuilder();
      sb.Append("select * from rooms where ");
      sb.Append((orgId = -1) ? "type=@type" : "organization=@organization and type=@type");
      return sb.ToString();
    }

    Then check to see if the organization parameter is needed before adding it to the sql adapter parameter colleciton

    As a side note - its a Z not an S => organization

    (sorry, it just got under my skin.)

    Wednesday, July 29, 2015 9:51 AM
  • User260076833 posted

    Hi,

    I know how to build a String that holds the condition.

    But how do I pass this condition to the DataSet?

    Remember, I create the DataSet (xsd) in VS at design time...

    Thanks
    Magnus

    Wednesday, July 29, 2015 12:39 PM
  • User37182867 posted

    This is the first time you have mentioned what kind of DataSet you are using.

    You need to create an adapter, table object and row object from your dataset.

            Dim ad As DataSet1TableAdapters.testTableTableAdapter
            ad = New DataSet1TableAdapters.testTableTableAdapter
            Dim tt As DataSet1.testTableDataTable
            tt = ad.GetData
            For Each r As DataSet1.testTableRow In tt.Rows
                Trace.Write(r.testStr)
            Next
    

    That should get you going. Sorry, I don't have time to convert it to C# right now.

    Wednesday, July 29, 2015 3:13 PM
  • User-271186128 posted

    Hi Magnus,

    From your code and description, I suggest you could fill the dataset using a DataAdapter. You could refer to the following code:

            private void BindGridView()
            {
                DataSet ds = new DataSet();
                string constr = ConfigurationManager.ConnectionStrings["MyTestDBConnStr"].ConnectionString.ToString();
                using (SqlConnection con = new SqlConnection(constr))
                {
    //The condition query statement. string cmdtext = "SELECT [EmployeeID], [LastName], [FirstName], [Address], [City] FROM [Employees]"; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = cmdtext; cmd.Connection = con; con.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(ds); } } Session["TaskTable"] = ds.Tables[0]; GridView2.DataSource = ds.Tables[0]; GridView2.DataBind(); }

    Here is a tutorial about filling a Dataset with Data, you could refer to it:

    https://msdn.microsoft.com/en-us/library/ms171920.aspx

    Best Regards,
    Dillion

    Thursday, July 30, 2015 1:56 AM
  • User260076833 posted

    Hi Dillon

    I assume from your answer that it is not possible to add a query to a DataSet and pass a boolean expression to it, like this:

    in TableAdapter:
    SELECT * FROM room WHERE (@condition);

    When you try this, the DataSet designer shows an error.

    However, I got the problem solved by using the FilterExpression property of the corresponding ObjectDataSource:

    private void updateSelection()
    {
     String c = getCondition();
     ObjectDataSource1.FilterExpression = c;
    }
    

    Magnus

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 31, 2015 1:59 AM