/// <summary>
/// Return the line that represents the given <paramref name="col"/> for slotting into a CREATE statement SQL e.g. "description varchar(20)"
/// </summary>
/// <param name="col"></param>
/// <param name="datatype"></param>
/// <param name="syntaxHelper"></param>
/// <returns></returns>
protected virtual string GetCreateTableSqlLineForColumn(DatabaseColumnRequest col, string datatype, IQuerySyntaxHelper syntaxHelper)
{
return(string.Format("{0} {1} {2} {3} {4} {5}",
syntaxHelper.EnsureWrapped(col.ColumnName),
datatype,
col.Default != MandatoryScalarFunctions.None ? "default " + syntaxHelper.GetScalarFunctionSql(col.Default) : "",
string.IsNullOrWhiteSpace(col.Collation) ? "" : "COLLATE " + col.Collation,
col.AllowNulls && !col.IsPrimaryKey ? " NULL" : " NOT NULL",
col.IsAutoIncrement ? syntaxHelper.GetAutoIncrementKeywordIfAny() : ""
));
}
/// <inheritdoc/>
public string ScriptTableCreation(DiscoveredTable table, bool dropPrimaryKeys, bool dropNullability, bool convertIdentityToInt, DiscoveredTable toCreateTable = null)
{
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
foreach (DiscoveredColumn c in table.DiscoverColumns())
{
string sqlType = c.DataType.SQLType;
if (c.IsAutoIncrement && convertIdentityToInt)
{
sqlType = "int";
}
bool isToDifferentDatabaseType = toCreateTable != null && toCreateTable.Database.Server.DatabaseType != table.Database.Server.DatabaseType;
//translate types
if (isToDifferentDatabaseType)
{
var fromtt = table.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
var tott = toCreateTable.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
sqlType = fromtt.TranslateSQLDBType(c.DataType.SQLType, tott);
}
var colRequest = new DatabaseColumnRequest(c.GetRuntimeName(), sqlType, c.AllowNulls || dropNullability);
colRequest.IsPrimaryKey = c.IsPrimaryKey && !dropPrimaryKeys;
colRequest.IsAutoIncrement = c.IsAutoIncrement && !convertIdentityToInt;
colRequest.AllowNulls = colRequest.AllowNulls && !colRequest.IsAutoIncrement;
//if there is a collation
if (!string.IsNullOrWhiteSpace(c.Collation))
{
//if the script is to be run on a database of the same type
if (toCreateTable == null || !isToDifferentDatabaseType)
{
//then specify that the column should use the live collation
colRequest.Collation = c.Collation;
}
}
columns.Add(colRequest);
}
var destinationTable = toCreateTable ?? table;
string schema = toCreateTable != null ? toCreateTable.Schema : table.Schema;
return(table.Database.Helper.GetCreateTableSql(destinationTable.Database, destinationTable.GetRuntimeName(), columns.ToArray(), null, false, schema));
}
public DiscoveredTable CreateTable(CreateTableArgs args)
{
var typeDictionary = new Dictionary <string, Guesser>(StringComparer.CurrentCultureIgnoreCase);
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
List <DatabaseColumnRequest> customRequests = args.ExplicitColumnDefinitions != null
? args.ExplicitColumnDefinitions.ToList()
: new List <DatabaseColumnRequest>();
if (args.DataTable != null)
{
ThrowIfObjectColumns(args.DataTable);
//If we have a data table from which to create the table from
foreach (DataColumn column in args.DataTable.Columns)
{
//do we have an explicit overriding column definition?
DatabaseColumnRequest overriding = customRequests.SingleOrDefault(c => c.ColumnName.Equals(column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
//yes
if (overriding != null)
{
columns.Add(overriding);
customRequests.Remove(overriding);
//Type requested is a proper FAnsi type (e.g. string, at least 5 long)
var request = overriding.TypeRequested;
if (request == null)
{
if (!string.IsNullOrWhiteSpace(overriding.ExplicitDbType))
{
//Type is for an explicit SQL Type e.g. varchar(5)
//Translate the sql type to a FAnsi type definition
var tt = args.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
request = tt.GetDataTypeRequestForSQLDBType(overriding.ExplicitDbType);
}
else
{
throw new Exception(string.Format(FAnsiStrings.DiscoveredDatabaseHelper_CreateTable_DatabaseColumnRequestMustHaveEitherTypeRequestedOrExplicitDbType, column));
}
}
var guesser = GetGuesser(request);
CopySettings(guesser, args);
typeDictionary.Add(overriding.ColumnName, guesser);
}
else
{
//no, work out the column definition using a guesser
Guesser guesser = GetGuesser(column);
guesser.Culture = args.Culture;
CopySettings(guesser, args);
guesser.AdjustToCompensateForValues(column);
//if DoNotRetype is set on the column adjust the requested CSharpType to be the original type
if (column.GetDoNotReType())
{
guesser.Guess.CSharpType = column.DataType;
}
typeDictionary.Add(column.ColumnName, guesser);
columns.Add(new DatabaseColumnRequest(column.ColumnName, guesser.Guess, column.AllowDBNull)
{
IsPrimaryKey = args.DataTable.PrimaryKey.Contains(column)
});
}
}
}
else
{
//If no DataTable is provided just use the explicitly requested columns
columns = customRequests;
}
if (args.Adjuster != null)
{
args.Adjuster.AdjustColumns(columns);
}
//Get the table creation SQL
string bodySql = GetCreateTableSql(args.Database, args.TableName, columns.ToArray(), args.ForeignKeyPairs, args.CascadeDelete, args.Schema);
//connect to the server and send it
var server = args.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
ExecuteBatchNonQuery(bodySql, con);
}
//Get reference to the newly created table
var tbl = args.Database.ExpectTable(args.TableName, args.Schema);
//unless we are being asked to create it empty then upload the DataTable to it
if (args.DataTable != null && !args.CreateEmpty)
{
using (var bulk = tbl.BeginBulkInsert(args.Culture))
{
bulk.DateTimeDecider.Settings.ExplicitDateFormats = args.GuessSettings.ExplicitDateFormats;
bulk.Upload(args.DataTable);
}
}
args.OnTableCreated(typeDictionary);
return(tbl);
}
public DiscoveredTable CreateTable(CreateTableArgs args)
{
var typeDictionary = new Dictionary <string, DataTypeComputer>(StringComparer.CurrentCultureIgnoreCase);
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
List <DatabaseColumnRequest> customRequests = args.ExplicitColumnDefinitions != null
? args.ExplicitColumnDefinitions.ToList()
: new List <DatabaseColumnRequest>();
if (args.DataTable != null)
{
//If we have a data table from which to create the table from
foreach (DataColumn column in args.DataTable.Columns)
{
//do we have an explicit overriding column definition?
DatabaseColumnRequest overriding = customRequests.SingleOrDefault(c => c.ColumnName.Equals(column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
//yes
if (overriding != null)
{
columns.Add(overriding);
customRequests.Remove(overriding);
//Type reqeuested
var request = overriding.TypeRequested;
//Type is for an explicit Type e.g. datetime
if (request == null)
{
if (!string.IsNullOrWhiteSpace(overriding.ExplicitDbType))
{
var tt = args.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
request = new DatabaseTypeRequest(
tt.GetCSharpTypeForSQLDBType(overriding.ExplicitDbType),
tt.GetLengthIfString(overriding.ExplicitDbType),
tt.GetDigitsBeforeAndAfterDecimalPointIfDecimal(overriding.ExplicitDbType));
}
else
{
throw new Exception("explicitColumnDefinitions for column " + column + " did not contain either a TypeRequested or ExplicitDbType");
}
}
typeDictionary.Add(overriding.ColumnName, GetDataTypeComputer(request));
}
else
{
//no, work out the column definition using a datatype computer
DataTypeComputer computer = GetDataTypeComputer(column);
typeDictionary.Add(column.ColumnName, computer);
columns.Add(new DatabaseColumnRequest(column.ColumnName, computer.GetTypeRequest(), column.AllowDBNull)
{
IsPrimaryKey = args.DataTable.PrimaryKey.Contains(column)
});
}
}
}
else
{
//If no DataTable is provided just use the explicitly requested columns
columns = customRequests;
}
if (args.Adjuster != null)
{
args.Adjuster.AdjustColumns(columns);
}
//Get the table creation SQL
string bodySql = GetCreateTableSql(args.Database, args.TableName, columns.ToArray(), args.ForeignKeyPairs, args.CascadeDelete, args.Schema);
//connect to the server and send it
var server = args.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
ExecuteBatchNonQuery(bodySql, con);
}
//Get reference to the newly created table
var tbl = args.Database.ExpectTable(args.TableName, args.Schema);
//unless we are being asked to create it empty then upload the DataTable to it
if (args.DataTable != null && !args.CreateEmpty)
{
tbl.BeginBulkInsert().Upload(args.DataTable);
}
args.OnTableCreated(typeDictionary);
return(tbl);
}