|
|
  | Fast and Securely Accessing Any Remote Database Anywhere Using All Types of Networks
1. Purpose This article is a software development guide for accessing different remote databases anywhere with all types of networks using SocketPro remote database service. 2. SocketPro Remote Database Service Architecture The following figure illustrates the architecture of SocketPro remote database service. The figure shows a set of libraries cooperated on client and server sides, but your code will be extremely simple and easy. The following sections will show you how simple code development is.
On client side, your code will access udb.dll, which is remote database client side library and is written from ATL/COM object with open source code at the directory ..\SocketPro\DataBase\UDB\. The database library calls usocket.dll, which is SocketPro client side core library. The client core library is responsible for data communication with a remote server by TCP/IP protocol. On server side, there is a core SocketPro server library, usktpror.dll. This server core library is responsible for data communication on server side through TCP/IP protocol. The two dlls, usocket.dll and usktpror.dll, are the foundation of SocketPro with lots of key features as described at this article. On server side, there is a library named as uodbsvr.dll, which is a server side database access component, and also is a generic OLEDB consumer. This uodbsvr.dll is able to call any one of OLEDB data providers. As you can see from the above figure, SocketPro remote database service is capable to access any data source as long as a data source has either ODBC driver or an OLEDB provider. On client side, if your development environment is .NET, extra three .NET libraries, usocketnet.dll, udbnet.dll and sproadapter.dll, are required. As shown in the above figure, usocketnet.dll and udbnet.dll are .NET interop assemblies for for two ATL/COM libraries usocket.dll and udb.dll, respectively. The library sproadapter.dll is a SocketPro adapter for .NET. Note that client platforms can be Vista, Win2k3, WinXP, Pocket PC, Smartphone, Win2k, WinNT, WinMe or Win9X. All of major development languages are fully supported. 3. Available Samples There are a number of diverse samples available within SocketPro package to help your development. The directory ..\SocketPro\samples\RemoteDB contains a set of remote database samples written from both C# and VB.NET
The directory ..\SocketPro\samples\AsyWeb contains a web application with batching requests either asynchronously or concurrently for both C# and vb.net. The directory ..\SocketPro\samples\performance\csharp\PerformanceStudySocketPro and ..\SocketPro\samples\performance\vbnet\PerformanceStudySocketPro contains performance study applications for C# and VB.NET, respectively. There are also two vb6 sample applications available at ..\SocketPro\samples\others\client\vb6\dbaathome and ..\SocketPro\samples\others\client\vb6\schema\. CeDB -- A simple CE application for directly accessing a remote database from a pocket pc at the directory ..\SocketPro\samplesCE\CeDB\. There is a simple C++ sample available at the directory ..\SocketPro\samples\others\client\vc\ODBClient. Also, our SocketProAdapter for .NET 2 is written from C++/CLI. If you are a C++ developer, you should understand all of these open source code without any problem at all. 4. Use of Classes inside Namespace SocketProAdapter.ClientSide.RemoteDB There is one simple but key class CAsynDBLite, which is a wrapper for key features in the file udb.dll. Basic usages of CAsynDBLite and CAsynDBLiteExTo access a remote backend database, as shown in the simplest sample MultiParam, we need to create an instance of client socket and an instance of database client handler. CAsynDBLite m_AsyDBLite = new CAsynDBLite();
CClientSocket m_ClientSocket = new CClientSocket();
For monitoring network events like socket connection and disconnection, we need to set delegates as shown in the below. private void frmMulti_Load(object sender, EventArgs e)
{
m_AsyDBLite.Attach(m_ClientSocket);
m_ClientSocket.m_OnSocketConnected += new DOnSocketConnected(OnSocketConnected);
m_ClientSocket.m_OnSocketClosed += new DOnSocketClosed(OnSocketClosed);
}
The above call procedures keep exactly the same for all of different types of services, as shown in the tutorials one through four. To connect with a remote SocketPro server which has loaded the library uodbsvr.dll, call the method CClientSocket::Connect. m_ClientSocket.Connect("127.0.0.1", 17001);
Once socket connection is
established, we ask for remote database service and connect the remote SocketPro
server with a backend database.
private void OnSocketConnected(int hSocket, int nError)
{
if (nError == 0)
{
m_ClientSocket.SetUID("SocketPro");
m_ClientSocket.SetPassword("PassOne");
m_ClientSocket.SwitchTo(m_AsyDBLite);
m_AsyDBLite.ConnectDB("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind3.mdb");
m_ClientSocket.WaitAll(); //wait until all requests are processed
btnInsert.Enabled = m_AsyDBLite.DBConnected; //if db is connected, enable a button
}
}
At this time, database is connected, and uodbsvr.dll is ready to function as we request. private void btnInsert_Click(object sender, EventArgs e)
{
List<CParamInfo> lstParamInfo = new List<CParamInfo>();
lstParamInfo.Add(new CParamInfo());
lstParamInfo.Add(new CParamInfo());
lstParamInfo[0].m_sDBType = tagSockDataType.sdVT_WSTR;
lstParamInfo[0].m_nLen = 255 * 2; //in bytes
lstParamInfo[1].m_sDBType = tagSockDataType.sdVT_WSTR;
lstParamInfo[1].m_nLen = 255 * 2; //in bytes
m_AsyDBLite.DBErrors.Clear();
//ShipperID ignored because it is an auto-number.
m_AsyDBLite.OpenCommandWithParameters("Insert into Shippers (CompanyName, Phone) Values (?, ?)", lstParamInfo);
object[] aData = new object[8];
aData[0] = "Oracle";
aData[1] = "(111) 111-1111";
aData[2] = "Microsoft";
aData[3] = "(222) 121-1221";
aData[4] = "Google";
aData[5] = "(333) 111-1111";
aData[6] = "Yahoo!";
aData[7] = "(444) 444-4444";
//4 sets of parameter data in one batch
m_AsyDBLite.DoBatch(aData);
m_AsyDBLite.GetAttchedClientSocket().WaitAll();
if (m_AsyDBLite.DBErrors.Count != 0)
MessageBox.Show(m_AsyDBLite.DBErrors[0].m_strErrorMsg);
}
The above code demonstrates how to insert multiple sets of parameters onto a remote backend database. Of course, you can use the above approach for any other parameterized statements or stored procedures. To open a rowset and bring all of records to a client at the fastest speed, you can use the below code. //Open one rowset and set generated DataTable with the name "Table1"
m_AsynDBLite.OpenRowset(txtSQL.Text, "Table1");
//wait until all of requests are executed
m_AsynDBLite.GetAttchedClientSocket().WaitAll();
If you want to directly bind a remote database cursor with data grid view control like the below, you should use the class CAsynDBLiteEx instead CAsynDBLite as shown in the sample project MyTestGui.
With help of an instance of CAsynDBLiteEx, you can directly navigate a rowset on remote backend database server without bringing these records to clients. Advanced usages of CAsynDBLite and CAsynDBLiteEx Our SocketPro is written from batching, asynchrony and parallel computation with 100% non-blocking socket communication. It offers many advanced features than other common remoting framworks. This short article is not able to present all of these features. For details, you may need to experiment provided tutorials. However, we'll list a few of these features here. 1). Don't forget batch requests if possible for better performance and scalability, as shown in the below code from sample web application MyWebApp. if (m_AsynDBLite != null)
{
//Start batch
m_AsynDBLite.BeginBatch();
if (!m_AsynDBLite.DBConnected)
{
//Connect to a db one time only
m_AsynDBLite.ConnectDB("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind3.mdb");
}
//delete some records
m_AsynDBLite.ExecuteNonQuery("Delete from Shippers Where ShipperID > 3");
string strSQL = "Insert into Shippers (CompanyName, Phone) Values ('";
strSQL += txtCompany.Text;
strSQL += "', '";
strSQL += txtPhoneNumber.Text;
strSQL += "')";
//insert a record
m_AsynDBLite.ExecuteNonQuery(strSQL);
//query records and fetch all of them
m_AsynDBLite.OpenRowset("Select * from Shippers", "Shippers");
//batch requests and remember a callback cb
m_AsynDBLite.CommitBatch(cb);
}
2). Use socket pool object for reuse of a pool of existing socket connections. SocketPro has a built-in object on client side to manage a pool of socket connections. The above AsynWeb and DBConsol samples demonstrate use of the socket pool object. In addition, you can see the tutorial four for how to use the object. Socket pool is very useful to web and middle tier application. Here is the code snippet from sample Console. [MTAThread]
static void Main(string[] args)
{
CSocketPool<CAsynDBLite> dbPool = new CSocketPool<CAsynDBLite>();
//start a pool socket with one thread, one socket, and one DB handler.
if (dbPool.StartSocketPool( "localhost", //remote host server
17001, //port for a SocketPro server
"SocketPro", //user id
"PassOne", //password
1, //sockets per thread
1, //number of threads in the epool
tagEncryptionMethod.NoEncryption, //encription method
false //enable online compression or not
))
{
CAsynDBLite DBLite = dbPool.Lock();
DBLite.BeginBatch();
DBLite.ConnectDB("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind3.mdb");
DBLite.OpenRowset("Select * from Customers", "Customers");
DBLite.CommitBatch(null);
DBLite.GetAttchedClientSocket().WaitAll();
DataTable dt = DBLite.CurrentDataTable;
dbPool.ShutdownPool();
}
}
3). Use the method WaitAll to convert asynchronous requests into synchronous ones. By default, SocketPro processes all of requests asynchronously. In case you like to process requests synchronously, you can convert all of asynchronous requests into synchronous ones by calling the method CClientSocket::WaitAll. You can see this conversion from these tutorials. Here is the code snippet from the file ProgramSyn.cs inside the sample DBConsole. class CMyDBSyn : CAsynDBLite
{
public bool Connect(string strConn)
{
ConnectDB(strConn);
GetAttchedClientSocket().WaitAll();
return DBConnected;
}
public void Disconnect()
{
DisconnectDB();
GetAttchedClientSocket().WaitAll();
}
public bool OpenRowset(string strSQL)
{
OpenRowset(strSQL, "MyTable", tagCursorType.ctStatic, CAsynDBLite.Readonly, 20, -1);
GetAttchedClientSocket().WaitAll();
return (CurrentDataTable.Columns.Count != 0);
}
public bool IsEOF()
{
return (CurrentDataTable.Rows.Count == 0);
}
public void MoveFirst()
{
FirstBatch();
GetAttchedClientSocket().WaitAll();
}
public void MoveNext()
{
MoveNext(0);
}
public void MoveNext(int nSkip)
{
NextBatch(nSkip);
GetAttchedClientSocket().WaitAll();
}
public void MovePrev()
{
MoveNext(-2);
}
public void MoveLast()
{
LastBatch();
GetAttchedClientSocket().WaitAll();
}
}
class Program
{
[MTAThread]
static void Main(string[] args)
{
CSocketPool<CMyDBSyn> dbPool = new CSocketPool<CMyDBSyn>();
//start a pool socket with one thread, one socket, and one DB handler.
if (dbPool.StartSocketPool( "localhost", //remote host server
17001, //port for a SocketPro server
"SocketPro", //user id
"PassOne", //password
1, //sockets per thread
1, //number of threads in the epool
tagEncryptionMethod.NoEncryption, //encription method
false //enable online compression or not
))
{
CMyDBSyn myDB = dbPool.Lock();
int nCount = 0;
if (myDB.Connect("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind3.mdb"))
{
if (myDB.OpenRowset("Select * from Orders"))
{
while (!myDB.IsEOF())
{
nCount += myDB.CurrentDataTable.Rows.Count;
// process your batch records here
myDB.MoveNext();
}
}
}
dbPool.ShutdownPool();
Console.WriteLine("Recods fetched = " + nCount.ToString());
Console.WriteLine("Presss the key <ENTER> to exit the application");
Console.ReadLine();
}
}
}
If your project is a window application, asynchrony computation is preferred in general because window application is actually driven by message asynchronously. SocketPro asynchrony computation is well coupled and matched as shown in the samples MyTestGui, MultiParam and ParameterizedSQL. 4). Use the property SubBatchSize to reduce latency. If your window application requires fetching a large size of batch records across DSL/Cable modem or a low bandwidth network, you can reduce data display latency by setting the property to a small value. See the below code from the sample MyTestGui. //SubBatchSize reduces latency
m_AsynDBLite.SubBatchSize = 20;
//Open one rowset and set generated DataTable with the name "Table1"
m_AsynDBLite.OpenRowset(txtSQL.Text, "Table1", tagCursorType.ctStatic, CAsynDBLite.Scrollable, 100, -1);
5). Send a real-time message to a team of coworkers. SocketPro comes a built-in feature, real-time notification (chat) service, for all of socket connections. Usually, you may have a team of coworkers or other applications to access the same database. These coworkers or applications requires cooperation. You can use the real-time notification service for this purpose. For details, see this article and tutorial 2. 6). Parallel computation is not only possible but also practical with SocketPro. Did you ever try to divide a large problem into a set of small ones and process these small problems in parallel so that your application performance and scalability are improved? Usually, this is not trivial work. However, this is very achievable with SocketPro. See the following code snippet from the sample AsyWeb. protected void btnParallel_Click(object sender, EventArgs e)
{
//lock two instance CAsynDBLite
CAsynDBLite AsynDBLite1 = Global.m_QueryPool.Lock(100);
CAsynDBLite AsynDBLite2 = Global.m_QueryPool.Lock(100);
CTOne Tone = Global.m_TOnePool.Lock(100);
if (AsynDBLite1 == null || AsynDBLite2 == null)
{
//indicate error message like server is too busy
return;
}
if (!AsynDBLite1.DBConnected)
{
//connect to DB one time only
AsynDBLite1.ConnectDB("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind3.mdb");
AsynDBLite1.GetAttchedClientSocket().WaitAll();
}
if (!AsynDBLite2.DBConnected)
{
//connect to DB one time only
AsynDBLite2.ConnectDB("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind3.mdb");
AsynDBLite2.GetAttchedClientSocket().WaitAll();
}
//execute two SQLs in parallel with two instances of CAsynDBLite
AsynDBLite1.OpenRowset(txtSQL1.Text, "SQL1");
AsynDBLite2.OpenRowset(txtSQL2.Text, "SQL2");
if (Tone == null)
{
txtCount.Text = "-1";
}
else
{
//execute the call in parallel with executing the above two SQLs. Also, it calls WaitAll inside the function CTone::QueryCount
txtCount.Text = Tone.QueryCount().ToString();
Global.m_TOnePool.Unlock(Tone);
}
//Block until two requests are processed
AsynDBLite2.GetAttchedClientSocket().WaitAll();
AsynDBLite1.GetAttchedClientSocket().WaitAll();
gvQueryOne.DataSource = AsynDBLite1.CurrentDataTable;
gvQueryTwo.DataSource = AsynDBLite2.CurrentDataTable;
gvQueryOne.DataBind();
gvQueryTwo.DataBind();
//unlock sockets and their associated DB handlers, and return them back into pool for reuse
Global.m_QueryPool.Unlock(AsynDBLite1);
Global.m_QueryPool.Unlock(AsynDBLite2);
}
7). Set a global OLEDB connection string at server side. By setting a global OLEDB connection string at server side, you can connect a client to a remote backend database server without requiring an OLEDB connection string from a client. This not only simplifies client side OLEDB connection string management, but also helps data accessing security. See the following code from the sample ..\SocketPro\samples\performance\csharp\PerformanceStudySocketPro\PerfSvr. IntPtr hInst = CBaseService.AddALibrary("uodbsvr.dll", 0);
if (hInst == (IntPtr)0)
{
Console.WriteLine("library uodbsvr.dll not available.");
}
else
{
//"Provider=sqlncli;Data Source=localhost\\sqlexpress;Integrated Security=SSPI;Initial Catalog=northwind"
SetGlobalOLEDBConnectionString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Program Files\\udaparts\\SocketPro\\bin\\nwind3.mdb");
}
You can use the following code to connect a database without specifying an OLEDB connection string on client side. See the sample ..\SocketPro\samples\performance\csharp\PerformanceStudySocketPro\PerfClient. if (!m_AsynDB.DBConnected)
{
//use global oledb connection string which is setting at server side
m_AsynDB.ConnectDB(null);
m_AsynDB.GetAttchedClientSocket().WaitAll();
}
8). Secure data connection, cancel fetching records, directly access a remote database from a device, custom authentication, and online compression, ..... SocketPro is an extremely powerful communication framework. Remote database service inherits all of these features from its parent, SocketPro. You can see these features within these tutorials. |
