Get data from a dynamic query using a DataSet in c#

Question

I'm having a couple of days with a dynamic query that doesn't bring me data to show in a report. What I have done is to make a code that helps me build a dynamic query using the parameters that I get from a windows form. For example, this is one of the parts of the code I use to get my table:

DataTable result = new DataTable();
String sqlQuery = "SELECT";
String myTable = getInfoReport();
SqlCommand myCommand;

switch (myTable)
{
    case "tbProducts":
        sqlQuery += String.Format(" DateTime AS [{0}]", _DBFields.Date);
        sqlQuery += String.Format(",ProductID AS [{0}]", _DBFields.MatNr);
        sqlQuery += String.Format(",Material AS [{0}]", _DBFields.Material);
        break;
    case "tbErrors":
        sqlQuery += String.Format(" DateTime AS [{0}]", _DBFields.Date);
        sqlQuery += String.Format(",Message  AS [{0}]", _DBFields.Message);
        break;
}
sqlQuery += " FROM dbo." + myTable;
sqlQuery += " WHERE (DateTime between @StartDate and @EndDate)";
sqlQuery += " ORDER BY DateTime";

myCommand = new SqlCommand(sqlQuery);
myCommand.CommandType = CommandType.Text;

myCommand.Parameters.AddWithValue("@StartDate", myReportData.StartDate);
myCommand.Parameters.AddWithValue("@EndDate", myReportData.EndDate);

if (myTable.Equals("tbErrors")) { result = myErrorsAdapter.fillErrorsDataTable(myCommand); }
else { result = myProductsAdapter.fillProductsDataTable(myCommand); }

Then I have the next code inside my DataSet Class:

partial class tbProductsTableAdapter
{
    internal DataTable fillProductsDataTable(SqlCommand myCommand)
    {
        MyDataSet.tbProductsDataTable result = new MyDataSet.tbProductsDataTable();

           try
           {
               this.Connection.Open();
               myCommand.Connection = this.Connection;
               this.Adapter.SelectCommand = myCommand;

               result.Load(this.Adapter.SelectCommand.ExecuteReader());

               this.Connection.Close();
           }
           catch (Exception e)
           {
           }

        return result;
    }
}

My problem comes that when I tried to load the data in the DataTable I declared at the beginning, the adapter doesn't execute the query and doesn't bring me data that I want to show. I am a bit new to C# and I'm trying to get a solution for a long while, but I get stuck trying to check other problems that are similar as mine.

Thanks in advance for the help!

You should give the `EntityFramework` a try instead of writing "dynamic SQL-Queries" - http://msdn.microsoft.com/en-us/library/vstudio/bb399182(v=vs.100).aspx — Jan P., May 24 '13 at 11:19
I heard of it, and I have readen of it, but half of the application is already done, so what I did is adding the DataSets as a solution to make a connection with the database. — Marialvy Martínez, May 24 '13 at 11:22
Possible duplicate: http://stackoverflow.com/questions/4099652/c-sharp-how-do-you-return-dataset-from-sqldatareader — Jan P., May 24 '13 at 11:26
I have tried with the `Adapter.Fill()` but it happens the same. But I think that now I'm a bit closer to the solution... — Marialvy Martínez, May 24 '13 at 11:36

score 2 · Accepted Answer · answered May 24 '13 at 12:28

I found what's going on: It has to be with the values of the parameters that I sent to my query. Sometimes are good but sometimes not, so I have to keep an eye on them. There is something also that I found when I use the type tbProductsDataTable. I changed it to a simple DataTable type and works perfectly also. So the code of the second procedure is:

partial class tbProductsTableAdapter
{
    internal DataTable fillProductsDataTable(SqlCommand myCommand)
    {
        DataTable result = new DataTable();

           try
           {
               this.Connection.Open();
               myCommand.Connection = this.Connection;
               this.Adapter.SelectCommand = myCommand;

               this.Adapter.fill(result);

               this.Connection.Close();
           }
           catch (Exception e)
           {
           }

        return result;
    }
}

Get data from a dynamic query using a DataSet in c#

1 Answers1