NET中SQL Server存储过程

存储过程教程 (Stored Procedure Tutorial)

This article covers the basic of writing a stored procedure and how to call that from .net.  A stored procedure is written using Transact-SQL (T-SQL). T-SQL is a subset of ANSI SQL-92 that has extensions to the Standard. T-SQL includes variables, conditional logic, loops and flow control.  A stored procedure allows you to put code or business logic on the database server.

本文介绍了编写存储过程的基本知识以及如何从.net调用存储过程。 使用Transact-SQL（T-SQL）编写存储过程。 T-SQL是ANSI SQL-92的子集，具有对标准的扩展。 T-SQL包括变量，条件逻辑，循环和流控制。 存储过程使您可以将代码或业务逻辑放在数据库服务器上。

A stored procedure allows for more modular programming. You can create reusable, discrete pieces of functionality using stored procedures. Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL. If you have a stored procedure that manipulates large amounts of data, that data will remain on the server and not be transported across the network. You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting those permissions on objects manipulated by those stored procedures.

存储过程允许进行更多的模块化编程。 您可以使用存储过程来创建可重用的，离散的功能。 存储过程在创建时具有解析和执行计划，因此它们的执行速度比动态SQL快。 如果您有一个存储过程可以处理大量数据，则这些数据将保留在服务器上，并且不会在网络上传输。 您也可以使用存储过程来提高安全性。 您可以授予用户对存储过程的执行权限，而无需向那些存储过程所操作的对象授予这些权限。

TABLE CREATION:

表创建：

1.      Open SQL Server

1.打开SQL Server

PROCEDURE:

程序：

1.       Create the stored procedure shown below:

1.创建如下所示的存储过程：

create procedure getempproc @city varchar(20)
as
begin
select * from emp where city=@city
end

3.      Write your own stored procedure (or copy and paste the code provided above)

3.编写您自己的存储过程（或复制并粘贴上面提供的代码）

PROGRAM:

程序：

1.      Open Visual Studio

1.打开Visual Studio

2.      Create a new console application (note: C# project / code used as an example)

2.创建一个新的控制台应用程序（注意：以C＃项目/代码为例）

3.      Type the following code into Program.cs, compile and run

3.在Program.cs中键入以下代码，进行编译并运行

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = "server=MCASYS13\\SQLEXPRESS;database=employee;user id=sa;pwd=admin123"; 
            cn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getempproc";
            SqlParameter p = new SqlParameter("@city", SqlDbType.VarChar, 50);
            p.Direction = ParameterDirection.Input;
            p.Value = "Chennai";
            cmd.Parameters.Add(p);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Console.WriteLine(dr[0].ToString());
                Console.WriteLine(dr[1].ToString());

            }
            Console.Read();
        }
    }
}

How it works:

这个怎么运作：

The SqlConnnection() will create a new connection to SQL Server.

SqlConnnection（）将创建与SQL Server的新连接。

When you use the “ConnectionString” you have to specify the servername, database name username and password of your SQL Server.

使用“ ConnectionString”时，必须指定SQL Server的服务器名称，数据库名称用户名和密码。

Then we have to open the Connection.

然后，我们必须打开连接。

By using SqlCommand we can communicate with the SQL Server.

通过使用SqlCommand，我们可以与SQL Server通信。

Then we have to set the connection, command type and command text. The connection is SqlConnection object, the command type is stored procedure and the command text is the procedure name.

然后我们必须设置连接，命令类型和命令文本。 连接是SqlConnection对象，命令类型是存储过程，命令文本是过程名称。

Then we have to set the parameters, for that we create SqlParameter and set the parameter value.

然后，我们必须设置参数，为此，我们创建SqlParameter并设置参数值。

By using the reader we can execute the procedure. The ExecuteReader() method is used for execute the stored procedure.

通过使用阅读器，我们可以执行该过程。 ExecuteReader（）方法用于执行存储过程。

The results stored in the SqlDataReader object. We can use those results depends on our requirements

结果存储在SqlDataReader对象中。 我们可以使用这些结果取决于我们的要求

翻译自: https://www.experts-exchange.com/articles/3833/Sql-Server-Stored-Procedure-in-net.html