Tutorial – how to use it

1. Create Connection String
2. Set Default Server
3. Use Database Table
4. Handle Table Items
5. Listen Item Events
6. Execute Standard Queries
7. Execute Transactional Queries
8. Execute Stored Procedure
9. Manage Concrete classes like ORMs and Entity Framework
10. Export Data to Excel
11. Simple table searches
12. Server tables, views, users

1. Create Connection String

DbxSettings settings = new DbxSettings(DbxServerType.SQLServer, "Server=myserver;Database=mydb;User ID=username;Password=password");

// OR – Get from Registry
settings = new DbxSettings(Microsoft.Win32.Registry.CurrentUser, @"Software\YourApplicationName");
settings.LoadSettings();

// Save to registry, if required
if
(string.IsNullOrEmpty(settings.ConnectionString)){
     settings.SaveSettings(
DbxServerType.SQLServer, "new connection string...");
     settings.LoadSettings();
}
  

2. Set Default Server

DbxServer.GetServer(settings, true); 

3. Use Database Table

NOTE: DbxDynamicTable and DbxDynamicTableItem classes works with tables having single-primary-key column GUID/nchar(36) or equivalent

var table = new DbxDynamicTable("Contacts");
// OR
table = new DbxDynamicTable("Contacts", "ContactID");

table.TableName;
table.KeyColumn;                                    // primary-key column name
table.Count();                                      // total rows in database-table
string[] columnNames = table.ColumNames();          // All columns in a table

// Table Column info
string columnName = "JobTitle";
bool columnExists = table.ColumnExists(columnName); // Column exists or not in table
int columnIndex = table.ColumnIndex(columnName);    // get column index by name
columnName = table.ColumnName(columnIndex);         // get column name by index

// Table Column data type info
DbxColumnNetDataType cType = table.ColumnNetDataType(columnName);   // i.e. String, Int32, Decimal, Binary, XML etc.
string providerTypeName = table.ColumnProviderTypeName(columnName); // i.e. NVarChar, VarChar, Decimal etc.
int providerType = table.ColumnProviderType(columnName);            // i.e. integer value of data types, like 12 for NVarChar 

4. Handle Table Items

// Find item by primary key value
string contactID = "10BD0BAA-73C3-85F8-0C5F-39C129798BA9";
var itemFound = table.FindItem(contactID);

// Create new item
var item = table.NewItem();

// Set some values
item.SetValue("FullName", "Mr. My Name");
item.SetValue(
"EmailAddress", "myname@localhost.com");
item.SetValue(
"JobTitle", "Developer");
item.Save();                                    
// Insert new Item in database-table
item.Reload();                                   // Reload item from database-table

// modify item
item.SetValue("JobTitle", "Senior Developer");
bool isItemModified = item.IsModified;

item.Save();                                     // Update item in database-table
item.Delete();                                   // Delete item from database-table
item.Save();                                     // Insert same-item again in database-table
item.Save();                                     // Update item again

// check the last-operation performed on this item
int lastOperation = item.ItemLastOperationType;  // 0-none, 1-Inserted, 2-Updated, 3-Deleted

// get item column-value
object itemValue = item.GetValue("FullName");
item.Exists();                                  
// item exists in database-table 

// Limiting columns for Insert/Update operations, restrict users as per their privileges for add/edit operations
item = table.NewItem();
item.ColumnNames_ForInsert =
new string[] { item.KeyColumn, "FullName", "EmailAddress", "JobTitle" };
item.ColumnsNames_ForUpdate =
new string[] { item.KeyColumn, "JobTitle" };

// Set some values
item.SetValue("FullName", "Another Name");
item.SetValue(
"EmailAddress", anothername@localhost.com);
item.SetValue(
"JobTitle", "Developer");
item.Save();                               
// insert item in databse-table

// modify some values
item.SetValue("FullName", "New Name");
item.SetValue(
"JobTitle", "Software Engineer");
item.Save();                               
// update item for "JobTitle" column only in database table
item.Reload();

item.ColumnNames_ForInsert = null;          // reset item back to use all table-columns for insert
item.ColumnsNames_ForUpdate = null;         // reset item back to use all table-columns for update

5. Listen Item Events

item.Saved += item_Saved;                   // Raised after item Inserted/Updated in database
item.Deleted += item_Deleted;               // Raised after item Deleted from database

item.EnableValueChangeEvent = true;         // enable/disable event
item.ValueChanged += item_ValueChanged;

void item_Saved(object sender, EventArgs e)
{
     DbxDynamicTableItem item = sender as DbxDynamicTableItem;
     bool inserted = item.ItemLastOperationType == 1;
     bool updated = item.ItemLastOperationType == 2;
}

static void item_Deleted(object sender, EventArgs e)
{
     DbxDynamicTableItem item = sender as DbxDynamicTableItem;
     bool deleted = item.ItemLastOperationType == 3;
}

static void item_ValueChanged(object sender, DbxTableItemValueChanged e)
{
    
DbxDynamicTableItem item = sender as DbxDynamicTableItem;
// e.ColumnName,
// e.ColumnDataType,
// e
.NewValue,
// e.OldValue
}

6. Execute Standard Queries

string sql = "Select FirstName, LastName, FullName, EmailAddress from Contacts";

// Execute query
Object result = new DbxQuery(sql).Execute();
result =
new DbxQuery(sql, DbxQueryResultType.DataSet).Execute();
result =
new DbxQuery(sql, DbxQueryResultType.DataTable).Execute();
result =
new DbxQuery(sql, DbxQueryResultType.SingleRow).Execute();
result =
new DbxQuery(sql, DbxQueryResultType.SchemaOnly).Execute();

// Execute Scalar
sql = "Select FullName From Contacts Where ContactID = '" + Guid.Empty.ToString() + "'";
result =
new DbxQuery(sql, DbxQueryResultType.Scalar, CommandType.Text).Execute();

// Parameterized, Scalar
sql = "Select FullName From Contacts Where ContactID = @ContactID";
DbxQuery q = new DbxQuery(sql, DbxQueryResultType.Scalar);
q.Parameters.Add(
"ContactID", Guid.Empty.ToString());
result = q.Execute();

// Parameterized, NonQuery
sql = "Update Contacts Set JobTitle = @JobTitle Where ContactID = @ContactID";
q =
new DbxQuery(sql, DbxQueryResultType.NonQuery);
q.Parameters.Add(
"JobTitle", "CEO");
q.Parameters.Add(
"ContactID", Guid.Empty);
result = q.Execute();

7. Execute Transactional Queries

DbxQuery q = null;
try
{ 
     string sql = "INSERT INTO Contacts (ContactID, FullName, LastName, FirstName) VALUES (newid(), 'tID:..', 'Last Name', 'First Name')";
     q = new DbxQuery(sql, DbxQueryResultType.NonQuery);
     string tID = q.BeginTransaction();

     // OR
     // string tID = q.BeginTransaction("myNewCustomTransaction_ID");
     // string tID = q.BeginTransaction(Guid.NewGuid().ToString());

     object result = q.Execute();

     // 2nd query
     sql = "INSERT INTO Contacts (ContactID, FullName, LastName, FirstName) VALUES (newid(), 'tID: " + tID + "', 'Last Name2', 'First Name2')";
     q =
new DbxQuery(sql, DbxQueryResultType.NonQuery) { TransactionID = tID }; 
     result = q.Execute(); 
     q.CommitTransaction();
}
catch (Exception)
{
  
q.RollbackTransaction();
}

8. Execute Stored Procedure

string sql = "spTestOutParameter";
var q = new DbxQuery(sql, DbxQueryResultType.DataSet, CommandType.StoredProcedure);
q.Parameters.Add(
new DbxParameter("SettingName", "hello world.....") { Direction = ParameterDirection.Output });
DataSet result = q.Execute() as DataSet;

// Get Parameter value set by SP, using Index
object pValue = q.Parameters[0].Value;

// Get Parameter value set by SP, using Name
pValue = q.Parameters.GetByName("SettingName").Value;

9. Manage Concrete classes like ORMs and Entity Framework

Create simple concrete classes and/or parent/child classes in your own style and then use them like this

DbxDynamicTable table = new DbxDynamicTable("Contacts", "ContactID");

ContactItem contact = new ContactItem(table.NewItem());
contact.FullName =
"Contact one";
contact.EmailAddress =
"contact@contact.com";
contact.JobTitle =
"Sale Representative";
contact.Save();

public class ContactItem
{
    
DbxDynamicTableItem item = null;
    
public ContactItem(DbxDynamicTableItem item)
    
{
         
this.item = item;
    
}

     public DbxDynamicTableItem Item { get { return item; } }

public string ID
{
    
get { return item.KeyColumnValue; }
}

public string FullName
{
     get { return item.GetValue("FullName") as string; }
     set { item.SetValue("FullName", value); }
}

public string EmailAddress
{
    
get { return item.GetValue("EmailAddress") as string; }
    
set { item.SetValue("EmailAddress", value); }
}

public string JobTitle
{
    
get { return item.GetValue("JobTitle") as string; }
    
set { item.SetValue("JobTitle", value); }
}

public void Save() { item.Save(); }
public void Delete() { item.Delete(); }
public void Reload() { item.Reload(); }

// TODO: add more code .....
}

10. Export Data to Excel

string sql = "Select FirstName, LastName, FullName, EmailAddress from Contacts";

// Execute query
DataTable result = new DbxQuery(sql, DbxQueryResultType.DataTable).Execute() as DataTable;
List<DataRow> selectedRows = null;

//Export DataTable or selectedRows to Excel .xlsx
Byte[] bin = mye.DbxServers.excelservices.ExcelAgent.ExportData(result, selectedRows);
string file = "DataExported_" + Guid.NewGuid().ToString() + ".xlsx";
File.WriteAllBytes(file, bin);

// Open it in Excel
ProcessStartInfo pi = new ProcessStartInfo(file);
Process.Start(pi);

11. Simple table searches

// Get All table items in database, including all table-columns
DataSet ds = table.Select();

// Get All table items in database, including 3 table-columns only
ds = table.Select(new string[] { "ContactID", "FullName", "EmailAddress" });

// Get item, using primary key
row = table.Select(contactID);

// Get items, using primary keys
List<string> idsList = new List<string>(new string[] { contactID, Guid.NewGuid().ToString(), Guid.Empty.ToString() });
ds = table.Select(idsList);

// Get items, using Foreign Key column exists in same(Contacts) table
ds = table.Select("CompanyID", "C2C59801-D65C-B72D-2059-39C0536C9699");

// Get items, using any single column, applying operators like Contains, EndWidth, StartWith, Equal etc. etc.
table.Select("EmailAddress", "@microsoft.com", DbxFilterComparisonOperator.Contains);
table.Select(
"EmailAddress", "@microsoft.com", DbxFilterComparisonOperator.NotContains);
table.Select(
"EmailAddress", "@microsoft.com", DbxFilterComparisonOperator.EndWith);
table.Select(
"EmailAddress", "@microsoft.com", DbxFilterComparisonOperator.NotEndWith);
table.Select(
"EmailAddress", "@microsoft.com", DbxFilterComparisonOperator.StartWith);
table.Select(
"EmailAddress", "@microsoft.com", DbxFilterComparisonOperator.NotStartWith);

// Get items via Linking tables, parent key column name, parent id
DbxDynamicTable addresses = new DbxDynamicTable("Addresses", "AddressID");

// returns all Addresses linked with given Customer(Customers table),
// using linking table "LinksCustomerAddress" table having columns like (AddressID, CustomerID, ...)
ds = addresses.Select("LinksCustomerAddress", "CustomerID", "1C811A4C-AA6A-4658-89A9-C9FEEF87DDC6");

12. Server tables, views, users

// SWITCH database server for this TABLE INSTANCE ONLY
var newServer = DbxServer.GetServer(new DbxSettings(DbxServerType.SQLServer, "connection string"));
var mySqlServer = DbxServer.GetServer(new DbxSettings(DbxServerType.MySQL, "connection string"));

table.ChangeServer(newServer); // use new server
table.ChangeServer(null); // reset it back to Application-level default server

// SWITCH database server at Application level
DbxServer.SetDefaultServer(mySqlServer);

// Get server currently in use by item.table instance
DbxServer server = item.ParentServer();
string dbName = server.DatabaseName;
server.TestConnection();

var dt = server.Schema_Tables();
dt = server.Schema_Views();
dt = server.Schema_Columns(tableName);
dt = server.Schema_PrimaryKeys(tableName);
columnName = server.Schema_PrimaryKeyName(dt);
dt = server.Schema_ForeignKeys(tableName);
dt = server.Schema_IndexColumns(tableName);
dt = server.Schema_Indexes(tableName);
dt = server.Schema_Procedures();
dt = server.Schema_Users_AllOnServer();

// Traditional way: Handle, connection, command, transaction objects etc., yourself
IDbConnection con = server.Connection();

13. Interested to explore more

Check Toolkit demo source code with comments, available under downloads tab.

Last edited Feb 13, 2014 at 9:23 AM by MYousaf, version 6