protected override void WriteArguments(SqlWriter writer) {
if (isMax) {
writer.WriteKeyword("MAX");
} else {
writer.WriteScript(extension, WhitespacePadding.None);
}
}
internal void WriteNonGlobalInternal(SqlWriter writer) {
if (!quote) {
writer.Write(Value);
} else {
base.WriteToInternal(writer, false);
}
}
public override void WriteTo(SqlWriter writer) {
writer.WriteScript(tableNameQualified, WhitespacePadding.None);
writer.Write('.');
writer.WriteScript(columnName, WhitespacePadding.None);
writer.Write('.');
base.WriteTo(writer);
}
public override void WriteTo(SqlWriter writer) {
WriteCommentsTo(writer);
writer.WriteKeyword("OPTION ");
writer.Write('(');
writer.WriteScriptSequence(options, WhitespacePadding.None, w => w.Write(", "));
writer.Write(')');
}
public override void WriteTo(SqlWriter writer) {
base.WriteTo(writer);
writer.WriteKeyword("DEFAULT ");
writer.Write('(');
writer.WriteScript(defaultValue, WhitespacePadding.None);
writer.Write(')');
}
public static Statement CreateAddColumnStatement(string tableName, Column column)
{
var writer = new SqlWriter();
writer.Alter.Table.QuotedName(tableName).Add.Flush();
WriteColumnDefinition(writer, column);
return new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None);
}
public override void WriteTo(SqlWriter writer) {
WriteCommentsTo(writer);
writer.WriteFunction("UPDATE");
writer.Write('(');
writer.WriteScript(columnName, WhitespacePadding.None);
writer.Write(')');
}
/// <summary>
/// Initializes a new instance of the <see cref="ProcedureGenerator"/> class.
/// </summary>
/// <param name="table">The table.</param>
public ProcedureGenerator(DatabaseTable table)
: base(table)
{
SqlWriter = new SqlWriter(table, SqlType.Oracle);
SqlWriter.InStoredProcedure = true;
PackageName = "PACK_" + TableName;
}
protected internal override void WriteToInternal(SqlWriter writer, bool isPartOfQualifiedName) {
Debug.Assert(!isPartOfQualifiedName);
if (global) {
writer.WriteKeyword("GLOBAL ");
}
WriteNonGlobalInternal(writer);
}
public override void WriteTo(SqlWriter writer) {
writer.WriteFunction("COUNT");
writer.Write('(');
writer.WriteDuplicateRestriction(restriction, WhitespacePadding.SpaceAfter);
writer.WriteScript(expression, WhitespacePadding.None);
writer.Write(')');
}
protected internal override void WriteToInternal(SqlWriter writer, bool isPartOfQualifiedName) {
if (IsWildcard) {
writer.Write(Value);
} else {
base.WriteToInternal(writer, isPartOfQualifiedName);
}
}
public override void WriteTo(SqlWriter writer) {
WriteCommentsTo(writer);
writer.WriteScript(valueExpression, WhitespacePadding.None);
base.WriteTo(writer);
writer.WriteKeyword(" IN ");
writer.WriteScript(tuple, WhitespacePadding.None);
}
public override void WriteTo(SqlWriter writer) {
base.WriteTo(writer);
writer.WriteKeyword("CHECK ");
writer.WriteScript(replication, WhitespacePadding.SpaceAfter);
writer.WriteScript(predicate, WhitespacePadding.None);
writer.Write(')');
}
protected virtual void WriteToInternal(SqlWriter writer) {
writer.WriteScript(function, WhitespacePadding.None);
if (functions.Count > 0) {
writer.Write('.');
writer.WriteScriptSequence(functions, WhitespacePadding.None, w => w.Write('.'));
}
}
public override void WriteTo(SqlWriter writer) {
writer.WriteKeyword("FOR UPDATE");
if (columns.Count > 0) {
writer.WriteKeyword(" OF ");
writer.WriteScriptSequence(columns, WhitespacePadding.None, w => w.Write(", "));
}
}
public override void WriteTo(SqlWriter writer) {
writer.WriteScript(serverName, WhitespacePadding.None, null, w => w.Write('.'));
writer.WriteScript(databaseName, WhitespacePadding.None, null, w => w.Write('.'));
writer.WriteScript(schemaName, WhitespacePadding.None, null, w => w.Write('.'));
writer.WriteScript(tableName, WhitespacePadding.None);
base.WriteTo(writer);
}
public override void WriteTo(SqlWriter writer) {
base.WriteTo(writer);
writer.WriteKeyword("REFERENCES ");
writer.WriteScript(tableName, WhitespacePadding.None);
writer.WriteScript(refColumnName, WhitespacePadding.SpaceBefore);
writer.WriteScriptSequence(keyActions, WhitespacePadding.SpaceBefore, null);
}
protected internal override void WriteToInternal(SqlWriter writer, bool isPartOfQualifiedName) {
if (isPartOfQualifiedName || (!IsBuiltinType)) {
base.WriteToInternal(writer, isPartOfQualifiedName);
} else {
writer.WriteType(Value);
}
}
/// <summary>
/// We delegate the writing of the fragment to the appropriate type.
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
if (null != _sqlFragments)
{
foreach (object o in _sqlFragments)
{
string str = (o as String);
if (null != str)
{
writer.Write(str);
}
else
{
ISqlFragment sqlFragment = (o as ISqlFragment);
if (null != sqlFragment)
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
else
{
throw new InvalidOperationException();
}
}
}
}
}
public override void WriteTo(SqlWriter writer) {
WriteCommentsTo(writer);
writer.WriteKeyword("CREATE XML SCHEMA COLLECTION ");
writer.WriteScript(xmlSchemaCollectionName, WhitespacePadding.None);
writer.WriteKeyword(" AS ");
writer.WriteScript(expression, WhitespacePadding.None);
}
public override void WriteTo(SqlWriter writer) {
writer.WriteKeyword("IDENTITY");
writer.Write('(');
writer.WriteScript(typeName, WhitespacePadding.None);
writer.WriteScript(seed, WhitespacePadding.None, w => w.Write(", "), null);
writer.WriteScript(increment, WhitespacePadding.None, w => w.Write(", "), null);
writer.Write(')');
}
public override void WriteTo(SqlWriter writer) {
writer.WriteFunction("CAST");
writer.Write('(');
writer.WriteScript(expression, WhitespacePadding.None);
writer.WriteKeyword(" AS ");
writer.WriteScript(typeName, WhitespacePadding.None);
writer.Write(')');
}
public override sealed void WriteTo(SqlWriter writer) {
writer.WriteScript(parameterName, WhitespacePadding.SpaceAfter);
writer.WriteScript(parameterTypeName, WhitespacePadding.None);
WriteParameterQualifiers(writer);
if (readOnly) {
writer.WriteKeyword(" READONLY");
}
}
public AnyStatement(Identifier identifier, Sequence<Expression> expressions) {
using (StringWriter stringWriter = new StringWriter()) {
SqlWriter statementWriter = new SqlWriter(stringWriter, DatabaseEngine.Unknown);
statementWriter.WriteIdentifier(identifier.Value);
statementWriter.WriteScriptSequence(expressions, WhitespacePadding.SpaceBefore, null);
statementText = stringWriter.ToString();
}
}
public override void WriteTo(SqlWriter writer) {
WriteCommentsTo(writer);
writer.Write('(');
using (writer.Indent()) {
writer.WriteScript(expression, WhitespacePadding.None);
}
writer.Write(')');
}
public override void WriteTo(SqlWriter writer) {
WriteCommentsTo(writer);
writer.WriteScript(functionCall, WhitespacePadding.None);
writer.WriteKeyword(" OVER ");
writer.Write('(');
writer.WriteScript(rankingArguments, WhitespacePadding.None);
writer.Write(')');
}
public ProcedureGenerator(DatabaseTable table)
: base(table)
{
SqlWriter = new SqlWriter(table, SqlType.Db2);
SqlWriter.InStoredProcedure = true;
SqlWriter.FormatParameter = x => { return "p_" + x; };
FormatParameter = SqlWriter.FormatParameter;
}
/// <summary>
/// Write out the SKIP part of sql select statement
/// It basically writes SKIP (X).
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
writer.Write("SKIP (");
this.SkipCount.WriteSql(writer, sqlGenerator);
writer.Write(")");
writer.Write(" ");
}
public void WriteStartUpdate_WhenDisposed_ThrowsObjectDisposed()
{
StringBuilder builder = new StringBuilder();
SqlWriter writer = new SqlWriter(builder);
writer.Dispose();
AssertEx.Throws<ObjectDisposedException>(() => writer.WriteStartUpdate());
}
public override void WriteTo(SqlWriter writer) {
// we ignore the fill factor when computing a hash, so that differences due to fill factor only are ignored
if (writer.Mode != SqlWriterMode.ForHashing) {
writer.WriteKeyword("WITH FILLFACTOR");
writer.Write('=');
writer.WriteScript(fillfactor, WhitespacePadding.None);
}
}
public void WriteTable_WhenInsertStatement_WritesTable()
{
StringBuilder builder = new StringBuilder();
using (SqlWriter writer = new SqlWriter(builder))
{
writer.WriteStartInsert();
writer.WriteStartInto();
writer.WriteTable("Users");
Assert.AreEqual(SqlWriterState.StartInto, writer.WriteState);
}
Assert.AreEqual("INSERT INTO [Users]", builder.ToString());
}
public void TestGeneratedSqlForInsert()
{
//arrange
var table = LoadCategoriesFromNorthwind();
var writer = new SqlWriter(table, SqlType.SqlServer);
var sql = writer.InsertSql();
int identity;
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
con.Open();
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Transaction = transaction;
string identityParameterName = "Id";
foreach (var column in table.Columns)
{
var par = cmd.CreateParameter();
par.ParameterName = writer.ParameterName(column.Name);
if (column.IsAutoNumber)
{
//get the name of the identity parameter
identityParameterName = par.ParameterName;
par.Direction = ParameterDirection.Output;
par.DbType = DbType.Int32;
}
else
{
object value = DummyDataCreator.CreateData(column);
par.Value = value ?? DBNull.Value;
}
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
identity = (int)cmd.Parameters[identityParameterName].Value;
}
//explicit rollback. If we errored, implicit rollback.
transaction.Rollback();
}
}
//assert
Assert.AreNotEqual(0, identity);
}
public void WriteUnion_WhenSelectWithJoin_WritesUnionStatement()
{
StringBuilder builder = new StringBuilder();
using (SqlWriter writer = new SqlWriter(builder))
{
writer.WriteStartSelect();
writer.WriteColumn("u", "Id", null);
writer.WriteColumn("u", "Name", null);
writer.WriteColumn("p", "Age", null);
writer.WriteStartFrom();
writer.WriteTable("Users", "u");
writer.WriteStartJoin(SqlJoinType.Inner);
writer.WriteTable("Profiles", "p");
writer.WriteStartOn();
writer.WriteColumn("u", "Id", null);
writer.WriteOperator(SqlBinaryOperator.Equal);
writer.WriteColumn("p", "UserId", null);
//writer.WriteStartWhere();
//writer.WriteColumn("u", "Age", null);
//writer.WriteOperator(SqlBinaryOperator.LessThanOrEqual);
//writer.WriteValue(18);
writer.WriteEndSelect();
Assert.AreEqual(SqlWriterState.EndSelect, writer.WriteState);
writer.WriteUnion();
Assert.AreEqual(SqlWriterState.Union, writer.WriteState);
writer.WriteStartSelect();
writer.WriteColumn("u", "Id", null);
writer.WriteColumn("u", "Name", null);
writer.WriteColumn("p", "Age", null);
writer.WriteStartFrom();
writer.WriteTable("Users", "u");
writer.WriteStartJoin(SqlJoinType.Inner);
writer.WriteTable("Profiles", "p");
writer.WriteStartOn();
writer.WriteColumn("u", "Id", null);
writer.WriteOperator(SqlBinaryOperator.Equal);
writer.WriteColumn("p", "UserId", null);
writer.WriteStartWhere();
writer.WriteColumn("p", "Age", null);
writer.WriteOperator(SqlBinaryOperator.GreaterThanOrEqual);
writer.WriteValue(30);
writer.WriteEndSelect();
}
Assert.AreEqual("SELECT [u].[Id], [u].[Name], [p].[Age] FROM [Users] [u] INNER JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId]) UNION SELECT [u].[Id], [u].[Name], [p].[Age] FROM [Users] [u] INNER JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId]) WHERE ([p].[Age] >= 30)", builder.ToString());
}
/// <inheritdoc/>
protected override void WriteFragmentForSelect(SqlWriter writer, ISqlFragment fragment)
{
var select = (SelectFragment)fragment;
writer.Enter(delegate()
{
if (select.Skip > 0)
{
var rownumber = select.Members.Select(a => a.OutputName).Unique("RowIndex");;
if (select.Take > 0)
{
WriteFragmentForSelectTake(writer, select, rownumber, delegate()
{
WriteFragmentForSelectSkip(writer, select, rownumber, delegate()
{
WriteFragmentForSelectRowNumber(writer, select, rownumber);
});
});
}
else
{
WriteFragmentForSelectSkip(writer, select, rownumber, delegate()
{
WriteFragmentForSelectRowNumber(writer, select, rownumber);
}, true);
}
}
else
{
if (select.Take > 0)
{
var alias = fragment.Context.GetDataSourceAlias();
writer.WriteLine("SELECT ");
writer.Write(alias);
writer.WriteLine(".* FROM (");
WriteFragmentForSelectSimple(writer, select);
writer.WriteLine();
writer.Write(") ");
writer.Write(alias);
writer.Write(" WHERE ROWNUM <= ");
writer.Write(select.Take);
}
else
{
WriteFragmentForSelectSimple(writer, select);
}
}
}, select);
}
/// <inheritdoc/>
protected override void WriteFragmentForUpdate(SqlWriter writer, ISqlFragment fragment)
{
var update = (UpdateFragment)fragment;
writer.Enter(delegate()
{
writer.Write("UPDATE ");
if (update.Sources.Contains(update.Target) ||
update.Sources.OfType <InheritFragment>().Any(a => a.Tables.Contains(update.Target)))
{
update.Sources.ForEach(() => writer.WriteLine(),
source => WriteFragmentForSource(writer, source));
}
else
{
update.Target.WriteSql(writer);
writer.Write(" AS ");
writer.Write(update.Target.AliasName);
if (update.Sources.Any())
{
writer.Write(" CROSS JOIN ");
update.Sources.ForEach(() => writer.WriteLine(),
source => WriteFragmentForSource(writer, source));
}
}
writer.WriteLine();
writer.Write("SET ");
var target = update.Target;
var values = update.Values.ToArray();
for (int i = 0; i < update.Members.Count; i++)
{
var member = update.Members[i];
var value = values[i];
if (i > 0)
{
writer.Write(", ");
}
member.WriteSql(writer);
writer.Write(" = ");
value.WriteSql(writer);
}
if (update.Where != null)
{
writer.WriteLine();
writer.Write("WHERE ");
update.Where.WriteSql(writer);
}
}, update);
}
public override bool Delete(object toDelete)
{
try
{
List <SqlStringBuilder> sqls = SqlWriter.GetDeleteStatements(toDelete, GetBaseType(toDelete.GetType()));
sqls.Each(sql => Execute(sql));
return(true);
}
catch (Exception ex)
{
string value = toDelete == null ? "null" : toDelete.ToString();
Logger.AddEntry("Exception occurred on delete of {0}: {1}", ex, value, ex.Message);
return(false);
}
}
public string BuildTableUpdate(DatabaseTable databaseTable)
{
StringBuilder sb = new StringBuilder();
var sqlWriter = new SqlWriter(databaseTable, m_sqlType);
try
{
sb.Append(sqlWriter.UpdateSql());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
public string BuildTableInsert(DatabaseTable databaseTable)
{
StringBuilder sb = new StringBuilder();
var sqlWriter = new SqlWriter(databaseTable, m_sqlType);
try
{
sb.Append(sqlWriter.InsertSqlWithoutOutputParameter());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
/// <inheritdoc/>
protected override void WriteFragmentForSourceCondition(SqlWriter writer, ISourceFragment source)
{
if (source.Condition != null)
{
if (source is InheritFragment && source.Join.Value == EJoinType.InnerJoin)
{
writer.Write(" AND ");
}
else
{
writer.Write(" ON ");
}
source.Condition.WriteSql(writer);
}
}
public void WriteColumn_WhenSelectStatement_WritesColumns()
{
StringBuilder builder = new StringBuilder();
using (SqlWriter writer = new SqlWriter(builder))
{
writer.WriteStartSelect();
writer.WriteColumn("Id");
writer.WriteColumn("Name", "n");
writer.WriteColumn("Users", "Email", "e");
Assert.AreEqual(SqlWriterState.Select, writer.WriteState);
}
Assert.AreEqual("SELECT [Id], [Name] AS [n], [Users].[Email] AS [e]", builder.ToString());
}
public void TestGeneratedSqlForInsert()
{
//arrange
var table = LoadCountryFromSakila();
var writer = new SqlWriter(table, SqlType.MySql);
//MySQL can only use output parameters with sprocs.
var sql = writer.InsertSqlWithoutOutputParameter();
Console.WriteLine(sql);
int identity;
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
con.Open();
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Transaction = transaction;
foreach (var column in table.Columns)
{
if (column.IsAutoNumber)
{
continue;
}
var par = cmd.CreateParameter();
par.ParameterName = writer.ParameterName(column.Name);
object value = DummyDataCreator.CreateData(column);
par.Value = value ?? DBNull.Value;
cmd.Parameters.Add(par);
}
identity = Convert.ToInt32(cmd.ExecuteScalar());
//if using a sproc
//identity = (int)cmd.Parameters[identityParameterName].Value;
}
//explicit rollback. If we errored, implicit rollback.
transaction.Rollback();
}
}
//assert
Assert.AreNotEqual(0, identity);
}
public override void Visit(DbConstantExpression expression)
{
if (generateParameters)
{
var parameter = CreateParameter(expression.Value, expression.ResultType);
commandText.Append(parameter.ParameterName);
}
else
{
using (var writer = new SqlWriter(commandText))
{
var sqlGenerator = new SqlGenerator();
sqlGenerator.WriteSql(writer, expression.Accept(sqlGenerator));
}
}
}
//写入带精度的数据类型。
private void WriteDbDataTypeForScale(SqlWriter writer, ColumnMetadata column, Type type, string name)
{
var precision = column.GetProperty <PrecisionAttribute>();
if (precision != null)
{
writer.Write(name);
writer.Write('(');
writer.Write(precision.Scale);
writer.Write(')');
}
else
{
WriteDbDataType(writer, type);
}
}
/// <inheritdoc/>
protected override void WriteFragmentForBlock(SqlWriter writer, ISqlFragment fragment)
{
writer.WriteLine("BEGIN");
var block = (BlockFragment)fragment;
block.ForEach(writer.WriteLine, a =>
{
a.WriteSql(writer);
if (a.HasTerminator)
{
writer.Write(';');
}
});
writer.WriteLine();
writer.Write("END;");
}
public void WriteSortOrder_WithoutWriteAscendingSortOrder_WritesSortOrder()
{
var sortOrders = new[] { SqlSortOrder.Ascending, SqlSortOrder.Descending };
var expected = new[] { "", "DESC" };
for (int index = 0; index < sortOrders.Length; index++)
{
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
writer.WriteSortOrder(sortOrders[index]);
}
Assert.Equal(expected[index], builder.ToString());
}
}
public void WriteOperator_WritesOperator()
{
var operators = new[] { SqlBinaryOperator.And, SqlBinaryOperator.Equal, SqlBinaryOperator.GreaterThan, SqlBinaryOperator.GreaterThanOrEqual, SqlBinaryOperator.LessThan, SqlBinaryOperator.LessThanOrEqual, SqlBinaryOperator.Like, SqlBinaryOperator.NotEqual, SqlBinaryOperator.Or, SqlBinaryOperator.In };
var expected = new[] { "AND", "=", ">", ">=", "<", "<=", "LIKE", "<>", "OR", "IN" };
for (int index = 0; index < operators.Length; index++)
{
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
writer.WriteOperator(operators[index]);
}
Assert.Equal(expected[index], builder.ToString());
}
}
/// <summary>
/// Initializes a new instance of the <see cref="InsertWriter" /> class. The databaseTable must have dataTypes (call DataReader.DataTypes()). Use this with <see cref="InsertWriter.WriteInsert(IDataRecord)"/>
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <param name="sqlType">Type of the SQL.</param>
/// <exception cref="System.ArgumentNullException">databaseTable</exception>
public InsertWriter(DatabaseTable databaseTable, SqlType sqlType)
{
if (databaseTable == null)
{
throw new ArgumentNullException("databaseTable");
}
_databaseTable = databaseTable;
PrepareTypes();
_sqlType = sqlType;
_sqlWriter = new SqlWriter(_databaseTable, sqlType);
_converter = new Converter(sqlType, _dateTypes);
PrepareTemplate();
}
public DataTable Read()
{
var sqlType = FindSqlType();
var originSql = new SqlWriter(_databaseTable, sqlType);
var selectAll = originSql.SelectPageSql();
var dt = new DataTable(_databaseTable.Name)
{
Locale = CultureInfo.InvariantCulture
};
var dbFactory = DbProviderFactories.GetFactory(_providerName);
using (var con = dbFactory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = selectAll;
cmd.CommandTimeout = 6000;
var p = cmd.CreateParameter();
var parameterName = "currentPage";
if (sqlType == SqlType.SqlServerCe)
{
parameterName = "offset";
}
p.ParameterName = parameterName;
p.Value = 1;
if (sqlType == SqlType.SqlServerCe)
{
p.Value = 0;
}
cmd.Parameters.Add(p);
var ps = cmd.CreateParameter();
ps.ParameterName = "pageSize";
ps.Value = _pageSize;
cmd.Parameters.Add(ps);
using (var da = dbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}
return(dt);
}
/// <summary>
/// Write out the TOP part of sql select statement
/// It basically writes LIMIT (X).
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
writer.Write(" LIMIT ");
this.TopCount.WriteSql(writer, sqlGenerator);
if (this.WithTies)
{
throw new NotSupportedException("WITH TIES");
}
//writer.Write(" ");
//if (this.WithTies)
//{
// writer.Write("WITH TIES ");
//}
}
private void WriteFragmentForSelectRowNumber(SqlWriter writer, SelectFragment select, string rownumber)
{
writer.Write("SELECT");
if (select.Distinct)
{
writer.Write(" DISTINCT");
}
writer.WriteLine();
WriteFragmentForSelectMembers(writer, select.Members);
writer.Write(", ROW_NUMBER() OVER ( ");
WriteFragmentForOrderBy(writer, select.Sorts);
writer.Write(" ) ");
writer.Write(rownumber);
WriteFragmentForFrom(writer, select.Sources);
WriteFragmentForWhere(writer, select.Where);
WriteFragmentForGroupBy(writer, select.GroupBys);
}
/// <inheritdoc/>
protected override void WriteFragmentForCreateTemporaryTable(SqlWriter writer, ISqlFragment fragment)
{
var content = (CreateTempTableFragment)fragment;
if (content.IsVariable)
{
throw new NotSupportedException(Res.NotSupportedWriteTableVariable);
}
var create = (CreateTempTableFragment)fragment;
writer.Write($"CREATE GLOBAL TEMPORARY TABLE ");
create.Table.WriteSql(writer);
writer.WriteLine("(");
create.Members.ForEach(() => writer.WriteLine(", "),
m => m.WriteSql(writer));
writer.Write(')');
}
private void WriteFragmentForObjectExsit(SqlWriter writer, ISqlFragment fragment)
{
var exist = (ObjectExsitFragment)fragment;
writer.Write("SELECT COUNT(1) FROM RDB$RELATIONS ");
switch (exist.Kind)
{
case EDatabaseObject.Table: writer.Write("WHERE RDB$VIEW_BLR IS NULL AND (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)"); break;
case EDatabaseObject.View: writer.Write("WHERE RDB$VIEW_BLR IS NOT NULL AND (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)"); break;
default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, exist.Kind));
}
writer.Write(" AND RDB$RELATION_NAME='");
writer.Write(exist.Name.Name);
writer.Write('\'');
}
private void WriteFragmentForObjectExsit(SqlWriter writer, ISqlFragment fragment)
{
var exist = (ObjectExsitFragment)fragment;
writer.Write("SELECT CASE WHEN EXISTS (");
switch (exist.Kind)
{
case EDatabaseObject.Table:
writer.Write("SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_TYPE='TABLE' AND t.TABLE_NAME='");
writer.Write(exist.Name.Name);
writer.Write('\'');
break;
default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, exist.Kind));
}
writer.Write(") THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END");
}
/// <inheritdoc/>
protected override void WriteFragmentForRenameObject(SqlWriter writer, ISqlFragment fragment)
{
var content = (RenameObjectFragment)fragment;
writer.Write("ALTER ");
switch (content.Kind)
{
case EDatabaseObject.Table: writer.Write("TABLE "); break;
case EDatabaseObject.View: writer.Write("VIEW "); break;
default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, content.Kind));
}
content.Name.WriteSql(writer);
writer.Write(" RENAME TO ");
WriteDbName(writer, content.NewName);
}
private void WriteFragmentForObjectExsit(SqlWriter writer, ISqlFragment fragment)
{
var exist = (ObjectExsitFragment)fragment;
writer.Write("SELECT COUNT(1) FROM MSysObjects Where ");
switch (exist.Kind)
{
case EDatabaseObject.Table: writer.Write("Type=1"); break;
case EDatabaseObject.View: writer.Write("Type=5"); break;
default: throw new NotSupportedException(string.Format(Res.NotSupportedWriteDatabaseObject, exist.Kind));
}
writer.Write(" AND Name='");
writer.Write(exist.Name.Name);
writer.Write('\'');
}
private void WriteFragmentForCreateRelation(SqlWriter writer, ISqlFragment fragment)
{
var relation = (CreateRelationFragment)fragment;
writer.Write("ALTER TABLE ");
relation.Foreign.WriteSql(writer);
writer.Write(" ADD CONSTRAINT ");
WriteDbName(writer, relation.Name);
writer.Write(" FOREIGN KEY(");
relation.ForeignKeys.ForEach(() => writer.Write(','), a => WriteDbName(writer, a));
writer.Write(") REFERENCES ");
relation.Principal.WriteSql(writer);
writer.Write(" (");
relation.PrincipalKeys.ForEach(() => writer.Write(','), a => WriteDbName(writer, a));
writer.Write(')');
}
/// <inheritdoc/>
protected override void WriteFragmentForInsertValue(SqlWriter writer, ISqlFragment fragment)
{
var insert = (InsertValueFragment)fragment;
writer.Write("INSERT INTO ");
insert.Target.WriteSql(writer);
writer.WriteLine();
writer.Write('(');
insert.Members.ForEach(
() => writer.Write(","),
column => this.WriteDbName(writer, column.OutputName));
writer.WriteLine(")");
writer.Write("VALUES");
writer.Write('(');
insert.Values.ForEach(() => writer.Write(","), val => val.WriteSql(writer));
writer.Write(')');
}
public void TestGeneratedSqlForInsert()
{
//arrange
var table = LoadRegionsFromHr();
var writer = new SqlWriter(table, SqlType.Oracle);
var sql = writer.InsertSql();
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = ConnectionString;
con.Open();
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Transaction = transaction;
foreach (var column in table.Columns)
{
var par = cmd.CreateParameter();
par.ParameterName = writer.ParameterName(column.Name);
if (column.IsAutoNumber)
{
//we could be using sequences here
par.Direction = ParameterDirection.Output;
par.DbType = DbType.Int32;
}
else
{
object value = DummyDataCreator.CreateData(column);
par.Value = value ?? DBNull.Value;
}
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
//explicit rollback. If we errored, implicit rollback.
transaction.Rollback();
}
}
//assert
}
static void WriteColumns(SqlWriter writer, IEnumerable <string> columns, bool separateLines = false)
{
var separator = (string)null;
foreach (var column in columns)
{
if (separator != null)
{
writer.Write(separator);
if (separateLines)
{
writer.WriteLine();
}
}
writer.Write(column);
separator = ", ";
}
}
public void Read(Func <IDataRecord, bool> processRecord)
{
if (processRecord == null)
{
return;
}
var sqlType = FindSqlType(_providerName);
var originSql = new SqlWriter(_databaseTable, sqlType);
var sql = originSql.SelectAllSql();
var dbFactory = DbProviderFactories.GetFactory(_providerName);
using (var connection = dbFactory.CreateConnection())
{
connection.ConnectionString = _connectionString;
RunCommand(connection, processRecord, sql);
}
}
