Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Code Snippets » ADO.NET »
Program for the beginners in ADO.Net.
|
It is a console based application that handle all the transactions related to database.
Transactions like insertion,updation,modification,deletion and searching of data. addition it do the error handling.
'Option Explicit off indicates that there is no need of declartion of variables. You can use direct variables.
Option Explicit Off
'System.Data.OleDb is the namespace used to deal with the database.
Imports System.Data.OleDb
Module Module1
Sub Main()
'System.Console.WriteLine is used to write satement on the console.
System.Console.WriteLine("Choices")
System.Console.WriteLine("Enter 1 Insert a Record")
System.Console.WriteLine("Enter 2 Search a Record")
System.Console.WriteLine("Enter 3 Modify a Record")
System.Console.WriteLine("Enter 4 Delete a Record")
System.Console.WriteLine("Enter 5 Show All Records")
System.Console.WriteLine("Enter 6 to Stop")
no1 = System.Console.ReadLine()
'System.Console.ReadLine will read the data from the console and that value will be stored in the no1.
If no1 = 1 Then
Dim con As New dblayer
Dim td As DataTable
td = New DataTable
If con.filltable(td, "emp", "employee.mdb") Then
Dim t As DataRow
t = td.NewRow
System.Console.WriteLine("Enter name")
t.Item(1) = System.Console.ReadLine()
System.Console.WriteLine("Enter Address")
t.Item(2) = System.Console.ReadLine()
System.Console.WriteLine("Enter Phone no.")
t.Item(3) = System.Console.ReadLine()
System.Console.WriteLine("Enter Salary")
t.Item(4) = System.Console.ReadLine()
td.Rows.Add(t)
If con.insertdata(td, "emp", "employee.mdb") Then
System.Console.WriteLine("Record Saved")
End If
End If
ElseIf no1 = 2 Then
Call search()
ElseIf no1 = 3 Then
Call modify()
ElseIf no1 = 4 Then
Call delete()
ElseIf no1 = 5 Then
Call show()
ElseIf no1 = 6 Then
End
Else
System.Console.WriteLine("Wrong Choice")
End If
End Sub
Sub show()
Try
Dim st As Text.StringBuilder
st = New Text.StringBuilder
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\neetu\prac\4082006\4082006\bin\employee.mdb;Persist Security Info=False
With st
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append("employee.mdb")
.Append(";Persist Security Info=False")
End With
con = New OleDbConnection(st.ToString)
con.Open()
System.Console.WriteLine("Connection opened")
Dim query As String = "Select * from emp"
Dim ta As New DataTable
Dim da As New OleDbDataAdapter(New OleDbCommand(query, New OleDbConnection(st.ToString)))
da.Fill(ta)
For i = 0 To ta.Rows.Count - 1
For j = 0 To ta.Columns.Count - 1
System.Console.Write(ta.Rows(i).Item(j))
System.Console.Write(ControlChars.Tab)
Next
System.Console.WriteLine()
Next
Catch ex As Exception
System.Console.WriteLine(ex.Message)
System.Console.WriteLine(ex.Source)
System.Console.WriteLine(ex.StackTrace)
End Try
End Sub
Class dblayer
Implements IDisposable
Dim str As Text.StringBuilder
Dim con As OleDbConnection
Sub New()
str = New Text.StringBuilder
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\neetu\prac\4082006\4082006\bin\employee.mdb;Persist Security Info=False
With str
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append("employee.mdb")
.Append(";Persist Security Info=False")
End With
con = New OleDbConnection(str.ToString)
con.Open()
System.Console.WriteLine("Connection opened")
End Sub
Public Sub Dispose() Implements System.IDisposable.Dispose
If Not con Is Nothing Then
con.Dispose()
con = Nothing
End If
End Sub
Function filltable(ByRef table As DataTable, ByRef t As String, ByRef dbname As String) As Boolean
str = Nothing
str = New Text.StringBuilder
With str
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append(dbname)
.Append(";Persist Security Info=False")
End With
Try
Dim sql11 As String = "select * from " & t
Dim da As New OleDbDataAdapter(New OleDbCommand(sql11, New OleDbConnection(str.ToString)))
da.FillSchema(table, SchemaType.Source)
Return True
Catch ex As Exception
System.Console.WriteLine(ex.Source)
System.Console.WriteLine(ex.StackTrace)
System.Console.WriteLine(ex.Message)
End Try
End Function
Function insertdata(ByRef table As DataTable, ByRef t As String, ByRef dbname As String) As Boolean
str = Nothing
str = New Text.StringBuilder
With str
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append(dbname)
.Append(";Persist Security Info=False")
End With
Try
Dim sql11 As String = "select * from " & t
Dim da As New OleDbDataAdapter(sql11, New OleDbConnection(str.ToString))
Dim cb As New OleDbCommandBuilder(da)
da.Update(table)
Return True
Catch ex As Exception
System.Console.WriteLine(ex.Source)
System.Console.WriteLine(ex.StackTrace)
System.Console.WriteLine(ex.Message)
End Try
End Function
End Class
Sub search()
Try
Dim con1 As New OleDbConnection
Dim search1 As New Text.StringBuilder
With search1
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append("employee.mdb")
.Append(";Persist Security Info=False")
End With
con1.ConnectionString = search1.ToString
con1.Open()
Dim command As OleDbCommand
command = New OleDbCommand
command.Connection = con1
sql11 = " query1"
Dim paraname As String = "@empno"
System.Console.WriteLine("Enter Employee number")
Dim paravalue As Integer = System.Console.ReadLine()
parameter = New OleDbParameter
parameter.ParameterName = paraname
parameter.Value = paravalue
parameter.OleDbType = OleDbType.Integer
command.Parameters.Add(parameter)
parameter = Nothing
command.CommandType = CommandType.StoredProcedure
command.CommandText = sql11
datareader = command.ExecuteReader
Dim i As Integer
While datareader.Read
For i = 0 To datareader.FieldCount - 1
System.Console.Write(datareader.Item(i))
System.Console.Write(ControlChars.Tab)
Next
End While
System.Console.WriteLine()
Catch ex As Exception
System.Console.WriteLine(ex.Message)
System.Console.WriteLine(ex.Source)
System.Console.WriteLine(ex.StackTrace)
End Try
End Sub
Sub modify()
Try
Dim con1 As New OleDbConnection
Dim search1 As New Text.StringBuilder
With search1
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append("employee.mdb")
.Append(";Persist Security Info=False")
End With
con1.ConnectionString = search1.ToString
con1.Open()
Dim command As OleDbCommand
command = New OleDbCommand
command.Connection = con1
sql11 = "query3"
Dim paraname As String = "@empsal"
System.Console.WriteLine("Enter Employee Sal")
Dim paravalue As Integer = System.Console.ReadLine()
Dim paraname1 As String = "@empno"
System.Console.WriteLine("Enter Employee no")
Dim paravalue1 As Integer = System.Console.ReadLine()
parameter = New OleDbParameter
parameter.ParameterName = paraname
parameter.Value = paravalue
parameter.OleDbType = OleDbType.Integer
command.Parameters.Add(parameter)
parameter = Nothing
parameter = New OleDbParameter
parameter.ParameterName = paraname1
parameter.Value = paravalue1
parameter.OleDbType = OleDbType.Integer
command.Parameters.Add(parameter)
parameter = Nothing
command.CommandType = CommandType.StoredProcedure
command.CommandText = sql11
datareader = command.ExecuteReader
System.Console.WriteLine("Record Moified")
Catch ex As Exception
System.Console.WriteLine(ex.Message)
System.Console.WriteLine(ex.Source)
System.Console.WriteLine(ex.StackTrace)
End Try
End Sub
Sub delete()
Try
Dim con1 As New OleDbConnection
Dim search1 As New Text.StringBuilder
With search1
.Append("Provider=Microsoft.Jet.OLEDB.4.0;")
.Append("Data Source=")
.Append(AppDomain.CurrentDomain.BaseDirectory)
.Append("employee.mdb")
.Append(";Persist Security Info=False")
End With
con1.ConnectionString = search1.ToString
con1.Open()
Dim command As OleDbCommand
command = New OleDbCommand
command.Connection = con1
sql11 = "query2"
Dim paraname As String = "@empno"
System.Console.WriteLine("Enter Employee number")
Dim paravalue As Integer = System.Console.ReadLine()
parameter = New OleDbParameter
parameter.ParameterName = paraname
parameter.Value = paravalue
parameter.OleDbType = OleDbType.Integer
command.Parameters.Add(parameter)
parameter = Nothing
command.CommandType = CommandType.StoredProcedure
command.CommandText = sql11
datareader = command.ExecuteReader
System.Console.WriteLine("Record Deleted")
Catch ex As Exception
System.Console.WriteLine(ex.Message)
System.Console.WriteLine(ex.Source)
System.Console.WriteLine(ex.StackTrace)
End Try
End Sub
End Module
Regards
Neetu
 Attachments
ADO.Net for beginners (31216-9147-4082006(2nd).rar)
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|