This topic lists tips and best practices that may be helpful when working with DataObjects for .NET. The following tips were compiled from frequently asked user questions posted in the C1DataObjects forum.
To access the field values of the current row, get the current row from the CurrencyManager object (which can be obtained via the BindingContext property) and then invoke the FromDataItem method to obtain the currently selected row's corresponding C1DataRow object. If you need typed access you can pass a C1DataRow object to the static Obj() method of the corresponding TableView row object from the DataLibrary.DataObjects assembly.
For example:
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
Imports C1.Data
Imports DataLibrary.DataObjects.MyDataSet
Private Sub EditButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles EditButton.Click
Dim cm As CurrencyManager cm = _
CType(BindingContext(C1DataTableSource1), CurrencyManager)
If cm IsNot Nothing AndAlso cm.Count > 0 AndAlso cm.Position >= 0 Then
Dim row As C1DataRow = C1DataRow.FromDataItem(cm.Current)
' untyped access
row("UnitsInStock") = 1234
' typed access
Dim product As ProductsRow = ProductsRow.Obj(row)
product.UnitPrice += 4D
cm.EndCurrentEdit()
End If
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
using C1.Data;
using DataLibrary.DataObjects.MyDataSet;
private void EditButton_Click(object sender, EventArgs e)
{
CurrencyManager cm = (CurrencyManager)BindingContext[c1DataTableSource1];
if (cm != null && cm.Count > 0 && cm.Position >= 0)
{
C1DataRow row = C1DataRow.FromDataItem(cm.Current);
// untyped access
row["UnitsInStock"] = 1234;
// typed access
ProductsRow product = ProductsRow.Obj(row);
product.UnitPrice += 4m;
cm.EndCurrentEdit();
}
}
|
|
You can use the autoincremented primary key to clone a data row. Suppose you have to add a copy of the currently selected row to the same data table, for example when the user wants to add a new row based on the currently selected data row. To do so, you should create a new row, then fill it out with data from the original data row except for the primary key and the unique key fields.
For example:
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
Private Sub CloneRowButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles CloneRowButton.Click
' Get the currently selected row
Dim CurrentRow As C1DataRow = C1DataRow.FromDataItem( _
BindingContext(C1DataSet1, "Shippers").Current)
' Get data from the original row as an item array
Dim Arr() As Object = CurrentRow.ItemArray
' Create a new data row
Dim NewRow As C1DataRow = C1DataSet1.TableViews("Shippers").AddNew()
' Preserve autoincremented primary key
Arr(0) = NewRow("ShipperID")
' Fill out the unique key fields
Arr(1) = CompanyName_TextBox.Text
' Assign the item array
NewRow.ItemArray = Arr
' End the edit on the new row
NewRow.EndEdit()
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
private void CloneRowButton_Click(object sender, EventArgs e)
{
// Get the currently selected row
C1DataRow currentRow = C1DataRow.FromDataItem(
BindingContext[C1DataSet1, "Shippers"].Current);
// Get data from the original row as an item array
object[] arr = currentRow.ItemArray;
// Create a new data row
C1DataRow newRow = C1DataSet1.TableViews["Shippers"].AddNew();
// Preserve autoincremented primary key
arr[0] = newRow("ShipperID");
// Fill out the unique key fields
arr[1] = CompanyName_TextBox.Text;
// Assign the item array
newRow.ItemArray = arr;
// End the edit on the new row
newRow.EndEdit();
}
|
|
You may want to specify a date constant in the client-side filter condition or how to display the set of rows between two given dates. The following code demonstrates one possible example of doing so:
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
Private Sub FilterButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles FilterButton.Click
Dim date1 As DateTime = From_DateTimePicker.Value.Date
Dim date2 As DateTime = To_DateTimePicker.Value.Date.AddDays(1)
C1ExpressView1.RowFilter = String.Format( _
"OrderDateTime >= #{0}-{1}-{2}# AND OrderDateTime < #{3}-{4}-{5}#",_
date1.Year, date1.Month, date1.Day, _
date2.Year, date2.Month, date2.Day)
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
private void FilterButton_Click(object sender, EventArgs e)
{
DateTime date1 = From_DateTimePicker.Value.Date;
DateTime date2 = To_DateTimePicker.Value.Date.AddDays(1);
C1ExpressView1.RowFilter = string.Format(
"OrderDateTime >= #{0}-{1}-{2}# AND OrderDateTime < #{3}-{4}-{5}#",
date1.Year, date1.Month, date1.Day,
date2.Year, date2.Month, date2.Day);
}
|
|
When working with DataObjects for .NET you can filter data either on the client side (using the RowFilter property) or on the server side. Filtering on the server minimizes the amount of data passed between the server and client and improves performance.
There are two ways you can set the filter conditions:
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim filters As New FilterConditions()
filters.Add(New FilterCondition(Nothing, "Suppliers", _
"Country = 'France'"))
filters.Add(New FilterCondition(Nothing, "Products", _
"SupplierID IN (SELECT SupplierID From Suppliers WHERE " + _
"Country = 'France')"))
C1DataSet1.Fill(filters, False)
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
private void Form1_Load(object sender, EventArgs e)
{
FilterConditions filters = new FilterConditions();
filters.Add(new FilterCondition(null, "Suppliers",
"Country = 'France'"));
filters.Add(new FilterCondition(null, "Products",
"SupplierID IN (SELECT SupplierID From Suppliers WHERE " +
"Country = 'France')"));
C1DataSet1.Fill(filters, False);
}
|
|
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
Private Sub C1DataSet1_BeforeFill(ByVal sender As System.Object, _
ByVal e As C1.Data.FillEventArgs) Handles C1DataSet1.BeforeFill
e.Filter.Add(New FilterCondition(Nothing, "Suppliers", _
"Country = 'France'"))
e.Filter.Add(New FilterCondition(Nothing, "Products", _
"SupplierID IN (SELECT SupplierID FROM Suppliers WHERE " + "Country = 'France'")))
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
private void C1DataSet1_BeforeFill(object sender, C1.Data.FillEventArgs e)
{
e.Filter.Add(new FilterCondition(null, "Suppliers",
"Country = 'France'"));
e.Filter.Add(new FilterCondition(null, "Products",
"SupplierID IN (SELECT SupplierID FROM Suppliers WHERE " + "Country = 'France'")));
}
|
|
If you want to execute some SQL statements that update a group of rows you can obtain the connection object from DataObjects for .NET. It is also possible to start an SQL transaction using C1.Data.SchemaObjects.Connection and perform all data manipulations as a single logical unit.
For example:
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
Imports C1.Data
Imports C1.Data.SchemaObjects
Imports System.Data.OleDb
Private Sub BatchUpdateButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim N As Int32 = 0
Dim c As Connection = C1DataSet1.Schema.Connections(0)
c.Open()
c.BeginTransaction()
Try
Dim oc As OleDbConnection = CType(c.DbConnection, OleDbConnection)
Dim ot As OleDbTransaction = CType(c.DbTransaction, OleDbTransaction)
Dim cmd As OleDbCommand
cmd = New OleDbCommand("UPDATE Products SET " + _
"ReorderLevel = ReorderLevel + 5 WHERE UnitPrice < 5", oc, ot)
N += cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = New OleDbCommand("UPDATE Products SET ReorderLevel = " + _
"ReorderLevel - 3 WHERE ReorderLevel > 40", oc, ot)
N += cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = New OleDbCommand("INSERT INTO Products (ProductName, " + _
"Discontinued, UnitPrice) VALUES ('N product', FALSE, 135)", _
oc, ot)
N += cmd.ExecuteNonQuery()
cmd.Dispose()
c.CommitTransaction()
Catch ex As Exception
c.RollbackTransaction()
End Try
' You shouldn't close the connection here. It is already
' closed within the CommitTransaction() method.
'
c.Close()
C1DataSet1.Fill()
MessageBox.Show(N.ToString() + " rows affected.")
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
using C1.Data;
using C1.Data.SchemaObjects;
using System.Data.OleDb;
private void BatchUpdateButton_Click(object sender, EventArgs e)
{
int n = 0;
Connection c = C1DataSet1.Schema.Connections[0];
c.Open();
c.BeginTransaction();
try
{
OleDbConnection oc = (OleDbConnection)c.DbConnection;
OleDbTransaction ot = (OleDbTransaction)c.DbTransaction;
OleDbCommand cmd;
cmd = new OleDbCommand("UPDATE Products SET " +
"ReorderLevel = ReorderLevel + 5 WHERE UnitPrice < 5", oc, ot);
N += cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new OleDbCommand("UPDATE Products SET ReorderLevel = " +
"ReorderLevel - 3 WHERE ReorderLevel > 40", oc, ot);
N += cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new OleDbCommand("INSERT INTO Products (ProductName, " +
"Discontinued, UnitPrice) VALUES ('N product', FALSE, 135)",
oc, ot);
N += cmd.ExecuteNonQuery();
cmd.Dispose();
c.CommitTransaction();
}
catch (Exception ex)
{
c.RollbackTransaction();
}
// You shouldn't close the connection here. It is already
// closed within the CommitTransaction() method.
//
// c.Close();
C1DataSet1.Fill();
MessageBox.Show(N.ToString() + " rows affected.");
}
|
|
If you choose to, you can change the connection string on the fly. To do so, the connection string for the data schema must first be specified in the app.config file.
For example, a connection string to the Northwind database might appear as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="MyConnStr" value="Data Source=(local)\SQLExpress;
Initial Catalog=Northwind;Integrated Security=True" />
</appSettings>
...
</configuration>
Then you would attach the following handler to the CreateSchema event:
To write code in Visual Basic
| VB |
Copy Code
|
|---|---|
'Add the following import statement to the top of the code:
Imports System.Configuration
' Add the C1SchemaDef_CreateSchema event handler:
Private Sub C1SchemaDef1_CreateSchema(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles C1SchemaDef1.CreateSchema
Dim s As String = ConfigurationManager.AppSettings("MyConnStr")
C1SchemaDef1.Schema.Connections(0).ConnectionString = s
End Sub
|
|
To write code in C#
| C# |
Copy Code
|
|---|---|
// Add the following import statement to the top of the code:
using System.Configuration;
// Add the C1SchemaDef_CreateSchema event handler:
private void C1SchemaDef1_CreateSchema(object sender, EventArgs e)
{
string s = ConfigurationManager.AppSettings["MyConnStr"];
C1SchemaDef1.Schema.Connections[0].ConnectionString = s;
}
|
|