Verifying a column exists in a datareader

[RSH]

Hi,

I have a situation where I have two datareaders, and I want to make sure any
given field from Datareader A exists in Datareader B before I can do
anything with that column.
I tried the code below but I get an exception thrown because the column
doesn't exist in Datareader B.

I'm not opposed to using another method but the same thing happened when
using a Dataset. How do I do this??
Thanks,
Ron

SqlConnection cnProductionSQL = new SqlConnection(" Data Source=" +
cmbSourceServer .SelectedItem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnProductionSQL .Open();

String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdProd = new SqlCommand(strP roductionSQL,cn ProductionSQL);

SqlDataReader dtrProd = cmdProd.Execute Reader();

SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=" +
cmbDestinationS erver.SelectedI tem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnDevelopmentSQ L.Open();

String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdDev = new SqlCommand(strD evelopmentSQL, cnDevelopmentSQ L);

SqlDataReader dtrDev = cmdDev.ExecuteR eader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder() ;

sbUpdateQuery.A ppend("UPDATE [Global].[dbo].[Companies] SET ");

int Ordinal;

dtrDev.Read();

while (dtrProd.Read() )

{

for(int i = 0; i < dtrProd.FieldCo unt; i++)

{

if (dtrProd.GetNam e(i)!= "ID")

{

Ordinal = dtrDev.GetOrdin al(dtrProd.GetN ame(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.A ppend(",");

}

sbUpdateQuery.A ppend(dtrProd.G etName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.A ppend("WHERE CompanyID = '" + strSQLDestDatab ase + "'");

dtrDev.Close();

dtrProd.Close() ;

//dtrDev = cmdDev.ExecuteN onQuery(sbUpdat eQuery.ToString ());

}

[Balasubramanian Ramanathan]

You can do in 2 ways

1) Use GetSchemaTable( ) method to get the metadata about the sqldatareader
and check the datatable or use the datatable in your loop
2) use try catch to catch the error and if there is an error the column not
exists...not a recommended method

"RSH" <wa************ *@yahoo.com> wrote in message
news:eK******** ******@TK2MSFTN GP03.phx.gbl...

Hi,

I have a situation where I have two datareaders, and I want to make sure
any given field from Datareader A exists in Datareader B before I can do
anything with that column.
I tried the code below but I get an exception thrown because the column
doesn't exist in Datareader B.

I'm not opposed to using another method but the same thing happened when
using a Dataset. How do I do this??
Thanks,
Ron

SqlConnection cnProductionSQL = new SqlConnection(" Data Source=" +
cmbSourceServer .SelectedItem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnProductionSQL .Open();

String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdProd = new SqlCommand(strP roductionSQL,cn ProductionSQL);

SqlDataReader dtrProd = cmdProd.Execute Reader();

SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=" +
cmbDestinationS erver.SelectedI tem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnDevelopmentSQ L.Open();

String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdDev = new SqlCommand(strD evelopmentSQL, cnDevelopmentSQ L);

SqlDataReader dtrDev = cmdDev.ExecuteR eader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder() ;

sbUpdateQuery.A ppend("UPDATE [Global].[dbo].[Companies] SET ");

int Ordinal;

dtrDev.Read();

while (dtrProd.Read() )

{

for(int i = 0; i < dtrProd.FieldCo unt; i++)

{

if (dtrProd.GetNam e(i)!= "ID")

{

Ordinal = dtrDev.GetOrdin al(dtrProd.GetN ame(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.A ppend(",");

}

sbUpdateQuery.A ppend(dtrProd.G etName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.A ppend("WHERE CompanyID = '" + strSQLDestDatab ase + "'");

dtrDev.Close();

dtrProd.Close() ;

//dtrDev = cmdDev.ExecuteN onQuery(sbUpdat eQuery.ToString ());

}

[Marina Levit [MVP]]

Use datatables instead. Then you can check the Columns collection to see if
a particular column exists.

"RSH" <wa************ *@yahoo.com> wrote in message
news:eK******** ******@TK2MSFTN GP03.phx.gbl...

Hi,

I have a situation where I have two datareaders, and I want to make sure
any given field from Datareader A exists in Datareader B before I can do
anything with that column.
I tried the code below but I get an exception thrown because the column
doesn't exist in Datareader B.

I'm not opposed to using another method but the same thing happened when
using a Dataset. How do I do this??
Thanks,
Ron

SqlConnection cnProductionSQL = new SqlConnection(" Data Source=" +
cmbSourceServer .SelectedItem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnProductionSQL .Open();

String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdProd = new SqlCommand(strP roductionSQL,cn ProductionSQL);

SqlDataReader dtrProd = cmdProd.Execute Reader();

SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=" +
cmbDestinationS erver.SelectedI tem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnDevelopmentSQ L.Open();

String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdDev = new SqlCommand(strD evelopmentSQL, cnDevelopmentSQ L);

SqlDataReader dtrDev = cmdDev.ExecuteR eader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder() ;

sbUpdateQuery.A ppend("UPDATE [Global].[dbo].[Companies] SET ");

int Ordinal;

dtrDev.Read();

while (dtrProd.Read() )

{

for(int i = 0; i < dtrProd.FieldCo unt; i++)

{

if (dtrProd.GetNam e(i)!= "ID")

{

Ordinal = dtrDev.GetOrdin al(dtrProd.GetN ame(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.A ppend(",");

}

sbUpdateQuery.A ppend(dtrProd.G etName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.A ppend("WHERE CompanyID = '" + strSQLDestDatab ase + "'");

dtrDev.Close();

dtrProd.Close() ;

//dtrDev = cmdDev.ExecuteN onQuery(sbUpdat eQuery.ToString ());

}

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Unfortunately DataReader does not has a Columns collection,
You have 3 options:
1- Use DataReder.GetNa me( index ) , you coudl do a method like:
int GetIndex( DataReader dr, string name)
{ for(int i=0; i< dr.FieldCount; i++)
if ( dr.GetName(i) == name ) return i;
return -1;
}

2- Use DataReader.GetS chemaTable :
if ( DataReader.GetS chemaTable.Colu mns[ name] == null ) // does
not exist

3- try to access it and catch the exception

--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"RSH" <wa************ *@yahoo.com> wrote in message
news:eK******** ******@TK2MSFTN GP03.phx.gbl...

Hi,

I have a situation where I have two datareaders, and I want to make sure
any given field from Datareader A exists in Datareader B before I can do
anything with that column.
I tried the code below but I get an exception thrown because the column
doesn't exist in Datareader B.

I'm not opposed to using another method but the same thing happened when
using a Dataset. How do I do this??
Thanks,
Ron

SqlConnection cnProductionSQL = new SqlConnection(" Data Source=" +
cmbSourceServer .SelectedItem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnProductionSQL .Open();

String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdProd = new SqlCommand(strP roductionSQL,cn ProductionSQL);

SqlDataReader dtrProd = cmdProd.Execute Reader();

SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=" +
cmbDestinationS erver.SelectedI tem + "; Integrated Security=SSPI; Initial
Catalog=Master" );

cnDevelopmentSQ L.Open();

String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlCommand cmdDev = new SqlCommand(strD evelopmentSQL, cnDevelopmentSQ L);

SqlDataReader dtrDev = cmdDev.ExecuteR eader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder() ;

sbUpdateQuery.A ppend("UPDATE [Global].[dbo].[Companies] SET ");

int Ordinal;

dtrDev.Read();

while (dtrProd.Read() )

{

for(int i = 0; i < dtrProd.FieldCo unt; i++)

{

if (dtrProd.GetNam e(i)!= "ID")

{

Ordinal = dtrDev.GetOrdin al(dtrProd.GetN ame(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.A ppend(",");

}

sbUpdateQuery.A ppend(dtrProd.G etName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.A ppend("WHERE CompanyID = '" + strSQLDestDatab ase + "'");

dtrDev.Close();

dtrProd.Close() ;

//dtrDev = cmdDev.ExecuteN onQuery(sbUpdat eQuery.ToString ());

}