Add Records

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

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!
 
In short, to get you started...

// namespace
using System.Data.SqlClient;

// Database Connection
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated
Security=SSPI");

// Execute SQL Statement
SqlCommand cmd = new SqlCommand("select * from Customers", conn);
 
Jonathan,

I know that they are hard to find a full configured DataAdapter for that
where you have used do one by one the deletes, the updates and the the
insert.

However if your insert/update/delete is a simple one, then you can use the
Commandbuilder.

http://www.vb-tips.com/SQLServerUpdate.aspx

There are more tips on our website using the commandbuilder and only few to
use full written updates. Those needs a lot of code and are therefore not so
well to use as tip.

Cor
 
Thanks, but that code doesn't add any records. Moreover, I couldn't see
where the list of articles at the link you provided does either.

?
 
I'll be adding hundreds of rows and I'm not sure the CommandBuilder would be
the most efficient. I'd really like to know how to add records to a database
from code.

I can't yet tell if the code at that link does what I need or not. I'll
check it out.

Thanks.
 
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" <[email protected]>
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.
Click to expand...

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top