/// <summary>
/// Returns the translated T-SQL script. For testing only.
/// </summary>
/// <returns>The translated T-SQL script</returns>
internal string GetTsqlQuery()
{
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
// Translation and Check CheckInvisibleColumn
using (SqlTransaction tx = Connection.BeginTransaction())
{
var visitor = new TranslateMatchClauseVisitor(tx);
visitor.Invoke(script);
return script.ToString();
}
}
/// <summary>
/// Create procedure and related metadata.
/// </summary>
/// <param name="sqlStr"> A create procedure statement with metadata.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the create procedure will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool CreateProcedure(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of CREATE Procedure command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
// Translation
var modVisitor = new TranslateDataModificationVisitor(Conn);
modVisitor.Invoke(script);
var matchVisitor = new TranslateMatchClauseVisitor(Conn);
matchVisitor.Invoke(script);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WCreateProcedureStatement;
if (statement == null)
return false;
var procName = statement.ProcedureReference.Name;
if (procName.SchemaIdentifier == null)
procName.Identifiers.Insert(0, new Identifier {Value = "dbo"});
bool exists = false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
using (var cmd = Conn.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandText = script.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format(
@"SELECT ProcID FROM {0} WHERE ProcName = @procName AND ProcSchema = @procSchema",
MetadataTables[4]);
cmd.Parameters.AddWithValue("@procName", procName.BaseIdentifier.Value);
cmd.Parameters.AddWithValue("@procSchema", procName.SchemaIdentifier.Value);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
exists = true;
}
if (!exists)
{
cmd.CommandText = string.Format(@"
INSERT INTO [{0}]([ProcSchema], [ProcName]) VALUES (@schema, @name)", MetadataTables[4]);
cmd.Parameters.AddWithValue("@schema", procName.SchemaIdentifier.Value);
cmd.Parameters.AddWithValue("@name", procName.BaseIdentifier.Value);
cmd.ExecuteNonQuery();
}
if (externalTransaction == null)
{
tran.Commit();
}
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when creating the procedure.", e);
}
return true;
}
public int ExecuteNonQuery()
{
try
{
if (CommandType == CommandType.StoredProcedure)
{
if (Tx != null)
{
Command.Transaction = Tx;
}
Command.CommandText = CommandText;
return Command.ExecuteNonQuery();
}
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
bool externalTransaction = true;
if (Tx == null)
{
externalTransaction = false;
Tx = Connection.BeginTransaction();
}
// Translation
var modVisitor = new TranslateDataModificationVisitor(Tx);
modVisitor.Invoke(script);
var matchVisitor = new TranslateMatchClauseVisitor(Tx);
matchVisitor.Invoke(script);
Command.CommandText = script.ToString();
Command.Transaction = Tx;
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
#endif
int res = Command.ExecuteNonQuery();
if (!externalTransaction)
{
Tx.Commit();
Tx.Dispose();
Tx = null;
}
return res;
}
catch (SqlException e)
{
throw new SqlExecutionException("An error occurred when executing the query", e);
}
}
public SqlDataReader ExecuteReader()
{
try
{
if (CommandType == CommandType.StoredProcedure)
{
if (Tx != null)
{
Command.Transaction = Tx;
}
Command.CommandText = CommandText;
return Command.ExecuteReader();
}
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (Tx == null)
{
using(SqlConnection translationConnection = new SqlConnection(Connection.Conn.ConnectionString))
{
translationConnection.Open();
using (SqlTransaction translationTx = translationConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
{
var visitor = new TranslateMatchClauseVisitor(translationTx);
visitor.Invoke(script);
// Executes translated SQL
Command.CommandText = script.ToString();
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
//throw new GraphViewException("No Execution");
#endif
var reader = Command.ExecuteReader();
translationTx.Commit();
return reader;
}
}
}
else
{
var visitor = new TranslateMatchClauseVisitor(Tx);
visitor.Invoke(script);
// Executes translated SQL
Command.CommandText = script.ToString();
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
//throw new GraphViewException("No Execution");
#endif
var reader = Command.ExecuteReader();
return reader;
}
}
catch (SqlException e)
{
throw new SqlExecutionException("An error occurred when executing the query", e);
}
}