Warensoft Unity3D Communication Library Tutorial 4-SQL SERVER Access Components

(Author: Warensoft, Email: warensoft@163.com)

In the previous chapter “Warensoft Unity3d Communication Library Tutorial 3-Create Warensoft Data Service”, we have shown the steps of configuring Warensoft Data Service, and from this chapter we will implement how to communicate to Warensoft Data Service with Warensoft Unity3D Communication Library components, and reach the final point of directly accessing to Sql Server in Unity3D:

1. Name space and classes

name space: Warensoft.Unity.Communication.Client.DataClient

DataContext class

This class manages sql server connections. this class provides common sql server operations, such as insert, update, delete, select. And additionally, this class contains the mapped data tables and data rows.

DataEntity class

Represents a data record

DataEntitySet class

Represents a data table

DataProperty class

Represents a data column(data field)

DataPropertySet class

Data column collection, it represents all the columns in a data row

DataQuery class

This class provides many methods to build sql server queries. These built queries will be sent to the Warensoft DataService servers, and be parsed to different sql statements that could be recognized by sql server.

2. DataContext class

  • The class diagram is shown as below:

clip_image002

  • Public Properties:

Ø SchemaLoaded:Indicates whether the data table schemas are completely loaded. After loaded developers could do insert, delete, update and select operations with DataContext class. Alternatively the callback method of SchemaLoadedComplete event will be automatically invoked after the schemas are loaded.

Ø ServiceUrl:Get the URL of Warensoft Data Service which is using in the current context.

Ø Tables:Get the data table collection in the current connection context, developers could index a specified data table by the table name(string).

l Public Events

Ø Error:Occurs when the data connection or data operations raised an error

Ø SchemaLoadCompleted:Occurs after the data table schemas are completely loaded. When the event is occurred, the DataContext would only load the structures of data table, but no data is contained。This event is triggered in one of the Update methods, so it is thread safe to the Unity3D.

  • Public methods

Ø SaveChanges():Synchronize all the changes, including insert, update and delete operations to database. The strategy of synchronization is client wins。If developers want to choose the different synchronization strategy, the overloaded form is preferred.

Ø SaveChanges(RefreshMode mode):Synchronize all the changes, including insert, update and delete operations to database. The parameter is a RefreshMode enum type, and it indicates different synchronization strategies. There are 2 types included:1.RefreshMode.ClientWins,2.RefreshMode.StoreWins.

3. DataEntity class

  • The class diagram is shown as below:

clip_image004

  • Public properties

Ø Properties:Get the collection of all the fields(data columns) in a data entity(data row). The collection could be indexed by specifying the column name (string).

Ø State:Get the current state of a data entity (data row). This property is an EntityState enum type, which contains four values:Changed,Added,Deleted,Unchanged.

Ø TableName:Get the name of data table which the data entity belongs to.

Ø this[string column]:Indexer, get the field value by providing a field name.

  • Public methods

Ø AcceptChanges():Accept all the changes of the data entity, after this method is invoked the State property will be reset to Unchaged, after that if the SaveChanges() method of the related DataContext was invoked, data in this data entity will not be submit to the server.

Ø Delete():After invoke this method, the State property would be set to Deleted, and this entity will not be removed from memory immediately, until the SaveChanges() method of the related DataContext is invoked somewhere. When the State property is set to Deleted, this data entity will not occurs in the available data row collection of the data table which the entity belongs to any more.

4. DataEntitySet class

  • The class diagram is shown as below:

clip_image006

  • Public properties

Ø Count: Get the count of the rows(data entities) which are contained in the entity collection.

Ø Entities:Get the data entity collection(data rows).

Ø TableName:Get the name of the data table which the current entity collection represents.

  • Public methods

Ø AcceptChanges():It will invoke the AcceptChanges() method of all the data entities contained in the current entity collection.

Ø Add(DataEntity item):Add a new data entity to the current entity collection. The parameter item must be created by calling NewEntity() method of the same data entity collection, or else an exception will be thrown. The State property of the new added entity will be set to Added, and this operation would just affect the client memory, until the SaveChanges() method of the related DataContext is called somewhere, this data row will be finally insert to the database, if succeed, the State property will be reset to Unchanged.

Ø Clear():Clear all the data entities in entity collection in memory. This operation will only affect the client memory, when the SaveChanges() method of the related DataContext is called, no effects will occur in the database.

Ø ContainsDataEntity item:Whether the specified data entity instance is contained in the entity collection.

Ø LoadAsync(Action fillAsyncCallback):Asynchronously load the data entities(data rows) of the entity collection from server(that is, the select sql statement). The parameter fillAsyncCallback is the callback method and it will be invoked when the asynchronous load is finished. PLEASE be careful when using this method, because it will load all the data rows to the client memory. It is preferred to use the overloaded form, and do the queries with conditional parameters.

Ø LoadAsync(Action fillAsyncCallback,DataQuery query):Asynchronously load the data entities(data rows) of the entity collection from server(that is, the select sql statement). The parameter fillAsyncCallback is the callback method and it will be invoked when the asynchronous load is finished. and the parameter “query” is the conditional query parameter.

Ø NewEntity():This method will create a new instance of DataEntity class, and this instance would have the same schema with the data table (DataEntityCollection).

5. DataProperty class

  • The class diagram is shown as below:

clip_image008

  • Public properties

Ø ColumnName:Get the name of the field.

Ø IsPrimaryKey:Indicates whether this key is one of the primary keys.

Ø Value:Get the value of this field.

Ø OriginalValue:Get the original value before the field was changed. In the initialization period, the Value property and the Original property have the same value, if a new value is assigned to this field, the Value property would change, but the OriginalValue property will keep its value until the SaveChanges() method of the related DataContext is successfully called somewhere, and then the OriginalValue property would be set to the same with the Value property.

6. DataPropertySet class

  • The class diagram is shown as below:

clip_image010

  • Public properties

Ø Count:Get the count of the properties contained in the current property collection.

Ø this[int index]: Indexer, get a property by providing an index number.

Ø this[string columnName]:Indexer, get a property by providing a column name

  • Public mehtods

Ø No public methods, all the methods are internal.

7. DataQuery class

  • The class diagram is shown as below

clip_image012

  • Public properties

Ø No public properties, all the properties are internal

  • Public methods

Ø static And(DataQuery q1,DataQuery q2):This static method represents a logical AND operation to combine two data queries.

Ø static Or(DataQuery q1,DataQuery q2):This static method represents a logical OR operation to combine two data queries.

Ø EqualsTo<T>(string key,T value):The key equals to the value, the type parameter T specifies the data type of the value.

Ø NotEqualsTo<T>(string key,T value) :The key not equals to the value, the type parameter T specifies the data type of the value.

Ø GreaterThan<T>(string key,T value) :The key is greater than the value, the type parameter T specifies the data type of the value.

Ø GreaterThanOrEqualsTo<T>(string key,T value) :The key is greater than or equals to the value, the type parameter T specifies the data type of the value。

Ø SmallerThan<T>(string key,T value) :The key is smaller than the value, the type parameter T specifies the data type of the value.

Ø SmallerThanOrEqualsTo<T>(string key,T value) :The key is smaller than or equals to the value, the type parameter T specifies the data type of the value.

Ø OrderBy(string key) :Order by ascending。

Ø OrderByDescending(string key):Order by descending

Ø Skip(int count):When querying, this method will skip the specified count of rows.

Ø Take(int count):When querying, this method will take specified count of rows, this method equals to the “TOP” operation in sql server.

  • Currently , the following data types are supported in DataQuery

Ø Guid

Ø byte

Ø short

Ø int

Ø long

Ø ushort

Ø uint

Ø ulong

Ø double

Ø float

Ø decimal

Ø bool

Ø string

8. How To Build Combined Queries with DataQuery Class

Developers could build the common data queries with DataQuery class, the followings are the details:

//select * from customers where customerid=’ALFKI’

this.context.Tables["Customers"].LoadAsync(

() => 

{ 

//load finished

},(new DataQuery()).EqualsTo("CustomerID","ALFKI"));

//select * from customers where Country!=’US’

this.context.Tables["Customers"].LoadAsync(

() =>

{

// load finished

}, (new DataQuery()).NotEqualsTo("Country", "US"));

//select * from products where UnitPrice>10

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

}, (new DataQuery()).GreaterThan("UnitPrice",10f));

//select * from products where UnitPrice<10

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

}, (new DataQuery()).SmallerThanOrEqualsTo("UnitPrice", 10f));

//select * from products where UnitPrice<=10 order by ProductID

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

}, (new DataQuery()).SmallerThanOrEqualsTo("UnitPrice", 10f).OrderBy("ProductID"));

//select * from Products where UnitPrice<=10 order by ProductID desc

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

}, (new DataQuery()).SmallerThanOrEqualsTo("UnitPrice", 10f).OrderByDescending("ProductID"));

//select * from Products where UnitPrice>10

//Skip the top 10 records and take the top 5 records

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

}, (new DataQuery()).GreaterThan("UnitPrice", 10f).Skip(10).Take(5));

// select * from products where discontinued=true and UnitPrice>20

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

},

(new DataQuery()).EqualsTo("Discontinued", true).GreaterThan("UnitPrice", 20f));

//Logical AND operation

// select * from products where discontinued=true and UnitPrice>20

//this operation equals to the previous one

this.context.Tables["Products"].LoadAsync(

() =>

{

// load finished

}, 

DataQuery.And(

(new DataQuery()).EqualsTo ("Discontinued",true),

(new DataQuery()).GreaterThan("UnitPrice",20f)

));

//Logical OR operation

// select * from customers where customerid =’ALFKI’ or CustomerID=’Warensoft’

//this operation will return two records

this.context.Tables["Customers"].LoadAsync(

() =>

{

// load finished

},

DataQuery.Or(

(new DataQuery()).EqualsTo("CustomerID", "ALFKI"),

(new DataQuery()).EqualsTo("CustomerID", "Warensoft")

));

Last edited May 4, 2012 at 6:04 AM by warensoft, version 1

Comments

No comments yet.