Walkthrough 13 – Custom types

Document Sample
Walkthrough 13 – Custom types Powered By Docstoc
					Walkthrough 13 : Custom types

Walkthrough 13: Custom types
The Objective
The objective of this walkthrough is to introduce the data type philosophy of Nolics.net and teach you how to extend it using custom types and SQL Server 2005 UDTs. The topics include:    Data types of Nolics.net How to build a custom data type How to build a custom data type utilizing SQL Server 2005

Requirements
Before you go through this walkthrough, you must have    SQL Server 2005 (or SQL Server Express) Nolics.net 2005 installed A database as described in Walkthrough 1

Steps
1.Background
In addition to database rules (see WT 12), custom data types are the best way to extend the Nolics.net domain. In this example, we are constructing a custom data type that is used to store the result of a calculation. The result can be overridden by the user.

2.Create a new project
In this exercise we'll start with an empty Nolics.net Database/Forms application. Create a new solution and name it WT13_CustomTypes. Create a new project, name the project as WT13_CustomTypesGUI. Add the ClassLibrary project WT13_CalcDouble to the solution. Add a reference to the NN4_engine.dll to this project. In Nolics.net, all the dbclass properties will contain a single class inherited from the FieldValueAttribute class. The purpose of this class is to:  Introduce the attributes that can be used in the database class definitions  Generate the property

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

1 / 16

Walkthrough 13 : Custom types

 Check that the property has been utilized in a correct manner  Create a suitable type converter for the class, used for displaying the value to the user  Convert the database data into format suitable for the property First we'll add the CalcDoubleAttribute class to the WT13_CalcDouble project. Edit the class file and add the using statements:
C# using Nolics.ORMapper.CodeGen; using System.CodeDom; using System.ComponentModel;

The add a class to the file:
C# [AttributeUsage(AttributeTargets.Property)] public class CalcDoubleValueAttribute: DoubleValueAttribute { }

Generally, you should not construct your custom type from the atomary units. Instead, use a suitable existing class as a base class. As we are now constructing a type similar to the DoubleValue class, the DoubleValueAttribute class is the most suitable. Next we'll add a database class that will utilize the attribute. Add the following class description to the database1.dbclass in the GUI project:
type calcDouble = "WT13_CalcDouble.CalcDoubleValueAttribute"; dbclass CalcTest { primary key long ID[AutoGenID = true]; double Value1; double Value2; calcDouble Result; } query CalcTestQuery for CalcTest { }

In the GUI project, remember to add a reference to the WT13_CalcDouble project. Use the connection editor to define a database connection.

Run the upgrade tool to update the database schema.

Now we have a custom type, calcDouble. However, this class functions exactly the same as compared to the normal DoubleValue.

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

2 / 16

Walkthrough 13 : Custom types

3.Modifying the generator
Next we'll change the CalcDouble value so that the value will always be calculated according to the formula we provide. For this functionality, we need to add a new attribute. The value of this attribute will be the method that will evaluate the chosen statement.
C# [AttributeUsage(AttributeTargets.Property)] public class CalcDoubleValueAttribute: DoubleValueAttribute { public string CalcMethod { get { return calcMethod; } set { calcMethod = value; } } private string calcMethod; }

At this point, you should build the application.

You may then set the attribute according to the following example:
type calcDouble = "WT13_CalcDouble.CalcDoubleValueAttribute"; dbclass CalcTest { primary key long ID[AutoGenID = true]; double Value1; double Value2; calcDouble Result[CalcMethod = "CalcResult"]; } query CalcTestQuery for CalcTest { }

Next, construct the partial class CalcTest and add the body for the method CalcResult. This will be added to the WT13_CustomTypesGUI project into the Database1.cs file:
C#

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

3 / 16

Walkthrough 13 : Custom types

partial class CalcTest { private double CalcResult() { return Value1 * Value2; } }

If we were to test the application at this point, nothing would happen. The attribute and the method must be connected together. For this, we need to modify the generator of the property. Add the following code to the CalcDoubleValueAttribute class: Note: all of the following code goes inside the CalcDoubleValueAttribute class definition

C# public override Nolics.ORMapper.CodeGen.PropertyGenBase PropertyGenerator() { return new CalcDoubleGenerator(this); } protected class CalcDoubleGenerator: Nolics.ORMapper.CodeGen.PropertyGenVT { public CalcDoubleGenerator(CalcDoubleValueAttribute cdva) : base(cdva, typeof(double), false) { this.cdva = cdva; } private CalcDoubleValueAttribute cdva; protected override void GenGetSet(Nolics.ORMapper.CodeGen.ViewGenBase genClass, System.CodeDom.CodeMemberProperty prop) { prop.GetStatements.Add( new CodeAssignStatement( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), MakeName("_fld") ), new CodeMethodInvokeExpression( new CodeThisReferenceExpression(), cdva.CalcMethod) )); prop.GetStatements.Add( new CodeMethodReturnStatement( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), MakeName("_fld")) ) ); } }

As a base we are using the general generator intended for handling all ValueType classes. We override the GenGetSet part and construct a Get method of our own. Obviously, there is no reason to implement the Set method.

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

4 / 16

Walkthrough 13 : Custom types

At this point, you should build the application.

After you have successfully built the application, you should save the Database1.dbclass file in order for the new generator to be usable. After you do the save operation, the generated code will contain the following code in Database1.Designer.cs file:
C# [WT13_CalcDouble.CalcDoubleValueAttribute(CalcMethod="CalcResult")] public virtual double Result { get { this._fld__Result = this.CalcResult(); return this._fld__Result; } }

Now we can test the class by making a simple user interface for it.

Remember to set the ActiveTransaction property of calcTestQuery1!

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

5 / 16

Walkthrough 13 : Custom types

Note! The fields in Grid do not update until you move the mouse over the fields or navigate to the field. This can be circumvented by refreshing the data grid manually as follows:

C# private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e) { calcTestQuery1.FireListChanged(); }

You may also check the database to see that the values have really been saved to the database.

4.Type conversion
Next we’ll change a property so that we have a structure which includes both the value itself and the information whether the value is a calculated or a user entered value. Let us add a new class to the CalcDoubleValueType file:
C# namespace WT13_CalcDouble { [AttributeUsage(AttributeTargets.Property)] public class CalcDoubleValueAttribute : DoubleValueAttribute { public struct CalcValue { public double Value; public bool Manual; }

Then we’ll change the existing generator so that the property type is the newly created class:
C# protected class CalcDoubleGenerator: Nolics.ORMapper.CodeGen.PropertyGenVT { public CalcDoubleGenerator(CalcDoubleValueAttribute cdva) : base(cdva, typeof(CalcValue), false) { this.cdva = cdva; }

In addition to this, we have to edit the value as it is retrieved from the database. Also, we have to edit the value as it is stored into the database. For purposes like this, we have PropertyGenVT generator which will invoke the ValueFromDS and ValueToDS methods as data is loaded or saved. In the ValueFromDS method you must prepare that the database driver will not return a type that you’d want. For example, in some SQL databases all the numbers will be of the decimal type. In this example, we will first invoke DoubleValueAttribute method which includes all the general type conversions.

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

6 / 16

Walkthrough 13 : Custom types

Add the following methods to the CalcDoubleValueAttribute class:
C# [AttributeUsage(AttributeTargets.Property)] public class CalcDoubleValueAttribute : DoubleValueAttribute { public static new CalcValue ValueFromDS(object value) { CalcValue cv = new CalcValue(); cv.Value = DoubleValueAttribute.ValueFromDS(value); return cv; } public static object ValueToDS(CalcValue value) { return value.Value; }

The most difficult part of this is the CodeDom generator change. The utilization of the CodeDom model and constructing your own generators is somewhat challenging. If you have a lot of needs related to generator extensions of your own, you should buy the Enterprise license. With the Enterprise license, you’ll get the code for existing generators and you can use the existing code when building generators of your own. The generator now has the Set part so that the calculated value can be set. After the changes, the generator will look like follows. The following will go inside the CalcDoubleGenerator class:
C# protected override void GenGetSet(Nolics.ORMapper.CodeGen.ViewGenBase genClass, System.CodeDom.CodeMemberProperty prop) { CodeConditionStatement ccs = new CodeConditionStatement(); ccs.Condition = new CodeBinaryOperatorExpression( new CodeFieldReferenceExpression( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), MakeName("_fld")), "Manual"), CodeBinaryOperatorType.ValueEquality, new CodePrimitiveExpression(false)); prop.GetStatements.Add(ccs); ccs.TrueStatements.Add( new CodeAssignStatement( new CodeFieldReferenceExpression( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), MakeName("_fld") ), "Value"),

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

7 / 16

Walkthrough 13 : Custom types

new CodeMethodInvokeExpression( new CodeThisReferenceExpression(), cdva.CalcMethod) )); prop.GetStatements.Add( new CodeMethodReturnStatement( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), MakeName("_fld")) ) ); prop.SetStatements.Add( new CodeAssignStatement( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), MakeName("_fld")), new CodePropertySetValueReferenceExpression()) ); }

The last missing element is the type converter class. TypeConverter is a standard class which the user interface uses to convert a type to the textual format and back. Now we’ll construct a type converter suitable for the ConvValue class. The TypeConverter class is constructed in the CalcDoubleValueAttribute class and the actual code goes as follows:
C# public override TypeConverter Converter { get { return new CalcValueConverter(); } } protected class CalcValueConverter : System.ComponentModel.TypeConverter { public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType) { if (destinationType == typeof(string)) return true; return false; } public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType) { if (destinationType != typeof(string)) throw new ArgumentException("Invalid type", "destinationType"); CalcValue val = (CalcValue) value; return val.Value.ToString(culture.NumberFormat); } public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType) {

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

8 / 16

Walkthrough 13 : Custom types

if (sourceType == typeof(string)) return true; return false; } public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value) { if (value is string) { string sValue = ((string)value).Trim(); CalcValue cv = new CalcValue(); if (sValue == "?") { // Autocalc on return cv; } cv.Manual = true; cv.Value = Double.Parse(sValue, culture.NumberFormat); return cv; } throw new ArgumentException("Invalid type", "value"); } }

Change the Result field into a read-only field in the user-interface. Before building the file, save the dbclass after the last changes in the CalcDoubleValueAttribute class as the generator will generate different code according to the C# code.

Build the application and run it to test the new attribute. You’ll get an error message, why?

The Nolics.net double class will check that the DoubleValue attribute has been attached to a Double type class. This will cause the error. We’ll override the error checking with the following code:
C# public override void VerifyAttribute(System.Reflection.PropertyInfo boundToProperty, IVerifyReport report) { }

If you enter a value into a field, the manual entry will override the result of the calculations. If you enter a “?” as the field value, the override will be canceled. What happens if you close the application and restart it? (Make sure that you load the query in the beginning of the application.)

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

9 / 16

Walkthrough 13 : Custom types

5.Creating an UDT class
The value of the manual flag is not preserved during application restarts as the manual flag is not stored into the database. The double value does not have a place to store the flag. Next we’ll construct a user defined type for storing the value. The next example requires the use of SQL Server 2005 or SQL Server Express. It will not work on any other database. Close the current solution. Create a new solution as Database / SQL Server project.

The new project and solution will be called WT13_UDTType. The project database should be the same as the database where your test database resides. Next we’ll add a new user defined type to the SQL Server project:
C# [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct CalcValue : INullable {

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

10 / 16

Walkthrough 13 : Custom types

public override string ToString() { return Value.ToString() + (Manual ? "M" : ""); } public bool IsNull { get { return false; } } public static CalcValue Null { get { CalcValue h = new CalcValue(); return h; } } public static CalcValue Parse(SqlString s) { CalcValue u = new CalcValue(); if (!s.IsNull) { if (s.Value.EndsWith("M")) { u.Manual = true; u.Value = Double.Parse(s.Value.Substring(0, s.Value.Length - 1)); } else u.Value = Double.Parse(s.Value); } return u; } public double Value; public bool Manual; }

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

11 / 16

Walkthrough 13 : Custom types

Construct a strong key for the UDT type

Then compile the UDT solution.

When the UDT project has been compiled Deploy Solution. Now the new type should be available. Note that you must have enabled the CLR for the deploy to success, see example below:

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

12 / 16

Walkthrough 13 : Custom types

You may test the UDT type by creating a test table. Run the following commands and see that you have no problems:
CREATE TABLE TestTable(Field1 CalcValue) GO INSERT INTO TestTable (Field1) VALUES ('123') GO SELECT Field1.Value, Field1.Manual FROM TestTable GO

At this stage, install WT13_UDTType library to the GAC.

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

13 / 16

Walkthrough 13 : Custom types

Change the CalcDoubleValueAttribute class and remove the CalcValue struct. Add a DLL reference to the WT13_UDTType project in both WT13_CustomerTypesGUI and WT13_CalcDouble (browse and select the DLL). Now the database1.dbclass will use the CalcValue version that exists in the WT3_UDT library. Note! You might need to stop SQL server from SQL Configuration Manager when you would like to modify WT13_UDTTypes in order to deploy again.

Note! Do not add a project reference to WT13_UDTType project. Instead, browse and select the DLL with the browse functionality and construct a reference. Check that the copy local setting is false.

6.Using the UDT class
Next we’ll change the generation process so that we utilize the new UDT type. First we’ll add a new override for the CalcDoubleValueAttribute class:
C# public override Type GetPersistType() { return typeof(CalcValue); }

In addition, we’ll change the ValueFromDS and ValueToDS methods so that they will expect to get a CalcValue type.

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

14 / 16

Walkthrough 13 : Custom types

C# public static new CalcValue ValueFromDS(object value) { return (CalcValue)value; } public static object ValueToDS(CalcValue value) { return value; }

Build and run the application. You will get an error.

The next error will be caused by a check in the SQL Server 2005 driver. This error message tells us that the type CalcValue is not known. Thus, we’ll have to change the SQL Server 2005 database rules so that it will recognize the new data type (see walkthrough 12 for more information on the database rules). Next, we’ll include the CalcDouble.sqlrule file in the WT13_CalcDouble project. The rule file will go as follows:
field_type when "@DBType='CalcValue'" { 'CalcValue' } field_default when "@DBType='CalcValue'" { ' default ''0'' ' } // Check change check_field_type when "@DBType='CalcValue' and '$0$' = 'CalcValue'" { // Field OK } check_field_type when "@DBType='CalcValue'" { Error('remove column ', "@FieldName", ' and recreate it') } // Link to sql server rules #include 'res:Nolics.ORMapper.Base.DataProviders.SQLServer2005.sqlrule'

Use the Connection Editor to edit the rule to be used so that UpgradeRule will point to the newly created CalcDouble.sqlrule file. Our rule will not try to execute an alter table command even if the database type was invalid. This is because the SQL Server would have no way of converting a double type into a CalcValue type. So instead, we’ll remove the column by adding the Remove attribute to the column:

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

15 / 16

Walkthrough 13 : Custom types

calcDouble Result [CalcMethod = "CalcResult"] Remove[];

At this point, you should build the application and then upgrade the database. Make sure that the Result column is removed.
calcDouble Result [CalcMethod = "CalcResult"] Remove[];

Take out the Remove attribute and run DB upgrade again. You are now using your own UDT type. Test the application.

7.Conclusion
Utilizing UDT classes requires somewhat work to enable the UDT class to be used easily. After this one time effort, the UDT type is very easy to use in different classes and projects. Note that if you are under the impression that using an UDT class with Nolics.net is a lot of work you should try to use them without Nolics.net. You still have to go through all these steps (except the generator part) but you are not aided by the Nolics.net tools.

Copyright © 2003-2006. Nolics Oy. All rights reserved

Document version 2

16 / 16


				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:15
posted:12/29/2009
language:English
pages:16