Dailycode.info

Short solution for short problems

How to get a dataset with variable SQL from the entity framework

When you are working with the EF, you will finally get to the point that you need some joined data from the database that could be based on variable SQL queries. In our case we can define SQL queries in the database that will decide what to show in grids. Since this data is combining several tables from a non relational DB, its not returning entity data. So I found a good way to get a dataset and still use the entity framework connection. So no new connection logic to be instantiated:

 

public DataSet ExecuteStoreQuery(string commandText)

{

    DataSet retVal = new DataSet();

    EntityConnection entityConn = (EntityConnection)MyContext.Connection;

    SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;

    SqlCommand cmdReport = new SqlCommand(commandText, sqlConn);

    SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);

    using (cmdReport)

    {

        cmdReport.CommandType = CommandType.Text;

        daReport.Fill(retVal);

    }                  



    return retVal;

}

 

You should only use this for view purposes only!!!


How to rebind a Select on a datatable?

If you have for example a data grid that binds to a dataset or data table and you perform a Select(“”) on the table, it can happen that you lose your bindings and the row is shown but the values not:
  
The code I used was like this:

grdProjectOverview.DataSource = dt.Select("ProjectEndDate > '" + DateTime.Now.ToShortDateString() + "'");

The system finds the correct number of rows and displays them, but the values are not in there. I solved it by using a dataview like this:

grdProjectOverview.DataSource = new DataView(dt, "ProjectEndDate > '" + DateTime.Now.ToShortDateString() + "'", String.Empty, DataViewRowState.CurrentRows);

Now the binding works and the values are shown. It  can really be a pain to get this one solved. Unfortunately as always I do not have time to dig into this deeper. So just the quick solution: