Skip to content

Instantly share code, notes, and snippets.

@nikanos
Last active July 19, 2020 08:27
Show Gist options
  • Save nikanos/97a42fff38a77a88473ce36b893734d9 to your computer and use it in GitHub Desktop.
Save nikanos/97a42fff38a77a88473ce36b893734d9 to your computer and use it in GitHub Desktop.
C# - ADO.NET - Call SQL Server Stored Procedure
namespace MyDbTest
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine(DBHelper.Greet("Frodo", "Baggins"));
}
}
}
class DBHelper
{
public static string Greet(string name, string surname)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString))
{
using (var command = new SqlCommand("Greet", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)
{
Value = name
});
command.Parameters.Add(new SqlParameter("@Surname", SqlDbType.NVarChar)
{
Value = surname
});
command.Parameters.Add(new SqlParameter("@Message", SqlDbType.NVarChar)
{
Direction = ParameterDirection.Output,
Size = Int32.MaxValue
});
command.ExecuteNonQuery();
return command.Parameters["@Message"].Value.ToString();
}
}
}
}
CREATE PROCEDURE [dbo].[Greet]
@Name NVARCHAR(MAX),
@Surname NVARCHAR(MAX),
@Message NVARCHAR(MAX) OUT
AS
BEGIN
SET @Message='Hello ' + @Name + ' ' + @Surname +'.How are you?';
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment