Thursday, May 14, 2020

SQL Server Connection String with SQL Query in VB.Net

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.

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

 1. SqlConnection :
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;

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.");
            } 

ConnectionString Code in VB.Net and C#

 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 " );
            }    
  

 2OleDbConnection :
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  style="background-color: #fcfbfb;"= 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).

0 comments: