adding a dataset to Oracle database in C#

[igurov]

Hello,

Sorry for writing in English but my German is still not good enough .So my question -

I am reading a dataset from .csv files and now I want to write this dataset to an oracle database. I want to create these datatables in the database and every time after that the tables should be only updated. I started with openning the oracle connection but I am not sure how to proceed further. Any ideas?

Thanks

Code:

public void Write(DataSet ds, string[] names)

{

string conStr = "User d=***;Password=***;DataSource=***";

Console.WriteLine("Exporting to dataset {0} ...", names[0]);

OracleConnection oraConnection = new OracleConnection(conStr);

try

{

oraConnection.Open();

Console.WriteLine("\nHello, Oracle Here!\n");

Console.WriteLine("Connection String: ");

Console.WriteLine(oraConnection.ConnectionString.ToString() + "\n");

Console.WriteLine("Current Connection State: ");

Console.WriteLine(oraConnection.State.ToString() + "\n");

Console.WriteLine("Oracle Database Server Version: ");

Console.WriteLine(oraConnection.ServerVersion.ToString());

}

catch (Exception ex)

{

Console.WriteLine("Error occured: " + ex.Message);

}

// create the command object

OracleCommand command = new OracleCommand(conStr);

foreach (DataTable table in ds.Tables)

{

.........

.........

[TDM]

I had this problem a few months ago.

Probably the rows won't be write in the database if you only use the Dataadapter.updata-Function.

(I think there is a property in the DataSet-Objectlist, which indicates what rows are updated,inserted or deleted)

I recommend you to iterate each table and row in the DataSet and send a querry for each row to the DB.

Edit: At the first time you should send the CREATE-Table-Query

Bearbeitet 24. Juni 2008 von TDM

[igurov]

Well, I did the same thing but exporting the dataset to microsoft access file(see code below). But now for the Oracle it is a little bit more complicated for me since I don't have experience with it:

public void Write(DataSet ds, string[] names)

{

string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + names[0] + ";";

Console.WriteLine("Exporting to database {0} ...", names[0]);

DbConnection connection = new OleDbConnection(conStr);

try

{

connection.Open();

}

catch (DbException e)

{

ConsoleEx.WriteException(true, e, "Unable to open database: {0}", names[0]);

throw;

}

DbCommand command = connection.CreateCommand();

foreach (DataTable table in ds.Tables)

{

Console.WriteLine("\tDeleting table: {0}", table.TableName);

// delete old tables

command.CommandText = string.Format("drop table {0}", table.TableName);

TryExecute(command, false);

// create new

Console.WriteLine("\tCreating new table: {0}", table.TableName);

string[] columnStrings = new string[table.Columns.Count];

for (int i = 0; i < table.Columns.Count; i++)

columnStrings = "`" + table.Columns.ColumnName + "`" + " varchar";

command.CommandText = string.Format("create table {0} ({1})",

table.TableName, string.Join(", \n", columnStrings));

TryExecute(command, true);

// add rows

for (int row = 0; row < table.Rows.Count; row++)

{

for (int col = 0; col < table.Columns.Count; col++)

columnStrings[col] = "'" + Convert.ToString(table.Rows[row].ItemArray[col]) + "'";

command.CommandText = string.Format("insert into {0} values ({1})",

table.TableName, string.Join(", \n", columnStrings));

TryExecute(command, true);

}

}

connection.Close();

So could you please help with writing the same thing but for Oracle database.

[TDM]

At first I have some Questions:

- Does really every columntype is a varchar?

- What does TryExecute do? Only send the Querry or does it send a commit too?

by the Way: please use the [ CODE ]-Tags (without space), then the code is easier to read.

Bearbeitet 24. Juni 2008 von TDM

[igurov]

Thanks for the help btw

1.- Does really every columntype is a varchar?

Well, yes. Despite this, in some columns I am storing only numbers (only used for a name of a tool).

2. What does TryExecute do? Only send the Querry or does it send a commit too?

Both of them. I am using it to execute the statement and populate the .mdb file. The code works fine. The mdb contains correctly all needed tables.

[igurov]

Here is the way I am calling the function after that in Execute.cmd file:

MdbExporter G:\2_Equipment\22_Wet\22k_ToolParameter\TEL\Convert\TelRecConvertor\TelRecConvertor\bin\Release\temp.mdb

[TDM]

hmm, the code should normaly work.

But only with MDB-Files - or do you set the first application argument (G:\[...]\temp .mdb) to the oracle path?

Do you get an exception, if you try to import in oracle?

[igurov]

I didn't get your first point- what do you mean by "do you set the first application argument (G:\[...]\temp .mdb) to the oracle path" ?

And also I don't know how to test it with the oracle database since the database is at remote server and I have only access to it(of course I can create tables and stuff like this), but I don't have the database in my local disk.

How can I test it then (G:\[...]\????) :mod:

Any ideas?

[igurov]

And the code works with mdb files for sure. I tested it like several times, but the bad thing is that I need the dataset in an Oracle database since I will use it in an internet application

[TDM]

I didn't get your first point- what do you mean by "do you set the first application argument (G:\[...]\temp .mdb) to the oracle path" ?

I mean this line

string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + names[0] + ";";

So the argument shouldn't be G:\... and so on, but the URI-Path to your Oracle-Server

[Argbeil]

Hi.

Is the structure of the csv always identical? Then write a SQL Script to create the DataTable in the OracleDB and use the OracleDataAdapter for filling afterwards.

Generating the create Script from a DataTable is dangerous, you have to check the columnnames for SQL-Injection attacs.

[igurov]

Hi.

Is the structure of the csv always identical? Then write a SQL Script to create the DataTable in the OracleDB and use the OracleDataAdapter for filling afterwards.

.

Actually there are around 10 different formats thats why I am using my own parser.

I mean this line

string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + names[0] + ";";

So the argument shouldn't be G:\... and so on, but the URI-Path to your Oracle-Server

Doesn' work. I changed my connection string and cmd file as well. Here is it: execute.cmd file -

MdbExporter ORA102.DRS.QIM.COM .It raised an exception -

Error: Unable to open database: ORA102.DRS.QIM.COM

Exception Information:

Could not find file 'E:\arbeit\Parser\TelRecConvertor\TelRecConvertor\bin\Releas

e\ORA102.DRS.QIM.COM'.

[TDM]

Erm, yeah, but you shoud tell them, where your table is.

It will never work if you use the path of the mdb as datasource at every time.

[igurov]

Erm, yeah, but you shoud tell them, where your table is.

It will never work if you use the path of the mdb as datasource at every time.

Well, I am not sure you got my previous point.

1 case) -working

c:\temp\TelRecConvertor IniImporter #E:\[..]\inidir.txt

MdbExporter E:\arbeit\db.mdb

2 case) -workig

c:\temp\TelRecConvertor IniImporter #E:\[..]\inidir.txt

MdbExporter E:\arbeit\whatever.mdb

The thing is that you have to have an mdb database that already exists in this place when u write the dataset in this database.

But what about when I want to write in Oracle ...?

[igurov]

Any new suggestions ?

[Klotzkopp]

Please refrain from thread pushing (see board rules).

[Amstelchen]

you're using the Jet OleDB provider. that cannot work because pure Jet is based on MDB files located on a filesystem path.

you'll have to use another OleDB provider in your connectionstring, preferably the Oracle OleDB provider which is stacked over the Oracle TNS client.

s'Amstel