stu nicholls dot com | menu - Professional dropdown #3

Connecting to a SQL Server Data Provider

A small program to connect to the SQL server database provider

The program illustrates the ConnectionString and State properties of the connection object and the open method.

Using Commands
Private Function createcommand(ByVal query As String) As IDbCommand
Return New SqlCommand(query, sqlConn)
End Function

ExecuteNonQuery
The following code illustrates executing a SQL DELETE statement using a command object. We create a query string for the command, and obtain a command object for this command. The call to ExecuteNonQuery returns the number of rows that were updated.

Private Sub RemoveName()
Dim query As String = "delete from StudentInfo where Name ='aaa‘ "
Dim Command As IDbCommand = createcommand(query)
Dim numrow As Integer = Command.ExecuteNonQuery()
Console.WriteLine("{0}rows updated", numrow)
End Sub

Using Data Reader Example
Imports System.Data.SqlClient
Module Module1
Dim connstr As String = "Data Source=USER;Initial
Catalog=Students;Integrated Security=True"
Dim sqlConn As New SqlConnection()
Dim conn As IDbConnection = sqlConn
Sub Main()
sqlConn.ConnectionString = connstr
Console.WriteLine("Database state :" & sqlConn.State.ToString())
sqlConn.Open()
Console.WriteLine("Database state:" & sqlConn.State.ToString())
showlist()
End Sub
Private Sub showlist()
Dim query As String = "select * from StudInfo"
Dim command As New SqlCommand(query, sqlConn)
Dim reader As SqlDataReader = command.ExecuteReader()
Console.WriteLine("Reading from Database")
While reader.Read()
Console.WriteLine(reader("RegNum"))
Console.WriteLine(reader("name"))
End While
reader.Close()
End Sub
End Module



The program is written using Interface reference to type IDbCommand Imports System.Data.SqlClient
Module Module2
Dim connstr As String = "Data Source=USER;Initial Catalog=Students;Integrated Security=True"
Dim sqlConn As New SqlConnection()
Dim conn As IDbConnection = sqlConn
Sub Main()
conn.ConnectionString = connstr
Console.WriteLine("Database state-IDbConnection:" & conn.State.ToString())
conn.Open()
Console.WriteLine("Database state-IDbConnection:" & conn.State.ToString())
showlist()
End Sub
Private Function createcommand(ByVal query As String) As IDbCommand
Return New SqlCommand(query, sqlConn)
End Function
Private Sub showlist()
Dim query As String = "select * from StudInfo"
Dim command As IDbCommand = createcommand(query)
Dim reader As IDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine(reader("RegNum"))
Console.WriteLine(reader("name"))
End While
reader.Close()
End Sub
End Module



All Tasks:
Dim sqlConn As New SqlConnection()
Sub Main()
Dim n As Integer
Dim nm As String
Dim cl As String
sqlConn.ConnectionString = connstr
Console.WriteLine("Database state:" & sqlConn.State.ToString())
sqlConn.Open()
Console.WriteLine("Database state:" & sqlConn.State.ToString())
showlist()
Console.Write("Input the Register number: ")
n = Console.ReadLine()
Console.Write("Input the Name: ")
nm = Console.ReadLine()
Console.Write("input the Class: ")
cl = Console.ReadLine()
AddName(n, nm, cl)
showlist()
Console.Write("Input the Name to delete: ")
nm = Console.ReadLine()
RemoveName(nm)
showlist()
End Sub
Private Sub showlist()
Dim query As String = "select * from StudInfo"
Dim command As New SqlCommand(query, sqlConn)
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine(reader("RegNum"))
Console.WriteLine(reader("Name"))
Console.WriteLine(reader("Class"))
End While
reader.Close()
End Sub
Private Sub AddName(ByVal n As Integer, ByVal Nm1 As String, ByVal cl1 As String)
Dim query As String = "insert into StudInfo values('" & n & "','" & Nm1 & "', '" & cl1 & "')"
Dim command As New SqlCommand(query, sqlConn)
Dim numrow As Integer = command.ExecuteNonQuery()
Console.WriteLine("{0} rows updated", numrow)
End Sub
Private Sub RemoveName(ByVal nm1 As String)
Dim query As String = "delete from StudInfo where Name= '" & nm1 & "'"
Dim command As New SqlCommand(query, sqlConn)
Dim numrow As Integer = command.ExecuteNonQuery()
Console.WriteLine("{0} rows updated", numrow)
End Sub
End Module

Displaying and Inserting Records:

Displaying and Inserting Records:

Using OLEDb Provider Using Access Database Imports System.Data.Oledb
Module Module1
Sub main()
Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\Content-pp\.Net\db1.mdb"
Dim jetConn As New OleDbConnection()
jetConn.ConnectionString = connstr
Console.WriteLine("Using Access database")
Console.WriteLine("Database state:" & jetConn.State.ToString())
jetConn.Open()
Console.WriteLine("Database state:" & jetConn.State.ToString())
End Sub
End Module



ADO<< Previous    Next>>Datasets


comments powered by Disqus
Footer1