Reading from a text file and then storing into the database
-
Hi all I want to write a program that will read a text file as an unput. This file will contain some info for students, one student in one line. It might contain 60,000 to 500,000 lines. Now the program shoul read each line and store the info. into the database. My question is: What is the fastest way for doing that? inserting the data into the database must be through the program(not importing the data through the SQL Server or MS Access). Would u help me please :)
-
Hi all I want to write a program that will read a text file as an unput. This file will contain some info for students, one student in one line. It might contain 60,000 to 500,000 lines. Now the program shoul read each line and store the info. into the database. My question is: What is the fastest way for doing that? inserting the data into the database must be through the program(not importing the data through the SQL Server or MS Access). Would u help me please :)
First of all you will need to open a connection to the database. Depending on which db you want to write to choose one of the providers coming with the framework (OleDb, ODBC...). If you need it fast you'll probably have to test them. If its possible I would define a stored procedure in the database. Otherwise you will have to make a parameterized query. Once this is done the reading can begin. You will now need a FileStream to open the file and read it line by line. Depending on the format you will have to split each line some way and fill in the parameters to your query. Execute it and go fopr the next line. Thats it. :)
-
Hi all I want to write a program that will read a text file as an unput. This file will contain some info for students, one student in one line. It might contain 60,000 to 500,000 lines. Now the program shoul read each line and store the info. into the database. My question is: What is the fastest way for doing that? inserting the data into the database must be through the program(not importing the data through the SQL Server or MS Access). Would u help me please :)
-
First of all you will need to open a connection to the database. Depending on which db you want to write to choose one of the providers coming with the framework (OleDb, ODBC...). If you need it fast you'll probably have to test them. If its possible I would define a stored procedure in the database. Otherwise you will have to make a parameterized query. Once this is done the reading can begin. You will now need a FileStream to open the file and read it line by line. Depending on the format you will have to split each line some way and fill in the parameters to your query. Execute it and go fopr the next line. Thats it. :)
-
Thanks But, would u tell me more about parameterized query and give me some resources or examples. And another Q, do u prefer to insert the data line by line or first read as much as I can from the file and the insert them together. :)
1. Have a look here. Its in VB.Net and originally meant for ASP. But the same techniques apply for C# windows applications. The sample there could give you a good start. As you want to load data into the database and not read the only really difference is that you will have to use ExecuteQuery instead of ExecuteReader. 2. When using parameterized queries you will have to insert the data line by line. Thus there is no real reason to load more than line at a time into memory. In order to increase you could read your data in one thread (storing many lines in memory) and writing them to the database in another thread. This could improve performance in certain situations, but is also a bit advanced, so you probably should stick to the line by line handling.
-
DTS is a part of the SQL Server and it states for: Data Transformation Services. It is particularly handy when you want to perform a task of transferring a data from text files, excels or other databases. DTS will be significantly faster than other program under assumption that there is no special business logic for the data to be transferred (Conversion of data isn’t considered to be a special business logic and can be easily done by the DTS). Hope it helps,