• Creating CLRbased Stored Procedures in C# and VS 2005


    Creating CLR-based Stored Procedures in C# and VS 2005

     

    rickieleemail@yahoo.com on Aug. 30, 2005

    http://rickie.cnblogs.com

     

    The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages.

     

    So when will we use the Managed code such as VB.NET and C# or T-SQL to create stored procedures in SQL Server 2005?  Generally, Managed code is better suited for the complicated business logic and calculation, and string handling and regular expressions. On the contrary, T-SQL is simpler in situations where the code is just used to perform data access with little or no procedural logic.

     

    The following steps illustrate how to created CRL-based stored procedures. The managed code will be used in the example, although T-SQL is more suitable over there.

     

    1. Create a “SQL Server Project” in C# by using VS 2005

    Project Language: C#

    Project Type: Database

    Template: SQL Server Project

    VS 2005 will ask you to create a database reference or use an existing one. In this example, the AdventureWorks database will be referenced.

     

    2. Create a demo stored procedure in the AdventureWorks database

        [Microsoft.SqlServer.Server.SqlProcedure]

        public static void GetEmployee()

        {

            SqlPipe sp = SqlContext.Pipe;

            using(SqlConnection conn = new SqlConnection("context connection=true"))

            {

                conn.Open();

                SqlCommand cmd = new SqlCommand();

                cmd.CommandType = CommandType.Text;

                cmd.Connection = conn;

                cmd.CommandText = "Select * From HumanResources.Employee (nolock)";

                SqlDataReader reader = cmd.ExecuteReader();

                sp.Send(reader);

            }

        }

    ***

    [Microsoft.SqlServer.Server.SqlProcedure]

    This attribute tells Visual Studio that this is a stored procedure for SQL Server.

     

    Next we need to build and deploy the stored procedure by right-clicking above SQL Server project. This will compile the DLL, copy it into SQL Server and create a stored procedure called GetEmployee in the AdventureWorks database.

     

    3. Call the stored procedure

     

    USE [AdventureWorks]

    GO

    DECLARE      @return_value int

    EXEC      @return_value = [dbo].[GetEmployee]

    SELECT      'Return Value' = @return_value

    GO

     

    All employee records will be retrieved in the query results pane. If there is an error message about execution the above code. Please enable execution CLR by using the following script.

     

    -- To enable execution of user code in the .NET Framework.

    Exec sp_configure 'clr enabled', 1

    Reconfigure with override

     

  • 相关阅读:
    mysql配置时,提示:Failed to start service MYSQL80
    修改NUGET包默认存放位置
    非Hive Metastore Server节点执行load命令时出现“cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in switch database statement”
    微信小程序登录授权及手机号获取
    后台获取 HttpServletResponse 中的值
    Request Payload 后台拦截器读取入参方式
    答题活动小程序
    答题小程序V5.0
    考研刷题小程序V2.0
    答题小程序优化
  • 原文地址:https://www.cnblogs.com/rickie/p/226661.html
Copyright © 2020-2023  润新知