Return Value From SQL Server to C#

On some occasions it is important to retrieve a value from a Store Procedure and return it to a C# function. This example is an excerpt from a project to create an image library. File names are pushed into SQL Server using a Stored Procedure. Part of it is to record the path. Paths are stored in one table, file names in another. The record ID of the path is required to create the image record.

SQL Server Stored Procedure

CREATE PROCEDURE [dbo].[sp_newPath]
@path nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- NewPathID is used to retain the Record ID of the existing or new path ID
DECLARE @NewPathID int

-- If the path already has a record, get the ID
SELECT TOP 1 @NewPathID = dbo.Paths.path_ID from dbo.Paths WHERE dbo.Paths.path_path = @path;

-- If there is no record, @NewPathID will be 0 or NULL
if @NewPathID = 0 OR @NewPathID IS NULL
BEGIN

-- Insert the new path record into the Table
INSERT dbo.Paths(path_path)
OUTPUT INSERTED.path_ID INTO @TblforID
VALUES(@path)

-- Get the new Identity value
SELECT @NewPathID = SCOPE_IDENTITY()
END
RETURN (@NewPathID)
END

C# function to push data into SQL Server and retrieve the record ID.

private static int InsertPathSQL(string dirPath)
{

string sqlConnStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Image_Library;Integrated Security=true";
SqlConnection sqlConn = new SqlConnection(sqlConnStr);

SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlComm.CommandText = "dbo.sp_newPath";
sqlComm.Parameters.Add("@path", System.Data.SqlDbType.NVarChar).Direction = System.Data.ParameterDirection.Input;
sqlComm.Parameters["@path"].SqlValue = dirPath;
sqlComm.Parameters.Add("@returnValue", System.Data.SqlDbType.Int, 4).Direction = System.Data.ParameterDirection.ReturnValue;
sqlComm.Connection.Open();
sqlComm.ExecuteNonQuery();
int j = (int)sqlComm.Parameters["@returnValue"].Value;
sqlComm.Connection.Close();
return j;
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s