public static void CopyTableStructure(SqlTableReference sourceTable, SqlTableReference targetTable) { var sisTable = InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinition(sourceTable); var createTableScript = SISToSqlDmlCreateStatementGenerator.GenerateCreateTableScript(sisTable); targetTable.SqlConnectionProvider.Execute(createTableScript); }
public static string GetInformationSchemaColumnsSqlQuery(SqlTableReference sqlTableReference) { var sql = string.Format(_sqlInformationSchemaColumn, sqlTableReference.TableName.Debracketize(), sqlTableReference.SqlConnectionProvider.DatabaseName); sql = AddSchemaWhereClauseIfNecessary(sql, sqlTableReference); return(sql); }
public void EnsureCreateClass_FromSqlTableReference_AndFromQuery_ProduceEquivalentResults() { string classFromQuery = string.Empty, classFromTable = string.Empty; "Given a C# class generated from a query" ._(() => { var query = string.Format("select * from {0}", TableName); classFromQuery = CSharpClassGeneratorFromQueryViaSqlDescribeResultSet .GenerateClass(SqlConnectionProviders.AdventureWorksDb, query, "Employee"); var compileResults = RoslynHelper.TryCompile(classFromQuery); compileResults.IsValid().Should().BeTrue(); }); "Given a C# class generated from SQL InformationSchema metadata" ._(() => { var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, TableName); classFromTable = CSharpClassGeneratorFromInformationSchema.CreateCSharpClass(sqlTableReference); var compileResults = RoslynHelper.TryCompile(classFromTable); compileResults.IsValid().Should().BeTrue(); }); "They should produce identical output" ._(() => { Console.WriteLine("From Query:\r\n" + classFromQuery); Console.WriteLine("From Table:\r\n" + classFromTable); classFromTable.Should().BeEquivalentTo(classFromQuery); }); }
public static string CreateCSharpClass(SqlTableReference sqlTableReference, string?className = default(string)) { var tableName = className ?? sqlTableReference.TableName; var schemaColumns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns(sqlTableReference); return(CreateCSharpClass(schemaColumns, tableName, CSharpClassTextGeneratorOptions.Default)); }
public void CreateClassFromSqlTableReferenceForActualUseWhenNeedingToGenerateCSharpClasses() { var sqlTableReference = new SqlTableReference(SqlConnectionProviders.PbsiCopy, "PbsiWM.MTDINV_LINE"); var sqlTable = SqlTableFactory.Create(sqlTableReference); var cSharpClass = CSharpClassGeneratorFromSqlTable.GenerateClass(sqlTable); Console.WriteLine(cSharpClass); }
public void CreateClassFromSqlTableReference() { var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, TableName); var sqlTable = SqlTableFactory.Create(sqlTableReference); var cSharpClass = CSharpClassGeneratorFromSqlTable.GenerateClass(sqlTable); Console.WriteLine(cSharpClass); }
public void RetrieveHumanResourcesEmployeeTableAsJson() { var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, "HumanResources.Employee"); var informationSchemaTableDefinition = InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinition(sqlTableReference); var tableDefinitionAsJson = JsonConvert.SerializeObject(informationSchemaTableDefinition); Console.WriteLine(tableDefinitionAsJson); }
public static SqlTableReference ToSqlTableReference(this SISTable sisTable, ISqlConnectionProvider sqlConnectionProvider) { var sqlTableReference = new SqlTableReference(sqlConnectionProvider, sisTable.TABLE_SCHEMA, sisTable.TABLE_NAME); return(sqlTableReference); }
public void CreateClassFromSqlTableReference() { var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, TableName); var cSharpClass = CSharpClassGeneratorFromInformationSchema.CreateCSharpClass(sqlTableReference); var compileResult = RoslynHelper.TryCompile(cSharpClass); compileResult.IsValid().Should().BeTrue(); Console.WriteLine(cSharpClass); }
public static void EnsureTableExists <T>(this SqlTableReference tableReference) { var tableExists = CheckTableExistence(tableReference.SqlConnectionProvider, tableReference.SchemaName, tableReference.TableName); if (!tableExists) { CreateTable <T>(tableReference.SqlConnectionProvider, tableReference.SchemaName, tableReference.TableName); } }
public static SISTable GetInformationSchemaTable(SqlTableReference sqlTableReference) { var sqlQuery = InformationSchemaMetadataSqlQueryGenerator.GetInformationSchemaTableSqlQuery(sqlTableReference); var tables = sqlTableReference.SqlConnectionProvider.Query <SISTable>(sqlQuery).ToList(); CheckOnlyOneTableWasReturned(tables); return(tables.SingleOrDefault()); }
public static IList <SISColumn> GetInformationSchemaColumns(SqlTableReference sqlTableReference) { var sqlQuery = InformationSchemaMetadataSqlQueryGenerator.GetInformationSchemaColumnsSqlQuery(sqlTableReference); var tableColumns = sqlTableReference.SqlConnectionProvider.Query <SISColumn>(sqlQuery) .ToList(); return(tableColumns); }
public static InformationSchemaTableDefinition GetInformationSchemaTableDefinition( SqlTableReference sqlTableReference) { var definition = new InformationSchemaTableDefinition(); definition.InformationSchemaTable = GetInformationSchemaTable(sqlTableReference); definition.InformationSchemaColumns = GetInformationSchemaColumns(sqlTableReference); definition.TableConstraints = GetTableConstraints(sqlTableReference); return(definition); }
/// <summary>Visits the <see cref="T:System.Linq.Expressions.ParameterExpression" />.</summary> /// <returns>The modified expression, if it or any subexpression was modified; otherwise, returns the original expression.</returns> /// <param name="node">The expression to visit.</param> protected override Expression VisitParameter(ParameterExpression node) { var result = new SqlTableReference() { Table = _tables[node.Name] }; Return(result); return(node); }
public static string GenerateClassFromTable(this ISqlConnectionProvider sqlConnectionProvider, string schemaname, string tableName, string className = null) { className = className ?? tableName; var sqlTableReference = new SqlTableReference(sqlConnectionProvider, schemaname, tableName); var sqlTable = SqlTableFactory.Create(sqlTableReference); return(CSharpClassGeneratorFromSqlTable.GenerateClass(sqlTable, className)); }
private static string AddSchemaWhereClauseIfNecessary(string sql, SqlTableReference sqlTableReference) { var hasSchemaName = !string.IsNullOrWhiteSpace(sqlTableReference.SchemaName); if (hasSchemaName) { sql += $" AND TABLE_SCHEMA = '{sqlTableReference.SchemaName}'"; } return(sql); }
private static List <TableConstraintInfoDto> GetTableConstraints(SqlTableReference sqlTableReference) { var whereclause = $"WHERE tableConstraint.TABLE_SCHEMA = @schemaName AND tableConstraint.TABLE_NAME = @tableName "; var query = TableConstraintInfoDto.SqlQuery.Replace("--WHERE", whereclause); var queryParams = new { schemaName = sqlTableReference.SchemaName, tableName = sqlTableReference.TableName }; var result = sqlTableReference.SqlConnectionProvider.Query <TableConstraintInfoDto>(query, queryParams) .ToList(); return(result); }
public static SqlTable Create(SqlTableReference sqlTableReference) { var columns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns(sqlTableReference); var sqlColumns = columns.Select(CSharpClassGeneratorFromInformationSchema.InformationSchemaColumnToSqlColumn).ToList(); var sqlTable = new SqlTable() { Name = sqlTableReference.TableName, Schema = sqlTableReference.SchemaName, SqlColumns = sqlColumns }; return(sqlTable); }
public void CreateTableShouldWork() { ISqlConnectionProvider sourceSqlConnectionProvider = null; UniqueDbConnectionProvider targetSqlConnectionProvider = null; SqlTableReference sourceSqlTableReference = null; SqlTableReference targetSqlTableReference = null; "Given a blank target database" ._(() => { targetSqlConnectionProvider = new UniqueDbConnectionProvider(new UniqueDbConnectionProviderOptions( "ws2012sqlexp1\\sqlexpress", "TableManipulationTests")); targetSqlConnectionProvider.CreateDatabase(); }); using (targetSqlConnectionProvider.ToSelfDeletingDisposable()) { "Given a source database and a new blank database" ._(() => { sourceSqlConnectionProvider = SqlConnectionProviders.AdventureWorksDb; }); "Given a source table to copy" ._(() => { sourceSqlTableReference = new SqlTableReference( sourceSqlConnectionProvider, "Person.Person"); }); "When copying the source table to the target db." ._(() => { targetSqlTableReference = new SqlTableReference( targetSqlConnectionProvider, "dbo.Person"); TableManipulation.CopyTableStructure(sourceSqlTableReference, targetSqlTableReference); }); "Then there should be a copy of the table at the target DB" ._(() => { var tableSchemas = InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider); tableSchemas.Count.Should().Be(1); tableSchemas[0].TABLE_NAME.Should().Be("Person"); }); } }
public void DropTableShouldWork() { ISqlConnectionProvider sourceSqlConnectionProvider = null; UniqueDbConnectionProvider targetSqlConnectionProvider = null; SqlTableReference sourceSqlTableReference = null; SqlTableReference targetSqlTableReference = null; "Given a table to drop in the database." ._(() => { targetSqlConnectionProvider = new UniqueDbConnectionProvider(new UniqueDbConnectionProviderOptions( "ws2012sqlexp1\\sqlexpress", "TableManipulationTests")); targetSqlConnectionProvider.CreateDatabase(); sourceSqlConnectionProvider = SqlConnectionProviders.AdventureWorksDb; sourceSqlTableReference = new SqlTableReference( sourceSqlConnectionProvider, "Person.Person"); targetSqlTableReference = new SqlTableReference( targetSqlConnectionProvider, "dbo.Person"); TableManipulation.CopyTableStructure(sourceSqlTableReference, targetSqlTableReference); var tableSchemas = InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider); tableSchemas.Count.Should().Be(1); tableSchemas[0].TABLE_NAME.Should().Be("Person"); }); using (targetSqlConnectionProvider.ToSelfDeletingDisposable()) { "When dropping the target table." ._(() => { TableManipulation.DropTable(targetSqlTableReference); }); "Then the table should be removed from the database." ._(() => { var tableSchemas = InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider); tableSchemas.Count.Should().Be(0); }); } }
public static string GenerateDropTableScript(SqlTableReference tableReference) { return(GenerateDropTableScript(tableReference.SchemaName, tableReference.TableName)); }
public static void TruncateTable(this SqlTableReference tableReference) { TruncateTable(tableReference.SqlConnectionProvider, tableReference.SchemaName, tableReference.TableName); }
public static void DropTable(SqlTableReference table) { var dropTableScript = DropSqlTableReference.GenerateDropTableScript(table); table.SqlConnectionProvider.Execute(dropTableScript); }
private SqlQueryExpression VisitColumn(MemberExpression node) { // first we detect closures if (!node.IsScopedParameterAccess()) { return(new SqlObjectParameter() { Parameter = ObtainResult <object>(node) }); } // Get whether property points to an entity (case like x.User.Order) if (_isEntity(node.Type)) { // if so, we have nested table access var nestedResult = new SqlTableReference() { Table = ObtainNestedTableReference(node, false) }; return(nestedResult); } TableReference tableRef = null; PropertyInfo colId = null; var root = node.GetRootMember(); var nex = node.Expression.Unconvert(); if (nex.NodeType != ExpressionType.Parameter) { if (nex.NodeType == ExpressionType.MemberAccess && nex is MemberExpression mex) //nested table column { var derived = (node.Member.DeclaringType != mex.Type) && _isEntity(node.Member.DeclaringType); tableRef = ObtainNestedTableReference(mex, derived); colId = node.Member as PropertyInfo; } else { throw new Exception(string.Format("Please refer only top-level properties of {0}", root.Type)); } } if (tableRef == null && colId == null) { var parRef = root as ParameterExpression; if (parRef == null) { throw new Exception("Unknown column reference: " + node.ToString()); } tableRef = _tables[parRef.Name]; var derived = (node.Member.DeclaringType != root.Type) && _isEntity(node.Member.DeclaringType); colId = node.Member as PropertyInfo; } var result = new SqlColumnReference() { Column = colId, Table = tableRef }; return(result); }
private string VisitTableReference(SqlTableReference x9) { return(EmitParameter(x9)); }
protected virtual string VisitTableReference(SqlTableReference expr) { return(VisitTableReference(expr.Table, expr.ChildrenJoinedAs, expr.AsAlias)); }