|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Add RecordsWell, 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); -------------------- taken from - http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx TAJ Show quote "Jonathan Wood" <jw***@softcircuits.com> wrote in message news:uP2p230HIHA.5520@TK2MSFTNGP06.phx.gbl... > 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 > 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. ? Show quote "TAJ" <thomasajenk***@msn.com> wrote in message news:eWVq9k1HIHA.5520@TK2MSFTNGP06.phx.gbl... > 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); > > -------------------- > taken from - > http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx > > TAJ > > > "Jonathan Wood" <jw***@softcircuits.com> wrote in message > news:uP2p230HIHA.5520@TK2MSFTNGP06.phx.gbl... >> 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 >> > Replace the select with your sql insert command
http://www.w3schools.com/sql/sql_insert.asp Show quote "Jonathan Wood" <jw***@softcircuits.com> wrote in message news:uvetc42HIHA.1184@TK2MSFTNGP04.phx.gbl... > 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. > > ? > > -- > Jonathan Wood > SoftCircuits Programming > http://www.softcircuits.com > > "TAJ" <thomasajenk***@msn.com> wrote in message > news:eWVq9k1HIHA.5520@TK2MSFTNGP06.phx.gbl... >> 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); >> >> -------------------- >> taken from - >> http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx >> >> TAJ >> >> >> "Jonathan Wood" <jw***@softcircuits.com> wrote in message >> news:uP2p230HIHA.5520@TK2MSFTNGP06.phx.gbl... >>> 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 >>> >> > 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 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. Show quote "Cor Ligthert[MVP]" <notmyfirstn***@planet.nl> wrote in message news:CDE6485C-2423-4A60-BD8A-9CDA58DE3144@microsoft.com... > 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 > 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" <jw***@softcircuits.com> Newsgroups: microsoft.public.dotnet.framework.adonetSent: Monday, November 05, 2007 12:42 PM Subject: Re: Importing Data Show quote > 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 > -- Show quote____________________________________ 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" <jw***@softcircuits.com> wrote in message news:uP2p230HIHA.5520@TK2MSFTNGP06.phx.gbl... > 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 >  |
|||||||||||||||||||||||
Other interesting topics