Datatable.clone - C#

[Guest]

Hi,
Using the clone method I copied the structure and constraints of a table.
Now using this clone table, can I create a new table in the database ?

If yes, is it by the update method of the dataadapter ?

Help appreciated.

Thank You

 

[Cor]

No, there is no createcommand in the dataadapter.

Only
Insert,
Delete
Update
Select

Cor

 

[Guest]

Thank You. Could anyone suggest a way to do this ?
Should it be done programmatically - building the query by reading the clone table ? Or is there an easy way to do it ?

Thank You
David

 

[Cor]

Hi David,

A very simple table (watch typos because I did change it here from VB in C#)
\\\\
OleDb.OledbCommand cmd = new OleDb.OleDbCommand( "CREATE TABLE tbl1 (a int
NOT NULL,b Char(20) CONSTRAINT [pk_a] PRIMARY KEY (a))", conn);
cmd.ExecuteNonQuery();
///
When it is SQL change OleDB.Oledb for SQLClient.SQL

I hope this helps?

Cor

 

[Cor]

Hi David,

I never did it but why not just loop throug your datatable with a foreach
datacolumn in de datatable.columns

And then append the data from that to the sample I did send you, cannot be
that hard to make.

I would try it.

Cor

 

[HKM]

well it works, only problem if ur size gets bigger then
you could eat up lot of memory on a target machine.

here is the code that im using to achieve pretty much same
thing that you want.

In following code, I have a hashtable table
(sourceColumnCache) that holds the columns to be selected
for output (second table). It loops thru them and copies
data from the rows in the respective columns.



for(row=0;row<originalTable.Rows.Count;row++)
{
DataRow targetRow
= targetDataSet.Tables[0].NewRow();

((ProgressBarDialog)pbd).performStep();
for(int
columns=0;columns<originalTable.Columns.Count;columns++)
{
if
(sourceColumnCache.ContainsKey(originalTable.Columns
[columns].ColumnName))
{

targetRow[originalTable.Columns
[columns].ColumnName] = originalTable.Rows[row][columns];

foundFlag = true;
}

}
if(foundFlag ==
true)
{
// set
product provider id here
targetRow
[DbDefs.product_provider_id] = providerId;
if
(sourceFileName == null)

MessageBox.Show("error", "Error");
targetRow
[DbDefs.commission_file_name] = sourceFileName;
targetRow
[DbDefs.policy_event_id] =
DbDefs.commission_upload_event_id;
targetRow
[DbDefs.commission_file_month] =
commissionFileDate.ToShortDateString();
targetRow
[DbDefs.upload_file_month] = DateTime.Now.ToLongDateString
();
targetRow
[DbDefs.commission_reconcile_attempt] = 0;

targetDataSet.Tables[0].Rows.Add(targetRow);
}
foundFlag = false;
}


Thanks,
-HKM

 

[Guest]

Thank You HKM. I was going through the code and have a few doubts.It would be great if you could answer yes/no for my questions

1) SourceColumnCache(the hashtable) and columns of originalTable have the same column names ? Did you mean to take out the column names from the originalTable and put them in the SourceColumnCache

2)Is targetRow referring to the row of the second table which i am trying to build from the originalTable

3) sourceColumnCache.ContainsKey(originalTable.Columns [columns].ColumnName - I believe this is doing a string comparison of the column names .Right

4) targetRow[originalTable.Columns[columns].ColumnName] = originalTable.Rows[row][columns];

Is this the statement which adds the column name to the second table

5) product provider id - this is the connection

Thank You HK
Davi

 

[Cor]

Hi HKM,

In my opinion does this something as a copy a datatable and does not create
a new table in a database.

But maybe was that what was wanted and did I understand it wrong.

(The methode to do that is much easier by the way. When it is a complete
table you can do copy)

And when it is a part you can do a for loop thru the rows with a copy of a
selected sets of items. If that are a lot, you can place that in a simple
array, therefore you do not need a hastable.

Cor

 

[HKM]

answers inline..

Thank You HKM. I was going through the code and have a

few doubts.It would be great if you could answer yes/no
for my questions.

1) SourceColumnCache(the hashtable) and columns of
originalTable have the same column names ? Did you mean
to take out the column names from the originalTable and
put them in the SourceColumnCache ?

Okay, SourceColumnCache only holds the column names and
respective ordinal of the Database table. Scenario is I
have table A with 15 columns and my target Database table
has only 6 columns, these 6 column names and ordinal will
be in the hashtable yes their names are same.

2)Is targetRow referring to the row of the second table

which i am trying to build from the originalTable ?

Yes, Target row is derived from the table in the database
which is target table in this case.

3) sourceColumnCache.ContainsKey(originalTable.Columns

[columns].ColumnName - I believe this is doing a string
comparison of the column names .Right ?

Correct, this compares TABLE A column names with database
table columns.

4) targetRow[originalTable.Columns

[columns].ColumnName] = originalTable.Rows[row][columns]; -

Above statement copies the data from the TABLE A into the
table that is target/database table.

Is this the statement which adds the column name to the

second table ?

This is not adding column to second table but this is
adding data from column A from table A to column A to
table B (target table).

5) product provider id - this is the connection ?

Mistake, it is context sensiteive, since i copied the code
directly from development environment did not realize it.
Sorry for that.

Thank You HKM
David

Thanks,
-HKM

 

[HKM]

-----Original Message-----
Hi HKM,

In my opinion does this something as a copy a datatable and does not create
a new table in a database.

Yah, to be precise selective copy with column datatype
translation forced onto underlying layer.

But maybe was that what was wanted and did I understand

it wrong.

?

(The methode to do that is much easier by the way. When

it is a complete table you can do copy)

MY problem was, I construct TABLE A by reading bunch of
delimited text files. Now ODBC/JET drivers are really not
intelligent enough to understand the datatypes on most of
the cases. On the top of this, my target schema in the
database is not flexible to accept numbers in the text
format. Next point is , you cannot change TABLE A column
datatypes once it has data. This forced me into situation
where I'll create a typed dataset with schema from the
database and feed data into [row][column] fashion. This
implementation then delegates the responsibility of
conversion of text data into respective format as that of
target table in the database.

And when it is a part you can do a for loop thru the
rows with a copy of a
selected sets of items. If that are a lot, you can place that in a simple
array, therefore you do not need a hastable.

ACtually hashtable just holds that column name that needs
to be transfered to target table. I needed a lookup
mechanism since I want to upload selected columns only. So
when u come across column x in the TABLE A (build by
reading text files) all you do is just check if current
column is there in the target schema if yes then copy the
data otherwise dont.

Cor

Thanks,
-HKM