Getting started

XDataAccess defines two objects to interact with a database: XSqlHelper and XDAO (take a look at the API).

XSqlHelper is wrapper over the SqlHelper object of the DAAB v2 with support to execute querys getting the sql text from the XML Sql catalog, and create sql commands by using reflection over a reference object, typically a business entity.

XDAO is a generic Data Access Object, wich provides methods for creating, reading, updating, and deleting objects. XDAO is oriented to handling objects persistence, based on existing metadata in the xml catalog.

Both XSqlHelper, and XDAO rely on an XML Sql catalog definition, wich defines the mapping between ID tokens and sql text, and between a .NET type and sql text.

XSqlHelper samples

Query Regions

The following sample retrieves rows from the Regions table, in a DataSet.

C# source code

string sqlClientFileLocation = @"C:\temp\northwind.xml";
XPathDocument xdoc = new XPathDocument(sqlClientFileLocation);
DataSet dsRegions = XSqlHelper.ExecuteDataset(xdoc, "GetRegions");

XML SQL catalog element

<transaction id="GetRegions">
	<query>SELECT * FROM [REGION]</query>
</transaction>

Create Customer

The following sample inserts a new customer. Command text is taken from the nonquery element. Command parameters are created using reflection over a reference object (customer in this case), based on the parameters element declaration.

C# source code

XPathDocument xdoc = new XPathDocument(sqlClientFileLocation);

Customer customer = new Customer("XCORP", "XDataCorp");
customer.ContactName = "Jhon Doe";
customer.ContactTitle = "Msc.";

//insert customer
XSqlHelper.ExecuteNonQuery(xdoc, "CreateCustomer", customer);

XML SQL catalog element

<transaction id="CreateCustomer">
	<nonquery>
	INSERT INTO Customers
	(CustomerId, CompanyName, ContactName, ContactTitle)
	values(@customerId, @companyName, @contactName, @contactTitle)
	</nonquery>
	<parameters>
		<param name="@customerId" property="CustomerID"/>
		<param name="@companyName" property="CompanyName"/>
		<param name="@contactName" property="ContactName"/>
		<param name="@contactTitle" property="ContactTitle"/>
	</parameters>
</transaction>

Querying Territories using filters

The following sample retrieves territories in region 1. SQL Where subexpression is created dynamically.

C# source code

//create filter
Object [][] condition = new Object[1][];
condition[0] = new object[3] {"RegionID", "=", 1};
IXDataFilter filter = DataFilterFactory.CreateDataFilter(condition);

XPathDocument xdoc = new XPathDocument(sqlClientFileLocation);
//query
DataSet dsTerritories =
		XSqlHelper.ExecuteDataset(xdoc, "GetTerritories", filter);

XML SQL catalog element

<transaction id="GetTerritories">
	<query>SELECT * FROM [TERRITORIES]</query>
</transaction>

XDAO samples

Create customer revisited

The following sample creates a customer object. The main difference with XSqlHelper create customer is that mapping is resolved based on .NET type and command type declaration in the XML sql catalog. Notice the type and command attributes in the transaction element.

C# source code

XPathDocument xdoc = new XPathDocument(sqlClientFileLocation);

Customer customer = new Customer("XCORP", "XDataCorp");
customer.ContactName = "Jhon Doe";
customer.ContactTitle = "Msc.";

//create order
XDAO.CreateObject(xdoc,customer);

XML SQL catalog element

<transaction
	id="CreateCustomer"
	type="Simbiosis.XDataAccess.Tests.Customer"
	command="insert">
	<nonquery>
	INSERT INTO Customers
	(CustomerId, CompanyName, ContactName, ContactTitle)
	values(@customerId, @companyName, @contactName, @contactTitle)
	</nonquery>
	<parameters>
		<param name="@customerId" property="CustomerID"/>
		<param name="@companyName" property="CompanyName"/>
		<param name="@contactName" property="ContactName"/>
		<param name="@contactTitle" property="ContactTitle"/>
	</parameters>
</transaction>

Create Order

The following sample creates an Order object. There are some things to highlight in this case:

1. This object contains detail objects, OrderDetail in this case, and is through the nestedTransaction element, in the xml catalog that the master detail pattern is supported. There is no restriction on the number of nesting levels that could be implemented.
2. Notice the GETDATE() invocation in the create order sql text, this function can be used in SqlServer, but not in MS Access or other DBMS, this is the sort of thing that must be customized for each DBMS.
3. Notice the scalar element in the create order transaction (after the nonquery element), it declares an execute scalar invocation, whose result will be set in OrderID property of the reference object. This is used to support autonumbered (aka identity fields) fields in the database, where the id is created by the DBMS, read and set into the object by the library, before the nested transactions are executed. All the nonquery and scalar elements inside a transaction element are wrapped in a single database transaction.
4. Should the transaction represent an update over the Order object, then the xml catalog should have declared the insert, update, and delete operations for the detail object (not only the insert as in this case).

C# source code

XPathDocument xdoc = new XPathDocument(sqlClientFileLocation);

//populate order object
Order order = new Order();
order.CustomerID = "ALFKI";
order.EmployeeID = 5;
order.Freight = Decimal.Parse("100.3");
order.ShipVia = 1;
order.OrderID = -1;
order.ShipName = "Pluna S.A. Montevideo";
order.ShipAddress = "Miraflores 1553";

//populate order details object
OrderDetail detail1 = new OrderDetail();
detail1.ProductID = 12;
detail1.Quantity = 1;
detail1.UnitPrice = Decimal.Parse("50.52");
order.OrderDetails.Add(detail1);

OrderDetail detail2 = new OrderDetail();
detail2.ProductID = 40;
detail2.Quantity = 2;
detail2.UnitPrice = Decimal.Parse("60.52");
order.OrderDetails.Add(detail2);

//create order
XDAO.CreateObject(xdoc,order);




XML SQL catalog element

<transaction id="CreateOrder"
		type="Simbiosis.XDataAccess.Tests.Order"
		command="insert">
	<nonquery>
INSERT INTO [ORDERS]
(
	[CUSTOMERID],
	[EMPLOYEEID],
	[ORDERDATE],
	[REQUIREDDATE],
	[SHIPVIA],
	[FREIGHT],
	[SHIPNAME],
	[SHIPADDRESS],
	[SHIPCITY],
	[SHIPREGION],
	[SHIPPOSTALCODE],
	[SHIPCOUNTRY]
)

VALUES
(
	@CUSTOMERID,
	@EMPLOYEEID,
	GETDATE(),
	@REQUIREDDATE,
	@SHIPVIA,
	@FREIGHT,
	@SHIPNAME,
	@SHIPADDRESS,
	@SHIPCITY,
	@SHIPREGION,
	@SHIPPOSTALCODE,
	@SHIPCOUNTRY
)
	</nonquery>
	<scalar property="OrderID">
select MAX([ORDERID]) from orders
	</scalar>
	<parameters>
		<param name="@CUSTOMERID" property="CUSTOMERID"/>
		<param name="@EMPLOYEEID" property="EMPLOYEEID"/>
		<param name="@REQUIREDDATE" property="REQUIREDDATE"/>
		<param name="@SHIPVIA" property="SHIPVIA"/>
		<param name="@FREIGHT" property="FREIGHT"/>
		<param name="@SHIPNAME" property="SHIPNAME"/>
		<param name="@SHIPADDRESS" property="SHIPADDRESS"/>
		<param name="@SHIPCITY" property="SHIPCITY"/>
		<param name="@SHIPREGION" property="SHIPREGION"/>
		<param name="@SHIPPOSTALCODE" property="SHIPPOSTALCODE"/>
		<param name="@SHIPCOUNTRY" property="SHIPCOUNTRY"/>
	</parameters>
	<nestedTransactions>
		<nestedTransaction
			ref="CreateOrderDetail"
			type="Simbiosis.XDataAccess.Tests.OrderDetail"
			collectionProperty="OrderDetails"/>
	</nestedTransactions>
</transaction>
<transaction id="CreateOrderDetail"
		type="Simbiosis.XDataAccess.Tests.OrderDetail"
		command="insert"
		container="Simbiosis.XDataAccess.Tests.Order">
	<nonquery>
INSERT INTO [Order Details]
(
	[OrderID],
	[ProductID],
	[UnitPrice],
	[Quantity],
	[Discount]
)

VALUES
(
	@ORDERID,
	@PRODUCTID,
	@UNITPRICE,
	@QUANTITY,
	@DISCOUNT
)
	</nonquery>
	<parameters>
		<param name="@ORDERID" containerProperty="ORDERID"/>
		<param name="@PRODUCTID" property="PRODUCTID"/>
		<param name="@UNITPRICE" property="UNITPRICE"/>
		<param name="@QUANTITY" property="QUANTITY"/>
		<param name="@DISCOUNT" property="DISCOUNT"/>
	</parameters>
</transaction>

Feedback

Please send questions and/or feedback!, you can do so by email, or by using the forums.

[ + ] Start a new thread [ - ] Start a new thread

Subject:

Message:

HTML tags will display in your post as text

Go to open discussion forum