public static List<DBTableInfo> GetTableInfo(string tableName)
        {
            List<DBTableInfo> list = new List<DBTableInfo>();

            try
            {
                string sql = string.Format("PRAGMA table_info({0});",tableName);

                SQLiteCommand command = new SQLiteCommand(sql, LocalSqllite.m_sqlLiteConnection);
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DBTableInfo data = new DBTableInfo();
                    data.m_cid = reader["cid"].ToString();
                    data.m_columnName = reader["name"].ToString();
                    data.m_columnType = reader["type"].ToString();
                    data.m_defaultValue = reader["notnull"].ToString();
                    data.m_notNull = reader["dflt_value"].ToString();
                    data.m_primaryKey = reader["pk"].ToString();

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return list;
        }
Beispiel #2
0
        //private static string _sqlTables = "SELECT [name],[xtype] FROM [sysobjects] Where [xtype] in ('U','V') and [name] not in('dtproperties','sysdiagrams') ORDER BY [xtype],[crdate] desc";

        /// <summary>
        /// 获取所有用户表
        /// </summary>
        /// <returns></returns>
        public virtual List <DBTableInfo> GetAllTableName(DataBaseOperate oper, DBInfo info)
        {
            ParamList lstParam = new ParamList();
            //DataBaseOperate oper = info.DefaultOperate;
            List <DBTableInfo> lstName = new List <DBTableInfo>();

            using (BatchAction ba = oper.StarBatchAction())
            {
                OleDbConnection conn = oper.Connection as OleDbConnection;
                oper.ConnectDataBase();


                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow row in dt.Rows)
                {
                    DBTableInfo tableInfo = new DBTableInfo();
                    if (row.IsNull("TABLE_NAME"))
                    {
                        continue;
                    }
                    tableInfo.Name   = row["TABLE_NAME"].ToString();
                    tableInfo.IsView = false;
                    lstName.Add(tableInfo);
                }
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "VIEW" });

                foreach (DataRow row in dt.Rows)
                {
                    DBTableInfo tableInfo = new DBTableInfo();
                    if (row.IsNull("TABLE_NAME"))
                    {
                        continue;
                    }
                    tableInfo.Name   = row["TABLE_NAME"].ToString();
                    tableInfo.IsView = true;
                    lstName.Add(tableInfo);
                }
            }
            return(lstName);
        }
Beispiel #3
0
        private static string ToSelect(DBTableInfo tb)
        {
            StringBuilder cBuilder = new StringBuilder();

            cBuilder.AppendLine("--【Select】------------------------------------------");
            cBuilder.AppendLine("SELECT");
            var idx = 0;

            foreach (DBColInfo c in tb.Cols)
            {
                idx++;
                if (string.IsNullOrWhiteSpace(c.ColumnName))
                {
                    continue;
                }
                var scriptEnd = idx == tb.Cols.Count ? "" : ",";
                cBuilder.AppendLine(string.Format(" a.[{0}]{1}", c.ColumnName, scriptEnd));
            }
            cBuilder.AppendLine(string.Format("FROM dbo.[{0}] as a", tb.TableName));
            cBuilder.AppendLine("");
            return(cBuilder.ToString());
        }
Beispiel #4
0
 /// <summary>
 /// 填充是否存在的列
 /// </summary>
 private void RefreashExistsInfo()
 {
     foreach (DataGridViewRow dr in gvTables.Rows)
     {
         string      exists = "未生成";
         DBTableInfo info   = dr.DataBoundItem as DBTableInfo;
         if (info != null)
         {
             string fileName = DBEntityInfo.GetEntityRealFileName(info, _dbInfo, DesignerInfo);
             try
             {
                 if (File.Exists(fileName))
                 {
                     exists = "已生成";
                     dr.DefaultCellStyle.ForeColor = Color.Red;
                 }
             }
             catch { }
         }
         dr.Cells["ColExists"].Value = exists;
     }
 }
Beispiel #5
0
        protected override string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols)
        {
            List <string> values = new List <string>();
            List <string> fields = new List <string>();

            foreach (DBTableColumnInfo colInf in selectedCols.Where(x => !x.Exclude))
            {
                values.Add(PrefixInputParameter + colInf.ColumnName);
                fields.Add(Wrap(colInf.ColumnName));
            }
            var sb = new StringBuilder();

            sb.Append($"\tINSERT INTO {tableInfo.FullTableName}");
            if (values.Any())
            {
                sb.AppendLine($" ({string.Join(", ", fields)})");
            }
            if (tableInfo.Columns.Any(x => x.IsAutogeneratedGuid))
            {
                IEnumerable <string> autoGuids = tableInfo.Columns.Where(x => x.IsAutogeneratedGuid)
                                                 .Select(x => $"inserted.[{x.ColumnName}]");
                sb.AppendLine($"\tOUTPUT {string.Join(", ", autoGuids)}");
            }
            if (values.Any())
            {
                sb.Append($"\tVALUES ({string.Join(", ", values)})");
            }
            else
            {
                sb.AppendLine();
                sb.Append("\tDEFAULT VALUES");
            }
            if (tableInfo.Columns.Any(x => x.IsIdentity))
            {
                sb.AppendLine();
                sb.Append("\tSELECT @@IDENTITY");
            }
            return(sb.ToString());
        }
        public StoredProcedure GenerateSingleSP(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols,
                                                List <DBTableColumnInfo> whereConditionCols)
        {
            var name             = GetSpName(tableInfo.TableName, whereConditionCols);
            var statementBuilder = new StringBuilder();

            statementBuilder.AppendLine($"CREATE PROCEDURE [{tableInfo.Schema}].[{name}]");
            //GenerateErrorNumberOutParameter(statementBuilder);
            var inputs = GenerateInputParameters(selectedCols);
            var wheres = GenerateWhereParameters(whereConditionCols);

            if (!string.IsNullOrWhiteSpace(inputs))
            {
                statementBuilder.Append(inputs);
                if (!string.IsNullOrWhiteSpace(wheres))
                {
                    statementBuilder.Append(',');
                }
                statementBuilder.AppendLine();
            }

            if (!string.IsNullOrWhiteSpace(wheres))
            {
                statementBuilder.AppendLine(wheres);
            }
            statementBuilder.AppendLine("AS");
            //GenerateStartTryBlock(statementBuilder);
            statementBuilder.AppendLine(GenerateStatement(tableInfo, selectedCols, whereConditionCols));
            //GenerateEndTryBlock(statementBuilder);
            //GenerateCatchBlock(statementBuilder);
            var res = new StoredProcedure
            {
                Name       = name,
                DropScript = GenerateDropScript(name),
                Script     = statementBuilder.ToString()
            };

            return(res);
        }
        protected override string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols)
        {
            string selectFieldsStr;

            if (selectedCols.Count == tableInfo.Columns.Count)
            {
                selectFieldsStr = "*";
            }
            else
            {
                List <string> fields = new List <string>();
                foreach (DBTableColumnInfo colInf in selectedCols.Where(x => !x.Exclude))
                {
                    fields.Add(Wrap(colInf.ColumnName));
                }
                selectFieldsStr = string.Join(", ", fields);
            }

            var sb = new StringBuilder();

            sb.AppendLine($"\tSELECT {selectFieldsStr} FROM {tableInfo.FullTableName}{Environment.NewLine}{GenerateWhereStatement(whereConditionCols)}");
            return(sb.ToString());
        }
Beispiel #8
0
        private static string ToTable(string server, string db, DBTableInfo tb)
        {
            StringBuilder cBuilder = new StringBuilder();

            cBuilder.AppendLine("--【表结构】------------------------------------------");
            cBuilder.AppendLine("CREATE TABLE [dbo].[" + tb.TableName + "](");

            var idx           = 0;
            var tableDao      = new GeneralDAO.TableDAO();
            var Indexs        = tableDao.GetIndexs(server, db, tb.TableName);
            var primaryColumn = Indexs.FirstOrDefault(t => t.IsPrimaryKey);

            foreach (DBColInfo c in tb.Cols)
            {
                idx++;
                if (string.IsNullOrWhiteSpace(c.ColumnName))
                {
                    continue;
                }
                var scriptPrimaryKey = "";
                if (primaryColumn != null)
                {
                    scriptPrimaryKey = c.ColumnName == primaryColumn.ColumnNames ? "primary key" : "";
                }
                var scriptEnd      = idx == tb.Cols.Count ? "" : ",";
                var scriptNullable = c.IsNullable ? "null" : "not null";


                cBuilder.AppendLine(string.Format("   [{0}] [{1}]{2} {3} {4} {5} --{6}"
                                                  , c.ColumnName, c.ColumnType, c.ColumnLengthStr, scriptNullable, scriptPrimaryKey, scriptEnd, c.DisplayName));
            }
            cBuilder.AppendLine(")");
            cBuilder.AppendLine("GO");
            cBuilder.AppendLine("");
            return(cBuilder.ToString());
        }
Beispiel #9
0
        /// <summary>
        /// 获取所有用户表
        /// </summary>
        /// <param name="oper"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
        {
            ParamList lstParam = new ParamList();

            lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
            List <DBTableInfo> lstName = new List <DBTableInfo>();
            string             inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
            string             sql     = _sqlTables;

            if (!string.IsNullOrEmpty(inTable))
            {
                sql += " and TABLE_NAME in(" + inTable + ")";
            }

            using (IDataReader reader = oper.Query(sql, lstParam, null))
            {
                while (reader.Read())
                {
                    DBTableInfo tableInfo = new DBTableInfo();
                    tableInfo.Name = reader[0] as string;
                    string type = reader[1] as string;
                    if (!string.IsNullOrEmpty(type))
                    {
                        if (type.Trim() == "VIEW")
                        {
                            tableInfo.IsView = true;
                        }
                    }
                    string comment = reader[2] as string;
                    tableInfo.Description = comment;

                    lstName.Add(tableInfo);
                }
            }
            return(lstName);
        }
Beispiel #10
0
        private static string ToInsert(DBTableInfo tb)
        {
            StringBuilder cBuilder = new StringBuilder();

            cBuilder.AppendLine("--【Insert】------------------------------------------");
            cBuilder.AppendLine("INSERT INTO [dbo].[" + tb.TableName + "](");
            var idx = 0;

            foreach (DBColInfo c in tb.Cols)
            {
                idx++;
                if (string.IsNullOrWhiteSpace(c.ColumnName))
                {
                    continue;
                }
                var scriptEnd = idx == tb.Cols.Count ? "" : ",";
                cBuilder.AppendLine(string.Format(" [{0}]{1}", c.ColumnName, scriptEnd));
            }
            /////////////////////////////////
            cBuilder.AppendLine(")VALUES(");
            idx = 0;
            foreach (DBColInfo c in tb.Cols)
            {
                idx++;
                if (string.IsNullOrWhiteSpace(c.ColumnName))
                {
                    continue;
                }
                var scriptEnd      = idx == tb.Cols.Count ? "" : ",";
                var scriptNullable = c.IsNullable ? "null" : "not null";
                cBuilder.AppendLine(string.Format(" 'xxxxxx'{4}--<{0},{1}{2},{3}>", c.ColumnName, c.ColumnType, c.ColumnLengthStr, scriptNullable, scriptEnd));
            }
            cBuilder.AppendLine(")");
            cBuilder.AppendLine("");
            return(cBuilder.ToString());
        }
Beispiel #11
0
        public string GetRemarkSql(DBTableInfo tb)
        {
            var sb = new StringBuilder();

            if (tb.TableDisplayName.IsNullOrEmpty() == false)
            {
                sb.AppendFormat(@"
                 IF(EXISTS(	SELECT a.*  FROM sys.tables AS a 
	            INNER JOIN sys.extended_properties AS b ON a.object_id=b.major_id AND b.minor_id = 0
	            WHERE  ISNULL(b.class_desc, 'OBJECT_OR_COLUMN') = 'OBJECT_OR_COLUMN'  
	            AND ISNULL(b.name, 'MS_Description') = 'MS_Description'
                AND  a.name='{0}'))
                BEGIN 
                EXEC sys.sp_dropextendedproperty @name=N'MS_Description',  @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}';
                End
                ", tb.TableName);
                sb.Append(Environment.NewLine + "GO" + Environment.NewLine);

                sb.AppendFormat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{1}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}';", tb.TableName, tb.TableDisplayName);

                sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
            }
            foreach (var col in tb.Cols)
            {
                if (col.DisplayName.IsNullOrEmpty() == false)
                {
                    sb.AppendFormat("EXEC sys.sp_dropextendedproperty @name=N'MS_Description',  @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}';", tb.TableName, col.ColumnName);
                    sb.Append(Environment.NewLine + "GO" + Environment.NewLine);

                    sb.AppendFormat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{2}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}';", tb.TableName, col.ColumnName, col.DisplayName);

                    sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
                }
            }
            return(sb.ToString());
        }
Beispiel #12
0
        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="oper"></param>
        /// <param name="info"></param>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
        {
            ParamList lstParam = new ParamList();
            string    inTables = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);

            StringBuilder sbSQL = new StringBuilder(_sqlTables);

            if (string.IsNullOrEmpty(inTables))
            {
                sbSQL.Append(" and [sql]<>''");
            }
            else
            {
                sbSQL.Append(" and [Name] IN(" + inTables + ")");
            }


            List <DBTableInfo> lstName = new List <DBTableInfo>();

            ///只能获取表名,其它的没用
            using (IDataReader reader = oper.Query(sbSQL.ToString(), lstParam, null))
            {
                while (reader.Read())
                {
                    DBTableInfo tableInfo = new DBTableInfo();
                    tableInfo.Name   = reader["name"] as string;
                    tableInfo.IsView = (reader["type"] as string) == "view";
                    if (IsSysTable(tableInfo.Name))
                    {
                        continue;
                    }
                    lstName.Add(tableInfo);
                }
            }
            return(lstName);
        }
Beispiel #13
0
        /// 填充字段信息
        /// </summary>
        /// <param name="prm">字段信息</param>
        /// <param name="reader">reader</param>
        private void FillParam(DBTableInfo table, IDataReader reader)
        {
            string prmName = reader["COLUMN_NAME"] as string;

            if (string.IsNullOrEmpty(prmName))
            {
                return;
            }

            foreach (EntityParam ep in table.Params)
            {
                if (ep.ParamName == prmName)
                {
                    return;
                }
            }

            EntityParam prm = new EntityParam();

            prm.ParamName = prmName;

            EntityPropertyType type = EntityPropertyType.Normal;
            bool   isIdentity       = false;
            bool   isPrimaryKey     = false;
            string extra            = reader["EXTRA"] as string;

            if (!string.IsNullOrEmpty(extra) && extra.Trim().Equals("auto_increment", StringComparison.CurrentCultureIgnoreCase))
            {
                isIdentity = true;
            }
            string columnKey = reader["COLUMN_KEY"] as string;

            if (!string.IsNullOrEmpty(columnKey) && columnKey.Trim().Equals("PRI", StringComparison.CurrentCultureIgnoreCase))
            {
                isPrimaryKey = true;
            }
            if (isPrimaryKey)
            {
                type = EntityPropertyType.PrimaryKey;
            }
            if (isIdentity)
            {
                type = type | EntityPropertyType.Identity;
            }
            prm.PropertyType = type;

            if (!(reader["CHARACTER_OCTET_LENGTH"] is DBNull))
            {
                prm.Length = Convert.ToInt64(reader["CHARACTER_OCTET_LENGTH"]);
            }

            if (!table.IsView)
            {
                prm.Description = reader["COLUMN_COMMENT"] as string;
            }

            prm.AllowNull = Convert.ToInt32(reader["IS_NULLABLE"]) == 1;

            string strDBType  = reader["COLUMN_TYPE"] as string;
            bool   isUnsigned = strDBType.IndexOf("unsigned") > -1;

            string strDataType = reader["DATA_TYPE"] as string;

            prm.SqlType = GetDbType(strDataType, isUnsigned);
            table.Params.Add(prm);
        }
Beispiel #14
0
        /// 填充字段信息
        /// </summary>
        /// <param name="prm">字段信息</param>
        /// <param name="reader">reader</param>
        private void FillParam(DBTableInfo table, IDataReader reader)
        {
            string prmName = reader["column_name"] as string;

            if (string.IsNullOrEmpty(prmName))
            {
                return;
            }

            foreach (EntityParam ep in table.Params)
            {
                if (ep.ParamName == prmName)
                {
                    return;
                }
            }

            EntityParam prm = new EntityParam();

            prm.ParamName = prmName;

            EntityPropertyType type = EntityPropertyType.Normal;
            bool isIdentity         = false;
            bool isPrimaryKey       = false;

            string columnKey = reader["constraint_type"] as string;

            if (!string.IsNullOrEmpty(columnKey))
            {
                isPrimaryKey = true;
                isIdentity   = true;
            }
            if (isPrimaryKey)
            {
                type = EntityPropertyType.PrimaryKey;
            }
            if (isIdentity)
            {
                type = type | EntityPropertyType.Identity;
            }
            prm.PropertyType = type;

            object val = reader["character_maximum_length"];

            if (!(val is DBNull))
            {
                prm.Length = Convert.ToInt64(val);
            }

            val = reader["is_nullable"];
            if (!(val is DBNull))
            {
                prm.AllowNull = val.ToString().Equals("YES", StringComparison.CurrentCultureIgnoreCase);
            }



            string strDataType = reader["udt_name"] as string;

            prm.SqlType = GetDbType(strDataType);
            table.Params.Add(prm);
        }
 protected abstract string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols);
Beispiel #16
0
        /// <summary>
        /// 填充字段信息
        /// </summary>
        /// <param name="prm">字段信息</param>
        /// <param name="reader">reader</param>
        private void FillParam(DBTableInfo table, IDataReader reader)
        {
            string prmName = reader["COLNAME"] as string;

            if (string.IsNullOrEmpty(prmName))
            {
                return;
            }

            foreach (EntityParam ep in table.Params)
            {
                if (ep.ParamName == prmName)
                {
                    return;
                }
            }

            EntityParam prm = new EntityParam();

            prm.ParamName = prmName;

            EntityPropertyType type = EntityPropertyType.Normal;
            int    isPrimary        = 0;
            object val = reader["KEYSEQ"];

            if (!(val is DBNull))
            {
                isPrimary = Convert.ToInt32(val);
            }

            val = reader["IDENTITY"];
            bool isIdentity = false;

            if (!(val is DBNull))
            {
                isIdentity = val.ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase);
            }
            if (isPrimary == 1)
            {
                type = EntityPropertyType.PrimaryKey;
            }
            if (isIdentity)
            {
                type = type | EntityPropertyType.Identity;
            }

            val = reader["NULLS"];
            bool isNull = false;

            if (!(val is DBNull))
            {
                isNull = val.ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase);
            }
            prm.AllowNull    = isNull;
            prm.PropertyType = type;
            prm.Length       = Convert.ToInt64(reader["LENGTH"]);
            if (!table.IsView)
            {
                prm.Description = reader["REMARKS"] as string;
            }
            string strDBType = reader["TYPENAME"] as string;

            prm.SqlType = GetDbType(strDBType);
            table.Params.Add(prm);
        }
Beispiel #17
0
        private static bool SaveObject <T>(string ProcedureName, T EntityInstance, bool IsInserting) where T : class
        {
            bool bSuccess = false;

            try
            {
                Type oCustomType = typeof(T);

                DBTableInfo oDBInfo = SchemaManager.GetTable(oCustomType.Name);

                object         objPropertyValue = null;
                PropertyInfo[] arrProperties    = oCustomType.GetProperties();

                ArrayList arrParams = new ArrayList();

                foreach (PropertyInfo oInfo in arrProperties)
                {
                    DBColumnInfo oColumnInfo = oDBInfo.Columns[oInfo.Name] as DBColumnInfo;
                    if (oColumnInfo != null && !(IsInserting && oColumnInfo.IsPrimaryKey))
                    {
                        objPropertyValue = oInfo.GetValue(EntityInstance, null);

                        if (objPropertyValue == null || DateTime.MinValue.Equals(objPropertyValue))
                        {
                            arrParams.Add(new SqlParameter("@" + oColumnInfo.Name, DBNull.Value));
                        }
                        else
                        {
                            arrParams.Add(new SqlParameter("@" + oColumnInfo.Name, objPropertyValue));
                        }
                    }
                }
                SqlParameter [] Parameters = new SqlParameter[arrParams.Count];
                arrParams.CopyTo(Parameters, 0);
                object Result = SqlHelper.ExecuteScalar(ConfigManager.Current.ConnectionString, CommandType.StoredProcedure, ProcedureName, Parameters);
                bSuccess = true;

                if (IsInserting && Result != null)
                {
                    try
                    {
                        foreach (string Key in oDBInfo.Columns.Keys)
                        {
                            DBColumnInfo oColInfo = oDBInfo.Columns[Key] as DBColumnInfo;

                            if (oColInfo.IsPrimaryKey)
                            {
                                PropertyInfo oInfo = oCustomType.GetProperty(oColInfo.Name);
                                oInfo.SetValue(EntityInstance, Convert.ChangeType(Result, oInfo.PropertyType), null);

                                return(bSuccess);
                            }
                        }
                    }
                    catch (Exception) { }
                }
            }
            catch (Exception ex)
            {
                ExceptionManager.Publish(ex);
            }
            return(bSuccess);
        }
Beispiel #18
0
        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="oper"></param>
        /// <param name="info"></param>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
        {
            Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
            List <DBTableInfo> lst = new List <DBTableInfo>();


            using (BatchAction ba = oper.StarBatchAction())
            {
                OleDbConnection conn = oper.Connection as OleDbConnection;
                oper.ConnectDataBase();
                DataTable dtpk = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);    //主键
                Dictionary <string, bool> dicPk = new Dictionary <string, bool>();
                foreach (DataRow row in dtpk.Rows)
                {
                    string key = row["TABLE_NAME"].ToString() + ":" + row["COLUMN_NAME"].ToString();
                    dicPk[key.ToLower()] = true;
                }

                foreach (string tableName in tableNames)
                {
                    DBTableInfo tableinfo = new DBTableInfo();
                    tableinfo.Params        = new List <EntityParam>();
                    tableinfo.RelationItems = new List <TableRelationAttribute>();

                    tableinfo.IsView = false;
                    DataTable dtStr = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, tableName, "TABLE" });
                    if (dtStr.Rows.Count <= 0)
                    {
                        dtStr            = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, tableName, "VIEW" });
                        tableinfo.IsView = true;
                    }
                    if (dtStr.Rows.Count <= 0)
                    {
                        continue;
                    }
                    tableinfo.Name        = dtStr.Rows[0]["TABLE_NAME"] as string;
                    tableinfo.Description = dtStr.Rows[0]["DESCRIPTION"] as string;
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });



                    //获取表结构
                    DataTable dtData = null;
                    string    sql    = "select * from [" + tableName + "]";
                    using (OleDbCommand comm = new OleDbCommand(sql, conn))
                    {
                        using (OleDbDataReader dr = comm.ExecuteReader(CommandBehavior.KeyInfo))
                        {
                            dtData = dr.GetSchemaTable();
                        }
                    }
                    DataView dv = dt.DefaultView;
                    dv.Sort = "ORDINAL_POSITION asc";
                    foreach (DataRowView row in dv)
                    {
                        FillParam(tableinfo, row.Row, dicPk, dtData);
                    }
                    lst.Add(tableinfo);
                }
            }
            foreach (DBTableInfo table in lst)
            {
                dicTables[table.Name] = table;
            }
            List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);

            FillRelation(dicTables, lstRelation);
            return(lst);
        }
Beispiel #19
0
            private DBTableInfo GetTableInfo(List <List <string> > data, List <List <string> > pkData, string tableName)
            {
                DBTableInfo dbTableInfo = new DBTableInfo()
                {
                    TableName  = tableName,
                    FieldInfos = new List <DBFieldInfo>()
                };


                foreach (var row in data)
                {
                    try
                    {
                        string genetalType = "null";
                        string type        = row.ElementAt(0);

                        switch (type)
                        {
                        case "tinyint(1)":
                            genetalType = "bool";
                            break;

                        case string int_1 when int_1.StartsWith("tinyint"):
                        case string int_2 when int_2.StartsWith("int"):
                        case string int_3 when int_3.StartsWith("bigint"):
                        case string int_4 when int_4.StartsWith("numeric"):
                            genetalType = "int";

                            break;

                        case string string_1 when string_1.StartsWith("varchar"):
                        case string string_2 when string_2.StartsWith("longtext"):
                        case string string_3 when string_3.StartsWith("character"):
                        case string string_4 when string_4.StartsWith("character varying"):
                            genetalType = "string";

                            break;

                        case string dateTime_1 when dateTime_1.StartsWith("datetime"):
                        case string dateTime_2 when dateTime_2.StartsWith("date"):
                            genetalType = "datetime";

                            break;

                        default:
                            break;
                        }

                        bool isPk = false;

                        if (pkData.Any(i => i.ElementAt(0) == row.ElementAt(1)))
                        {
                            isPk = true;
                        }

                        DBFieldInfo field = new DBFieldInfo()
                        {
                            //Id = reader.GetInt32(0),
                            Name        = row.ElementAt(1),
                            Type        = row.ElementAt(0),
                            GeneralType = genetalType,
                            Nullable    = row.ElementAt(2) == "YES",
                            // df value needed?
                            IsPrimaryKey = isPk
                        };


                        dbTableInfo.FieldInfos.Add(field);
                    }
                    catch (Exception ex)
                    {
                        // _logger.LogError(ex, ex.Message);
                    }
                }

                return(dbTableInfo);
            }
 protected override string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols)
 {
     return("\tDELETE FROM " + tableInfo.FullTableName + Environment.NewLine
            + GenerateWhereStatement(whereConditionCols));
 }
Beispiel #21
0
 public DBTableInfoEx(DBTableInfo tableInfo)
     : base(tableInfo)
 {
 }
Beispiel #22
0
        private TreeViewNode CreateTableNode(DBTableInfo sqlTableInfo, TreeViewNode parent)
        {
            string tableDisplayName = sqlTableInfo.Schema == "dbo"
                ? sqlTableInfo.TableName
                : sqlTableInfo.Schema + "." + sqlTableInfo.TableName;
            TreeViewNode tblNode = new TreeViewNode(tableDisplayName, parent);

            tblNode.Tag = sqlTableInfo;

            TreeViewNode insertSp = new TreeViewNode(Constants.insertTreeNodeText, tblNode);

            AddColumnNodes(insertSp, sqlTableInfo.Columns.Where(x => !x.Exclude), true);
            tblNode.Children.Add(insertSp);


            TreeViewNode deleteSp          = new TreeViewNode(Constants.deleteTreeNodeText, tblNode);
            TreeViewNode whereDelCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, deleteSp);

            deleteSp.Children.Add(whereDelCondition);
            tblNode.Children.Add(deleteSp);
            AddColumnNodes(whereDelCondition, sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToList(), true);

            var updateCols      = sqlTableInfo.Columns.Where(x => !x.Exclude).ToArray();
            var updateWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();

            if (updateCols.Any() && updateCols.All(x => !updateWhereCols.Contains(x)))
            {
                TreeViewNode updateSp = new TreeViewNode(Constants.updateTreeNodeText, tblNode);

                AddColumnNodes(updateSp, updateCols, true);
                tblNode.Children.Add(updateSp);
                TreeViewNode whereUpdateCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, updateSp);
                updateSp.Children.Add(whereUpdateCondition);
                AddColumnNodes(whereUpdateCondition, updateWhereCols, true);
            }
            AddColumnNodes(deleteSp, new List <DBTableColumnInfo>(), true);

            var          selectColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
            TreeViewNode selectSp      = new TreeViewNode(Constants.selectTreeNodeText, tblNode);

            sqlTableInfo.Columns.CopyTo(selectColumns, 0);
            Array.ForEach(selectColumns, x =>
            {
                x.Exclude = false;
            });
            AddColumnNodes(selectSp, selectColumns, true);
            tblNode.Children.Add(selectSp);

            var selectOneColumns   = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
            var selectOneWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();

            sqlTableInfo.Columns.CopyTo(selectOneColumns, 0);
            Array.ForEach(selectColumns, x =>
            {
                x.Exclude = false;
            });
            if (selectOneColumns.Any() &&
                selectOneWhereCols.All(x => selectOneColumns.Any(y => x.ColumnName != y.ColumnName)))
            {
                TreeViewNode selectOneSp = new TreeViewNode(Constants.selectOneTreeNodeText, tblNode);
                AddColumnNodes(selectOneSp, selectOneColumns, true);
                tblNode.Children.Add(selectOneSp);
                TreeViewNode whereSelectOneCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
                                                                        selectOneSp);
                selectOneSp.Children.Add(whereSelectOneCondition);
                AddColumnNodes(whereSelectOneCondition, selectOneWhereCols, true);
            }

            var selectByColumns   = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
            var selectByWhereCols = sqlTableInfo.Columns.Where(x => x.IsForeignKey && !x.IsPrimaryKey).ToArray();

            sqlTableInfo.Columns.CopyTo(selectByColumns, 0);
            Array.ForEach(selectByColumns, x =>
            {
                x.Exclude = false;
            });
            if (selectByColumns.Any() &&
                selectByWhereCols.Any())
            {
                TreeViewNode selectBySp = new TreeViewNode(Constants.selectByTreeNodeText, tblNode);
                AddColumnNodes(selectBySp, selectByColumns, true);
                tblNode.Children.Add(selectBySp);
                TreeViewNode whereSelectByCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
                                                                       selectBySp);
                selectBySp.Children.Add(whereSelectByCondition);
                AddColumnNodes(whereSelectByCondition, selectByWhereCols, true);
            }

            return(tblNode);
        }
Beispiel #23
0
            private DBTableInfo GetTableInfo(DbCommand command, string tableName)
            {
                DBTableInfo dbTableInfo = new DBTableInfo()
                {
                    TableName  = tableName,
                    FieldInfos = new List <DBFieldInfo>()
                };

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        try
                        {
                            string genetalType = "null";
                            string type        = reader.GetString(1);
                            if (type.Contains("("))
                            {
                                type = type.Substring(0, type.IndexOf('('));
                            }

                            type = type.ToLower();

                            switch (type)
                            {
                            case "tinyint":     // TODO SmallInt 1 is bool
                            case "int":
                            case "bigint":
                            case "smallint":
                                genetalType = "int";
                                break;

                            case "varchar":
                                genetalType = "string";
                                break;

                            case "datetime":
                                genetalType = "datetime";
                                break;

                            default:
                                break;
                            }

                            DBFieldInfo field = new DBFieldInfo()
                            {
                                //Id = reader.GetInt32(0),
                                Name        = reader.GetString(0),
                                Type        = reader.GetString(1),
                                GeneralType = genetalType,
                                Nullable    = reader.GetString(2) == "YES",
                                // df value needed?
                                IsPrimaryKey = reader.GetString(3) == "PRI"
                            };


                            dbTableInfo.FieldInfos.Add(field);
                        }
                        catch (Exception ex)
                        {
                            _logger.LogError(ex, ex.Message);
                        }
                    }
                }

                return(dbTableInfo);
            }