|
|
Hello,
I'm getting data via file transfer-interface and I'd like to import it to a sql server 2005 database. Now my question: What is the best way to write the data to the database (I'm using C#)? Open a SqlConnection, transfer each single data row and the close the connection again? This can't be the best solution I think. Is there a better, faster way to get the data into the database - e.g. with a dataset? It's a little complicated, because I've corresponding data tables and I've got to query these tables for primary keys, to be able to fill the new data rows and import the new data properly. I already know DataSet.Update() but then there's still the problem that I've got to query the related tables for the ID (primary key) before I can update the DataSet and the database. I simply can't figure out how I can avoid the query for these IDs. Maybe someone got an idea for this problem?! Thanks in advance.
Tobias
|
|
|
|
|
Tobias Schoenig wrote: What is the best way to write the data to the database (I'm using C#)?
There are many ways, it depends on your situation.
Tobias Schoenig wrote: Open a SqlConnection, transfer each single data row and the close the connection again?
Probably not, you are receiving the data in a constant stream are you not?
Open connection, INSERT many rows, close connection.
Remember that you can perform multiple actions in a single SqlCommand if needed. Or you can repeatedly execute the SqlCommand with a different set of parameters each time.
|
|
|
|
|
Colin Angus Mackay wrote: Probably not, you are receiving the data in a constant stream are you not?
I read out the data line per line and not in a constant stream. I know that i can perform multiple actions in a single SqlCommand but then i still got the problem that i've got to query the related tables for the IDs of corresponding rows. Is there no way to avoid these queries? Is it possible to query the ID of a related row and insert the new data row into the database? Maybe anyone has an example for such an SqlCommand.
|
|
|
|
|
Tobias Schoenig wrote: I read out the data line per line and not in a constant stream.
Sorry, what I meant was: You are running as a batch process rather than an interactive one. The data will be read in line by line, but it does not require user interaction to keep this process going.
Tobias Schoenig wrote: Is there no way to avoid these queries?
If you need to keep the data related, then you'll have to perform the queries.
If the relationship is in the form of a lookup with only a few entries then you could do one query to bring the data to your .NET application.
If the joined data is more dynamic (i.e. it will also be changing during the upload) then you might consider using Stored Procedures. The stored procedure takes as parameters the data to be inserted and does the lookup internally - thus you only have one round trip to the database per insert.
|
|
|
|
|
Colin Angus Mackay wrote: Sorry, what I meant was: You are running as a batch process rather than an interactive one. The data will be read in line by line, but it does not require user interaction to keep this process going.
The process is a batch process called every x seconds (10s - 30s) and does not require user interaction.
Colin Angus Mackay wrote: If the relationship is in the form of a lookup with only a few entries then you could do one query to bring the data to your .NET application.
The problem is, that I've got to do the lookup for each data row (about 100 entries) I want to insert and this adds the same number of queries to my application, which takes its time. The data is not that dynamic that stored procedures would be effective. The question is, is it possible to do this query together with the insert in one SqlCommand?
|
|
|
|
|
Tobias Schoenig wrote: The process is a batch process called every x seconds
Okay - Open the connection at the start of a batch, issue your commands then close the connection. 20-30 seconds later you repeat the process.
Tobias Schoenig wrote: The problem is, that I've got to do the lookup for each data row (about 100 entries)
100 entries in the look up is not much. You could pull that across once and put in in a hashtable.
Tobias Schoenig wrote: The question is, is it possible to do this query together with the insert in one SqlCommand?
Yes.
|
|
|
|
|
Ok, thanks for your answers! I'll try to improve the performance of the data import by merging the query and the insert together in one SqlCommand.
|
|
|
|
|
jithbiz0033 wrote: The following coding threw the error
What error?
|
|
|
|
|
Hi,
I am working with a program for uploading and downloading files.I am able to upload and download files with SqlConnection.But when I use OleDbConnection it shows following error while uploading the files
OleDbException : "Must declare the variable '@id'."
Error Code : -2147217900
{"Must declare the variable '@id'."}
I am using the parameters to the insert the values.for all the parameters it shows the same kind of error.
Please give your valuable Suggestions or codings
Thanks
Jith
|
|
|
|
|
jithbiz0033 wrote: Please give your valuable Suggestions or codings
Without seeing your code, it is difficult to suggest a corrective solution.
|
|
|
|
|
Hi,
The following coding threw the error.
OleDbConnection mycon = new OleDbConnection(con);
OleDbCommand mycom = new OleDbCommand("insert into files (Id,FileData) values (@id,@filedata)", mycon);
OleDbParameter param0= new OleDbParameter("@id",OleDbType.Guid);
param0.value=id; //initially got guid in id;
mycom.Parameters.Add(param0);
OleDbParameter param0= new OleDbParameter("@filedata",OleDbType.VarBinary);
param1.value=filedata; //initially got byte[] in filedata
mycom.Parameters.Add(param1);
mycon.Open();
mycom.ExecuteNonQuery();
mycon.Close();
Now I changed my coding as below.
OleDbConnection mycon = new OleDbConnection(con);
OleDbCommand mycom = new OleDbCommand("insert into files (Id,FileData) values (?,?)", mycon);
mycom.Parameters.Add("@id", OleDbType.Guid).Value = id;
mycom.Parameters.Add("@filedata", OleDbType.VarBinary, fileData.Length).Value = fileData;
mycon.Open();
mycom.ExecuteNonQuery();
mycon.Close();
It is working fine.But I want to know why the first coding threw the error
Thanks
Jith
|
|
|
|
|
It may be possible that your back end database doesn't understand named parameters and uses the parameter's position instead. Also, it will prefer the ? notation for positioning parameters rather than the @ notation.
|
|
|
|
|
Hi,
Thanks for the explanation.I didn't really knew that the SQL servers prefers different parameters.
Thanks 
Jith
-- modified at 0:50 Thursday 22nd March, 2007
|
|
|
|
|
I want to loop one SELECT statement and get its value in a variable. Base on that variable and I want to loop Another SELECT statement and get it value in another variable. This both variable I want to put in one table which function will return from SQL Server.
|
|
|
|
|
You can loop over the result set of a SELECT with a CURSOR. However, cursors should be used as a last resort because they are very slow. SQL is a set based language. In other words it operates most efficiently on sets of data rather than processing one row at a time.
From the description of your problem it looks like there is a strong possibility of a set based answer. You can do this by JOINing the two tables in a SELECT. If you want help with that you will need to supply more information. The relevant columns in the source tables (including primary keys, foreign keys and output columns)
|
|
|
|
|
Thanks for ur help Sir.
I can use JOINS but it wil give only one or both column from the tables...
I want only one column...in which both the column from both table should come... and cursors are very slow... as i want use it in a website then wil b of no use....
I wil give one scenario.....
Suppose i ve two tables say master and detail. In Master i m taking data with SELEC statement, in wich i got unique value say ID. Now this master table's ID Column i want to loop Row By Row and get data from detail table. and input it in one single column of the new table.
e.g
ID = 1234 (Master Table)
From Detail Table i ve Foreign key as PID, wich der are multiple records..
now my new table should reflect like this...
New Table
NewColumn
1234
ABC
XYZ
5678
DEF
TUV
where all the numeric data is from Master table and Alphabetic is from Detail table...
for this i want to use user-define functions from SQL.
-- modified at 2:44 Thursday 15th March, 2007
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Do two inserts, first gets the IDs from the master table. The second has a join and inserts the data from the detail table.
|
|
|
|
|
Thanks Sir,
Dats true but how i wil loop it i.e the syntax....
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
You insert it like this:
INSERT INTO NewTable(ColumnName)
SELECT ID
FROM Master;
INSERT INTO NewTable(ColumnName)
SELECT SomeColumn
FROM Detail
INNER JOIN Master ON Master.ID = Detail.PID;
No looping is required. SQL is a set based language. It is designed an optimised to operate on large quantities of data in a single operation.
|
|
|
|
|
thanks sir,
here is the code wich u ve given and results this output.
INSERT INTO menu(menus)
SELECT
'Main - '+ menuname
FROM modulemast
INSERT INTO menu(menus)
SELECT
'Detail - '+optionname
FROM modules
INNER
JOIN modulemast ON modulemast.PID = modules.PID
menus
-------------------
Main - Transactions
Main - Masters
SubMain - Accounts
Detail - Equity
Detail - Derivatves
Detail - Mutual Fund
Detail - Bank
Detail - Cash
Detail - JV
Detail - Bank Reco
Detail - Accounts
Detail - Banks
But... I want want in this fashion ..... so how could i do it....
menus
-------------------
Main - Transactions
Detail - Equity
Detail - Derivatves
Detail - Mutual Fund
SubMain - Accounts
Detail - Bank
Detail - Cash
Detail - JV
Detail - Bank Reco
Main - Masters
Detail - Accounts
Detail - Banks
Please Help me 4 this scenario......Pleaseee....
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Databases are set based - That means there is no order. The implementation of most database systems give the illusion of order, but that is just a by-product of the algorithms used in the implementation. There is no guarantee that the perceived order will be preserved if you migrate to a new database, upgrade your existing database, or possibly even if you apply a service pack.
Next, Where does this "Submain" come from - the code you showed cannot create that row?
|
|
|
|
|
Sir,
I didnt got ur First Point....
And regarding Submain1 it just a column in table which i ve not inclucded. Its just a reference.....
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Smart_Boy wrote: I didnt got ur First Point....
You want the data to be inserted in a specific order. You cannot. The table contains no order. Any perceived order is an illusion created by the specific implementation of the database.
If you attempt to add an order by class to an INSERT statement it will come back with an error. The order data is inserted is not necessarily the order in which it is stored. The order in which the data is stored may change. It is outwith your control.
If you want the data to be retrieved in a specific order then you have add some markers to the table so that it can be done.
e.g.
The table contains the columns:
Menu, SubMenu, Detail, Combined
The first three columns can be used in an ORDER BY clause. The last column is used in the SELECT's column list
SELECT Combined
FROM MyTable
ORDER BY Menu, SubMenu, Detail
The INSERT statements would need to be altered to add the additional data to your table, with nulls in place where there is no relevant information.
|
|
|
|
|
Thanks Sir,
I forgetted dat i ve given order column in the table.
And I used UNION to join 2 tables, and then given order by order column.
Thank you Sir for ur help..
My task is Achieved......
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
