Example #1
0
        public DataTable GetColumns(string DatabaseName, string TableName)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("ColumnName", typeof(string));
            dt.Columns.Add("ColumnType", typeof(string));
            dt.Columns.Add("ColumnSize", typeof(int));
            dt.Columns.Add("ColumnKey", typeof(bool));
            dt.Columns.Add("ColumnNull", typeof(bool));
            dt.Columns.Add("ColumnID", typeof(int));
            dt.Columns.Add("ColumnRemark", typeof(string));

            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer2  svr    = new SQLDMO.SQLServer2Class();

            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
                myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
                SQLDMO.Table2 myTb = new SQLDMO.Table2Class();

                foreach (SQLDMO.Table2 tb in myDb.Tables)
                {
                    if (tb.Name == TableName)
                    {
                        myTb = tb;
                    }
                }
                foreach (SQLDMO.Column2 column in myTb.Columns)
                {
                    DataRow dr = dt.NewRow();
                    dr["ColumnName"]   = column.Name;
                    dr["ColumnType"]   = column.Datatype;
                    dr["ColumnSize"]   = column.Length;
                    dr["ColumnKey"]    = column.InPrimaryKey;
                    dr["ColumnNull"]   = column.AllowNulls;
                    dr["ColumnID"]     = column.ID;
                    dr["ColumnRemark"] = column.Properties.Application.ODBCVersionString;


                    dt.Rows.Add(dr);
                }
            }
            catch (Exception e)
            {
                //throw (new Exception("连接数据库出错:" + e.Message));
                ShowError("连接数据库出错:" + e.Message);
                return(null);
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
            return(dt);
        }
Example #2
0
        public bool UpdateTable(string DatabaseName, string TableName, DataTable dt)
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();
            Hashtable ht1 = new Hashtable();
            Hashtable ht2 = new Hashtable();
            Hashtable ht3 = new Hashtable();
            string keyname1 = "";
            string keyname2 = "";

            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
                myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
                SQLDMO.Table2 myTb = new SQLDMO.Table2Class();

                foreach (SQLDMO.Table2 tb in myDb.Tables)
                {
                    if (tb.Name == TableName)
                        myTb = tb;
                }
                foreach (SQLDMO.Column2 column in myTb.Columns)
                {
                    ht1.Add(column.ID, column);
                    if (column.InPrimaryKey)
                        keyname1 = column.Name;
                }

                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["ColumnID"] != DBNull.Value)
                    {
                        ht2.Add(dr["ColumnID"].ToString(), dr);
                    }
                    else
                    {
                        ht3.Add("1", dr);
                    }
                    if (bool.Parse(dr["ColumnKey"].ToString()))
                        keyname2 = dr["ColumnName"].ToString();
                }

                foreach (DictionaryEntry de3 in ht3)
                {
                    DataRow dr3 = (DataRow)de3.Value;
                    SQLDMO.Column2 column = new SQLDMO.Column2Class();
                    column.Name = dr3["ColumnName"].ToString();
                    column.Datatype = dr3["ColumnType"].ToString();
                    column.Length = int.Parse(dr3["ColumnSize"].ToString());
                    column.AllowNulls = bool.Parse(dr3["ColumnNull"].ToString());
                    myTb.Columns.Add(column);
                }

                foreach (DictionaryEntry de1 in ht1)
                {
                    if (ht2.Contains(de1.Key.ToString()))
                    {
                        DataRow dr = (DataRow)ht2[de1.Key.ToString()];
                        SQLDMO.Column2 co = (SQLDMO.Column2)de1.Value;
                        SQLDMO.Column2 cm = (SQLDMO.Column2)myTb.Columns.Item(co.Name);

                        if (dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "timestamp")
                            continue;

                        if (dr["ColumnName"].ToString() == cm.Name && dr["ColumnType"].ToString() == cm.Datatype && int.Parse(dr["ColumnSize"].ToString()) == cm.Length && bool.Parse(dr["ColumnNull"].ToString()) == cm.AllowNulls)
                            continue;

                        if (dr["ColumnName"].ToString() != cm.Name)
                        {
                            cm.Name = dr["ColumnName"].ToString();
                        }
                        if (dr["ColumnType"].ToString() != cm.Datatype)
                        {
                            switch (dr["ColumnType"].ToString())
                            {
                                case "bigint":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "binary":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                    break;
                                case "bit":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "char":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                    break;
                                case "datetime":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "decimal":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "float":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "image":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "int":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "money":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "nchar":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                    break;
                                case "ntext":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "numeric":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 9, 3);
                                    break;
                                case "nvarchar":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                    break;
                                case "real":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "text":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "timestamp":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                    break;
                                case "varchar":
                                    cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                    break;
                            }
                        }
                        if (dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "timestamp")
                        {
                            continue;
                        }
                        else
                        {
                            cm.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
                        }
                    }
                    else
                    {
                        SQLDMO.Column2 cm1=new SQLDMO.Column2Class();
                        cm1=(SQLDMO.Column2)de1.Value;
                        myTb.Columns.Remove(cm1.Name);
                    }
                }

                if (keyname1 != keyname2)
                {
                    if(keyname1!="")
                    myTb.Keys.Remove(keyname1);
                }
                if (keyname2 != "")
                {
                    SQLDMO.Key key = new SQLDMO.KeyClass();
                    key.Name = keyname2;
                    key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
                    key.KeyColumns.Add(keyname2);
                    myTb.Keys.Add(key);
                }
                return true;
            }
            catch (Exception e)
            {
                //throw (new Exception("�������ݿ�����" + e.Message));
                ShowError("������ݿ�ʧ��!" + e.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
        }
Example #3
0
        public DataTable GetColumns(string DatabaseName, string TableName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ColumnName", typeof(string));
            dt.Columns.Add("ColumnType", typeof(string));
            dt.Columns.Add("ColumnSize", typeof(int));
            dt.Columns.Add("ColumnKey", typeof(bool));
            dt.Columns.Add("ColumnNull", typeof(bool));
            dt.Columns.Add("ColumnID", typeof(int));
            dt.Columns.Add("ColumnRemark", typeof(string));

            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();

            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
                myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
                SQLDMO.Table2 myTb = new SQLDMO.Table2Class();

                foreach (SQLDMO.Table2 tb in myDb.Tables)
                {
                    if (tb.Name == TableName)
                        myTb = tb;
                }
                foreach (SQLDMO.Column2 column in myTb.Columns)
                {
                    DataRow dr = dt.NewRow();
                    dr["ColumnName"] = column.Name;
                    dr["ColumnType"] = column.Datatype;
                    dr["ColumnSize"] = column.Length;
                    dr["ColumnKey"] = column.InPrimaryKey;
                    dr["ColumnNull"] = column.AllowNulls;
                    dr["ColumnID"] = column.ID;
                    dr["ColumnRemark"] = column.Properties.Application.ODBCVersionString;

                    dt.Rows.Add(dr);
                }
            }
            catch (Exception e)
            {
                //throw (new Exception("�������ݿ�����" + e.Message));
                ShowError("�������ݿ�����" + e.Message);
                return null;
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
            return dt;
        }
Example #4
0
        public bool UpdateTable(string DatabaseName, string TableName, DataTable dt)
        {
            SQLDMO.Application sqlApp   = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer2  svr      = new SQLDMO.SQLServer2Class();
            Hashtable          ht1      = new Hashtable();
            Hashtable          ht2      = new Hashtable();
            Hashtable          ht3      = new Hashtable();
            string             keyname1 = "";
            string             keyname2 = "";

            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
                myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
                SQLDMO.Table2 myTb = new SQLDMO.Table2Class();

                foreach (SQLDMO.Table2 tb in myDb.Tables)
                {
                    if (tb.Name == TableName)
                    {
                        myTb = tb;
                    }
                }
                foreach (SQLDMO.Column2 column in myTb.Columns)
                {
                    ht1.Add(column.ID, column);
                    if (column.InPrimaryKey)
                    {
                        keyname1 = column.Name;
                    }
                }

                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["ColumnID"] != DBNull.Value)
                    {
                        ht2.Add(dr["ColumnID"].ToString(), dr);
                    }
                    else
                    {
                        ht3.Add("1", dr);
                    }
                    if (bool.Parse(dr["ColumnKey"].ToString()))
                    {
                        keyname2 = dr["ColumnName"].ToString();
                    }
                }


                foreach (DictionaryEntry de3 in ht3)
                {
                    DataRow        dr3    = (DataRow)de3.Value;
                    SQLDMO.Column2 column = new SQLDMO.Column2Class();
                    column.Name       = dr3["ColumnName"].ToString();
                    column.Datatype   = dr3["ColumnType"].ToString();
                    column.Length     = int.Parse(dr3["ColumnSize"].ToString());
                    column.AllowNulls = bool.Parse(dr3["ColumnNull"].ToString());
                    myTb.Columns.Add(column);
                }



                foreach (DictionaryEntry de1 in ht1)
                {
                    if (ht2.Contains(de1.Key.ToString()))
                    {
                        DataRow        dr = (DataRow)ht2[de1.Key.ToString()];
                        SQLDMO.Column2 co = (SQLDMO.Column2)de1.Value;
                        SQLDMO.Column2 cm = (SQLDMO.Column2)myTb.Columns.Item(co.Name);


                        if (dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "timestamp")
                        {
                            continue;
                        }


                        if (dr["ColumnName"].ToString() == cm.Name && dr["ColumnType"].ToString() == cm.Datatype && int.Parse(dr["ColumnSize"].ToString()) == cm.Length && bool.Parse(dr["ColumnNull"].ToString()) == cm.AllowNulls)
                        {
                            continue;
                        }



                        if (dr["ColumnName"].ToString() != cm.Name)
                        {
                            cm.Name = dr["ColumnName"].ToString();
                        }
                        if (dr["ColumnType"].ToString() != cm.Datatype)
                        {
                            switch (dr["ColumnType"].ToString())
                            {
                            case "bigint":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "binary":
                                cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                break;

                            case "bit":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "char":
                                cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                break;

                            case "datetime":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "decimal":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "float":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "image":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "int":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "money":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "nchar":
                                cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                break;

                            case "ntext":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "numeric":
                                cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 9, 3);
                                break;

                            case "nvarchar":
                                cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                break;

                            case "real":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "text":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "timestamp":
                                cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
                                break;

                            case "varchar":
                                cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
                                break;
                            }
                        }
                        if (dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "timestamp")
                        {
                            continue;
                        }
                        else
                        {
                            cm.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
                        }
                    }
                    else
                    {
                        SQLDMO.Column2 cm1 = new SQLDMO.Column2Class();
                        cm1 = (SQLDMO.Column2)de1.Value;
                        myTb.Columns.Remove(cm1.Name);
                    }
                }



                if (keyname1 != keyname2)
                {
                    if (keyname1 != "")
                    {
                        myTb.Keys.Remove(keyname1);
                    }
                }
                if (keyname2 != "")
                {
                    SQLDMO.Key key = new SQLDMO.KeyClass();
                    key.Name = keyname2;
                    key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
                    key.KeyColumns.Add(keyname2);
                    myTb.Keys.Add(key);
                }
                return(true);
            }
            catch (Exception e)
            {
                //throw (new Exception("连接数据库出错:" + e.Message));
                ShowError("添加数据库失败!" + e.Message);
                return(false);
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
        }