Yes, there is a better way: instead of using a SqlDataAdapter, use a SqlCommand and execute the statements yourself using its ExecuteReader method. When calling ExecuteReader, pass in the CommandBehavior.SequentialAccess modifier (and-ed with any other modifiers
that may be usefull in your situation). Then use the returned SqlDataReader to fetch the data.
SequentialAccess means you have to read each column in sequence (i.e. for a table with 2 columns: row 0, column 0 then row 0, column 1 then row 1, column 0, row 1, column 1, etc.). You can skip rows and you can skip columns, but you can never go back: If
you try to access column 0 after accessing column 1, you'll get an error. If you try to read row 0 after reading row 1, you'll get an error. It is realy that strict, so you'll have to realy think while coding... But the good thing is that you don't specify
column names in your c# (which speeds up processing), and it also means you can process binary large object columns, just like 'normal' columns. But the realy best thing is that SQL server doesn't need to allocate memory for the entire resultset while you're
accessing it. i.e. if a statement is executed on your audited table that updates all (or just a lot) of rows, SQL server doesn't need to allocate memory for all these rows of data twice: inserted & deleted, (remember?). Instead your CLR will at any time
only need sufficient memory for a single column being processed (or some buffer size when streaming large objects). So with some more programming effort you can make sure the server doesn't slow down or even crash when (accidently) a lot of data is touched
in your audited table.
I'm not going to give it all away (we need to make living too), but here's a rudimentary example how to better read the data in the trigger code. This code doesn't do anyhting other than reading the changes. It's up to you to do something with the changes
found.
[Microsoft.SqlServer.Server.SqlTrigger (Name="AuditTrigger", Target="dbo.Table1", Event="FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlPipe pipe = SqlContext.Pipe;
SqlTriggerContext context = SqlContext.TriggerContext;
using (SqlConnection conn = new SqlConnection(@"context connection=true"))
{
conn.Open();
// Determine if at least one row exists in
// inserted and/or deleted.
// I deliberatly don't use TriggerAction:
// I don't want to execute any more queries
// if zero rows were affected. i.e. I need
// to do an exists check for each action
// anyway, so it is cheaper to just
// skip that check all together.
// Also, an update may change a primary key value,
// this is treated as a delete of the old key
// plus an insert of the new key.
bool inserted = false;
bool deleted = false;
SqlCommand datacmd = conn.CreateCommand();
datacmd.CommandType = CommandType.Text;
datacmd.CommandText = "select case when exists( select top 1 * from inserted) then 1 else 0 end as insertedcount, case when exists (select top 1 * from deleted) then 1 else 0 end as deletedcount";
using (SqlDataReader datareader = datacmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
{
if (datareader.Read())
{
inserted = 0 == datareader.GetSqlInt32(0) ? false : true;
deleted = 0 == datareader.GetSqlInt32(1) ? false : true;
}
else
{
throw new Exception("Unable to read inserted and deleted counts.");
}
}
int cols = context.ColumnCount;
// First find any newly created rows.
if (inserted)
{
datacmd.CommandText = "select i.ID, i.Name from inserted i where not exists (select * from deleted d where d.ID = i.ID)";
using (SqlDataReader datareader = datacmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
{
int rows = 0;
while (datareader.Read())
{
rows++;
for (int i = 0; i < cols; i++)
{
object val = datareader.GetSqlValue(i);
// Do whatever you need to do with the inserted value.
}
}
}
}
// Now see if any rows were updated.
if (inserted && deleted)
{
datacmd.CommandText = "select i.ID, d.ID, i.Name, d.Name from inserted i inner join deleted d on (d.ID = i.ID)";
using (SqlDataReader datareader = datacmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
{
int rows = 0;
while (datareader.Read())
{
rows++;
for (int i = 0; i < cols; i += 2)
{
object val = datareader.GetSqlValue(i);
// Do whatever you need to do with the new value.
val = datareader.GetSqlValue(i + 1);
// Do whatever you need to do with the old value.
}
}
}
}
// And finaly find any deleted rows.
if (deleted)
{
datacmd.CommandText = "select d.ID, d.Name from deleted d where not exists (select * from inserted i where i.ID = d.ID)";
using (SqlDataReader datareader = datacmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
{
int rows = 0;
while (datareader.Read())
{
rows++;
for (int i = 0; i < cols; i++)
{
object val = datareader.GetSqlValue(i);
// Do whatever you need to do with the deleted value.
}
}
}
}
SQL expert for JF Hillebrand IT BV - The Netherlands.
Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)
