/// <summary>
/// Sets the expected column types for a given function command tree
/// </summary>
private void SetFunctionExpectedTypes(DbFunctionCommandTree tree, EFMySqlCommand cmd)
{
if (tree.ResultType != null)
{
Debug.Assert(tree.ResultType.EdmType.BuiltInTypeKind == BuiltInTypeKind.CollectionType,
Resources.WrongFunctionResultType);
CollectionType collectionType = (CollectionType)(tree.ResultType.EdmType);
EdmType elementType = collectionType.TypeUsage.EdmType;
if (elementType.BuiltInTypeKind == BuiltInTypeKind.RowType)
{
ReadOnlyMetadataCollection <EdmMember> members = ((RowType)elementType).Members;
cmd.ColumnTypes = new PrimitiveType[members.Count];
for (int ordinal = 0; ordinal < members.Count; ordinal++)
{
EdmMember member = members[ordinal];
PrimitiveType primitiveType = (PrimitiveType)member.TypeUsage.EdmType;
cmd.ColumnTypes[ordinal] = primitiveType;
}
}
else if (elementType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType)
{
cmd.ColumnTypes = new PrimitiveType[1];
cmd.ColumnTypes[0] = (PrimitiveType)elementType;
}
else
{
Debug.Fail(Resources.WrongFunctionResultType);
}
}
}
public async Task ExecuteNonQueryAndScalarAsyncAwait()
{
if (st.Version < new Version(5, 0))
{
return;
}
st.execSQL("CREATE TABLE NonQueryAndScalarAsyncAwaitTest (id int)");
st.execSQL("CREATE PROCEDURE NonQueryAndScalarAsyncAwaitSpTest() BEGIN SET @x=0; REPEAT INSERT INTO NonQueryAndScalarAsyncAwaitTest VALUES(@x); " +
"SET @x=@x+1; UNTIL @x = 100 END REPEAT; END");
EFMySqlCommand proc = new EFMySqlCommand()
{
CommandText = "NonQueryAndScalarAsyncAwaitSpTest", Connection = st.conn
};
proc.CommandType = CommandType.StoredProcedure;
int result = await proc.ExecuteNonQueryAsync();
Assert.NotEqual(-1, result);
EFMySqlCommand cmd = new EFMySqlCommand()
{
CommandText = "SELECT COUNT(*) FROM NonQueryAndScalarAsyncAwaitTest;", Connection = st.conn
};
cmd.CommandType = CommandType.Text;
object cnt = await cmd.ExecuteScalarAsync();
Assert.Equal(100, Convert.ToInt32(cnt));
}
/// <summary>
/// Sets the expected column types
/// </summary>
private void SetExpectedTypes(DbCommandTree commandTree, EFMySqlCommand cmd)
{
if (commandTree is DbQueryCommandTree)
SetQueryExpectedTypes(commandTree as DbQueryCommandTree, cmd);
else if (commandTree is DbFunctionCommandTree)
SetFunctionExpectedTypes(commandTree as DbFunctionCommandTree, cmd);
}
public void ExecuteNonQueryAndScalarAsync()
{
if (st.Version < new Version(5, 0))
{
return;
}
st.execSQL("CREATE TABLE test (id int)");
st.execSQL("DROP PROCEDURE IF EXISTS spTest");
st.execSQL("CREATE PROCEDURE spTest() BEGIN SET @x=0; REPEAT INSERT INTO test VALUES(@x); " +
"SET @x=@x+1; UNTIL @x = 100 END REPEAT; END");
EFMySqlCommand proc = new EFMySqlCommand()
{
CommandText = "spTest", Connection = st.conn
};
proc.CommandType = CommandType.StoredProcedure;
System.Threading.Tasks.Task <int> result = proc.ExecuteNonQueryAsync();
Assert.NotEqual(-1, result.Result);
EFMySqlCommand cmd = new EFMySqlCommand()
{
CommandText = "SELECT COUNT(*) FROM test;", Connection = st.conn
};
cmd.CommandType = CommandType.Text;
object cnt = cmd.ExecuteScalarAsync().Result;
Assert.Equal(100, Convert.ToInt32(cnt));
}
protected override DbCommandDefinition CreateDbCommandDefinition(
DbProviderManifest providerManifest, DbCommandTree commandTree)
{
if (commandTree == null)
throw new ArgumentNullException("commandTree");
SqlGenerator generator = null;
if (commandTree is DbQueryCommandTree)
generator = new SelectGenerator();
else if (commandTree is DbInsertCommandTree)
generator = new InsertGenerator();
else if (commandTree is DbUpdateCommandTree)
generator = new UpdateGenerator();
else if (commandTree is DbDeleteCommandTree)
generator = new DeleteGenerator();
else if (commandTree is DbFunctionCommandTree)
generator = new FunctionGenerator();
string sql = generator.GenerateSQL(commandTree);
EFMySqlCommand cmd = new EFMySqlCommand();
cmd.CommandText = sql;
if (generator is FunctionGenerator)
cmd.CommandType = (generator as FunctionGenerator).CommandType;
SetExpectedTypes(commandTree, cmd);
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
function = (commandTree as DbFunctionCommandTree).EdmFunction;
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair<string, TypeUsage> queryParameter in commandTree.Parameters)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = queryParameter.Key;
parameter.Direction = ParameterDirection.Input;
parameter.DbType = Metadata.GetDbType(queryParameter.Value);
FunctionParameter funcParam;
if (function != null &&
function.Parameters.TryGetValue(queryParameter.Key, false, out funcParam))
{
parameter.ParameterName = funcParam.Name;
parameter.Direction = Metadata.ModeToDirection(funcParam.Mode);
parameter.DbType = Metadata.GetDbType(funcParam.TypeUsage);
}
cmd.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen
foreach (DbParameter p in generator.Parameters)
cmd.Parameters.Add(p);
return CreateCommandDefinition(cmd);
}
public async Task PrepareAsyncAwait()
{
st.execSQL("CREATE TABLE PrepareAsyncAwaitTest (val1 varchar(20), numbercol int, numbername varchar(50));");
EFMySqlCommand cmd = new EFMySqlCommand()
{
CommandText = "INSERT INTO PrepareAsyncAwaitTest VALUES(NULL, @number, @text)", Connection = st.conn
};
await cmd.PrepareAsync();
cmd.Parameters.Add(new MySqlParameter("@number", 1));
cmd.Parameters.Add(new MySqlParameter("@text", "One"));
for (int i = 1; i <= 100; i++)
{
cmd.Parameters["@number"].Value = i;
cmd.Parameters["@text"].Value = "A string value";
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Sets the expected column types for a given query command tree
/// </summary>
private void SetQueryExpectedTypes(DbQueryCommandTree tree, EFMySqlCommand cmd)
{
DbProjectExpression projectExpression = tree.Query as DbProjectExpression;
if (projectExpression != null)
{
EdmType resultsType = projectExpression.Projection.ResultType.EdmType;
StructuralType resultsAsStructuralType = resultsType as StructuralType;
if (resultsAsStructuralType != null)
{
cmd.ColumnTypes = new PrimitiveType[resultsAsStructuralType.Members.Count];
for (int ordinal = 0; ordinal < resultsAsStructuralType.Members.Count; ordinal++)
{
EdmMember member = resultsAsStructuralType.Members[ordinal];
PrimitiveType primitiveType = member.TypeUsage.EdmType as PrimitiveType;
cmd.ColumnTypes[ordinal] = primitiveType;
}
}
}
}
protected override System.Data.Entity.Core.Common.DbCommandDefinition CreateDbCommandDefinition(
System.Data.Entity.Core.Common.DbProviderManifest providerManifest, DbCommandTree commandTree)
{
if (commandTree == null)
{
throw new ArgumentNullException("commandTree");
}
SqlGenerator generator = null;
if (commandTree is DbQueryCommandTree)
{
generator = new SelectGenerator();
}
else if (commandTree is DbInsertCommandTree)
{
generator = new InsertGenerator();
}
else if (commandTree is DbUpdateCommandTree)
{
generator = new UpdateGenerator();
}
else if (commandTree is DbDeleteCommandTree)
{
generator = new DeleteGenerator();
}
else if (commandTree is DbFunctionCommandTree)
{
generator = new FunctionGenerator();
}
string sql = generator.GenerateSQL(commandTree);
EFMySqlCommand cmd = new EFMySqlCommand();
cmd.CommandText = sql;
if (generator is FunctionGenerator)
{
cmd.CommandType = (generator as FunctionGenerator).CommandType;
}
SetExpectedTypes(commandTree, cmd);
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = (commandTree as DbFunctionCommandTree).EdmFunction;
}
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair <string, TypeUsage> queryParameter in commandTree.Parameters)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = queryParameter.Key;
parameter.Direction = ParameterDirection.Input;
parameter.DbType = Metadata.GetDbType(queryParameter.Value);
#if NET_45_OR_GREATER
if (queryParameter.Value.EdmType is PrimitiveType &&
((PrimitiveType)queryParameter.Value.EdmType).PrimitiveTypeKind == PrimitiveTypeKind.Geometry)
{
((MySqlParameter)parameter).MySqlDbType = MySqlDbType.Geometry;
}
#endif
FunctionParameter funcParam;
if (function != null &&
function.Parameters.TryGetValue(queryParameter.Key, false, out funcParam))
{
parameter.ParameterName = funcParam.Name;
parameter.Direction = Metadata.ModeToDirection(funcParam.Mode);
parameter.DbType = Metadata.GetDbType(funcParam.TypeUsage);
}
cmd.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen
foreach (DbParameter p in generator.Parameters)
{
cmd.Parameters.Add(p);
}
return(CreateCommandDefinition(cmd));
}
