跨服务器连接

 

 /// <summary>
    /// 跨服务器连接
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="sql">要替换的名称</param>
    /// <returns></returns>
    public static System.Data.DataTable SQLLink(string sql, string ServerPrefix)
    {
        string serverName = "SRV_LINK_CATEGORY_MASTER" + DateTime.Now.ToString("yyyyMMddHHmmssfff");

        System.Data.DataTable dt = null;

        sql = sql.Replace(ServerPrefix, serverName);

        try
        {
            ComDB db = new ComDB(ComMethod.STORAGEString);
            ComDB.SetAllTimeOut(36000);
            YzDBTools.YzSqlDBTool.ConnectionString = ComMethod.STORAGEString;
            YzDBTools.YzSqlDBTool.AllTimeOut = 36000;
            IDataParameter[] parms1 = new IDataParameter[4];
            parms1[0] = new SqlParameter("@server", serverName);
            parms1[1] = new SqlParameter("@srvproduct", "");
            parms1[2] = new SqlParameter("@provider", "SQLOLEDB");
            parms1[3] = new SqlParameter("@datasrc", "192.168.9.75");
            IDataParameter[] parms2 = new IDataParameter[5];
            parms2[0] = new SqlParameter("@rmtsrvname", serverName);
            parms2[1] = new SqlParameter("@useself", "FALSE");
            parms2[2] = new SqlParameter("@locallogin", null);
            parms2[3] = new SqlParameter("@rmtuser", "dreamv");
            parms2[4] = new SqlParameter("@rmtpassword", "DVq6tv6");
            IDataParameter[] parms3 = new IDataParameter[2];
            parms3[0] = new SqlParameter("@server", serverName);
            parms3[1] = new SqlParameter("@droplogins", "DROPLOGINS");
            int res = 0;

            YzDBTools.YzSqlDBTool.RunProcedure("SP_ADDLINKEDSERVER ", parms1, out res);
            YzDBTools.YzSqlDBTool.RunProcedure("SP_ADDLINKEDSRVLOGIN", parms2, out res);
            dt = db.getDataTable(sql);  //执行sql语句的操作
            YzDBTools.YzSqlDBTool.RunProcedure("SP_DROPSERVER", parms3, out res);

        }
        catch (Exception ex)
        {
            throw ex;
        }
        return dt;
    }

 

 

例 sql 语句

select number_two,mail_number_two,sku,location
from dbo.TBL_SHIPMENT_INFO as tbl_01 left join
(
 select
  quantity_changes as 'NUM'
   ,seq AS 'SEQ'
   ,tbl1.document_no
      ,goods_code_show
   ,shipmentday AS 'SHIP_DATE'
 from ITSV.goodsup.dbo.TBL_SHIPMENT_DETAIL  as tbl1
 inner join ITSV.goodsup.dbo.TBL_SHIPMENT as tbl2
    on tbl1.document_no=tbl2.document_no
) as tbl_02
on tbl_01.number_two=tbl_02.document_no  COLLATE database_default
and  tbl_01.sku=tbl_02.goods_code_show COLLATE database_default
where 1=1

 

 

DataTable dt = ComMethod.SQLLink(sql, "ITSV");