Populating WPF datagrid from SQLCommand using stored procedure

Question

I'm currently trying to populate a datagrid using a member of a class that uses SQLCommand to execute a stored procedure and return the results.

My class member (and where I believe the issues lies) is:

public DataView DisplayHealthIndicator(DateTime startDate, DateTime endDate)
{
    string queryString =
        "DECLARE @RC int"
        + "DECLARE @date_from datetime = dateadd(day, 0, datediff(day, 0, getdate()))"
        + "DECLARE @date_to datetime = dateadd(day, 0, datediff(day, 0, getdate()))"

        + "EXECUTE @RC = [Testing].[marlin].[support_retrieve_workflow_history] "
            + "@date_from "
            + ",@date_to"
        + "GO";

    using (SqlConnection connection = new SqlConnection(GetConnectionString()))
    {
        using (var cmd = new SqlCommand(queryString, connection))
        {
            connection.Open();

            var reader = cmd.ExecuteReader();

            var dt = new DataTable();
            dt.Load(reader);

            return dt.DefaultView;
        }
    }

}

and I'm calling this member using:

var db = new DatabaseHandle();
        dataGridWorkflow.ItemsSource = db.DisplayHealthIndicator(DateTime.Now, DateTime.Now);

However! I'm currently receiving the error:

Incorrect syntax near @date_from

Must declare the scalar variable @RC

To a degree I understand the error - I believe that I can't declare variables in my sqlQuery string... but then, how do I do this?

I'm fairly sure that it doesn't have any bearing on this, but in case it does, this is the contents of the stored procedure:

create procedure marlin.support_retrieve_workflow_history
(
    @date_from datetime,
    @date_to datetime
)
as
select  dateadd(day, 0, datediff(day, 0, e.event_date)) as 'Date',
        c.setting_secondary 'Workflow Category' ,
        d.setting_main as 'Error Type' ,
        sum(e.event_count) as 'Total'
from    marlin.support_events e
        inner join marlin.support_config c
            on e.event_category = c.setting_code
            and c.config_code = 60
        inner join marlin.support_config d
            on e.event_type = d.setting_code
            and d.config_code = 70
where   e.event_date between @date_from and @date_to
group by 
        e.event_date,
        c.setting_secondary ,
        d.setting_main

Answer 1

This post is a bit old...But, I wanted to share how I am dynamically populating the WPF DataGrid

private void Fill_DataGrid_ServiceName()
    {
        this.Cursor = Cursors.Wait;

        // create an instance
        DatabaseClass objDatabaseClass = new DatabaseClass(_connectionString);

        // if we are able to open and close the SQL Connection then proceed
        if (objDatabaseClass.CheckSQLConnection())
        {
            try
            {
                // create an instance. variable 'con' will hold the instance
                SqlConnection con = new SqlConnection(_connectionString);

                con.Open();

                // Query to populate the Grid
                string Query = @"SELECT 
                                cm_mktdata_mdsservice_fits_to_finance_id_unique AS [Id Unique]
                                ,cm_mktdata_mdsservice_fits_to_finance_MDSService_fits AS [FITS MDSService]
                                ,cm_mktdata_mdsservice_fits_to_finance_MDSService_finance AS [Finance MDSService]
                                ,'[ ' + CONVERT(varchar, user_detail_user_info_id_user) + ' ] ' + user_detail_user_info_nm_login AS [Last Modified By]
                                ,cm_mktdata_mdsservice_fits_to_finance_record_version AS [Record Version]
                                ,cm_mktdata_mdsservice_fits_to_finance_dt_modified AS [Dt Modified]
                                ,cm_mktdata_mdsservice_fits_to_finance_ind_active AS [Ind Active]
                            FROM 
                                dbo.v_mktdata_ui_mdsservice_fits_to_finance_detail
                            WHERE
                                cm_mktdata_mdsservice_fits_to_finance_ind_operational = 1
                            ORDER BY
                                cm_mktdata_mdsservice_fits_to_finance_MDSService_fits";


                SqlCommand createCommand = new SqlCommand(Query, con);
                createCommand.ExecuteNonQuery();

                // transfer the results of createCommand to the dataGrid
                SqlDataAdapter dataAdapter = new SqlDataAdapter(createCommand);
                DataTable dt = new DataTable("vcm_mktdata_mdsservice_fits_to_finance");
                dataAdapter.Fill(dt);
                dataGrid_ServiceName.ItemsSource = dt.DefaultView;
                dataAdapter.Update(dt);

                con.Close();

                // Enable the Refresh Grid Button
                btn_RefreshGrid_ServiceName.IsEnabled = true;


                // get DataGrid row count                    
                lbl_dataGrid_RowCount_ServiceName.Content = dataGrid_ServiceName.Items.Count.ToString() + " rows";

                //return true;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
                //return false;
            }
        }
        else
        {
            MessageBox.Show("Connection not established to the SQL Server. " + Environment.NewLine + "The SQL Server may be offline or valid credentials are not yet granted.", "SQL Server Connection Error", MessageBoxButton.OK, MessageBoxImage.Error);

            this.Close();
        }

        this.Cursor = Cursors.Arrow;
    }

The DatabaseClass is as follows

   class DatabaseClass
{
    // Variables
    private string _connectionString = "";


    public DatabaseClass(string connectionString)
    {
        _connectionString = connectionString;  
    }

    /// Check to see if Connection can be opened
    /// 
    /// Returns True if the connection can be open else it returns False
    ///
    public bool CheckSQLConnection()
    {
        SqlConnection con = new SqlConnection(_connectionString);

        try 
        {           
            con.Open();
            con.Close();

            return true;
        }
        catch (SqlException ex)
        {       
           return false;
        }
    }

}

And for the connection string it will look as follows

public static string SQLDataSourceStr = "Data Source=MySQL-DB-DV;Initial Catalog=My_Data;Integrated Security=True";

Answer 2

cmd.Parameters["@ReturnValue"] contains the return value - you don't need to add a parameter in dynamic SQL

Add your parameters to the cmd

cmd.Parameters.AddWithValue("ParamName", Value);

Also change the cmd.CommandType (might not be called that, check members of cmd) to StoredProcedure

e.g.

using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
    using (var cmd = new SqlCommand(queryString, connection))
    {
        connection.Open();

        cmd.CommandType = ??.StoredProcedure; // Can't remember what enum name is prob SqlCommandType or something
        cmd.Parameters.AddWithValue("date_from", DateTime.blah.blah);
        cmd.Parameters.AddWithValue("date_to", DateTime.blah.blah);
        var reader = cmd.ExecuteReader();

        var dt = new DataTable();
        dt.Load(reader);

        return dt.DefaultView;
    }
}

Disclaimer: Some of these prop names, the name of the return value param might not be correct so check the docs :)