public byte[] ExecuteExcelExport(string sheetName)
{
var connectionOpt = this.GetConnectionOption();
var sqlQueryAndParms = this.GetSqlQueryAndParms(SelectClauseBuildMode.Raw);
var reader = DatabaseReader.Create(connectionOpt.Item1, connectionOpt.Item2)
.Reader.GetDataReader(sqlQueryAndParms.Item1, sqlQueryAndParms.Item2);
var excel = ExcelFactory.Create(sheetName, reader);
return(excel.SaveAsBytes());
}
public void GetReport_Test()
{
var sqlQuery =
"SELECT CAST(SNO AS NVARCHAR) AS P1, CAST(Name AS NVARCHAR) AS P2, CAST(Sex AS NVARCHAR) AS P3, CAST(Age AS NVARCHAR) AS P4 ";
sqlQuery += " , CAST(Birthdate AS NVARCHAR) AS P5, CAST(Address AS NVARCHAR) AS P6 ";
sqlQuery += " FROM Students WHERE (Name = @k29yFVkbzH)";
var student = DatabaseReader.Create("report")
.Reader.SelectFirstOrDefault <AutoMapperTest>(sqlQuery, new Dictionary <string, object> {
{ "@k29yFVkbzH", "apple" }
});
Assert.IsNull(student, student.P1);
}
public IEnumerable <DatabaseSchemaDto> GetDatabaseSchema(ConnectionOptions connectionOptions, string providerName)
{
var sqlQuery = new StringBuilder();
sqlQuery.AppendLine("WITH T AS (");
sqlQuery.AppendLine(" SELECT TABLE_CATALOG AS TableCatalog, TABLE_SCHEMA AS TableSchema, TABLE_NAME AS TableName, TABLE_TYPE AS TableType ");
sqlQuery.AppendLine(" FROM INFORMATION_SCHEMA.TABLES ");
sqlQuery.AppendLine(" UNION ALL ");
sqlQuery.AppendLine(" SELECT TABLE_CATALOG AS TableCatalog, TABLE_SCHEMA AS TableSchema, TABLE_NAME AS TableName, 'VIEW' AS TableType ");
sqlQuery.AppendLine(" FROM INFORMATION_SCHEMA.VIEWS ");
sqlQuery.AppendLine(") ");
sqlQuery.AppendLine(" SELECT TableCatalog, TableSchema, TableName, TableType ");
sqlQuery.AppendLine(" ROM T ");
return(DatabaseReader.Create(connectionOptions, providerName).Reader.Select <DatabaseSchemaDto>(sqlQuery.ToString()));
}
public IEnumerable <TableSchemaDto> GetTableSchema(ConnectionOptions connectionOptions, string providerName, string schema, string table)
{
var sqlQuery = new StringBuilder();
sqlQuery.AppendLine(" SELECT TABLE_CATALOG AS TableCatalog, TABLE_SCHEMA AS TableSchema, TABLE_NAME AS TableName ");
sqlQuery.AppendLine(" ,COLUMN_NAME AS ColunmName, DATA_TYPE AS OriginalDataType, ORDINAL_POSITION AS OrdinalPosition, CHARACTER_MAXIMUM_LENGTH AS MaximumLength ");
sqlQuery.AppendLine(" ,CASE DATA_TYPE");
sqlQuery.AppendLine(" WHEN 'int' THEN 'integer' ");
sqlQuery.AppendLine(" WHEN 'bit' THEN 'integer' ");
sqlQuery.AppendLine(" WHEN 'float' THEN 'number' ");
sqlQuery.AppendLine(" WHEN 'datetime' THEN 'date' ");
sqlQuery.AppendLine(" ELSE 'string' ");
sqlQuery.AppendLine(" END AS DataType ");
sqlQuery.AppendLine(" FROM INFORMATION_SCHEMA.COLUMNS ");
sqlQuery.AppendLine(" WHERE TABLE_SCHEMA = @TableSchema AND TABLE_NAME = @TableName ");
return(DatabaseReader.Create(connectionOptions, providerName).Reader.Select <TableSchemaDto>(sqlQuery.ToString(),
new { TableSchema = schema, TableName = table }));
}
public void AdoNet_Test()
{
var sql = "SELECT Name AS P1, Sex AS P2, Age AS P3, Birthdate AS P4, Address AS P5 FROM Students WHERE Age = @Age";
var reader = DatabaseReader.Create("reportDebug").Reader.GetDataReader(sql, new { Age = 17 });
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendFormat("{0}|{1}|{2}|{3}|{4};", reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString());
}
if (!reader.IsClosed)
{
reader.Close();
}
Assert.IsNull(sb, sb.ToString());
}