Executing SQL Queries And SQL Stored Procedure With ODBC

In this article, you will learn how to execute SQL queries and SQL Stored procedure with ODBC.

Introduction

 
This article demonstrates how to execute SQL queries, stored procedure using ODBC, how to pass a parameter to a stored procedure using ODBC, and return output parameter from stored procedure using ODBC.
 
ODBC (Open Database Connectivity) driver helps to access data in applications from the database management system.
 
Prerequisites
  • Visual Studio
  • MS SQL
Note
In this example, I am using Visual Studio 2019 community edition and the framework version is 4.7.2 with SQL server 2014.
 
Let’s code.
 
In this example, we will use two SQL tables and Windows form applications to display the result on the screen.
  1. Customers: - SQL table for customers record
  2. Orders: - SQL table for orders of customers
  3. .NET Framework Windows Form application screen like below.

    Executing SQL Queries And SQL Stored Procedure With ODBC
Show a button for showing the record on the screen.
 

Execute the SELECT command using ODBC

 
In the below example, I have added an inline ODBC connection string to make the connection between database and application. We can also use ODBC DSN for making a connection between the database and the application.
 
Execute the following code to check the result for SELECT command.
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1. private void BtnShow_Click(object sender, EventArgs e)    
  2. {    
  3.     #region Execute SELECT command using ODBC: -     
  4.     
  5.     OdbcConnection conn = new OdbcConnection();    
  6.     DataSet ds = new DataSet();    
  7.     
  8.     conn.ConnectionString = @"Driver={SQL Server}"    
  9.                             + "Server=server name;DataBase=database name;";    
  10.     
  11.     String selectSql = "SELECT * FROM Customers;";    
  12.     OdbcCommand cmd = new OdbcCommand(selectSql, conn);    
  13.     OdbcDataAdapter da = new OdbcDataAdapter(cmd);    
  14.     da.Fill(ds);    
  15.     
  16.     dgvODBC.AutoGenerateColumns = true;    
  17.     dgvODBC.DataSource = ds.Tables[0];    
  18.     
  19.     conn.Close();    
  20.   
  21.     #endregion    
  22. }  
Output
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
 

SQL INNER join using ODBC

 
Code
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1.  private void BtnShow_Click(object sender, EventArgs e)    
  2.  {    
  3.             #region SQL INNER join using ODBC    
  4.     
  5.             OdbcConnection conn = new OdbcConnection();    
  6.             DataSet ds = new DataSet();    
  7.             conn.ConnectionString = @"Driver={SQL Server};Server=server name;DataBase=database name;";    
  8.     
  9.             String selectSql = "SELECT * FROM Customers C INNER JOIN Orders O ON C.CustomerId= o.CustomerId";    
  10.             OdbcCommand cmd = new OdbcCommand(selectSql, conn);    
  11.             OdbcDataAdapter da = new OdbcDataAdapter(cmd);    
  12.             da.Fill(ds);    
  13.             dgvODBC.AutoGenerateColumns = true;    
  14.             dgvODBC.DataSource = ds.Tables[0];    
  15.             conn.Close();    
  16.       #endregion      
  17.  
Output
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
 

SQL INSERT query using ODBC

 
We cannot pass the scaler variable in SQL query with ODBC. If you try to pass that, then it will throw an exception like below.
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
 
“’ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@VariableName’.”
 
Then, how do we pass a parameter?
  • We have a question mark(?) placeholder to solve this issue.
  • The order should be proper while working with question mark placeholder
Refer to the below code.
 
Code
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1. private void BtnShow_Click(object sender, EventArgs e)    
  2.       {    
  3.           OdbcConnection conn = new OdbcConnection();    
  4.           DataSet ds = new DataSet();    
  5.           conn.ConnectionString = @"Driver={SQL Server};Server=DESKTOP-H9JA9S4\SQLEXPRESS;DataBase=EmployeeDB;";    
  6.     
  7.           String insertSql = "INSERT INTO Customers(CustomerId, Name, City, OrderId) VALUES(?, ?, ?, ?)";    
  8.           OdbcCommand cmd = new OdbcCommand(insertSql, conn);    
  9.           conn.Open();    
  10.           cmd.Parameters.Add("CustomerId", OdbcType.Int).Value = 3;    
  11.           cmd.Parameters.Add("Name", OdbcType.VarChar).Value = "Admin";    
  12.           cmd.Parameters.Add("City", OdbcType.VarChar).Value = "Hyderabad";    
  13.           cmd.Parameters.Add("OrderId", OdbcType.Int).Value = 4;    
  14.           cmd.ExecuteNonQuery();    
  15.     
  16.           String selectSql = "SELECT * FROM Customers;";    
  17.           cmd = new OdbcCommand(selectSql, conn);    
  18.           OdbcDataAdapter da = new OdbcDataAdapter(cmd);    
  19.           da.Fill(ds);    
  20.           dgvODBC.AutoGenerateColumns = true;    
  21.           dgvODBC.DataSource = ds.Tables[0];    
  22.           conn.Close();    
  23.       }  
Output
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
 
For Insert and Update queries also, we can use the same technique.
 

SQL Stored Procedure using ODBC

 
In this example, we will add a stored procedure with name GetCustomerDetails which accepts a parameter as id and based on Id, we will get a customer's record.
 
SQL query for the stored procedure.
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1. CREATE PROCEDURE [dbo].[GetCustomerDetails]     
  2. (    
  3.   @ID INT    
  4. )    
  5. AS BEGIN     
  6.     SELECT * FROM Customers C INNER JOIN Orders O ON C.CustomerId= o.CustomerId     
  7.     WHERE C.CustomerId = @ID    
  8. END     
  9. GO    
C# Code
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1. private void BtnShow_Click(object sender, EventArgs e)    
  2.     {    
  3.         OdbcConnection conn = new OdbcConnection();    
  4.         OdbcCommand cmd = new OdbcCommand();    
  5.         DataSet ds = new DataSet();    
  6.     
  7.         conn.ConnectionString = @"Driver={SQL Server};Server=servername;DataBase=database name;";    
  8.     
  9.         cmd.Connection = conn;    
  10.         cmd.CommandType = System.Data.CommandType.Text;    
  11.         cmd.CommandText = "EXEC dbo.GetCustomerDetails @ID=? ";    
  12.         cmd.Parameters.Add("?", OdbcType.Int).Value = 2;    
  13.     
  14.         OdbcDataAdapter da = new OdbcDataAdapter(cmd);    
  15.         da.Fill(ds);    
  16.         dgvODBC.AutoGenerateColumns = true;    
  17.         dgvODBC.DataSource = ds.Tables[0];    
  18.         conn.Close();    
  19.     }   
Output
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
 

SQL Stored Procedure with OUTPUT parameter using ODBC

 
In this example, we will alter the stored procedure and add one more parameter to return the name of the customer based on customer id.
 
SQL query for the stored procedure.
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1. CREATE PROCEDURE [dbo].[GetCustomerDetails]     
  2. (    
  3.   @ID INT,     
  4.   @Name VARCHAR(20) OUTPUT    
  5. )    
  6. AS BEGIN     
  7.     SELECT C.Name FROM Customers C INNER JOIN Orders O ON C.CustomerId= o.CustomerId     
  8.     WHERE C.CustomerId = @ID    
  9. END     
  10.     
  11. GO   
C# Code
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
  1. private void BtnShow_Click(object sender, EventArgs e)    
  2.  {    
  3.       OdbcConnection conn = new OdbcConnection();    
  4.       OdbcCommand cmd = new OdbcCommand();    
  5.       DataSet ds = new DataSet();    
  6.     
  7.       conn.ConnectionString = @"Driver={SQL Server};Server=server name;DataBase=database name;";    
  8.     
  9.       cmd.Connection = conn;    
  10.       cmd.CommandType = System.Data.CommandType.Text;    
  11.       cmd.CommandText = "DECLARE @CustomerName VARCHAR(20) "    
  12.                       + "EXEC dbo.GetCustomerDetails @ID=?, @Name=@CustomerName OUTPUT";    
  13.       cmd.Parameters.Add("Id", OdbcType.Int).Value = 2;    
  14.     
  15.       OdbcDataAdapter da = new OdbcDataAdapter(cmd);    
  16.       da.Fill(ds);    
  17.       dgvODBC.AutoGenerateColumns = true;    
  18.       dgvODBC.DataSource = ds.Tables[0];    
  19.       conn.Close();    
  20.     
  21.   }    
Output
 
Executing SQL Queries And SQL Stored Procedure With ODBC 
 

Summary

 
In this article, I demonstrated how to execute SQL query and stored procedure using ODBC, pass a parameter to a stored procedure using ODBC, and return output parameter from stored procedure using ODBC. Hopefully, it will help.