using System;
using System.Threading;
using System.Net.Sockets;
using System.Text;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            TcpListener serverSocket = new TcpListener(8888);
            TcpClient clientSocket = default(TcpClient);
            int counter = 0;
            serverSocket.Start();
            Console.WriteLine(" >> " + "Server Started");
            while (true)
            {
                counter ++;
                clientSocket = serverSocket.AcceptTcpClient();
                Console.WriteLine(" >> " + "Client No:" + Convert.ToString(counter) + " connected!");
                handleClinet client = new handleClinet();
                client.startClient(clientSocket, Convert.ToString(counter));
            }
            clientSocket.Close();
            serverSocket.Stop();
            Console.WriteLine(" >> " + "exit");
            Console.ReadLine();
        }
    }

    public class handleClinet
    {
        public String connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetFullPath("base.mdb") + @";";
        public    OleDbCommand cmd;
        public    OleDbConnection MDBConn;
        DataTable table = new DataTable();
        public DataSet ds = new DataSet();
        public OleDbDataAdapter da; 
        TcpClient clientSocket;
        string clNo;
        public byte[] SerializeData(DataTable table)
        {
            byte[] data = null;
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf1 = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            bf1.Serialize(ms, table);
            data = ms.ToArray();
            ms.Close();
            ms.Dispose();
            return data;
        }

        public byte[] SerializeData(DataSet table)
        {
            byte[] data = null;
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf1 = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            bf1.Serialize(ms, table);
            data = ms.ToArray();
            ms.Close();
            ms.Dispose();
            return data;
        }
       public void startClient(TcpClient inClientSocket, string clineNo)
        {
            this.clientSocket = inClientSocket;
            this.clNo = clineNo;
            Thread ctThread = new Thread(doChat);
            ctThread.Start();
        }
        private void doChat()
        {
            int requestCount = 0;
            string dataFromClient = null;
            string serverResponse = null;
            requestCount = 0;
            while ((true))
            {
                try
                {
                    requestCount = requestCount + 1;
                    NetworkStream Stream = clientSocket.GetStream();
                    int size  = (int)clientSocket.ReceiveBufferSize;
                    byte[] bytesFrom = new byte[size];
                    Stream.Read(bytesFrom, 0, size);
                    dataFromClient = System.Text.Encoding.UTF8.GetString(bytesFrom);
                    dataFromClient = dataFromClient.Substring(0, dataFromClient.IndexOf("$"));
                    Console.WriteLine(" >> " + "From client-" + clNo + dataFromClient);
                    OpenSQLx(dataFromClient);
                    // Отправка table клиенту
                    Stream.Write(SerializeData(ds), 0, SerializeData(ds).Length);
                    //Stream.Write(SerializeData(table), 0, SerializeData(table).Length);
                    Stream.Flush();
                //  sendBytes = Encoding.ASCII.GetBytes("12345");
                    // rCount = Convert.ToString(requestCount);
                   // serverResponse = "Server to clinet(" + clNo + ") " + rCount;
                   // sendBytes = Encoding.ASCII.GetBytes(serverResponse);
                    Console.WriteLine(" >> " + serverResponse);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(" >> " + ex.ToString());
                }
            }
        }
    private void OpenSQL(String _sql)
    {
        MDBConn = new OleDbConnection(connectionString); 
        try // Открываем соединение 
         {
            MDBConn.Open();
            ds.DataSetName = "LocalDataBase";
            table.TableName = "sprContragents";
            ds.Tables.Clear();
            ds.Tables.Add(table);
            using (da = new OleDbDataAdapter(_sql, MDBConn))
            {
                da.Fill(ds);
                ds.WriteXml(@"bbb.xml");
            }
          }
               catch (Exception ex)
               {
                   Console.WriteLine(" >> " + ex.ToString());
               }
             
               finally // Закрываем соединение 
               {
                  MDBConn.Close();
               }
            //======================================= 
        }
    private void OpenSQLx(String _sql)
    {
        MDBConn = new OleDbConnection(connectionString);
        try // Открываем соединение 
        {
            MDBConn.Open();
            ds.DataSetName = "LocalDataBase";
            table.TableName = "sprContragents";
            ds.Tables.Clear();
            ds.Tables.Add(table);
            using (da = new OleDbDataAdapter(_sql, MDBConn))
            {
                da.Fill(table);
                ds.WriteXml(@"bbb.xml");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(" >> " + ex.ToString());
        }
               finally // Закрываем соединение 
        {
            MDBConn.Close();
        }
        //======================================= 
    }
  }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.OleDb;
using System.Net.Sockets;
namespace Shop
{
    public partial class FormSpr : Form
    {
        System.Net.Sockets.TcpClient clientSocket = new System.Net.Sockets.TcpClient();
        public String connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FormMain.dblocation + @";";
        public    OleDbCommand cmd;
        public    OleDbConnection MDBConn;
        DataTable table = new DataTable();
        private String  _sql = "";
        public OleDbDataAdapter da; 
        public String SQL
        {
            get
            {
                return _sql;
            }

            set
            {
                _sql = value;
            }
        }
        public FormSpr()
        {
            InitializeComponent();
        }
        public DataSet DeserializeData(byte[] theByteArray)
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream(theByteArray);
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf1 = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            ms.Position = 0;
            return (DataSet) bf1.Deserialize(ms);
        }
    
        private void SetupDataGridView(DataGridView dgv)
        {
            dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.Navy;
            dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
            dgv.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
            dgv.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single;
            dgv.CellBorderStyle = DataGridViewCellBorderStyle.Single;
            dgv.GridColor = Color.Black;
            dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dgv.MultiSelect = false;
            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
        }

        private void FormSpr_Load(object sender, EventArgs e)
        {

            try
            {
                clientSocket.Connect("127.0.0.1", 8888);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка!\n\n" + ex.Message, "Ошибка SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            MDBConn = new OleDbConnection(connectionString);
            SetupDataGridView(dataGridView1);
            NetworkStream Stream = clientSocket.GetStream();
            byte[] outStream = System.Text.Encoding.ASCII.GetBytes(_sql+"$");
            Stream.Write(outStream, 0, outStream.Length);
            Stream.Flush();
            Stream = clientSocket.GetStream();
            int size = (int)clientSocket.ReceiveBufferSize;
            byte[] ReceivedBytes = new byte[size];
            Stream.Read(ReceivedBytes, 0, size);
            DataSet data = DeserializeData(ReceivedBytes);
            data.WriteXml(@"aaa");
            dataGridView1.DataSource = data.Tables[0].DefaultView; 
        }
            
        private void FormSpr_FormClosed(object sender, FormClosedEventArgs e)
        {
           // da.Update(table);
           // MDBConn.Close();
        }
          
        } 
    }