SQLServer中存储过程StoredProcedure创建及C#调用（转）

此文作为入门了解用，转自http://www.2cto.com/database/201502/378260.html

 

存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL语句；可供应用程序直接调用。使用存储过程有以下几个优点： 
1、执行速度比普通的SQL语句快 
2、便于集中控制 
3、可以降低网络的通信量 
4、保证数据库的安全性和完整性 
5、灵活性

创建存储过程 
可以采用Transact-SQL语句来创建存储过程Stored Procedured。在Microsoft SQL Server Management Studio中Databases->Database Name->Programmability->Stored Procedures右键选择Stored Procedure就生成了一个创建存储过程的模板，修改其中的内容再执行就创建了Stored Procedured。

下面我首先以创建对表中插入数据的存储过程来为例。比如我的原始表是如下语句所创建的：

?

1 2	<code class= " hljs sql" >IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N 'Persons' ) AND OBJECTPROPERTY(id,N 'isUserTable' ) = 1) CREATE TABLE Persons (Id_P int ,LastName varchar (255),FirstName varchar (255),Address varchar (255),City varchar (255))</code>

Stored Procedure创建插入功能的SQL如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

<code class= " hljs sql" >USE [databasename] GO /****** Object:  StoredProcedure [dbo].[insert_persions]    Script Date : 2/25/2015 11:14:11 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID ( 'insert_persons' , 'p' ) IS NOT NULL      DROP PROCEDURE insert_persons; GO -- ============================================= -- Author:      <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE insert_persons      -- Add the parameters for the stored procedure here      @id int ,      @lastname varchar (255),      @firstname varchar (255),      @adress varchar (255),      @city varchar (255) AS BEGIN      -- SET NOCOUNT ON added to prevent extra result sets from      -- interfering with SELECT statements.      SET NOCOUNT ON ;        -- Insert statements for procedure here      INSERT Persons (Id_P, LastName, FirstName, Address, City)      VALUES (@id, @lastname, @firstname, @adress, @city) END GO</code>

以上代码完成了首先检查Stored Procedure是否存在，如果存在先把该Stored Procedure删掉，然后再创建该Stored Procedure。而其功能则是通过传入的参数，插入一条记录。直接点击Execute或者按F5则以上代码被执行，相应的数据库下面将有一个Stored Procedure名字为insert_persons。以SQL Server 2014为例，只要刷新相应的数据库，再到Programmability->Stored Procedures下面就发现多了个dbo.insert_persons。

执行存储过程 
在Stored Procedures下面找到相应的存储过程，点击右键选择Execute Stored Procedure…就可以产生一个新的对话框，填写输入的参数再点击OK就执行完毕。回去查看相应的表会发现多了一行数据。 

实时数据备份，备份容灾-浪擎

【点击进入】

实时在线复制数据，实时数据容灾， 数据恢复无需停机，400-017-3338

查 看

 

 

 

 

当然我们也可以通过SQL语句来执行存储过程。

?

1 2	<code class= " hljs ruby" > EXECUTE insert_persons 5, "Wang" , "San" , "Zhongguancun" , "Beijing" EXECUTE insert_persons @id = 6,@firstname = "Wang" ,@lastname = "San" ,@adress = "Zhongguancun" ,@city = "Beijing" </code>

C#执行存储过程 
下面的代码比较简单的展示了如何利用C#来执行上面创建的存储过程：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<code class= " hljs avrasm" >            String conStr = @ "Data Source=host\SQLEXPRESS;Initial Catalog=dbtan;Integrated Security=SSPI" ;              SqlConnection con = new SqlConnection(conStr);              try              {                  con. Open ();                  Console.WriteLine( "Connect Sucess!" );                    SqlCommand com = new SqlCommand();                  com.CommandType = System.Data.CommandType.StoredProcedure;                  com. Connection = con;                  com.CommandText = "insert_persons" ;                    SqlParameter pId = new SqlParameter( "id" , 10);                  SqlParameter pLastName = new SqlParameter( "lastname" , "last" );                  SqlParameter pFirstName = new SqlParameter( "firstname" , "first" );                  SqlParameter pAdress = new SqlParameter( "adress" , "pop" );                  SqlParameter pCity = new SqlParameter( "city" , "Hangzhou" );                    com.Parameters. Add (pId);                  com.Parameters. Add (pLastName);                  com.Parameters. Add (pFirstName);                  com.Parameters. Add (pAdress);                  com.Parameters. Add (pCity);                    com.ExecuteNonQuery();              }              catch (Exception e)              {                  Console.WriteLine(e.ToString());              }              finally              {                  con. Close ();              }              Console.ReadKey();</code>

转载于:https://www.cnblogs.com/lyd2016/p/6039830.html