A new Database Reference Window will open type the SQL Server name in the Server Name textbox and Select "Northwind" database from the Select or enter a database Name drop down , click on Test Connection button to make sure that the connection is successful , click Ok, Click OK.
Right click on the Solution Icon "CLR Demo" on the right pane and click Add Trigger , Add New Item Window will appear with the trigger class selected , type in the Name "CustomersTrigger.cs"
and click Add the Trigger class will be added to the project as shown in the figure below.
let's create our web service that we will pass to it the captured inserted values to insert them into another database.
click on File --> Add --> New Web Site --> choose ASP.NET Web Service Template make sure that the selected Language is C# and change the name of the Project to "PassValues" and Click OK
The Web Service Project will be added to our solution as intended now , let's add our connection string value in the "Web.config" file in the Web service Project so right click on the Web Service Project and click on add New Item and choose Web Configuration File from the Add New Item Window and Click Add
double click on the "Web.Config" file and replace the following code
<connectionStrings/> Tag
<connectionStrings>
<add name="SQLcon" connectionString ="uid=your_username;pwd=database Password;data source=.;Initial Catalog=database_name"/>
</connectionStrings>
this connection string that I've just wrote down is the one that the web service will connect on to throw the captured inserted data by our trigger.
to make the web Service work successfully you will have to run the following script in your SQL Server
USE [Northwind]
GO
/****** Object: Table [dbo].[Users] Script Date: 09/06/2007 12:43:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[UserName] [varchar](100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Northwind]
GO
/****** Object: Table [dbo].[test1] Script Date: 09/09/2007 12:08:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[CustomerName] [nchar](100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
Press F5 to execute the SQL Code
to create a Table named "Users" and another one named "Test" in the "Northwind" database.
let's write some code in our Web Service , double click on the "service.cs" file , copy and paste the following code in it :
[WebMethod]
public void InsertIntoTemp(string CustomerName)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["sqlcon"].ToString();
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = "Insert users values('" + CustomerName + "')";
com.ExecuteNonQuery();
}
Compile the code to make sure that it has no errors...
now you've created a Web service that has one web Method that takes one Parameter which is "CustomerName" and inserts it in the "users" Table in the "Northwind" database.
to attach our web service to the CLR project you will have to right click on the "CLRDemo" Project Icon and click on add Web Reference button.
click on Web services in this solution the following window will appear to you
click on the Service Link the following window will appear to you...
Type in the Web reference Name "PassValues" and click Add Reference button
let's get back to the our "CustomerTrigger.cs" class , double click on the "CustomerTrigger.cs" class and copy and paste the following code :
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer;
using CLRDemo.PassValues;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
//Target Means = the name of the Table we need to monitor which is in our case Table "test" , Event Means = Monitors which Action
[Microsoft.SqlServer.Server.SqlTrigger(Name = "CustomerTrigger", Target = "test", Event = "FOR INSERT")]
public static void CustomerTrigger()
{
CLRDemo.PassValues.Service service = new CLRDemo.PassValues.Service();
// Replace with your own code
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlPipe sqlPipe = SqlContext.Pipe;
if (triggerContext.TriggerAction ==
TriggerAction.Insert)
{
SqlDataReader sdr;
// here I'll set the context of the sqlconnection I use as the same context of my Trigger
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
//open SQL Connection
conn.Open();
// Setting my SQL command
SqlCommand cmd = new SqlCommand("SELECT * FROM INSERTED", conn);
//Executing my command in the Data reader
sdr = cmd.ExecuteReader();
//Checking if the data reader has Rows or not
if (sdr.HasRows)
{
// read the data reader
while (sdr.Read())
{
// passing the captured data to the created web Service
service.InsertIntoTemp(sdr[0].ToString().Trim());
}
}
}
}
SqlContext.Pipe.Send("Trigger FIRED");
}
}
you will find a folder named Test Scripts Folder under the root of the"CLRDemo" project and under it "Test.sql" file; this file is used for testing , you can write in it the SQL test script you want to test your CLR code so just add in it the following code:
insert test values ('Karim')
where test is our table name in the "Northwind" database
and comment anything else...by putting "//" at the beginning of the line.
try to compile the project right now, the following error will appear in the output window
That's because SQL Server need another assembly to access the web services, a "Serialization Assembly". How can I do that? Simple, go to the project's properties, and set the feature "generates serialization assembly" to "on".
then go to the Database tab in the project properties to set the permission level to External because you are calling web service so you have to set the database permission level to External.
After you try to compile the code after making the Generate Serialization assembly to ON the Code will generate the following error ...
Calling web service methods requires serializing all the types being used to XML. Outside SQL Server, this serialization code is generated at runtime, compiled and loaded in the application. However, for security and reliability reasons, SQL Server does not allow you to dynamically load assemblies. Hence, to make this scenario work, you need to pre-generate the serialization assembly and register it in the database. Visual Studio 2005 beta2 allows you to generate the serialization assembly from your project. You need to turn the Generate serialization assembly option on in project properties a Build. You can also generate the serialization assembly using a tool called ‘sgen’ that is shipped with the .NET Framework SDK as follows:
>sgen.exe myAsm.dll
Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.
Once you have these two assemblies available. You need to register them in SQL Server as follows:
CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’
with permission_set = EXTERNAL ACCESS
CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’
with permission_set = SAFE
Visual Studio 2005 allows you to register the first assembly in SQL Server using the deploy feature in SQL Server projects. However, it does not support deploying the serialization assembly and you would need to manually register it. If you are using Visual Studio on the same machine as your SQL Server, you can use the pre / post deploy script feature in Visual Studio to automate this in your project. This can be done as follows:
1. Add the following to a file called predeployscript.sql:
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'myAsmXML')
DROP ASSEMBLY myAsmXML with NO DEPENDENTS;
2. Add the following to a file called postdeployscript.sql:
CREATE ASSEMBLY myAsmXML from '<path>\myAsm.XmlSerializers.dll'
3. Add these two files to your project by right clicking your project in Solution Explorer and Add an Existing Item.
Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run these scripts before and after the deployment of your project. However since the scripts are run on the SQL Server, the path must be relative to the machine on which you have SQL Server. If SQL Server and VS are on separate machines then you must make sure the path is a network path and accessible from SQL Server machine.
after this the try to compile the code and it will work just fine.
Hint:
you may face some problems regarding the database compatability version , so you will have to run this query in the SQL Server 2005 to ovveride this issue :
sp_dbcmptlevel databaseName, 90 -- the level of comatablility
if you faced any other problems during the implementation feel free to contact me.