Problems with CLR Trigger in C#

Question

Post reply

Problems with CLR Trigger in C#

Akeel.Mughal

Mr or Mrs. 500

Points: 579
More actions
May 29, 2008 at 9:45 am

#191619

Hi,

There is a database table with an image datatype that stores files. I need a trigger, so that when new documents are uploaded here, they are also transferred onto the filesystem.

I'm not really that familiar with C#, so today has been a pretty steep learning curve and I've come across a host of issues and now feel I've got so close, would really appreciate if someone could highlight what I'm doing wrong here.

Here's the C# assembly code:

using System;

using System.IO;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public class CopyDoc

{

[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Demand, Name = "FullTrust")]

[SqlTrigger(Name = "CopyDoc", Target = "[dbo].[HD_OBJECT]", Event = "FOR INSERT")]

public static void CopyDocTrig()

{

//Trigger Context

string HDS_TABLE;

string HDS_TITLE;

string REF;

SqlCommand command;

SqlTriggerContext triggContext = SqlContext.TriggerContext;

SqlPipe pipe = SqlContext.Pipe;

SqlDataReader reader;

FileStream fs; // Writes the BLOB to a file (*.bmp).

BinaryWriter bw; // Streams the BLOB to the FileStream object.

int bufferSize = 100; // Size of the BLOB buffer.

byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.

long retval; // The bytes returned from GetBytes.

long startIndex = 0; // The starting position in the BLOB output.

switch (triggContext.TriggerAction)

{

case TriggerAction.Insert:

using (SqlConnection connection

= new SqlConnection(@"context connection=true"))

{

connection.Open();

command = new SqlCommand(@"SELECT CAST(REF as varchar) REF, HDS_TABLE, HDS_TITLE FROM INSERTED;",

connection);

reader = command.ExecuteReader();

reader.Read();

HDS_TABLE = (string)reader[1];

HDS_TITLE = (string)reader[2];

REF = (string)reader[0];

reader.Close();

if (IsCMDBDocument(HDS_TABLE))

{

command = new SqlCommand(@"SELECT HDS_IMAGE FROM [dbo].[HD_OBJECT] WHERE REF = @ref;",

//"CREATE TABLE WORKED (Yes char(20));",

connection);

command.Parameters.Add(new SqlParameter("@REF", REF));

pipe.Send(command.CommandText);

byte[] storedImage = (byte[])command.ExecuteScalar();

fs = new FileStream(@"c:\" + HDS_TITLE, FileMode.OpenOrCreate, FileAccess.Write);

bw = new BinaryWriter(fs);

startIndex = 0;

retval = reader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

while (retval == bufferSize)

{

bw.Write(outbyte);

bw.Flush();

startIndex += bufferSize;

retval = reader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

}

// Write the remaining buffer.

bw.Write(outbyte, 0, (int)retval);

bw.Flush();

// Close the output file.

bw.Close();

fs.Close();

}

}

break;

}

}

public static bool IsCMDBDocument(string Table)

{

return Regex.IsMatch(Table, @"AR_PRIMARY_ASSET");

}

}

I then alter the database with the trustworty on option, enable CLR's for SQL Server and run the following:

CREATE ASSEMBLY DocumentTrigger from 'C:\Trigger.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

CREATE TABLE TEST

(REF varchar(5),

REFNO varchar(5),

REF_STR varchar(5),

HDS_TABLE varchar(20),

HDS_IMAGE varchar(50),

HDS_TITLE varchar(20)

)

GO

CREATE TRIGGER trig_DocumentTrigger

ON TEST

FOR INSERT

AS EXTERNAL NAME DocumentTrigger.CopyDoc.CopyDocTrig

GO

INSERT

INTO TEST

VALUES ('test', 'test', 'test', 'AR_PRIMARY_ASSET', 'test', 'test.txt')

GO

I get the following error message:

Msg 6522, Level 16, State 1, Procedure trig_DocumentTrigger, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_DocumentTrigger':

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

System.InvalidOperationException:

at System.Data.SqlClient.SqlInternalConnectionSmi.ValidateConnectionForExecute(SqlCommand command)

at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteScalar()

at CopyDoc.CopyDocTrig()

.

TIA,

Akeel.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

bledu

SSChampion

Points: 12335

June 18, 2008 at 9:13 am

#830546

are you still having this problem?

Everything you can imagine is real.

Akeel.Mughal Mr or Mrs. 500 Points: 579 More actions · Answer 2

Hi,

Sorry, forgot about this.

I managed to work it out, here's the C# code if anyone is interested:

using System;

using System.IO;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public class CopyDoc

{

[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Demand, Name = "FullTrust")]

[SqlTrigger(Name = "CopyDoc", Target = "[dbo].[HD_OBJECT]", Event = "FOR INSERT")]

public static void CopyDocTrig()

{

//Trigger Context

string HDS_TABLE;

string HDS_TITLE;

string REF;

SqlCommand command;

SqlTriggerContext triggContext = SqlContext.TriggerContext;

SqlPipe pipe = SqlContext.Pipe;

SqlDataReader reader;

FileStream fs;

int bufferSize = 0;

byte[] outbyte = new byte[bufferSize];

switch (triggContext.TriggerAction)

{

case TriggerAction.Insert:

using (SqlConnection connection

= new SqlConnection(@"context connection=true"))

{

connection.Open();

command = new SqlCommand(@"SELECT CAST(REF as varchar) REF, HDS_TABLE, HDS_TITLE FROM INSERTED;",

connection);

reader = command.ExecuteReader();

reader.Read();

HDS_TABLE = (string)reader[1];

HDS_TITLE = (string)reader[2];

REF = (string)reader[0];

reader.Close();

if (IsCMDBDocument(HDS_TABLE))

{

SqlDataAdapter image = new SqlDataAdapter(@"SELECT * FROM [dbo].[HD_OBJECT] WHERE REF ="+REF+" ORDER BY CHECKIN_DATE DESC",

//"CREATE TABLE WORKED (Yes char(20));",

connection);

DataSet ds = new DataSet("image");

byte[] MyData = new byte[0];

image.Fill(ds, "image");

DataRow myRow;

myRow = ds.Tables["image"].Rows[0];

MyData = (byte[])myRow["HDS_IMAGE"];

int ArraySize = new int();

ArraySize = MyData.GetUpperBound(0);

fs = new FileStream(@"\\hbmuksidinf1001\Knowledge\" + HDS_TITLE, FileMode.OpenOrCreate, FileAccess.Write);

fs.Write(MyData, 0, ArraySize);

fs.Close();

}

break;

}

public static bool IsCMDBDocument(string Table)

{

return Regex.IsMatch(Table, @"AR_PRIMARY_ASSET");

}

I also had to grant extra permissions:

--Grant Permissions for External Access

USE MASTER

GO

GRANT EXTERNAL ACCESS ASSEMBLY TO sa

GO

--Trust the database (Check Database)

USE InfraEnterpriseDev

GO

ALTER DATABASE InfraEnterpriseDev SET TRUSTWORTHY ON

GO

Regards,

Akeel.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Akeelm,

Looking at your trigger, it has a common mistake that I see in both TSQL and CLR triggers. It only handles a single row, so if you have more that one row inserted at a single time, it won't function for every row. It also probably has a fairly large Memory foot print, and given the limited memory in the MemToLeave area in SQL, this can cause forced unloads of your AppDomain if there is memory pressure. I reworked the trigger code below to try and resolve both of these issues:

using System;

using System.IO;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public class CopyDoc

{

[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Demand, Name = "FullTrust")]

[SqlTrigger(Name = "CopyDoc", Target = "[dbo].[HD_OBJECT]", Event = "FOR INSERT")]

public static void CopyDocTrig()

{

FileStream fs;

int bufferSize;

byte[] outbyte;

switch (SqlContext.TriggerContext.TriggerAction)

{

case TriggerAction.Insert:

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

{

conn.Open();

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = @"

SELECT REF, HDS_TABLE, HDS_TITLE, HDS_IMAGE

FROM [dbo].[HD_OBJECT]

JOIN INSERTED ON INSERTED.REF = HD_OBJECT.REF AND INSERTED.CHECKIN_DATE = HD_OBJECT.CHECKIN_DATE";

using (SqlDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

if (Regex.IsMatch(dr["HDS_TABLE"].ToString(), "AR_PRIMARY_ASSET"))

{

outbyte = (byte[])dr["HDS_IMAGE"];

bufferSize = outbyte.GetUpperBound(0);

fs = new FileStream(@"\\hbmuksidinf1001\Knowledge\" + dr["HDS_TITLE"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);

fs.Write(outbyte, 0, bufferSize);

fs.Close();

}

conn.Close();

}

break;

}

One question I have for you is why you chose a Regex.Match for the HDS_TABLE field over a String.Equals(), using ==, or possibly String.Contains() which would use less memory than the Regular Expression match. It still requires External Access because it is touching the filesystem, so there is nothing that you can do about the access level. You might however, consider signing the Assembly over setting Trustworthy On, since signing the assembly is the recommended best practice.

Let me know if this helps any, or if there are any questions on it.