By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

Verifying a column exists in a datareader

P: n/a
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());

}
Jun 16 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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());

}

Jun 16 '06 #2

P: n/a
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());

}

Jun 16 '06 #3

P: n/a
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());

}

Jun 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.