0 DATABASES IN VB.NET

DATABASES IN VB.NET

The connection objects is needed want to connect to a databases .There are a number of different connection objects ,and the one we use depends largely on the type of databases, we are connecting because we are connecting to an access databases .We will need something called OLEDB connection objects OLE STANDS FOR (OBJECTS LINKING AND EMBEDDING), and its basically a lot of objects (com objects)  bundled together that allow to connect to data source in general ,and not  just databases .We can use it EXAMPLE To connect the text files ,sql server ,email, and a whole lots more.There are a number of different OLEDB objects called data provider,but the one we will use is called "jet",other are sql server .and oracle. So place a button on the form. Change the name property to button load .Double click button to open the code window

                                     USE 

                                        Dim con as new oledb.oledb connection

                                 SETTING A CONNECTION STRING

There are properties and method associated with the connection objects .We want to start with the connection string property.This technology is called provider ,and we use data source ,so add the code: 

                                          Dim dbprovider as string

                                          Dim dbsource as string 

                 dbprovider ="provider =microsoft .jet .oledb.4.0;"

                 dbsource ="datasource =c:\addressbook .mdb"

                  con.connection string=dbpovider&dbsource

                                         OPENING THE CONNECTION 

Now we have  a connection string ,we can go ahead  and open the databases,code:

                                   

                                            con.open

once open , the connection gas to closed again,code:

  

                                                              con.close

                                 DATA SETS AND DATA ADAPTERS 

The data set and datatadapter will hold a copy of the information from the databases.The dataset is not something ,we can draw on the form ,like a button or a textbox .The dataset is something that is hidden ,and just stored in memory.

                                                  CODING

                   

                     Dim ds as new dataset

                                          Dim da as oledb.oledbdataadapter

                                         da= new oledb.oledbdatadapter(sql,con) 

The data adapters is the property of the oledb objects:

                                                                               oledb .oledbdata adapter

we are passing the objects to the variable called DA.This variable will hold a reference to the data adapters .

while the second line in the code above sets up a reference to the data adapters , the third line creates a new data adapters objects .we need to put two thing in the round bracket of the objects declaration :sql string and connection objects 

FILLING THE DATA SETS 

The data adapters can fill a data set with record from a table .we can use only single line code :

                                da.fill(ds,"addressbook")

                                                                   ITEMS
                              0                  1                                   2                            3                           4

*ROWS	ID	FIRSTNAME	SURNAME	ADDRESS1	ADDRESS2
0	01	RAMESH	VERMA	HR	WA
1	02	PANKAJ	SHEGAL	DE	WWC
2	03	PARVEEN	VERMA	DDR	EERT
3	04	ANUJ	SAINI	SRE	TRG
4	05	SAGAR	KUMAR	DDN	PPN

Textbox1.text=ds.tables ("address").rows(0).item(0)
Textbox2.text=ds.tables ("address").rows(0).item(1)

Textbox3.text=ds.tables ("address").rows(0).item(2)
Textbox4.text=ds.tables ("address").rows(0).item(3)

Textbox5.text=ds.tables ("address").rows(0).item(4)


                                                      NAVIGATE 

To navigate through the datasets ,lets change the form .by adding some navigation.





             CODING FOR NAVIGATE THE BUTTON

  

<<                 <               >               >>

1).  << coding 

 pos=0 

textbox1.text=ds.tables("address").rows(pos).item(0)
textbox2.text=ds.tables("address").rows(pos).item(1)
 end sub

2).< coding

If pos=0 then

msgbox("you are at first record")
else
pos=pos-1

textbox1.text=ds.tables("address").rows(pos).item(0)
textbox2.text=ds.tables("address").rows(pos).item(1)
end if
end sub

3).> coding

if pos=ds.tables("address").rows.count-1 then

msgbox("you are at last record")
else
textbox1.text=ds.tables("address").rows(pos).item(0)
textbox2.text=ds.tables("address").rows(pos).item(1)
end id
end sub
4). >>  coding

pos= ds.tables ("address").rows.count-1

textbox1.text=ds.tables("address").rows(pos).item(0)
textbox2.text=ds.tables("address").rows(pos).item(1)

ADD/UPDATE/CANCEL/COMMIT IN DATABASES

1. ADD= You have to add a new row to the data set ,then commit the new rows to the data base. But the add new record button in the form is quite simple .the only way it does is to switch off other button ,and the clear the textboxes ,ready for new entry.

                     CODING FOR THA ADD AND COMMIT THE DATA IN THE DATA BASES

Button 2.enabled= false

          Button3.enabled=true
          Button4.enabled=false
          textbox1.clear
          textbox2.clear

            2.    COMMIT

If pos>=0 then

Dim cb as new oledb.oledbcomand bullder (da)
Dim ds row as data rows
dsrow = ds.tables("address").newrow()
dsrow.item(0)=textbox1.text
dsrow.item(1)=textbox2.text
ds.tables("sagarbook").row.add(ds row)
da.update(ds,"address")
msgbox("new record add")

                 

           3.   UPDATE =  To update the database,we need something called a command builder .The command builder will build a sql string .In between round bracket ,we type the name of the data adapter  ,da in the case.The command builder is then stored in a variable ,it is called command builder(cb).                        

                                                   CODING                                        

                                                   
                ds.tables ("sagar").rows(pos).item(0)=textbox1.text 
                ds.tables ("sagar").rows(pos).item(0)=textbox1.text    
                con.open
                Dim cb as new new oledb.oledbcommandbuilder(da)
                da.update (ds,"sagar")
                msgbox("data updated")
                end sub
     
       4.    DELETE =  To delete the record in the databases.


                                                           CODING                    

If pos >=0 and pos<=ds.tables ("sagar").rows.count then

          If msgbox.show("do you have delete record","delete",messageboxbutton .yesno,messageboxicon.warning)=dialogresult.no then
msgbox("operation cancelled")
exit sub
else
Dim cb as new oledb.oledbcommandbuilder(da)
ds.tables ("sagar").rows(pos).delete()
pos=0
textbox1.text=ds.tables("sagar").rows(pos).item(0)
textbox2.text=ds.tables("sagar").rows(pos).item(1)
da .update(ds,"sagar")
end if
end if
end sub