Insertion, Updation , Deletion

[softwarevj]

Hi fnds,

Although i have started doing things the VB.NET way, but the method i use to insert, update or delete a record is the same as VB. I have not been able to make full use of the datasets and dataadapters to do everything in a disconnected mode. Can anybody give me a smal example of how all the three sql queries (insert, update and delete) can be executed seperately making proper use of the disconnected mode of .NET architecture. I will be extremely thankful.

P.S you can use a maximum of two fields to make me understand.

Tanking again in advance.


Software VJ

 

[vis781]

Disconnected mode is pretty much the same as connected mode, just rather than having an open connection to the actual datasource, instead you have a small scaled down in memory database in the guise of a dataset or a datatable
So you fill your datatable with the desired information from database using your SELECT command and dataadapter. So here we are now working in disconnected mode.
You can now make changes to the data in the datatable, using databinding, or by directly modifying the data using datarow object. When you have finished making the ammendments to your data it's time to send the changes back. If you are used to ADO then ADO.Net is no different in terms of the SQL. So to update....

Dim UpdateCom as new OleDbCommand
UpdateCom.commandText = "UPDATE TableName SET Column1 = ? , Column2 = ? WHERE Column3 = ?"
Updatecom.connection = MyOleDbConnection

'Now for the parameters

UpdateCom.Parameters.Add("@ColumnName",OleDbType.VarWchar,FieldSize,"Column1")

'The thing to note here is that the last argument in the parameters must be the name of the actual field in the database.

'Once you've added the parameters for all the ?'s in the SQL statement, and in the order that they appear in the SQL Statement, it's really just a matter of passing the new UPDATE command to the OleDbDataAdapter's UpdateCommand property.

MyOleDbDataAdapter.UpdateCommand = UpdateCom

Then execute the update method of the dataadapter

MyOleDbDataAdapter.Update(Dataset Name OR DatatableName OR Collection Of DataRow Objects OR DataSet And a Datatable Name)

'The Dataadapter itsself does the job of identifying the rowstate property of each datarow object in the collection and then uses the appropriate, UPDATE, INSERT, DELETE command to perform the action on the datasource.

 

[softwarevj]

hi frnd,

i got the answer , but there are certain things like acceptchanges, rejectchanges etc. what is their use.

 

[vis781]

Acceptchanges will cause the datatable/dataset to reset all datarow objects rowstate property to default. That is to say that if you modify some rows in a datatable their rowstate property will be set to modified to that the dataadapter will know what SQL command to use when sending changes back to the database, if you call acceptchanges on that same dataset all of those rows that had their rowstate property set to modified will revert back to thier default state. AcceptChanges is automatically called on a dataset/datatable after the update has occured.

Reject changes will cause the dataset/datatable to 'Undo' all of the changes that have been made since the last time acceptchanges was called. That is to say that if you modify some datarow objects in the collection and then call reject changes the original data will be restored.

 

[softwarevj]

got that. but one more question.

if i do not use AcceptChanges and RejectChanges, what is the harm.

What i wanted to do is that i wanted to minimize my trips to the server . that is why i wanted to know about how all the three operations can be carried without going to server all the times an operation is being called.

I hope u r getting me.

Thanks a lot for your response.

Software VJ

 

[vis781]

Thats the whole point of working in disconnected mode.. to reduce server overhead. Is allows the client to retrieve a certain amount of data, modify, delete and add rows as needed then send all the changes back in one operation.

 

[cjard]

Hi fnds,

Although i have started doing things the VB.NET way, but the method i use to insert, update or delete a record is the same as VB. I have not been able to make full use of the datasets and dataadapters to do everything in a disconnected mode. Can anybody give me a smal example of how all the three sql queries (insert, update and delete) can be executed seperately making proper use of the disconnected mode of .NET architecture. I will be extremely thankful.

P.S you can use a maximum of two fields to make me understand.

Tanking again in advance.


Software VJ

Your profile says that you are using .NET 2.0, so you shouldnt be using DataAdapters any more, as the new TableAdapter was intended to supplant them. Are you familiar with a tableadapter?

Heres a quick run down:

If you drag an item (table, view) out of the database explorer and into the DataSet designer surface, you will see a wizard start that asks you what data you want to select from the table.

Along with it come the option to generate I U and D queries for manipulating data. With these queries correctly defined yourdata updates are as simple as changing the datatable, then calling Update() on the tableadapter and passing in the changes datatable


So, suppose I defined my select for the table adapter as:

SELECT name, age FROM people WHERE personID = ersonID



(i use oracle, if you use sqlserver you use WHERE personID=@personid) and the table adapter generates you a method called FillBy. You rename this to FillByPersonID, or your convenience.

Then you load the datatable with some data:

Dim pDT as New MyDataSet.PersonDataTable
Dim pTA as New MyDataSetTableAdapters.PersonTableAdapter
 
pTA.FillByPersonID(pDT, "id123") 'search for person with an id of: id123
 
 
'our datatable should now contain a single row
'we should try to avboid downloading the whole database into our
'datatable. i,.e. dont use select *from person
 
'now we update some details:
pDT.Rows(0)("name") = "new name"
pDT.Rows(0)("age") = "99"
 
'to add a row..
pDT.Rows.Add ....
 
 
'now we save our changes
pTA.Update(pDT)

explains:

the datatable object is like a local cache of database data. VB knows the primary key and how to update the data. to see the update command it generated go to the data set DESIGNER, click the TABLE ADAPTER and look in the update command section of proerties. you can tidy the query up i you like.

i tend to tidy my queries up because for oracle, it gains a load of junk in the where clause (like testing if every field is null or a value.. or 1 or something). i strip out all the junk leaving behind a query that looks like:
UPDATE people SET name = :name, age = :age WHERE personID = :Original_personID

when you load data into the datatable its like a grid you can edit, the rows behaves jsut like a Collection and pDT.Rows(0) returns you the first row. Because what it returns is a DataRow you can immediately use ("name") to index the row. You can also do this:

DirectCast(pDT.Rows(0), MyDataSet.PersonDataRow).NameColumn

the cast turns the object from a gneric datarow into the specific PersonDataRow that it is, and it gets some new properties and methods available like methods to check if a column is null etc.. try it out

so, once youve edited your datatable just like you would any other collection, you jsut call Update() on the tableadapter and pass in the modified datatable

you can do other interesting stuff like:

pDT.GetChanges(DataRowState.XXXX) 'xxxx is an enum

this method will return you a whole new datatable with the specified enum options. if you say Modified, it gets all updated rows, if you say added, it gets you lal new rows. You can add them together (Added+Modified) gets you all new or changed rows


Note, for the update to succeed, the tableadapter has to have the relevant quieres defined. Click it in the DataSet designer, and look at the insert update and delete queries. if any of these queries ar emissing or invalid, then the attempt to update that kind of change will fail.
i.e. if the delete query is missing, and you remove a row from the datatable, then call update, the tableadapter will work out it has to run a delete query to update the db, but it will fail to do so because the delete qury is not defined.

search msdn for Visual Studio 2005 Data Walkthroughs for more information