コード例 #1
0
        public static DataRow DecodeRow(DataRow sourceRow, DataTable destTable)
        {
            var resultRow = destTable.NewRow();
            var fkDict    = TableKeys.GetTableKeys(destTable.TableName, TableKeys.KeyType.ForeignKey);

            foreach (DataColumn column in sourceRow.Table.Columns)
            {
                if (destTable.Columns.Contains(column.ColumnName))
                {
                    var value = sourceRow[column];
                    if (value is string && string.IsNullOrWhiteSpace((string)value))
                    {
                        value = DBNull.Value;
                    }
                    if (value != null && value != DBNull.Value)
                    {
                        if (fkDict.Contains(column.ColumnName))
                        {
                            value = GetKeyByValue(destTable.TableName, column.ColumnName, value);
                        }
                        else
                        {
                            value = Convert.ChangeType(value, destTable.Columns[column.ColumnName].DataType);
                        }
                    }
                    resultRow[column.ColumnName] = value;
                }
            }
            return(resultRow);
        }
コード例 #2
0
        public static DataRow CreateRow(string tableName)
        {
            var row = GetTable(tableName).NewRow();

            row[TableKeys.GetPrimaryKey(tableName)] = GetNewId(tableName);
            return(row);
        }
コード例 #3
0
        public static string GetValueByKey(string tableName, string columnName, object key, bool extension = false)
        {
            string value    = string.Empty;
            var    relation = TableKeys.GetColumnRelation(tableName, columnName, key);

            if (string.IsNullOrEmpty(relation.columnName) == false)
            {
                var cacheDict = GetValuesCache();
                var cacheKey  = CreateCacheKey(tableName, columnName, key, true, extension);
                if (cacheDict.ContainsKey(cacheKey))
                {
                    value = cacheDict[cacheKey];
                }
                else
                {
                    var    table        = TableManager.GetTable(relation.tableName);
                    string valueColName = GetValueColumnName(table, relation.columnName);
                    try {
                        if (extension)
                        {
                            value = SelectValueByKey(table, relation.columnName, key, valueColName);
                        }
                        else
                        {
                            value = TableManager.SelectScalarFromTable(table, relation.columnName, key, valueColName).ToString();
                        }
                        cacheDict.Add(cacheKey, value.ToString());
                    } catch {
                        //TODO вывод в лог
                    }
                }
            }
            return(value);
        }
コード例 #4
0
        private static void SetNewId(string tableName, DataRow currentRow)
        {
            string aiColumn = null;
            var    aiDict   = TableKeys.GetTableKeys(tableName, TableKeys.KeyType.AutoIncrement);

            if (aiDict.Count != 0)
            {
                aiColumn = aiDict[0];
            }
            if (aiColumn != null)
            {
                var newId = TableManager.GetMaxValue(tableName, aiColumn);
                currentRow[aiColumn] = newId;
            }
        }
コード例 #5
0
        private static void SetTablePrimaryKey(DataTable table)
        {
            var pkList    = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.PrimaryKey);
            var pkColumns = new DataColumn[pkList.Count];

            for (int i = 0; i < pkList.Count; i++)
            {
                var colName = pkList[i];
                if (table.Columns.Contains(colName))
                {
                    pkColumns[i] = table.Columns[colName];
                }
            }
            table.PrimaryKey = pkColumns;
        }
コード例 #6
0
        public static string CreateWhereString(DataRow row)
        {
            var query       = new StringBuilder();
            var sourceTable = row.Table;
            var pkList      = TableKeys.GetTableKeys(sourceTable.TableName, TableKeys.KeyType.PrimaryKey);

            for (int i = 0; i < pkList.Count; i++)
            {
                var column = pkList[i];
                query.AppendFormat("[{0}] = '{1}'", column, row[column]);
                if (i != pkList.Count - 1)
                {
                    query.Append(" and ");
                }
            }
            return(query.ToString());
        }
コード例 #7
0
        public static string CreateWhereString(DataTable table, object[] values)
        {
            var query  = new StringBuilder();
            var pkList = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.PrimaryKey);

            if (values.Length == pkList.Count)
            {
                for (int i = 0; i < pkList.Count; i++)
                {
                    var column = pkList[i];
                    query.AppendFormat("[{0}] = '{1}'", column, values[i]);
                    if (i != pkList.Count - 1)
                    {
                        query.Append(" and ");
                    }
                }
            }
            return(query.ToString());
        }
コード例 #8
0
        private static FbCommand CreateUpdateCommand(DataRow oldRow, DataRow newRow)
        {
            var table      = oldRow.Table;
            var pkList     = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.PrimaryKey);
            var query      = new StringBuilder();
            var command    = new FbCommand();
            int paramIndex = 1;

            query.AppendFormat("update {0} set", table.TableName);
            foreach (DataColumn column in table.Columns)
            {
                if (!oldRow[column].Equals(newRow[column.ColumnName]))  //если значение поменялось
                {
                    var parameter = CreateParameter(column, newRow[column.ColumnName], paramIndex);
                    query.AppendFormat(" {0} = {1},", column.ColumnName, parameter.ParameterName);
                    command.Parameters.Add(parameter);
                    paramIndex++;
                }
            }
            if (command.Parameters.Count != 0)
            {
                query.Remove(query.Length - 1, 1);
                query.Append(" where ");
                for (int i = 0; i < pkList.Count; i++)
                {
                    var column    = table.Columns[pkList[i]];
                    var parameter = CreateParameter(column, oldRow[column], paramIndex);
                    paramIndex++;
                    command.Parameters.Add(parameter);
                    query.AppendFormat("{0} = {1}", column.ColumnName, parameter.ParameterName);
                    if (i != pkList.Count - 1)
                    {
                        query.Append(" and ");
                    }
                }
                command.CommandText = query.ToString();
            }
            return(command);
        }
コード例 #9
0
        /// <param name="tableName">Текущая таблица</param>
        /// <param name="colName">Название текущей колонки</param>
        public static object GetKeyByValue(string tableName, string colName, object value)
        {
            object result   = value;
            var    relation = TableKeys.GetColumnRelation(tableName, colName);

            if (string.IsNullOrWhiteSpace(relation.columnName) == false)
            {
                int key;
                if (value is int)//если пришел уже ключ
                {
                    result = value;
                }
                else if (int.TryParse(value.ToString(), out key))//пробуем парсить ключ
                {
                    result = key;
                }
                else
                {
                    var cacheDict = GetKeysCache();
                    var cacheKey  = CreateCacheKey(tableName, colName, value, false, false);
                    if (cacheDict.ContainsKey(cacheKey))//если закешировано
                    {
                        result = cacheDict[cacheKey];
                    }
                    else
                    {
                        var    relationTable   = TableManager.GetTable(relation.tableName);
                        var    valueColumnName = GetValueColumnName(relationTable, relation.columnName);
                        object selectObj       = SelectKeyByValue(relationTable, relation.columnName, value, valueColumnName);
                        if (selectObj != DBNull.Value)
                        {
                            result = selectObj;
                            cacheDict.Add(cacheKey, result);
                        }
                    }
                }
            }
            return(result);
        }
コード例 #10
0
        private static FbCommand CreateInsertCommand(DataRow newRow)
        {
            var table      = newRow.Table;
            var nnList     = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.NotNull);
            var query      = new StringBuilder();
            var command    = new FbCommand();
            int paramIndex = 1;

            query.AppendFormat("insert into {0} (", table.TableName);
            foreach (DataColumn column in table.Columns)
            {
                var value = newRow[column];
                if (value == DBNull.Value && nnList.Contains(column.ColumnName))  //если недопустимое нулевое поле
                //TODO error
                {
                }
                else
                {
                    var parameter = CreateParameter(column, value, paramIndex);
                    query.AppendFormat(" {0},", column.ColumnName);
                    command.Parameters.Add(parameter);
                    paramIndex++;
                }
            }
            if (command.Parameters.Count != 0)
            {
                query.Remove(query.Length - 1, 1);
                query.Append(" ) values ( ");
                foreach (FbParameter parameter in command.Parameters)
                {
                    query.AppendFormat("{0},", parameter.ParameterName);
                }
                query.Remove(query.Length - 1, 1);
                query.Append(")");
                command.CommandText = query.ToString();
            }
            return(command);
        }
コード例 #11
0
        public static SortedDictionary <string, int> GetRelativeItemsSource(string tableName, string columnName, bool addNullItem)
        {
            var relation = TableKeys.GetColumnRelation(tableName, columnName);
            var result   = new SortedDictionary <string, int>();

            if (string.IsNullOrEmpty(relation.tableName) == false)
            {
                var       table        = TableManager.GetTable(relation.tableName);
                string    valueColName = GetValueColumnName(table, relation.columnName);
                DataRow[] rows         = table.Select();
                foreach (DataRow row in rows)
                {
                    var key   = (int)row[relation.columnName];
                    var value = GetRowStringView(row, relation.columnName, valueColName);
                    result.Add(value, key);
                }
            }
            if (addNullItem)
            {
                result.Add("", -1);
            }
            return(result);
        }
コード例 #12
0
        public static SortedDictionary <string, int> GetTableKeyItems(string tableName, string columnName, bool addNullItem = true)
        {
            DataTable tab    = TableManager.GetTable(tableName);
            bool      isFk   = TableKeys.CheckColumnKeyType(tableName, columnName, TableKeys.KeyType.ForeignKey);
            var       result = new SortedDictionary <string, int>();

            if (isFk)
            {
                var keysList = GetUniqueSet(tab, columnName);
                foreach (var key in keysList)
                {
                    var value = GetValueByKey(tableName, columnName, key, true);
                    if (result.ContainsKey(value) == false)
                    {
                        result.Add(value, (int)key);
                    }
                }
                if (result.ContainsKey("") == false && addNullItem)
                {
                    result.Add("", -1);
                }
            }
            return(result);
        }
コード例 #13
0
        public static SortedSet <string> GetTableItems(string tableName, string columnName)
        {
            DataTable tab        = TableManager.GetTable(tableName);
            bool      isFk       = TableKeys.CheckColumnKeyType(tableName, columnName, TableKeys.KeyType.ForeignKey);
            var       keysList   = GetUniqueSet(tab, columnName);
            var       valuesList = new SortedSet <string>();

            foreach (var value in keysList)
            {
                if (isFk)
                {
                    valuesList.Add(GetValueByKey(tableName, columnName, value, true));
                }
                else
                {
                    valuesList.Add(value.ToString());
                }
            }
            if (valuesList.Contains("") == false)
            {
                valuesList.Add("");
            }
            return(valuesList);
        }