Using InterBase XE7 Change Views with Visual Studio
Using InterBase XE7 Change Views with Visual Studio
InterBase Change Views are a new “subscription based” model allowing you to “subscribe” to data; once subscribed you are able to ask the database at a later time, spanning connections, for what has changed.
You can download the demo here
For full detail on the InterBase XE7 Change View feature; http://docwiki.embarcadero.com/InterBase/XE7/en/Change_Views
And for other great resources.
https://delphiaball.co.uk/2015/03/06/interbase-change-views-example-demo-skill-sprint/
https://delphiaball.co.uk/2015/02/06/interbase-change-views-part-1/
https://delphiaball.co.uk/2015/02/06/interbase-change-views-part-2-creating-change-view/
https://delphiaball.co.uk/2015/02/07/using-interbase-change-views/
These are the steps that I used to create an application with Microsoft Visual Studio 2013 and using the NEW InterBase XE7 Change View feature to sync the changes between 2 databases.
Install InterBase XE7
Registered Users can download the InterBase XE7 installer here -The free Trial is available here
Install the InterBase ODBC driver
Registered users can download the InterBase ODBC installer here
Run a script to create both the local and remote databases that have the new InterBase XE7 Change View feature that contain subscriptions
The following is an InterBase SQL script that will create the sample database that is used in the Demo.
If you have never used isql to create a database here is a good link http://docwiki.embarcadero.com/InterBase/XE7/en/To_Run_a_SQL_Script_Using_the_Command-line_isql_Tool
CREATE DATABASE 'C:\SUB.IB'
USER 'SYSDBA' PASSWORD 'masterkey'
DEFAULT CHARACTER SET UTF8;
CREATE GENERATOR gen;
CREATE TABLE tab (
id DECIMAL(10,0) NOT NULL PRIMARY KEY,
name VARCHAR(20)
);
SET TERM ^ ;
CREATE TRIGGER tr_tab_before_ins FOR tab
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.id IS NULL) THEN
BEGIN
NEW.id = GEN_ID( gen, 1);
END
END
^
CREATE TRIGGER tr_tab_after_ins FOR tab
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'TAB';
END
^
CREATE TRIGGER tr_tab_after_upd FOR tab
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'TAB';
END
^
CREATE TRIGGER tr_tab_after_del FOR tab
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'TAB';
END
^
SET TERM ; ^
CREATE SUBSCRIPTION subinsertup ON tab FOR ROW (INSERT, UPDATE);
CREATE SUBSCRIPTION subdelete ON tab FOR ROW (INSERT, UPDATE);
INSERT INTO tab (name) VALUES ('aaa');
INSERT INTO tab (name) VALUES ('bbb');
INSERT INTO tab (name) VALUES ('ccc');
SET SUBSCRIPTION SUBDELETE ACTIVE;
select * from tab;
SET SUBSCRIPTION SUBDELETE INACTIVE;
SET SUBSCRIPTION SUBINSERTUP ACTIVE
select * from tab;
After running the script, copy the database C:\Sub.ib to c:\Sub2.ib to be used as the remote database for demonstration purposes.
Create an ODBC data source for the local database
Create an ODBC data source for the remote database
Create a new Visual Studio Project C# Windows Form Application
In Visual Studio select File->New->Visual C# -> Widows Forms Application
This will create a blank Windows Application.
Add Components to your form
The DataGridView control provides a great way to display data in a tabular format.
View->Toolbox and type DataGridView. We will add 4 of these components for displaying our different Datasets and subscriptions.
The 4 DataGridViews will be for
-
The Local Dataset
-
The Remote Dataset
-
The Subscription that tracks Inserts and Updates
-
The Subscription that tracks Deletes
In Addition to the DataGridViews, this Demo will use 3 Buttons and one BindingNavigator Component. We will bind the BindingNavigator Component to the Local Dataset to allow for easy updates, inserts and deletes to the Data.
The 3 Buttons will be for
-
Opening our databases
-
Committing the Edit from the Local Dataset and viewing the Changes
-
Merging the data to our remote Dataset
Connect to both Databases
Because we already setup our ODBC Data Sources earlier connecting to our 2 Databases will be simple.
First we will set up simple methods to connect to our DataSources
public OdbcConnection getConnection()
{
OdbcConnection c1 = new OdbcConnection("DSN=SUBSCRIPTION");
return c1;
}
public OdbcConnection getRemoteConnection()
{
OdbcConnection c1 = new OdbcConnection("DSN=SUBSCRIPTIONREMOTE");
return c1;
}
Then will have our BtnOpen Click Event handler connect both Databases.
private void BtnOpen_Click(object sender, EventArgs e)
{
OdbcConnection c = new OdbcConnection();
c = getConnection();
OdbcDataAdapter DataAp1 = new OdbcDataAdapter("select * from tab order by ID", c);
DataAp1.Fill(dsOriginal, "tab");
bindingSource1.DataSource = dsOriginal.Tables[0];
DgOriginal.DataSource = bindingSource1;
bindingNavigator1.BindingSource = bindingSource1;
c.Close();
BtnOpenDb.Enabled = false;
dgRemote.Enabled = false;
GetRemote();
}
private void GetRemote(){
OdbcConnection remotecon = new OdbcConnection();
remotecon = getRemoteConnection();
OdbcDataAdapter datapRemote = new OdbcDataAdapter("select * from tab order by ID", remotecon);
dsRemote.Tables.Clear();
datapRemote.Fill(dsRemote);
bs.DataSource = dsRemote.Tables[0];
dgRemote.DataSource = bs;
remotecon.Close();
}
The local data is setup to be editable, while the remote database will be read only from the form.
Edit your Data
Use the Binding Navigator and the Local DataGridView to add, insert or update the data.
See the changes
The Commit Button Click Event will commit the changes we have made and then read what has changed.
private void btnCommit_Click(object sender, EventArgs e)
{
if (dsOriginal.HasChanges())
{
OdbcConnection c = getConnection();
OdbcDataAdapter DataAp1 = new OdbcDataAdapter();
DataAp1.SelectCommand = new OdbcCommand("select * from tab", c);
OdbcCommandBuilder builder = new OdbcCommandBuilder(DataAp1);
DataAp1.Update(dsOriginal, "tab");
c.Close();
DgDelete.Enabled = false;
DgUpdate.Enabled = false;
ReadData(getConnection(), "SubInsertUp", DgUpdate);
ReadData(getConnection(), "SubDelete", DgDelete);
}
else
{
MessageBox.Show("No Changes detected to orginal Dataset");
}
}
public void ReadData(DbConnection conn, String sqlstring, DataGridView dg)
{
string sqlSubscribe = "set subscription " + sqlstring +" active;";
string sqlCommit = "Commit;";
string sqlSelect = "Select * from TAB;";
conn.Open();
DbCommand cmd = conn.CreateCommand();
DbTransaction sqltrans = conn.BeginTransaction(IsolationLevel.RepeatableRead);
cmd.Transaction = sqltrans;
cmd.CommandText = sqlSubscribe;
cmd.ExecuteNonQuery();
cmd.CommandText = sqlSelect;
DbDataReader myreader = cmd.ExecuteReader();
dg.DataSource = myreader;
DataSet ds = new DataSet();
ds.Clear();
ds.Tables.Clear();
DataTable dt = new DataTable("TAB");
ds.Tables.Add(dt);
ds.Load(myreader, LoadOption.PreserveChanges, ds.Tables[0]);
// Load the DataGrid
dg.DataSource = ds.Tables[0];
myreader.Close();
cmd.CommandText = sqlCommit;
cmd.ExecuteNonQuery();
conn.Close();
}
Merge the data
Now that we see Changes, we can apply the Changes to our Remote Dataset. The record with ID 2 will be removed and the record with ID 5 will be added.
private void btnMerge_Click(object sender, EventArgs e)
{
DataTable tbl1 = DgUpdate.DataSource as DataTable;
DataTable tbl2 = DgDelete.DataSource as DataTable;
MergeData(tbl1, "SubInsertUp");
MergeData(tbl2, "SubDelete");
GetRemote();
}
// MergeData will check if its an update/insert/ or delete and apply the proper sql command.
public void MergeData(DataTable dt, String sqlstring)
{
OdbcConnection c = getRemoteConnection();
c.Open();
DbCommand cmd1 = c.CreateCommand();
if (sqlstring.Equals("SubInsertUp"))
{
foreach (DataRow row in dt.Rows)
{
string id = row["ID"].ToString();
string name = row["NAME"].ToString();
cmd1.CommandText = "insert into TAB values( " + id + ", '" + name + "' )";
try
{
//check if its an update
cmd1.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
//execute the update
cmd1.CommandText = "Update Tab set ID=" + id + ",Name='" + name + "' where id =" + id;
cmd1.ExecuteNonQuery();
}
}
}
else
{
foreach (DataRow row in dt.Rows)
{
string id = row["ID"].ToString();
string name = row["NAME"].ToString();
cmd1.CommandText = "delete from tab where ID =" + id;
try
{
cmd1.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
c.Close();
}
Comments
-
Please login first in order for you to submit comments