  Integrate DotNet in Sql Server 2005
[25 mn de lecture - paru le 10/29/2005 6:06:13 PM - Public : Confirmé]
|
|
Auteur
3. To create Trigger in Sql server 2005 with NET
3.1. Creation of the Trigger class
We will take for example that if our table is modified then we will automatically create an INSERT in this same table with the current date in parameter.
To add a news class Trigger.
Simple example
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
partial public class Triggers
{
[ Microsoft.SqlServer.Server. SqlTrigger (Name = "MonTrigger" , Target = "project" , Vent = "FOR UPDATE" ) ]
public static void Trigger1()
{
SqlTriggerContext monTrig = SqlContext TriggerContext;
SqlPipe sqlPip = SqlContext Pipe;
yew (monTrig.TriggerAction == TriggerAction Update)
{
using ( SqlConnection conn = new SqlConnection ( "context connection=true;" ))
using ( SqlCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "INSERT INTO project (nomProjet) BEEN WORTH (getdate())" ;
conn.Open();
SqlContext Pipe.Send( "Result: " + cmd.ExecuteNonQuery().ToString());
}
wrestling ( Exception ex)
{
SqlContext Pipe.Send( "Error" + ex.Message);
}
finally
{
conn.Close();
}
}
}
}
}
|
The Trigger class is declared into partial (class partial). The class partial will allow to divide the clase into several files what will make it possible to facilitate the development, the tests and maintenance.
partial public class Triggers
{
} |
One places an attribute to specify in Sql Server that it is a procedure.
The attribute takes several parameters:
- Name: the name of the object
- Target: on which table the object acts
- Vent: on which event: FOR INSERT, FOR UPDATE, FOR DELETE, FOR CREATE COUNTS...
[ Microsoft.SqlServer.Server. SqlTrigger (Name = "MonTrigger" , Target = "project" , Vent = "FOR UPDATE" ) ]
public static void Trigger1()
{
} |
When you are carried out a procedure, a trigger, a function CLR within SQL Server 2005, the code operates in the context of a preestablished connection. SqlContext represents this connection. It provided various objects like SqlPipe, SqlResultSet, SqlTransaction, Sql TriggerContext....
Sql TriggerContext will determine the cause of release of the trigger.
SqlTriggerContext monTrig = SqlContext TriggerContext;
SqlPipe sqlPip = SqlContext Pipe; |
Then it is checked if the trigger detected Update
yew (monTrig.TriggerAction == TriggerAction Update)
{
} |
One recovers connection into short.
| using ( SqlConnection conn = new SqlConnection ( "context connection=true;" )) |
Then one carries out the request insert in the Data base
using ( SqlCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "INSERT INTO project (nomProjet) BEEN WORTH (getdate())" ;
conn.Open();
SqlContext Pipe.Send( "Result: " + cmd.ExecuteNonQuery().ToString());
}
wrestling ( Exception ex)
{
SqlContext Pipe.Send( "Error" + ex.Message);
}
finally
{
conn.Close(); |
3.2. To deploy the assemblie in SQL Server 2005
Then you must deploy your DLL,
Click right on the project > Deploy
To rock in Sql Server Management Studio (in the past undertaken to manage)
Make Refresh of your data base
In the tree structure of your data base: DataBase > > VotreTable > > Triggers. Your trigger was well spread.
3.3 Test of Trigger
By carrying out this script you must see that a line it is created in the same table with the date and hour of the day.
The test is finished.
|
15 Visites
Michel DEGREMONT
[25 mn de lecture - paru le 10/29/2005 4:41:28 PM - Public : Expert]
[10 mn de lecture - paru le 10/29/2005 4:32:47 PM - Public : ÐÂÊÖ]