Checking to see if a column exists in a datatable

R

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.ToString() == drProd.Table.Columns.ToString())

{

drDev = drProd;

}

}

drDev.EndEdit();





Console.WriteLine(dsDevelopment.HasChanges(DataRowState.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.ToString() ==
drProd.Table.Columns.ToString())

{

drDevAdd = drProd;

}

}

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

daDevelopment.Update(dsDevelopment, "Companies");

}

}

}
 
B

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 said:
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.ToString() ==
drProd.Table.Columns.ToString())

{

drDev = drProd;

}

}

drDev.EndEdit();





Console.WriteLine(dsDevelopment.HasChanges(DataRowState.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.ToString() ==
drProd.Table.Columns.ToString())

{

drDevAdd = drProd;

}

}

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

daDevelopment.Update(dsDevelopment, "Companies");

}

}

}
Click to expand...
 
R

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 said:
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.ToString() ==
drProd.Table.Columns.ToString())

{

drDev = drProd;

}

}

drDev.EndEdit();





Console.WriteLine(dsDevelopment.HasChanges(DataRowState.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.ToString() ==
drProd.Table.Columns.ToString())

{

drDevAdd = drProd;

}

}

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

daDevelopment.Update(dsDevelopment, "Companies");

}

}

}
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DataAdapter.update not updating 3
Verifying a column exists in a datareader 3

Top