Oracle数据表的主键信息导入到SQL Server中

   因为工作需要，将原来在Oracle数据库中开发一个项目程序转移到Sql Server中。在数据库的导入导出的时候遇到了麻烦。使用SQL Server的dts不能导出Oracle中表的主键资料！这下麻烦了！！后来尝试使用PB的数据管道，可以是可以，不过就是需要逐个表的进行指定。表的数量不小，逐个指定比较浪费时间。所以就写了一个小程序，从Oracle中读出各个表的主键信息，然后生成符合T-SQL语法的生成主键语句
源程序如下：

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using DB;

namespace Ora2SQL
{
    /**//// <summary>
    /// Form1 的摘要说明。
    /// </summary>
    public class Form1 : System.Windows.Forms.Form
    {
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.TextBox OraConn;
        private System.Windows.Forms.TextBox SQLConn;
        private System.Windows.Forms.DataGrid Grid;
        private System.Windows.Forms.Button button2;
        private System.Windows.Forms.RichTextBox SQLText;
        private System.Windows.Forms.ComboBox DBType;
        /**//// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1()
        {
            //
            // Windows 窗体设计器支持所必需的
            //
            InitializeComponent();

            //
            // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
            //
        }

        /**//// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        protected override void Dispose( bool disposing )
        {
            if( disposing )
            {
                if (components != null) 
                {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        Windows 窗体设计器生成的代码#region Windows 窗体设计器生成的代码
        /**//// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.button1 = new System.Windows.Forms.Button();
            this.OraConn = new System.Windows.Forms.TextBox();
            this.SQLConn = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.Grid = new System.Windows.Forms.DataGrid();
            this.button2 = new System.Windows.Forms.Button();
            this.SQLText = new System.Windows.Forms.RichTextBox();
            this.DBType = new System.Windows.Forms.ComboBox();
            ((System.ComponentModel.ISupportInitialize)(this.Grid)).BeginInit();
            this.SuspendLayout();
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(424, 88);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(75, 40);
            this.button1.TabIndex = 0;
            this.button1.Text = "读Oracle主键信息";
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // OraConn
            // 
            this.OraConn.Location = new System.Drawing.Point(88, 8);
            this.OraConn.Name = "OraConn";
            this.OraConn.Size = new System.Drawing.Size(448, 21);
            this.OraConn.TabIndex = 1;
            this.OraConn.Text = "Provider=OraOLEDB.Oracle;Password=missrain;User ID=dictdb;Data Source=lnserp";
            // 
            // SQLConn
            // 
            this.SQLConn.Location = new System.Drawing.Point(88, 40);
            this.SQLConn.Name = "SQLConn";
            this.SQLConn.Size = new System.Drawing.Size(448, 21);
            this.SQLConn.TabIndex = 2;
            this.SQLConn.Text = "provider=SQLOLEDB;server=luneng-luneng\server;user id=appdb;password=missrain;dat" +
                "abase=appdb";
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(0, 8);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(79, 17);
            this.label1.TabIndex = 3;
            this.label1.Text = "Oracle连接串";
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(8, 40);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(60, 17);
            this.label2.TabIndex = 4;
            this.label2.Text = "SQL连接串";
            // 
            // Grid
            // 
            this.Grid.DataMember = "";
            this.Grid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.Grid.Location = new System.Drawing.Point(16, 72);
            this.Grid.Name = "Grid";
            this.Grid.Size = new System.Drawing.Size(376, 184);
            this.Grid.TabIndex = 5;
            // 
            // button2
            // 
            this.button2.Location = new System.Drawing.Point(424, 144);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(75, 56);
            this.button2.TabIndex = 6;
            this.button2.Text = "主键信息写入到SQL Server";
            this.button2.Click += new System.EventHandler(this.button2_Click);
            // 
            // SQLText
            // 
            this.SQLText.Location = new System.Drawing.Point(16, 264);
            this.SQLText.Name = "SQLText";
            this.SQLText.Size = new System.Drawing.Size(528, 192);
            this.SQLText.TabIndex = 8;
            this.SQLText.Text = "";
            // 
            // DBType
            // 
            this.DBType.Location = new System.Drawing.Point(416, 208);
            this.DBType.Name = "DBType";
            this.DBType.Size = new System.Drawing.Size(121, 20);
            this.DBType.TabIndex = 9;
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
            this.ClientSize = new System.Drawing.Size(552, 461);
            this.Controls.Add(this.DBType);
            this.Controls.Add(this.SQLText);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.Grid);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.SQLConn);
            this.Controls.Add(this.OraConn);
            this.Controls.Add(this.button1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.Form1_Load);
            ((System.ComponentModel.ISupportInitialize)(this.Grid)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        /**//// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main() 
        {
            Application.Run(new Form1());
        }

        private void button1_Click(object sender, System.EventArgs e)
        {
            Database db = new Database();
            db.IsSqlDataBase = false;
            db.IsSql = true;
            db.ConnectionString = OraConn.Text;
            DataSet ds;
            string SQL = "SELECT a.table_name,a.column_name FROM all_cons_columns a, all_constraints b WHERE a.owner = b.owner  AND a.constraint_name = b.constraint_name  AND a.table_name = b.table_name AND b.constraint_type = 'P' and a.owner = 'DICTDB'";
            db.RunProc(SQL,out ds);
            Grid.DataSource = ds.Tables[0];
            
        }

        private void button2_Click(object sender, System.EventArgs e)
        {
            Database db = new Database();
            db.IsSqlDataBase = false;
            db.IsSql = true;
            db.ConnectionString = OraConn.Text;
            DataSet oratableds;
            DataSet orads;
            string SQL = "SELECT distinct(a.table_name) as table_name  FROM all_cons_columns a, all_constraints b WHERE a.owner = b.owner   AND a.constraint_name = b.constraint_name   AND a.table_name = b.table_name   AND b.constraint_type = 'P'   and a.owner = '"+DBType.Text+"' ";
            string ResultSQL = "";
            bool flag;
            db.RunProc(SQL,out oratableds);
            string tablename="";
            foreach(DataRow dr in oratableds.Tables[0].Rows){
                tablename=Convert.ToString(dr["table_name"]);
                SQL= "SELECT a.table_name,a.column_name FROM all_cons_columns a, all_constraints b WHERE a.owner = b.owner  AND a.constraint_name = b.constraint_name  AND a.table_name = b.table_name AND b.constraint_type = 'P' and a.owner = '"+DBType.Text+"' and a.table_name='"+tablename+"'";
                db.RunProc(SQL,out orads);
                ResultSQL += "ALTER TABLE "+tablename+" WITH NOCHECK ADD ";
                ResultSQL += "    CONSTRAINT PK_"+tablename+" PRIMARY KEY  CLUSTERED ";
                ResultSQL += "(";
                flag = false;
                foreach(DataRow sdr in orads.Tables[0].Rows){
                    if (flag)
                        ResultSQL += ","+Convert.ToString(sdr["column_name"]);
                    else
                        ResultSQL += Convert.ToString(sdr["column_name"]);
                    flag= true;
                }
                ResultSQL += ")   go ";

            }            
            SQLText.Text = ResultSQL;                    
        }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            
            DBType.Items.Add("DICTDB");
            DBType.Items.Add("USERDB");
        }
    }
}

目前的功能很简单，回头可以在改进改进。做成一个导入导出的小工具出来。