Yes, TDS is "tabular data stream". It's the low-level protocol I discuss in
Chapter 2. It's the only interface exposed by SQL Server to the outside
world. All of the data access interfaces like ADO, ADO.NET and the rest use
to communicate with the server. The BCP operators bypass much of the
overhead involved in adding rows.
Ah, did you try this:
http://www.google.com/search?hl=en&q=sqlbulkcopy
Here is a VB.NET example drawn from the book--I hope it will suffice:
It opens a delimited text file and imports the data to SQL Server using the
SqlBulkCopy class.
Imports System.Data.Sqlclient
Imports System.Data.odbc
Public Class Form1
Dim WithEvents bc As SqlBulkCopy
Dim dr As Odbc.OdbcDataReader
Dim cn As SqlConnection
Dim cn2 As OdbcConnection
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
cn = New SqlConnection("data source=betav7;integrated
security=sspi;database=biblio")
cn.Open()
cn2 = New
OdbcConnection("DSN=TextFile;DATABASE=c:\MSFTStock.TXT;Extensions=asc,csv,tab,txt")
cn2.Open()
Dim cmd As New OdbcCommand("SELECT * FROM MSFTStock.TXT", cn2)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
bc = New SqlBulkCopy(cn)
With bc
.BatchSize = 10
.DestinationTableName = "StockData"
.NotifyAfter = 5
.WriteToServer(dr)
End With
Catch exIO As InvalidOperationException
Debug.Print(exIO.ToString)
Catch ex As Exception
MsgBox(ex.ToString)
Finally
Dim cmd2 As New SqlCommand("SELECT Count(*) FROM StockData", cn)
Dim strRows As String
strRows = cmd2.ExecuteScalar().ToString
lblRowsInTable.Text = strRows
cn.Close()
cn2.Close()
End Try
End Sub
Private Sub bc_SqlRowsCopied(ByVal sender As Object, ByVal e As
System.Data.SqlClient.SqlRowsCopiedEventArgs) Handles bc.SqlRowsCopied
lblRowsCopied.Text = e.RowsCopied.ToString
lblRowsCopied.Refresh()
End Sub
End Class
----- Original Message -----
From: "Jonathan Wood" <(E-Mail Removed)>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Monday, November 05, 2007 12:42 PM
Subject: Re: Importing Data
> Bill,
>
>>>>>>
> ADO and ADO.NET (and all of its predecessors after DB-Library) are QUERY
> interfaces, not designed for bulk operations. It's a waste of time and
> resources to try to use the System.Data classes to import more than a few
> rows of data.
>>>>>>
>
> Can you explain why it's a waste of time? Looking at what I've found so
> far, that approach would probably take me, personally, about hundredth of
> the time requires to figure out the other stuff. And that takes into
> consideration the fact that I've been so far unable to find a simple
> example showing how to add records to a database using the System.Data
> classes.
>
>>>>>>
> When it's time to import data into SQL Server, the fastest (by an order of
> magnitude or more) is to use the TDS bulk copy approach. This is exposed
> in a number of ways including:
> a.. The BCP commandline utility.
> b.. TSQL BulkCopy operations
> c.. DTS/SSIS scripts
> d.. ADO.NET SqlBulkCopy method.
> All of these techniques can import data from anything that can be read by
> a .NET data provider, an OLE DB data provider, and ODBC driver or your own
> custom-written data provider that exposes a DataReader. This means other
> database tables, flat files, text files, delimited files--almost anything.
> These routines can import millions of rows in no time. Some Oracle
> customers buy SQL Server just to get SSIS.
>>>>>>
>
> Okay...
>
> I guess TDS stands for something but "tds bulk copy" brings up one a
> single result on Google, and that wasn't much help.
>
> Let's try this: Can you tell me which of these is available with what I
> have (VS2005 and SQL Server Management Studio Express)? Also, any spec of
> details on using any of these would be a tremendous help. Maybe even a
> link of some sort.
>
> And I have your book "Hitchhiker's Guide to Visual Studio and SQL Server."
> Am I understanding correctly that this doesn't step through what I want to
> do anywhere? Or how about an example that shows how to add records to a
> database from C# (with no controls, Access, or anything else other than C#
> and SQL)?
>
>>>>>>
> My latest book details how to use SqlBulkCopy. BOL shows how to do the
> rest. I'll be happy to show it to you if you come to one of my sessions
> this week at DevConnections in Vegas or at my workshop in Vancouver BC on
> the 26th (see www.devweek.com for availability).
> <<<<<
>
> Sounds good, but I'll be far from Vegas at that time.
>
> Thanks.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit
www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"Jonathan Wood" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Well, I spent about 45 minutes trying to find a simple example that shows
> how to add records to a SQL database from C# code.
>
> All I can find is databound stuff, or examples working with Access and who
> knows what.
>
> How hard can it be? I want to write some code that adds records to an
> existing table. No controls will be involved (bound or otherwise).
>
> Can anyone show me how, or point me in the right direction?
>
> Thanks!
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>
Similar Threads
