How to execute a stored procedure within C program

Question

I want to execute this stored procedure from a C  program   I have written the following stored procedure in a SqlServer query window and saved it as  stored1   use master  go create procedure dbo test as  DECLARE  command as varchar 1000    i int SET  i   0 WHILE  i  lt  5 BEGIN Print  I VALUE    CONVERT varchar 20   i  EXEC  command  SET  i    i   1 END   EDITED   using System  using System Collections Generic  using System Text  using System Data  using System Data SqlClient  namespace AutomationApp       class Program               public void RunStoredProc                         SqlConnection conn   null              SqlDataReader rdr    null               Console WriteLine   nTop 10 Most Expensive Products  n                 try                               conn   new SqlConnection  Server  local  DataBase master Integrated Security SSPI                    conn Open                    SqlCommand cmd   new SqlCommand  dbo test   conn                   cmd CommandType   CommandType StoredProcedure                  rdr   cmd ExecuteReader                      while  rdr Read                                          Console WriteLine                           Product   0 -25  Price    1 6      00                            rdr  TenMostExpensiveProducts                            rdr  UnitPrice                                                   finally                               if  conn    null                                        conn Close                                      if  rdr    null                                        rdr Close                                                      static void Main string   args                        Console WriteLine  Hello World                Program p  new Program                p RunStoredProc                      Console Read                        This displays the exception Cannot find the stored procedure dbo test  Do I need to provide the path  If yes  in which location should the stored procedures be stored

User · Answer

You mean that your code is DDL  If so  MSSQL has no difference  Above examples well shows how to invoke this  Just ensure  CommandType   CommandType Text

User · Answer

using  SqlConnection conn   new SqlConnection  Server  local  DataBase Northwind Integrated Security SSPI          conn Open            1   create a command object identifying the stored procedure     SqlCommand cmd    new SqlCommand  CustOrderHist   conn           2  set the command object so it knows to execute a stored procedure     cmd CommandType   CommandType StoredProcedure          3  add parameter to command  which will be passed to the stored procedure     cmd Parameters Add new SqlParameter   CustomerID   custId            execute the command     using  SqlDataReader rdr   cmd ExecuteReader                 iterate through results  printing each to console         while  rdr Read                          Console WriteLine  Product   0 -35  Total   1 2   rdr  ProductName   rdr  Total                         Here are some interesting links you could read    http   www csharp-station com Tutorials AdoDotNet Lesson07 aspx http   www c-sharpcorner com UploadFile dclark InsOutsinCS11302005072332AM InsOutsinCS aspx http   www codeproject com KB cs simplecodeasp aspx http   msdn microsoft com en-us library ms171921 VS 80  aspx

User · Answer

using  SqlConnection sqlConnection1   new SqlConnection  Your Connection String      using  SqlCommand cmd   new SqlCommand        Int32 rowsAffected     cmd CommandText    StoredProcedureName     cmd CommandType   CommandType StoredProcedure    cmd Connection   sqlConnection1     sqlConnection1 Open       rowsAffected   cmd ExecuteNonQuery

User · Answer

Calling Store Procedure in C       SqlCommand cmd   new SqlCommand  StoreProcedureName  con       cmd CommandType CommandType StoredProcedure      cmd Parameters AddWithValue   value  txtValue Text       con Open        int rowAffected cmd ExecuteNonQuery        con Close

User · Answer

SqlConnection conn   null  SqlDataReader rdr    null  conn   new SqlConnection  Server  local  DataBase Northwind Integrated Security SSPI    conn Open        1   create a command object identifying        the stored procedure SqlCommand cmd    new SqlCommand  CustOrderHist   conn       2  set the command object so it knows       to execute a stored procedure cmd CommandType   CommandType StoredProcedure      3  add parameter to command  which       will be passed to the stored procedure cmd Parameters Add new SqlParameter   CustomerID   custId        execute the command rdr   cmd ExecuteReader        iterate through results  printing each to console while  rdr Read          Console WriteLine  Product   0 -35  Total   1 2    rdr  ProductName    rdr  Total

User · Answer

What I made  in my case I wanted to show procedure s result in dataGridView  using  var command   new SqlCommand  quot ProcedureNameHere quot   connection                   Set command type and add Parameters             CommandType   CommandType StoredProcedure              Parameters     new SqlParameter  quot  parameterName quot  parameterValue                                         Execute command in Adapter and store to dataset             var adapter   new SqlDataAdapter command               var dataset   new DataSet                adapter Fill dataset                  Display results in DatagridView             dataGridView1 DataSource   dataset Tables 0

User · Answer

this is an example of a stored procedure that returns a value and it s execution in c   CREATE PROCEDURE  dbo   InsertPerson     -- Add the parameters for the stored procedure here    FirstName nvarchar 50   LastName nvarchar 50      PersonID int output   AS   BEGIN       insert  dbo   Person  LastName FirstName  Values  LastName  FirstName         set  PersonID SCOPE IDENTITY     END   Go     --------------     Using stored procedure in adapter to insert new rows and update the identity value       static void InsertPersonInAdapter String connectionString  String firstName  String lastName            String commandText    dbo InsertPerson           using  SqlConnection conn   new SqlConnection connectionString                SqlDataAdapter mySchool   new SqlDataAdapter  Select PersonID FirstName LastName from  dbo   Person    conn               mySchool InsertCommand   new SqlCommand commandText  conn              mySchool InsertCommand CommandType   CommandType StoredProcedure              mySchool InsertCommand Parameters Add                 new SqlParameter   FirstName   SqlDbType NVarChar  50   FirstName                mySchool InsertCommand Parameters Add                 new SqlParameter   LastName   SqlDbType NVarChar  50   LastName                 SqlParameter personId   mySchool InsertCommand Parameters Add new SqlParameter   PersonID   SqlDbType Int  0   PersonID                personId Direction   ParameterDirection Output              DataTable persons   new DataTable               mySchool Fill persons               DataRow newPerson   persons NewRow               newPerson  FirstName     firstName             newPerson  LastName     lastName             persons Rows Add newPerson               mySchool Update persons              Console WriteLine  Show all persons                ShowDataTable persons  14

User · Answer

By using Ado net  using System  using System Data  using System Data SqlClient  using System Configuration   namespace PBDataAccess       public class AddContact                     for preparing connection to sql server database             private SqlConnection conn               for preparing sql statement or stored procedure that             we want to execute on database server          private SqlCommand cmd               used for storing the result in datatable  basically             dataset is collection of datatable          private DataSet ds               datatable just for storing single table          private DataTable dt               data adapter we use it to manage the flow of data            from sql server to dataset and after fill the data             inside dataset using fill   method             private SqlDataAdapter da                created a method  which will return the dataset          public DataSet GetAllContactType                        retrieving the connection string from web config  which will         tell where our database is located and on which database we want        to perform opearation  in this case we are working on stored         procedure so you might have created it somewhere in your database          connection string will include the name of the datasource  your         database name  user name and password           using  conn   new SqlConnection ConfigurationManager ConnectionString  conn            ConnectionString                                              Addcontact is the name of the stored procedure                     using  cmd   new SqlCommand  Addcontact   conn                                                   cmd CommandType   CommandType StoredProcedure                          here we are passing the parameters that                         Addcontact stored procedure expect                       cmd Parameters Add   CommandType                        SqlDbType VarChar  50  Value    GetAllContactType                                here created the instance of SqlDataAdapter                            class and passed cmd object in it                         da   new SqlDataAdapter cmd                                created the dataset object                         ds   new DataSet                                 fill the dataset and your result will be                         stored in dataset                         da Fill ds                                                                          return ds                      Stored Procedure         CREATE PROCEDURE Addcontact  CommandType VARCHAR MAX    NULL AS BEGIN   IF   CommandType    GetAllContactType     BEGIN     SELECT   FROM Contacts   END END

User · Answer

This is code for executing stored procedures with and with out parameters via reflection  Do note that the objects property names need to match the parameters of the stored procedure   private static string ConnString   ConfigurationManager ConnectionStrings  SqlConnection   ConnectionString      private SqlConnection Conn   new SqlConnection ConnString        public void ExecuteStoredProcedure string procedureName                SqlConnection sqlConnObj   new SqlConnection ConnString            SqlCommand sqlCmd   new SqlCommand procedureName  sqlConnObj           sqlCmd CommandType   CommandType StoredProcedure           sqlConnObj Open            sqlCmd ExecuteNonQuery            sqlConnObj Close               public void ExecuteStoredProcedure string procedureName  object model                var parameters   GenerateSQLParameters model           SqlConnection sqlConnObj   new SqlConnection ConnString            SqlCommand sqlCmd   new SqlCommand procedureName  sqlConnObj           sqlCmd CommandType   CommandType StoredProcedure           foreach  var param in parameters                        sqlCmd Parameters Add param                      sqlConnObj Open            sqlCmd ExecuteNonQuery            sqlConnObj Close               private List lt SqlParameter gt  GenerateSQLParameters object model                var paramList   new List lt SqlParameter gt             Type modelType   model GetType            var properties   modelType GetProperties            foreach  var property in properties                        if  property GetValue model     null                                paramList Add new SqlParameter property Name  DBNull Value                              else                               paramList Add new SqlParameter property Name  property GetValue model                                     return paramList

User · Answer

No Dapper answer here  So I added one  using Dapper  using System Data SqlClient   using  var cn   new SqlConnection   Server  local  DataBase master Integrated Security SSPI        cn Execute  dbo test   commandType  CommandType StoredProcedure

User · Answer

Using Dapper  so i added this i hope anyone help   public void Insert ProductName obj                        SqlConnection connection   new SqlConnection Connection GetConnectionString                 connection Open                connection Execute  ProductName sp   new                Name   obj Name   Code   obj Code   CategoryId   obj CategoryId   CompanyId   obj CompanyId   ReorderLebel   obj ReorderLebel   logo   obj logo  Status obj Status   ProductPrice   obj ProductPrice                   SellingPrice   obj SellingPrice   VatPercent   obj VatPercent   Description obj Description   ColourId   obj ColourId   SizeId   obj SizeId                   BrandId   obj BrandId   DisCountPercent   obj DisCountPercent   CreateById  obj CreateById   StatementType    Create     commandType  CommandType StoredProcedure               connection Close

User · Answer

Please check out Crane  I m the author   https   www nuget org packages Crane   SqlServerAccess sqlAccess   new SqlServerAccess  your connection string    var result   sqlAccess Command   ExecuteNonQuery  StoredProcedureName      Also has a bunch of other features you might like

User · Answer

using  var conn   new SqlConnection connectionString   using  var command   new SqlCommand  ProcedureName   conn                                CommandType   CommandType StoredProcedure         conn Open       command ExecuteNonQuery

[c#] How to execute a stored procedure within C# program

Examples related to c#

Examples related to sql-server

Examples related to stored-procedures