-1

I have a C# code that executes the SQL query. The input of the SQL query comes from a result of a stored procedure. The stored procedure returns a table 6 columns and 25,000 rows. I want to use this stored procedure result as an input to the SQL query. I am storing the stored procedure result in a DataSet. It looks like it's not the correct way to add DataSet results in the query. I need help here experts

Here is what I am doing. 

public static void ExecuteSQLQueryTest(string qryName, Boolean nestedQry, string spName)
{
    DataTable dtLocalResult = new DataTable();
    DataTable dtAzurelResult = new DataTable();
    DataTable dt = new DataTable();
    DataTable dt1 = new DataTable();
    List<string> lstring = new List<string>();
    aadSQLConnection.Open();
    //Connection to CPDashboard DB
    var cpdbconnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CATSQL"].ConnectionString);
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = cpdbconnection;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;

        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);
        }
    }

    string qryName = "Update A SET A.Environment = B.SC_MD_Environment, A.OSImage = B.SC_APMD_OSImage FROM" + dt + " A"
        + "INNER JOIN[CLOUD].cloudsql.caa.aa_AT_S B ON A.ServerName = B.SC_APMD_MachineName";

    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = aadSQLConnection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = qryName;
        using (SqlDataAdapter daa = new SqlDataAdapter(cmd))
        {
            daa.Fill(dt1);
        }
    }
}
Kei
  • 1,026
  • 8
  • 15
Umapathy
  • 23
  • 4
  • This won't work, you cannot just concatenate a data table *object* to a SQL Query *string* – Jon P Oct 30 '19 at 02:35
  • If you're getting 25K rows from a stored proc, I'm not sure how you would want to include that in a SQL query. – Rakesh Oct 30 '19 at 02:39
  • 2
    Your best option, in my opinion, write a new stored proc, this will internally call the original stored proc to populate a table variable, or temp table, which can then be used to perform the update. You then call the *new* stored proc from your C# app. – Jon P Oct 30 '19 at 02:51
  • It sounds like table valued parameters are what you are looking for (?): https://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net But note that returning 25,000 rows to the client, then resending them back to the server may not be a very efficient way of doing things - IF you aren't going to do anything else with that table on the client. – Moe Sisko Oct 30 '19 at 03:05
  • Why not use a `SELECT` query that selects from the result of the stored procedure? `SELECT * FROM my_stored_procedure`. This way you can skip the step of storing the SP result in a datatable. – Stefan Oct 30 '19 at 07:00

1 Answers1

0

Accessing cell value of your DataTable should be in this format

dt[row index][col index]

And also, we need to put space in between your Table and From

string qryName = "Update A SET A.Environment = B.SC_MD_Environment, A.OSImage = B.SC_APMD_OSImage "
        + "FROM " + dt[0][0] + " A"
        + "INNER JOIN[CLOUD].cloudsql.caa.aa_AT_S B ON A.ServerName = B.SC_APMD_MachineName";

If you are trying to update your datatable from your stored procedure output. you need to use select query for your cloud table instead, then update your datatable.

foreach(DataRow row in dt.Rows)
{
    string qryName = "select Environment from [CLOUD].cloudsql.caa.aa_AT_S B where A.ServerName = '"+row["ServerName"].ToString()+"'";

    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = aadSQLConnection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = qryName;
        using (SqlDataAdapter daa = new SqlDataAdapter(cmd))
        {
            .....update your datable here
        }
    }
}
Ed Bangga
  • 12,879
  • 4
  • 16
  • 30