How to call a SQL server Stored Procedure from a C# Data Object

Question

I want to use Data objects in my C#2022 Desktop application. I have spent weeks creating stored procedures in my SQL Server database.

I have spent a couple of days searching C# help files without finding a simple and complete explanation or example.

Any help would be greatly appreciated.

Jignesh Kumar · Answer

Hello Edward, Please find below n example for Datareader for get data from database, // Connection string to your SQL Server string connectionString = "YourConnectionStringHere"; // Create a SqlConnection using (SqlConnection connection = new SqlConnection(connectionString)) { // Open the connection connection.Open(); // Create a SqlCommand using (SqlCommand command = new SqlCommand("YourStoredProcedureName", connection)) { // Set the command type to Stored Procedure command.CommandType = CommandType.StoredProcedure; // Add parameters if your stored procedure has any // For example: // command.Parameters.AddWithValue("@ParameterName", parameterValue); // Execute the stored procedure SqlDataReader reader = command.ExecuteReader(); // Process the results if necessary while (reader.Read()) { // Access data using reader // For example: string value = reader.GetString(0); } } } Example for ExecutiveNonQuery for Insert, Update, Delete, string connectionString = "your_connection_string_here"; string storedProcedureName = "your_stored_procedure_name"; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(storedProcedureName, connection)) { command.CommandType = CommandType.StoredProcedure; // Add parameters if your stored procedure has any command.Parameters.Add(new SqlParameter("@ParameterName", parameterValue)); try { connection.Open(); int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine($"Rows Affected: {rowsAffected}"); } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); } } Please refer this link which will help you out, https://www.c-sharpcorner.com/article/crud-operation-in-c-sharp-windows-application-using-store-procedure/

Asma Khalid · Answer

Check this out -> http://bit.ly/2tJqs9c

Muhammad Imran Ansari · Answer

Hi Edward, In C#, you can call a SQL Server Stored Procedure using the SqlConnection class to establish a connection to the database and the SqlCommand class to execute the stored procedure. Here's a basic example: using System; using System.Data; using System.Data.SqlClient; class Program { static void Main() { string connectionString = "YourConnectionString"; // Replace with your actual connection string string storedProcedureName = "YourStoredProcedureName"; // Replace with your actual stored procedure name // Create SqlConnection and SqlCommand objects using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(storedProcedureName, connection)) { try { // Set the command type to stored procedure command.CommandType = CommandType.StoredProcedure; // Add parameters if your stored procedure has any // For example, if you have a parameter named @ParameterName: // command.Parameters.AddWithValue("@ParameterName", parameterValue); // Open the connection connection.Open(); // Execute the stored procedure command.ExecuteNonQuery(); // If your stored procedure returns data, you can use a DataReader or other methods to retrieve the results // For example, using SqlDataReader: // using (SqlDataReader reader = command.ExecuteReader()) // { // while (reader.Read()) // { // // Process the results // } // } } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); } } } } Kindly refer to the link below for better comprehension or as a point of reference. https://www.mssqltips.com/sqlservertip/5810/working-with-sql-server-stored-procedures-and-net/

Anandu G Nath · Answer

Refer beow link https://dotnettutorials.net/lesson/ado-net-using-stored-procedure/