public void CreateMySqlCommand()
{
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = "SELECT * FROM Categories ORDER BY CategoryID";
myCommand.CommandTimeout = 15;
myCommand.CommandType = CommandType.Text;
}
Can I use Sql Server functions in myCommand.CommandText and why?
If you mean, SQL Server user defined functions. Then, yes; you can use it normally like:
myCommand.CommandText = "SELECT fn_Yourfunctionname(@parameternames)";
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.Add(new SqlParameter("@parameternames", ...
The reason it works is because this is the way that functions are called in SQL Server directly.
Another approach:
public T ExecuteScalarFunction<T>(string functionName, List<SqlParameter> parameters, SqlDbType returnSqlType) where T : new()
{
using (var conn = new SqlConnection(CTX.Database.GetDbConnection().ConnectionString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
using (var cmd = new SqlCommand(functionName, conn)) {
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.Parameters.Add("@RETURN_VALUE", returnSqlType).Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
return (T)cmd.Parameters["@RETURN_VALUE"].Value;
}
}
}
