I'd still suggest using either a bulk insert or bcp. After your last
explanation, maybe bcp would be better. It'll definitely be faster than
inserting a row at a time. First of all, you can do massive inserts without
logging which will reduce overhead and speed the process. Second, you can
write your C# code to change the bcp command line, for example, change the
table name or the test file path/name according to whatever parameters you
want. In your program, write the bcp command to a windows .bat file then
execute a shell to run the bat if that makes more sense. That way you have
the flexibility to change the insert parameters on the fly and you get the
speed of bcp inserts.
Other than performing an import from text to sql table, what do you mean by
this statement?
"i want to get the functionality of DTS from C#. i mean i want
to use SQL Server objects and libraries to perform SQL server related
tasks from my application."
"Adnan" wrote:
> thanks for sparing time for me.
> actually i have log files that are very huge sized like 700MB or even
> sometimes these reach upto GBs.
> i want to extract information from these files u can say i want to
> apply filters to get only required rows. log files are maintained by
> IIS and contain space seperated fields in each row and there are
> millions of rows.
> now currently i m reading file line by line and inserting that line
> into a database table. after that i select data from that table using
> different filtering criteria and then binding that selected data to a
> data grid.
> now the problem is when i use huge sized files the process becomes very
> slow and user hav to wait for a long time so that the file to be read
> line by line and get inserted into database line by line.
> there are different sample codes that are related to execute DTS
> packages. i want to get the functionality of DTS from C#. i mean i want
> to use SQL Server objects and libraries to perform SQL server related
> tasks from my application.
> i hope that now u hav understood the scenario. the other thing is if i
> create a DTS package from sql server and while executing it i want to
> change its attributes like file name at run time so that i give file
> name as a parameter and the package with that file name get executed.
> if u still hav confusions abt understanding scenario then plz do tell
> me.
> thanks.
> best regards.
>BCP is very much like "bulk copy" technically, but BCP is a dos command. Yo
u
can read all about it in the Sql Server Books Online.
What I'm suggesting is that you can write your C# code to dynamically create
or edit a BCP command in a .bat file (technically it's just a text file).
You can manipulate the file/folder name or even the sql server table name
that the BCP will load if that makes sense in your application. You can set
it to load a table from a formatted or delimited file. For example, you sai
d
you have a space separated file.
bcp is pretty easy to set up and runs quickly.
A simple example of a bcp command would look like this:
bcp "database1.dbo.tbl_target_table" in "c:\foldername\file1.txt"
-c /S"servername" /U"userid" /P"userpassword" -f"c:\foldername\formatfile.fm
t"
In this example, the process is to load an existing sql table
(tbl_target_table) from a text file (file1.txt) using a format file that you
pre-created (formatfile.fmt)
There are many options for BCP depending on your needs. You would be
interested in the -t (field terminator character) and the -r (row terminator
character) which is where you'd tell it that a space is a field terminator
and a Cr/LF is the row terminator. As long as you can run a dos command fro
m
within C#, you can do this. BCP is part of Sql Server, not .Net per se, so
I
don't think you'll have any problems with your version.
"Adnan" wrote:
> hy
> thanks again :-)
> r u talking about SqlBulkCopy? is it BCP? as far as i know about
> it(SqlBulkCopy), it is supported in version 2 of Dot net and in my
> company we r using version 1.1. so if BCP is SqlBulkCopy then it is not
> possible now. and if this is other thing then i m going to check it and
> plz i'll b very thankful to u if u provide me related web links or
> material along with ur valuable suggestions.
> as far as my statement is concerned actually i was going to use SQLDMO
> and finding solution via SQLDMO. at present i hav to only import txt
> files but in future may b i need it.
> i m really thankful to u that u spared ur time for me because i have
> posted this problem at different forums but u r the only ho replied.
> my email id is "adnan.developer@.gmail.com" if u like to attach some
> useful material.
> best regards.
>|||Adnan,
Sorry, I can't help you with C# code. I don't use it. But I've done
similar processes from VB. I suggest you try a different MS community for C
#
programming help. Ask about opening a Dos command shell from c#. I'm sure
you can do this, and it's probably not very difficult.
Microsoft's sites will mostly just describe the command and options, as you
found. They will not help much if you are looking for specific code. But,
there are some good IT community sites other than Microsoft that offer tips
and code for just about any languange. You can search for them on the web
probably find something useful quickly. Use your favorite search engine and
find "C# programming examples" Here's one I like to use: www.ittoolbox.com
You need to know two main programming concepts: 1 how to create/update a
text file from within c#. 2: how to open a dos command shell and execute a
.bat or .cmd command file.
To use it, your application must know how to set up the BCP. that is, your
app must know the pathname/file name of the the text data file an of the BCP
.bat file. That's up to you to determine. Next, you would need to write a
BCP command to the .bat file using the the path/file name of the data, and
the SQL server, database, table, etc. that will be loaded. Last, you need t
o
open a dos command shell, and tell it to execute the BCP .bat file. All of
this can be done in the application without the user intervention if that's
what you need to do.
Good luck.
"Adnan" wrote:
> hello
> how r u tthrone?
> i hav read ur suggestion and i think it is v close to my solution but
> one thing that i hav to telll u that i m not an expert of .net i m new
> to it.
> as i found abt BCp it is a command line utility as u also told. now the
> problem is that from where i can execute it? i mean i m going to make a
> GUI windows based application and i m confudsed that how to implement
> it in my windows based application. plz help me regarding:
> if BCP is a command line utility then fromn which command prompt i can
> execute it? .net command prompt or which.
> and how and where i hav to use it in my application. i dont want my
> user to go to any command prompt.
> actually as it looks i m not clear about BCP that how it works and how
> it is used.
> i'll b very very thankful to u if u help me in this regard because i
> have already taken very much time from company for this.
> plz if it is possible then do provide me some sort of code or at least
> try to clear the concept that what steps should b taken to use BCP from
> my C# application.
> or if u know the link of such a web page then plz tell me because i hav
> searched my best but couldnt find satisfactory help. i hav already
> searched MSDN library it only tell about BCP but doesnt tell that how
> to use it in my application. it just tells its syntax and parameters
> details.
> best regards.
> Adnan Akram.
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment