1、附加数据库

下载的文件为sql2000的，sqlserver版本不能超过2008r2，不然不能完成附加

附加数据库sql语句如下：

EXEC sp_attach_db "QunInfo1", "H:\QunData\QunInfo1_Data.MDF"
EXEC sp_attach_db "QunInfo2", "H:\QunData\QunInfo2_Data.MDF"
EXEC sp_attach_db "QunInfo3", "H:\QunData\QunInfo3_Data.MDF"
EXEC sp_attach_db "QunInfo4", "H:\QunData\QunInfo4_Data.MDF"
EXEC sp_attach_db "QunInfo5", "H:\QunData\QunInfo5_Data.MDF"
EXEC sp_attach_db "QunInfo6", "H:\QunData\QunInfo6_Data.MDF"
EXEC sp_attach_db "QunInfo7", "H:\QunData\QunInfo7_Data.MDF"
EXEC sp_attach_db "QunInfo8", "H:\QunData\QunInfo8_Data.MDF"
EXEC sp_attach_db "QunInfo9", "H:\QunData\QunInfo9_Data.MDF"
EXEC sp_attach_db "QunInfo10", "H:\QunData\QunInfo10_Data.MDF"
EXEC sp_attach_db "QunInfo11", "H:\QunData\QunInfo11_Data.MDF"
          
EXEC sp_attach_db "GroupData1", "H:\QunData\GroupData1_Data.MDF"
EXEC sp_attach_db "GroupData2", "H:\QunData\GroupData2_Data.MDF"
EXEC sp_attach_db "GroupData3", "H:\QunData\GroupData3_Data.MDF"
EXEC sp_attach_db "GroupData4", "H:\QunData\GroupData4_Data.MDF"
EXEC sp_attach_db "GroupData5", "H:\QunData\GroupData5_Data.MDF"
EXEC sp_attach_db "GroupData6", "H:\QunData\GroupData6_Data.MDF"
EXEC sp_attach_db "GroupData7", "H:\QunData\GroupData7_Data.MDF"
EXEC sp_attach_db "GroupData8", "H:\QunData\GroupData8_Data.MDF"
EXEC sp_attach_db "GroupData9", "H:\QunData\GroupData9_Data.MDF"
EXEC sp_attach_db "GroupData10", "H:\QunData\GroupData10_Data.MDF"
EXEC sp_attach_db "GroupData11", "H:\QunData\GroupData11_Data.MDF"

2、建立索引

IF not EXISTS (SELECT indid FROM GroupData9.dbo.sysindexes WHERE name = 'ix_QQNum_Group801')
CREATE INDEX ix_QQNum_Group801 On GroupData9.dbo.Group801(QQNum);
IF not EXISTS (SELECT indid FROM GroupData9.dbo.sysindexes WHERE name = 'ix_QQNum_Group802')
CREATE INDEX ix_QQNum_Group802 On GroupData9.dbo.Group802(QQNum);
IF not EXISTS (SELECT indid FROM GroupData9.dbo.sysindexes WHERE name = 'ix_QQNum_Group803')
CREATE INDEX ix_QQNum_Group803 On GroupData9.dbo.Group803(QQNum);

因为需要建立索引的表太多了，复制的话要累死人的，so，程序生成之，C#代码如下：

private static StringBuilder GetSqlIndexStr()
        {
            StringBuilder sb = new StringBuilder();
       
            for (int j = 1; j <= 11; j++)
            {
                for (int i = 1; i <= 100; i++)
                {
                    int temp = 100 * j + i - 100;
                    sb.Append(string.Format("IF not EXISTS (SELECT indid FROM GroupData{1}.dbo.sysindexes WHERE name = 'ix_QQNum_Group{0}')", temp.ToString(), j.ToString()));
                    sb.Append("\r\n");
                    sb.Append(string.Format("CREATE INDEX ix_QQNum_Group{0} On GroupData{1}.dbo.Group{0}(QQNum);", temp.ToString(), j.ToString()));
                    sb.Append("\r\n");
                }
                sb.Append("\r\n");
                sb.Append("\r\n");
            }
            return sb;
        }

注意：建立索引过程中，最好分批执行建索引的sql语句，不然可能会卡死。

3、查询（建立存储过程）

(1)查询QQ信息

CREATE PROCEDURE REN_CX @QQNum int=null
AS
BEGIN
declare @sql varchar(8000)
declare @dbIdx int = 1
-------------------------------------------------------------
if OBJECT_ID('tempdb.dbo.#QunList') is not null
drop table #QunList
   
--新建了一个临时表
create table #QunList
(
QQNum int,
Nick varchar(20),
Age int,
Gender int,
Auth int,
QunNum int
)
   
--------------------------------------------------------------
-- Search QunList
--一个嵌套循环查找 一个表一个表找，找到一个插入临时表一条
while @dbIdx <= 11
begin
declare @tblIdx int = 1
declare @tblName varchar(50)
   
while @tblIdx <= 100
begin
set @tblName = 'GroupData' + CONVERT(varchar(2), @dbIdx) + '.dbo.Group'
set @tblName += CONVERT(varchar(5), (@dbIdx - 1) * 100 + @tblIdx)
   
set @sql = 'select QQNum, Nick, Age,Gender, Auth,QunNum from '
set @sql += @tblName + ' where QQNum=' + CONVERT(varchar(15), @QQNum)
   
insert into #QunList(QQNum, Nick,Age,Gender,Auth,QunNum)  exec(@sql)
   
print @tblname + ' OK'
   
set @tblIdx += 1    --循环递增
end
   
set @dbIdx += 1   --外层递增
end
   
-----------------------------------------------------------------------
--显示
   
select * from #QunList
END
GO

(2)查询群消息

--建立查询群信息的存储过程
CREATE  PROCEDURE QUNXX_CX @QunNum int=null
as
BEGIN
declare @sql varchar(8000)
set @sql = 'select QunNum,Title,QunText from QunInfo' + CONVERT(varchar(5), @QunNum / 10000000 + 1) + '.dbo.'
set @sql += 'QunList' + CONVERT(varchar(10), @QunNum / 1000000 + 1) + ' where QunNum=' + Convert(varchar(20),@QunNum)
exec(@sql)
print(@sql)
END
GO

(3)查询群成员

--建立群成员的存储过程
CREATE  PROCEDURE QUNCY_CX @QunNum int=null
as
BEGIN
declare @sql varchar(8000)
   
--群成员
set @sql = 'select * from GroupData' + CONVERT(varchar(5), @QunNum / 10000000 + 1) + '.dbo.'
set @sql += 'Group' + CONVERT(varchar(10), @QunNum / 100000 + 1) + ' where QunNum=' + Convert(varchar(20),@QunNum)
   
exec(@sql)
print(@sql)
END
GO

执行完上面的3个SQL，存储过程就那建好了。

下面为执行：

EXEC  REN_CX    QQ号
EXEC  QUNXX_CX  群号
EXEC  QUNCY_CX  群号

4、C#查询

关键代码如下：

//数据库连接
SqlConnection coon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
    DataSet finaldata = new DataSet();   
   
    public DataSet QueryREN(string _QueryREN)
    {
        string sqlstr = " EXEC REN_CX   "+ _QueryREN;   //执行存储过程
        SqlDataAdapter da = new SqlDataAdapter(sqlstr, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
}

//单击事件
    protected void Button1_Click(object sender, EventArgs e)
    {
        string ren = TextBox1.Text;
        try
        {
            if (ren != null || Convert.ToInt32(ren) > 10000)
            {
                if (QueryREN(ren) == null || QueryREN(ren).Tables[0].Rows.Count == 0)
                { Label1.Text = "查无此人"; }
                else
                {
                    finaldata.Merge(QueryREN(ren));
                    GridView1.DataSource = finaldata;    //绑定数据
                    GridView1.DataBind();   
                }
            }
            else
            {
                Label1.Text = "重新输入";
            }
        }
        catch(Exception ex)
        {Label1.Text = "BUG:"+ex.ToString();    //BUG测试
        }
    }