softwarevj said:
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