Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
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
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
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
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
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
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
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
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

referencing fields in the code window

Hi, how do I reference a report field in a custom function which is written
in the code window (Report Properties/code tab)? i.e.
Function Something
If Fields!FIELD1_Company.Value = Nothing Then
Return ""
End If
End Function
This example will not work but it's similar to my needs
Thanks
NPSend the field value as a parameter to your function. i.e. re-write the
function as:
Function SomeFunction(byVal MyFieldValue as datatype) as datatype
if MyFieldValue is nothing
...
return ...
End Function
In your report expression, just call the function:
=Code.SomeFunction(Fields!MyField.Value)
Charles Kangai, MCT, MCDBA
"slk55guy" wrote:
> Hi, how do I reference a report field in a custom function which is written
> in the code window (Report Properties/code tab)? i.e.
> Function Something
> If Fields!FIELD1_Company.Value = Nothing Then
> Return ""
> End If
> End Function
> This example will not work but it's similar to my needs
> Thanks
> NP
>|||Thanks Charles, by the way how are you? I was on one of your DTS courses a
couple of years ago in the city. Small world eh?
I think this will work but there is another post (converting crystal code) I
put up here and basically I'm trying to get the same functionality as I would
in a Crystal function.
Happy Christmas
NP
"Charles Kangai" wrote:
> Send the field value as a parameter to your function. i.e. re-write the
> function as:
> Function SomeFunction(byVal MyFieldValue as datatype) as datatype
> if MyFieldValue is nothing
> ...
> return ...
> End Function
> In your report expression, just call the function:
> =Code.SomeFunction(Fields!MyField.Value)
> Charles Kangai, MCT, MCDBA
>
> "slk55guy" wrote:
> > Hi, how do I reference a report field in a custom function which is written
> > in the code window (Report Properties/code tab)? i.e.
> >
> > Function Something
> > If Fields!FIELD1_Company.Value = Nothing Then
> > Return ""
> > End If
> > End Function
> >
> > This example will not work but it's similar to my needs
> >
> > Thanks
> >
> > NP
> >|||Hi,
It should work. I am using something similar myself.
I did a demonstration and some conversations for a customer earlier this
week. The developers are all Crystal users, and they were so impressed with
Reporting Services that they are going to migrate all their sites in UK,
Italy, USA, and Denmark to RS within the next few weeks.
Great to come across you again - we will be running a Reporting Services
course from the end of Feb. I am the author. Check with Learning Tree.
Merry Christmas!
Charles
"slk55guy" wrote:
> Thanks Charles, by the way how are you? I was on one of your DTS courses a
> couple of years ago in the city. Small world eh?
> I think this will work but there is another post (converting crystal code) I
> put up here and basically I'm trying to get the same functionality as I would
> in a Crystal function.
> Happy Christmas
> NP
> "Charles Kangai" wrote:
> > Send the field value as a parameter to your function. i.e. re-write the
> > function as:
> >
> > Function SomeFunction(byVal MyFieldValue as datatype) as datatype
> > if MyFieldValue is nothing
> > ...
> > return ...
> > End Function
> >
> > In your report expression, just call the function:
> > =Code.SomeFunction(Fields!MyField.Value)
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> > "slk55guy" wrote:
> >
> > > Hi, how do I reference a report field in a custom function which is written
> > > in the code window (Report Properties/code tab)? i.e.
> > >
> > > Function Something
> > > If Fields!FIELD1_Company.Value = Nothing Then
> > > Return ""
> > > End If
> > > End Function
> > >
> > > This example will not work but it's similar to my needs
> > >
> > > Thanks
> > >
> > > NP
> > >

Reference to a non-shared member requires an object reference

Hi,
I tried to use a custom assembly, written in C#. I received the following
error:
"[BC30469] Reference to a non-shared member requires an object reference..."
Here's the detail info for the custom assembly:
*I have 2 C# files. I did not write the code. So my C# knowledge is
limited. I removed some of the code within the methods because it gets very
long. Here is the code:
--[IConvertor.cs]--
using System;
namespace sbu.convertor
{
public interface IConvertor
{
string convert(string label, string direction, string val);
string convertByUnit(string before, string after, string val, string
direction, string group, string format);
string convertByUnit2(string before, string after, string val, string
direction);
string getUnit(string usUnit, string saUnit, string direction, string
group);
string getUnit2(string usUnit, string saUnit, string direction);
}
}
--[Convertor.cs]--
using System;
using System.IO;
using System.Xml;
using System.Globalization;
namespace sbu.convertor
{
public class Convertor:IConvertor
{
private static Convertor instance;
public static IConvertor getInstance()
{
if (instance==null)
{
instance = new Convertor();
}
return instance;
}
private XmlDocument doc;
private string docSource = null;
private NumberFormatInfo format;
public Convertor()
{
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//pdu dev box
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//webdevtest
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
doc = new XmlDocument();
doc.Load(docSource);
format = new NumberFormatInfo();
format.NumberDecimalDigits = 3;
format.NumberGroupSizes = new int[] {3};
format.NumberGroupSeparator = ",";
format.NumberDecimalSeparator = ".";
}
public string convert(string label, string direction, string val)
{
return (double.Parse(val)*getRatio(label,direction)).ToString();
//return "0000000000";
}
private double getRatio(string label, string direction)
{
XmlNode node = doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
string valStr = node.InnerText;
return double.Parse(valStr);
}
public string convertByUnit(string usUnit, string saUnit, string val,
string direction, string group, string format)
{
////
}
public string convertByUnit2(string before, string after, string val,
string direction)
{
////
}
public string getUnit(string usUnit, string saUnit, string direction,
string group)
{
///
}
public string getUnit2(string usUnit, string saUnit, string direction)
{
///
}
public string convertTemp(string val, string direction)
{
///
}
}
}
* Here is how I complied it:
csc /t:library /out:bin/sbu_convertor.dll *.cs
Thanks in advance. JLI found a solution. I simply added the class and instance names in the
Reference tab. That fixed it.
"JL" wrote:
> Hi,
> I tried to use a custom assembly, written in C#. I received the following
> error:
> "[BC30469] Reference to a non-shared member requires an object reference..."
>
> Here's the detail info for the custom assembly:
> *I have 2 C# files. I did not write the code. So my C# knowledge is
> limited. I removed some of the code within the methods because it gets very
> long. Here is the code:
> --[IConvertor.cs]--
> using System;
> namespace sbu.convertor
> {
> public interface IConvertor
> {
> string convert(string label, string direction, string val);
> string convertByUnit(string before, string after, string val, string
> direction, string group, string format);
> string convertByUnit2(string before, string after, string val, string
> direction);
> string getUnit(string usUnit, string saUnit, string direction, string
> group);
> string getUnit2(string usUnit, string saUnit, string direction);
> }
> }
>
> --[Convertor.cs]--
> using System;
> using System.IO;
> using System.Xml;
> using System.Globalization;
> namespace sbu.convertor
> {
> public class Convertor:IConvertor
> {
> private static Convertor instance;
> public static IConvertor getInstance()
> {
> if (instance==null)
> {
> instance = new Convertor();
> }
> return instance;
> }
> private XmlDocument doc;
> private string docSource = null;
> private NumberFormatInfo format;
> public Convertor()
> {
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //pdu dev box
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //webdevtest
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> doc = new XmlDocument();
> doc.Load(docSource);
> format = new NumberFormatInfo();
> format.NumberDecimalDigits = 3;
> format.NumberGroupSizes = new int[] {3};
> format.NumberGroupSeparator = ",";
> format.NumberDecimalSeparator = ".";
> }
> public string convert(string label, string direction, string val)
> {
> return (double.Parse(val)*getRatio(label,direction)).ToString();
> //return "0000000000";
> }
> private double getRatio(string label, string direction)
> {
> XmlNode node => doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
> string valStr = node.InnerText;
> return double.Parse(valStr);
> }
> public string convertByUnit(string usUnit, string saUnit, string val,
> string direction, string group, string format)
> {
> ////
> }
> public string convertByUnit2(string before, string after, string val,
> string direction)
> {
> ////
> }
> public string getUnit(string usUnit, string saUnit, string direction,
> string group)
> {
>
> ///
> }
> public string getUnit2(string usUnit, string saUnit, string direction)
> {
> ///
> }
> public string convertTemp(string val, string direction)
> {
> ///
> }
> }
> }
>
> * Here is how I complied it:
> csc /t:library /out:bin/sbu_convertor.dll *.cs
>
> Thanks in advance. JL