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)

{

.........

.........

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 200817 j von TDM

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.

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 200817 j von TDM

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.

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

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?

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?

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

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

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.

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'.

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.

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 ...?

Any new suggestions ?

Please refrain from thread pushing (see board rules).

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