专注于自动化测试,性能测试.......

使用SMO比较表结构差异(原创)

上一篇 / 下一篇 2009-09-28 23:00:36 / 个人分类：C# Programming

今天因为工作需要，需对比SQL Server2005数据库中相同表的字段是否相同。之前，在SQL server2000数据库中做过同样的事情，利用的是VBS脚本操作DMO来实现的。不过很可惜，在SQL server2005已经不在使用DMO，而是启用了新的SMO。而且VBS脚本也不再支持SMO。所以，只能用C#来进行了。具体思路:先比较两个数据库，找到相同的表，然后比较表的列，找出差异。

0wUxP/Z `NR5{r0使用SMO，需先引用Microsoft.SqlServer.ConnectionInfo，

QD;zB7pC)Zz\K7e0Microsoft.SqlServer.Smo 这两个命名空间。

@sH#]5J/|C;|-M5v0源代码：51Testing软件测试网3B'mo5b)l'XYN

&r:f,[7G(Y1E;Y0using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
(\n\iS0using Microsoft.SqlServer.Server;
Tn!bT^{?f6k-W0using Microsoft.SqlServer.Management.Common;
4l [.Iz-iQ+vyX0using Microsoft.SqlServer;
"[:@5V{ qoyD0using System.Data.SqlClient;
fs4U$A+h0using System.Collections;

J;~~,`/M:dx2LU\L051Testing软件测试网+X\6cWY+De_
namespace SMOsample51Testing软件测试网0h#Ql+AfR%J
{
    class Program51Testing软件测试网4p,w4mF$T2DP*Z
    {
        static void Main(string[] args)
p|R6V0Sg0        {51Testing软件测试网)G\~GT
            string connectionString1 = "server =192.168.40.148;database = JcyPD;uid =sa;pwd=1";
            SqlConnection connection1 = new SqlConnection(connectionString1);
            Server server1 = new Server(new ServerConnection(connection1));
            Database db1 = server1.Databases["JcyPD"];

iN"C$Ev5Bl|q0            string connectionString2 = "server =192.168.40.110;database = Jcy40 ;uid =sa;pwd=sa";
            SqlConnection connection2 = new SqlConnection(connectionString2);
~:\[f#}m0            Server server2 = new Server(new ServerConnection(connection2));51Testing软件测试网 j.o.@8Z5k'H
            Database db2 = server2.Databases["Jcy40"];

6{K.os;cOF4N0A0 ArrayList al1 = new ArrayList();51Testing软件测试网"ChW0X7J'X
ArrayList al2 = new ArrayList();51Testing软件测试网6jH7WSF

a,Bq}oLn0            for (int x = 0; x < db1.Tables.Count; x++)51Testing软件测试网H,W%]^!EUy$j
            {
@(g R%\A,K9F0A0                al1.Add(db1.Tables[x].Name.ToString());
            }

            for (int y = 0; y < db2.Tables.Count; y++)
'wT2fc{(m,oj|0            {51Testing软件测试网_5BY2p3p5mx
                al2.Add(db2.Tables[y].Name.ToString());
            }51Testing软件测试网n Pkg6JH4g5n

            for (int m = 0; m < al1.Count; m++)
H*[c}hr@0Qz-T0            {
!}4~\N`k0                for (int n = 0; n < al2.Count; n++)
                {
                    if (al1[m].ToString() == al2[n].ToString())
                    {51Testing软件测试网,H&M uv(ZH5G
                        ArrayList cal1 = new ArrayList();
                        ArrayList cal2 = new ArrayList();51Testing软件测试网/[:Q@xBRX

,B:Un@Qy,e%e0                        foreach(Column c1 in db1.Tables[al1[m].ToString()].Columns)
                        {51Testing软件测试网gyi2i |
                            cal1.Add(c1.Name.ToString());
W#L\h.[Y?6ng f0                        }51Testing软件测试网:Q&xctxX1o

                        foreach (Column c2 in db2.Tables[al2[n].ToString()].Columns)
W8G\Tx f0                        {
                            cal2.Add(c2.Name.ToString());
:e}@L"\v|0                        }51Testing软件测试网 [7jG:AdG

&UtvG6\2Sb1o0                        foreach (string s in cal2)51Testing软件测试网9Z Y(u^G%]wz
                        {51Testing软件测试网'JiT8jtWd
                            if (!cal1.Contains(s))51Testing软件测试网 P%q_tr)~
                            {
+z"nL$F E5Ysjc0                                Console.WriteLine(al2[n].ToString() + " : " + s);

}51Testing软件测试网.IX5E g8E)O
}

                    }
$j^"F]s0                }
4Gqd/^-kb0            }
v*['F^r2Qk#nxF0            Console.WriteLine("完毕!");51Testing软件测试网j:zX4@F,cp
            Console.ReadLine();

}
}
!g9V.hV3m;CWNHM0}
j HP;L2g-?w/eO3e0

收藏举报

TAG:

查看全部评论