Simple example of inserting multiple records under C# (.Net) wanted.

forum.connectivity (2000-2005) & forum.td.connectivity (2005-2010)
Scott
Australia
Posts: 8
Joined: 13 Mar 2017, 23:31
Location: Sydney, Australia

Simple example of inserting multiple records under C# (.Net) wanted.

Post by Scott » 25 May 2009, 06:32

 Posted by:  Scott Booth 

Does it exist? I can't find it in the official documentation. Can anyone
help?

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 26 May 2009, 15:12

 Posted by:  Jim McNamara 

Can you give us a bit more detail?

What database?
Using a stored procedure?
Are you trying to load an array of stuff with one call?

Scott
Australia
Posts: 8
Joined: 13 Mar 2017, 23:31
Location: Sydney, Australia

Simple example of inserting multiple records under C# (.Net) wanted.

Post by Scott » 27 May 2009, 00:29

 Posted by:  Scott Booth 

Hi Jim

SQLBase using the .Net drivers (eg using Gupta.SQLBase.Data).
Not using a stored procedure.
I'm loading a table after pre-processing the data. I know how to do this
creating a new SQLBaseCommand object for each record, but not how to bind
multiple records.

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 27 May 2009, 15:10

 Posted by:  Jim McNamara 

I'll take a look at your C# class(es) - if they are small to see if I can
make any suggestions.

I'm working with Oracle, but the class structures should be similar. I know
from my experiences of the last 10 months or so that navagating C#
documentation for some basic 'how to' help can be very trying.

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 27 May 2009, 20:24

 Posted by:  Krzysztof Dorko 

Hi
I am not using SQLBase but I think the problem is in using IDBCommand
instead of IDBAdapter.
In .net you can handle database in two ways one dataset + dataadapter second
using command and set of execute methods like executescalar.
Using Commands and its execute methods you really have to go record by
record but the first method with dataadapter and dataset will allow you to
manage multiple rows. In other words will be faster since all loops are done
inside a dll (probably c++)
So in sqlbase case you should use SQLBaseDataAdapter and DataSet.
Go to documentation for datails about using dataadapters and dataset.
hth
Kris

Scott
Australia
Posts: 8
Joined: 13 Mar 2017, 23:31
Location: Sydney, Australia

Simple example of inserting multiple records under C# (.Net) wanted.

Post by Scott » 01 Jun 2009, 08:39

 Posted by:  Scott Booth 

Thanks Jim,

I'm taking an XML file & reading that into 4 DataSets, rearanging &
validating the data then pulling that out as collections of DataRows. It's
this information I want to insert into 4 different SQLBase tables.

But to simpliify things, lets say I had a For loop that did 5000 itterations
and I wanted to insert 5000 records without compiling the INSERT statement
5000 times. ie,
1) How do I setup the insert command at the start of the loop?
2) How do I tell whatever I set up in 1 where to get its fields from?
3) How do I do the actual insert of a row and get ready for the next
one?
4) How to I commit and clean up at the end?

Thanks again

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 01 Jun 2009, 08:46

 Posted by:  Scott Booth 

Thanks Kris

Would you happen to have an example on using SQLBaseDataAdapter?


I have downloaded the .Net samples. They have NO examples of inserts under
.Net. None. Not in any .Net language.
I have read the section on the .Net data provider in the connectivity
manual. Below (as far as I can tell) is the COMPLETE TOTAL of all
documentation on the InsertCommand Property of the SQLBaseDataAdapter
property anywhere in existance as taken verbatium from the manual:
InsertCommand
Contains the text of a SQL query used to insert new records.
Huh? This is documentation? WTF am I supposed to delve out of that? Not
one single sollitary hint of an example anywhere that I can find - seriously
not one anywhere.

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 01 Jun 2009, 16:41

 Posted by:  Krzysztof Dorko 

Ok
Digging a little more into this data adapter I have found that it will not
help you at all since looks like it is doing the same job like you can do
using loop and command (it is going row by row and perform required action
depends from rowstate. So network traffic will not be reduced.
But at least when using sqlserver or oracle and data adapters you can set
updatebatchsize to 0 or any value > 1 to reduce this.
I am nor sure about sqlBaseDataAdapter since going to unify documentation
this "robust" object is probably created base on framework 1.1 where this
"updatebatchsize " is not supported. I hope this is only unify documentation
issue that it refer to 1.1. Check you SqlbaseDataAdapter for this property.
And using commands you do not need to prepare sql statement every time you
do insert. Just use prepare method at the very beginning and subsequently
change parameters values.
About documentation, there is a lot of examples of using adapters, commands
and everything. In this particular case maybe this can help then you have to
call update method to apply all kind of changes to database. You set up
insert command in design time once and data adapter will use it every time
row stare value will indicate new row ("added")

thx
Kris

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 01 Jun 2009, 16:45

 Posted by:  Jim McNamara 

Isn't finding the right samples fun? Millions to choose from & it's a crap
shoot whether you find the right one.

Here's a short extract from some code I have. It's a select - reads from an
Oracle db and loads a strongly typed data table. You basically have to
reverse the process - build the table and insert the rows. (I haven't
gotten to a task like yours yet) Hope this will help. BTW, Oracle and
SQLBase have very similar syntax - Former Oracle engineers were responsible
for the SQLBase archetecture.

[DataObject(true)]

public class tableE_ins_companies

{

[DataObjectMethod(DataObjectMethodType.Select, true)]

public static ICollection GetE_ins_companies(string
sCondition, string sOrder, ref string sReturn)

{

List tab = new List();

if (sCondition == null) { sReturn = "Missing Select Condition"; return
tab; }

OracleCommand cmd = new OracleCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select * from e_ins_companies where " + sCondition + " "
+ sOrder;

using (OracleConnection sql = new
OracleConnection(FFOM.Globals.ConnectString))

{

cmd.Connection=sql;

try

{

sql.Open();

int idx = -1;

OracleDataReader r = cmd.ExecuteReader();

while (r.Read())

{

idx++;

tab.Add(new baseE_ins_companies(int.Parse(r.GetOracleNumber(0).ToString()),

r.GetOracleString(1).ToString(),

r.GetOracleString(2).ToString()));

}

}

catch (Exception e)

{

sReturn = "Reading Data from E_INS_COMPANIES Failed!\r" + e.Message;

}

}

return tab;

}

}

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 02 Jun 2009, 04:10

 Posted by:  Scott Booth 

Hi Jim,

Yup, oodles of fun for the whole family to enjoy. Everyone loves daddy
after a day of searching for what should be litterally staring me in the
face.

Thanks for your help.

BTW, Re: "Former Oracle engineers were responsible for the SQLBase
archetecture." More than that, I believe they shared a (vice) presidient -
Umang Gupta.

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 02 Jun 2009, 04:11

 Posted by:  Scott Booth 

Hi Kris,

I'm not trying to reduce network traffic, so that's not an issue. I'm
making the assumption that behind the scenes, SQLBase operated the same way
regardless of whether the client is .Net or C++ or SQLWindows. ie, the
sages involved in adding multiple records is as follows:
1) Compile SQL statement.
1a) Parses the command. This step detects syntax errors and verifies
the existence of database objects.
1b) Performs a security check.
1c) Determines the best access path. The system finds the indexes (if
any) that provide the best access path to the data.
1d) Translates the command into a series of executable modules.
2) Bind the data.
3) Execute SQL statement.
4) Repeat from step 2 (NOT step 1 which is what I'm trying to avoid) as
long as there is more data.
5) Commit.


I know this is how it worked because once upon a time, GCGU
(Gupta/Centura/Gupta/Unify) actually wrote pretty decent manuals - excellent
really. The fact that the page numbers for this information in my printed
copy of the API reference purchased with 90's 16 bit MS-DOS version are the
same as the latest downloadable PDF speaks volumes about the quality of
current documentation.


Thanks for your thoughts.

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 02 Jun 2009, 14:51

 Posted by:  Krzysztof Dorko 

Hi
I understand that your major concern is about to split these inserts on
Prepare and Execute but eliminating network traffic when inserting 5000 rows
can speed it up a little :)
So, Like I said you still have two options (it will be nice to know which
one is faster). Since you are familiar with Command object go and take a
look on this Prepare method which should be called after Command's
parameters are created and before you set values of them and execute.
If I will find some time today I try create some example base on one table
Kris

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 02 Jun 2009, 18:11

 Posted by:  Krzysztof Dorko 

Ok
Now I remember why I drop this adapters 7 years ago :). They are slow, looks
like this UpdateBatch fix this problem but since you are working with
SqlBase and this adapter probably do not have this property available You
probably do not have choice and have to implement Command.
I have test it (against SqlServer 2008 and small empty table) and the best
result inserting 500000 rows was using this Prepare method I have mentioned
before.
For 5000 it does not matter (but table was empty and row structure less then
basic)
This is example how you can do this
private void InsertCommandMethod()

{

DateTime start = DateTime.Now;

DateTime end;

using (SqlConnection conRemote = new SqlConnection("Data Source=;Initial
Catalog=;User ID=;Password="))

{

conRemote.Open();

using (SqlTransaction transaction = conRemote.BeginTransaction())

{

try

{

SqlCommand commandInsertTransactions = new SqlCommand("INSERT INTO
A14(id,row_id) Values(@ID, 'row no ' + @index)", conRemote, transaction);

commandInsertTransactions.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);

commandInsertTransactions.Parameters.Add("@index", SqlDbType.NVarChar, 20);

commandInsertTransactions.Prepare();

foreach (DataRow row in Table.Rows)

{

commandInsertTransactions.Parameters[0].Value = (Guid)row[0];

commandInsertTransactions.Parameters[1].Value = (String)row[1];

commandInsertTransactions.ExecuteNonQuery();

}

transaction.Commit();

end = DateTime.Now;

this.label1.Text = (end.Subtract(start)).TotalSeconds.ToString();

}

catch { transaction.Rollback(); }

}

}

}

This is code for Adapter just in case you will like it better

private void InsertAdapterMethod()

{

DateTime start = DateTime.Now;

DateTime end;

using (SqlConnection conRemote = new SqlConnection("Data Source=;Initial
Catalog=;User ID=;Password="))

{

conRemote.Open();

SqlDataAdapter adapter = new SqlDataAdapter();

adapter.UpdateBatchSize = 500;

adapter.InsertCommand = new SqlCommand("INSERT INTO A14(id,row_id)
Values(@ID, 'row no ' + @index)", conRemote);

adapter.InsertCommand.Parameters.Add("@ID", SqlDbType.UniqueIdentifier,16,
"Id");

adapter.InsertCommand.Parameters.Add("@index", SqlDbType.NVarChar, 20,
"Row_Id");

adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

adapter.InsertCommand.Prepare();

adapter.Update(Table);

end = DateTime.Now;

this.label1.Text = (end.Subtract(start)).TotalSeconds.ToString();

}

}

HTH

Kris

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Simple example of inserting multiple records under C# (.Net) wanted.

Post by NewsgroupServer » 05 Jun 2009, 16:55

 Posted by:  Philip Hautekiet 

This works fine within Sql Talk...

INSERT INTO xxx (kol1,kol2) VALUES (:1,:2)
\
$DATATYPES CHARACTER,CHARACTER
aa,2009-05-10-17.00.52.000000
bb,2009-05-11-17.20.52.000000
cc,2009-05-12-23.30.52.000000
/
;

Return to “td.connectivity”

Who is online

Users browsing this forum: [Ccbot] and 0 guests