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
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: -
  5. OdbcConnection conn = new OdbcConnection();
  6. DataSet ds = new DataSet();
  8. conn.ConnectionString = @"Driver={SQL Server}"
  9. + "Server=server name;DataBase=database name;";
  11. String selectSql = "SELECT * FROM Customers;";
  12. OdbcCommand cmd = new OdbcCommand(selectSql, conn);
  13. OdbcDataAdapter da = new OdbcDataAdapter(cmd);
  14. da.Fill(ds);
  16. dgvODBC.AutoGenerateColumns = true;
  17. dgvODBC.DataSource = ds.Tables[0];
  19. conn.Close();
  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
  5. OdbcConnection conn = new OdbcConnection();
  6. DataSet ds = new DataSet();
  7. conn.ConnectionString = @"Driver={SQL Server};Server=server name;DataBase=database name;";
  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?
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;";
  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();
  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();
  7. conn.ConnectionString = @"Driver={SQL Server};Server=servername;DataBase=database name;";
  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;
  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
  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();
  7. conn.ConnectionString = @"Driver={SQL Server};Server=server name;DataBase=database name;";
  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;
  15. OdbcDataAdapter da = new OdbcDataAdapter(cmd);
  16. da.Fill(ds);
  17. dgvODBC.AutoGenerateColumns = true;
  18. dgvODBC.DataSource = ds.Tables[0];
  19. conn.Close();
  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.