CSharp-SQL-Stored_ProcWithReturnValues

CSharp-SQL-Stored_ProcWithReturnValues

The following two examples show the following:

1.    Code to use a stored procedure to return more than one variable as an “out” variable.

2.    The related Stored Procedure SQL

3.  How to get the new counter Id field dynamically created by the database

TOC:

Example 1

Example 2

Example 1:

CODE

using System;

using System.Data.SqlClient;

using System.IO;

using System.Text;

namespace MiscConsoleApp

{

class Program

{

static void Main(string[] args)

{

Console.WriteLine("Start test now");

RunStoredProcExample();

Console.WriteLine("Done!");

}

private static void RunStoredProcExample()

{

int Br_Id = 95;

string NameOfStoredProcedure01 = "sp_MyStoredProcText";

int Job_Id = 0;

int Item1_Id = 0;

int Item2_Id = 0;

int Item3_Id = 0;

       

DeleteStoredProcedureFromDB(NameOfStoredProcedure01);           

if (CreateStoredProcedureFromFile(NameOfStoredProcedure01 + ".sql"))

{

string jobName = "My Test SP Job";

SqlConnection sqlConnection = ReturnSQLConnection();

sqlConnection.Open();

SqlMyType sqlMyType = new SqlMyType();

sqlMyType = new SqlMyType(NameOfStoredProcedure01, sqlConnection);

sqlMyType.MyTypeType = System.Data.MyTypeType.StoredProcedure;

//Note that the first two non-out parameters do not use the "@" prefix:

sqlMyType.Parameters.Add(new SqlParameter("JobName", jobName));

sqlMyType.Parameters.Add(new SqlParameter("Br_id", Br_Id));

//Note that the following "out" parameters are the ones that we

//want returned from the Stored Procedure so they are sent with

//a prefixed "@" and given starting values of 0 (zero):

//For EACH of these out parameters we have to call the direction.

//I tried just at the beginning or at the end and it did not work.

SqlParameter OutParams = sqlMyType.Parameters.AddWithValue("@Job_Id", 0);

OutParams.DbType = System.Data.DbType.Int32;

OutParams.Direction = System.Data.ParameterDirection.Output;               

OutParams = sqlMyType.Parameters.AddWithValue("@Item1_Id", 0);

OutParams.DbType = System.Data.DbType.Int32;

OutParams.Direction = System.Data.ParameterDirection.Output;               

OutParams = sqlMyType.Parameters.AddWithValue("@Item2_Id", 0);

OutParams.DbType = System.Data.DbType.Int32;

OutParams.Direction = System.Data.ParameterDirection.Output;               

OutParams = sqlMyType.Parameters.AddWithValue("@Item3_Id", 0);

OutParams.DbType = System.Data.DbType.Int32;

OutParams.Direction = System.Data.ParameterDirection.Output;               

sqlMyType.ExecuteNonQuery();

//After the Stored Proc is run our "out" variables will have

//the values that the Stored Procedure filled in during its

//execution.

Job_Id = Convert.ToInt32(sqlMyType.Parameters["@Job_Id"].Value);

Item1_Id = Convert.ToInt32(sqlMyType.Parameters["@Item1_Id"].Value);

Item2_Id = Convert.ToInt32(sqlMyType.Parameters["@Item2_Id"].Value);

Item3_Id = Convert.ToInt32(sqlMyType.Parameters["@Item3_Id"].Value);

sqlConnection.Close();

Console.WriteLine("Job_Id = {0}", Job_Id);

Console.WriteLine("Item1_Id = {0}", Item1_Id);

Console.WriteLine("Item2_Id = {0}", Item2_Id);

Console.WriteLine("Item3_Id = {0}", Item3_Id);

}

else

{

Console.WriteLine("Unable to create stored procedure: \"" + NameOfStoredProcedure01 + "\"");

}

DeleteStoredProcedureFromDB(NameOfStoredProcedure01);

}

#region SQLHelpers

protected static SqlConnection ReturnSQLConnection()

{

string SQLConnectionString = "Data Source=BoxWithSQLServer;Initial Catalog=MyDatabaseName;Integrated Security=True";

return new SqlConnection(SQLConnectionString);

}

protected static void KillSqlConnection(SqlConnection conn)

{

try

{

conn.Close();

}

catch { }

}

protected static bool CreateStoredProcedureFromFile(string NameOfFileInCurrentDir)

{

SqlConnection sqlConnection = ReturnSQLConnection();

sqlConnection.Open();

SqlMyType sqlMyType = new SqlMyType();

sqlMyType.Connection = sqlConnection;

sqlMyType.MyTypeType = System.Data.MyTypeType.Text;

sqlMyType.MyTypeText = ReturnFileText(NameOfFileInCurrentDir);

sqlMyType.ExecuteNonQuery();

KillSqlConnection(sqlConnection);

return true;

}

protected static void DeleteStoredProcedureFromDB(string NameOfStoredProcedure)

{

try

{

SqlConnection sqlConnection = ReturnSQLConnection();

sqlConnection.Open();

SqlMyType sqlMyType = new SqlMyType();

sqlMyType.Connection = sqlConnection;

sqlMyType.MyTypeType = System.Data.MyTypeType.Text;

sqlMyType.MyTypeText = "use MyDB; drop procedure " + NameOfStoredProcedure;

sqlMyType.ExecuteNonQuery();

KillSqlConnection(sqlConnection);

}

catch { }

}

protected static string ReturnFileText(string FullFileWithPath)

{

    FileStream fs;

    fs = File.OpenRead(FullFileWithPath);

    byte[] b = new byte[fs.Length];

    UTF8Encoding temp = new UTF8Encoding(true);

    fs.Read(b, 0, b.Length);  

    return temp.GetString(b).Trim();

}

#endregion //SQLHelpers

}

}

RELATED STORED PROCEDURE

CREATE PROCEDURE sp_SetUpJobForMultipleSPCalls

(

  @JobName varchar(256), 

  @Br_Id int,

  @Job_Id int output,

  @Item1_Id int output,

  @Item2_Id int output,

  @Item3_Id int output

)

AS

BEGIN

  SET NOCOUNT ON

  SET DEADLOCK_PRIORITY LOW

  INSERT INTO dbo.Job (Resource_Id, Priority_Id, Name ,[Type])

    Values (@Br_Id, 1, @JobName, 'Build')

  SELECT @Job_Id = SCOPE_IDENTITY()

  INSERT INTO dbo.Item (Name, [Type], Job_Id)

    Values ('Item1', 'MyType', @Job_Id)

  SELECT @Item1_Id = SCOPE_IDENTITY()

  INSERT INTO dbo.Item (Name, [Type], Job_Id)

    Values ('Item2', 'MyType', @Job_Id)

  SELECT @Item2_Id = SCOPE_IDENTITY()

  INSERT INTO dbo.Item (Name, [Type], Job_Id)

    Values ('Item3', 'MyType', @Job_Id)

  SELECT @Item3_Id = SCOPE_IDENTITY()

END

Example 2:

Another example:

CREATE PROCEDURE sp_SetUpNewProductForTest

(

  @productNameToCreate varchar(256), 

  @productCreatedId int output

)

AS

BEGIN

INSERT INTO MyDB.dbo.Product (Name)

  SELECT @productNameToCreate

  SELECT @productCreatedId = SCOPE_IDENTITY()

END

Code example to use the above SQL:

public static bool Execute_sp_CreateProducTableEntry(TestSQLPopulation testSQLPopulation)

        {

            try

            {

                using (SqlConnection sqlConnection = ReturnSQLConnection())

                {

                    sqlConnection.Open();

                    SqlCommand sqlCommand = new SqlCommand();

                    sqlCommand = new SqlCommand("sp_SetUpNewProductForTest", sqlConnection);

                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                   sqlCommand.Parameters.Add(new SqlParameter("productNameToCreate", testSQLPopulation.productName));

                    SqlParameter OutParams = sqlCommand.Parameters.AddWithValue("@productCreatedId", 0);

                    OutParams.DbType = System.Data.DbType.Int32;

                    OutParams.Direction = System.Data.ParameterDirection.Output;

                    sqlCommand.ExecuteNonQuery();

                    testSQLJobPopulation.productId = Convert.ToInt32(sqlCommand.Parameters["@productCreatedId"].Value);

                }

                return true;

            }

            catch (Exception errorNow)

            {

                Console.WriteLine("Error when running myStoredProcedure:");

                Console.WriteLine(errorNow.ToString());

                return false;

            }           

        }