Sqlserver函数之SCOPE_IDENTITY()demo

　　之前有看到有人给我留言说希望有个SCOPE_IDENTITY()函数的demo,今天得闲，就整了个出来，希望对大家有帮助.既然做了就从头开始吧，先创建一个表，如果下

create table Tab1
(
    id int identity primary key,
    Texts nvarchar(50)
)

表结构很简单，就两个字段，一个ID，一个texts,ID是个整型自增长的主键，texts是个长度为50个字符的字符类型。表建好后，写存储过程。先贴代码再解释。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Fuyuanzhao
-- Create date: 2011-3-25
-- Description:    添加测试数据
-- =============================================
CREATE PROCEDURE AddTexts 
    -- Add the parameters for the stored procedure here
@Texts nvarchar(50),@id int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT Off;--这里设置为OFF，这样会返回影响的行数，如果设置为ON这不会返回影响的行数

    -- Insert statements for procedure here
    insert into tab1(texts)values(@Texts)
    set @id=SCOPE_IDENTITY()
END
GO

这是个往表tab1里插入数据的存储过程，需要两个参数。这存储过程很简单，就不再解释了，如有需要解释的请留言。存储过程写完后就是程序中怎么来调用存储过程，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace TestWeb
{
    public partial class AddTextsProcedure : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection SqlConnection = new SqlConnection("SqlConnectionString");
            try {
                using (SqlCommand SqlCommand = new SqlCommand("AddTexts", SqlConnection))
                {
                    SqlCommand.CommandType = CommandType.StoredProcedure;
                    SqlCommand.Parameters.Add("@Texts",SqlDbType.NVarChar,50).Value="张三";
                    SqlCommand.Parameters.Add("@id", SqlDbType.Int, 8).Value = 0;
                    SqlCommand.Parameters["@id"].Direction = ParameterDirection.Output;
                    if (SqlCommand.ExecuteNonQuery() == 1)
                    {
                        int ID = (int)SqlCommand.Parameters["@id"].Value;
                    }                  

                }
            }
            catch (Exception ex)
            { 
#if DEBUG
                throw ex;
#else
#endif
            }
            finally
            {
                SqlConnection.Close(); 
            }
        }
    }
}

这只是个简单的demo,我们往表tab1里插入了“张三"，我们的到的是张三的ID