未加星标

Working with SQL Server Stored Procedures and .NET

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

By:Artemakis Artemiou | Last Updated: 2018-12-06 || Related Tips:More > Application Development

Problem

In previous tips (seetip1,tip 2), we've learned how to get started with .NET and SQL Server data access. To this end, we've learned how to connect to SQL Server from a C# program and run a simple query, as well as how to query SQL Server tables from .NET and process the results. In this tip, we will learn how to work with SQL Server stored procedures from within a .NET Application.

Solution

There is more than one way to work with SQL Server stored procedures from within a .NET Application. However, when using parameters, in order to avoid exposing your code to "SQL Injection" vulnerabilities, there is a certain way based on which you should write your code. The examples in this tip will be taking the above into consideration.

Sample Database

Just like in my previous tips, our example, will be based on the database " SampleDB " which can be found on our test SQL Server 2017 named instance on the local machine, which is called " SQL2K17 ".

Here's a screenshot of the SQL Server instance, as it can be seen in SSMS:


Working with SQL Server Stored Procedures and .NET

The sample database has two tables named " employees " and " location ", as well as two stored procedures named " uspEmployeeInfo " and " uspUpdateEmployeeLocation ".

Since we will be working with stored procedures in this tip, below you can see the DDL T-SQL scripts for the two above stored procedures:

USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspEmployeeInfo]
@employeeID int
AS
BEGIN
SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr
FROM employees e
INNER JOIN location l on e.locationID=l.id
where <a href="/cdn-cgi/l/email-protection" data-cfemail="e085ce8984dda0858d908c8f998585a9a4">[email protected]</a>
END
GO

The stored procedure " uspEmployeeInfo " takes as an input parameter the employee id and returns basic employee information.

USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspUpdateEmployeeLocation]
@employeeID int,
@locationID int
AS
BEGIN
UPDATE employees SET <a href="/cdn-cgi/l/email-protection" data-cfemail="fe92919d9f8a979190b7bac3be92919d9f8a979190b7ba">[email protected]</a> WHERE <a href="/cdn-cgi/l/email-protection" data-cfemail="523b366f12373f223e3d2b37371b16">[email protected]</a>;
END
GO

The stored procedure " uspUpdateEmployeeLocation " takes as an input parameter the employee id and the location id and updates the relevant employee record with the specified location id.

Sample Data

In the below screenshot, you can take a look at the data currently stored in the "employees" and "location" tables.


Working with SQL Server Stored Procedures and .NET
Calling the Stored Procedures from a .NET Application

The next step in our example, is to write a .NET application and more specifically a C# application, that connects to the database server and properly calls these two stored procedures.

This example is based on the examples originally presented in my two previous tips (seetip1 andtip 2).

One of the main points, is that I will make use of the .NET Class SqlParameter in order to write more secure code, thus minimizing the risk for SQL injections.

Connecting to the SQL Server Instance - Connection String

Again, like in my previous tips, the connection string will be the same:

string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";

Using the above connection string, I will connect to the named instance "SQL2K17" on the local machine, using a trusted connection, that is, with my windows account.

Calling Stored Procedure "uspEmployeeInfo"

As you will see in the below full .NET code, I'm using the SqlParameter class, in order to properly set the input parameter "employeeID" for calling the "uspEmployeeInfo" stored procedure.

After setting the SqlParameter object, I'm adding it as a parameter in the SqlCommand object.

//set stored procedure name
string spName = @"dbo.[uspEmployeeInfo]";
//define the SqlCommand object
SqlCommand cmd = new SqlCommand(spName, conn);
//Set SqlParameter - the employee id parameter value will be set from the command line
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@employeeID";
param1.SqlDbType = SqlDbType.Int;
param1.Value = int.Parse(args[0].ToString());
//add the parameter to the SqlCommand object
cmd.Parameters.Add(param1);

Great! Let's see the full code, where besides properly calling the stored procedure, I'm also getting the results and display it on screen.

Note:Prior to start writing the code, similarly to my previous tips on .NET (seetip1,tip 2), you will need to create a new Visual C# project in Visual Studio, and select the "Console App (.NET Framework)" template. In my example, I named the project " TestApp3-Call SP1 " and saved it in the "C:\temp\demos" folder on my local machine (a new subfolder with the name of the project was created).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace TestApp3_Call_SP1
{
class Program
{
static void Main(string[] args)
{//set the connection stringstring connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";//variables to store the query resultsint empID;string empCode, empFirstName, empLastName, locationCode, locationDescr;try{ //sql connection object using (SqlConnection conn = new SqlConnection(connString)) { //set stored procedure name string spName = @"dbo.[uspEmployeeInfo]"; //define the SqlCommand object SqlCommand cmd = new SqlCommand(spName, conn); //Set SqlParameter - the employee id parameter value will be set from the command line SqlParameter param1 = new SqlParameter(); param1.ParameterName = "@employeeID"; param1.SqlDbType = SqlDbType.Int; param1.Value = int.Parse(args[0].ToString()); //add the parameter to the SqlCommand object cmd.Parameters.Add(param1); //open connection conn.Open(); //set the SqlCommand type to stored procedure and execute cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr = cmd.ExecuteReader(); Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine); Console.WriteLine("Retrieved recor

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Working with SQL Server Stored Procedures and .NET
本站链接:https://www.codesec.net/view/621152.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(65)