- Introduction
- 1st Example: Stored Procedure Returns No Value
- 2nd Example: Stored Procedure Returns Integer
- 3rd Example: Stored Procedure Returns Table
- 4th Example: Stored Procedure With Table-Value Parameter
- How it works
- Conclusion
Introduction
I have recently made the transition from a C++ job to a C# job. There are so many new and exciting things to learn in the .NET world that I got carried away and wrote a very naive article on calling store procedure from C#. This article presents an application that can generate C# methods to call your stored procedure; I modified the library from the previous article to write this Stored Procedure Caller Generator. The old library is called Stored Procedure Caller. The code generation is mainly done by the new SPCallerGen class. It is a very simple class.
1st Example: Stored Procedure Returns No Value
SPCG needs the stored procedure signature to generate the method. There are three things we need to supply to SPCG: stored procedure signature, method name, and the return type of the stored procedure. There are three return types to choose from: Tables, Integer and None. The Tables option will generate a method, which return a DataSet. The Integer option will return an integer from the stored procedure. The None option is for stored procedures that return nothing. For our first example, we will call the sp_InsertEmp. sp_InsertEmp is a very simple store procedure to insert an employee record. There is one OUTPUT parameter called ID, which is the new employee ID.
CREATE PROCEDURE [dbo].[sp_InsertEmp]( @ID int OUTPUT, @Name nvarchar(30), @Title varchar(20), @Address varchar(30), @Salary money, @JoinedDate datetime, @Children tinyint)
If anyone is interested, this is the table creation script for Employee.
CREATE TABLE [dbo].[Employee]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](30) NOT NULL, [Title] [varchar](20) NOT NULL, [Address] [varchar](30) NOT NULL, [Salary] [money] NOT NULL, [JoinedDate] [datetime] NOT NULL, [Children] [tinyint] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Below is the input, we use for SPCG application.
Method Name : InsertEmployee Return type : None
This is the generated method. The developer may need to change the connection string name according to what is already defined in the source file. Notice the ID parameter is a ref type, which will hold the outputted value from sp_InsertEmp. The reader may also notice that all the value types are nullable types. Nullable type declaration sometimes is done by appending ?, rather than using the full proper declaration, for instance, Nullable
public void InsertEmployee( ref int ID, string Name, string Title, string Address, decimal? Salary, DateTime? JoinedDate, byte? Children) { SqlConnection connection = new SqlConnection(ConnectionStr); try { connection.Open(); SqlCommand command = new SqlCommand("[dbo].[sp_InsertEmp]", connection); command.CommandType = CommandType.StoredProcedure; SqlParameter parameter = null; parameter = new SqlParameter("@ID", SqlDbType.Int); parameter.Direction = System.Data.ParameterDirection.Output; parameter.Value = ID; command.Parameters.Add(parameter); parameter = new SqlParameter("@Name", SqlDbType.NVarChar, 30); if (Name == null) parameter.Value = DBNull.Value; else parameter.Value = Name; command.Parameters.Add(parameter); parameter = new SqlParameter("@Title", SqlDbType.VarChar, 20); if (Title == null) parameter.Value = DBNull.Value; else parameter.Value = Title; command.Parameters.Add(parameter); parameter = new SqlParameter("@Address", SqlDbType.VarChar, 30); if (Address == null) parameter.Value = DBNull.Value; else parameter.Value = Address; command.Parameters.Add(parameter); parameter = new SqlParameter("@Salary", SqlDbType.Money); if (Salary == null) parameter.Value = DBNull.Value; else parameter.Value = Salary; command.Parameters.Add(parameter); parameter = new SqlParameter("@JoinedDate", SqlDbType.DateTime); if (JoinedDate == null) parameter.Value = DBNull.Value; else parameter.Value = JoinedDate; command.Parameters.Add(parameter); parameter = new SqlParameter("@Children", SqlDbType.TinyInt); if (Children == null) parameter.Value = DBNull.Value; else parameter.Value = Children; command.Parameters.Add(parameter); command.ExecuteNonQuery(); ID = Convert.ToInt32(command.Parameters["@ID"].Value); } catch (Exception exp) { throw exp; } finally { connection.Close(); } return; }