internal override string[] GetForeignKeyForeignColumnNames(string tableName, string constraintName) { var schemaName = GetSchema(tableName); return(Dapper.Query <string>( @" SELECT KCU2.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND KCU1.TABLE_NAME = RC.TABLE_NAME AND KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = UNIQUE_CONSTRAINT_NAME AND KCU2.TABLE_NAME = RC.REFERENCED_TABLE_NAME AND KCU2.CONSTRAINT_CATALOG = UNIQUE_CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA = UNIQUE_CONSTRAINT_SCHEMA WHERE RC.CONSTRAINT_NAME = @constraintName AND RC.CONSTRAINT_SCHEMA = @schemaName AND KCU1.TABLE_NAME = @tableName ORDER BY KCU2.ORDINAL_POSITION ", new { constraintName, tableName, schemaName }).ToArray()); }
internal override string GetPrimaryKeyName(string tableName) { var schemaName = GetSchema(tableName); var escapedSchemaName = schemaName; var escapedTableName = EscapeIdentifier(tableName); return(Dapper .Query <TableInfo>($"PRAGMA {escapedSchemaName}.table_info({escapedTableName})") .Any(tableInfo => tableInfo.Pk) ? "" : null); }
internal override string[] GetIndexColumnNames(string tableName, string indexName) { var escapedIndexName = EscapeIdentifier(indexName); var schemaName = GetSchema(tableName); var escapedSchemaName = schemaName; return(Dapper .Query <IndexList>($"PRAGMA {escapedSchemaName}.index_info({escapedIndexName})") .Select(tableInfo => tableInfo.Name) .ToArray()); }
internal override bool GetIndexType(string tableName, string indexName) { var schemaName = GetSchema(tableName); var escapedSchemaName = schemaName; var escapedTableName = EscapeIdentifier(tableName); return(Dapper .Query <IndexList>($"PRAGMA {escapedSchemaName}.index_list({escapedTableName})") .Single(indexList => indexList.Name == indexName) .Unique); }
internal override string[] GetColumnNames(string tableName) { var escapedTableName = EscapeIdentifier(tableName); var schemaName = GetSchema(tableName); var escapedSchemaName = schemaName; return(Dapper .Query <TableInfo>($"PRAGMA {escapedSchemaName}.table_info({escapedTableName})") .Select(tableInfo => tableInfo.Name) .ToArray()); }
internal override string GetColumnDataType(string tableName, string columnName) { var escapedTableName = EscapeIdentifier(tableName); var schemaName = GetSchema(tableName); var escapedSchemaName = schemaName; return(Dapper .Query <TableInfo>($"PRAGMA {escapedSchemaName}.table_info({escapedTableName})") .SingleOrDefault(tableInfo => tableInfo.Name == columnName) ?.Type); }
internal override bool GetColumnNullable(string tableName, string columnName) { var escapedTableName = EscapeIdentifier(tableName); var schemaName = GetSchema(tableName); var escapedSchemaName = schemaName; return(Dapper .Query <TableInfo>($"PRAGMA {escapedSchemaName}.table_info({escapedTableName})") .Single(tableInfo => tableInfo.Name == columnName) .NotNull == false); }
internal override string[] GetIndexNames(string tableName) { return(Dapper.Query <string>( @" SELECT name FROM sys.indexes WHERE object_id = OBJECT_ID(@tableName) AND [type] = 2 ", new { tableName }).ToArray()); }
internal override string[] GetIndexColumnNames(string tableName, string indexName) { var schemaName = GetSchema(tableName); return(Dapper.Query <string>( @" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_NAME = @indexName AND TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName ", new { indexName, tableName, schemaName }).ToArray()); }
internal override string[] GetIndexNames(string tableName) { var schemaName = GetSchema(tableName); return(Dapper.Query <string>( @" SELECT DISTINCT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName AND INDEX_NAME <> 'PRIMARY' ", new { tableName, schemaName }) .Except(GetForeignKeyNames(tableName)) .ToArray()); }
public IQueryable <File> GetLatestFilesReport(string contentType) { var sB = new StringBuilder(); sB.AppendLine(" SELECT "); sB.AppendLine(" Id,"); sB.AppendLine(" ReferenceId, "); sB.AppendLine(" [Name], "); sB.AppendLine(" ContentType, "); sB.AppendLine(" CreatedAt"); sB.AppendLine(" FROM Files "); sB.AppendLine(" WHERE DeletedAt IS NULL"); sB.AppendLine(" AND ContentType like '%'+@contentType+'%' "); sB.AppendLine(" AND CreatedAt >= (getutcdate()-30)"); sB.AppendLine(" ORDER BY CreatedAt DESC"); return(Dapper.Query <File>(sB.ToString(), new { @contentType = contentType }).AsQueryable()); }
internal override string[] GetIndexColumnNames(string tableName, string indexName) { return(Dapper.Query <string>( @" SELECT sys.columns.Name FROM sys.indexes INNER JOIN sys.index_columns ON sys.index_columns .object_id = sys.indexes.object_id AND sys.index_columns .index_id = sys.indexes.index_id INNER JOIN sys.columns ON sys.columns.object_id = sys.index_columns .object_id AND sys.columns.column_id = sys.index_columns .column_id WHERE sys.columns.object_id = OBJECT_ID(@tableName) AND sys.indexes.name = @indexName AND sys.indexes.[type] = 2 ", new { indexName, tableName }).ToArray()); }
internal override string GetColumnDataType(string tableName, string columnName) { var schemaName = GetSchema(tableName); var columnInformation = Dapper.Query <InformationSchema.Column>( @" SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_TYPE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @columnName AND TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName ", new { tableName, schemaName, columnName }).SingleOrDefault(); if (columnInformation == null) { return(null); } var unsigned = columnInformation.column_type.EndsWith("unsigned") ? " unsigned" : ""; var includePrecisionOnTypes = new[] { "varchar", "char", "integer", "time", "decimal" }; if (includePrecisionOnTypes.Contains(columnInformation.data_type) == false) { return(columnInformation.data_type + unsigned); } var parameters = new[] { columnInformation.datetime_precision, columnInformation.character_maximum_length, columnInformation.numeric_precision, columnInformation.numeric_scale }; var usedParameters = parameters.Where(parameter => parameter.HasValue).Select(parameter => parameter.Value.ToString()).ToArray(); return(columnInformation.data_type + (usedParameters.Any() ? "(" + string.Join(",", usedParameters) + ")" : "") + unsigned); }
internal override string[] GetIndexColumnNames(string tableName, string indexName) { return(Dapper.Query <string>( @" SELECT a.attname FROM pg_class ic, pg_attribute a, pg_class tc, pg_index ix WHERE ic.relname = @indexName AND ic.oid = ix.indexrelid AND tc.oid = ix.indrelid AND a.attrelid = tc.oid AND a.attnum = ANY(ix.indkey) AND tc.relkind = 'r' ", new { indexName }) .ToArray()); }
internal override string[] GetIndexNames(string tableName) { var schemaName = GetSchema(tableName); return(Dapper.Query <string>( @" SELECT indexname FROM pg_indexes WHERE schemaname = 'public' AND tablename = @tableName EXCEPT SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND table_name = @tableName AND table_schema = @schemaName ", new { tableName, schemaName }) .ToArray()); }
public List <KJ370_FluxPointModel> GetKJ370_FluxPointModels() { var sql = "select * from KJ370_FluxPoint"; return(Dapper.Query <KJ370_FluxPointModel>(sql).ToList()); }
internal override string[] GetTableNames() { return(Dapper .Query <string>("SELECT name FROM sqlite_master WHERE type = 'table'") .ToArray()); }
internal override bool GetTableExists(string tableName) { return(Dapper .Query <string>("SELECT name FROM sqlite_master WHERE type = 'table' AND name = @tableName", new { tableName }) .Any()); }
public List <EquipmentInfoModel> GetEquipmentInfoModels() { var sql = "select * from EquipmentInfo"; return(Dapper.Query <EquipmentInfoModel>(sql).ToList()); }
public List <AnalogPointModel> GetAnalogPointModels() { var sql = "select * from AnalogPoint "; return(Dapper.Query <AnalogPointModel>(sql).ToList()); }
public List <SwitchPointModel> GetSwitchPointModels() { var sql = "select * from SwitchPoint"; return(Dapper.Query <SwitchPointModel>(sql).ToList()); }
public IActionResult Get() { var r = Dapper.Query("select * from COMPANY where id=1 LIMIT 1 OFFSET 0;"); return(Ok(new { r, DI.Value })); }
public List <ControlPointModel> GetControlPointModels() { var sql = "select * from ControlPoint"; return(Dapper.Query <ControlPointModel>(sql).ToList()); }