AddNew equivalent in ADO.NET

[jdy0803]

I found that ADO's AddNew equivalent to ADO.NET is DataTable's NewRow method.
So I made code like this.
But I can't relate existing connection object to the DataSet or DataTable.
Here is my code.
-------------------------------------------------------
Dim ds As DataSet = New DataSet()
' Get the Data Table
Dim dt As DataTable = ds.Tables("patient")
Dim dr As DataRow = dt.NewRow()
' Set all columns
dr("patient_id") = 6
dr("first_name") = "Thomas"
dr("last_name") = "Edison"
' Add to the Rows collection
ds.Tables("patient").Rows.Add(dr)
ds.Dispose()

 

[jmcilhinney]

All you're doing there is adding the new row to the DataTable, which is a local cache of data. Unlike an ADO Recordset, there's no live connection between a DataSet/DataTable and the database. That's where the connection, commands and data adapter come in. The fact that you have a DataTable named "patient" in that DataSet suggests that you called Fill on a data adapter at some point, which executes its SelectCommand to retrieve data. The converse operation is the Update method, which executes the InsertCommand, UpdateCommand and DeleteCommand to save changes. if you need some examples, check this out:

Retrieving and Saving Data in Databases

 

[jdy0803]

Can you add some codes to the code I posted?
If you do, I could start as a base.

 

[jdy0803]

First, I tried changing my code like following and got correct result.
I'm not sure if this code is proper adn efficient even though record was added correctly.
---------------
Dim Sql As String = "SELECT * FROM patient"
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(Sql, cn)
Dim commandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
Dim ds As DataSet = New DataSet()
adapter.Fill(ds, "patient")
Dim dr As DataRow
dr = ds.Tables("patient").NewRow()
dr("patient_id") = 7
dr("first_name") = "Katie"
dr("last_name") = "Jeong"
' Add to the Rows collection
ds.Tables("patient").Rows.Add(dr)
adapter.Update(ds, "patient")
ds.Dispose()
commandBuilder.Dispose()
adapter.Dispose()
------------------------------


My new question is that;
To add new record, I should Fill the DataTable and the Sql("SELECT * FROM patient") should be passed to the adapter(OleDbDataAdapter) at every time I add new row.
I'm worrying about the performance when the patient table become humongous.
I'm asking this because still I'm not sure this code is correct or not even though currently works fine.

 

[jmcilhinney]

You need to do some reading then, so that you understand the types and members that you're using and then you'll have an idea of whether what you're writing is correct or not and why. It won't come all at once, but specific research is worth the time rather than just flailing in the dark.

The connection object is what creates the connection between your application and the database. It's the pipe through which data can flow in each direction. The command object is what executes the SQL code. It needs an open connection in order to execute and it can execute any valid SQL code. That will most commonly be a SELECT, INSERT, UPDATE or DELETE statement but it can be anything. A data adapter is a convenience that groups together up to four commands, those containing the four common SQL statements mentioned earlier. It provides an easy way for you to execute a SELECT statement and populate a DataTable with the result set, i.e. the Fill method, and also an easy way to execute INSERT, UPDATE and DELETE statements to save any changes in that DataTable back to the database, i.e. the Update method. So, you create the connection, commands and data adapter once and once only. The same goes for the command builder if you use one.

If you've followed the link I provided then you've seen examples that demonstrate that. You should also have read the documentation for every type and every member used in those examples. That's how you learn.