使用 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 &amp;&amp; (len = s.Receive(butter, 10240, SocketFlags.None)) &gt; 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 &gt;= length)
                    {
                        object ob = doCommand(str.ToString(0, length), ref login, conn, lockList);
                        string r = "";
                        if (ob != null &amp;&amp; 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 &lt; 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 &lt; = 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 &lt; 0 ||
                dst == null || dstIndex &lt; 0 || count &lt; 0)
            {
                throw new System.ArgumentException();
            }
 
            int srcLen = src.Length;
            int dstLen = dst.Length;
            if (srcLen - srcIndex &lt; count || dstLen - dstIndex &lt; 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 &lt; 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 &lt; count % 4; i++)
                {
                    *pd = *ps;
                    pd++;
                    ps++;
                }
            }
        }
    }
}

题外话:我帮你整理了包括 AI 写作、绘画、视频(自媒体制作)零门槛 AI 课程 + 国内可直接顺畅使用的软件。想让自己快速用上 AI 工具来降本增效,辅助工作和生活?限时报名

当前页阅读量为: