COM+数据库连接池

连接池
添加以下引用
System.EnterpriseServices
System.Runtime.Remoting

using System;
using System.IO;
using System.Text;
using System.Reflection;
using System.Runtime.InteropServices;
using System.EnterpriseServices;

[assembly: ApplicationName("POOLINGTEST")]
[assembly: AssemblyKeyFile("poolserver.snk")]

namespace PoolServer
{
[ObjectPooling(Enabled=true,MinPoolSize=0, MaxPoolSize=1,CreationTimeout=2000)]
[JustInTimeActivation(true)]
[ConstructionEnabled(Default="Workstation id=.;data source=.;initial catalog=card;integrated security=sspi")]    //Text File to be created during construction

public class PooledFile : ServicedComponent
{
    private string strConnection;

    protected override void Construct(string constructString)
    {
     strConnection = constructString;
    }

    public string ConnectionString
    {
     get
     {
      return strConnection;
     }
    }
}
}

远程数据库访问类
添加以下引用
System.EnterpriseServices
System.Runtime.Remoting
添加连接池组件PoolServer.dll
using System;
using PoolServer;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace RemoteTest
{
//SQL Server数据库连接类
public class SQLConnection:MarshalByRefObject
{
    private SqlConnection m_sqlConn;
    private SqlDataAdapter m_sqlDA;
    private SqlCommand m_sqlComm;

    public SQLConnection()
    {
     PooledFile pool = new PooledFile();
     m_sqlConn = new SqlConnection(pool.ConnectionString);
    }

    //创建一个SQLConnection连接实例
    public SQLConnection(string strConnection)
    {
     Console.WriteLine("有数构造");
     m_sqlConn = new SqlConnection(strConnection);
    }

    //执行查询，返回DataSet对象(多条记录)
    public DataSet executeQueryDS(string strQuery,string table)
    {
     open();
     m_sqlDA = new SqlDataAdapter(strQuery, m_sqlConn);
     DataSet ds = new DataSet();
     m_sqlDA.Fill(ds,table);
     close();
     return ds;
    }

    //执行查询，返回SqlDataReader对象(一条记录)
    //执行该方法后，必须执行close()方法以关闭SQL Server数据库连接
    public SqlDataReader executeQueryDR(string strQuery)
    {
     open();
     m_sqlComm = new SqlCommand(strQuery, m_sqlConn);
     SqlDataReader dr;
     dr = m_sqlComm.ExecuteReader();
     return dr;
    }

    //执行更新，返回所影响的行数
    public int executeUpdate(string strUpdate)
    {
     open();
     m_sqlComm = new SqlCommand(strUpdate, m_sqlConn);
     int rows;
     rows = m_sqlComm.ExecuteNonQuery();
     return rows;
    }
    public bool isExistProcudure(string sql, SqlParameter[] param)
    {
     m_sqlComm = new SqlCommand(sql, m_sqlConn);
     m_sqlComm.CommandType = CommandType.StoredProcedure;
     if (param != null)
     {
      foreach (SqlParameter parameter in param)
      {
       m_sqlComm.Parameters.Add(parameter);
      }
     }
     open();
     int n = Convert.ToInt32(m_sqlComm.ExecuteScalar());
     close();
     if (n >= 0)
     {
      return true;
     }
     else
     {
      return false;
     }    
    }
    //执行返回查询结果的存储过程
    public SqlDataReader executeQueryProcedure(string strCommandText, SqlParameter[] param)
    {
     open();
     m_sqlComm = new SqlCommand(strCommandText, m_sqlConn);
     m_sqlComm.CommandType = CommandType.StoredProcedure;
   
     if (param != null)
     {
      foreach (SqlParameter parameter in param)
      {
       if(parameter != null)
       {
        m_sqlComm.Parameters.Add(parameter);
       }
      }
     }

     SqlDataReader dr = m_sqlComm.ExecuteReader();
     return dr;
    }

    //执行更新，返回所影响行数的存储过程
    public int executeUpdateProcedure(string strCommandText, SqlParameter[] param)
    {
     open();
     m_sqlComm = new SqlCommand(strCommandText, m_sqlConn);
     m_sqlComm.CommandType = CommandType.StoredProcedure;
   
     if (param != null)
     {
      foreach (SqlParameter parameter in param)
      {
       if(parameter != null)
       {
        m_sqlComm.Parameters.Add(parameter);
       }
      }
     }

     int rows = m_sqlComm.ExecuteNonQuery();
     return rows;
    }

    //打开SQLConnection连接
    public void open()
    {
     if(m_sqlConn.State == ConnectionState.Closed)
     {
      m_sqlConn.Open();
     }
    }

    //关闭SQLConnection连接
    public void close()
    {
     if(m_sqlConn.State == ConnectionState.Open)
     {
      m_sqlConn.Close();
     }
    }
}
}

服务器
添加以下引用System.EnterpriseServices
System.Runtime.Remoting
添加数据库访问类组件RemoteTest.dll

private SQLConnection obj;
    private bool m_bSign=true;
    private void sign()
    {
     TcpServerChannel channel = new TcpServerChannel(9932);
     obj = new SQLConnection();
     ChannelServices.RegisterChannel(channel);
     ObjRef    objref=RemotingServices.Marshal(obj, "TestLoader", typeof(SQLConnection));
    }
    private void btn_start_Click(object sender, System.EventArgs e)
    {
   
     //RemotingConfiguration.RegisterWellKnownServiceType(typeof(SQLConnection),
     // "TestLoader", WellKnownObjectMode.Singleton );
   
     if(m_bSign)
     {
      m_bSign=false;
      this.sign();
     }
     else
     {
      ObjRef objrefWellKnown = RemotingServices.Marshal(obj, "TestLoader");
     }
     this.lb_message.Items.Clear();
     this.lb_message.Items.Add("CM+服务器已起动!!!");
     this.btn_start.Enabled=false;
     this.btn_stop.Enabled=true;
    }

    private void btn_stop_Click(object sender, System.EventArgs e)
    {
     this.btn_stop.Enabled=false;
     this.btn_start.Enabled=true;
     RemotingServices.Disconnect(obj);
     //ChannelServices.UnregisterChannel(channel);
     this.lb_message.Items.Add("COM+服务器已关闭!!!");
    }

    private void menuItem_openServer_Click(object sender, System.EventArgs e)
    {
     if(m_bShowWnd==true)
     {
      this.Visible=true;
      m_bShowWnd=false;
     }
    }

    private void menuItem_minimize_Click(object sender, System.EventArgs e)
    {
     if(m_bShowWnd==false)
     {
      this.Visible=false;
      m_bShowWnd=true;
     }
    }

    private void menuItem_exit_Click(object sender, System.EventArgs e)
    {
     DialogResult result=MessageBox.Show("你确定要退出COM+服务器吗?如果单击是,它将终止所有访问数据库的服务.","信息提示",MessageBoxButtons.YesNo,MessageBoxIcon.Question);
     if(result==DialogResult.Yes)
     {
      this.Close();
     }
    }

    private void notifyIcon1_DoubleClick(object sender, System.EventArgs e)
    {
     m_bShowWnd=false;
     this.Visible=true;
    }
  
    private void button1_Click(object sender, System.EventArgs e)
    {
     menuItem_exit_Click(null,null);
    }

    private void label2_Click(object sender, System.EventArgs e)
    {
     if(m_bShowWnd==false)
     {
      this.Visible=false;
      m_bShowWnd=true;
     }
    }

客户端
添加以下引用
System.EnterpriseServices
System.Runtime.Remoting
添加数据库访问类组件RemoteTest.dll
using System;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;
using RemoteTest;
using System.Data.SqlClient;
namespace Client
{
public class ClassClient
{
    static void Main(string[] args)
    {   
     try
     {
      ChannelServices.RegisterChannel(new TcpClientChannel());
      SQLConnection loader=(SQLConnection)Activator.GetObject(typeof(SQLConnection),"tcp://localhost:9932/TestLoader");
   
      if(loader==null)
      {
       Console.WriteLine("Unable to get remote");
      }

else

{
      SqlParameter[] param=new SqlParameter[2];
      param[0] = new SqlParameter("@emp_id", SqlDbType.NChar, 9);     //存储过程的输入参数
      param[0].Value = "PMA42628M";      
      param[1] = new SqlParameter("@emp_name", SqlDbType.NVarChar, 20); //存储过程的输出参数
      param[1].Direction = ParameterDirection.Output;

      ESqlParameter[] eparam = new ESqlParameter[2];
      eparam[0] = new ESqlParameter(param[0]);
      eparam[1] = new ESqlParameter(param[1]);
    
      IFormatter formatter = new BinaryFormatter();
      Stream stream = new FileStream("MyFile.txt", FileMode.Create, FileAccess.Write, FileShare.None, 1000, true);
      formatter.Serialize(stream, eparam);
    
      //执行根据指定员工编号查询员工名字的存储过程
      SqlDataReader dr =loader .executeQueryProcedure("prcGetEmpName", stream);
      stream.Close();
      stream = null;
      GC.Collect();

      System.Console.WriteLine("员工编号 : " + param[0].Value);
      System.Console.WriteLine("员工名字 : " + param[1].Value);       
      conn.close();
     }
     catch(SingletonException err)
     {
      System.Console.WriteLine(err.Message);
     }
     catch(Exception err)
     {
      System.Console.WriteLine(err.ToString());
     }
    }

[Serializable]
public class ESqlParameter
{
    public ESqlParameter(SqlParameter sPara)
    {
     this.paraName = sPara.ParameterName ;
     this.paraLen    = sPara.Size ;
     this.paraVal    = sPara.Value ;
     this.sqlDbType= sPara.SqlDbType ;        
    }

    public SqlParameter ToSqlParameter()
    {
     SqlParameter para = new SqlParameter(this.paraName ,this.sqlDbType ,this.paraLen) ;
     para.Value = this.paraVal ;

     return para ;
    }

    private string paraName = "" ;
    public string ParaName
    {
     get
     {
      return this.paraName ;
     }
     set
     {
      this.paraName = value ;
     }
    }

    private int paraLen = 0 ;
    public int ParaLen
    {
     get
     {
      return this.paraLen ;
     }
     set
     {
      this.paraLen = value ;
     }
    }
    
    private object paraVal = null ;
    public object ParaVal
    {
     get
     {
      return this.paraVal ;
     }
     set
     {
      this.paraVal = value ;
     }
    }    

    private SqlDbType sqlDbType = SqlDbType.NVarChar ;
    public SqlDbType SqlDbType
    {
     get
     {
      return this.sqlDbType ;
     }
     set
     {
      this.sqlDbType = value ;
     }
    }
}

一步：打开管理工具,打开服务,然后把所有关于COM+的服务起动
二步：起动Distributed Transaction Coordinator服务
三步：注册强名称
1.打开.net命令窗口
2.定位COM+所在目标,然后输入 sn -k 强名称
二步：起动服务器
三步：客房端访问