One of the easiest way to insert and fetch record from Database

For Beginners:

One of the easiest way to insert and fetch record from Database:-
How to use common code for developing windows application using vb.net.

Hello Everybody,
I am Amarjit singh a software developer in a reputed software company at Bhubaneswar since Jan .2007.
This article is very useful for developing desktop applications or windows based applications using vb.net.In this article ,I have shown when a novice programmer writes a code especially for database connectivity he or she often use this below code in every windows form.

Suppose there a table like this (below):-

Empid Empname EmpAge EmpSalary
101 Amarjit 30 25000.00
102 Sourav 34 17500.00
103 Sujit 28 20300.00
104 Kamla 27 12000.00

Here I have used SQLSERVER2000 as database.

To create table Emp_Details :

Use MYDB --Name of the database.

Create table Emp_Details
(
EmpId int primary key,
EmpName varchar (50),
EmpAge int,
EmpSalary Decimal(7,2)
)

For inserting data in database:

Imports System.Data.SqlClient

Dim con As New SqlConnection("Server=.;Integrated security=SSPI;Database=MYDB")
Dim da As SqlDataAdapter
Dim com As SqlCommand
Dim str As String

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

str = "Insert into Emp_Details values (" & TxtEmpID.Text & ",'" & TxtEmpname.Text & "'," & Txtage.Text & "," & TxtSalary.Text & ")"

com = New SqlCommand(str,con)
con.open()
com.ExecuteNonQuery()
con.close()
MsgBox("Record Saved.")
End Sub
……………………………………




For fetching record from database (using DataAdapter):

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Str="Select * from Emp_Details"
da = New SqlDataAdapter(str, con)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "RECORD")
MsgBox("Record Fetched.")

End Sub

The above code is often used for inserting and fetching records in/from database. So in a desktop application if 50 to 100 forms are there then there is no need to write the above code again and again in each of the form ,it is just a wastage of time. Here in the above code u have also marked when you insert/fetch data in database you use these four lines frequently.
(i.e. the below code is commonly used for inserting and fetching record respectively.)

For inserting record:

Com=new Sqlcommand (str, con)
Con.open ()
Com.ExecuteNonQuery ()
Con.close ()

For fetching record:

Da = new sqldataadapter (str, con)
Dim ds as new dataset
Ds.clear ()
Da.fill(ds,"record")


i.e. str value is only changing rest part in still constant.So if we pass the str string value as parameter in a user defind function and write the above lines in that user defind function we can easily insert or fetch record from the table.














i.e. if we write the code in the Module :

Imports System.Data.SqlClient
Module Module1

Public con As New SqlConnection("Server=.;Integrated security=SSPI;Database=MYDB")
Public da As SqlDataAdapter
Public com As SqlCommand

For Inserting Record :

Public Sub Save_Record(ByVal str As String)

com = New SqlCommand(str, con)
con.Open()
com.ExecuteNonQuery()
con.Close()
MsgBox("Record Saved")

End Sub

To Fetch Record. :

Public Function Fetch_Record(ByVal str As String) As System.Data.DataSet
da = New SqlDataAdapter(str, con)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "RECORD")
Return ds
End Function


End Module
-------------------------------

Now if we use these above codes in any window form of that project:

For Inserting Record:
We have to write only these 2 lines in the button click event. :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim str As String

str = "Insert into Emp_Details values (" & TxtEmpID.Text & ",'" & TxtEmpname.Text & "'," & Txtage.Text & "," & TxtSalary.Text & ")"

Save_Record(str)

End Sub






Similarly To Fetch Records :

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


Dim str As String
str="Select * from Emp_Details"
Dim ds As New DataSet
ds.Clear()
ds=Fetch_Record(str)

End Sub

So in this way if we want to Insert or Fetch or even Update and Delete record(s), these codes can be used in any of the window form of that application or project. In this way we can also hide the codes from the window forms.


So Hope you will try this code and if you have any doubt regarding these codes so please send me an email.
My Email id is amarmca_12@yahoo.co.in

Enjoy programming.


AMARJIT SINGH
Software Developer
(Bhubaneswar)