Connecting to a SQL Server Data Provider

A small program to connect to the SQL server database provider
  • Create a console application say SqlConnectOnly
  • Obtain an interface reference of type IDbConnection

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

 Using Commands
  • After we have opened a connection to a data source, we can create a command object, which executes a query against a data source
  • The following code illustrates creating a command object and returning an IDbCommand interface reference
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
  • After we have created a command object we can call the ExecuteReader method to return an IDataReader.
  • With the data reader, we can obtain a read only, forward-only stream of data.
  • This method is suitable for reading large amount of data.
  • When you are finished with the data reader you should explicitly close it.
  • Any output parameters or return values of the command object are not available until after the data reader has been closed
  • Data readers have the Item property that can be used for accessing the current record.
  • The item property accepts either an integer representing column number or string representing column name
  • The Item property is the default property and can be omitted if desired
  • The Read method is used to advance the data reader to the next row
  • You must call Read before accessing the data
  • The Read returns true if there are more rows otherwise it returns false.
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

Our aim is to provide information to the knowledge seekers. 


comments powered by Disqus




Footer1