Exemplo n.º 1
0
        string GetDbColumnType(SOColumn c)
        {
            string s = c.NativeType;

            if (c.Length == -1)
            {
                return(s);
            }

            switch (c.DataType)
            {
            case System.Data.DbType.AnsiString:
            case System.Data.DbType.AnsiStringFixedLength:
            case System.Data.DbType.String:
            case System.Data.DbType.StringFixedLength:
                //s = string.Format("{0}({1})", c.NativeType, c.NativeType.StartsWith("n") ? c.Size / 2 : c.Size);
                s = string.Format("{0}({1})", c.NativeType, c.Length);
                break;

            case System.Data.DbType.Currency:
            case System.Data.DbType.Decimal:
            case System.Data.DbType.Single:
                s = string.Format("{0}({1}, {2})", c.NativeType, c.Precision, c.Scale);
                break;

            default:
                s = c.NativeType;
                break;
            }

            return(s);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public override List <SOColumn> GetTableColumnList(SOTable table)
        {
            string cmdText = string.Format(@"SELECT *  
                FROM INFORMATION_SCHEMA.`COLUMNS` 
                WHERE table_schema='{0}' AND table_name='{1}' 
                ORDER BY ordinal_position;", table.Database.Name, table.Name);

            List <SOColumn> columnList = new List <SOColumn>();
            DataTable       dt         = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent       = table,
                    Name         = row["column_name"].ToString(),
                    DefaultValue = row["column_default"].ToString(),
                    Nullable     = row["is_nullable"].ToString().ToUpper() == "YES",
                    NativeType   = row["data_type"].ToString(),
                    Identify     = row["extra"].ToString().IndexOf("auto_increment") != -1,
                    //ForeignKey
                    Length     = ConvertUtil.ToInt32(row["character_maximum_length"], -1),
                    Precision  = ConvertUtil.ToInt32(row["numeric_precision"], -1),
                    PrimaryKey = row["column_key"].ToString() == "PRI",
                    Scale      = ConvertUtil.ToInt32(row["numeric_scale"], -1),
                    Comment    = row["column_comment"].ToString()
                };

                column.DataType = this.GetDbType(column.NativeType);
                columnList.Add(column);
            }

            return(columnList);
        }
Exemplo n.º 3
0
        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public override List <SOColumn> GetTableColumnList(SOTable table)
        {
            string cmdText = string.Format(@"use {2};exec sp_columns '{0}','{1}','{2}';", table.Name, table.Owner, table.Database.Name);

            List <SOColumn> columnList = new List <SOColumn>();
            List <string>   pkList     = GetPrimaryKeys(table);
            DataTable       dt         = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent       = table,
                    Name         = row["column_name"].ToString(),
                    DefaultValue = row["column_def"].ToString(),
                    Nullable     = row["is_nullable"].ToString().ToUpper() == "YES",
                    NativeType   = row["type_name"].ToString().Replace(" identity", ""),
                    Identify     = row["type_name"].ToString().IndexOf("identity") != -1,
                    //ForeignKey
                    Length    = ConvertUtil.ToInt32(row["length"], -1),
                    Precision = ConvertUtil.ToInt32(row["precision"], -1),
                    Scale     = ConvertUtil.ToInt32(row["scale"], -1),
                };

                column.PrimaryKey = pkList.Contains(column.Name);
                column.DataType   = this.GetDbType(column.NativeType);
                column.Comment    = GetColumnComment(column);
                columnList.Add(column);
            }

            return(columnList);
        }
Exemplo n.º 4
0
        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public virtual List <SOColumn> GetTableColumnList(SOTable table)
        {
            List <SOColumn> list = new List <SOColumn>();

            string[] restrictions = new string[4];
            restrictions[0] = table.Database.Name;
            restrictions[2] = table.Name;
            DataTable dt = GetSchema(MetaDataCollectionName_Columns, restrictions);

            foreach (DataRow dr in dt.Rows)
            {
                SOColumn c = new SOColumn();
                c.Name         = dr["column_name"].ToString();
                c.Comment      = c.Name;
                c.Parent       = table;
                c.DefaultValue = ConvertUtil.ToString(dr["column_default"]);
                c.Nullable     = dr["is_nullable"].ToString() == "YES" ? true : false;
                c.NativeType   = dr["data_type"].ToString();
                c.Length       = ConvertUtil.ToInt32(dr["character_maximum_length"], -1);
                c.Precision    = ConvertUtil.ToInt32(dr["numeric_precision"], -1);
                c.Scale        = ConvertUtil.ToInt32(dr["numeric_scale"], -1);

                c.DataType = this.GetDbType(c.NativeType);

                list.Add(c);
            }

            return(list);
        }
Exemplo n.º 5
0
        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public override List <SOColumn> GetTableColumnList(SOTable table)
        {
            string cmdText = string.Format(@"select c.COLUMN_ID,c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH,c.DATA_PRECISION,c.DATA_SCALE,c.NULLABLE,c.DATA_DEFAULT,c.CHAR_COL_DECL_LENGTH,c.CHAR_LENGTH,cc.COMMENTS 
                from dba_tab_columns c left join dba_col_comments cc on c.table_name=cc.table_name and c.column_name=cc.column_name 
                where c.owner='{0}' and cc.owner='{0}' and c.table_name='{1}' order by c.column_id", table.Database.Name, table.Name);

            if (!IsDBA)
            {
                cmdText = string.Format(@"select c.COLUMN_ID,c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH,c.DATA_PRECISION,c.DATA_SCALE,c.NULLABLE,c.DATA_DEFAULT,c.CHAR_COL_DECL_LENGTH,c.CHAR_LENGTH,cc.COMMENTS
                from user_tab_columns c left join user_col_comments cc on c.table_name = cc.table_name and c.column_name = cc.column_name
                where c.table_name='{0}' order by c.column_id", table.Name);
            }
            List <SOColumn> columnList = new List <SOColumn>();
            List <string>   pkList     = GetPrimaryKeys(table);
            DataTable       dt         = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            StringCollection sc = new StringCollection();

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent       = table,
                    Name         = row["COLUMN_NAME"].ToString(),
                    DefaultValue = row["DATA_LENGTH"].ToString(),
                    Nullable     = row["NULLABLE"].ToString().ToUpper() == "Y",
                    NativeType   = row["DATA_TYPE"].ToString().Replace(" identity", ""),
                    //Identify = row["type_name"].ToString().IndexOf("identity") != -1,
                    Identify = false,
                    //ForeignKey
                    Length    = ConvertUtil.ToInt32(row["CHAR_LENGTH"], 0),
                    Precision = ConvertUtil.ToInt32(row["DATA_PRECISION"], 0),
                    Scale     = ConvertUtil.ToInt32(row["DATA_SCALE"], 0),
                    Comment   = row["COMMENTS"].ToString()
                };

                column.PrimaryKey = pkList.Contains(column.Name);
                column.DataType   = this.GetDbType(column.NativeType, column.Precision, column.Scale);

                if (!sc.Contains(column.Name))
                {
                    columnList.Add(column);
                }

                sc.Add(column.Name);
            }

            return(columnList);
        }
Exemplo n.º 6
0
        public void DoBuildCode()
        {
            Config.MainForm.SetStatusText("正在生成代码...");
            textEditorControl1.SaveFile(gbTemplateFile.Text);

            TableHost host = new TableHost();

            host.Table        = this.Table;
            host.TemplateFile = gbTemplateFile.Text;

            List <SOColumn> columnList = new List <SOColumn>();

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Cells[0].FormattedValue.ToString().ToLower() == "true")
                {
                    SOColumn c = row.DataBoundItem as SOColumn;
                    columnList.Add(c);
                }
            }

            host.ColumnList = columnList;
            Engine engine        = new Engine();
            var    sw            = new Stopwatch();
            var    content       = File.ReadAllText(host.TemplateFile);
            string outputContent = engine.ProcessTemplate(content, host);

            StringBuilder sb = new StringBuilder();

            if (host.ErrorCollection.HasErrors)
            {
                foreach (CompilerError err in host.ErrorCollection)
                {
                    sb.AppendLine(err.ToString());
                }
                outputContent = outputContent + Environment.NewLine + sb.ToString();
            }

            textEditorControl2.Text = outputContent;
            tabControl1.SelectedTab = tabPage2;
            textEditorControl2.Refresh();

            Config.MainForm.SetStatusText();
        }
        public static SOColumn ToSOColumn(PDColumn column)
        {
            SOColumn c = new SOColumn();

            c.Name         = column.Code;
            c.Comment      = string.IsNullOrEmpty(column.Comment) ? column.Name : column.Comment;
            c.PrimaryKey   = column.IsPK;
            c.ForeignKey   = column.IsFK;
            c.Identify     = column.Identity;
            c.Length       = column.Length;
            c.NativeType   = column.DataType.Split('(')[0];
            c.Nullable     = !column.Mandatory;
            c.DefaultValue = column.DefaultValue;
            c.Precision    = column.Length;
            c.Scale        = column.Precision; //pdm的精度对应数据库的小数位数
            c.DataType     = PDMDataTypeToDbType(column);

            return(c);
        }
Exemplo n.º 8
0
        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public override List <SOColumn> GetTableColumnList(SOTable table)
        {
            string cmdText = string.Format(@"use [{2}];select   colm.name column_name, object_definition(colm.default_object_id) as column_def, systype.name type_name, colm.is_identity, colm.is_nullable ,
                                                        cast(colm.max_length as int) length, cast(colm.precision as int) precision, cast(colm.scale as int) scale, colm.is_computed
                                            from     sys.columns colm
                                                inner join sys.types systype on colm.system_type_id = systype.system_type_id and colm.user_type_id = systype.user_type_id
                                            where    colm.object_id = object_id('{0}')
                                            order by colm.column_id;", table.Name, table.Owner, table.Database.Name);

            List <SOColumn> columnList = new List <SOColumn>();
            List <string>   pkList     = GetPrimaryKeys(table);
            DataTable       dt         = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
            string          sql        = string.Format(@"select  col.name from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid
                                                 and OBJECT_NAME(f.fkeyid)  ='{0}'; ", table.Name);

            string fkcol = this.DbProvider.ExecuteScalar <string>(CommandType.Text, sql);

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent       = table,
                    Name         = row["column_name"].ToString(),
                    DefaultValue = row["column_def"].ToString(),
                    Nullable     = row["is_nullable"].ToString() == "True",
                    NativeType   = row["type_name"].ToString(),
                    Identify     = row["is_identity"].ToString() == "True",
                    Computed     = row["is_computed"].ToString() == "True",
                    ForeignKey   = row["column_name"].ToString() == fkcol,
                    Length       = ConvertUtil.ToInt32(row["length"], -1),
                    Precision    = ConvertUtil.ToInt32(row["precision"], -1),
                    Scale        = ConvertUtil.ToInt32(row["scale"], -1),
                };

                column.PrimaryKey = pkList.Contains(column.Name);
                column.DataType   = this.GetDbType(column.NativeType);
                column.Comment    = GetColumnComment(column);
                columnList.Add(column);
            }

            return(columnList);
        }
Exemplo n.º 9
0
        private string GetColumnComment(SOColumn column)
        {
            string key = string.Format("{0}_{1}_{2}", column.Database.Name, column.Parent.Name, column.Name);

            lock (syncObject)
            {
                if (dic.ContainsKey(key))
                {
                    return(dic[key]);
                }
                else
                {
                    string    cmdText = string.Format(@"use [{0}];
                                                    SELECT major_id, minor_id, t.name AS 'table_name', c.name AS 'column_name', value AS 'comment'
                                                    FROM sys.extended_properties AS ep
                                                    INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
                                                    INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
                                                    WHERE class = 1;", column.Database.Name);
                    DataTable dt      = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText).Tables[0];
                    foreach (DataRow row in dt.Rows)
                    {
                        string tempKey = string.Format("{0}_{1}_{2}", column.Database.Name, row["table_name"].ToString(), row["column_name"].ToString());
                        if (dic.ContainsKey(tempKey))
                        {
                            continue;
                        }
                        dic.Add(tempKey, row["comment"].ToString());
                    }
                }

                if (dic.ContainsKey(key))
                {
                    return(dic[key]);
                }
                else
                {
                    return(column.Name);
                }
            }
        }
Exemplo n.º 10
0
        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public override List <SOColumn> GetTableColumnList(SOTable table)
        {
            List <SOColumn> list = new List <SOColumn>();

            string[] restrictions = new string[4];
            restrictions[0] = table.Database.Name;
            restrictions[2] = table.Name;
            DataTable dt = GetSchema(MetaDataCollectionName_Columns, restrictions);

            List <SOIndex> indexList = this.GetTableIndexList(table);

            foreach (DataRow dr in dt.Rows)
            {
                SOColumn c = new SOColumn();
                c.Name    = dr["column_name"].ToString();
                c.Comment = dr["DESCRIPTION"].ToString();
                c.Parent  = table;

                if (dr["COLUMN_HASDEFAULT"].ToString().ToLower() != "false")
                {
                    c.DefaultValue = ConvertUtil.ToString(dr["COLUMN_DEFAULT"]);
                }

                c.Nullable   = dr["IS_NULLABLE"].ToString().ToLower() == "true" ? true : false;
                c.NativeType = dr["DATA_TYPE"].ToString();
                c.Length     = ConvertUtil.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"], -1);
                c.Precision  = ConvertUtil.ToInt32(dr["NUMERIC_PRECISION"], -1);
                c.Scale      = ConvertUtil.ToInt32(dr["NUMERIC_SCALE"], -1);

                c.DataType   = this.GetDbType(c.NativeType);
                c.PrimaryKey = IndexColumnIsPrimary(indexList, c.Name);
                c.Identify   = IndexColumnIsIdentity(indexList, c.Name);
                c.Computed   = false;

                list.Add(c);
            }

            return(list);
        }
Exemplo n.º 11
0
        /// <summary>
        /// 获取视图所拥有的列列表
        /// </summary>
        /// <param name="view"></param>
        /// <returns></returns>
        public override List <SOColumn> GetViewColumnList(SOView view)
        {
            string cmdText = string.Format(@"use [{2}];exec sp_columns '{0}','{1}','{2}';", view.Name, view.Owner, view.Database.Name);

            List <SOColumn> columnList = new List <SOColumn>();
            DataTable       dt         = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];


            string sql = string.Format(@"select  col.name from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid
                                                 and OBJECT_NAME(f.fkeyid)  ='{0}'; ", view.Name);

            string fkcol = this.DbProvider.ExecuteScalar <string>(CommandType.Text, sql);

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent       = view,
                    Name         = row["column_name"].ToString(),
                    DefaultValue = row["column_def"].ToString(),
                    Nullable     = row["is_nullable"].ToString().ToUpper() == "YES",
                    NativeType   = row["type_name"].ToString().Replace(" identity", ""),
                    Identify     = row["type_name"].ToString().IndexOf("identity") != -1,
                    Computed     = false,
                    ForeignKey   = row["column_name"].ToString() == fkcol,
                    Length       = ConvertUtil.ToInt32(row["length"], -1),
                    Precision    = ConvertUtil.ToInt32(row["precision"], -1),
                    Scale        = ConvertUtil.ToInt32(row["scale"], -1),
                };

                column.DataType = this.GetDbType(column.NativeType);
                column.Comment  = GetColumnComment(column);
                columnList.Add(column);
            }

            return(columnList);
        }
Exemplo n.º 12
0
        //生成数据插入脚本
        private void menuItemBuildInsertSqlForTable_Click(object sender, EventArgs e)
        {
            TreeNode        tn      = tvDatabase.SelectedNode;
            SOTable         table   = tn.Tag as SOTable;
            List <SOColumn> list    = dbSchema.GetTableColumnList(table);
            string          cmdText = string.Format("select * from {0}", table.FullName);
            DataTable       dt      = DbSchemaHelper.Instance.CurrentSchema.ExecuteQuery(table.Database, cmdText).Tables[0];

            StringBuilder sbDocText = new StringBuilder();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("INSERT INTO ");
                    sb.Append(table.FullName + " (");
                    for (int i = 0; i < list.Count; i++)
                    {
                        SOColumn c = list[i];
                        if (c.Identify)
                        {
                            continue;            //忽略标识列
                        }
                        if (i != list.Count - 1)
                        {
                            sb.Append(string.Format("{0},", dbSchema.QuoteIdentifier(c.Name)));
                        }
                        else
                        {
                            sb.Append(string.Format("{0}) VALUES (", dbSchema.QuoteIdentifier(c.Name)));
                        }
                    }
                    for (int i = 0; i < list.Count; i++)
                    {
                        SOColumn c = list[i];
                        if (c.Identify)
                        {
                            continue;            //忽略标识列
                        }
                        string value = "";
                        if (c.DataType == DbType.Boolean)
                        {
                            value = string.Format("'{0}'", Utils.DbBooleanValueToStirng(dr[c.Name]));
                        }
                        else if (c.DataType == DbType.Byte || c.DataType == DbType.Currency || c.DataType == DbType.Decimal ||
                                 c.DataType == DbType.Double || c.DataType == DbType.Int16 || c.DataType == DbType.Int32 ||
                                 c.DataType == DbType.Int64 || c.DataType == DbType.SByte || c.DataType == DbType.Single ||
                                 c.DataType == DbType.UInt16 || c.DataType == DbType.UInt32 || c.DataType == DbType.UInt64)
                        {
                            value = Utils.DbNumericValueToString(dr[c.Name]);
                        }
                        else
                        {
                            value = string.Format("'{0}'", Utils.DbStringValueToString(dr[c.Name]));
                        }

                        if (i != list.Count - 1)
                        {
                            sb.Append(string.Format("{0},", value));
                        }
                        else
                        {
                            sb.Append(string.Format("{0});", value));
                        }
                    }

                    sbDocText.AppendLine(sb.ToString());
                }
            }
            else
            {
                sbDocText.Append(string.Format("表“{0}”没有任何数据!", table.FullName));
            }

            base.MainForm.NewDockDocument(string.Format("{0}_InsertData", table.Name), CodeType.TSQL, sbDocText.ToString());
        }
Exemplo n.º 13
0
 /// <summary>
 /// 获取指定语言的类型字符串
 /// </summary>
 /// <param name="colItem"></param>
 /// <returns></returns>
 public String GetTypeString(SOColumn colItem)
 {
     return(TypeMapConfigBLL.ConvertToLanguageType(this.typeMapConfigList, colItem.NativeType, colItem.Length));
 }