Execute Custom query on tableadapter

Question

I want to be able to execute a custom SQL query against a table adapter that I have. Is it possible to do this? Or can I only use the predefined queries on each table adapter in the dataset design view?

If I can't do this, how would I go about executing my SQL query against a table, and having the results display in my datagridview that's bound to a table adapter?

Thanks.

EDIT: I didn't explain myself properly. I know how to Add queries to a tableadapter using the dataset designer. My issue is i need to execute a custom peice of SQL (which i build dynamically) against an existing table adapter.

Answer 1

I posted a comment pointing to an example using VB which creates a class that extends TableAdapter. Instead of using the VB example and rewriting it in C# I will show how this can be done without creating a class that extends TableAdapter.

Basically create a BackgroundWorker to perform the sql query. You don't have to but it would be nice. Build the query string based on input from the user.

    private void queryBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
    {
        //Initialize sqlconnection
        SqlConnection myConnection;

        //Convert date in to proper int format to match db
        int fromDate = int.Parse(dateTimePickerStartDate.Value.ToString("yyyyMMdd"));
        int toDate = int.Parse(dateTimePickerEndDate.Value.ToString("yyyyMMdd"));

        //Setup Parameters
        SqlParameter paramFromDate;
        SqlParameter paramToDate;
        SqlParameter paramItemNo;
        SqlParameter paramCustomerNo;

        //Fill the data using criteria, and throw any errors
        try
        {
            myConnection = new SqlConnection(connectionString);
            myConnection.Open();
            using (myConnection)
            {
                using (SqlCommand myCommand = new SqlCommand())
                {
                    //universal where clause stuff
                    string whereclause = "WHERE ";

                    //Add date portion
                    paramFromDate = new SqlParameter();
                    paramFromDate.ParameterName = "@FromDate";
                    paramFromDate.Value = fromDate;

                    paramToDate = new SqlParameter();
                    paramToDate.ParameterName = "@ToDate";
                    paramToDate.Value = toDate;

                    myCommand.Parameters.Add(paramFromDate);
                    myCommand.Parameters.Add(paramToDate);

                    whereclause += "(TableName.date BETWEEN @FromDate AND @ToDate)";

                    //Add item num portion
                    if (!string.IsNullOrEmpty(itemNo))
                    {
                        paramItemNo = new SqlParameter();
                        paramItemNo.ParameterName = "@ItemNo";
                        paramItemNo.Value = itemNo;

                        myCommand.Parameters.Add(paramItemNo);

                        whereclause += " AND (Tablename.item_no = @ItemNo)";
                    }

                    //Add customer number portion
                    if (!string.IsNullOrEmpty(customerNo))
                    {
                        paramCustomerNo = new SqlParameter();
                        paramCustomerNo.ParameterName = "@CustomerNo";
                        paramCustomerNo.Value = customerNo;

                        myCommand.Parameters.Add(paramCustomerNo);

                        whereclause = whereclause + " AND (Tablename.cus_no = @CustomerNo)";
                    }


                    string sqlquery = "SELECT * FROM TableName ";
                    sqlquery += whereclause;

                    //MessageBox.Show(sqlquery);

                    myCommand.CommandText = sqlquery;
                    myCommand.CommandType = CommandType.Text;
                    myCommand.Connection = myConnection;

                    this.exampleTableAdapter.ClearBeforeFill = true;
                    this.exampleTableAdapter.Adapter.SelectCommand = myCommand;
                    this.exampleTableAdapter.Adapter.Fill(this.ExampleDataSet.ExampleTable);
                }
            }
        }
        catch (System.Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }

I personally like the idea of coding a class that extends TableAdapter but this was a quick easy way of answering the OP's question. Sorry it took a year :)

Answer 2

Taken from here

To add a query to a TableAdapter in the Dataset Designer

Open a dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.

Right-click the desired TableAdapter, and select Add Query.

-or-

Drag a Query from the DataSet tab of the Toolbox onto a table on the designer.

The TableAdapter Query Configuration Wizard opens.

Complete the wizard; the query is added to the TableAdapter.