/// <summary>
/// Reads the table data and returns the INSERT statements
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="providerName">Name of the provider.</param>
/// <returns></returns>
public string ReadTable(DatabaseTable databaseTable, string connectionString, string providerName)
{
var r = new Reader(databaseTable, connectionString, providerName);
r.PageSize = PageSize;
var dt = r.Read();
var w = new InsertWriter(databaseTable, dt);
w.IncludeIdentity = IncludeIdentity;
w.IncludeBlobs = IncludeBlobs;
return w.Write(FindSqlType(providerName));
}
/// <summary>
/// Reads the table data and returns the INSERT statements
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="providerName">Name of the provider.</param>
/// <returns></returns>
public string ReadTable(DatabaseTable databaseTable, string connectionString, string providerName)
{
var r = new Reader(databaseTable, connectionString, providerName);
r.PageSize = PageSize;
var dt = r.Read();
var w = new InsertWriter(databaseTable, dt);
w.IncludeIdentity = IncludeIdentity;
w.IncludeBlobs = IncludeBlobs;
return(w.Write(FindSqlType(providerName)));
}
/// <summary>
/// Reads the table data and returns the INSERT statements
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public string ReadTable(DatabaseTable databaseTable, DbConnection connection)
{
#if NETSTANDARD2_0
var r = new Reader(databaseTable);
#else
var r = new Reader(databaseTable, connection.ConnectionString, connection.GetType().Namespace);
#endif
r.PageSize = PageSize;
var dt = r.Read(connection);
var w = new InsertWriter(databaseTable, dt);
w.IncludeIdentity = IncludeIdentity;
w.IncludeBlobs = IncludeBlobs;
var providerName = connection.GetType().Namespace;
return(w.Write(FindSqlType(providerName)));
}
public void TestInsertIntegration()
{
//arrange
var dbReader = TestHelper.GetNorthwindReader();
var table = dbReader.Table("Orders");
var rdr = new Reader(table, ConnectionString, Providername);
var dt = rdr.Read();
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//assert
Console.WriteLine(txt);
//check this manually
}
public void TestOracleInsert()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn { Name = "Id", DbDataType = "INTEGER" });
table.Columns.Add(new DatabaseColumn { Name = "Name", DbDataType = "VARCHAR" });
var dt = new DataTable { Locale = CultureInfo.InvariantCulture };
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.Oracle);
//we don't care about formatting
txt = RemoveLineBreaks(txt);
//assert
Assert.AreEqual("INSERT INTO \"Categories\" ( \"Id\", \"Name\") VALUES (1 ,'Hello');", txt);
}
public void TestSqlServerInsertExcludeIdentity()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn { Name = "Id", DbDataType = "INTEGER", IsAutoNumber = true });
table.Columns.Add(new DatabaseColumn { Name = "Name", DbDataType = "VARCHAR" });
var dt = new DataTable { Locale = CultureInfo.InvariantCulture };
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = false;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//we don't care about formatting
txt = RemoveLineBreaks(txt);
//assert
Assert.AreEqual("INSERT INTO [Categories] ( [Name]) VALUES (N'Hello');", txt);
}
public void TestSqlServerInsertIncludeIdentity()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn { Name = "Id", DbDataType = "INTEGER", IsAutoNumber = true });
table.Columns.Add(new DatabaseColumn { Name = "Name", DbDataType = "VARCHAR" });
var dt = new DataTable { Locale = CultureInfo.InvariantCulture };
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//we don't care about formatting
txt = RemoveLineBreaks(txt);
//assert
Assert.IsTrue(txt.Contains("INSERT INTO [Categories] ( [Id], [Name]) VALUES (1 ,N'Hello');"));
Assert.IsTrue(txt.StartsWith("SET IDENTITY_INSERT [Categories] ON", StringComparison.OrdinalIgnoreCase));
Assert.IsTrue(txt.Contains("SET IDENTITY_INSERT [Categories] OFF"));
Assert.IsTrue(txt.Contains("DBCC CHECKIDENT ([Categories])"));
}
public bool RunData(DirectoryInfo directory, SqlType dialect, DatabaseTable table)
{
if (table == null)
{
Message = "No table";
return false;
}
var path = Path.Combine(directory.FullName, table.Name + "_data.sql");
try
{
var rdr = new Reader(table, table.DatabaseSchema.ConnectionString, table.DatabaseSchema.Provider);
var dt = rdr.Read();
var insertWriter = new InsertWriter(table, dt);
//we have special processing for sqlserver
if (dialect == SqlType.SqlServer || dialect == SqlType.SqlServerCe
//assume db2 uses "generated by default" not "generated always".
|| dialect == SqlType.SQLite
//SQLite is fine with identity inserts and MySQL autoincrement is effectively DEFAULT=MAX(col) anyway
|| dialect == SqlType.Db2 || dialect == SqlType.MySql)
{
insertWriter.IncludeIdentity = true;
}
//act
string txt = insertWriter.Write(dialect);
File.WriteAllText(path, txt);
Message = @"Wrote " + path;
return true;
}
catch (Exception exception)
{
Message =
@"An error occurred while creating the script.\n" + exception.Message;
}
return false;
}