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());
}
