自己写的一个.net跨服务器查询方法

首先在webconfig中加入链接

 <connectionStrings>
    <add name="ConnectionString" connectionString="Server=10.0.0.188;database=HA_Psy_Test;User ID=sa;Password=!qwe123456"/>
    <add name="ConnectionString1" connectionString="Server=10.0.0.188;database=ZD_EPDB_WEB_Huai;User ID=sa;Password=!qwe123456"/>
  </connectionStrings>

接下来写一个存放表名的model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PsyModel.System
{
    [Serializable]
    public partial class SuperTableInfo
    {
        private int _server;
        private string _fullName;
        private string _alias;
        /// <summary>
        /// 服务器
        /// </summary>
        public int Server
        {
            set { _server = value; }
            get { return _server; }
        }
        /// <summary>
        /// 表全称：例如（[DataBase].[dbo].[Table]）
        /// </summary>
        public string FullName
        {
            set { _fullName = value; }
            get { return _fullName; }
        }
        /// <summary>
        /// 表别名
        /// </summary>
        public string Alias
        {
            set { _alias = value; }
            get { return _alias; }
        }

    }
}

接着是查询方法

using PsyModel.System;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;

namespace PsyDAL
{
     public abstract class AcrossServersQuery
    {
         //protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
         //protected static string connectionString1 = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
        protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        protected static string connectionString1 = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
         public static DataSet Query(string selectItems,List<SuperTableInfo> list,string strWhere)
         {
             string[] linkOne = connectionString.Split(';');
             string[] linkTwo = connectionString1.Split(';');
             char equal = '=';

             string IPOne = linkOne[0].Substring(linkOne[0].IndexOf(equal) + 1);
             string UIDOne = linkOne[2].Substring(linkOne[2].IndexOf(equal) + 1);
             string PWOne = linkOne[3].Substring(linkOne[3].IndexOf(equal) + 1);
             string IPTwo = linkTwo[0].Substring(linkTwo[0].IndexOf(equal) + 1);
             string UIDTwo = linkTwo[2].Substring(linkTwo[2].IndexOf(equal) + 1);
             string PWTwo = linkTwo[3].Substring(linkTwo[3].IndexOf(equal) + 1);
             //开启数据库跨服务器查询功能
             StringBuilder sbOpen = new StringBuilder();
             sbOpen.Append(" exec sp_configure 'show advanced options',1 ");
             sbOpen.Append(" reconfigure ");
             sbOpen.Append(" exec sp_configure 'Ad Hoc Distributed Queries',1 ");
             sbOpen.Append(" reconfigure ");
             DbHelperSQL.Query(sbOpen.ToString());
             //查询语句
             StringBuilder sb = new StringBuilder();
             sb.Append(selectItems);
             StringBuilder allItem = new StringBuilder();
             foreach (SuperTableInfo item in list)
             {
              
                 StringBuilder sbItem = new StringBuilder();
                 sbItem.Append(" OPENROWSET('SQLOLEDB','");
                 if (item.Server == 1)
                 {
                     sbItem.Append(IPOne);
                     sbItem.Append("';'");
                     sbItem.Append(UIDOne);
                     sbItem.Append("';'");
                     sbItem.Append(PWOne);
                     sbItem.Append("', ");
                 }
                 else if (item.Server == 2)
                 {
                      sbItem.Append(IPTwo);
                      sbItem.Append("';'");
                      sbItem.Append(UIDTwo);
                      sbItem.Append("';'");
                      sbItem.Append(PWTwo);
                      sbItem.Append("', ");
                 }
                 sbItem.Append(item.FullName);
                 sbItem.Append(" ) ");
                 sbItem.Append(item.Alias);
                 sbItem.Append(" ,");

                 allItem.Append(sbItem);
             }
             sb.Append(allItem.ToString().TrimEnd(','));
             sb.Append( strWhere );

           DataSet ds =  DbHelperSQL.Query(sb.ToString());
             //关闭数据库跨服务器查询功能
             StringBuilder sbClose = new StringBuilder();       
             sbClose.Append("  exec sp_configure 'Ad Hoc Distributed Queries',0 ");
             sbClose.Append(" reconfigure ");
             sbClose.Append(" exec sp_configure 'show advanced options',0 ");
             sbClose.Append(" reconfigure ");
             DbHelperSQL.Query(sbClose.ToString());

             return ds;
         }
    }
}

最后  在后台调用这个方法

DataSet ds3 = new DataSet();
                List<SuperTableInfo> list = new List<SuperTableInfo>();          
                SuperTableInfo PsyNewsCommnet = new SuperTableInfo();
                PsyNewsCommnet.Server = 1;
                PsyNewsCommnet.FullName = "[HA_Psy_Test].[dbo].[PsyNewsCommnet]";
                PsyNewsCommnet.Alias = "a";
                list.Add(PsyNewsCommnet);
                SuperTableInfo Pu_User = new SuperTableInfo();
                Pu_User.Server = 2;
                Pu_User.FullName = "[ZD_EPDB_WEB_Huai].[dbo].[Pu_User]";
                Pu_User.Alias = "b";
                list.Add(Pu_User);
                SuperTableInfo Psy_User = new SuperTableInfo();
                Psy_User.Server = 2;
                Psy_User.FullName = "[ZD_EPDB_WEB_Huai].[dbo].[Psy_User]";
                Psy_User.Alias = "c";
                list.Add(Psy_User);
                string selectItems = " select c.imgurl,a.username,a.[content],a.applydate from ";
                string strWhere = " where b.UserID = c.UserID and a.username=b.username and a.Newsid=" + Newsid + " order by a.id desc  ";
                ds3 = AcrossServersQuery.Query(selectItems,list,strWhere);

OK，结束。