- For this recipe, I am using SQL Server 2012. The concept is the same if you are using an older version of SQL Server. After you have created the CookbookDB database, you will see that there is a table called UserDisplayData under the Tables folder:
- The UserDisplayData table is simply used to illustrate the concept of querying using a parameterized stored procedure. It would not have any real benefit in a production database, because it only returns a screen name:
- We need to create a stored procedure to select data from this table for a specific ID (user ID). Click on the Programmability node to expand it:
- Next, right-click on the Stored Procedures node and selectNew Stored Procedure... from the context menu:
- SQL Server will create the following stored procedure template for you. This template consists of a section where you can comment on the particular stored procedure, as well as a section to add parameters you might need, and obviously a section that you need to add the actual SQL statement to:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
- Give the stored procedure a suitable name that will describe the action or intent of the stored procedure:
CREATE PROCEDURE cb_ReadCurrentUserDisplayData
There are many people that do prefix their stored procedures, and I'm one of those. I like to keep my stored procedures grouped. I, therefore, name my stored procedures in the format [prefix]_[tablename_or_module]_[stored_procedure_action]. Having said that, I generally avoid using sp_ as a prefix to my stored procedures. There are a lot of opinions on the Internet as to why this is a bad idea. It is generally believed that using sp_ as a stored procedure prefix impacts on performance because it is used as the stored procedure prefix in the master database. For the purposes of this recipe, I have just kept to a simple name for the stored procedure.
- Define a parameter for this stored procedure. By doing this, you are telling the database that when this stored procedure is called, it will pass through a value of type integer that is stored in a parameter caller @userID:
@userID INT
- You now define the SQL statement to be used by this stored procedure. We are just going to do a straightforward SELECT statement:
SELECT
Firstname, Lastname, Displayname
FROM
dbo.UserDisplayData
WHERE
ID = @userID
You will notice that my SELECT statement contains the specific column names instead of a SELECT * FROM. Doing a SELECT * is considered bad practice. You would usually not want to return all the column values from a table. If you want all the column values, then it is better to explicitly list the columns by name instead of just getting all. Using SELECT * returns unnecessary columns and increases the overhead on the server. This does make a difference in the bigger scheme of things, especially when the database starts getting a lot of traffic. The thought of having to type out the column names for a large table is definitely not something I would look forward to. You can, however, use the following tricks to make it easy for you to add the column names to your SQL SELECT statement. You can right-click on the database table and select Script Table As to create one of several SQL statements. Secondly, you can expand the Table node and expand the table you wish to write the statement for. You will then see a node called Columns. Drag the Columns node onto the query editor. That will insert all the column names into the query editor for you.
- When you have completed adding the code to your stored procedure, it will look like this:
- To create the stored procedure, you need to click on the Execute button. Be certain that you have the correct database selected when clicking on the Execute button:
- The stored procedure will then be created under the Stored Procedures node in SQL Server:
- We have now come halfway through this task. It is time to construct the code that we will use in our application to query the database. We will be adding this code directly to the Program.cs file of your console application. While this code isn't considered best practice (hardcoding the server credentials), it serves merely to illustrate the concept of calling a parameterized stored procedure from C#.
- To start, add the following using statement to the top of your console application:
using System.Data.SqlClient;
- We then add the variables to contain the credentials we need to log on to the server:
int intUserID = 1;
int cmdTimeout = 15;
string server = "DIRK";
string db = "CookbookDB";
string uid = "dirk";
string password = "uR^GP2ABG19@!R";
- We now use SecureString to store the password and add it to a SqlCredential object:
SecureString secpw = new SecureString();
if (password.Length > 0)
{
foreach (var c in password.ToCharArray()) secpw.AppendChar(c);
}
secpw.MakeReadOnly();
string dbConn = $"Data Source={server};Initial Catalog={db};";
SqlCredential cred = new SqlCredential(uid, secpw);
For more on SecureString, see the Using SecureString in code recipe of this chapter.
- We now create a SqlConnection object inside a using statement. This ensures that the SQL connection is closed when the using statement moves out of scope:
using (SqlConnection conn = new SqlConnection(dbConn, cred))
{
try
{
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.ReadLine();
- Inside the try, add the following code to open the connection string and create a SqlCommand object that takes the open connection and name of the stored procedure as parameters. You can use the shortcut method of creating the actual SQL parameter to pass to the stored procedure:
cmd.Parameters.Add("userID", SqlDbType.Int).Value = intUserID;
Because I'm just passing a parameter of type integer to the stored procedure, I'm not defining a length for this parameter:
If, however, you ever need to define a parameter of type VarChar(MAX), you would need to define the size of the parameter type by adding -1. Let's say, for example, you need to store a student's essay in the database; the code would then look as follows for the VarChar(MAX):
cmd.Parameters.Add("essay", SqlDbType.VarChar, -1).Value =
essayValue;
- After we have added our parameter with its value to the SqlCommand object, we specify a timeout value, execute the SqlDataReader, and load it into a DataTable. The value is then output to the console application:
conn.Open();
SqlCommand cmd = new SqlCommand("cb_ReadCurrentUserDisplayData",
conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("userID", SqlDbType.Int).Value = intUserID;
cmd.CommandTimeout = cmdTimeout;
var returnData = cmd.ExecuteReader();
var dtData = new DataTable();
dtData.Load(returnData);
if (dtData.Rows.Count != 0)
Console.WriteLine(dtData.Rows[0]["Displayname"]);
- After you have added all the code to your console application, the correct completed code will look as follows:
int intUserID = 1;
int cmdTimeout = 15;
string server = "DIRK";
string db = "CookbookDB";
string uid = "dirk";
string password = "uR^GP2ABG19@!R";
SecureString secpw = new SecureString();
if (password.Length > 0)
{
foreach (var c in password.ToCharArray())
secpw.AppendChar(c);
}
secpw.MakeReadOnly();
string dbConn = $"Data Source={server};Initial Catalog={db};";
SqlCredential cred = new SqlCredential(uid, secpw);
using (SqlConnection conn = new SqlConnection(dbConn, cred))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(
"cb_ReadCurrentUserDisplayData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("userID", SqlDbType.Int).Value = intUserID;
cmd.CommandTimeout = cmdTimeout;
var returnData = cmd.ExecuteReader();
var dtData = new DataTable();
dtData.Load(returnData);
if (dtData.Rows.Count != 0)
Console.WriteLine(dtData.Rows[0]["Displayname"]);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.ReadLine();
- Run your console application and you will see the display name output to the screen:
