Greetings,
.Net is tricking you a little bit. You can combine a
select/Insert/Update/Delete command in a single dataAdapter object. So
you really have 4 separate commands combined into one when you first
create the dataAdapter object. You have to declare each command
individually, then when you fill a dataset with data from a database
table (Access, MS Sql Server, Oracle...) all you have to do is edit your
in memory dataset datatable and the commands you declared will take care
of the rest automatically.
Note: I would steer clear of the command builder object. It creates
generic commands that may not necessarily suit your needs. Here is how
you declare all your command in one shot (at the Form Level):
Imports System.Data
Imports System.Data.OleDB
Dim da As OleDBDataAdapter, conn As OleDBConnection
Dim ds As DataSet
Private Sub Form1_Load(...)...
ds = New DataSet
conn = New OleDBConnection
conn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source =
db2test.mdb"
da = New OleDBDataAdapter
da.SelectCommand = New OleDBCommand
da.SelectCommand.Connection = conn
da.InsertCommand = New OleDBCommand
da.InsertCommand.Connection = conn
da.UpdateCommand = New OleDBCommand
da.UpDateCommand.Connection = conn
da.InsertCommand.CommandText = "Insert Into Table1(FName, LName, Phone)
Select @FName, @LName, @Phone"
da.InsertCommand.Parameters.Add("@Fname", OleDBType.Varchar, 50,
"FName")
da.InsertCommand.Parameters.Add("@LName", OleDBType.Varchar, 50,
"LName")
da.InsertCommand.Parameters.Add("@Phone", OleDBType.Varchar, 50,
"Phone")
'--do the same for the UPdate Command
da.SelectCommand.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1") '--tbl1 gets created automatically inside ds when
calling da.Fill
'--Note: if you are entering/editing data through datagridview control,
then you will have to loop through your dataset table to copy the
entries/edits from the datagridview to the dataset table and then call
da.Update(ds, "tbl1")
the da.Update call will automatically invoke either/and/or the
Insert/Update commands. da.Fill only applies to the Select command.
So, if you are entering data directly into your dataset table from
textboxes, then you don't need to worry about making sure that
entries/edits are already in the dataset table:
Private Sub btnAdd_Click(...)handles btn1.Click
Dim dr As DataRow
dr = ds.Tables("tbl1").NewRow
dr("FName") = txtFName.text
dr("LName") = txtLName.Text
dr("Phone") = txtPhone.Text
ds.Tables("tbl1").Rows.Add(dr)
da.Update(ds, "tbl1")
End Sub
Another Note: .Net may seem a little bit on the busy side or complex -
i.e. for Access. Where ADO.Net is real nice is when you are dealing
with large tables on a sql server DB. Actually, if you have to upload
data from a bunch of Access MDB's to the sql server, ADO.Net is real
nice for that too. It is a straight forward pull and push. You use the
da.Fill command to pull the data from Access to your memory table
da.Fill(ds, "tbl1") and then use the dataTable.CreateReader property of
the memory table to push the data straight to the sql server table. No
data looping involved (I had to write a routine to import data from 50
mdb's in one shot to a sql server db table - it was a snap with
ADO.Net). I did have to set up a loop to import the data from each MDB
to my memory table (all the tables were the same structure for each
mdb). Then I pushed the data in my memory table to the sql server in
one shot:
Dim reader As DataTableReader = dsSql.tblImport.CreateDataReader
dsOle.Tables("tbl1").Load(reader, LoadOption.Upsert)
daSql.Update(dsSql, "tblSql")
Note: have to create the sqlInsert, sqlUpdate commands and all the
parameters for this to work. And if you have at least 2gigs of memory
and at least a 2.8 gig processor, this will upload 500,000 records in a
matter of seconds (the hardware is the catch with .Net).
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
