CODEWITHSUNDEEP
c#ado.net
Sultan
Sultan

Reputation: 41

Error while using NextResult fuction with datareader

Error while using NextResult fuction with datareader

cannot get second table result and error on second NextResult line " invalid attempt to call nextresult when reader is closed "

using (SqlConnection myCon = DBCon)
{
    try
    {
       string Qry =  @"SELECT [OPSProcedure],[OPSInsertedOn],[OPSInsertedBy]
           FROM [Operation] where OPSID =  '" + opId + "';";

       Qry += @"SELECT  LKCPID  FROM dbo.ConcurrentProcedure  where CPOperationID = '" + opId + "';";

       Qry += @"SELECT  IOperaitonID  FROM  dbo.LkupIntraOperativeAdverseEvents   where IOperaitonID = '" + opId + "';";

        myCon.Open();
        SqlCommand myCommand = new SqlCommand(Qry, myCon);
        myCommand.CommandType = CommandType.Text;
        SqlDataReader sqlReader = myCommand.ExecuteReader();
        DataSet dr = new DataSet();
        if (sqlReader.HasRows)
        {
            dt1.Load(sqlReader);
            if(sqlReader.NextResult())
            {
            dt2.Load(sqlReader);
            }
            if (sqlReader.NextResult())
            {
            dt3.Load(sqlReader);
            }

        }
        sqlReader.Close();
   }
   catch (Exception ex)
   {

   }
}

What I have tried:

i have tried using below code for multiple result

Upvotes: 0

Views: 903

Answers (2)

Steve
Steve

Reputation: 216333

In this context I would simply use an SqlDataAdapter to make one single call and fill all your tables

using (SqlConnection myCon = DBCon)
{
    try
    {
       string Qry =  @"SELECT [OPSProcedure],[OPSInsertedOn],[OPSInsertedBy]
                       FROM [Operation] where OPSID =  @id;
                       SELECT  LKCPID  FROM dbo.ConcurrentProcedure  
                       where CPOperationID = @id;
                       SELECT IOperaitonID FROM dbo.LkupIntraOperativeAdverseEvents   
                       where IOperaitonID = @id";
        myCon.Open();
        SqlDataAdapter da = new SqlDataAdapter(Qry, myCon);
        da.SelectCommand.Parameter.Add("@id", SqlDbType.NVarChar).Value = opID;
        DataSet ds = new DataSet();
        da.Fill(ds);

        // Test...
        Console.WriteLine(ds.Tables[0].Rows.Count);
        Console.WriteLine(ds.Tables[1].Rows.Count);
        Console.WriteLine(ds.Tables[2].Rows.Count);

Notice also that you should never concatenate strings to build sql commands. Always use parameters.

Upvotes: 1

mjwills
mjwills

Reputation: 23975

DataTable.Load closes the sqlReader if sqlReader.IsClosed is false and NextResults returns false as per this forum.

As such, instead of:

if (sqlReader.NextResult())

you need to use:

if (!sqlReader.IsClosed && sqlReader.NextResult() && sqlReader.HasRows)

Upvotes: 2

Related Questions