Checking to see if a column exists in a datatable

[RSH]

Hi,

Iam struggling with an application where I am trying to transfer a datarow
from one sql server to another instance of sql server. The schmeas may be
slightly different and I am getting an exception when they are different.
Is there anyway i can modify the code below so that if the schemas are
different I can drop the offending column in the appropriate datatable?

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 + "'";

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 + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction, "Companies");

SqlCommandBuilder cbProduction = new SqlCommandBuilder(daProduction);

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment, "Companies");

SqlCommandBuilder cbDevelopment = new SqlCommandBuilder(daDevelopment);

if (dsProduction.Tables["Companies"].Rows.Count > 0)

{

DataRow drProd;

drProd = dsProduction.Tables["Companies"].Rows[0];

if (dsDevelopment.Tables["Companies"].Rows.Count > 0)

{

// Update if row exists

DataRow drDev;

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

for (Int16 i = 0; i < drProd.ItemArray.Length; i++)

{
if (drDev.Table.Columns[i].ToString() == drProd.Table.Columns[i].ToString())

{

drDev[i] = drProd[i];

}

}

drDev.EndEdit();

Console.WriteLine(dsDevelopment.HasChanges(DataRow State.Modified));

if (dsDevelopment.HasChanges(DataRowState.Modified))

{

daDevelopment.Update(dsDevelopment, "Companies");

}

}

else

{

//Insert If row doesn't exist

DataRow drDevAdd;

drDevAdd = dsDevelopment.Tables["Companies"].NewRow();

for (Int16 i = 0; i < drProd.ItemArray.Length; i++)

{

if (drDevAdd.Table.Columns[i].ToString() ==
drProd.Table.Columns[i].ToString())

{

drDevAdd[i] = drProd[i];

}

}

dsDevelopment.Tables["Companies"].Rows.Add(drDevAdd);

daDevelopment.Update(dsDevelopment, "Companies");

}

}

}

[Balasubramanian Ramanathan]

You have already posted this....its better to databale.merge instead of
processing every rows. it has got missingschemaaction...there you can ignore
the column which does't exist. Take a look at DataTable.Merge method in
MSDN
"RSH" <wa*************@yahoo.com> wrote in message
news:el**************@TK2MSFTNGP03.phx.gbl...

Hi,

Iam struggling with an application where I am trying to transfer a datarow
from one sql server to another instance of sql server. The schmeas may be
slightly different and I am getting an exception when they are different.
Is there anyway i can modify the code below so that if the schemas are
different I can drop the offending column in the appropriate datatable?

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 + "'";

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 + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction, "Companies");

SqlCommandBuilder cbProduction = new SqlCommandBuilder(daProduction);

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment, "Companies");

SqlCommandBuilder cbDevelopment = new SqlCommandBuilder(daDevelopment);

if (dsProduction.Tables["Companies"].Rows.Count > 0)

{

DataRow drProd;

drProd = dsProduction.Tables["Companies"].Rows[0];

if (dsDevelopment.Tables["Companies"].Rows.Count > 0)

{

// Update if row exists

DataRow drDev;

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

for (Int16 i = 0; i < drProd.ItemArray.Length; i++)

{
if (drDev.Table.Columns[i].ToString() ==
drProd.Table.Columns[i].ToString())

{

drDev[i] = drProd[i];

}

}

drDev.EndEdit();

Console.WriteLine(dsDevelopment.HasChanges(DataRow State.Modified));

if (dsDevelopment.HasChanges(DataRowState.Modified))

{

daDevelopment.Update(dsDevelopment, "Companies");

}

}

else

{

//Insert If row doesn't exist

DataRow drDevAdd;

drDevAdd = dsDevelopment.Tables["Companies"].NewRow();

for (Int16 i = 0; i < drProd.ItemArray.Length; i++)

{

if (drDevAdd.Table.Columns[i].ToString() ==
drProd.Table.Columns[i].ToString())

{

drDevAdd[i] = drProd[i];

}

}

dsDevelopment.Tables["Companies"].Rows.Add(drDevAdd);

daDevelopment.Update(dsDevelopment, "Companies");

}

}

}

[RSH]

Thanks!

Actually if you read carefully my other post was asking about a DataReader,
this one was asking about a DataTable. i changed the code to use a
datatable instead of the original datareader based on input on the other
thread.

Thank you for your suggestion, it worked great!
Ron

"RSH" <wa*************@yahoo.com> wrote in message
news:el**************@TK2MSFTNGP03.phx.gbl...

Hi,

Iam struggling with an application where I am trying to transfer a datarow
from one sql server to another instance of sql server. The schmeas may be
slightly different and I am getting an exception when they are different.
Is there anyway i can modify the code below so that if the schemas are
different I can drop the offending column in the appropriate datatable?

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 + "'";

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 + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction, "Companies");

SqlCommandBuilder cbProduction = new SqlCommandBuilder(daProduction);

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment, "Companies");

SqlCommandBuilder cbDevelopment = new SqlCommandBuilder(daDevelopment);

if (dsProduction.Tables["Companies"].Rows.Count > 0)

{

DataRow drProd;

drProd = dsProduction.Tables["Companies"].Rows[0];

if (dsDevelopment.Tables["Companies"].Rows.Count > 0)

{

// Update if row exists

DataRow drDev;

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

for (Int16 i = 0; i < drProd.ItemArray.Length; i++)

{
if (drDev.Table.Columns[i].ToString() ==
drProd.Table.Columns[i].ToString())

{

drDev[i] = drProd[i];

}

}

drDev.EndEdit();

Console.WriteLine(dsDevelopment.HasChanges(DataRow State.Modified));

if (dsDevelopment.HasChanges(DataRowState.Modified))

{

daDevelopment.Update(dsDevelopment, "Companies");

}

}

else

{

//Insert If row doesn't exist

DataRow drDevAdd;

drDevAdd = dsDevelopment.Tables["Companies"].NewRow();

for (Int16 i = 0; i < drProd.ItemArray.Length; i++)

{

if (drDevAdd.Table.Columns[i].ToString() ==
drProd.Table.Columns[i].ToString())

{

drDevAdd[i] = drProd[i];

}

}

dsDevelopment.Tables["Companies"].Rows.Add(drDevAdd);

daDevelopment.Update(dsDevelopment, "Companies");

}

}

}