コード例 #1
0
ファイル: frmTable.cs プロジェクト: znyet/DapperExtensions
        private List <TableEntity> GetSelectTableList()
        {
            List <TableEntity> tables = new List <TableEntity>();
            // DataGridCell cel=(sender as DataGridCell).
            int count = Convert.ToInt16(this.dataGridView1.Rows.Count.ToString());

            for (int i = 0; i < count; i++)
            {
                DataGridViewCheckBoxCell checkCell = (DataGridViewCheckBoxCell)dataGridView1.Rows[i].Cells["Check"];
                Boolean flag = Convert.ToBoolean(checkCell.Value);
                if (flag == true) //查找被选择的数据行
                {
                    DataGridViewTextBoxCell name     = (DataGridViewTextBoxCell)dataGridView1.Rows[i].Cells["TableName"];
                    DataGridViewTextBoxCell descript = (DataGridViewTextBoxCell)dataGridView1.Rows[i].Cells["TableDescript"];
                    TableEntity             table    = tableList.FirstOrDefault(f => f.Name == name.Value.ToString());
                    try
                    {
                        table.Comment = descript.Value.ToString();
                    }
                    catch { }
                    table.NameUpper = MyUtils.ToUpper(table.Name);
                    table.NameLower = MyUtils.ToLower(table.Name);
                    if (string.IsNullOrEmpty(table.IsIdentity))
                    {
                        table.IsIdentity = "false";
                    }
                    tables.Add(table);
                }
                continue;
            }

            return(tables);
        }
コード例 #2
0
        public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
        {
            string sql = @"SELECT  
ColumnName=a.name, 
IsKey=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
  SELECT name FROM sysindexes WHERE indid in(
   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
   ))) then 1 else 0 end, 
IsIdentity=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 1 else 0 end, 
ColumnType=b.name, 
ColumnLength=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 
DecimalDigit =isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 
ColumnCommnent=isnull(g.[value],''),
AllowNull=case when a.isnullable=1 then 1 else 0 end, 
DefaultValue=isnull(e.text,'')
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype 
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id 
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
where d.name=@name
order by a.id,a.colorder";

            IEnumerable <dynamic> data;

            using (var conn = DbHelper.GetConn())
            {
                data = conn.Query(sql, new { name = tableEntity.Name });
            }

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

            foreach (var item in data)
            {
                ColumnEntity model = new ColumnEntity();
                model.Name      = item.ColumnName;             //列表
                model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
                model.NameLower = MyUtils.ToLower(model.Name); //首字母小写

                if (item.IsKey == 1)
                {
                    tableEntity.KeyName = model.Name;
                    if (item.IsIdentity == 1)
                    {
                        tableEntity.IsIdentity = "true";
                    }
                }
                string columnType = item.ColumnType;//数据类型
                if (string.IsNullOrEmpty(columnType))
                {
                    columnType = "";
                }
                string t = columnType.ToLower();

                var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.SqlServerCSharp].FirstOrDefault(f => f.Name == t);
                if (cs != null)
                {
                    model.CsType = cs.To;
                }
                else
                {
                    model.CsType = ConfigHelper.UnKnowDbType;
                }


                var java = ConfigHelper.DbTypeDictionary[ConfigHelper.SqlServerJava].FirstOrDefault(f => f.Name == t);
                if (java != null)
                {
                    model.JavaType = java.To;
                }
                else
                {
                    model.JavaType = ConfigHelper.UnKnowDbType;
                }


                model.DbType = item.ColumnType + "," + item.ColumnLength + "," + item.DecimalDigit;
                if (ConfigHelper.ColumnComment)
                {
                    model.Comment = item.ColumnCommnent;        //说明
                }
                model.AllowNull    = item.AllowNull.ToString(); //是否允许空
                model.DefaultValue = item.DefaultValue;         //默认值

                columnList.Add(model);
            }

            return(columnList);
        }
コード例 #3
0
        public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
        {
            string sql1 = "set session \"myapp.name\"='" + tableEntity.Name + "';";  //this only select name and comment

            sql1 += "SELECT ";
            sql1 += "a.attname as name,";
            sql1 += "col_description(a.attrelid,a.attnum) as comment, ";
            sql1 += "concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\\(.*\\)')) as type ";
            sql1 += "FROM pg_class as c,pg_attribute as a, pg_type t ";
            sql1 += "where c.relname=current_setting('myapp.name') and a.attrelid =c.oid and a.attnum>0 and a.atttypid=t.oid ";



            string sql2 = "set session \"myapp.name\" = '" + tableEntity.Name + "';";

            sql2 += @"select 
column_name as name,
is_nullable as cannull,
column_default as defaultval,
case  when position('nextval' in column_default)>0 then '1' else '0' end as isidentity, 
case when b.pk_name is null then '0' else '1' end as ispk,
c.DeText as detext
from information_schema.columns 
left join (
    select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint  
    inner join pg_class on pg_constraint.conrelid = pg_class.oid 
    inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and  pg_attr.attnum = pg_constraint.conkey[1] 
    inner join pg_type on pg_type.oid = pg_attr.atttypid
    where pg_class.relname = current_setting('myapp.name') and pg_constraint.contype='p' 
) b on b.colname = information_schema.columns.column_name
left join (
    select attname,description as DeText from pg_class
    left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
    left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
    where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname=current_setting('myapp.name')
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name=current_setting('myapp.name') order by ordinal_position asc";


            IEnumerable <dynamic> data;
            IEnumerable <dynamic> data2;

            using (var conn = DbHelper.GetConn())
            {
                data  = conn.Query(sql1);
                data2 = conn.Query(sql2);
            }
            List <ColumnEntity> columnList = new List <ColumnEntity>();

            foreach (var item in data)
            {
                dynamic ddd = data2.FirstOrDefault(s => s.name == item.name);

                ColumnEntity model = new ColumnEntity();
                model.Name      = item.name;
                model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
                model.NameLower = MyUtils.ToLower(model.Name); //首字母小写

                if (ddd.ispk == "1")
                {
                    tableEntity.KeyName = model.Name;
                    if (ddd.isidentity == "1")
                    {
                        tableEntity.IsIdentity = "true";
                    }
                }

                string columnType = item.type;//数据类型

                if (string.IsNullOrEmpty(columnType))
                {
                    columnType = "";
                }

                string t = columnType.Split('(')[0].ToLower();

                var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.PostgreSqlCSharp].FirstOrDefault(f => f.Name == t);
                if (cs != null)
                {
                    model.CsType = cs.To;
                }
                else
                {
                    model.CsType = ConfigHelper.UnKnowDbType;
                }


                var java = ConfigHelper.DbTypeDictionary[ConfigHelper.PostgreSqlJava].FirstOrDefault(f => f.Name == t);
                if (java != null)
                {
                    model.JavaType = java.To;
                }
                else
                {
                    model.JavaType = ConfigHelper.UnKnowDbType;
                }

                model.DbType = item.type;
                if (ConfigHelper.ColumnComment)
                {
                    model.Comment = ddd.detext; //说明
                }

                model.AllowNull    = ddd.cannull;    //是否允许空
                model.DefaultValue = ddd.defaultval; //默认值

                columnList.Add(model);
            }

            return(columnList);
        }
コード例 #4
0
        public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
        {
            string sql = "pragma table_info('" + tableEntity.Name + "')";

            IEnumerable <dynamic> data;

            using (var conn = DbHelper.GetConn())
            {
                data = conn.Query(sql);
            }

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

            foreach (var item in data)
            {
                ColumnEntity model = new ColumnEntity();
                model.Name = item.name; //列名

                if (item.pk == 1)
                {
                    tableEntity.KeyName = model.Name;
                }

                model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
                model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
                string columnType = item.type;                 //数据类型
                if (string.IsNullOrEmpty(columnType))
                {
                    columnType = "";
                }
                string t = columnType.ToLower();

                var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.SQLiteCSharp].FirstOrDefault(f => f.Name == t);
                if (cs != null)
                {
                    model.CsType = cs.To;
                }
                else
                {
                    model.CsType = ConfigHelper.UnKnowDbType;
                }

                var java = ConfigHelper.DbTypeDictionary[ConfigHelper.SQLiteJava].FirstOrDefault(f => f.Name == t);
                if (java != null)
                {
                    model.JavaType = java.To;
                }
                else
                {
                    model.JavaType = ConfigHelper.UnKnowDbType;
                }


                model.DbType       = item.type;
                model.AllowNull    = Convert.ToString(item.notnull); //是否允许空
                model.DefaultValue = item.dflt_value;                //默认值

                columnList.Add(model);
            }
            return(columnList);
        }
コード例 #5
0
        public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
        {
            string sql = "SHOW FULL COLUMNS FROM " + tableEntity.Name;
            IEnumerable <dynamic> data;

            using (var conn = DbHelper.GetConn())
            {
                data = conn.Query(sql);
            }

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

            foreach (var item in data)
            {
                ColumnEntity model = new ColumnEntity();
                model.Name = item.Field; //列名

                if (!string.IsNullOrEmpty(item.Key) && item.Key == "PRI")
                {
                    tableEntity.KeyName = model.Name;
                    if (!string.IsNullOrEmpty(item.Extra) && item.Extra == "auto_increment")
                    {
                        tableEntity.IsIdentity = "true";
                    }
                }

                model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
                model.NameLower = MyUtils.ToLower(model.Name); //首字母小写

                string columnType = item.Type;                 //数据类型

                if (string.IsNullOrEmpty(columnType))
                {
                    columnType = "";
                }

                string t = columnType.Split('(')[0].ToLower();

                var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.MySqlCSharp].FirstOrDefault(f => f.Name == t);
                if (cs != null)
                {
                    model.CsType = cs.To;
                }
                else
                {
                    model.CsType = ConfigHelper.UnKnowDbType;
                }


                var java = ConfigHelper.DbTypeDictionary[ConfigHelper.MySqlJava].FirstOrDefault(f => f.Name == t);
                if (java != null)
                {
                    model.JavaType = java.To;
                }
                else
                {
                    model.JavaType = ConfigHelper.UnKnowDbType;
                }


                model.DbType = item.Type;
                if (ConfigHelper.ColumnComment)
                {
                    model.Comment = item.Comment;  //说明
                }
                model.AllowNull    = item.Null;    //是否允许空
                model.DefaultValue = item.Default; //默认值

                columnList.Add(model);
            }
            return(columnList);
        }
コード例 #6
0
        public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
        {
            string sql = "SELECT C.COLUMN_ID AS \"Id\",C.TABLE_NAME AS \"TableId\",C.COLUMN_NAME AS \"Name\",C.DATA_TYPE AS \"DbType\",C.DATA_LENGTH AS \"DataLength\",NVL(CC.COMMENTS,C.COLUMN_NAME) AS \"Description\",";

            sql += "to_number(CASE C.NULLABLE WHEN 'N' THEN '0' ELSE '1' END) AS \"IsNullable\",";
            sql += "to_number('0') AS \"AutoIncrement\",";
            sql += "to_number(CASE WHEN P.COLUMN_NAME = C.COLUMN_NAME THEN '1' ELSE '0' END) AS \"IsPrimaryKey\" ";
            sql += "FROM USER_TAB_COLUMNS C ";
            sql += "LEFT JOIN USER_COL_COMMENTS CC ON C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME ";
            sql += "LEFT JOIN ( ";
            sql += "SELECT CU.COLUMN_NAME FROM USER_CONS_COLUMNS CU ";
            sql += "LEFT JOIN USER_CONSTRAINTS AU ON CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME ";
            sql += "WHERE CU.TABLE_NAME = :name AND AU.CONSTRAINT_TYPE='P' ";
            sql += ")P ON C.COLUMN_NAME = P.COLUMN_NAME ";
            sql += "WHERE C.TABLE_NAME = :name ORDER BY C.COLUMN_ID";

            IEnumerable <dynamic> data;

            using (var conn = DbHelper.GetConn())
            {
                data = conn.Query(sql, new { name = tableEntity.Name });
            }

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

            foreach (var item in data)
            {
                ColumnEntity model = new ColumnEntity();
                model.Name      = item.Name;                   //列表
                model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
                model.NameLower = MyUtils.ToLower(model.Name); //首字母小写

                if (item.IsPrimaryKey.ToString() == "1")
                {
                    tableEntity.KeyName = model.Name;
                    if (item.AutoIncrement.ToString() == "1")
                    {
                        tableEntity.IsIdentity = "true";
                    }
                }
                string t = item.DbType;//数据类型

                if (string.IsNullOrEmpty(t))
                {
                    t = "";
                }

                var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.OracleCSharp].FirstOrDefault(f => f.Name == t);
                if (cs != null)
                {
                    model.CsType = cs.To;
                }
                else
                {
                    model.CsType = ConfigHelper.UnKnowDbType;
                }

                var java = ConfigHelper.DbTypeDictionary[ConfigHelper.OracleJava].FirstOrDefault(f => f.Name == t);
                if (java != null)
                {
                    model.JavaType = java.To;
                }
                else
                {
                    model.JavaType = ConfigHelper.UnKnowDbType;
                }

                model.DbType = item.DbType + "," + item.DataLength;
                if (ConfigHelper.ColumnComment)
                {
                    model.Comment = item.Description;            //说明
                }
                model.AllowNull    = item.IsNullable.ToString(); //是否允许空
                model.DefaultValue = item.DefaultValue;          //默认值

                columnList.Add(model);
            }

            return(columnList);
        }