How to run the .net code within the Sql server

You can do with managed code Sql Server 2005 (SMO)  run the .net code within the Sql Server.

Look here for more understanding and idea.

Create a simple function in the class as following


using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace ManagedCodeAndSQLServer
{
    public class BaseFunctionClass
    {
        #region "Default Constructor"
        public BaseFunctionClass()
        {
                 
        }
        #endregion

        #region "Welcome Function"
        /// <summary>
        /// This function will be called from the SQL Stored Procedure.
        /// </summary>
        /// Name
        /// <returns>Welcome Message </returns>
        [SqlProcedure]
        public static void GetMessage(SqlString strName, out SqlString 
        strMessge)
        {
          strMessge = "Welcome," + strName + ", " + "your code is getting
          executed under CLR !";
        }

        #endregion
    }
}

Build the class and you will get a dll file.

Now you need to enable the SQL Server to work with External Objects and thus you will need to fire the below command..


ALTER DATABASE TestingCLR SET TRUSTWORTHY ON
GO

Now Register ManagedCodeAndSQLServer.dll under that using the following code:


CREATE ASSEMBLY ManagedCodeAndSQLServer
AUTHORIZATION dbo
FROM 'E:\Important\SQL\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll'
WITH PERMISSION_SET = UNSAFE
GO

Create a procedure which can be called from the Trigger...


CREATE PROCEDURE sp_managed_example
@name nvarchar(200), @msg nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage
GO
Done. Now you just need to invoke the procedure from your trigger and it most certainly should work :)

转载于:https://www.cnblogs.com/javak/archive/2010/01/20/1652378.html