Note that unless stated otherwise, whenever you refer to an
item in code (generated class, property, table, field), names are case
sensitive. This internally allows for fast searching and name resolution.
Generated classes provide built-in functionality for working
with records in your underlying data source using an object-oriented approach.For each table or view in your data source, a class is created. Technically,
two classes are created: A base class that derives from MappedClass
and a project class that derives from the base class. However, you
should only work with project classes in your code. This allows you to add
your own code to those classes and retain any additions you’ve made if you
regenerate output.
Functions
Most of the built-in functions provided by generated classes
are inherited from MappedClass.
You can visit the API documentation for more information on MappedClass members. Generated classes also contain custom code
that is specific to a particular class, namely to retrieve strongly-typed
instances of that class. Those functions are GetClassName and GetClassNameList.
Properties
Mapped classes contain a strongly-typed property for each
field in the source table it maps to. Additionally, mapped classes contain
properties that reference related mapped classes, making it possible to
navigate relational hierarchies. There
are three types of related properties: Parent, Child, and Linked. Parent type
properties represent a many to one type of relationship, and therefore refer to
a single instance of a related class. Child type properties represent a one
to many type of relationship, and refer to a list of related child objects.
Linked type properties represent a many to many relationship, and refer to a list
of related objects (where a third class acts as a bridge, defining how the class
and the referenced class relate to one another). Both child and linked properties
return a strongly-typed list class that derives from MappedObjectList(Of mappedClassType).
State
Mapped classes store two versions
of property values: original and updated.
The original value can be thought of as the value of the property as it exists in
the underlying data source, i.e. a persisted value. The updated value is a
value that has been set in-memory for the property, but has not
yet been persisted to the data source. Distinguishing between these value types
allows for optimistic concurrency, incremental
updates, and the ability to call RevertToOriginalValues to restore the
original state of an object. Properties return the updated value by default,
or the original value if no updated value exists. However, MappedClass
provides protected functions that you can use inside
your derived classes to get/set either value at any
time: GetOriginalPropertyValue/SetOriginalPropertyValue
and GetUpdatedPropertyValue/SetUpdatedPropertyValue.
Null Values
Many database systems allow for
Null values for their data types, however value types in the .NET framework are not
nullable by default. If a mapped object record contains a property in which
the underlying field value is Null, the property will return a default value
for that type (for example, zero in the case of an integer).
However, it is
possible to determine if the underlying value of a property is Null by calling IsPropertyNull(propertyName).
Similarly,
you can set a property value to Null by calling SetPropertyNull(propertyName).
When using generated project classes, you can simply call the appropriate method to
insert, update, delete, or retrieve data. However, you also have the option of
using request objects. Request objects are capable of doing everything
generated classes can do and more (in fact, mapped classes internally use
request objects to carry out their tasks). They can handle complex filters,
multi-table joins, and more. You can carry out requests using the appropriate request
manager class (RequestManager for mapped class requests and SourceRequestManager for data
library requests).
Below
are examples of how to use generated classes and request objects to perform
various data-related tasks, using hypothetical generated project classes such as 'Product',
'Customer', etc. Note that the code below was kept simple for demonstrative
purposes and does not reflect best practices. For example, use constants instead
of string literals for names of
properties, fields, etc., functions that may throw
exceptions should be called in Try…Catch blocks, and so on.
Consult the API documentation for more detailed information on any of the classes used
in the examples.
Retrieving Data
Single Record
The examples below show different ways to retrieve a single data record (in various forms). Note that the only required information a request needs is the name
of the project class (for mapped class requests),
or the name of the data source and table (for data library
requests). The name of the data source can be omitted if your project uses only
one database.
Mapped Class
'Retrieve a product record by ID.
'Although not used in this example, composite primary
'keys are also supported.
Dim prod As Product = Product.GetProduct(789)
ObjectRecordRequest
Dim req As New ObjectRecordRequest(GetType(Product))
req.Criteria.AddFilter("ProductID", 789)
Dim prod As Product = DirectCast(RequestManager.GetObject(req), Product)
DataLib.RecordRequest
'Note that for single data source projects, it is
'not necessary to supply a data source name for requests.
Dim recReq As New RecordRequest("MyDataSource", "ProductsTable")
recReq.Where.AddFilter("ProductID", 789)
Dim row As DataRow = SourceRequestManager.GetRecord(recReq)
' -OR-
Dim reader As DbDataReader = SourceRequestManager.GetReader(recReq)
Convert Data Record To Object
Certain specialized queries may not be directly supported by Auto SQL. Knowing this, Auto SQL
provides a means to convert your raw data query result into a strongly-typed project class.
Dim source As DataSource = SourceInfoProvider.GetDataSource()
Dim cmd As DbCommand = source.CreateCommand()
cmd.CommandText = "Select Top 1 * From Products"
Try
cmd.Connection.Open()
Dim reader As DbDataReader = cmd.ExecuteReader()
Dim obj As MappedClass = _
LibAdapter.ResultConvertor.ConvertToObject(GetType(Product), reader)
Dim prod As Product = DirectCast(obj, Product)
Catch
Throw
Finally
cmd.Connection.Close()
End Try
Multi-Record
Note that the only required
information a request for records needs is the name of the project class (for mapped class requests), or the name of the data source and table (for data library
requests). The name of the data source can be omitted if your project uses
only one database.
Mapped Class
Dim prods As MappedObjectList(Of Product) = _
Product.GetProductList()
ObjectRecordsRequest
Dim req As New ObjectRecordsRequest(GetType(Product))
Dim list As IMappedObjectList = RequestManager.GetObjects(req)
'Optionally, cast to a strongly typed list
Dim products As MappedObjectList(Of Product) = _
DirectCast(list, MappedObjectList(Of Product))
DataLib.RecordsRequest
Dim recsReq As New RecordsRequest("ProductsTable")
Dim rows As DataRow() = SourceRequestManager.GetRecords(recsReq)
' -OR-
Dim reader As DbDataReader = SourceRequestManager.GetReader(recsReq)
Multi-Record: Sorted
Results can be sorted by any number of fields and in any order.
Mapped Class
'Returns a list of products sorted by price
'in descending order.
Dim prods As MappedObjectList(Of Product) = _
Product.GetProductList("UnitPrice DESC")
ObjectRecordsRequest
Dim req As New ObjectRecordsRequest(GetType(Product))
req.Sort.Add("UnitPrice", LibAdapter.SortOrder.Descending)
Dim list As IMappedObjectList = RequestManager.GetObjects(req)
DataLib.RecordsRequest
Dim recsReq As New RecordsRequest("ProductsTable")
recsReq.OrderBy.Add("UnitPrice", LibAdapter.SortOrder.Descending)
Dim rows As DataRow() = SourceRequestManager.GetRecords(recsReq)
Multi-Record: Filtered
You can apply filters to requests to control what records are returned.
Mapped Class: Single Filter
'Get products priced over $100
Dim criteria As New Criteria()
criteria.AddFilter("UnitPrice", 100, ComparisonOperator.GreaterThan)
Dim prods As MappedObjectList(Of Product) = _
Product.GetProductList(criteria)
Mapped Class: Multi-Filter
'Request for all supervisors hired before 6/21/2000
'or managers hired before 6/21/1998.
Dim req As New ObjectRecordsRequest(GetType(Employee))
With req.Criteria
.BeginGroup()
.AddFilter("Title", "Supervisor")
.AddFilter("HireDate", New Date(2000, 6, 21), ComparisonOperator.LessThan)
.EndGroup()
.AddLogicalOperator(LogicalOperator.Or)
.BeginGroup()
.AddFilter("Title", "Manager")
.AddFilter("HireDate", New Date(1998, 6, 21), ComparisonOperator.LessThan)
.EndGroup()
End With
ObjectRecordsRequest
Dim req As New ObjectRecordsRequest(GetType(Product))
req.Criteria.AddFilter("UnitPrice", 100, ComparisonOperator.GreaterThan)
Dim list As IMappedObjectList = RequestManager.GetObjects(req)
DataLib.RecordsRequest
Dim recsReq As New RecordsRequest("ProductsTable")
recsReq.Where.AddFilter("UnitPrice", 100, _
ComparisonOperator.GreaterThanOrEqualTo)
Dim rows As DataRow() = SourceRequestManager.GetRecords(recsReq)
Multi-Record: Limited/Paged
Auto SQL supports queries that start returning records at a specified record
index and/or limit the number of records returned. By applying both a start index and record limit, you can enable
record paging in your application.
Mapped Class
'Return object records, starting at the
'10th record, and limit results to 50 items.
'Zero-based index
Dim startIndex As Integer = 9
Dim maxItems As Integer = 50
Dim products As MappedObjectList(Of Product) = _
Product.GetProductList("ProductName", startIndex, maxItems)
ObjectRecordsRequest
Dim req As New ObjectRecordsRequest(GetType(Product))
req.StartRowIndex = 9
req.MaxItems = 50
Dim items As IMappedObjectList = RequestManager.GetObjects(req)
DataLib.RecordsRequest
Dim dataReq As New RecordsRequest("Products")
dataReq.StartRowIndex = 9
dataReq.MaxRows = 50
Dim rows As DataRow() = SourceRequestManager.GetRecords(dataReq)
Multi-Record: Grouped/Aggregated
The data library provides the ability to issue requests that group and
aggregate data records, with the ability to filter and order grouped/aggregated items.
'Returns the total number of orders for each city,
'for cities with 10 or more orders, sorted by
'cities with most orders first.
Dim req As New DataLib.RecordsRequest("Orders")
Dim cities As New RequestedField("ShipCity")
cities.Group = True
req.Fields.Add(cities)
Dim numOrders As New RequestedField("OrderID", _
DataFunction.Count, "Number of Orders")
req.Fields.Add(numOrders)
Dim orderCheck As New RowFilter("OrderID", 10, _
ComparisonOperator.GreaterThanOrEqualTo)
orderCheck.DataFunction = DataFunction.Count
req.Having.AddFilter(orderCheck)
Dim sortCities As New SortedField("OrderID", LibAdapter.SortOrder.Descending)
sortCities.DataFunction = DataFunction.Count
Dim rows As DataRow() = SourceRequestManager.GetRecords(req)
Multi-Record: Convert Data Records To Objects
Certain specialized queries may not be directly supported by Auto SQL. Knowing this, Auto SQL
provides a means to convert your raw data query results into a strongly-typed mapped object list.
Dim source As DataSource = SourceInfoProvider.GetDataSource()
Dim cmd As DbCommand = source.CreateCommand()
cmd.CommandText = "Select * From Products"
Try
cmd.Connection.Open()
Dim reader As DbDataReader = cmd.ExecuteReader()
Dim list As IMappedObjectList = _
LibAdapter.ResultConvertor.ConvertToObjects("Product", reader)
Dim products As MappedObjectList(Of Product) = _
DirectCast(list, MappedObjectList(Of Product))
Catch
Throw
Finally
cmd.Connection.Close()
End Try
Single Value
Single value requests allow you to pull a single piece of data directly from your data source, or apply a function that returns
a single value.
SingleValueRequest
Dim req As New SingleValueRequest(GetType(Product))
req.PropertyName = "UnitPrice"
req.DataFunction = DataFunction.Average
Dim averageProductPrice As Double = CDbl(RequestManager.GetSingleValue(req))
DataLib.ScalarValueRequest
Dim valReq As New ScalarValueRequest("CategoriesTable")
valReq.FieldName = "ID"
valReq.DataFunction = DataFunction.Count
Dim numCats As Integer = CInt(SourceRequestManager.GetScalarValue(valReq))
Distinct Values
Occasionally, you may need to retrieve a list of distinct values of a given field.
'Retrieve a list of countries where customers live.
Dim req As New DataLib.DistinctValuesRequest("Customers")
req.FieldName = "Country"
req.OrderBy.Add("Country")
Dim customerCountries As ArrayList = _
SourceRequestManager.GetDistinctValues(req)
Creating Records
The following examples show the different options you have to create (insert) new records in your data source.
Mapped Class
For classes with an auto-identity property, Auto SQL automatically retrieves and populates this property value after the object is
created in the underlying source table (so long as the data provider supports auto-identity retrieval).
Dim prod As New Product()
prod.ProductName = "TestProduct"
prod.UnitsInStock = 25
prod.Create()
' -OR-
prod.Save()
Mapped Object List
Note that calling SaveChanges on a mapped object list will not only create new objects, but
will also persist any other changes made to the list
(Save is called on all existing mapped objects, and all existing objects removed from the list will be deleted from the underlying data source).
Dim products As New MappedObjectList(Of Product)
Dim prod1 As New Product()
prod1.ProductName = "Widget ABC"
products.Add(prod1)
Dim prod2 As New Product()
prod2.ProductName = "Widget XYZ"
products.Add(prod2)
products.SaveChanges()
CreateRequest
Dim req As New CreateRequest(GetType(Product))
req.Values.Add("ProductName", "TestProduct")
req.Values.Add("UnitsInStock", 25)
RequestManager.Create(req)
DataLib.InsertRecordRequest
Dim req As New InsertRecordRequest("ProductsTable")
req.Values.Add("ProductName", "TestProduct")
req.Values.Add("UnitsInStock", 25)
req.Values.Add("Discontinued", False)
SourceRequestManager.InsertRecord(req)
Updating Records
The examples below show different options to update underlying data source records.
Single Record
Mapped Class
A simple save updates only directly mapped properties (those that are mapped to a field in a table). Note
that Auto SQL performs "smart updates", meaning that it will compare updated values to original values, and
only attempt to persist changed values. This boosts application performance,
as database throughput is kept to a minimum.
Dim prod As Product = Product.GetProduct(123)
prod.UnitPrice += 20
prod.Save()
UpdateRequest
Dim req As New UpdateRequest(GetType(Product))
req.Criteria.AddFilter("ProductID", 123)
req.Values.Add("UnitPrice", 99)
RequestManager.Update(req)
DataLib.UpdateTableDataRequest
Dim req As New UpdateTableDataRequest("ProductsTable")
req.Where.AddFilter("ProductID", 123)
req.Values.Add("UnitPrice", 99)
SourceRequestManager.UpdateData(req)
Multi-Record
Mapped Class: Related Property
Note that calling SaveChanges on a mapped object list will not only update existing objects, but
will also persist any other changes made to the list (Create is called on
any newly created mapped objects, and all existing objects removed from the list will be
deleted from the underlying data source).
'Mark up all product prices in a category
'by 10%
Dim cat As Category = Category.GetCategory(1)
For Each prod As Product In cat.Products
prod.UnitPrice *= 1.1D
Next
cat.Products.SaveChanges()
Mapped Object List
Note that calling SaveChanges on a mapped object list will not only update existing objects, but
will also persist any other changes made to the list (Create is called on any newly created mapped objects, and all existing objects removed from the list will be deleted from the underlying data source).
'Change all whole number product prices to end in .99
Dim products As MappedObjectList(Of Product) = Product.GetProductList()
For Each prod As Product In products
If Math.Round(prod.UnitPrice) = prod.UnitPrice Then
prod.UnitPrice -= 0.01D
End If
Next
products.SaveChanges()
UpdateRequest
'For employees without pictures, set
'picture to default image.
Dim req As New UpdateRequest(GetType(Employee))
req.Criteria.AddFilter("PicturePath", Nothing)
req.Values.Add("PicturePath", "Images\default.jpg")
RequestManager.Update(req)
DataLib.UpdateTableDataRequest
Dim upReq As New UpdateTableDataRequest("Employees")
upReq.Where.AddFilter("PicturePath", System.DBNull.Value)
upReq.Values.Add("PicturePath", "Images\default.jpg")
SourceRequestManager.UpdateData(upReq)
Deleting Records
The examples below show different methods of deleting object records in the underlying data source.
Single Record
Mapped Class
Dim prod As Product = Product.GetProduct(321)
prod.Delete()
DeleteRequest
Dim req As New DeleteRequest(GetType(Product))
req.Criteria.AddFilter("ProductID", 321)
RequestManager.Delete(req)
DataLib.DeleteTableDataRequest
Dim req As New DeleteTableDataRequest("ProductsTable")
req.Where.AddFilter("ProductID", 321)
SourceRequestManager.DeleteData(req)
Multi-Record
Mapped Class: Related Property
Note that calling SaveChanges on a mapped object list will not only delete
objects from the source, but
will also persist any other changes made to the list (Create is called on
any newly created mapped objects, and Save is called on all existing mapped objects).
'Delete all discontinued products in the category
Dim cat As Category = Category.GetCategory(1)
Dim i As Integer = 0
Do While i < cat.Products.Count
If cat.Products(i).Discontinued Then
cat.Products.RemoveAt(i)
Else
i += 1
End If
Loop
cat.Products.SaveChanges()
Mapped Object List
Note that calling SaveChanges on a mapped object list will not only delete
objects from the source, but
will also persist any other changes made to the list (Create is called on
any newly created mapped objects, and Save is called on all existing mapped objects).
'Delete all discontinued products
Dim products As MappedObjectList(Of Product) = Product.GetProductList()
Dim c As Integer = 0
Do While c < products.Count
If products(c).Discontinued Then
products.RemoveAt(c)
Else
c += 1
End If
Loop
products.SaveChanges()
DeleteRequest
'Delete all discontinued products
Dim req As New DeleteRequest(GetType(Product))
req.Criteria.AddFilter("Discontinued", True)
RequestManager.Delete(req)
DataLib.DeleteTableDataRequest
'Delete all discontinued products
Dim delReq As New DeleteTableDataRequest("Products")
delReq.Where.AddFilter("Discontinued", True)
SourceRequestManager.DeleteData(delReq)
Persisting Related Data
When Save is called for a mapped class, only directly mapped property values are persisted. However, you may
want to persist changes to the mapped object and all of its related properties. DeepSave
calls Save on the current object and any parent property objects as well as SaveChanges
on any child or linked property list. These operations are all performed using a single connection for best performance.
Dim cat As Category = Category.GetCategory(1)
For c As Integer = 0 To cat.Products.Count - 1
If cat.Products(c).ProductName = "Widget 1.0" Then
cat.Products.RemoveAt(c)
End If
Next
Dim prod As New Product
prod.CategoryID = cat.CategoryID
prod.ProductName = "Widget 2.0"
cat.Products.Add(prod)
cat.Description = "Widget 2.0 now available!"
'Deletes the removed product, inserts the new one,
'and updates the category description.
cat.DeepSave()
Multi-Class & Multi-Table Requests
By default, requests are carried out in the context of a single class or table. However, you have the option of referring to related classes and
tables in your requests.
Mapped Classes
'Request for all customer records who had orders shipped yesterday.
Dim req As New ObjectRecordsRequest(GetType(Customer))
Dim yesterday As Date = Date.Now.AddDays(-1)
'The Customer class contains a related property 'CustomerOrders'
'which references Order class instances containing
'a 'ShipDate' property
req.Criteria.AddFilter("ShipDate", "CustomerOrders", yesterday, _
ComparisonOperator.Equals)
Dim customers As IMappedObjectList = RequestManager.GetObjects(req)
Data Library
A key difference between mapped class
and data library requests in terms of referencing related items is that you must
explicitly define how tables are related in data library requests. The reason for
this is data library requests allow you to logically join tables at runtime, even
if the database has not formally defined a relationship.
Also, data library
requests allow you to navigate as deep as you’d like into relational
hierarchies. This is accomplished by adding table joins to the
request.
Simple Join
'Joins the Categories table to Products in order
'to see the category name (instead of an id number)
Dim req As New RecordsRequest("Products")
Dim prodCatJoin As New TableJoin("Categories", "Products")
prodCatJoin.AddJoinedFields("ID", "CategoryID")
req.Joins.Add(prodCatJoin)
req.Fields.Add("Name", "Products", "Product Name")
req.Fields.Add("Name", "Categories", "Category Name")
Dim rows As DataRow() = SourceRequestManager.GetRecords(req)
Nested Join
'Retrieves all customers who have ordered a particular product.
Dim req As New RecordsRequest("Customers")
Dim custOrderJoin As New TableJoin("Orders", "Customers")
custOrderJoin.AddJoinedFields("CustomerID", "ID")
req.Joins.Add(custOrderJoin)
Dim orderOrdDetJoin As New TableJoin("Order Details", "Orders")
orderOrdDetJoin.AddJoinedFields("OrderID", "ID")
req.Joins.Add(orderOrdDetJoin)
req.Where.AddFilter("ProductID", "Order Details", 123, ComparisonOperator.Equals)
Keep-Alive Connections
When a request is executed, the request
manager creates a connection to the underlying database, carries out the
request, then closes the connection. However, if you would like to execute a
series of requests in succession to one another, you have the option of sharing
a single open connection. This can boost application performance by avoiding
the overhead of creating new connections for each request.
Mapped Classes
'This batch token will be passed to any function that we
'want to use the same connection with.
Dim bt As BatchToken = RequestManager.StartBatch()
Try
'Create a new region record.
Dim reg As New Region
reg.RegionDescription = "NewRegion"
reg.Create(bt)
'Create a new territory record in the above region.
Dim ter As New Territory()
'reg.RegionID was automatically populated when reg.Create was
'called, as it is an auto identity field.
ter.RegionID = reg.RegionID
ter.TerritoryDescription = "NewTerritory"
ter.Create(bt)
'Retrieve an employee record
Dim emp As Employee = Employee.GetEmployee(35462, bt)
'Assign the employee to the new territory.
'emp.TerritoryID = ter.TerritoryID
emp.Save(bt)
Catch
Throw
Finally
RequestManager.EndBatch(bt)
End Try
Data Library
Dim cn As DbConnection
'Create two new category records, using a single connection that we pass
'to the SourceRequestManager.
Try
'Create a connection to the data source.
cn = SourceRequestManager.Connect("MyDataSource")
Dim req As New InsertRecordRequest("CategoriesTable")
req.Values.Add("Name", "Vegetables")
SourceRequestManager.InsertRecord(req, cn)
'Note that we can re-use the same request object for multiple requests.
'Here, we simply change the name of the category we want to create.
req.Values(0).Value = "Snacks"
SourceRequestManager.InsertRecord(req, cn)
Catch
Throw
Finally
SourceRequestManager.Disconnect(cn)
End Try
Concurrency
This section describes the built-in functionality Auto SQL offers in addressing concurrency issues.
Optimistic
This type of approach will create a
query that checks that none of the values you’ve modified have been changed in the underlying source
since the time you retrieved the record. The Save function will return False if the
record failed to update.
Dim prod As Product = Product.GetProduct(123)
prod.UnitsInStock = 10
'Pass True to the function to use optimistic concurrency.
prod.Save(True)
Pessimistic
This approach uses database
transactions to lock records. There are several options you have as to the
extent in which records are locked. This is controlled by the IsolationLevel
you specify for the transaction.
Mapped Class
'Internally creates a transaction
Dim bt As BatchToken = RequestManager.StartBatch(IsolationLevel.Serializable)
Try
Dim p As Product = Product.GetProduct(123, bt)
p.UnitsInStock = 10
p.Save(bt)
'Ends the batch and commits changes.
RequestManager.EndBatch(bt)
Catch ex As Exception
'Ends the batch and rolls back changes.
RequestManager.EndBatch(bt, True)
End Try
Data Library
Dim trans As DbTransaction = _
SourceRequestManager.BeginTransaction("MyDataSource", _
IsolationLevel.Serializable)
Dim req As New RecordRequest("MyDataSource", "ProductsTable")
req.Where.AddFilter("ProductID", 123)
Try
Dim row As DataRow = SourceRequestManager.GetRecord(req, trans)
row.Item("UnitsInStock") = CInt(row.Item("UnitsInStock")) + 10
Dim updateReq As New UpdateTableDataRequest("MyDataSource", _
"ProductsTable")
updateReq.Where.AddFilter("ProductID", 123)
updateReq.Values.Add("UnitsInStock", row.Item("UnitsInStock"))
SourceRequestManager.UpdateData(updateReq, trans)
'Ends the transaction and commits changes.
SourceRequestManager.EndTransaction(trans, False)
Catch ex As Exception
'Ends the transaction and rolls back changes.
SourceRequestManager.EndTransaction(trans, True)
End Try
Incremental Updates
Pessimistic updates can be costly
in terms of performance and scalability.
Optimistic approaches, while less
costly, will fail to update a record if it has been changed between the time of
retrieval and update. Auto SQL introduces the concept of
incremental updates, which solves the problems of both the pessimistic and
optimistic approaches in certain cases. Specifically, if you have a numeric field
that increments or decrements its value on update, it may be a good candidate.
Typically, fields are updated using
a straight value assignment such as AccountBalance = 150. Incremental updates,
on the other hand, add (or subtract) a change in value to the current value of
the underyling field: AccountBalance = AccountBalance + 100.
This allows the field to
safely be updated, even if its value has been changed elsewhere between the
time of retrieval and update.
To enable a property to use
incremental updates, simply mark it as such in the Auto SQL Generator.
You do not have to use any special code in your project class; continue to use the object as you normally would. When you
update/save the object, Auto SQL will automatically perform the
incremental update. Note that you still have the option of using incremental
updates in requests, even if you have not marked a field as such.
See examples
below.
Mapped Class
Dim prod As Product = Product.GetProduct(908324)
'Property has been marked to use incremental updates so
'no special code is required.
prod.UnitsInStock += 10
prod.Save()
UpdateRequest
'Request to increase the number of units in stock by 10.
Dim req As New UpdateRequest(GetType(Product))
req.Values.Add("UnitsInStock", 10, _
DataUpdateAction.IncrementalValue)
req.Criteria.AddFilter("ProductID", 908324)
Data Library
'Request to increase the number of units in stock by 10.
Dim req2 As New UpdateTableDataRequest("ProductsTable")
req2.Values.Add("UnitsInStock", 10, _
DataUpdateAction.IncrementalValue)
req2.Where.AddFilter("ProductID", 908324)
Obtaining Information for Data Sources and Generated Classes
You may have a need to retrieve
meta-data for you data sources, generated classes, or how they relate to one another
at runtime. Auto SQL provides classes designed for this sole
purpose.
Meta-Data Providers
Use ClassInfoProvider to retrieve
information about your generated classes and their properties. Use DataLib.SourceInfoProvider
to retrieve information for your data sources, tables, and fields. Use LibAdapter.MappingInfoProvider
to see how your mapped classes and properties relate to the data source and its
tables/ fields.
Meta-Data Classes
ClassInfoProvider obtains all of its information
from MappedClassInfo and MappedPropertyInfo
objects in ProjectContext.Current.ClassInfo. A MappedClassInfo
object exists for each generated class.
A MappedPropertyInfo object exists for
each generated property in each mapped class. You can obtain references to
these objects by calling ClassInfoProvider.GetClassInfo or ClassInfoProvider.GetPropertyInfo.
Generated classes also implement a property named ClassInfo which you can use to reference
the MappedClassInfo instance for that class.
SourceInfoProvider obtains its
information from DataSource.MasterDataSet
and DataSource.SourceMemberInformation
in ProjectContext.Current.DataSources. The master data set is an ADO
.NET DataSet
object that contains schema matching your underlying database (along with
in-memory records for cached data tables). SourceMemberInformation contains a list
of all tables and views in your database along with caching and filter options.
MappingInfoProvider obtains its
information from DataSourceMapping,
ClassMapping,
and PropertyMapping
objects in ProjectContext.Current.Mappings.
See the API documentation for more
information on these classes.
IMPORTANT: If you modify the
meta-data class instances, these changes will have a global effect throughout
your application. There is no need to make modifications in most cases, but
exceptional circumstances might call for it.
Please contact support@AutoSql.net if you would like
to make changes and have any questions.
Data Binding
Web Applications
Generated mapped classes contain
members specifically designed to work with the .NET ObjectDataSource.
Once configured, your ObjectDataSource will
automatically be able to insert, update, and delete records as well as perform
sorting and paging, without writing a single line of code. You can set the
following properties of your ObjectDataSource at design
time as follows (note that the Visual Studio designer will already set
some of these values for you by default):
| Property |
Value |
|
ConflictDetection
|
CompareAllValues
|
| DataObjectTypeName
|
AutoSqlGenerated.MappedClassName
|
| DeleteMethod
|
Delete
|
| EnablePaging
|
True
|
| InsertMethod
|
Create
|
| MaximumRowsParameterName
|
maximumRows
|
| OldValuesParameterFormatString
|
original_item
|
| SelectCountMethod
|
GetCount
|
| SelectMethod
|
GetMappedClassNameList
|
| SortParameterName
|
sortExpression
|
| StartRowIndexParameterName
|
startRowIndex
|
| TypeName
|
AutoSqlGenerated.MappedClassName
|
| UpdateMethod
|
Save
|
Once configured, you can bind the
source to any .NET control that can work with object data sources such as the GridView or DetailsView.
You can also work with lower-level
data readers and disconnected ADO .NET objects (data rows, tables, data sets)
if you choose. However, there is no built-in support for CRUD (create, update,
delete) operations, paging, and sorting as with mapped classes. Still, you can
create request objects and use the SourceRequestManager to perform any
operation. You can clone DataSource.MasterDataSet
(or any data table it contains) and fill it with records if you would like to
use disconnected data objects as your source.
Windows Forms
Generated mapped classes
automatically support data binding to windows forms controls. The preferred method of data binding is using a
MappedObjectList as the data source for a System.Windows.Forms.BindingSource. This binding source can then be used with bound controls such as System.Windows.Forms.DataGridView. The MappedObjectList is a generic list class that automatically tracks the changes made to any mapped class objects it contains. You can call MappedObjectList.PersistChanges to persist any changes to the list to the underlying data source. This includes deletions, insertions, and updates made to items in the list. Mapped classes that contain related child (one to many relationship)
or linked (many to many relationship) properties already return a MappedObjectList so creating master/detail type interfaces is usually a simple task.
Sample Binding Code
'Sample data windows forms data binding with a data grid view
'and binding source.
Private Sub frmMain_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
bindCustomers()
End Sub
Dim CustomerList As MappedObjectList(Of Customer)
Private Sub bindCustomers()
Dim customers As IMappedObjectList = Customer.GetCustomerList()
CustomerList = DirectCast(customers, MappedObjectList(Of Customer))
Dim bindingSource As New BindingSource(CustomerList, String.Empty)
'dgvCustomers is a data grid view control
Me.dgvCustomers.DataSource = bindingSource
End Sub
Private Sub btnSaveChanges_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSaveChanges.Click
CustomerList.SaveChanges()
End Sub
As with web applications, you have the option of using lower-level data readers
and disconnected ADO .NET objects (data rows, tables, data sets) if you choose.
Data source providers are
responsible for creating connections, commands, and other database-specific
objects. Auto SQL includes providers for MS SQL, MS Access, and MySQL
by default. If you would like to use a different database, you can create a
custom data source provider by creating a class that inherits from DataLib.DataSourceProvider.
If your source is OLEDB compatible, you can inherit from DataLib.OleDbProvider,
and the implementation will, by and large, already be done for you.
Once you have created your custom
provider class, place its compiled assembly file (must be a .dll) in the Providers folder (located under the main application folder of the Auto SQL Generator).
Copy any assemblies your provider references to the Dependencies folder. When you launch the Auto SQL Generator, your custom provider will automatically
be selectable in the provider's list.
When you have finished developing your application and are ready to deploy it, please make
sure to include the AutoSql.dll file (located in
the Auto SQL application directory) and your
project context file in your project output. If your project is being deployed to an environment
where untrusted users have access to your project files, please be sure to *not* include connection
string information in your project context file. For web applications, it is recommended that you place
the project context file in the App_Data folder, as this protects against
anonymous user downloads by default. If your application does
not run under full trust (common in many shared web hosting environments),
make sure that your project context
file type is XML. Note that your Auto SQL configuration file (file with a .autosql extension) is *not* required
to be anywhere in your project output, as it is only used by the generator.