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 strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdProd = new SqlCommand(strProductionSQL,cnProductionSQL);

SqlDataReader dtrProd = cmdProd.ExecuteReader();

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

cnDevelopmentSQL.Open();

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdDev = new SqlCommand(strDevelopmentSQL, cnDevelopmentSQL);

SqlDataReader dtrDev = cmdDev.ExecuteReader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder();

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

int Ordinal;

dtrDev.Read();

while (dtrProd.Read())

{

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

{

if (dtrProd.GetName(i)!= "ID")

{

Ordinal = dtrDev.GetOrdinal(dtrProd.GetName(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.Append(",");

}

sbUpdateQuery.Append(dtrProd.GetName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.Append("WHERE CompanyID = '" + strSQLDestDatabase + "'");

dtrDev.Close();

dtrProd.Close();

//dtrDev = cmdDev.ExecuteNonQuery(sbUpdateQuery.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**************@TK2MSFTNGP03.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 strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdProd = new SqlCommand(strProductionSQL,cnProductionSQL);

SqlDataReader dtrProd = cmdProd.ExecuteReader();

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

cnDevelopmentSQL.Open();

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdDev = new SqlCommand(strDevelopmentSQL, cnDevelopmentSQL);

SqlDataReader dtrDev = cmdDev.ExecuteReader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder();

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

int Ordinal;

dtrDev.Read();

while (dtrProd.Read())

{

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

{

if (dtrProd.GetName(i)!= "ID")

{

Ordinal = dtrDev.GetOrdinal(dtrProd.GetName(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.Append(",");

}

sbUpdateQuery.Append(dtrProd.GetName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.Append("WHERE CompanyID = '" + strSQLDestDatabase + "'");

dtrDev.Close();

dtrProd.Close();

//dtrDev = cmdDev.ExecuteNonQuery(sbUpdateQuery.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**************@TK2MSFTNGP03.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 strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdProd = new SqlCommand(strProductionSQL,cnProductionSQL);

SqlDataReader dtrProd = cmdProd.ExecuteReader();

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

cnDevelopmentSQL.Open();

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdDev = new SqlCommand(strDevelopmentSQL, cnDevelopmentSQL);

SqlDataReader dtrDev = cmdDev.ExecuteReader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder();

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

int Ordinal;

dtrDev.Read();

while (dtrProd.Read())

{

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

{

if (dtrProd.GetName(i)!= "ID")

{

Ordinal = dtrDev.GetOrdinal(dtrProd.GetName(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.Append(",");

}

sbUpdateQuery.Append(dtrProd.GetName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.Append("WHERE CompanyID = '" + strSQLDestDatabase + "'");

dtrDev.Close();

dtrProd.Close();

//dtrDev = cmdDev.ExecuteNonQuery(sbUpdateQuery.ToString());

}

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

Hi,

Unfortunately DataReader does not has a Columns collection,
You have 3 options:
1- Use DataReder.GetName( 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.GetSchemaTable :
if ( DataReader.GetSchemaTable.Columns[ 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**************@TK2MSFTNGP03.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 strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdProd = new SqlCommand(strProductionSQL,cnProductionSQL);

SqlDataReader dtrProd = cmdProd.ExecuteReader();

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

cnDevelopmentSQL.Open();

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlCommand cmdDev = new SqlCommand(strDevelopmentSQL, cnDevelopmentSQL);

SqlDataReader dtrDev = cmdDev.ExecuteReader();
if (dtrDev.HasRows == true)

{

StringBuilder sbUpdateQuery = new StringBuilder();

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

int Ordinal;

dtrDev.Read();

while (dtrProd.Read())

{

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

{

if (dtrProd.GetName(i)!= "ID")

{

Ordinal = dtrDev.GetOrdinal(dtrProd.GetName(i));

if (Ordinal > 0)

{

if (i != 1)

{

sbUpdateQuery.Append(",");

}

sbUpdateQuery.Append(dtrProd.GetName(i) + "='" + dtrProd[i].ToString() +
"'");

}

}

}

}

sbUpdateQuery.Append("WHERE CompanyID = '" + strSQLDestDatabase + "'");

dtrDev.Close();

dtrProd.Close();

//dtrDev = cmdDev.ExecuteNonQuery(sbUpdateQuery.ToString());

}