Checking to see if a column exists in a datatable

**Balasubramanian Ramanathan** · Jun 16 '06, 04:35 PM

Re: Checking to see if a column exists in a datatable

You have already posted this....its better to databale.merge instead of
processing every rows. it has got missingschemaac tion...there you can ignore
the column which does't exist. Take a look at DataTable.Merge method in
MSDN
"RSH" <way_beyond_oop s@yahoo.com> wrote in message
news:el0SM7VkGH A.3484@TK2MSFTN GP03.phx.gbl...[color=blue]
> 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 strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
> CompanyID ='" + strSQLDestDatab ase + "'";
>
> 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 + "'";
>
>
>
> SqlDataAdapter daProduction = new SqlDataAdapter( strProductionSQ L,
> cnProductionSQL );
>
> DataSet dsProduction = new DataSet();
>
> daProduction.Fi ll(dsProduction , "Companies" );
>
> SqlCommandBuild er cbProduction = new SqlCommandBuild er(daProduction );
>
> SqlDataAdapter daDevelopment = new SqlDataAdapter( strDevelopmentS QL,
> cnDevelopmentSQ L);
>
> DataSet dsDevelopment = new DataSet();
>
> daDevelopment.F ill(dsDevelopme nt, "Companies" );
>
> SqlCommandBuild er cbDevelopment = new SqlCommandBuild er(daDevelopmen t);
>
> if (dsProduction.T ables["Companies"].Rows.Count > 0)
>
> {
>
> DataRow drProd;
>
> drProd = dsProduction.Ta bles["Companies"].Rows[0];
>
> if (dsDevelopment. Tables["Companies"].Rows.Count > 0)
>
> {
>
> // Update if row exists
>
> DataRow drDev;
>
> drDev = dsDevelopment.T ables["Companies"].Rows[0];
>
> drDev.BeginEdit ();
>
> for (Int16 i = 0; i < drProd.ItemArra y.Length; i++)
>
> {
>
>
> if (drDev.Table.Co lumns[i].ToString() ==
> drProd.Table.Co lumns[i].ToString())
>
> {
>
> drDev[i] = drProd[i];
>
> }
>
> }
>
> drDev.EndEdit() ;
>
>
>
>
>
> Console.WriteLi ne(dsDevelopmen t.HasChanges(Da taRowState.Modi fied));
>
> if (dsDevelopment. HasChanges(Data RowState.Modifi ed))
>
> {
>
> daDevelopment.U pdate(dsDevelop ment, "Companies" );
>
> }
>
> }
>
> else
>
> {
>
> //Insert If row doesn't exist
>
> DataRow drDevAdd;
>
> drDevAdd = dsDevelopment.T ables["Companies"].NewRow();
>
> for (Int16 i = 0; i < drProd.ItemArra y.Length; i++)
>
> {
>
> if (drDevAdd.Table .Columns[i].ToString() ==
> drProd.Table.Co lumns[i].ToString())
>
> {
>
> drDevAdd[i] = drProd[i];
>
> }
>
> }
>
> dsDevelopment.T ables["Companies"].Rows.Add(drDev Add);
>
> daDevelopment.U pdate(dsDevelop ment, "Companies" );
>
> }
>
> }
>
> }
>
>[/color]

**RSH** · Jun 16 '06, 05:45 PM

Re: Checking to see if a column exists in a datatable

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" <way_beyond_oop s@yahoo.com> wrote in message
news:el0SM7VkGH A.3484@TK2MSFTN GP03.phx.gbl...[color=blue]
> 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 strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
> CompanyID ='" + strSQLDestDatab ase + "'";
>
> 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 + "'";
>
>
>
> SqlDataAdapter daProduction = new SqlDataAdapter( strProductionSQ L,
> cnProductionSQL );
>
> DataSet dsProduction = new DataSet();
>
> daProduction.Fi ll(dsProduction , "Companies" );
>
> SqlCommandBuild er cbProduction = new SqlCommandBuild er(daProduction );
>
> SqlDataAdapter daDevelopment = new SqlDataAdapter( strDevelopmentS QL,
> cnDevelopmentSQ L);
>
> DataSet dsDevelopment = new DataSet();
>
> daDevelopment.F ill(dsDevelopme nt, "Companies" );
>
> SqlCommandBuild er cbDevelopment = new SqlCommandBuild er(daDevelopmen t);
>
> if (dsProduction.T ables["Companies"].Rows.Count > 0)
>
> {
>
> DataRow drProd;
>
> drProd = dsProduction.Ta bles["Companies"].Rows[0];
>
> if (dsDevelopment. Tables["Companies"].Rows.Count > 0)
>
> {
>
> // Update if row exists
>
> DataRow drDev;
>
> drDev = dsDevelopment.T ables["Companies"].Rows[0];
>
> drDev.BeginEdit ();
>
> for (Int16 i = 0; i < drProd.ItemArra y.Length; i++)
>
> {
>
>
> if (drDev.Table.Co lumns[i].ToString() ==
> drProd.Table.Co lumns[i].ToString())
>
> {
>
> drDev[i] = drProd[i];
>
> }
>
> }
>
> drDev.EndEdit() ;
>
>
>
>
>
> Console.WriteLi ne(dsDevelopmen t.HasChanges(Da taRowState.Modi fied));
>
> if (dsDevelopment. HasChanges(Data RowState.Modifi ed))
>
> {
>
> daDevelopment.U pdate(dsDevelop ment, "Companies" );
>
> }
>
> }
>
> else
>
> {
>
> //Insert If row doesn't exist
>
> DataRow drDevAdd;
>
> drDevAdd = dsDevelopment.T ables["Companies"].NewRow();
>
> for (Int16 i = 0; i < drProd.ItemArra y.Length; i++)
>
> {
>
> if (drDevAdd.Table .Columns[i].ToString() ==
> drProd.Table.Co lumns[i].ToString())
>
> {
>
> drDevAdd[i] = drProd[i];
>
> }
>
> }
>
> dsDevelopment.T ables["Companies"].Rows.Add(drDev Add);
>
> daDevelopment.U pdate(dsDevelop ment, "Companies" );
>
> }
>
> }
>
> }
>
>[/color]

Checking to see if a column exists in a datatable

Checking to see if a column exists in a datatable

Comment

Comment