Example #1
0
        public MySmo.DataType GetDataType(Smo.Column c)
        {
            var dt = c.DataType;

            if (dt.SqlDataType == Smo.SqlDataType.UserDefinedDataType)
            {
                var db = c.GetDatabase();
                var ut = db.UserDefinedDataTypes[c.DataType.Name, c.DataType.Schema];
                var st = Utils.GetSqlDataType(ut.SystemType, ut.MaxLength);
                dt = new Smo.DataType(st)
                {
                    MaximumLength    = ut.MaxLength,
                    NumericPrecision = ut.NumericPrecision,
                    NumericScale     = ut.NumericScale
                };
            }
            return(new MySmo.DataType
            {
                Name = dt.Name,
                Schema = dt.Schema,
                MaximumLength = dt.MaximumLength,
                NumericPrecision = dt.NumericPrecision,
                NumericScale = dt.NumericScale,
                SqlDataType = (MySmo.SqlDataType)(int) dt.SqlDataType
            });
        }
Example #2
0
 Smo.Column AddColumn(Smo.Table t, string columnName, Smo.DataType dt, bool isNullable)
 {
     Smo.Column c = new Smo.Column(t, columnName, dt);
     t.Columns.Add(c);
     c.Nullable = isNullable;
     return(c);
 }
Example #3
0
        public ColumnResource(ServerContext context, string dbName, string schemaName, string tableName, string columnName, IUrlHelper urlHelper)
        {
            this._context = context;

            // Get database by name
            this._context.SmoServer.Databases.Refresh();
            SMO.Database smoDb = this._context.SmoServer.Databases[dbName];
            if (smoDb == null)
            {
                throw new SMO.SmoException(String.Format("Database '{0}' not found.", dbName));
            }

            // Get table by name
            smoDb.Tables.Refresh();
            SMO.Table smoTable = smoDb.Tables[tableName, schemaName];
            if (smoTable == null)
            {
                throw new SMO.SmoException(String.Format("Table '{0}' not found in Schema '{1}' in Database '{1}'.",
                                                         tableName, schemaName, dbName));
            }

            // Get column by name
            smoTable.Columns.Refresh();
            this._smoColumn = smoTable.Columns[columnName];
            if (this._smoColumn == null)
            {
                throw new SMO.SmoException(String.Format("Column {0} not found in Table {1} not found in Database {2}.", columnName, tableName, dbName));
            }

            this._parent   = this._smoColumn.Parent;
            this._smoTable = (SMO.Table) this._parent;  // TODO: handle other types of parents (only handling Table for now)
            this.UpdateLinks(urlHelper);
        }
Example #4
0
        private void info_line_Click(object sender, EventArgs e)
        {
            Server myServer = new Server(@".\MS_SQLSERVER"); //lub (local)

            //Autentykacja Windows
            myServer.ConnectionContext.LoginSecure = true;
            //Autentykacja SQL
            myServer.ConnectionContext.Connect();


            INFO.Database cBaza = myServer.Databases["PROJEKT"];

            INFO.Table cTab = cBaza.Tables["LINE"];
            foreach (INFO.Column item in cTab.Columns)
            {
                MessageBox.Show("Nazwa kolumny: " + item.Name + "\nTyp danych: " + item.DataType.ToString());
            }

            INFO.Column cKol = cTab.Columns["id"];
            MessageBox.Show("Czy kolumna id jest w kluczu głównym tabeli: " + cKol.InPrimaryKey.ToString());
            MessageBox.Show("Czy kolumna id jest kluczem obcym: " + cKol.IsForeignKey.ToString());
            MessageBox.Show("Czy kolumna id jest kolumną wyliczaną: " + cKol.Computed.ToString());
            MessageBox.Show("Czy kolumna id zezwala na NULL-e: " + cKol.Nullable.ToString());

            int licznik = 0;

            foreach (Microsoft.SqlServer.Management.Smo.View view in cBaza.Views)
            {
                if (licznik < 5)
                {
                    MessageBox.Show("Nazwa widoku: " + view.Name + "\nData utworzenia: " + view.CreateDate.ToShortDateString());
                    licznik++;
                }
            }

            //Procedury skałdowe
            int licznik2 = 0;

            foreach (StoredProcedure sp in cBaza.StoredProcedures)
            {
                if (licznik2 < 5)
                {
                    MessageBox.Show("Nazwa procedury: " + sp.Name + "\nLiczba parametrów: " + sp.Parameters.Count.ToString());
                    licznik2++;
                }
            }

            //przegladanie użytkowników DB
            foreach (User user in cBaza.Users)
            {
                MessageBox.Show("User: "******"\nLogin: "******"\nType: " + user.UserType);
            }
        }
Example #5
0
        void OnSelect()
        {
            // Get the ID of the selected field.
            Smo.Column col = (fieldsListBox.SelectedItem as Smo.Column);
            if (col == null)
            {
                return;
            }

            // Append the column name to the format
            formatTextBox.Text += String.Format("[{0}]", col.Name);
        }
Example #6
0
        /// <summary>
        /// Compare 2 schema Tables
        /// </summary>
        /// <param name="sp1">your first smo Table</param>
        /// <param name="sp2">your second smo Table</param>
        /// <param name="checkComments">True if you want to compare comments</param>
        /// <param name="checkBrackets">True if you want to compare scripts with brackets</param>
        /// <param name="ignoreCaseSensitive">True if you want to ignore Case Sensitive. False if Case sensitive</param>
        /// <returns></returns>
        public static KMOCompareInfo CompareSchema(this smo.Table t1, smo.Table t2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true)
        {
            smo.ScriptingOptions so = new smo.ScriptingOptions();
            so.DriAll = true;
            string s1      = String.Join(Environment.NewLine, t1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
            string s2      = String.Join(Environment.NewLine, t2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
            string message = string.Empty;

            if (t1.Columns.Count != t2.Columns.Count)
            {
                message += "Tables don't have the same column count. ";
            }
            foreach (smo.Column c1 in t1.Columns)
            {
                smo.Column c2 = t2.Columns[c1.Name];
                if (c2 == null)
                {
                    message += "The column " + c1.Name + " doesn't exist in the second table. ";
                }
                else if (c2.DataType.Name != c1.DataType.Name || c2.DataType.NumericPrecision != c1.DataType.NumericPrecision || c2.DataType.NumericScale != c1.DataType.NumericScale)
                {
                    message += "The column " + c1.Name + " doesn't have the same type. ";
                }
            }
            foreach (smo.Column c2 in t2.Columns)
            {
                smo.Column c1 = t1.Columns[c2.Name];
                if (c1 == null)
                {
                    message += "The column " + c2.Name + " doesn't exist in the first table. ";
                }
            }
            if (message != string.Empty)
            {
                return(new KMOCompareInfo {
                    IsIdentical = false, Message = message, Script1 = s1, Script2 = s2
                });
            }

            if (KMOCompareHelper.CompareScript(s1, s2, ignoreCaseSensitive, checkComments, checkBrackets))
            {
                return(new KMOCompareInfo {
                    IsIdentical = true, Message = string.Empty, Script1 = s1, Script2 = s2
                });
            }
            else
            {
                return(new KMOCompareInfo {
                    IsIdentical = false, Message = "Script difference", Script1 = s1, Script2 = s2
                });
            }
        }
Example #7
0
 public static void UpdateDataSet(this MSMO.Column column, Data.ApplicationDataSet.TBColumnsRow dataRow)
 {
     try
     {
         //dataRow.X = column.X;
     }
     catch (Exception ex)
     {
         VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
         // TODO(crhodes):
         // Wrap anything above that throws an exception that we want to ignore,
         // e.g. property not available because of SQL Edition.
     }
 }
Example #8
0
 /// <summary>
 /// Initializes a new instance of the Column class.
 /// </summary>
 public Column(SMO.Column column)
 {
     AddinHelper.Common.WriteToDebugWindow(string.Format("SMOH.{0}({1})", "Column", column));
     Name             = column.Name;
     DataType         = column.DataType.ToString();
     Default          = column.Default;
     MaximumLength    = column.DataType.MaximumLength.ToString();
     NumericPrecision = column.DataType.NumericPrecision.ToString();
     NumericScale     = column.DataType.NumericScale.ToString();
     ID           = column.ID.ToString();;
     Identity     = column.Identity.ToString();
     InPrimaryKey = column.InPrimaryKey.ToString();
     IsForeignKey = column.IsForeignKey.ToString();
     Nullable     = column.Nullable.ToString();
 }
Example #9
0
        private static void Update(MSMO.Column column, SQLInformation.Data.ApplicationDataSet.TBColumnsRow dataRow)
        {
            try
            {
                column.UpdateDataSet(dataRow);

                UpdateDatabaseWithSnapShot(dataRow, "");
            }
            catch (Exception ex)
            {
                VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);

                UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
            }
        }
        public override string GetColumnDbDataType(string tableName, string columnName)
        {
            Smo.Column column     = _database.Tables[tableName].Columns[columnName];
            string     dbDataType = column.DataType.SqlDataType.ToString();

            if (column.DataType.SqlDataType == Smo.SqlDataType.VarCharMax)
            {
                dbDataType = "VarChar";
            }
            else if (column.DataType.SqlDataType == Smo.SqlDataType.NVarCharMax)
            {
                dbDataType = "NVarChar";
            }
            else if (column.DataType.SqlDataType == Smo.SqlDataType.VarBinaryMax)
            {
                dbDataType = "VarBinary";
            }
            return(dbDataType);
        }
Example #11
0
        public MySmo.DefaultConstraint GetDefaultConstraint(Smo.Column smo_c, MySmo.Column parentColumn, MySmo.Database parentDatabase = null)
        {
            var dc = smo_c.DefaultConstraint;

            if (dc == null)
            {
                return(null);
            }
            var result = new MySmo.DefaultConstraint
            {
                CreateTime     = dc.CreateDate,
                IsSystemNamed  = dc.IsSystemNamed,
                Name           = dc.Name,
                Text           = dc.Text,
                ParentColumn   = parentColumn,
                ParentDatabase = parentDatabase
            };

            result.ExtendedProperties = GetExtendProperties(result, dc.ExtendedProperties);
            return(result);
        }
Example #12
0
        ///--------------------------------------------------------------------------------
        /// <summary>This loads information from a SQL view.</summary>
        ///
        /// <param name="sqlColumn">The input sql column.</param>
        /// <param name="viewText">View text to get table info.</param>
        ///--------------------------------------------------------------------------------
        public void LoadViewProperty(Microsoft.SqlServer.Management.Smo.Column sqlColumn, string viewText)
        {
            try
            {
                // load the basic view property information
                Urn              = sqlColumn.Urn;
                State            = sqlColumn.State.ToString();
                ReferencedColumn = sqlColumn.Name;

                // hack to get table name, as not currently available via smo
                if (viewText.IndexOf("." + ReferencedColumn) > 0)
                {
                    string s           = viewText.Substring(0, viewText.IndexOf("." + ReferencedColumn));
                    int    spaceIndex  = s.LastIndexOf(' ');
                    int    periodIndex = s.LastIndexOf('.');
                    int    quoteIndex  = s.LastIndexOf('"');
                    if (quoteIndex > spaceIndex)
                    {
                        s = s.Substring(0, quoteIndex);
                        ReferencedTable = s.Substring(s.LastIndexOf('"') + 1);
                    }
                    else if (spaceIndex > periodIndex)
                    {
                        ReferencedTable = s.Substring(spaceIndex + 1);
                    }
                    else
                    {
                        ReferencedTable = s.Substring(periodIndex + 1);
                    }
                }
            }
            catch (ApplicationAbortException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
        }
Example #13
0
        private void columnsPage_ShowFromNext(object sender, EventArgs e)
        {
            columnsGrid.Rows.Clear();
            idColumnComboBox.Items.Clear();

            TableFactory tf        = new TableFactory();
            string       tableName = m_Edit.TableName;

            Smo.Table t = tf.FindTableByName(tableName);

            if (t == null)
            {
                return;
            }

            // Get any domains already associated with the table
            IColumnDomain[] curDomains = m_Edit.ColumnDomains;

            columnsGrid.RowCount = t.Columns.Count;

            for (int i = 0; i < columnsGrid.RowCount; i++)
            {
                Smo.Column c = t.Columns[i];
                idColumnComboBox.Items.Add(c.Name);

                DataGridViewRow row = columnsGrid.Rows[i];
                row.Cells["dgcColumnName"].Value = c.Name;

                Smo.DataType dt       = c.DataType;
                string       dataType = dt.SqlDataType.ToString().ToLower();

                if (dt.SqlDataType == Smo.SqlDataType.Char ||
                    dt.SqlDataType == Smo.SqlDataType.NChar ||
                    dt.SqlDataType == Smo.SqlDataType.VarChar ||
                    dt.SqlDataType == Smo.SqlDataType.NVarChar)
                {
                    dataType += String.Format("({0})", dt.MaximumLength);
                }

                if (!c.Nullable)
                {
                    dataType += " not null";
                }

                row.Cells["dgcDataType"].Value = dataType;

                // Display any domain previously associated with the column
                IColumnDomain cd = Array.Find <IColumnDomain>(curDomains,
                                                              delegate(IColumnDomain tcd) { return(tcd.ColumnName == c.Name); });
                if (cd != null)
                {
                    row.Cells["dgcDomain"].Value = cd.Domain;
                }

                row.Tag = c;
            }

            // Nothing initially selected
            columnsGrid.CurrentCell = null;

            // If we have a simple primary key, assume it's the feature ID column
            if (String.IsNullOrEmpty(m_Edit.IdColumnName))
            {
                Smo.Column pk = TableFactory.GetSimplePrimaryKeyColumn(t);
                if (pk != null)
                {
                    idColumnComboBox.SelectedItem = pk.Name;
                }
            }
            else
            {
                idColumnComboBox.SelectedItem = m_Edit.IdColumnName;
            }
        }
Example #14
0
        private static SQLInformation.Data.ApplicationDataSet.TBColumnsRow Add(Guid tableID, MSMO.Column column)
        {
            SQLInformation.Data.ApplicationDataSet.TBColumnsRow dataRow = null;

            try
            {
                dataRow             = Common.ApplicationDataSet.TBColumns.NewTBColumnsRow();
                dataRow.ID          = Guid.NewGuid();
                dataRow.Name_Column = column.Name;

                dataRow.DBTable_ID   = tableID;
                dataRow.DataType     = column.DataType.ToString();
                dataRow.Identity     = column.Identity;
                dataRow.IsForeignKey = column.IsForeignKey;
                dataRow.Nullable     = column.Nullable;

                dataRow.SnapShotDate  = DateTime.Now;
                dataRow.SnapShotError = "";

                Common.ApplicationDataSet.TBColumns.AddTBColumnsRow(dataRow);
                Common.ApplicationDataSet.TBColumnsTA.Update(Common.ApplicationDataSet.TBColumns);
            }
            catch (Exception ex)
            {
                VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
                // TODO(crhodes):
                // Wrap anything above that throws an exception that we want to ignore,
                // e.g. property not available because of SQL Edition.

                UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
            }

            return(dataRow);
        }
Example #15
0
        private static SQLInformation.Data.ApplicationDataSet.TBColumnsRow GetInfoFromSMO(MSMO.Column column, Guid tableID)
        {
#if TRACE
            long startTicks = VNC.AppLog.Trace5("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
            SQLInformation.Data.ApplicationDataSet.TBColumnsRow dataRow = null;

            try
            {
                var dbs = from tb in Common.ApplicationDataSet.TBColumns
                          where tb.DBTable_ID == tableID
                          select tb;

                var dbs2 = from db2 in dbs
                           where db2.Name_Column == column.Name
                           select db2;

                if (dbs2.Count() > 0)
                {
                    dataRow = dbs2.First();
                    Update(column, dataRow);
                }
                else
                {
                    dataRow = Add(tableID, column);
                }
            }
            catch (Exception ex)
            {
                VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
            }
#if TRACE
            VNC.AppLog.Trace5("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
            return(dataRow);
        }
Example #16
0
 internal Column(Microsoft.SqlServer.Management.Smo.Column Column)
 {
     _column = Column;
 }
Example #17
0
        public GenResult Generate(params Oe.NodeBase[] targetElements)
        {
            #region Init

            GenResult gr;
            var       t  = (Smo.Table)targetElements[0].Tag;
            var       db = t.Parent;

            var        pks = Utils.GetPrimaryKeyColumns(t);
            Smo.Column vc = pks[0], nc = null;

            var sacs = Utils.GetSearchableColumns(t);
            if (sacs.Count == 0)
            {
                nc = vc;
            }
            else
            {
                nc = sacs[0];
            }

            var sb = new StringBuilder();

            #endregion

            #region Gen

            DataSet ds = null;
            try
            {
                ds = db.ExecuteWithResults("SELECT [" + Utils.GetEscapeSqlObjectName(vc.Name) + "], [" + Utils.GetEscapeSqlObjectName(nc.Name) + "] FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + "].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ORDER BY [" + Utils.GetEscapeSqlObjectName(nc.Name) + "]");
            }
            catch { }
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
            {
                gr         = new GenResult(GenResultTypes.Message);
                gr.Message = "当前表中没有数据!生成失败!";
                return(gr);
            }

            var tbn = Utils.GetEscapeSqlObjectName(t.Name);
            sb.Append(@"
/// <summary>
/// " + Utils.GetDescription(t) + @"
/// </summary>
public enum " + tbn + @"
{");
            foreach (DataRow c in ds.Tables[0].Rows)
            {
                sb.Append(@"
    " + Utils.GetEscapeName(c[nc.Name].ToString()) + @" = " + c[vc.Name].ToString() + @",");
            }
            sb.Append(@"
}
            ");

            #endregion

            #region return

            gr = new GenResult(GenResultTypes.CodeSegment);
            gr.CodeSegment.first  = this.Properties[GenProperties.Tips].ToString();
            gr.CodeSegment.second = sb.ToString();
            return(gr);

            //gr = new GenResult(GenResultTypes.CodeSegments);
            //gr.CodeSegments.Add("1", sb.ToString());
            //gr.CodeSegments.Add("2", sb.ToString());
            //return gr;

            //gr = new GenResult(GenResultTypes.Files);
            //gr.Files.Add("1.txt", sb);
            //gr.Files.Add("2.txt", sb);
            //return gr;

            #endregion
        }
        /// <summary>
        /// To Create Table and return the message as String
        /// </summary>
        /// <returns> return the message as String </returns>
        #region Create Table
        public string createTable(string DatabaseName, string TableName, DataTable dtColumns)
        {
            try
            {
                if (SqlServerConnect())
                {
                    Server   srv      = new Server(servConn);
                    Database database = srv.Databases[DatabaseName];
                    if (database != null)
                    {
                        bool tableExists = database.Tables.Contains(TableName);
                        if (tableExists)
                        {
                            SqlServerDisconnect();
                            return("Table Already Exist.kindly Enter Different Table Name");
                        }
                        else
                        {
                            Table tbl = new Table(database, TableName);
                            foreach (DataRow dr in dtColumns.Rows)
                            {
                                string columnName = dr["ColumName"].ToString();
                                string DataType   = dr["DataType"].ToString();
                                string dataSize   = dr["Size"].ToString();
                                Microsoft.SqlServer.Management.Smo.Column columntoAdd = null;
                                switch (DataType)
                                {
                                case "Varchar":
                                    if (dataSize == "max")
                                    {
                                        columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarCharMax);
                                    }
                                    else if (dataSize != "")
                                    {
                                        columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarChar(Convert.ToInt32(dataSize)));
                                    }
                                    break;

                                case "Int":
                                    columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.Int);
                                    break;

                                case "nVarchar":
                                    if (dataSize == "max")
                                    {
                                        columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarCharMax);
                                    }
                                    else if (dataSize != "")
                                    {
                                        columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarChar(Convert.ToInt32(dataSize)));
                                    }
                                    break;
                                }
                                if (columntoAdd != null)
                                {
                                    tbl.Columns.Add(columntoAdd);
                                }
                            }
                            tbl.Create();
                            SqlServerDisconnect();
                            return("Table Created Successfully !");
                        }
                    }
                }
                else
                {
                    return("Enter valid SQL Connection Details");
                }
            }
            catch (Exception ex)
            {
                writeLogMessage(ex.Message.ToString());
            }
            return("Sorry Error While Creating Table");
        }
        /// <summary>
        /// Gets a code member property based on the provided column.
        /// </summary>
        private static CodeMemberProperty GetCodeMemberProperty(ILanguageProvider languageProvider, Smo.Column column)
        {
            var typeName  = languageProvider.GetTypeString(column.DataType);
            var fieldName = languageProvider.GetFormattedInternalFieldName(column.Name);
            var retval    = new CodeMemberProperty
            {
                Name       = column.Name,
                Attributes = MemberAttributes.Public | MemberAttributes.Final,
                Type       = new CodeTypeReference(typeName),
                HasGet     = true,
                HasSet     = true
            };

            retval.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), fieldName)));
            retval.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), fieldName), new CodePropertySetValueReferenceExpression()));

            return(retval);
        }
 /// <summary>
 /// Get the max column length for the specified table and column
 /// </summary>
 /// <param name="tableName"></param>
 /// <param name="columnName"></param>
 /// <returns></returns>
 public override string GetColumnMaxLength(string tableName, string columnName)
 {
     Smo.Column column = _database.Tables[tableName].Columns[columnName];
     return(column.DataType.MaximumLength == -1 ? "MAX" : column.DataType.MaximumLength.ToString());
 }
        /// <summary>
        ///  Gets a code member field based on the provided column.
        /// </summary>
        private static CodeMemberField GetCodeMemberField(ILanguageProvider languageProvider, Smo.Column column)
        {
            var fieldName = languageProvider.GetFormattedInternalFieldName(column.Name);
            var typeName  = languageProvider.GetTypeString(column.DataType);

            return(new CodeMemberField
            {
                Name = fieldName,
                Attributes = MemberAttributes.Private,
                Type = new CodeTypeReference(typeName)
            });
        }