使用 OleDB 实现远程多线程数据库访问（C#, Access 数据库）
对于大多数人来说，使用Microsoft SQL Server 或 Mysql 数据库可以方便地完成网络B/S,C/S软件的开发。但是，这是一个潜在的生产成本。Microsoft SQL Server和Mysql 的用于生产的版本不是免费的，更何况SQL Server的安装需要Microsoft Windows Server 系统的支持。
其实，完全可以自己动手、丰衣足食，开发出高效的网络应用开发平台，以便支持同时多用户多线程并发读写，并且只需要本地数据库支持，在这里我用 Access 做例子。
应该注意的是，你应该可以发现，我为了稳定的执行多条命令，采用了将发送数据前加上字节长度的方法。例如 「00000000010BADCOMMAND」。这样可以保证所有数据传输过来后才执行命令，并且多条命令不会被当成一条。而且这种模式是绝对稳定的，如果采用别的方法，不一定稳定。如分隔符，如果用「||」当分隔符，万一命令里有分隔符怎么办？还是一件麻烦事。这样，我认为是简单高效的处理方法。
使用lock技术，多线程可以进行放心的读写，但要保证写操作在一个线程上，防止出错。
参考代码：（节选）
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Net.Sockets;
using System.Threading;
using System.IO;
using System.Data.OleDb;
using System.Net;
namespace Server
{
public partial class ServerForm : Form
{
public Thread[] threads = new Thread[100];
public Thread listenThread = null;
public bool[] objectlock = new bool[100];
public object locko = new object();
public object l = new object();
public object lock1 = new object();
public int userNum = 0;
public ServerForm()
{
//此处省略用户界面
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//load the server
listenThread = new Thread(new ThreadStart(this.listen));
listenThread.Start();
}
void listen()
{
try
{
TcpListener tcp = new TcpListener(System.Net.IPAddress.Any, 2008);
tcp.Start();
while (true)
{
Thread.Sleep(100);
if (tcp.Pending())
{
Socket s = tcp.AcceptSocket();
for (int i = 0; i < = 99; i++)
{
if (threads[i] == null || threads[i].ThreadState != ThreadState.Running)
{
threads[i] = new Thread(new ParameterizedThreadStart(this.doServerEvents));
threads[i].Start(s);
break;
}
}
}
}
}
catch (Exception e)
{
//todo
}
finally { }
}
///
/// 独立服务器组件
///
///
<span> </span>Socket
void doServerEvents(object o)
{
//客户端发送数据的长度最大限度为8位数，如99999999。
//服务器发送数据最大限度为11位数，如99999999999。
///////////////////////////////////////////////////////////////////////////////////////////////////
Socket s = (Socket)o;//连接
byte[] butter = new byte[10240];//缓冲区
StringBuilder str = new StringBuilder();//命令字符串
CConn conn = new CConn();
List lockList = new List();
///////////////////////////////////////////////////////////////////////////////////////////////////
try
{
lock (lock1)
userNum++;
conn.Log.WriteToFile = false;
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.GetAccessFileName() + ";Persist Security Info=False;Jet OLEDB:DataBase Password=123";
conn.Open();
bool login = false;
int len = 0;
int length = -1;
bool bad = false;
while (s.Connected && (len = s.Receive(butter, 10240, SocketFlags.None)) > 0)
{
str.Append(Encoding.Default.GetString(butter, 0, len));
if (length == -1)
{
if (int.TryParse(str.ToString(0, 8), out length) == false)
{
s.Send(Encoding.Default.GetBytes("00000000010BADCOMMAND"));
s.Close();
break;
}
str.Remove(0, 8);
}
while (str.Length >= length)
{
object ob = doCommand(str.ToString(0, length), ref login, conn, lockList);
string r = "";
if (ob != null && ob is string)
{
r = ob.ToString();
if (r != null)
{
s.Send(Encoding.Default.GetBytes(r.Length.ToString().PadLeft(11, '0') + r));
}
}
else
{
if (ob != null)
{
byte[] b = (byte[])ob;
if (b != null)
{
s.Send(Encoding.Default.GetBytes(b.Length.ToString().PadLeft(11, '0')));
s.Send(b);
}
}
}
str.Remove(0, length);
length = -1;
if (str.Length < 8)
break;
if (int.TryParse(str.ToString(0, 8), out length) == false)
{
s.Send(Encoding.Default.GetBytes("00000000010BADCOMMAND"));
s.Close();
bad = true;
break;
}
str.Remove(0, 8);
}
if (bad)
break;
}
}
catch (Exception e)
{
s.Close();
conn.Close();
return;
}
finally
{
lock (this.lock1)
userNum--;
this.ClearLock(lockList);
s.Close();
conn.Close();
}
}
///
/// 进行服务器端命令操作。发生任何错误会返回以"_"开头的错误信息文本。
///
///
命令
///
是否登录
///
连接
/// 返回值
object doCommand(string commStr, ref bool isLogin, CConn conn, List lockList)
{
try
{
string[] comm = commStr.Split((char)9);
if (comm.Length == 0)
{
return null;
}
if (!isLogin)
{
if (comm[0] != "login")
{
return "_您没有执行本命令的权限。错误: (10000) 没有成功登录。";
}
}
switch (comm[0])
{
//////////////////////////// hello 测试命令 /////////////////////////////////////
case "hello":
{
return "OKTEXT";
break;
}
//////////////////////////// login 登录 /////////////////////////////////////
case "login":
{
if (comm[1] != "登录验证信息")
{
return "F";
}
if (comm[2] != "服务器版本")
{
return "F";
}
isLogin = true;
return "T";
break;
}
//////////////////////////// 事务处理 /////////////////////////////////////
case "beginTransaction":
{
try
{
OleDbTransaction tran = conn.Connection.BeginTransaction();
conn.Command.Transaction = tran;
return "T";
}
catch
{
return "F";
}
}
case "commitTransaction":
{
try
{
if (conn.Command.Transaction != null)
{
conn.Command.Transaction.Commit();
conn.Command.Transaction = null;
}
return "T";
}
catch
{
return "F";
}
}
case "rollBackTransaction":
{
try
{
if (conn.Command.Transaction != null)
{
conn.Command.Transaction.Rollback();
conn.Command.Transaction = null;
}
return "T";
}
catch
{
return "F";
}
}
//////////////////////////// bye 退出登录 /////////////////////////////////////
case "bye":
{
this.ClearLock(lockList);
return "OK";
break;
}
case "onlinecount":
{
int count = 0;
lock (lock1)
count = userNum;
return count.ToString();
break;
}
//////////////////////////// lock 锁定 /////////////////////////////////////
case "lock":
{
if (comm.Length != 2)
return "_ERROR";
int li;
if (int.TryParse(comm[1], out li) == false)
{
return "_ERROR";
}
lock (locko)
{
if (objectlock[li])
return "F";
objectlock[li] = true;
lockList.Add(li);
return "T";
}
break;
}
case "unlock":
{
if (comm.Length != 2)
return "_ERROR";
int li;
if (int.TryParse(comm[1], out li) == false)
{
return "_ERROR";
}
lock (locko)
{
if (objectlock[li] == false)
return "F";
objectlock[li] = false;
lockList.Remove(li);
return "T";
}
break;
}
case "testlock":
{
if (comm.Length != 2)
return "_ERROR";
int li;
if (int.TryParse(comm[1], out li) == false)
{
return "_ERROR";
}
lock (locko)
{
if (objectlock[li] == false)
return "F";
return "T";
}
break;
}
//////////////////////////// get 获得记录集 /////////////////////////////////////
case "getfiles":
{
if (comm.Length != 3)
return "_ERROR";
string[] files=null;
if (comm[1] == "")
comm[1] = Tool.GetPath();
try
{
files=System.IO.Directory.GetFiles(comm[1], comm[2], SearchOption.TopDirectoryOnly);
}
catch (Exception e)
{
return "_" + e.Message;
}
string ret = "FileNametSizetCreatedDatetChangedDater";
foreach (string str in files)
{
FileInfo info = new FileInfo(str);
ret += str + "t";
ret += info.Length.ToString() + "t";
ret += info.CreationTime.ToString() + "t";
ret += info.LastWriteTime.ToString() + "r";
}
return ret.Substring(0, ret.Length - 1);
break;
}
case "getfilesize":
{
if (comm.Length != 2)
return "_ERROR";
try
{
if (comm[1] == "{}")
comm[1] = this.GetAccessFileName();
FileInfo info = new FileInfo(comm[1]);
return info.Length.ToString();
}
catch (Exception e)
{
return "_" + e.Message;
}
}
case "backup":
{
if (comm.Length != 2)
return "_请输入文件名。";
try
{
System.IO.File.Copy(this.GetAccessFileName(), Tool.GetPath() + comm[1], true);
}
catch(Exception e)
{
return "_" + e.Message;
}
return "OK";
}
case "restore":
{
if (comm.Length != 2)
return "_请输入文件名。";
try
{
conn.Close();
System.IO.File.Copy(Tool.GetPath() + comm[1], this.GetAccessFileName(), true);
conn = new CConn ();
conn.Log.WriteToFile = false;
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.GetAccessFileName() + ";Persist Security Info=False;Jet OLEDB:DataBase Password=123";
conn.Open();
}
catch (Exception e)
{
return "_" + e.Message;
}
return "OK";
}
case "download": ///////// 下载文件。这儿函数是为了获得单机缓存数据库而设置的。
{
if (comm.Length != 4)
return "_ERROR";
if (comm[1] == "{}")
{
comm[1] = this.GetAccessFileName() + "t";
System.IO.File.Copy(this.GetAccessFileName(), comm[1], true);
}
try
{
int start = int.Parse(comm[2]);
int end = int.Parse(comm[3]);
byte[] dst = new byte[end - start + 1];
ServerForm.Copy(System.IO.File.ReadAllBytes(comm[1]), start - 1, dst, 0, end - start + 1);
return dst;
}
catch (Exception e)
{
return new byte[0];
}
}
case "get":
{
if (comm.Length != 2)
return "_ERROR";
ICDataSet set = null;
try
{
set = conn.GetDataSet(comm[1]);
}
catch (Exception e)
{
return "_" + e.Message;
}
if (set == null)
{
return "_ERROR";
}
string ret = set.GetString();
set.Close();
return ret;
break;
}
case "getone":
{
if (comm.Length != 2)
return "_ERROR";
object o = conn.GetSqlResult(comm[1]);
if (o == null)
{
return "";
}
return o.ToString();
break;
}
//////////////////////////// exec 执行 /////////////////////////////////////
case "exec":
{
if (comm.Length != 2)
return "_ERROR";
int ret = -1;
string err = "";
lock (l)
{
try
{
ret = conn.ExecuteSql(comm[1]);
}
catch (Exception e)
{
err = e.Message;
}
}
if (err == "")
return ret.ToString();
else return "_" + err;
break;
}
}
return null;
}
catch (Exception e)
{
return "_" + e.Message;
}
}
private string GetAccessFileName()
{
return @"数据库文件名";
}
private void ClearLock(List lockList)
{
lock (locko)
{
foreach (int li in lockList)
{
this.objectlock[li] = false;
}
}
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
this.CloseServer();
}
private void CloseServer()
{
listenThread.Abort();
for (int i = 0; i < = 99; i++)
{
if (threads[i] != null)
threads[i].Abort();
}
static unsafe void Copy(byte[] src, int srcIndex, byte[] dst, int dstIndex, int count) //摘自MSDN的UNSAFE子程序
{
if (src == null || srcIndex < 0 ||
dst == null || dstIndex < 0 || count < 0)
{
throw new System.ArgumentException();
}
int srcLen = src.Length;
int dstLen = dst.Length;
if (srcLen - srcIndex < count || dstLen - dstIndex < count)
{
throw new System.ArgumentException();
}
// The following fixed statement pins the location of the src and dst objects
// in memory so that they will not be moved by garbage collection.
fixed (byte* pSrc = src, pDst = dst)
{
byte* ps = pSrc;
byte* pd = pDst;
// Loop over the count in blocks of 4 bytes, copying an integer (4 bytes) at a time:
for (int i = 0; i < count / 4; i++)
{
*((int*)pd) = *((int*)ps);
pd += 4;
ps += 4;
}
// Complete the copy by moving any bytes that weren't moved in blocks of 4:
for (int i = 0; i < count % 4; i++)
{
*pd = *ps;
pd++;
ps++;
}
}
}
}
}
题外话：我帮你整理了包括 AI 写作、绘画、视频（自媒体制作）零门槛 AI 课程 + 国内可直接顺畅使用的软件。想让自己快速用上 AI 工具来降本增效，辅助工作和生活？限时报名。
© 转载需附带本文链接，依据 CC BY-NC-SA 4.0 发布。