So, I've read some past articles that indciate there's no easy way of knowin
g
within a CLR trigger what table the trigger is acting against?!
I read there are 2 possible solutions:
1) Add a String argument to the CLR Trigger to pass the table name. Then
Create a user defined function for each table that will have this trigger
registered on.
or
2) Use the dm_tran_locks to retrieve the name of the table
Is this still the case?Hi Trex,
Welcome the MSDN newsgroup.
As for the SQL CLR managed trigger, actually it is designed for binding to
a certain target table. that's why at runtime the SQLTriggerContext class
dosn't contain any database/tablename info...
Also, is the article you mentioned the below one?
#SQL Server 2005: CLR Integration
http://blogs.msdn.com/sqlclr/archiv.../21/495438.aspx
I think the method mentioned there such as using an additional string input
parameter to supply table name may be a workaround if you do need to get
the bound table's name in the CLR trigger's code.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Yes, that's the one I had read. I guess I still don't understand why but
that's not as important to me now. So, can the trigger define a parameter?
Create Trigger EnforceInheritanceTriggerOnCar on [Foo].[Car]
For INSERT as External Name
CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
GO
Or do have have to convert my CLRTrigger to a CLRFunction with a parameter
then have the SQL trigger simply call it?
Thanks for your reply!!
"Steven Cheng[MSFT]" wrote:
> Hi Trex,
> Welcome the MSDN newsgroup.
> As for the SQL CLR managed trigger, actually it is designed for binding to
> a certain target table. that's why at runtime the SQLTriggerContext class
> dosn't contain any database/tablename info...
> Also, is the article you mentioned the below one?
> #SQL Server 2005: CLR Integration
> http://blogs.msdn.com/sqlclr/archiv.../21/495438.aspx
> I think the method mentioned there such as using an additional string inpu
t
> parameter to supply table name may be a workaround if you do need to get
> the bound table's name in the CLR trigger's code.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your response.
Normal SQL native trigger also dosn't provide parameters. So I think we
still have to create a CLR function (have a parameter) and let our SQL CLR
trigger call that function and pass a fixed name when attatched to a
certain table.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Showing posts with label clr. Show all posts
Showing posts with label clr. Show all posts
Monday, March 19, 2012
CLR vs T-SQL
Hi,
I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)
It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?
Thanks,
Bahadir
...very common question these days.
Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!
Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.
Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.
Does than mean T-SQL is always the 2nd choice ? NO Not at all !!
Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.
I hope I am able to answer your doubt.
Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)
and SQL Server Magazine (www.sqlmag.com)
cheers,
Ujwal
|||Thanks, this was the kind of rule-of-thumb answer I was expecting.Bahadir
|||
well, I don't know how much are you convinced with the answer or do you still have doubts.
anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well --Where to use SQLCLR ? Data access is not recommended?
|||
hi bahadir,
i think i discussed those thing in my blogs
kindly check
look for the SQL CLR topic
regards,
joey
|||Hi Bhadir,
You get some insight from the whitepaper that we published on CLR integrationyou can find it here.
The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.
Cheers,
-Isaac
|||Hi there,
check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx
Might help you.
Cheers, -DS
CLR vs T-SQL
Hi,
I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)
It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?
Thanks,
Bahadir
...very common question these days.
Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!
Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.
Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.
Does than mean T-SQL is always the 2nd choice ? NO Not at all !!
Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.
I hope I am able to answer your doubt.
Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)
and SQL Server Magazine (www.sqlmag.com)
cheers,
Ujwal
|||Thanks, this was the kind of rule-of-thumb answer I was expecting.Bahadir|||
well, I don't know how much are you convinced with the answer or do you still have doubts.
anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?
|||
hi bahadir,
i think i discussed those thing in my blogs
kindly check
look for the SQL CLR topic
regards,
joey
|||Hi Bhadir,
You get some insight from the whitepaper that we published on CLR integrationyou can find it here.
The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.
Cheers,
-Isaac
|||Hi there,
check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx
Might help you.
Cheers, -DS
CLR vs T-SQL
Hi,
I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)
It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?
Thanks,
Bahadir
...very common question these days.
Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!
Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.
Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.
Does than mean T-SQL is always the 2nd choice ? NO Not at all !!
Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.
I hope I am able to answer your doubt.
Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)
and SQL Server Magazine (www.sqlmag.com)
cheers,
Ujwal
|||Thanks, this was the kind of rule-of-thumb answer I was expecting.Bahadir|||
well, I don't know how much are you convinced with the answer or do you still have doubts.
anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?
|||
hi bahadir,
i think i discussed those thing in my blogs
kindly check
look for the SQL CLR topic
regards,
joey
|||Hi Bhadir,
You get some insight from the whitepaper that we published on CLR integrationyou can find it here.
The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.
Cheers,
-Isaac
|||Hi there,
check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx
Might help you.
Cheers, -DS
CLR User-defined aggregate support Java/J#?
I want to write a Java User-defined aggregate (UDA). Shall I use J#?
I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:
Msg 6265, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.
Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
From the warning, I can tell the J# UDA is not tested.
Can someone confirm whether J# UDA is supported or not?
Thanks!
-
Here is my code:
ALTER DATABASE MEDIO set TRUSTWORTHY ON
CREATE ASSEMBLY MyAgg FROM 'C:\code\console\PriceUDA\obj\Debug\PriceUDA.dll' WITH PERMISSION_SET = unsafe
CREATE AGGREGATE MyAgg (@.input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate
GO
import System.*;
import System.Data.*;
import Microsoft.SqlServer.Server.*;
import System.Data.SqlTypes.*;
import System.IO.*;
import System.Text.*;
/** @.attribute Serializable() */
/** @.attribute SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
*/
public class Concatenate implements IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.get_IsNull())
{
return;
}
this.intermediateResult.Append(value.get_Value()).Append(',');
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
String output = String.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.get_Length() > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.get_Length() - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
No responses... Has anyone else been able to get this to work?|||
Hi Chang!
In SQL Server 2005 we support C#, Visual Basic and managed C++ with SQL CLR.
We haven't tested integration with other languages. However, I can imagine that other languages' vendors have tested their languages with SQL CLR, or are going to do this. In this case, they may have published their recommendations on how to use their languages' features with SQL CLR, what problems you may have and so on.
I'm not aware of such recommendations for J#.
|||Vadim -- We are asking about Microsoft J#, not some 3rd party tool.Microsoft Visual J# is a .net language that uses the CLR. I've seen dozens of quotes that say it 'should work', but I have yet to find anybody who actually got it to work.
I have a bunch of database-neutral java stored procedures, that I recently ported to work with mssql, that I now need to implement as CLR stored procedures. I'd *really* like to port my java to j# using .net, as opposed to porting to c# (Yes, I know about JLCA, I'm working my way throught that now). Unfortunately, Visual Studio 2005 in J# mode has no support for SQL Server, and every document I find on the internet only talks about using VS to generate SQL code. So far, I have not found 1 single example that shows J# running as a CLR stored proc, or what you might have to do to get it to work.|||
Hi!
You are right; I should have phrased this better. Anyway, due to a variety of reasons we haven’t put significant effort into supporting J#. According to my knowledge, you have chances that functions and procedures will work, but you most probably will have problems with user-defined types and aggregates. Have you tried to wrap your J# logic into, say, C# aggregate?
The fact that a language is a .NET language doesn’t automatically mean that it will go well with SQL CLR. The reason is that SQL CLR puts additional restrictions and requirements to the IL code, compared to those necessary just to run IL executable. Not all code generated by .NET compilers satisfies them, not all run-time libraries of these languages satisfy them. I’d guess the way J# compiles classes is not compliant with SQL CLR.
|||I can't recall the exact problems off the top of my head, but I do recall there being problems with J# in SQL CLR. You may be able to get this working if you register the assembly as UNSAFEan unfortunate move.
I'll see if I can dredge up more.
Cheers,
-Isaac
CLR User-defined aggregate support Java/J#?
I want to write a Java User-defined aggregate (UDA). Shall I use J#?
I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:
Msg 6265, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.
Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
From the warning, I can tell the J# UDA is not tested.
Can someone confirm whether J# UDA is supported or not?
Thanks!
-
Here is my code:
ALTER DATABASE MEDIO set TRUSTWORTHY ON
CREATE ASSEMBLY MyAgg FROM 'C:\code\console\PriceUDA\obj\Debug\PriceUDA.dll' WITH PERMISSION_SET = unsafe
CREATE AGGREGATE MyAgg (@.input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate
GO
import System.*;
import System.Data.*;
import Microsoft.SqlServer.Server.*;
import System.Data.SqlTypes.*;
import System.IO.*;
import System.Text.*;
/** @.attribute Serializable() */
/** @.attribute SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
*/
public class Concatenate implements IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.get_IsNull())
{
return;
}
this.intermediateResult.Append(value.get_Value()).Append(',');
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
String output = String.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.get_Length() > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.get_Length() - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
No responses... Has anyone else been able to get this to work?|||
Hi Chang!
In SQL Server 2005 we support C#, Visual Basic and managed C++ with SQL CLR.
We haven't tested integration with other languages. However, I can imagine that other languages' vendors have tested their languages with SQL CLR, or are going to do this. In this case, they may have published their recommendations on how to use their languages' features with SQL CLR, what problems you may have and so on.
I'm not aware of such recommendations for J#.
|||Vadim -- We are asking about Microsoft J#, not some 3rd party tool.Microsoft Visual J# is a .net language that uses the CLR. I've seen dozens of quotes that say it 'should work', but I have yet to find anybody who actually got it to work.
I have a bunch of database-neutral java stored procedures, that I recently ported to work with mssql, that I now need to implement as CLR stored procedures. I'd *really* like to port my java to j# using .net, as opposed to porting to c# (Yes, I know about JLCA, I'm working my way throught that now). Unfortunately, Visual Studio 2005 in J# mode has no support for SQL Server, and every document I find on the internet only talks about using VS to generate SQL code. So far, I have not found 1 single example that shows J# running as a CLR stored proc, or what you might have to do to get it to work.
|||
Hi!
You are right; I should have phrased this better. Anyway, due to a variety of reasons we haven’t put significant effort into supporting J#. According to my knowledge, you have chances that functions and procedures will work, but you most probably will have problems with user-defined types and aggregates. Have you tried to wrap your J# logic into, say, C# aggregate?
The fact that a language is a .NET language doesn’t automatically mean that it will go well with SQL CLR. The reason is that SQL CLR puts additional restrictions and requirements to the IL code, compared to those necessary just to run IL executable. Not all code generated by .NET compilers satisfies them, not all run-time libraries of these languages satisfy them. I’d guess the way J# compiles classes is not compliant with SQL CLR.
|||I can't recall the exact problems off the top of my head, but I do recall there being problems with J# in SQL CLR. You may be able to get this working if you register the assembly as UNSAFEan unfortunate move.
I'll see if I can dredge up more.
Cheers,
-Isaac
CLR user defined aggregate function (SS2005)
Hello,
In a user defined aggregate in Visual Studio 2005 with SQL Server 2005, I
declared a private variable in the struct and initialized in the Init "event
"
of the aggregate.
In the Terminate "event" it turned out that the variable was out of scope
(null).
My first thought was that this happened as a result of reads and writes of
intermediate results of the aggregate function, where (in my case) the
private variable was not explicitly saved.
But this idea is not consistent with the Merge "event" that also uses the
private variable and there it never is out of scope.
Who can explain?
Thx.examnotes <Axford@.discussions.microsoft.com> wrote in
news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:
> In a user defined aggregate in Visual Studio 2005 with SQL Server
> 2005, I declared a private variable in the struct and initialized in
> the Init "event" of the aggregate.
> In the Terminate "event" it turned out that the variable was out of
> scope (null).
> My first thought was that this happened as a result of reads and
> writes of intermediate results of the aggregate function, where (in my
> case) the private variable was not explicitly saved.
> But this idea is not consistent with the Merge "event" that also uses
> the private variable and there it never is out of scope.
How do you establish that the variable is not null in the Merge method?
In other words, are you certain that the Merge is being called. SQL will
not call Merge unless it creates a new instance of your aggregate and
runs that instance in parallell over a group in your accumulation.
Anyway, can you please post the code for your aggregate and we'll have a
look.
Also, it'd probably be better is you posted this in the sqlserver.clr
group which deals explicitly with SQLCLR issues. I will x-post this
reply to there.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Sorry it was the Accumulate event where the private variable was in scope. S
o
probably the Merge event will set it to null also. Can I enforce thecall to
Merge in some way?
I cannot find a group sqlserver.clr.
Thanks for the reply.
"Niels Berglund" wrote:
> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:
>
>
> How do you establish that the variable is not null in the Merge method?
> In other words, are you certain that the Merge is being called. SQL will
> not call Merge unless it creates a new instance of your aggregate and
> runs that instance in parallell over a group in your accumulation.
> Anyway, can you please post the code for your aggregate and we'll have a
> look.
> Also, it'd probably be better is you posted this in the sqlserver.clr
> group which deals explicitly with SQLCLR issues. I will x-post this
> reply to there.
>
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||examnotes <Axford@.discussions.microsoft.com> wrote in
news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:
> Sorry it was the Accumulate event where the private variable was in
> scope. So probably the Merge event will set it to null also. Can I
> enforce thecall to Merge in some way?
OK, so in Accumulate the variable is OK, but in Terminate it is null -
is that correct? If so, please post the code for your UDA. AFAIK, you
can not force a call for Merge.
> I cannot find a group sqlserver.clr.
Do a refresh of your newsgroup list - that should make it available to
you (microsoft.public.sqlserver.clr).
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Here you go with the code (it is from Microsoft samples):
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,MaxByteSize=8000, IsInvariantToNulls=true,
IsInvariantToDuplicates = false, IsInvariantToOrder=true,
IsNullIfEmpty=true)]
public struct Concatenate : IBinarySerialize
{
private string delimeter;
private StringBuilder intermediateResult;
public void Init()
{
delimeter = "-";
intermediateResult = new StringBuilder();
}
public void Accumulate(SqlString value)
{
if (value.IsNull)
return;
intermediateResult.Append(value.Value).Append(delimeter);
}
public void Merge(Concatenate other)
{
intermediateResult.Append(other.intermediateResult).Append(delimeter);
}
public SqlString Terminate()
{
string output = string.Empty;
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0,
this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
#region IBinarySerialize Members
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
intermediateResult = new StringBuilder(r.ToString());
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(intermediateResult.ToString());
}
#endregion
}
"Niels Berglund" wrote:
> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:
>
> OK, so in Accumulate the variable is OK, but in Terminate it is null -
> is that correct? If so, please post the code for your UDA. AFAIK, you
> can not force a call for Merge.
>
> Do a refresh of your newsgroup list - that should make it available to
> you (microsoft.public.sqlserver.clr).
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>
In a user defined aggregate in Visual Studio 2005 with SQL Server 2005, I
declared a private variable in the struct and initialized in the Init "event
"
of the aggregate.
In the Terminate "event" it turned out that the variable was out of scope
(null).
My first thought was that this happened as a result of reads and writes of
intermediate results of the aggregate function, where (in my case) the
private variable was not explicitly saved.
But this idea is not consistent with the Merge "event" that also uses the
private variable and there it never is out of scope.
Who can explain?
Thx.examnotes <Axford@.discussions.microsoft.com> wrote in
news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:
> In a user defined aggregate in Visual Studio 2005 with SQL Server
> 2005, I declared a private variable in the struct and initialized in
> the Init "event" of the aggregate.
> In the Terminate "event" it turned out that the variable was out of
> scope (null).
> My first thought was that this happened as a result of reads and
> writes of intermediate results of the aggregate function, where (in my
> case) the private variable was not explicitly saved.
> But this idea is not consistent with the Merge "event" that also uses
> the private variable and there it never is out of scope.
How do you establish that the variable is not null in the Merge method?
In other words, are you certain that the Merge is being called. SQL will
not call Merge unless it creates a new instance of your aggregate and
runs that instance in parallell over a group in your accumulation.
Anyway, can you please post the code for your aggregate and we'll have a
look.
Also, it'd probably be better is you posted this in the sqlserver.clr
group which deals explicitly with SQLCLR issues. I will x-post this
reply to there.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Sorry it was the Accumulate event where the private variable was in scope. S
o
probably the Merge event will set it to null also. Can I enforce thecall to
Merge in some way?
I cannot find a group sqlserver.clr.
Thanks for the reply.
"Niels Berglund" wrote:
> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:
>
>
> How do you establish that the variable is not null in the Merge method?
> In other words, are you certain that the Merge is being called. SQL will
> not call Merge unless it creates a new instance of your aggregate and
> runs that instance in parallell over a group in your accumulation.
> Anyway, can you please post the code for your aggregate and we'll have a
> look.
> Also, it'd probably be better is you posted this in the sqlserver.clr
> group which deals explicitly with SQLCLR issues. I will x-post this
> reply to there.
>
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||examnotes <Axford@.discussions.microsoft.com> wrote in
news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:
> Sorry it was the Accumulate event where the private variable was in
> scope. So probably the Merge event will set it to null also. Can I
> enforce thecall to Merge in some way?
OK, so in Accumulate the variable is OK, but in Terminate it is null -
is that correct? If so, please post the code for your UDA. AFAIK, you
can not force a call for Merge.
> I cannot find a group sqlserver.clr.
Do a refresh of your newsgroup list - that should make it available to
you (microsoft.public.sqlserver.clr).
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Here you go with the code (it is from Microsoft samples):
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,MaxByteSize=8000, IsInvariantToNulls=true,
IsInvariantToDuplicates = false, IsInvariantToOrder=true,
IsNullIfEmpty=true)]
public struct Concatenate : IBinarySerialize
{
private string delimeter;
private StringBuilder intermediateResult;
public void Init()
{
delimeter = "-";
intermediateResult = new StringBuilder();
}
public void Accumulate(SqlString value)
{
if (value.IsNull)
return;
intermediateResult.Append(value.Value).Append(delimeter);
}
public void Merge(Concatenate other)
{
intermediateResult.Append(other.intermediateResult).Append(delimeter);
}
public SqlString Terminate()
{
string output = string.Empty;
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0,
this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
#region IBinarySerialize Members
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
intermediateResult = new StringBuilder(r.ToString());
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(intermediateResult.ToString());
}
#endregion
}
"Niels Berglund" wrote:
> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:
>
> OK, so in Accumulate the variable is OK, but in Terminate it is null -
> is that correct? If so, please post the code for your UDA. AFAIK, you
> can not force a call for Merge.
>
> Do a refresh of your newsgroup list - that should make it available to
> you (microsoft.public.sqlserver.clr).
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>
CLR User Defined Aggregate Function
I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help!
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
Hi Rob,
Unfortunately, a current limitation of our UDAs is that they can only take one parameter: the single parameter being aggregated. This precludes a few scenarios, including an additional parameter that is constant for the life of the aggregate (your situation) as well as a second aggregated parameter.
The usual workaround we recommend for folks who want to aggregate over two variables is to wrap them using either XML or a UDT and then unwrap them inside of the aggregate. This could work in your situation as welljust hold one of the variables constantbut is a particularly ugly hack.
Cheers,
-Isaac
|||Hi Isaac,Since we're considering ugly hacks, I had an idea for another workaround: could I create a table variable, and access it from within my UDA?
E.g., in the following procedure, I create a temp table with params, which my UDA will expect to find in scope when it runs. I have no idea if this would work at all...
CREATE PROCEDURE foo
AS
DECLARE @.settings table (
param1 int,
param2 char
)
SELECT lastName, dbo.myConcatUDA(firstName)
FROM people
GROUP BY lastName
GO
CLR User Defined Aggregate Function
I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help!
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
Hi Rob,
Unfortunately, a current limitation of our UDAs is that they can only take one parameter: the single parameter being aggregated. This precludes a few scenarios, including an additional parameter that is constant for the life of the aggregate (your situation) as well as a second aggregated parameter.
The usual workaround we recommend for folks who want to aggregate over two variables is to wrap them using either XML or a UDT and then unwrap them inside of the aggregate. This could work in your situation as welljust hold one of the variables constantbut is a particularly ugly hack.
Cheers,
-Isaac
|||Hi Isaac,Since we're considering ugly hacks, I had an idea for another workaround: could I create a table variable, and access it from within my UDA?
E.g., in the following procedure, I create a temp table with params, which my UDA will expect to find in scope when it runs. I have no idea if this would work at all...
CREATE PROCEDURE foo
AS
DECLARE @.settings table (
param1 int,
param2 char
)
SELECT lastName, dbo.myConcatUDA(firstName)
FROM people
GROUP BY lastName
GO
CLR UDTs bigger than 8000 bytes
Hi there,
Say I wanted to create a "compound" type, purely to pass as a parameter
to a stored procedure, but one of the components was NTEXT or
NVARCHAR(MAX). Say it was something like:
- Author NVARCHAR(256)
- IP address VARCHAR(16)
- Comment NVARCHAR(MAX)
How would I go about doing this? My code works fine if the UDT package
is less than 8000 bytes, but fails if it's greater than that (which is
understandable since I had to put MaxByteSize = 8000 into the UDT
definition). If the package is too big, I get an
IndexOutOfRangeException in
System.Data.SqlClient.TdsParser.TdsExecuteRPC().
Is the 8000-byte limit a "hard" limit that I can't work around? As I
mentioned, this is purely to pass as a parameter to a stored procedure,
it'll never be used as a column in a table.
Or am I misunderstanding something and I don't even need to make this a
real UDT since it won't ever be stored as a column? Is there a way for
me to pass a regular .NET object to a CLR stored procedure?
(This example is very simplistic, I know - I could just pass three
individual parameters instead of one compound one in this case. The
actual situation is more complicated though.)
Many thanks,
GeoffGeoff (opinionatedg
@.gmail.com) writes:
> Say I wanted to create a "compound" type, purely to pass as a parameter
> to a stored procedure, but one of the components was NTEXT or
> NVARCHAR(MAX). Say it was something like:
> - Author NVARCHAR(256)
> - IP address VARCHAR(16)
> - Comment NVARCHAR(MAX)
> How would I go about doing this? My code works fine if the UDT package
> is less than 8000 bytes, but fails if it's greater than that (which is
> understandable since I had to put MaxByteSize = 8000 into the UDT
> definition). If the package is too big, I get an
> IndexOutOfRangeException in
> System.Data.SqlClient.TdsParser.TdsExecuteRPC().
> Is the 8000-byte limit a "hard" limit that I can't work around?
That's right.
> Or am I misunderstanding something and I don't even need to make this a
> real UDT since it won't ever be stored as a column? Is there a way for
> me to pass a regular .NET object to a CLR stored procedure?
Check out the current thread "DataRow in a CLR Stored Procedure". There
are some suggestions on binary serialization.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
thanks for that - at least I know I'm not missing something simple at
the UDT/parameter level.
I've checked out the thread "DataRow in a CLR Stored Procedure" as you
suggested - interesting stuff. It would just mean serializing the
values manually before the call, rather than the serialization
happening as part of the call... The objects themselves wouldn't then
need to be UDTs, so there'd be no limit. Cool.
Many thanks,
Geoff
Say I wanted to create a "compound" type, purely to pass as a parameter
to a stored procedure, but one of the components was NTEXT or
NVARCHAR(MAX). Say it was something like:
- Author NVARCHAR(256)
- IP address VARCHAR(16)
- Comment NVARCHAR(MAX)
How would I go about doing this? My code works fine if the UDT package
is less than 8000 bytes, but fails if it's greater than that (which is
understandable since I had to put MaxByteSize = 8000 into the UDT
definition). If the package is too big, I get an
IndexOutOfRangeException in
System.Data.SqlClient.TdsParser.TdsExecuteRPC().
Is the 8000-byte limit a "hard" limit that I can't work around? As I
mentioned, this is purely to pass as a parameter to a stored procedure,
it'll never be used as a column in a table.
Or am I misunderstanding something and I don't even need to make this a
real UDT since it won't ever be stored as a column? Is there a way for
me to pass a regular .NET object to a CLR stored procedure?
(This example is very simplistic, I know - I could just pass three
individual parameters instead of one compound one in this case. The
actual situation is more complicated though.)
Many thanks,
GeoffGeoff (opinionatedg
@.gmail.com) writes:> Say I wanted to create a "compound" type, purely to pass as a parameter
> to a stored procedure, but one of the components was NTEXT or
> NVARCHAR(MAX). Say it was something like:
> - Author NVARCHAR(256)
> - IP address VARCHAR(16)
> - Comment NVARCHAR(MAX)
> How would I go about doing this? My code works fine if the UDT package
> is less than 8000 bytes, but fails if it's greater than that (which is
> understandable since I had to put MaxByteSize = 8000 into the UDT
> definition). If the package is too big, I get an
> IndexOutOfRangeException in
> System.Data.SqlClient.TdsParser.TdsExecuteRPC().
> Is the 8000-byte limit a "hard" limit that I can't work around?
That's right.
> Or am I misunderstanding something and I don't even need to make this a
> real UDT since it won't ever be stored as a column? Is there a way for
> me to pass a regular .NET object to a CLR stored procedure?
Check out the current thread "DataRow in a CLR Stored Procedure". There
are some suggestions on binary serialization.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
thanks for that - at least I know I'm not missing something simple at
the UDT/parameter level.
I've checked out the thread "DataRow in a CLR Stored Procedure" as you
suggested - interesting stuff. It would just mean serializing the
values manually before the call, rather than the serialization
happening as part of the call... The objects themselves wouldn't then
need to be UDTs, so there'd be no limit. Cool.
Many thanks,
Geoff
CLR TVF Error: Msg 6260, Level 16, State 1, Line 1
Dear All, I always got this error in CLR TVF:
Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
at System.Data.Common.DbEnumerator.BuildSchemaInfo()
at System.Data.Common.DbEnumerator.MoveNext()
Here is my code:
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;
public static class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
string sqlstring = "select * from Activity where CatActivityID=@.CatActivityID;";
connection.Open();
SqlCommand command = new SqlCommand(sqlstring, connection);
command.Parameters.AddWithValue("@.CatActivityID", CatActivityID);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
public static void rowfiller(Object obj,
out SqlInt32 ActID,
out SqlString ActName,
out SqlInt32 ActCreatorID,
out SqlString ActDesp,
out SqlDateTime ActCreateDate,
out SqlDateTime ActModifyDate,
out SqlDateTime ActStartDate,
out SqlDateTime ActEndDate,
out SqlInt32 Status,
out SqlInt32 Cost,
)
{
SqlDataRecord row = (SqlDataRecord)obj;
int column = 0;
ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
}
};
Can anyone tell me what I am doing wrong? Many thanks
.
You can not do data access from a Data Reader in a CLR TVF. Basically, after accessing the first row in the reader the data reader closes down.Niels
|||Just a correction, as soon as you leave the TVF method the datareader closes down - you won't be able to get even the first row.
Niels
|||
Hi, Niels
Thanks for your responding. Do you have any idea how to get arround this problem, if I do need a table value by query other tables?
|||Use T-SQL!! T-SQL is much, much better than CLR for in-database data access anyway. From your code above I ca not see any reason per se to use CLR (however, there may be more stuff going on than what you show in your code).Niels
CLR TVF -> Using Datareader...
Hi
I'm trying to create a CLR-TVF which should do some stuff (in my sample it's
just getting the syscolumns name column for the database _ODS).
I’ve got this error:
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.
Question: Is it really not possible to make a sql-query within the C#-Part
of the CLR? I know, I could easily do it using a StoredProc…but I need a T
VF.
Thanks for your help…
Here is the code:
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)" )]
public static IEnumerable LoadSysColumns(string str2)
{
return str2;
}
public static void FillRow2(object row,
out string str2)
{
// creating a connection using the current sqlserver-context
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from _ODS.dbo.SYSCOLUMNS";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
str2 = sqlDataReader.GetValue(0).ToString();
}
}"Dominic" <Dominic@.discussions.microsoft.com> wrote in message
news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Hi
> I'm trying to create a CLR-TVF which should do some stuff (in my sample
> it's
> just getting the syscolumns name column for the database _ODS).
> I've got this error:
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Data access is not allowed in this
> context. Either the context is a function or method not marked with
> DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
> data from FillRow method of a Table Valued Function, or is a UDT
> validation
> method.
> Question: Is it really not possible to make a sql-query within the C#-Part
> of the CLR? I know, I could easily do it using a StoredProc.but I need a
> TVF.
> Thanks for your help.
> Here is the code:
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)" )]
> public static IEnumerable LoadSysColumns(string str2)
> {
> return str2;
> }
> public static void FillRow2(object row,
> out string str2)
> {
> // creating a connection using the current sqlserver-context
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from
> _ODS.dbo.SYSCOLUMNS";
> SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
> sqlDataReader.Read();
> str2 = sqlDataReader.GetValue(0).ToString();
> }
> }
>
Any data access should be in the LoadSysColumns method, not in the
FillRowMethod. In LoadSysColumns just fill a List<string> and return that.
SQL will enumerate it and pass each member to your FillRowMethod, EG:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)")]
public static IEnumerable LoadSysColumns()
{
List<string> names = new List<string>();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
while (sqlDataReader.Read())
{
names.Add(sqlDataReader.GetValue(0).ToString());
}
}
return names;
}
public static void FillRow2(object row, out string str2)
{
str2 = (string)row;
}
};
David|||Thanks David...works fine!
"David Browne" wrote:
> "Dominic" <Dominic@.discussions.microsoft.com> wrote in message
> news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Any data access should be in the LoadSysColumns method, not in the
> FillRowMethod. In LoadSysColumns just fill a List<string> and return that
.
> SQL will enumerate it and pass each member to your FillRowMethod, EG:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections;
> using System.Collections.Generic;
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.None,
> SystemDataAccess = SystemDataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)")]
> public static IEnumerable LoadSysColumns()
> {
> List<string> names = new List<string>();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
> using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
> while (sqlDataReader.Read())
> {
> names.Add(sqlDataReader.GetValue(0).ToString());
> }
> }
> return names;
> }
> public static void FillRow2(object row, out string str2)
> {
> str2 = (string)row;
> }
> };
>
> David
>
>
I'm trying to create a CLR-TVF which should do some stuff (in my sample it's
just getting the syscolumns name column for the database _ODS).
I’ve got this error:
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.
Question: Is it really not possible to make a sql-query within the C#-Part
of the CLR? I know, I could easily do it using a StoredProc…but I need a T
VF.
Thanks for your help…
Here is the code:
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)" )]
public static IEnumerable LoadSysColumns(string str2)
{
return str2;
}
public static void FillRow2(object row,
out string str2)
{
// creating a connection using the current sqlserver-context
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from _ODS.dbo.SYSCOLUMNS";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
str2 = sqlDataReader.GetValue(0).ToString();
}
}"Dominic" <Dominic@.discussions.microsoft.com> wrote in message
news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Hi
> I'm trying to create a CLR-TVF which should do some stuff (in my sample
> it's
> just getting the syscolumns name column for the database _ODS).
> I've got this error:
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Data access is not allowed in this
> context. Either the context is a function or method not marked with
> DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
> data from FillRow method of a Table Valued Function, or is a UDT
> validation
> method.
> Question: Is it really not possible to make a sql-query within the C#-Part
> of the CLR? I know, I could easily do it using a StoredProc.but I need a
> TVF.
> Thanks for your help.
> Here is the code:
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)" )]
> public static IEnumerable LoadSysColumns(string str2)
> {
> return str2;
> }
> public static void FillRow2(object row,
> out string str2)
> {
> // creating a connection using the current sqlserver-context
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from
> _ODS.dbo.SYSCOLUMNS";
> SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
> sqlDataReader.Read();
> str2 = sqlDataReader.GetValue(0).ToString();
> }
> }
>
Any data access should be in the LoadSysColumns method, not in the
FillRowMethod. In LoadSysColumns just fill a List<string> and return that.
SQL will enumerate it and pass each member to your FillRowMethod, EG:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)")]
public static IEnumerable LoadSysColumns()
{
List<string> names = new List<string>();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
while (sqlDataReader.Read())
{
names.Add(sqlDataReader.GetValue(0).ToString());
}
}
return names;
}
public static void FillRow2(object row, out string str2)
{
str2 = (string)row;
}
};
David|||Thanks David...works fine!
"David Browne" wrote:
> "Dominic" <Dominic@.discussions.microsoft.com> wrote in message
> news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Any data access should be in the LoadSysColumns method, not in the
> FillRowMethod. In LoadSysColumns just fill a List<string> and return that
.
> SQL will enumerate it and pass each member to your FillRowMethod, EG:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections;
> using System.Collections.Generic;
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.None,
> SystemDataAccess = SystemDataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)")]
> public static IEnumerable LoadSysColumns()
> {
> List<string> names = new List<string>();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
> using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
> while (sqlDataReader.Read())
> {
> names.Add(sqlDataReader.GetValue(0).ToString());
> }
> }
> return names;
> }
> public static void FillRow2(object row, out string str2)
> {
> str2 = (string)row;
> }
> };
>
> David
>
>
CLR Triggers, Linq, and Transactions
I'm trying convert my working CLR Trigger to a Linq-based implementation, but I don't seem to be able to get Linq to cooperate with the Trigger's context transaction (a local SubordinateTransaction with IsolationLevel="ReadCommitted").
public static void LinQTrigger()
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
if (triggerContext.TriggerAction == TriggerAction.Insert || triggerContext.TriggerAction == TriggerAction.Update) {
//using (TransactionScope scope = new TransactionScope(Transaction.Current)) { /* err 6549 */
using (SqlConnection connection = new SqlConnection("context connection=true")) {
connection.Open();
using (MyDataContext context = new MyDataContext(connection)) {
List<int[]> updates = new List<int[]>();
Running this on SQL Server 2005 / .NET 3.5 beta2 produces an Error 6522 "Cannot enlist in the transaction because a local transaction is in progress on the connection." in the line executing the Linq query because the Linq DataContext apparently insists on opening a transaction.
The recommended pattern for DataContext query operations - running them inside a TransactionScope as indicated by the commented-out sections - unfortunately leads to a different Error 6549 in the TransactionScope constructor complaining about a missing MSDTC. And there shouldn't be a need for a distributed transaction here, should there?
Using different TransactionScope constructors (TransactionScopeOptions.Required, TransactionOptions(){IsolationLevel=ReadCommitted}) did not make any difference, either.
What am I doing wrong here? Is there any way for the Linq DataContext to attach to the CLR Trigger's ambient transaction?
Interesting scenario! I won't ask why you'd want to use LINQ in this way :-)
For brevity's sake I've boiled down my scenario to a bare minimum: A SQL table [Test] with three columns (int Manual, int Automatic, IDENTITY int ID) needs any external updates on its [Manual] column to be applied to its [Automatic] column (with an additional sign change) through a Trigger.
A fairly direct translation of my non-Linq CLR Trigger to Linq yields the following code (the [Trigger_TestLinQs] SQL table has been modified to point to the Trigger's "INSERTED" table, [TestLinQs] points to the actual SQL table):
Code Snippet
[SqlTrigger(Name = "LinQTrigger", Target = "dbo.TestLinQ", Event = "FOR INSERT, UPDATE")]public static void LinQTrigger()
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
if (triggerContext.TriggerAction == TriggerAction.Insert || triggerContext.TriggerAction == TriggerAction.Update) {
//using (TransactionScope scope = new TransactionScope(Transaction.Current)) { /* err 6549 */
using (SqlConnection connection = new SqlConnection("context connection=true")) {
connection.Open();
using (MyDataContext context = new MyDataContext(connection)) {
List<int[]> updates = new List<int[]>();
foreach (Trigger_TestLinQ test in (from t in context.Trigger_TestLinQs select t)) { /* err 6522 */
if (test.Manual != null && triggerContext.IsUpdatedColumn(0))
updates.Add(new int[] { test.ID, -test.Manual.Value });
}
foreach (int[] update in updates) {
dbo.TestLinQ test = (from t in context.TestLinQs where t.ID == update[0] select t).Single<dbo.TestLinQ>();
test.Automatic = update[1];
}
context.SubmitChanges();
}
}
// scope.Complete();
//}
}
}
Running this on SQL Server 2005 / .NET 3.5 beta2 produces an Error 6522 "Cannot enlist in the transaction because a local transaction is in progress on the connection." in the line executing the Linq query because the Linq DataContext apparently insists on opening a transaction.
The recommended pattern for DataContext query operations - running them inside a TransactionScope as indicated by the commented-out sections - unfortunately leads to a different Error 6549 in the TransactionScope constructor complaining about a missing MSDTC. And there shouldn't be a need for a distributed transaction here, should there?
Using different TransactionScope constructors (TransactionScopeOptions.Required, TransactionOptions(){IsolationLevel=ReadCommitted}) did not make any difference, either.
What am I doing wrong here? Is there any way for the Linq DataContext to attach to the CLR Trigger's ambient transaction?
Interesting scenario! I won't ask why you'd want to use LINQ in this way :-)
Anyway, first of all I'm a little bit surprised that LINQ works at all within SQLCLR. I thought it would not work with a Context Connection at all.
It looks like, at the moment, that LINQ does not work very well with transactions when running inside SQLCLR (in fact it looks it doesn't work at all). So at the moment, I don't think there is anything you can do.
Niels
|||Hi nielsb,
thanks for destroying my last hope :-).
You're probably right in that presently LinQ DataContexts don't fully - or maybe even not at all - interoperate with a Context Connection.
I'm wondering why that is the case, however, since it's basically just a case of O/R mapping - which can apparently be coaxed to work with dynamic SQL Tables - and of attaching to a local System.Transactions.Transaction.
Interestingly enough you can make the LinQ DataContext work for the SELECT statement embedded in the trigger by explicitely opening a new Transaction on the SqlConnection. You can't effectively perform any updates, though, because the DataContext's change tracking mechanism doesn't kick in.
As to why I'd want to use LinQ in this context - it's the logical next step once you've been lured away from the glory of T-SQL into the murky depths of SQL Server CLR integration. :-]
CLR Triggers and inserted table
I have created a CLR trigger that validates an address that has been enetere
d
into our address database does not already exist and that it meets country
critera for addressing.
I have a RowVersion column (timestamp), I use this to determin if any
changes have been made to a record in the table on update saving me having t
o
write long SELECT statements.
The problem I have is when I update a row in one instance of Manegement
Studio then try to update in another instance no rows are returned in the
inserted table. What I would like to have happen is a check to see if someon
e
else has changed the row and if so fail the trigger. My SQL trigger works
fine and does everything I need except all the complicated validation and is
why I have choosen to write using the CLR.
At present I have converted the following SQL to C#
CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
INSERT, UPDATE AS
SET NOCOUNT ON;
IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
[inserted].[CountryCode] = [Countries].[CountryCode]
WHERE
NULLIF(LTRIM(RTRIM([Line1])),'') IS NU
LL AND [Line1Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line2])),'') IS NU
LL AND [Line2Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line3])),'') IS NU
LL AND [Line3Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line4])),'') IS NU
LL AND [Line4Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line5])),'') IS NU
LL AND [Line5Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line6])),'') IS NU
LL AND [Line6Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line7])),'') IS NU
LL AND [Line7Required] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NULL AND [PostalCodeRequired] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
LIKE [PostalCodeRegExpression] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMinimumCharacters]
<> 0 AND LEN([PostalCode]) <
[PostalCodeMinimumCharacters] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMaximumCharacters]
<> 0 AND LEN([PostalCode]) >
[PostalCodeMaximumCharacters])
RAISERROR('Address does not meet the Countries address criteria.',16,1)
ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
([inserted].[CountryCode] = [Addresses].[CountryCode] OR [inserted].[CountryCode] IS
NULL AND [Addresses].[CountryCode] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') = [Addresses]. [BuildingName] OR NULLIF(LTRIM(RTRIM([i
nserted].[BuildingName])),'') IS
NULL AND [Addresses].[BuildingName] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') = [Addresses]. [BuildingNumber] OR NULLIF(LTRIM(RTRIM([
inserted].[BuildingNumber])),'') IS
NULL AND [Addresses].[BuildingNumber] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses]. [Line1] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine1])),'') IS
NULL AND [Addresses].[Line1] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses]. [Line2] OR NULLIF(LTRIM(RTRIM([insert
ed].[
Line2])),'') IS
NULL AND [Addresses].[Line2] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses]. [Line3] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine3])),'') IS
NULL AND [Addresses].[Line3] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses]. [Line4] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine4])),'') IS
NULL AND [Addresses].[Line4] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses]. [Line5] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine5])),'') IS
NULL AND [Addresses].[Line5] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses]. [Line6] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine6])),'') IS
NULL AND [Addresses].[Line6] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses]. [Line7] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine7])),'') IS
NULL AND [Addresses].[Line7] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') = [Addresses]. [PostalCode] OR NULLIF(LTRIM(RTRIM([ins
erted].[PostalCode])),'') IS
NULL AND [Addresses].[PostalCode] IS NULL))
RAISERROR('Address already exists.',16,1)
ELSE
IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
BEGIN
INSERT INTO Addresses (
[Addresses].[CountryCode],
[Addresses].[BuildingName],
[Addresses].[BuildingNumber],
[Addresses].[Line1],
[Addresses].[Line2],
[Addresses].[Line3],
[Addresses].[Line4],
[Addresses].[Line5],
[Addresses].[Line6],
[Addresses].[Line7],
[Addresses].[PostalCode]
)
SELECT
[inserted].[CountryCode],
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
FROM [inserted]
END
ELSE -- Update Address
BEGIN
IF EXISTS (
SELECT
NULL
FROM
[inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
[Addresses].[AddressID] AND [inserted].[RowVersion] = [Addresses].[RowVersion]
)
UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses]. [BuildingName] = NULLIF(LTRIM(RTRIM(
[inserted].[BuildingName])),''),
[Addresses]. [BuildingNumber] = NULLIF(LTRIM(RTRIM
([inserted].[BuildingNumber])),''),
[Addresses]. [Line1] = NULLIF(LTRIM(RTRIM([inse
rted].[Line1])),''),
[Addresses]. [Line2] = NULLIF(LTRIM(RTRIM([inse
rted].[Line2])),''),
[Addresses]. [Line3] = NULLIF(LTRIM(RTRIM([inse
rted].[Line3])),''),
[Addresses]. [Line4] = NULLIF(LTRIM(RTRIM([inse
rted].[Line4])),''),
[Addresses]. [Line5] = NULLIF(LTRIM(RTRIM([inse
rted].[Line5])),''),
[Addresses]. [Line6] = NULLIF(LTRIM(RTRIM([inse
rted].[Line6])),''),
[Addresses]. [Line7] = NULLIF(LTRIM(RTRIM([inse
rted].[Line7])),''),
[Addresses]. [PostalCode] = NULLIF(LTRIM(RTRIM([i
nserted].[PostalCode])),'')
FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
ELSE
RAISERROR('Address has since been modified.',16,1)
END
My tables are as follows:
CREATE TABLE [dbo].[Addresses](
[AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[CountryCode] [smallint] NOT NULL CONSTRAINT
[DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
[BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
(
[RowVersion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
CREATE TABLE [dbo].[Countries](
[CountryCode] [smallint] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line1Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine1Required] DEFAULT ((0)),
[Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
DEFAULT ((0)),
[Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
else (1) end,(0))),
[Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line2Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine2Required] DEFAULT ((0)),
[Line2ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line2ForceUppser]
DEFAULT ((0)),
[Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
else (1) end,(0))),
[Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line3Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine3Required] DEFAULT ((0)),
[Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
DEFAULT ((0)),
[Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
else (1) end,(0))),
[Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line4Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine4Required] DEFAULT ((0)),
[Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
DEFAULT ((0)),
[Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
else (1) end,(0))),
[Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line5Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine5Required] DEFAULT ((0)),
[Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
DEFAULT ((0)),
[Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
else (1) end,(0))),
[Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line6Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine6Required] DEFAULT ((0)),
[Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
DEFAULT ((0)),
[Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
else (1) end,(0))),
[Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line7Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine7Required] DEFAULT ((0)),
[Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
DEFAULT ((0)),
[Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
else (1) end,(0))),
[PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeRequired] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeRequired] DEFAULT ((1)),
[PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
[PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
then (0) else (1) end,(0))),
[PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
[PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
My CLR is:
using System;
using System.ComponentModel;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
namespace CoullByte.Eddie.SQL.Address.Triggers
{
/// <summary>
/// Address Trigger
/// </summary>
public class Address
{
/// <summary>
/// Prevents Duplicate and Validates the information being stored
/// </summary>
[SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
= "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
public static void DuplicateValidate()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlCommand command;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
#region Address Declaration
decimal AddressID;
byte[] RowVersion;
short CountryCode;
object BuildingName;
object BuildingNumber;
object Line1;
object Line2;
object Line3;
object Line4;
object Line5;
object Line6;
object Line7;
object PostalCode;
#endregion
#region Country Validation Declaration
bool Line1Required;
bool Line1Visible;
bool Line2Required;
bool Line2Visible;
bool Line3Required;
bool Line3Visible;
bool Line4Required;
bool Line4Visible;
bool Line5Required;
bool Line5Visible;
bool Line6Required;
bool Line6Visible;
bool Line7Required;
bool Line7Visible;
bool PostalCodeRequired;
bool PostalCodeVisible;
object PostalCodeRegExpression;
int PostalCodeMinimumCharacters;
int PostalCodeMaximumCharacters;
#endregion
try
{
switch (triggerContext.TriggerAction)
{
case TriggerAction.Insert:
break;
case TriggerAction.Update:
using (SqlConnection connection = new SqlConnection(@."context
connection=true"))
{
connection.Open();
#region Read Address
command = connection.CreateCommand();
command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
Line7, PostalCode FROM [inserted];";
reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw (new ApplicationException("No Rows"));
}
reader.Read();
if (reader == null)
{
throw (new ApplicationException("Reader"));
}
AddressID = (decimal)reader["AddressID"];
// RowVersion = (byte[])reader["RowVersion"];
CountryCode = (short)reader["CountryCode"];
BuildingName = reader["BuildingName"];
BuildingNumber = reader["BuildingNumber"];
Line1 = reader["Line1"];
Line2 = reader["Line2"];
Line3 = reader["Line3"];
Line4 = reader["Line4"];
Line5 = reader["Line5"];
Line6 = reader["Line6"];
Line7 = reader["Line7"];
PostalCode = reader["PostalCode"];
reader.Close();
#endregion
#region Read Country Validation
command = connection.CreateCommand();
command.CommandText =
@." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
command.CommandType = CommandType.StoredProcedure;
command.Parameters["@.CountryCode"].Value = CountryCode;
reader = command.ExecuteReader();
reader.Read();
Line1Required = (bool)reader["Line1Required"];
Line1Visible = (bool)reader["Line1Visible"];
Line2Required = (bool)reader["Line2Required"];
Line2Visible = (bool)reader["Line2Visible"];
Line3Required = (bool)reader["Line3Required"];
Line3Visible = (bool)reader["Line3Visible"];
Line4Required = (bool)reader["Line4Required"];
Line4Visible = (bool)reader["Line4Visible"];
Line5Required = (bool)reader["Line5Required"];
Line5Visible = (bool)reader["Line5Visible"];
Line6Required = (bool)reader["Line6Required"];
Line6Visible = (bool)reader["Line6Visible"];
Line7Required = (bool)reader["Line7Required"];
Line7Visible = (bool)reader["Line7Visible"];
PostalCodeRequired = (bool)reader["PostalCodeRequired"];
PostalCodeVisible = (bool)reader["PostalCodeVisible"];
PostalCodeRegExpression = reader["PostalCodeRegExpression"];
PostalCodeMinimumCharacters =
(int)reader["PostalCodeMinimumCharacters"];
PostalCodeMaximumCharacters =
(int)reader["PostalCodeMaximumCharacters"];
reader.Close();
#endregion
#region Validate Address
bool addressIsValid =
CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingName
,
BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7, PostalCode,
Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
Line6Required, Line6Visible, Line7Required, Line7Visible, PostalCodeRequired
,
PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
PostalCodeMaximumCharacters);
#endregion
command = connection.CreateCommand();
command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
[Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
[inserted].[RowVersion] = [Addresses].[RowVersion]";
command.CommandType = CommandType.Text;
int addressUnchanged = (int)command.ExecuteScalar();
if (addressUnchanged != 1)
{
throw (new ApplicationException("The address has since been changed
by someone else."));
}
if (!addressIsValid)
{
throw (new ApplicationException("The updated changes made do not
meet the countries address criteria."));
}
command = connection.CreateCommand();
command.CommandText = @."UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses].[BuildingName] =
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''), [Addresses].[BuildingNumber]
= NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''), [Addresses].[Line1] =
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
[inserted].[AddressID] = [Addresses].[AddressID]";
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
}
break;
}
}
catch (System.Data.SqlClient.SqlException sqlExp)
{
throw (sqlExp);
}
catch (System.NullReferenceException nullRef)
{
throw (new ApplicationException(nullRef.StackTrace));
}
catch (System.Exception exp)
{
throw (exp);
}
}
}
}Hi Paul,
What complicated validation? The IS NULL stuff etc... should be done in SQL
too.
Only use CLR if you are going to do something that cannot be better done
using set based methods, such examples would be if you had a post code look
up software to validate the post code exists, you'd probably do that using a
CLR UDF in a CHECK constraint.
Ditch the C# and stick with the SQL trigger - seriously!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:D04F7671-BB3B-42BB-A3C2-0ADC8810B708@.microsoft.com...
>I have created a CLR trigger that validates an address that has been
>enetered
> into our address database does not already exist and that it meets country
> critera for addressing.
> I have a RowVersion column (timestamp), I use this to determin if any
> changes have been made to a record in the table on update saving me having
> to
> write long SELECT statements.
> The problem I have is when I update a row in one instance of Manegement
> Studio then try to update in another instance no rows are returned in the
> inserted table. What I would like to have happen is a check to see if
> someone
> else has changed the row and if so fail the trigger. My SQL trigger works
> fine and does everything I need except all the complicated validation and
> is
> why I have choosen to write using the CLR.
> At present I have converted the following SQL to C#
> CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
> INSERT, UPDATE AS
> SET NOCOUNT ON;
> IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
> [inserted].[CountryCode] = [Countries].[CountryCode]
> WHERE
> NULLIF(LTRIM(RTRIM([Line1])),'') IS NULL AND [Line1Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line2])),'') IS NULL AND [Line2Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line3])),'') IS NULL AND [Line3Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line4])),'') IS NULL AND [Line4Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line5])),'') IS NULL AND [Line5Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line6])),'') IS NULL AND [Line6Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line7])),'') IS NULL AND [Line7Required] = 1 OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NULL AND [PostalCodeRequired] = 1
> OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
> LIKE [PostalCodeRegExpression] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMinimumCharacters] <> 0 AND LEN([PostalCode]) <
> [PostalCodeMinimumCharacters] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMaximumCharacters] <> 0 AND LEN([PostalCode]) >
> [PostalCodeMaximumCharacters])
> RAISERROR('Address does not meet the Countries address criteria.',16,1)
> ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
> ([inserted].[CountryCode] = [Addresses].[CountryCode] OR
> [inserted].[CountryCode] IS
> NULL AND [Addresses].[CountryCode] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') =
> [Addresses].[BuildingName] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') IS
> NULL AND [Addresses].[BuildingName] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') =
> [Addresses].[BuildingNumber] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') IS
> NULL AND [Addresses].[BuildingNumber] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses].[Line1] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') IS
> NULL AND [Addresses].[Line1] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses].[Line2] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),'') IS
> NULL AND [Addresses].[Line2] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses].[Line3] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') IS
> NULL AND [Addresses].[Line3] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses].[Line4] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') IS
> NULL AND [Addresses].[Line4] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses].[Line5] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') IS
> NULL AND [Addresses].[Line5] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses].[Line6] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') IS
> NULL AND [Addresses].[Line6] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses].[Line7] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') IS
> NULL AND [Addresses].[Line7] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') =
> [Addresses].[PostalCode] OR
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') IS
> NULL AND [Addresses].[PostalCode] IS NULL))
> RAISERROR('Address already exists.',16,1)
> ELSE
> IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
> BEGIN
> INSERT INTO Addresses (
> [Addresses].[CountryCode],
> [Addresses].[BuildingName],
> [Addresses].[BuildingNumber],
> [Addresses].[Line1],
> [Addresses].[Line2],
> [Addresses].[Line3],
> [Addresses].[Line4],
> [Addresses].[Line5],
> [Addresses].[Line6],
> [Addresses].[Line7],
> [Addresses].[PostalCode]
> )
> SELECT
> [inserted].[CountryCode],
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted]
> END
> ELSE -- Update Address
> BEGIN
> IF EXISTS (
> SELECT
> NULL
> FROM
> [inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
> [Addresses].[AddressID] AND [inserted].[RowVersion] =
> [Addresses].[RowVersion]
> )
> UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] = NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> [Addresses].[Line2] = NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> [Addresses].[Line3] = NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> [Addresses].[Line4] = NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> [Addresses].[Line5] = NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> [Addresses].[Line6] = NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> [Addresses].[Line7] = NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
> ELSE
> RAISERROR('Address has since been modified.',16,1)
> END
> My tables are as follows:
> CREATE TABLE [dbo].[Addresses](
> [AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [CountryCode] [smallint] NOT NULL CONSTRAINT
> [DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
> [BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
> [Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
> (
> [AddressID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
> CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
> (
> [RowVersion] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> CREATE TABLE [dbo].[Countries](
> [CountryCode] [smallint] NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
> [Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line1Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine1Required] DEFAULT ((0)),
> [Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
> DEFAULT ((0)),
> [Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
> else (1) end,(0))),
> [Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line2Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine2Required] DEFAULT ((0)),
> [Line2ForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_Line2ForceUppser]
> DEFAULT ((0)),
> [Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
> else (1) end,(0))),
> [Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line3Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine3Required] DEFAULT ((0)),
> [Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
> DEFAULT ((0)),
> [Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
> else (1) end,(0))),
> [Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line4Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine4Required] DEFAULT ((0)),
> [Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
> DEFAULT ((0)),
> [Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
> else (1) end,(0))),
> [Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line5Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine5Required] DEFAULT ((0)),
> [Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
> DEFAULT ((0)),
> [Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
> else (1) end,(0))),
> [Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line6Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine6Required] DEFAULT ((0)),
> [Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
> DEFAULT ((0)),
> [Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
> else (1) end,(0))),
> [Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line7Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine7Required] DEFAULT ((0)),
> [Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
> DEFAULT ((0)),
> [Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
> else (1) end,(0))),
> [PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeRequired] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeRequired] DEFAULT ((1)),
> [PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
> [PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
> then (0) else (1) end,(0))),
> [PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS
> NULL,
> [PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
> [PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
> CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
> (
> [CountryCode] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> My CLR is:
> using System;
> using System.ComponentModel;
> using System.Collections.Generic;
> using System.Diagnostics;
> using System.Text;
> using System.Data;
> using System.Data.Sql;
> using Microsoft.SqlServer.Server;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Xml;
> namespace CoullByte.Eddie.SQL.Address.Triggers
> {
> /// <summary>
> /// Address Trigger
> /// </summary>
> public class Address
> {
> /// <summary>
> /// Prevents Duplicate and Validates the information being stored
> /// </summary>
> [SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
> = "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
> public static void DuplicateValidate()
> {
> if (!SqlContext.IsAvailable)
> {
> throw (new NotSupportedException());
> }
>
> SqlTriggerContext triggerContext = SqlContext.TriggerContext;
> SqlCommand command;
> SqlPipe pipe = SqlContext.Pipe;
> SqlDataReader reader;
> #region Address Declaration
> decimal AddressID;
> byte[] RowVersion;
> short CountryCode;
> object BuildingName;
> object BuildingNumber;
> object Line1;
> object Line2;
> object Line3;
> object Line4;
> object Line5;
> object Line6;
> object Line7;
> object PostalCode;
> #endregion
> #region Country Validation Declaration
> bool Line1Required;
> bool Line1Visible;
> bool Line2Required;
> bool Line2Visible;
> bool Line3Required;
> bool Line3Visible;
> bool Line4Required;
> bool Line4Visible;
> bool Line5Required;
> bool Line5Visible;
> bool Line6Required;
> bool Line6Visible;
> bool Line7Required;
> bool Line7Visible;
> bool PostalCodeRequired;
> bool PostalCodeVisible;
> object PostalCodeRegExpression;
> int PostalCodeMinimumCharacters;
> int PostalCodeMaximumCharacters;
> #endregion
> try
> {
> switch (triggerContext.TriggerAction)
> {
> case TriggerAction.Insert:
> break;
> case TriggerAction.Update:
> using (SqlConnection connection = new SqlConnection(@."context
> connection=true"))
> {
> connection.Open();
> #region Read Address
> command = connection.CreateCommand();
> command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
> BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
> Line7, PostalCode FROM [inserted];";
> reader = command.ExecuteReader();
> if (!reader.HasRows)
> {
> throw (new ApplicationException("No Rows"));
> }
> reader.Read();
> if (reader == null)
> {
> throw (new ApplicationException("Reader"));
> }
> AddressID = (decimal)reader["AddressID"];
> // RowVersion = (byte[])reader["RowVersion"];
> CountryCode = (short)reader["CountryCode"];
> BuildingName = reader["BuildingName"];
> BuildingNumber = reader["BuildingNumber"];
> Line1 = reader["Line1"];
> Line2 = reader["Line2"];
> Line3 = reader["Line3"];
> Line4 = reader["Line4"];
> Line5 = reader["Line5"];
> Line6 = reader["Line6"];
> Line7 = reader["Line7"];
> PostalCode = reader["PostalCode"];
> reader.Close();
> #endregion
> #region Read Country Validation
> command = connection.CreateCommand();
> command.CommandText =
> @." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
> command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
> command.CommandType = CommandType.StoredProcedure;
> command.Parameters["@.CountryCode"].Value = CountryCode;
> reader = command.ExecuteReader();
> reader.Read();
> Line1Required = (bool)reader["Line1Required"];
> Line1Visible = (bool)reader["Line1Visible"];
> Line2Required = (bool)reader["Line2Required"];
> Line2Visible = (bool)reader["Line2Visible"];
> Line3Required = (bool)reader["Line3Required"];
> Line3Visible = (bool)reader["Line3Visible"];
> Line4Required = (bool)reader["Line4Required"];
> Line4Visible = (bool)reader["Line4Visible"];
> Line5Required = (bool)reader["Line5Required"];
> Line5Visible = (bool)reader["Line5Visible"];
> Line6Required = (bool)reader["Line6Required"];
> Line6Visible = (bool)reader["Line6Visible"];
> Line7Required = (bool)reader["Line7Required"];
> Line7Visible = (bool)reader["Line7Visible"];
> PostalCodeRequired = (bool)reader["PostalCodeRequired"];
> PostalCodeVisible = (bool)reader["PostalCodeVisible"];
> PostalCodeRegExpression = reader["PostalCodeRegExpression"];
> PostalCodeMinimumCharacters =
> (int)reader["PostalCodeMinimumCharacters"];
> PostalCodeMaximumCharacters =
> (int)reader["PostalCodeMaximumCharacters"];
> reader.Close();
> #endregion
> #region Validate Address
> bool addressIsValid =
> CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingNa
me,
> BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7,
> PostalCode,
> Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
> Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
> Line6Required, Line6Visible, Line7Required, Line7Visible,
> PostalCodeRequired,
> PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
> PostalCodeMaximumCharacters);
> #endregion
> command = connection.CreateCommand();
> command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
> [Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
> [inserted].[RowVersion] = [Addresses].[RowVersion]";
> command.CommandType = CommandType.Text;
> int addressUnchanged = (int)command.ExecuteScalar();
> if (addressUnchanged != 1)
> {
> throw (new ApplicationException("The address has since been changed
> by someone else."));
> }
> if (!addressIsValid)
> {
> throw (new ApplicationException("The updated changes made do not
> meet the countries address criteria."));
> }
> command = connection.CreateCommand();
> command.CommandText = @."UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber]
> = NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] =
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
> [inserted].[AddressID] = [Addresses].[AddressID]";
> pipe.Send(command.CommandText);
> command.ExecuteNonQuery();
> }
> break;
> }
> }
> catch (System.Data.SqlClient.SqlException sqlExp)
> {
> throw (sqlExp);
> }
> catch (System.NullReferenceException nullRef)
> {
> throw (new ApplicationException(nullRef.StackTrace));
> }
> catch (System.Exception exp)
> {
> throw (exp);
> }
> }
> }
> }
>|||In my orginal post I said "...and that it meets country
critera for addressing...". The CLR is the only way I can validate an
address is enetered that meets the countries addressing criteria. SQL
triggers do not allow me to do this and is why I chose to use c#. All
countries are stored in the Countries table and each country have diffrent
ways of handling addresses such as that between UK and US. US use Zip Code
and UK use Post Code for example and US Zip codes are not the same format as
UK Post Codes. In the address table I have columns such as Building Name,
Building Number, Line 1, Line 2 - Line 7 then Postal Code. When using our ow
n
software changing country code changes labels and validation requirements on
the Windows Form or on the Web Forms. The c# trigger validates based on the
country and forces NULL into the columns where their is empty strings etc.
Hope this help to explain what I am trying to acheive.|||Based on the code snippet you post, you might have some design issues as
well. Can you post the table structures, a few sample data along with the
address validation rules you have?
In general, Tony's last statement in his response would be a suitable
advice.
Anith|||My Address Validation class is as follows, other stuff already posted and
table scripts also posted:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
namespace CoullByte.Eddie.SQL.Address.Functions
{
/// <summary>
/// Address Validation Class
/// </summary>
public class Validation
{
#region SQL Context
/// <summary>
/// Validate if running under SQL
/// </summary>
private static void ValidateSQLContext()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
}
#endregion
#region Address Is Valid
/// <summary>
/// Address Data Validation
/// </summary>
/// <param name="BuildingName">Building Name</param>
/// <param name="BuildingNumber">Building Number</param>
/// <param name="Line1">Line 1</param>
/// <param name="Line2">Line 2</param>
/// <param name="Line3">Line 3</param>
/// <param name="Line4">Line 4</param>
/// <param name="Line5">Line 5</param>
/// <param name="Line6">Line 6</param>
/// <param name="Line7">Line 7</param>
/// <param name="PostalCode">Postal Code</param>
/// <param name="Line1Required">Line 1 Required?</param>
/// <param name="Line1Visible">Line 1 Visible to User?</param>
/// <param name="Line2Required">Line 2 Required?</param>
/// <param name="Line2Visible">Line 2 Visible to User?</param>
/// <param name="Line3Required">Line 3 Required?</param>
/// <param name="Line3Visible">Line 3 Visible to User?</param>
/// <param name="Line4Required">Line 4 Required?</param>
/// <param name="Line4Visible">Line 4 Visible to User?</param>
/// <param name="Line5Required">Line 5 Required?</param>
/// <param name="Line5Visible">Line 5 Visible to User?</param>
/// <param name="Line6Required">Line 6 Required?</param>
/// <param name="Line6Visible">Line 6 Visible to User?</param>
/// <param name="Line7Required">Line 7 Required?</param>
/// <param name="Line7Visible">Line 7 Visible to User?</param>
/// <param name="PostalCodeRequired">Postal Code Required?</param>
/// <param name="PostalCodeVisible">Postal Code Visible?</param>
/// <param name="PostalCodeRegExpression">Postal Code Regular
Expression</param>
/// <param name="PostalCodeMinimumCharacters">Postal Code Minimum
Characters</param>
/// <param name="PostalCodeMaximumCharacters">Postal Code Maximum
Characters</param>
/// <returns>Validation Result</returns>
public static bool AddressIsValid(object BuildingName, object
BuildingNumber, object Line1, object Line2, object Line3, object Line4,
object Line5, object Line6, object Line7, object PostalCode, bool
Line1Required, bool Line1Visible, bool Line2Required, bool Line2Visible, boo
l
Line3Required, bool Line3Visible, bool Line4Required, bool Line4Visible, boo
l
Line5Required, bool Line5Visible, bool Line6Required, bool Line6Visible, boo
l
Line7Required, bool Line7Visible, bool PostalCodeRequired, bool
PostalCodeVisible, object PostalCodeRegExpression, int
PostalCodeMinimumCharacters, int PostalCodeMaximumCharacters)
{
return Line1IsValid(Line1Required, Line1Visible, Line1) &&
Line2IsValid(Line2Required, Line2Visible, Line2) &&
Line3IsValid(Line3Required, Line3Visible, Line3) &&
Line4IsValid(Line4Required, Line4Visible, Line4) &&
Line5IsValid(Line5Required, Line5Visible, Line5) &&
Line6IsValid(Line6Required, Line6Visible, Line6) &&
Line7IsValid(Line7Required, Line7Visible, Line7) &&
PostalCodeIsValid(PostalCodeRequired, PostalCodeVisible,
PostalCodeMinimumCharacters, PostalCodeMaximumCharacters,
PostalCodeRegExpression, PostalCode);
}
#endregion
#region Line 1 Data Validation
/// <summary>
/// Line 1 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line1IsValid (bool Line1Required, bool Line1Visible,
object Line1Data)
{
ValidateSQLContext();
if (!Line1Visible)
{
return true;
}
else
{
return ((!Line1Required) || (Line1Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line1Data)));
}
}
#endregion
#region Line 2 Data Validation
/// <summary>
/// Line 2 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line2IsValid(bool Line2Required, bool Line2Visible,
object Line2Data)
{
ValidateSQLContext();
if (!Line2Visible)
{
return true;
}
else
{
return ((!Line2Required) || (Line2Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line2Data)));
}
}
#endregion
#region Line 3 Data Validation
/// <summary>
/// Line 3 Data Validation
/// </summary>
/// <param name="Line3Required">Data Required?</param>
/// <param name="Line3Visible">Visible to User?</param>
/// <param name="Line3Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line3IsValid(bool Line3Required, bool Line3Visible,
object Line3Data)
{
ValidateSQLContext();
if (!Line3Visible)
{
return true;
}
else
{
return ((!Line3Required) || (Line3Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line3Data)));
}
}
#endregion
#region Line 4 Data Validation
/// <summary>
/// Line 4 Data Validation
/// </summary>
/// <param name="Line4Required">Data Required?</param>
/// <param name="Line4Visible">Visible to User?</param>
/// <param name="Line4Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line4IsValid(bool Line4Required, bool Line4Visible,
object Line4Data)
{
ValidateSQLContext();
if (!Line4Visible)
{
return true;
}
else
{
return ((!Line4Required) || (Line4Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line4Data)));
}
}
#endregion
#region Line 5 Data Validation
/// <summary>
/// Line 5 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line5IsValid(bool Line5Required, bool Line5Visible,
object Line5Data)
{
ValidateSQLContext();
if (!Line5Visible)
{
return true;
}
else
{
return ((!Line5Required) || (Line5Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line5Data)));
}
}
#endregion
#region Line 6 Data Validation
/// <summary>
/// Line 6 Data Validation
/// </summary>
/// <param name="Line6Required">Data Required?</param>
/// <param name="Line6Visible">Visible to User?</param>
/// <param name="Line6Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line6IsValid(bool Line6Required, bool Line6Visible,
object Line6Data)
{
if (!Line6Visible)
{
return true;
}
else
{
return ((!Line6Required) || (Line6Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line6Data)));
}
}
#endregion
#region Line 7 Data Validation
/// <summary>
/// Line 7 Data Validation
/// </summary>
/// <param name="Line7Required">Data Required?</param>
/// <param name="Line7Visible">Visible to User?</param>
/// <param name="Line7Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line7IsValid(bool Line7Required, bool Line7Visible,
object Line7Data)
{
if (!Line7Visible)
{
return true;
}
else
{
return ((!Line7Required) || (Line7Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line7Data)));
}
}
#endregion
#region Postal Code Validation
/// <summary>
/// Postal Code Validation
/// </summary>
/// <param name="PostalCodeRequired">Data Required</param>
/// <param name="PostalCodeVisible">Visible to User?</param>
/// <param name="PostalCodeMinimumCharacters">Minimum Characters
Required?</param>
/// <param name="PostalCodeMaximumCharacters">Maximum Characters
Required?</param>
/// <param name="PostalCodeRegExpression">Regular Expression for
Validating Data</param>
/// <param name="PostalCodeData">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool PostalCodeIsValid(bool PostalCodeRequired, bool
PostalCodeVisible, int PostalCodeMinimumCharacters, int
PostalCodeMaximumCharacters, object PostalCodeRegExpression, object
PostalCodeData)
{
string postalCodeData = string.Empty;
string postalCodeRegExpression = string.Empty;
if (PostalCodeRegExpression != null && PostalCodeRegExpression !=
DBNull.Value && PostalCodeRegExpression is string)
{
postalCodeRegExpression = Convert.ToString(PostalCodeRegExpression);
}
if (PostalCodeData != null && PostalCodeData != DBNull.Value &&
PostalCodeData is string)
{
postalCodeData = Convert.ToString(PostalCodeData);
}
bool lengthIsValid =
(
(
PostalCodeMinimumCharacters == 0
) ||
(
PostalCodeMinimumCharacters != 0 &&
postalCodeData.Length >= PostalCodeMinimumCharacters
) &&
(
(
PostalCodeMaximumCharacters == 0
) ||
(
PostalCodeMaximumCharacters != 0 &&
postalCodeData.Length <= PostalCodeMaximumCharacters
)
)
);
bool regExpIsValid =
(
(
postalCodeRegExpression == string.Empty
) ||
(
CoullByte.Eddie.SQL.Functions.Data.IsMeaningless(PostalCodeRegExpression)
) ||
(
postalCodeRegExpression != string.Empty &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(postalCodeRegExpression) &&
System.Text.RegularExpressions.Regex.IsMatch(postalCodeData,
postalCodeRegExpression)
)
);
if (PostalCodeRequired)
{
return (CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(PostalCodeData)
&& lengthIsValid && regExpIsValid);
}
else
{
return (lengthIsValid && regExpIsValid);
}
}
#endregion
}
}
a sample row for the Countries table is as follows:
INSERT INTO [Eddie].[dbo].[Countries]
([CountryCode]
,[Country]
,[Line1Label]
,[Line1ToolTip]
,[Line1Required]
,[Line1ForceUpper]
,[Line2Label]
,[Line2ToolTip]
,[Line2Required]
,[Line2ForceUpper]
,[Line3Label]
,[Line3ToolTip]
,[Line3Required]
,[Line3ForceUpper]
,[Line4Label]
,[Line4ToolTip]
,[Line4Required]
,[Line4ForceUpper]
,[Line5Label]
,[Line5ToolTip]
,[Line5Required]
,[Line5ForceUpper]
,[Line6Label]
,[Line6ToolTip]
,[Line6Required]
,[Line6ForceUpper]
,[Line7Label]
,[Line7ToolTip]
,[Line7Required]
,[Line7ForceUpper]
,[PostalCodeLabel]
,[PostalCodeToolTip]
,[PostalCodeRequired]
,[PostalCodeForceUpper]
,[PostalCodeRegExpression]
,[PostalCodeMinimumCharacters]
,[PostalCodeMaximumCharacters])
VALUES
(44
,'United Kingdom'
,'Thoroughfare'
,NULL
,0
,0
,'Locality'
,NULL
,0
,0
,'Post Town'
,NULL
,1
,1
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,'Post Code'
,NULL
,1
,1
,'(GIR0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A
-Z-[IJZ]][0-9][ABEHMNPRVWXY]))))
[0-9][A-Z-[CIKMOV]]{2})'
,3
,8)
A sample address for the Addresses table is:
INSERT INTO [Eddie].[dbo].[Addresses]
([CountryCode]
,[BuildingName]
,[BuildingNumber]
,[Line1]
,[Line2]
,[Line3]
,[Line4]
,[Line5]
,[Line6]
,[Line7]
,[PostalCode])
VALUES
(44
,NULL
,NULL
,'Queens Walk'
,NULL
,'READING'
,NULL
,NULL
,NULL
,NULL
,'RG1 7PT')
d
into our address database does not already exist and that it meets country
critera for addressing.
I have a RowVersion column (timestamp), I use this to determin if any
changes have been made to a record in the table on update saving me having t
o
write long SELECT statements.
The problem I have is when I update a row in one instance of Manegement
Studio then try to update in another instance no rows are returned in the
inserted table. What I would like to have happen is a check to see if someon
e
else has changed the row and if so fail the trigger. My SQL trigger works
fine and does everything I need except all the complicated validation and is
why I have choosen to write using the CLR.
At present I have converted the following SQL to C#
CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
INSERT, UPDATE AS
SET NOCOUNT ON;
IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
[inserted].[CountryCode] = [Countries].[CountryCode]
WHERE
NULLIF(LTRIM(RTRIM([Line1])),'') IS NU
LL AND [Line1Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line2])),'') IS NU
LL AND [Line2Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line3])),'') IS NU
LL AND [Line3Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line4])),'') IS NU
LL AND [Line4Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line5])),'') IS NU
LL AND [Line5Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line6])),'') IS NU
LL AND [Line6Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line7])),'') IS NU
LL AND [Line7Required] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NULL AND [PostalCodeRequired] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
LIKE [PostalCodeRegExpression] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMinimumCharacters]
<> 0 AND LEN([PostalCode]) <
[PostalCodeMinimumCharacters] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMaximumCharacters]
<> 0 AND LEN([PostalCode]) >
[PostalCodeMaximumCharacters])
RAISERROR('Address does not meet the Countries address criteria.',16,1)
ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
([inserted].[CountryCode] = [Addresses].[CountryCode] OR [inserted].[CountryCode] IS
NULL AND [Addresses].[CountryCode] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') = [Addresses]. [BuildingName] OR NULLIF(LTRIM(RTRIM([i
nserted].[BuildingName])),'') IS
NULL AND [Addresses].[BuildingName] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') = [Addresses]. [BuildingNumber] OR NULLIF(LTRIM(RTRIM([
inserted].[BuildingNumber])),'') IS
NULL AND [Addresses].[BuildingNumber] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses]. [Line1] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine1])),'') IS
NULL AND [Addresses].[Line1] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses]. [Line2] OR NULLIF(LTRIM(RTRIM([insert
ed].[
Line2])),'') IS
NULL AND [Addresses].[Line2] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses]. [Line3] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine3])),'') IS
NULL AND [Addresses].[Line3] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses]. [Line4] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine4])),'') IS
NULL AND [Addresses].[Line4] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses]. [Line5] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine5])),'') IS
NULL AND [Addresses].[Line5] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses]. [Line6] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine6])),'') IS
NULL AND [Addresses].[Line6] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses]. [Line7] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine7])),'') IS
NULL AND [Addresses].[Line7] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') = [Addresses]. [PostalCode] OR NULLIF(LTRIM(RTRIM([ins
erted].[PostalCode])),'') IS
NULL AND [Addresses].[PostalCode] IS NULL))
RAISERROR('Address already exists.',16,1)
ELSE
IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
BEGIN
INSERT INTO Addresses (
[Addresses].[CountryCode],
[Addresses].[BuildingName],
[Addresses].[BuildingNumber],
[Addresses].[Line1],
[Addresses].[Line2],
[Addresses].[Line3],
[Addresses].[Line4],
[Addresses].[Line5],
[Addresses].[Line6],
[Addresses].[Line7],
[Addresses].[PostalCode]
)
SELECT
[inserted].[CountryCode],
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
FROM [inserted]
END
ELSE -- Update Address
BEGIN
IF EXISTS (
SELECT
NULL
FROM
[inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
[Addresses].[AddressID] AND [inserted].[RowVersion] = [Addresses].[RowVersion]
)
UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses]. [BuildingName] = NULLIF(LTRIM(RTRIM(
[inserted].[BuildingName])),''),
[Addresses]. [BuildingNumber] = NULLIF(LTRIM(RTRIM
([inserted].[BuildingNumber])),''),
[Addresses]. [Line1] = NULLIF(LTRIM(RTRIM([inse
rted].[Line1])),''),
[Addresses]. [Line2] = NULLIF(LTRIM(RTRIM([inse
rted].[Line2])),''),
[Addresses]. [Line3] = NULLIF(LTRIM(RTRIM([inse
rted].[Line3])),''),
[Addresses]. [Line4] = NULLIF(LTRIM(RTRIM([inse
rted].[Line4])),''),
[Addresses]. [Line5] = NULLIF(LTRIM(RTRIM([inse
rted].[Line5])),''),
[Addresses]. [Line6] = NULLIF(LTRIM(RTRIM([inse
rted].[Line6])),''),
[Addresses]. [Line7] = NULLIF(LTRIM(RTRIM([inse
rted].[Line7])),''),
[Addresses]. [PostalCode] = NULLIF(LTRIM(RTRIM([i
nserted].[PostalCode])),'')
FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
ELSE
RAISERROR('Address has since been modified.',16,1)
END
My tables are as follows:
CREATE TABLE [dbo].[Addresses](
[AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[CountryCode] [smallint] NOT NULL CONSTRAINT
[DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
[BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
(
[RowVersion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
CREATE TABLE [dbo].[Countries](
[CountryCode] [smallint] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line1Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine1Required] DEFAULT ((0)),
[Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
DEFAULT ((0)),
[Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
else (1) end,(0))),
[Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line2Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine2Required] DEFAULT ((0)),
[Line2ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line2ForceUppser]
DEFAULT ((0)),
[Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
else (1) end,(0))),
[Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line3Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine3Required] DEFAULT ((0)),
[Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
DEFAULT ((0)),
[Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
else (1) end,(0))),
[Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line4Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine4Required] DEFAULT ((0)),
[Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
DEFAULT ((0)),
[Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
else (1) end,(0))),
[Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line5Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine5Required] DEFAULT ((0)),
[Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
DEFAULT ((0)),
[Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
else (1) end,(0))),
[Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line6Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine6Required] DEFAULT ((0)),
[Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
DEFAULT ((0)),
[Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
else (1) end,(0))),
[Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line7Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine7Required] DEFAULT ((0)),
[Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
DEFAULT ((0)),
[Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
else (1) end,(0))),
[PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeRequired] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeRequired] DEFAULT ((1)),
[PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
[PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
then (0) else (1) end,(0))),
[PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
[PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
My CLR is:
using System;
using System.ComponentModel;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
namespace CoullByte.Eddie.SQL.Address.Triggers
{
/// <summary>
/// Address Trigger
/// </summary>
public class Address
{
/// <summary>
/// Prevents Duplicate and Validates the information being stored
/// </summary>
[SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
= "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
public static void DuplicateValidate()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlCommand command;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
#region Address Declaration
decimal AddressID;
byte[] RowVersion;
short CountryCode;
object BuildingName;
object BuildingNumber;
object Line1;
object Line2;
object Line3;
object Line4;
object Line5;
object Line6;
object Line7;
object PostalCode;
#endregion
#region Country Validation Declaration
bool Line1Required;
bool Line1Visible;
bool Line2Required;
bool Line2Visible;
bool Line3Required;
bool Line3Visible;
bool Line4Required;
bool Line4Visible;
bool Line5Required;
bool Line5Visible;
bool Line6Required;
bool Line6Visible;
bool Line7Required;
bool Line7Visible;
bool PostalCodeRequired;
bool PostalCodeVisible;
object PostalCodeRegExpression;
int PostalCodeMinimumCharacters;
int PostalCodeMaximumCharacters;
#endregion
try
{
switch (triggerContext.TriggerAction)
{
case TriggerAction.Insert:
break;
case TriggerAction.Update:
using (SqlConnection connection = new SqlConnection(@."context
connection=true"))
{
connection.Open();
#region Read Address
command = connection.CreateCommand();
command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
Line7, PostalCode FROM [inserted];";
reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw (new ApplicationException("No Rows"));
}
reader.Read();
if (reader == null)
{
throw (new ApplicationException("Reader"));
}
AddressID = (decimal)reader["AddressID"];
// RowVersion = (byte[])reader["RowVersion"];
CountryCode = (short)reader["CountryCode"];
BuildingName = reader["BuildingName"];
BuildingNumber = reader["BuildingNumber"];
Line1 = reader["Line1"];
Line2 = reader["Line2"];
Line3 = reader["Line3"];
Line4 = reader["Line4"];
Line5 = reader["Line5"];
Line6 = reader["Line6"];
Line7 = reader["Line7"];
PostalCode = reader["PostalCode"];
reader.Close();
#endregion
#region Read Country Validation
command = connection.CreateCommand();
command.CommandText =
@." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
command.CommandType = CommandType.StoredProcedure;
command.Parameters["@.CountryCode"].Value = CountryCode;
reader = command.ExecuteReader();
reader.Read();
Line1Required = (bool)reader["Line1Required"];
Line1Visible = (bool)reader["Line1Visible"];
Line2Required = (bool)reader["Line2Required"];
Line2Visible = (bool)reader["Line2Visible"];
Line3Required = (bool)reader["Line3Required"];
Line3Visible = (bool)reader["Line3Visible"];
Line4Required = (bool)reader["Line4Required"];
Line4Visible = (bool)reader["Line4Visible"];
Line5Required = (bool)reader["Line5Required"];
Line5Visible = (bool)reader["Line5Visible"];
Line6Required = (bool)reader["Line6Required"];
Line6Visible = (bool)reader["Line6Visible"];
Line7Required = (bool)reader["Line7Required"];
Line7Visible = (bool)reader["Line7Visible"];
PostalCodeRequired = (bool)reader["PostalCodeRequired"];
PostalCodeVisible = (bool)reader["PostalCodeVisible"];
PostalCodeRegExpression = reader["PostalCodeRegExpression"];
PostalCodeMinimumCharacters =
(int)reader["PostalCodeMinimumCharacters"];
PostalCodeMaximumCharacters =
(int)reader["PostalCodeMaximumCharacters"];
reader.Close();
#endregion
#region Validate Address
bool addressIsValid =
CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingName
,
BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7, PostalCode,
Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
Line6Required, Line6Visible, Line7Required, Line7Visible, PostalCodeRequired
,
PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
PostalCodeMaximumCharacters);
#endregion
command = connection.CreateCommand();
command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
[Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
[inserted].[RowVersion] = [Addresses].[RowVersion]";
command.CommandType = CommandType.Text;
int addressUnchanged = (int)command.ExecuteScalar();
if (addressUnchanged != 1)
{
throw (new ApplicationException("The address has since been changed
by someone else."));
}
if (!addressIsValid)
{
throw (new ApplicationException("The updated changes made do not
meet the countries address criteria."));
}
command = connection.CreateCommand();
command.CommandText = @."UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses].[BuildingName] =
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''), [Addresses].[BuildingNumber]
= NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''), [Addresses].[Line1] =
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
[inserted].[AddressID] = [Addresses].[AddressID]";
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
}
break;
}
}
catch (System.Data.SqlClient.SqlException sqlExp)
{
throw (sqlExp);
}
catch (System.NullReferenceException nullRef)
{
throw (new ApplicationException(nullRef.StackTrace));
}
catch (System.Exception exp)
{
throw (exp);
}
}
}
}Hi Paul,
What complicated validation? The IS NULL stuff etc... should be done in SQL
too.
Only use CLR if you are going to do something that cannot be better done
using set based methods, such examples would be if you had a post code look
up software to validate the post code exists, you'd probably do that using a
CLR UDF in a CHECK constraint.
Ditch the C# and stick with the SQL trigger - seriously!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:D04F7671-BB3B-42BB-A3C2-0ADC8810B708@.microsoft.com...
>I have created a CLR trigger that validates an address that has been
>enetered
> into our address database does not already exist and that it meets country
> critera for addressing.
> I have a RowVersion column (timestamp), I use this to determin if any
> changes have been made to a record in the table on update saving me having
> to
> write long SELECT statements.
> The problem I have is when I update a row in one instance of Manegement
> Studio then try to update in another instance no rows are returned in the
> inserted table. What I would like to have happen is a check to see if
> someone
> else has changed the row and if so fail the trigger. My SQL trigger works
> fine and does everything I need except all the complicated validation and
> is
> why I have choosen to write using the CLR.
> At present I have converted the following SQL to C#
> CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
> INSERT, UPDATE AS
> SET NOCOUNT ON;
> IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
> [inserted].[CountryCode] = [Countries].[CountryCode]
> WHERE
> NULLIF(LTRIM(RTRIM([Line1])),'') IS NULL AND [Line1Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line2])),'') IS NULL AND [Line2Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line3])),'') IS NULL AND [Line3Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line4])),'') IS NULL AND [Line4Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line5])),'') IS NULL AND [Line5Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line6])),'') IS NULL AND [Line6Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line7])),'') IS NULL AND [Line7Required] = 1 OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NULL AND [PostalCodeRequired] = 1
> OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
> LIKE [PostalCodeRegExpression] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMinimumCharacters] <> 0 AND LEN([PostalCode]) <
> [PostalCodeMinimumCharacters] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMaximumCharacters] <> 0 AND LEN([PostalCode]) >
> [PostalCodeMaximumCharacters])
> RAISERROR('Address does not meet the Countries address criteria.',16,1)
> ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
> ([inserted].[CountryCode] = [Addresses].[CountryCode] OR
> [inserted].[CountryCode] IS
> NULL AND [Addresses].[CountryCode] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') =
> [Addresses].[BuildingName] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') IS
> NULL AND [Addresses].[BuildingName] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') =
> [Addresses].[BuildingNumber] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') IS
> NULL AND [Addresses].[BuildingNumber] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses].[Line1] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') IS
> NULL AND [Addresses].[Line1] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses].[Line2] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),'') IS
> NULL AND [Addresses].[Line2] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses].[Line3] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') IS
> NULL AND [Addresses].[Line3] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses].[Line4] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') IS
> NULL AND [Addresses].[Line4] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses].[Line5] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') IS
> NULL AND [Addresses].[Line5] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses].[Line6] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') IS
> NULL AND [Addresses].[Line6] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses].[Line7] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') IS
> NULL AND [Addresses].[Line7] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') =
> [Addresses].[PostalCode] OR
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') IS
> NULL AND [Addresses].[PostalCode] IS NULL))
> RAISERROR('Address already exists.',16,1)
> ELSE
> IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
> BEGIN
> INSERT INTO Addresses (
> [Addresses].[CountryCode],
> [Addresses].[BuildingName],
> [Addresses].[BuildingNumber],
> [Addresses].[Line1],
> [Addresses].[Line2],
> [Addresses].[Line3],
> [Addresses].[Line4],
> [Addresses].[Line5],
> [Addresses].[Line6],
> [Addresses].[Line7],
> [Addresses].[PostalCode]
> )
> SELECT
> [inserted].[CountryCode],
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted]
> END
> ELSE -- Update Address
> BEGIN
> IF EXISTS (
> SELECT
> NULL
> FROM
> [inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
> [Addresses].[AddressID] AND [inserted].[RowVersion] =
> [Addresses].[RowVersion]
> )
> UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] = NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> [Addresses].[Line2] = NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> [Addresses].[Line3] = NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> [Addresses].[Line4] = NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> [Addresses].[Line5] = NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> [Addresses].[Line6] = NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> [Addresses].[Line7] = NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
> ELSE
> RAISERROR('Address has since been modified.',16,1)
> END
> My tables are as follows:
> CREATE TABLE [dbo].[Addresses](
> [AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [CountryCode] [smallint] NOT NULL CONSTRAINT
> [DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
> [BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
> [Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
> (
> [AddressID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
> CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
> (
> [RowVersion] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> CREATE TABLE [dbo].[Countries](
> [CountryCode] [smallint] NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
> [Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line1Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine1Required] DEFAULT ((0)),
> [Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
> DEFAULT ((0)),
> [Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
> else (1) end,(0))),
> [Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line2Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine2Required] DEFAULT ((0)),
> [Line2ForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_Line2ForceUppser]
> DEFAULT ((0)),
> [Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
> else (1) end,(0))),
> [Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line3Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine3Required] DEFAULT ((0)),
> [Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
> DEFAULT ((0)),
> [Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
> else (1) end,(0))),
> [Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line4Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine4Required] DEFAULT ((0)),
> [Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
> DEFAULT ((0)),
> [Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
> else (1) end,(0))),
> [Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line5Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine5Required] DEFAULT ((0)),
> [Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
> DEFAULT ((0)),
> [Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
> else (1) end,(0))),
> [Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line6Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine6Required] DEFAULT ((0)),
> [Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
> DEFAULT ((0)),
> [Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
> else (1) end,(0))),
> [Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line7Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine7Required] DEFAULT ((0)),
> [Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
> DEFAULT ((0)),
> [Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
> else (1) end,(0))),
> [PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeRequired] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeRequired] DEFAULT ((1)),
> [PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
> [PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
> then (0) else (1) end,(0))),
> [PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS
> NULL,
> [PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
> [PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
> CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
> (
> [CountryCode] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> My CLR is:
> using System;
> using System.ComponentModel;
> using System.Collections.Generic;
> using System.Diagnostics;
> using System.Text;
> using System.Data;
> using System.Data.Sql;
> using Microsoft.SqlServer.Server;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Xml;
> namespace CoullByte.Eddie.SQL.Address.Triggers
> {
> /// <summary>
> /// Address Trigger
> /// </summary>
> public class Address
> {
> /// <summary>
> /// Prevents Duplicate and Validates the information being stored
> /// </summary>
> [SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
> = "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
> public static void DuplicateValidate()
> {
> if (!SqlContext.IsAvailable)
> {
> throw (new NotSupportedException());
> }
>
> SqlTriggerContext triggerContext = SqlContext.TriggerContext;
> SqlCommand command;
> SqlPipe pipe = SqlContext.Pipe;
> SqlDataReader reader;
> #region Address Declaration
> decimal AddressID;
> byte[] RowVersion;
> short CountryCode;
> object BuildingName;
> object BuildingNumber;
> object Line1;
> object Line2;
> object Line3;
> object Line4;
> object Line5;
> object Line6;
> object Line7;
> object PostalCode;
> #endregion
> #region Country Validation Declaration
> bool Line1Required;
> bool Line1Visible;
> bool Line2Required;
> bool Line2Visible;
> bool Line3Required;
> bool Line3Visible;
> bool Line4Required;
> bool Line4Visible;
> bool Line5Required;
> bool Line5Visible;
> bool Line6Required;
> bool Line6Visible;
> bool Line7Required;
> bool Line7Visible;
> bool PostalCodeRequired;
> bool PostalCodeVisible;
> object PostalCodeRegExpression;
> int PostalCodeMinimumCharacters;
> int PostalCodeMaximumCharacters;
> #endregion
> try
> {
> switch (triggerContext.TriggerAction)
> {
> case TriggerAction.Insert:
> break;
> case TriggerAction.Update:
> using (SqlConnection connection = new SqlConnection(@."context
> connection=true"))
> {
> connection.Open();
> #region Read Address
> command = connection.CreateCommand();
> command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
> BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
> Line7, PostalCode FROM [inserted];";
> reader = command.ExecuteReader();
> if (!reader.HasRows)
> {
> throw (new ApplicationException("No Rows"));
> }
> reader.Read();
> if (reader == null)
> {
> throw (new ApplicationException("Reader"));
> }
> AddressID = (decimal)reader["AddressID"];
> // RowVersion = (byte[])reader["RowVersion"];
> CountryCode = (short)reader["CountryCode"];
> BuildingName = reader["BuildingName"];
> BuildingNumber = reader["BuildingNumber"];
> Line1 = reader["Line1"];
> Line2 = reader["Line2"];
> Line3 = reader["Line3"];
> Line4 = reader["Line4"];
> Line5 = reader["Line5"];
> Line6 = reader["Line6"];
> Line7 = reader["Line7"];
> PostalCode = reader["PostalCode"];
> reader.Close();
> #endregion
> #region Read Country Validation
> command = connection.CreateCommand();
> command.CommandText =
> @." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
> command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
> command.CommandType = CommandType.StoredProcedure;
> command.Parameters["@.CountryCode"].Value = CountryCode;
> reader = command.ExecuteReader();
> reader.Read();
> Line1Required = (bool)reader["Line1Required"];
> Line1Visible = (bool)reader["Line1Visible"];
> Line2Required = (bool)reader["Line2Required"];
> Line2Visible = (bool)reader["Line2Visible"];
> Line3Required = (bool)reader["Line3Required"];
> Line3Visible = (bool)reader["Line3Visible"];
> Line4Required = (bool)reader["Line4Required"];
> Line4Visible = (bool)reader["Line4Visible"];
> Line5Required = (bool)reader["Line5Required"];
> Line5Visible = (bool)reader["Line5Visible"];
> Line6Required = (bool)reader["Line6Required"];
> Line6Visible = (bool)reader["Line6Visible"];
> Line7Required = (bool)reader["Line7Required"];
> Line7Visible = (bool)reader["Line7Visible"];
> PostalCodeRequired = (bool)reader["PostalCodeRequired"];
> PostalCodeVisible = (bool)reader["PostalCodeVisible"];
> PostalCodeRegExpression = reader["PostalCodeRegExpression"];
> PostalCodeMinimumCharacters =
> (int)reader["PostalCodeMinimumCharacters"];
> PostalCodeMaximumCharacters =
> (int)reader["PostalCodeMaximumCharacters"];
> reader.Close();
> #endregion
> #region Validate Address
> bool addressIsValid =
> CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingNa
me,
> BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7,
> PostalCode,
> Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
> Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
> Line6Required, Line6Visible, Line7Required, Line7Visible,
> PostalCodeRequired,
> PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
> PostalCodeMaximumCharacters);
> #endregion
> command = connection.CreateCommand();
> command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
> [Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
> [inserted].[RowVersion] = [Addresses].[RowVersion]";
> command.CommandType = CommandType.Text;
> int addressUnchanged = (int)command.ExecuteScalar();
> if (addressUnchanged != 1)
> {
> throw (new ApplicationException("The address has since been changed
> by someone else."));
> }
> if (!addressIsValid)
> {
> throw (new ApplicationException("The updated changes made do not
> meet the countries address criteria."));
> }
> command = connection.CreateCommand();
> command.CommandText = @."UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber]
> = NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] =
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
> [inserted].[AddressID] = [Addresses].[AddressID]";
> pipe.Send(command.CommandText);
> command.ExecuteNonQuery();
> }
> break;
> }
> }
> catch (System.Data.SqlClient.SqlException sqlExp)
> {
> throw (sqlExp);
> }
> catch (System.NullReferenceException nullRef)
> {
> throw (new ApplicationException(nullRef.StackTrace));
> }
> catch (System.Exception exp)
> {
> throw (exp);
> }
> }
> }
> }
>|||In my orginal post I said "...and that it meets country
critera for addressing...". The CLR is the only way I can validate an
address is enetered that meets the countries addressing criteria. SQL
triggers do not allow me to do this and is why I chose to use c#. All
countries are stored in the Countries table and each country have diffrent
ways of handling addresses such as that between UK and US. US use Zip Code
and UK use Post Code for example and US Zip codes are not the same format as
UK Post Codes. In the address table I have columns such as Building Name,
Building Number, Line 1, Line 2 - Line 7 then Postal Code. When using our ow
n
software changing country code changes labels and validation requirements on
the Windows Form or on the Web Forms. The c# trigger validates based on the
country and forces NULL into the columns where their is empty strings etc.
Hope this help to explain what I am trying to acheive.|||Based on the code snippet you post, you might have some design issues as
well. Can you post the table structures, a few sample data along with the
address validation rules you have?
In general, Tony's last statement in his response would be a suitable
advice.
Anith|||My Address Validation class is as follows, other stuff already posted and
table scripts also posted:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
namespace CoullByte.Eddie.SQL.Address.Functions
{
/// <summary>
/// Address Validation Class
/// </summary>
public class Validation
{
#region SQL Context
/// <summary>
/// Validate if running under SQL
/// </summary>
private static void ValidateSQLContext()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
}
#endregion
#region Address Is Valid
/// <summary>
/// Address Data Validation
/// </summary>
/// <param name="BuildingName">Building Name</param>
/// <param name="BuildingNumber">Building Number</param>
/// <param name="Line1">Line 1</param>
/// <param name="Line2">Line 2</param>
/// <param name="Line3">Line 3</param>
/// <param name="Line4">Line 4</param>
/// <param name="Line5">Line 5</param>
/// <param name="Line6">Line 6</param>
/// <param name="Line7">Line 7</param>
/// <param name="PostalCode">Postal Code</param>
/// <param name="Line1Required">Line 1 Required?</param>
/// <param name="Line1Visible">Line 1 Visible to User?</param>
/// <param name="Line2Required">Line 2 Required?</param>
/// <param name="Line2Visible">Line 2 Visible to User?</param>
/// <param name="Line3Required">Line 3 Required?</param>
/// <param name="Line3Visible">Line 3 Visible to User?</param>
/// <param name="Line4Required">Line 4 Required?</param>
/// <param name="Line4Visible">Line 4 Visible to User?</param>
/// <param name="Line5Required">Line 5 Required?</param>
/// <param name="Line5Visible">Line 5 Visible to User?</param>
/// <param name="Line6Required">Line 6 Required?</param>
/// <param name="Line6Visible">Line 6 Visible to User?</param>
/// <param name="Line7Required">Line 7 Required?</param>
/// <param name="Line7Visible">Line 7 Visible to User?</param>
/// <param name="PostalCodeRequired">Postal Code Required?</param>
/// <param name="PostalCodeVisible">Postal Code Visible?</param>
/// <param name="PostalCodeRegExpression">Postal Code Regular
Expression</param>
/// <param name="PostalCodeMinimumCharacters">Postal Code Minimum
Characters</param>
/// <param name="PostalCodeMaximumCharacters">Postal Code Maximum
Characters</param>
/// <returns>Validation Result</returns>
public static bool AddressIsValid(object BuildingName, object
BuildingNumber, object Line1, object Line2, object Line3, object Line4,
object Line5, object Line6, object Line7, object PostalCode, bool
Line1Required, bool Line1Visible, bool Line2Required, bool Line2Visible, boo
l
Line3Required, bool Line3Visible, bool Line4Required, bool Line4Visible, boo
l
Line5Required, bool Line5Visible, bool Line6Required, bool Line6Visible, boo
l
Line7Required, bool Line7Visible, bool PostalCodeRequired, bool
PostalCodeVisible, object PostalCodeRegExpression, int
PostalCodeMinimumCharacters, int PostalCodeMaximumCharacters)
{
return Line1IsValid(Line1Required, Line1Visible, Line1) &&
Line2IsValid(Line2Required, Line2Visible, Line2) &&
Line3IsValid(Line3Required, Line3Visible, Line3) &&
Line4IsValid(Line4Required, Line4Visible, Line4) &&
Line5IsValid(Line5Required, Line5Visible, Line5) &&
Line6IsValid(Line6Required, Line6Visible, Line6) &&
Line7IsValid(Line7Required, Line7Visible, Line7) &&
PostalCodeIsValid(PostalCodeRequired, PostalCodeVisible,
PostalCodeMinimumCharacters, PostalCodeMaximumCharacters,
PostalCodeRegExpression, PostalCode);
}
#endregion
#region Line 1 Data Validation
/// <summary>
/// Line 1 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line1IsValid (bool Line1Required, bool Line1Visible,
object Line1Data)
{
ValidateSQLContext();
if (!Line1Visible)
{
return true;
}
else
{
return ((!Line1Required) || (Line1Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line1Data)));
}
}
#endregion
#region Line 2 Data Validation
/// <summary>
/// Line 2 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line2IsValid(bool Line2Required, bool Line2Visible,
object Line2Data)
{
ValidateSQLContext();
if (!Line2Visible)
{
return true;
}
else
{
return ((!Line2Required) || (Line2Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line2Data)));
}
}
#endregion
#region Line 3 Data Validation
/// <summary>
/// Line 3 Data Validation
/// </summary>
/// <param name="Line3Required">Data Required?</param>
/// <param name="Line3Visible">Visible to User?</param>
/// <param name="Line3Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line3IsValid(bool Line3Required, bool Line3Visible,
object Line3Data)
{
ValidateSQLContext();
if (!Line3Visible)
{
return true;
}
else
{
return ((!Line3Required) || (Line3Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line3Data)));
}
}
#endregion
#region Line 4 Data Validation
/// <summary>
/// Line 4 Data Validation
/// </summary>
/// <param name="Line4Required">Data Required?</param>
/// <param name="Line4Visible">Visible to User?</param>
/// <param name="Line4Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line4IsValid(bool Line4Required, bool Line4Visible,
object Line4Data)
{
ValidateSQLContext();
if (!Line4Visible)
{
return true;
}
else
{
return ((!Line4Required) || (Line4Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line4Data)));
}
}
#endregion
#region Line 5 Data Validation
/// <summary>
/// Line 5 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line5IsValid(bool Line5Required, bool Line5Visible,
object Line5Data)
{
ValidateSQLContext();
if (!Line5Visible)
{
return true;
}
else
{
return ((!Line5Required) || (Line5Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line5Data)));
}
}
#endregion
#region Line 6 Data Validation
/// <summary>
/// Line 6 Data Validation
/// </summary>
/// <param name="Line6Required">Data Required?</param>
/// <param name="Line6Visible">Visible to User?</param>
/// <param name="Line6Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line6IsValid(bool Line6Required, bool Line6Visible,
object Line6Data)
{
if (!Line6Visible)
{
return true;
}
else
{
return ((!Line6Required) || (Line6Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line6Data)));
}
}
#endregion
#region Line 7 Data Validation
/// <summary>
/// Line 7 Data Validation
/// </summary>
/// <param name="Line7Required">Data Required?</param>
/// <param name="Line7Visible">Visible to User?</param>
/// <param name="Line7Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line7IsValid(bool Line7Required, bool Line7Visible,
object Line7Data)
{
if (!Line7Visible)
{
return true;
}
else
{
return ((!Line7Required) || (Line7Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line7Data)));
}
}
#endregion
#region Postal Code Validation
/// <summary>
/// Postal Code Validation
/// </summary>
/// <param name="PostalCodeRequired">Data Required</param>
/// <param name="PostalCodeVisible">Visible to User?</param>
/// <param name="PostalCodeMinimumCharacters">Minimum Characters
Required?</param>
/// <param name="PostalCodeMaximumCharacters">Maximum Characters
Required?</param>
/// <param name="PostalCodeRegExpression">Regular Expression for
Validating Data</param>
/// <param name="PostalCodeData">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool PostalCodeIsValid(bool PostalCodeRequired, bool
PostalCodeVisible, int PostalCodeMinimumCharacters, int
PostalCodeMaximumCharacters, object PostalCodeRegExpression, object
PostalCodeData)
{
string postalCodeData = string.Empty;
string postalCodeRegExpression = string.Empty;
if (PostalCodeRegExpression != null && PostalCodeRegExpression !=
DBNull.Value && PostalCodeRegExpression is string)
{
postalCodeRegExpression = Convert.ToString(PostalCodeRegExpression);
}
if (PostalCodeData != null && PostalCodeData != DBNull.Value &&
PostalCodeData is string)
{
postalCodeData = Convert.ToString(PostalCodeData);
}
bool lengthIsValid =
(
(
PostalCodeMinimumCharacters == 0
) ||
(
PostalCodeMinimumCharacters != 0 &&
postalCodeData.Length >= PostalCodeMinimumCharacters
) &&
(
(
PostalCodeMaximumCharacters == 0
) ||
(
PostalCodeMaximumCharacters != 0 &&
postalCodeData.Length <= PostalCodeMaximumCharacters
)
)
);
bool regExpIsValid =
(
(
postalCodeRegExpression == string.Empty
) ||
(
CoullByte.Eddie.SQL.Functions.Data.IsMeaningless(PostalCodeRegExpression)
) ||
(
postalCodeRegExpression != string.Empty &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(postalCodeRegExpression) &&
System.Text.RegularExpressions.Regex.IsMatch(postalCodeData,
postalCodeRegExpression)
)
);
if (PostalCodeRequired)
{
return (CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(PostalCodeData)
&& lengthIsValid && regExpIsValid);
}
else
{
return (lengthIsValid && regExpIsValid);
}
}
#endregion
}
}
a sample row for the Countries table is as follows:
INSERT INTO [Eddie].[dbo].[Countries]
([CountryCode]
,[Country]
,[Line1Label]
,[Line1ToolTip]
,[Line1Required]
,[Line1ForceUpper]
,[Line2Label]
,[Line2ToolTip]
,[Line2Required]
,[Line2ForceUpper]
,[Line3Label]
,[Line3ToolTip]
,[Line3Required]
,[Line3ForceUpper]
,[Line4Label]
,[Line4ToolTip]
,[Line4Required]
,[Line4ForceUpper]
,[Line5Label]
,[Line5ToolTip]
,[Line5Required]
,[Line5ForceUpper]
,[Line6Label]
,[Line6ToolTip]
,[Line6Required]
,[Line6ForceUpper]
,[Line7Label]
,[Line7ToolTip]
,[Line7Required]
,[Line7ForceUpper]
,[PostalCodeLabel]
,[PostalCodeToolTip]
,[PostalCodeRequired]
,[PostalCodeForceUpper]
,[PostalCodeRegExpression]
,[PostalCodeMinimumCharacters]
,[PostalCodeMaximumCharacters])
VALUES
(44
,'United Kingdom'
,'Thoroughfare'
,NULL
,0
,0
,'Locality'
,NULL
,0
,0
,'Post Town'
,NULL
,1
,1
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,'Post Code'
,NULL
,1
,1
,'(GIR0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A
-Z-[IJZ]][0-9][ABEHMNPRVWXY]))))
[0-9][A-Z-[CIKMOV]]{2})'
,3
,8)
A sample address for the Addresses table is:
INSERT INTO [Eddie].[dbo].[Addresses]
([CountryCode]
,[BuildingName]
,[BuildingNumber]
,[Line1]
,[Line2]
,[Line3]
,[Line4]
,[Line5]
,[Line6]
,[Line7]
,[PostalCode])
VALUES
(44
,NULL
,NULL
,'Queens Walk'
,NULL
,'READING'
,NULL
,NULL
,NULL
,NULL
,'RG1 7PT')
Subscribe to:
Posts (Atom)