SQL Server Connection String with SQL Query in VB.Net

In VB.Net, if you want to Connect MS SQL Server Database , you have to Import some SQL Connection Class to your application. Now, we proceed with step by step process for MS SQL Server Connection String in VB.Net and the Example of SQL Query in VB.Net.

Search Files From Folders and Subfolder Using Code in VB.Net

In this post SKOTechLearn describe the connection of database with SQL Server in two ways in VB.Net.

SqlConnection in VB.Net
OleDbConnection in VB.Net

So, Let's Start with both connection class with point to point description.

1. SqlConnection in VB.Net:

If you connect Database through SqlConnection connection string in VB.Net, you have to import some Namespace.

Assembly Reference	Namespace	Details
System.Data.SqlClient.dll	System.Data.SqlClient	Connection String and SQL Server Data Provider.
System.Data.dll	System.Data	Provide ADO.Net classes’ access

In Vb.Net, You have to first Import this Namespace:

VB.Net :

 Imports System.Data.SqlClient
 Imports System.Data

C# :

 using System.Data.SqlClient;
 using System.Data;

Using DateTime Format in VB.Net Simple Way.

There are following Syntax of SqlConnection:

VB.Net :

Dim myCnn As SqlConnection
myCnn  =  new SqlConnection ("Data Source=MySQLServer_Name; Initial Catalog=MYDatabase_Name; User ID=My_USERID; Password=My_Password;")

C# :

SqlConnection MyCnn ;
MyCnn  =  new SqlConnection ("Data Source=MySQLServer_Name; Initial Catalog=MYDatabase_Name; User ID=My_USERID; Password=MySQL_Password;");

When you import above given class then write following SqlConnection Connection String example:
VB.Net :

Dim mySqlConn As SqlConnection
mySqlConn = New SqlConnection("Data Source=XXX.XXX.XXX.XX; Initial Catalog=MY_ABCD_DB; User ID=sa; Password=XXXXXXXXXXX;")

 try
    mySqlConn.Open()
    MessageBox.Show ( "My Database Connection Successfully Connected" )
    mySqlConn.Close()
 Catch errex As Exception
     MessageBox.Show( "My Database Connection Problem" )
 End Try

C# :

SqlConnection mySqlConn;
mySqlConn = new SqlConnection( "Data Source=XXX.XXX.XXX.XX; Initial Catalog=MY_ABCD_DB; User ID=sa; Password=XXXXXXXXXXXX;");  
  try
   {
         mySqlConn.Open();
         MessageBox.Show ("My Database Connected Successfully.");
         mySqlConn.Close();
   }
   catch (Exception errex)
   {
         MessageBox.Show("My Database Connection Error.");
   }

Execute or write Query or SQL Statement in VB.Net through SqlConnection:

Now, Let’s write code to execute query or SQL statement and read data from it.

How to Retrieve Data or Read Data From SQL Server in VB.Net?

VB.Net :

Dim myDBCnn As SqlConnection
'SqlCommand is used for write Query Statement
Dim myDBCmd As SqlCommand
'SqlDataReader is used for Read data from database
Dim myDBReader As SqlDataReader

myDBCnn  = New SqlConnection("Data Source=XXX.XXX.XXX.XX; Initial Catalog=MYDBName; User ID=MYDBUSERID; Password=MYDBPassword;")
myDBCnn.Open()

Dim MystrQ As String
MystrQ  = ""
'Write Query String
MystrQ  = "Select * From MyDBTableName;"
'SQLCommand Syntax: SQLCommand(Query, Connection)
myDBCmd  = New SqlCommand( MystrQ , myDBCnn )
myDBReader  = myDBCmd.ExecuteReader
'Start Loop to read data one by one from Table
Do  While myDBReader.Read()
    Debug.WriteLine(myDBReader("MyField ").ToString())
loop
myDBReader.Close()
myDBCmd.Dispose()
myDBCnn.Close()
MessageBox.Show("Data Reading Process Done.")

C# :

SqlConnection mySqlCnn;
'SqlCommand is used for write Query Statement
SqlCommand myDBcmd;
'SqlDataReader is used for Read data from database
SqlDataReader myDBreader;
mySqlCnn = new SqlConnection("Data Source=XXX.XXX.XXX.XX; Initial Catalog=MYDBName; User ID=MYDBUSERID; Password=MYDBPassword;");  
 try
  {
      mySqlCnn.Open();
      'SqlCommand Syntax: SqlCommand(Query, Conection)
      myDBcmd = new SqlCommand( "Select * From MyDBTableName;", mySqlCnn );
      'ExecuteReader is used to read data from table
      myDBreader = myDBcmd.ExecuteReader();
      'Start Loop to read data one by one from Table
      while ( myDBreader.Read() ) {
           Console.WriteLine( myDBreader["MyField"] );
       }
       myDBreader.Close();
       myDBcmd.Dispose();
       mySqlCnn.Close();
       MessageBox.Show( "Query Execution Done" );
       }
       catch (Exception errex)
       {
            MessageBox.Show( "Connection or Query Error " );
        }

2. OleDbConnection in VB.Net:

Now, we proceed with second process to connect MS SQL Server in VB.Net through OleDbConnection.
If you connect through OleDbConnection Connection String in Vb.Net, you have to import some Namespace.

Assembly Reference	Namespace	Details
System.Data.OleDb.dll	System.Data.OleDb	Provide ConnectionString through data source connection.

First, You will have to import following class:

VB.Net :

 Imports System.Data.OleDb

C# :

using System.Data.OleDb;

OleDbConnection Connection String Example:

VB.Net :

Dim myOleConn As OleDbConnection
myOleConn = New OleDbConnection("Provider=SQLOLEDB; Data Source=XXX.XXX.XXX.XX; Database=MyOleDataBase ; Uid=MyUserID; Pwd=MyServerPassword;")
Try
     myOleConn.Open()
     MessageBox.Show ( "Server Connected Successfully." )
     myOleConn.Close()
Catch errex As Exception
     MessageBox.Show ( "Server Connection Error." )
End Try

C# :

OleDbConnection MyOleConn;
  MyOleConn = new OleDbConnection("Provider=SQLOLEDB; Data Source=XXX.XXX.XXX.XX; Database=MyOleDataBase; Uid=MyUserID; Pwd=MyServerPassword;");
  try
   {
      MyOleConn.Open();
      MessageBox.Show( "OleConnection Successfully." );
      MyOleConn.Close();
   }
   catch (Exception olex)
   {
      MessageBox.Show( "OleConnection Error." );
   }

OleDB ConnectionString Code in VB.Net and C#

Note: In OleDbConnection String, you have to add Provider for data source.

Now Let’s come to the Query Example of OleDbConnection.

SQL Server Execute Query or Write Query through OleDbConnection :

So, there are following way to write query for read data form SQL Server through OleDbConnection.

How to use Select Query in SQL with VB.Net ?

VB.Net :

Dim myOleConn As OleDbConnection
  'ConnectionString Setting
  myOleConn = New OleDbConnection ( "Provider=SQLOLEDB; Data Source=XXX.XXX.XXX.XX; Database=MyOleDataBase; Uid=MyUserID; Pwd=MyServerPassword;" )  
  Try
      'OleDbCommand for Query
       Dim MyOlecmd As New OleDbCommand( "Select * From MyDBTableName;" )
       MyOlecmd.Connection = myOleConn
       myOleConn.Open()
       'OleDbDataReader to read data from Table
       Dim MyOleReader As OleDbDataReader = MyOlecmd.ExecuteReader()
       'Start Loop until Read data according to Query.
       While MyOleReader.Read()
            'If you want to show Particular field then write code like given bellow.
            Console.Write( MyOleReader("MyField ").ToString() )
       End While
       MessageBox.Show ( "OleDbServer Connected Successfully." )
       'fter that close all connection including OleReader.
       MyOleReader.Close()
       MyOlecmd.Dispose()
       myOleConn.Close()
   Catch oleex As Exception
     MessageBox.Show( "OleDbServer Connection Error." )
   End Try

C# :

OleDbConnection MyOleConn ;
OleDbCommand MyOlecmd ;
OleDbDataReader MyOlereader ;

MyOleConn = new OleDbConnection( "Provider=SQLOLEDB; Data Source=XXX.XXX.XXX.XX; Database=MyOleDataBase; Uid=MyUserID; Pwd=MyServerPassword;" );
try
    {
       MyOleConn.Open();
       MyOlecmd = new OleDbCommand( "Select * From MyDBTableName;" , MyOleConn);
       MyOlereader = MyOlecmd.ExecuteReader();
       while ( MyOlereader.Read() )
       {
            Console.WriteLine(MyOlereader["MyField"]);
       }
       MessageBox.Show( "Record Show Successfully." );
       MyOlereader.Close();
       MyOlecmd.Dispose();
       MyOleConn.Close();
   }
 catch (Exception Oleex)
   {
        MessageBox.Show( "Problem in Database Connection. " );
   }

So, SKOTechLearn Described the easiest way where you can use SqlConnection or OleDbConnection class for MS SQL Server Connection String with SQL Query in VB.Net (VB and C# Code).