public void RunSqlite()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Test(Value);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(1);";
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [main].[sqlite_master] WHERE [type] LIKE 'table'";
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
System.Console.WriteLine(dr[2].ToString());
}
}
}
var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = databaseReader.ReadAll();
var tableList = databaseReader.TableList();
var tables = databaseReader.AllTables();
var views = databaseReader.AllViews();
Assert.NotEmpty(tableList);
}
}
public void RunSqlite()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Test(Value);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(1);";
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [main].[sqlite_master] WHERE [type] LIKE 'table'";
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
System.Console.WriteLine(dr[2].ToString());
}
}
}
var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = databaseReader.ReadAll();
var tableList = databaseReader.TableList();
var tables = databaseReader.AllTables();
var views = databaseReader.AllViews();
Assert.NotEmpty(tableList);
}
}
public void RunTableList()
{
using (var connection = new SqlConnection(Northwind))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public static IEnumerable <BasicTableMetaData> GetMetaData(this SqlConnection connection)
{
using (var reader = new DatabaseSchemaReader.DatabaseReader(connection))
{
return(reader.AllTables().Select(z => new BasicTableMetaData()
{
Name = z.Name,
Schema = z.SchemaOwner,
PrimaryKeyName = z.PrimaryKeyColumn.Name
}));
}
}
public void RunTableList()
{
using (var connection = new SqlConnection(Northwind))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public void RunTableList()
{
//couldn't get package to restore.
using (var connection = new Npgsql.NpgsqlConnection(PostgreSql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public void RunTableList()
{
//couldn't get package to restore.
using (var connection = new Npgsql.NpgsqlConnection(PostgreSql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public void RunTableList()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(MySql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
dr.Owner = "sakila";
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public void RunTableList()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(MySql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
dr.Owner = "sakila";
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public void ReadData()
{
using (var connection = new SqlConnection(Northwind))
{
connection.Open();
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var table = dr.Table("Categories");
var reader = new DatabaseSchemaReader.Data.Reader(table);
var dt = reader.Read(connection);
Assert.IsTrue(dt.Rows.Count > 0);
}
}
public static BasicTableMetaData GetMetaData(this SqlConnection connection, string domain, string resource)
{
using (var reader = new DatabaseSchemaReader.DatabaseReader(connection))
{
var t = reader.AllTables().First(z => string.Equals(z.SchemaOwner, domain, StringComparison.OrdinalIgnoreCase) && string.Equals(z.Name, resource, StringComparison.OrdinalIgnoreCase));
return(new BasicTableMetaData()
{
Name = resource,
Schema = domain,
PrimaryKeyName = t.PrimaryKeyColumn.Name
});
}
}
public void RunTableList()
{
using (var connection = new SqlConnection(Northwind))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
//Microsoft.Data.SqlClient needs a longer timeout than System.Data.SqlClient (on the stored procedure query)
dr.CommandTimeout = 60;
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.IsTrue(tableList.Count > 0);
}
}
public void RunTableListWithTransaction()
{
using (var connection = new SqlConnection(Northwind))
{
connection.Open();
using (var txn = connection.BeginTransaction())
{
var dr = new DatabaseSchemaReader.DatabaseReader(txn);
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
txn.Rollback();
}
}
}
public void RunTableList()
{
//couldn't get package to restore.
using (var connection = new Npgsql.NpgsqlConnection(PostgreSql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
try
{
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.IsTrue(tableList.Count > 0);
}
catch (System.Net.Sockets.SocketException)
{
Console.WriteLine("PostgreSql not installed on this machine");
}
}
}
public void ReadDataWithFunc()
{
using (var connection = new SqlConnection(Northwind))
{
connection.Open();
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var table = dr.Table("Categories");
var reader = new DatabaseSchemaReader.Data.Reader(table);
var names = new List <string>();
reader.Read(connection, dataRecord =>
{
var name = dataRecord["CategoryName"].ToString();
names.Add(name);
return(true);
});
Assert.IsTrue(names.Count > 0);
}
}
public void ReadResultSets()
{
using (var connection = new SqlConnection(Northwind))
{
connection.Open();
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
dr.AllStoredProcedures();
var schema = dr.DatabaseSchema;
var rsr = new ResultSetReader(schema);
rsr.Execute(connection);
var sproc = schema.StoredProcedures.Find(x => x.Name == "SalesByCategory");
Assert.IsNotNull(sproc);
var rs = sproc.ResultSets.First();
foreach (var rsColumn in rs.Columns)
{
Console.WriteLine(rsColumn.Name);
Console.WriteLine(rsColumn.DbDataType);
}
Assert.IsNotNull(rs, "Stored procedure should return a result");
}
}
public void RunTableList()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(MySql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
dr.Owner = "sakila";
try
{
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.IsTrue(tableList.Count > 0);
}
catch (System.Resources.MissingManifestResourceException)
{
Console.WriteLine("MySql Core error");
//System.Resources.MissingManifestResourceException :
//Could not find any resources appropriate for the specified culture or the neutral culture.
//Make sure "MySql.Data.Resources.resources" was correctly embedded or linked into assembly "MySql.Data.Core" at compile time,
//or that all the satellite assemblies required are loadable and fully signed.
}
}
}
/// <summary>
/// Returns the full schema of the database including tables, indexes, foreign keys, etc.
/// </summary>
/// <remarks>
/// It's very slow for large databases
/// </remarks>
public static DataBaseSchema Load(DataBase database, string schemaProvider)
{
DataBaseSchema schema = new DataBaseSchema();
DatabaseSchema schemaReader;
using (var dbReader = new DatabaseSchemaReader.DatabaseReader(database.ConnectionString, schemaProvider))
{
dbReader.AllTables();
dbReader.AllViews();
try
{
dbReader.AllStoredProcedures();
}
catch { }
try
{
dbReader.AllUsers();
}
catch { }
schemaReader = dbReader.DatabaseSchema;
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
dbt.PrimaryKeyColumn.AddIdentity();
var table = new Table()
{
Name = dbt.Name,
DataBase = schema,
IdentityIncrement = dbt.PrimaryKeyColumn.IdentityDefinition.IdentityIncrement,
IdentitySeed = dbt.PrimaryKeyColumn.IdentityDefinition.IdentitySeed,
};
schema.Tables.Add(table);
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
var table = schema[dbt.Name];
foreach (DatabaseColumn dbc in dbt.Columns)
{
Column column = new Column()
{
Name = dbc.Name,
Table = table,
Description = dbc.Description,
IsNullable = dbc.Nullable,
IsAutoNumber = dbc.IsAutoNumber,
ComputedDefinition = dbc.ComputedDefinition,
DefaultValue = dbc.DefaultValue,
IsPrimaryKey = dbc.IsPrimaryKey,
Length = (uint?)dbc.Length,
Ordinal = dbc.Ordinal,
Precision = dbc.Precision,
Scale = dbc.Scale,
};
if (dbc.DataType != null)
{
column.DbType = DbTypeMapper.Parse(dbc.DataType.GetNetType());
}
else
{
if (dbc.DbDataType.StartsWith("varchar"))
{
column.DbType = DbType.AnsiString;
}
else if (dbc.DbDataType.StartsWith("int"))
{
column.DbType = DbType.Int32;
}
else if (dbc.DbDataType.StartsWith("decimal"))
{
column.DbType = DbType.Decimal;
}
else if (dbc.DbDataType.StartsWith("datetime"))
{
column.DbType = DbType.DateTime;
}
else if (dbc.DbDataType.StartsWith("money"))
{
column.DbType = DbType.Currency;
}
else if (dbc.DbDataType.StartsWith("char"))
{
column.DbType = DbType.AnsiStringFixedLength;
}
else if (dbc.DbDataType.StartsWith("text"))
{
column.DbType = DbType.AnsiString;
}
}
table.Columns.Add(column);
}
foreach (DatabaseIndex dbi in dbt.Indexes)
{
Index index = new Index()
{
Name = dbi.Name,
Table = table,
Direction = SortDirection.Ascending,
Unique = dbi.IsUnique,
};
foreach (DatabaseColumn dbc in dbi.Columns)
{
index.Columns.Add(table[dbc.Name]);
}
table.Indexes.Add(index);
}
foreach (DatabaseTrigger dbtr in dbt.Triggers)
{
DataBaseOperation operation = DataBaseOperation.Insert;
Enum.TryParse <DataBaseOperation>(dbtr.TriggerEvent, true, out operation);
Trigger trigger = new Trigger()
{
TriggerBody = dbtr.TriggerBody,
TriggerEvent = operation,
Table = table,
};
table.Triggers.Add(trigger);
}
foreach (DatabaseConstraint dbcons in dbt.CheckConstraints)
{
if (dbcons.ConstraintType == ConstraintType.Check)
{
CheckConstraint constraint = new CheckConstraint()
{
Expression = dbcons.Expression,
Table = table,
};
table.CheckConstraints.Add(constraint);
}
else if (dbcons.ConstraintType == ConstraintType.ForeignKey)
{
ForeignKey foreignKey = new ForeignKey()
{
Name = dbcons.Name,
DeleteAction = schema.ParseConstraintAction(dbcons.DeleteRule),
UpdateAction = schema.ParseConstraintAction(dbcons.UpdateRule),
RemoteTable = schema[dbcons.RefersToTable],
Table = table,
};
var referencedColumns = dbcons.ReferencedColumns(schemaReader).ToArray();
for (int i = 0; i < dbcons.Columns.Count; i++)
{
foreignKey.Columns.Add(new Tuple <Column, Column>(table[dbcons.Columns[i]], foreignKey.RemoteTable[referencedColumns[i]]));
}
table.ForeignKeys.Add(foreignKey);
}
}
}
foreach (DatabaseView dbv in schemaReader.Views)
{
View view = new View()
{
Name = dbv.Name,
Command = dbv.Sql,
Description = dbv.Description,
DataBase = schema,
};
schema.Views.Add(view);
}
foreach (DatabaseUser dbu in schemaReader.Users)
{
User user = new User()
{
Name = dbu.Name,
DataBase = schema,
};
schema.Users.Add(user);
}
return(schema);
}
/// <summary>
/// Returns the full schema of the database including tables, indexes, foreign keys, etc.
/// </summary>
/// <remarks>
/// It's very slow for large databases
/// </remarks>
public static DataBaseSchema Load(DataBase database, string schemaProvider)
{
DataBaseSchema schema = new DataBaseSchema();
DatabaseSchema schemaReader;
using (var dbReader = new DatabaseSchemaReader.DatabaseReader(database.ConnectionString, schemaProvider))
{
dbReader.AllTables();
dbReader.AllViews();
try
{
dbReader.AllStoredProcedures();
}
catch { }
try
{
dbReader.AllUsers();
}
catch { }
schemaReader = dbReader.DatabaseSchema;
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
dbt.PrimaryKeyColumn.AddIdentity();
var table = new Table()
{
Name = dbt.Name,
DataBase = schema,
IdentityIncrement = dbt.PrimaryKeyColumn.IdentityDefinition.IdentityIncrement,
IdentitySeed = dbt.PrimaryKeyColumn.IdentityDefinition.IdentitySeed,
};
schema.Tables.Add(table);
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
var table = schema[dbt.Name];
foreach (DatabaseColumn dbc in dbt.Columns)
{
Column column = new Column()
{
Name = dbc.Name,
Table = table,
Description = dbc.Description,
IsNullable = dbc.Nullable,
IsAutoNumber = dbc.IsAutoNumber,
ComputedDefinition = dbc.ComputedDefinition,
DefaultValue = dbc.DefaultValue,
IsPrimaryKey = dbc.IsPrimaryKey,
Length = (uint?) dbc.Length,
Ordinal = dbc.Ordinal,
Precision = dbc.Precision,
Scale = dbc.Scale,
};
if (dbc.DataType != null)
{
column.DbType = DbTypeMapper.Parse(dbc.DataType.GetNetType());
}
else
{
if(dbc.DbDataType.StartsWith("varchar"))
{
column.DbType = DbType.AnsiString;
}
else if (dbc.DbDataType.StartsWith("int"))
{
column.DbType = DbType.Int32;
}
else if (dbc.DbDataType.StartsWith("decimal"))
{
column.DbType = DbType.Decimal;
}
else if (dbc.DbDataType.StartsWith("datetime"))
{
column.DbType = DbType.DateTime;
}
else if (dbc.DbDataType.StartsWith("money"))
{
column.DbType = DbType.Currency;
}
else if (dbc.DbDataType.StartsWith("char"))
{
column.DbType = DbType.AnsiStringFixedLength;
}
else if (dbc.DbDataType.StartsWith("text"))
{
column.DbType = DbType.AnsiString;
}
}
table.Columns.Add(column);
}
foreach (DatabaseIndex dbi in dbt.Indexes)
{
Index index = new Index()
{
Name = dbi.Name,
Table = table,
Direction = SortDirection.Ascending,
Unique = dbi.IsUnique,
};
foreach (DatabaseColumn dbc in dbi.Columns)
{
index.Columns.Add(table[dbc.Name]);
}
table.Indexes.Add(index);
}
foreach (DatabaseTrigger dbtr in dbt.Triggers)
{
DataBaseOperation operation = DataBaseOperation.Insert;
Enum.TryParse<DataBaseOperation>(dbtr.TriggerEvent, true, out operation);
Trigger trigger = new Trigger()
{
TriggerBody = dbtr.TriggerBody,
TriggerEvent = operation,
Table = table,
};
table.Triggers.Add(trigger);
}
foreach (DatabaseConstraint dbcons in dbt.CheckConstraints)
{
if (dbcons.ConstraintType == ConstraintType.Check)
{
CheckConstraint constraint = new CheckConstraint()
{
Expression = dbcons.Expression,
Table = table,
};
table.CheckConstraints.Add(constraint);
}
else if (dbcons.ConstraintType == ConstraintType.ForeignKey)
{
ForeignKey foreignKey = new ForeignKey()
{
Name= dbcons.Name,
DeleteAction = schema.ParseConstraintAction(dbcons.DeleteRule),
UpdateAction =schema.ParseConstraintAction(dbcons.UpdateRule),
RemoteTable = schema[dbcons.RefersToTable],
Table = table,
};
var referencedColumns = dbcons.ReferencedColumns(schemaReader).ToArray();
for (int i = 0; i < dbcons.Columns.Count; i++)
{
foreignKey.Columns.Add(new Tuple<Column,Column>(table[dbcons.Columns[i]], foreignKey.RemoteTable[referencedColumns[i]]));
}
table.ForeignKeys.Add(foreignKey);
}
}
}
foreach (DatabaseView dbv in schemaReader.Views)
{
View view = new View()
{
Name = dbv.Name,
Command = dbv.Sql,
Description = dbv.Description,
DataBase = schema,
};
schema.Views.Add(view);
}
foreach (DatabaseUser dbu in schemaReader.Users)
{
User user = new User()
{
Name = dbu.Name,
DataBase = schema,
};
schema.Users.Add(user);
}
return schema;
}
