private static void SyncParameterProperties(
EntityParameter entityParameter,
DbParameter storeParameter,
DbProviderServices storeProviderServices)
{
IDbDataParameter dbDataParameter = (IDbDataParameter)storeParameter;
TypeUsage typeUsageForScalar = TypeHelpers.GetPrimitiveTypeUsageForScalar(entityParameter.GetTypeUsage());
storeProviderServices.SetParameterValue(storeParameter, typeUsageForScalar, entityParameter.Value);
if (entityParameter.IsDirectionSpecified)
{
storeParameter.Direction = entityParameter.Direction;
}
if (entityParameter.IsIsNullableSpecified)
{
storeParameter.IsNullable = entityParameter.IsNullable;
}
if (entityParameter.IsSizeSpecified)
{
storeParameter.Size = entityParameter.Size;
}
if (entityParameter.IsPrecisionSpecified)
{
dbDataParameter.Precision = entityParameter.Precision;
}
if (!entityParameter.IsScaleSpecified)
{
return;
}
dbDataParameter.Scale = entityParameter.Scale;
}
static public void StoredProcWithEntityCommand()
{
//<snippetStoredProcWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=SchoolEntities"))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SchoolEntities.GetStudentGrades";
cmd.CommandType = CommandType.StoredProcedure;
EntityParameter param = new EntityParameter();
param.Value = 2;
param.ParameterName = "StudentID";
cmd.Parameters.Add(param);
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Read the results returned by the stored procedure.
while (rdr.Read())
{
Console.WriteLine("ID: {0} Grade: {1}", rdr["StudentID"], rdr["Grade"]);
}
}
}
conn.Close();
}
//</snippetStoredProcWithEntityCommand>
}
private EntityParameter CreateEntityParameter(XmlNode element)
{
EntityParameter parameter = new EntityParameter();
if (element.Attributes["xmi:id"] != null)
{
parameter.Id = element.Attributes["xmi:id"].Value;
}
if (element.Attributes["name"] != null)
{
parameter.Name = element.Attributes["name"].Value;
}
if (element.Attributes["direction"] != null)
{
string kind = element.Attributes["direction"].Value.ToLower();
if (kind.CompareTo("return") == 0)
{
parameter.IsReturn = true;
}
else
{
if (kind.CompareTo("inout") != 0)
{
parameter.Modifier = kind;
}
}
}
return(parameter);
}
static public void Transactions()
{
//<snippetTransactionsWithEntityClient>
using (EntityConnection con = new EntityConnection("name=AdventureWorksEntities"))
{
con.Open();
EntityTransaction transaction = con.BeginTransaction();
DbCommand cmd = con.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = @"SELECT VALUE Contact FROM AdventureWorksEntities.Contacts
AS Contact WHERE Contact.LastName = @ln";
EntityParameter param = new EntityParameter();
param.ParameterName = "ln";
param.Value = "Adams";
cmd.Parameters.Add(param);
using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Iterate through the collection of Contact items.
while (rdr.Read())
{
Console.Write("First Name: " + rdr["FirstName"]);
Console.WriteLine("\tLast Name: " + rdr["LastName"]);
}
}
transaction.Commit();
}
//</snippetTransactionsWithEntityClient>
}
public void Select_desc_skip_limit_with_params()
{
var query = @"
select o.Id
from ArubaContext.Owners as o
order by o.Id desc skip @pInt16 LIMIT @pInt64";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
var prm2 = new EntityParameter("pInt64", DbType.Int64);
prm1.Value = 5;
prm2.Value = 2;
// verifying that there are 2 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var db2 = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, null, prm1, prm2))
{
var expectedResults = db.Owners.ToList().OrderByDescending(o => o.Id).Skip(5).Take(2).Select(o => o.Id).ToList();
Assert.Equal(expectedResults.Count, 2);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
}
}
public void Select_desc_skip_limit_with_params_and_literal()
{
var query = @"
select o.Id
from ArubaContext.Owners as o
order by o.Id desc skip @pInt16 LIMIT 5";
var expectedSql = @"
SELECT TOP (5)
[Extent1].[Id] AS [Id]
FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > @pInt16
ORDER BY [Extent1].[Id] DESC";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
prm1.Value = 5;
// verifying that there are 5 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql, prm1))
{
VerifySortDescAndCountInt(reader, 5);
}
}
}
public void Select_top_nested_with_params()
{
var query = @"
SELECT TOP (@pInt64) C.OwnerId
FROM (
SELECT TOP (@pInt16) o.Id AS OwnerId
FROM ArubaContext.Owners as o
ORDER BY o.Id
) AS C
ORDER BY C.OwnerId DESC";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
var prm2 = new EntityParameter("pInt64", DbType.Int64);
prm1.Value = 5;
prm2.Value = 2;
// verifying that there are 2 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, null, prm1, prm2))
{
VerifySortDescAndCountInt(reader, 2);
}
}
}
public void Select_top_nested_asc_and_desc_with_params()
{
var query = @"
SELECT TOP (@pInt64) C.OwnerId
FROM (
SELECT TOP (@pInt16) o.Id AS OwnerId
FROM ArubaContext.Owners as o
ORDER BY o.Id ASC
) AS C
ORDER BY C.OwnerId DESC";
string expectedSql = @"
SELECT TOP (@pInt64)
[Limit1].[Id] AS [Id]
FROM ( SELECT TOP (@pInt16) [Extent1].[Id] AS [Id]
FROM [dbo].[ArubaOwners] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
) AS [Limit1]
ORDER BY [Limit1].[Id] DESC";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
var prm2 = new EntityParameter("pInt64", DbType.Int64);
prm1.Value = 5;
prm2.Value = 3;
// verifying that there are 3 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql, prm1, prm2))
{
VerifySortDescAndCountInt(reader, 3);
}
}
}
/// <summary>
/// Given an entity command, store provider command and a connection, sets all output parameter values on the entity command.
/// The connection is used to determine how to map spatial values.
/// </summary>
/// <param name="entityCommand">Entity command on which to set parameter values. Must not be null.</param>
/// <param name="storeProviderCommand">Store provider command from which to retrieve parameter values. Must not
/// be null.</param>
/// <param name="connection">The connection on which the command was run. Must not be null</param>
internal static void SetEntityParameterValues(EntityCommand entityCommand, DbCommand storeProviderCommand, EntityConnection connection)
{
Debug.Assert(null != entityCommand);
Debug.Assert(null != storeProviderCommand);
Debug.Assert(null != connection);
foreach (DbParameter storeParameter in storeProviderCommand.Parameters)
{
ParameterDirection direction = storeParameter.Direction;
if (0 != (direction & ParameterDirection.Output))
{
// if the entity command also defines the parameter, propagate store parameter value
// to entity parameter
int parameterOrdinal = entityCommand.Parameters.IndexOf(storeParameter.ParameterName);
if (0 <= parameterOrdinal)
{
EntityParameter entityParameter = entityCommand.Parameters[parameterOrdinal];
object parameterValue = storeParameter.Value;
TypeUsage parameterType = entityParameter.GetTypeUsage();
if (Helper.IsSpatialType(parameterType))
{
parameterValue = GetSpatialValueFromProviderValue(parameterValue, (PrimitiveType)parameterType.EdmType, connection);
}
entityParameter.Value = parameterValue;
}
}
}
}
public Int32 checkBollette(Int32 NumBolletta, Int32 QuanteBollette)
{
Int32 retValue = 0;
try
{
using (EntityConnection conn = new EntityConnection(ObjectContext.Connection.ConnectionString))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "BollettariEntities.checkEsBollette";
cmd.CommandType = CommandType.StoredProcedure;
//retValue = 2;
cmd.Parameters.Clear();
//retValue = 3;
EntityParameter param = new EntityParameter("NumBolletta", System.Data.DbType.Int32);
param.Value = NumBolletta;
cmd.Parameters.Add(param);
//retValue = 4;
param = new EntityParameter("QuanteBollette", System.Data.DbType.Int32);
param.Value = QuanteBollette;
cmd.Parameters.Add(param);
//retValue = 5;
param = new EntityParameter("ret", System.Data.DbType.Int32);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
//retValue = 6;
cmd.ExecuteNonQuery();
//retValue = 7;
retValue = Convert.ToInt32(cmd.Parameters[2].Value.ToString());
//retValue = 8;
//retValue = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
//using (EntityDataReader rdr =cmd.ExecuteReader(CommandBehavior.SequentialAccess))
//{
// retValue =Convert.ToInt32 ( cmd.Parameters[2]);
//}
}
conn.Close();
}
}
catch (Exception ex)
{
//retValue = 1000;
//throw new ApplicationException("GeneraBollettario " + ex.Message);
}
//The data reader is incompatible with the specified 'BollettariModel.SP_Result'.
//A member of the type, 'ret', does not have a corresponding column in the data reader with the same name.
return(retValue);
}
private static EntityParameter CreateEntityParameterFromQueryParameter(
KeyValuePair <string, TypeUsage> queryParameter)
{
EntityParameter parameter = new EntityParameter();
parameter.ParameterName = queryParameter.Key;
EntityCommandDefinition.PopulateParameterFromTypeUsage(parameter, queryParameter.Value, false);
return(parameter);
}
protected EntityParameter GetParameterAEFL(DbType ltype, string strParamName, object value = null, ParameterDirection parameterDirection = ParameterDirection.Input)
{
EntityParameter parameterObject = new EntityParameter(strParamName, ltype);
parameterObject.ParameterName = strParamName;
parameterObject.DbType = ltype;
parameterObject.Value = value;
return(parameterObject);
}
async private void button31_Click(object sender, EventArgs e)
{
//其實用方法就可以了
//Dynamic LINQ 讓 LINQ 的世界變的更美好
//nuget system.linq.dynamic
//using system.linq.dynamic
db.Database.Log = Console.WriteLine;
var query =
db.Customers.Where("City == @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("New (CompanyName as Name, Phone)");
Console.WriteLine(query);
var result = await query.ToListAsync();
this.dataGridView1.DataSource = result;
//============================
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create a query that takes two parameters.
string esqlQuery =
@"SELECT VALUE Contact FROM AdventureWorksEntities.Contacts
AS Contact WHERE Contact.LastName = @ln AND
Contact.FirstName = @fn";
using (EntityCommand cmd = new EntityCommand(esqlQuery, conn))
{
// Create two parameters and add them to
// the EntityCommand's Parameters collection
EntityParameter param1 = new EntityParameter();
param1.ParameterName = "ln";
param1.Value = "Adams";
EntityParameter param2 = new EntityParameter();
param2.ParameterName = "fn";
param2.Value = "Frances";
cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Iterate through the collection of Contact items.
while (rdr.Read())
{
this.listBox1.Items.Add(rdr["FirstName"]);
this.listBox1.Items.Add(rdr["LastName"]);
}
}
}
conn.Close();
}
}
// protected EntityParameter GetParameterAEF(DbType ltype, string strParamName, object value = null, ParameterDirection parameterDirection = ParameterDirection.Input)
protected EntityParameter GetParameterAEF(EntityParameter entityParameter)
{
EntityParameter parameterObject = new EntityParameter();
parameterObject = entityParameter;
parameterObject.ParameterName = entityParameter.ParameterName;
// parameterObject.DbType = ltype;
parameterObject.Value = entityParameter.Value;
return(parameterObject);
}
public void Delete(int widgetId)
{
FlushWidgetCache(widgetId);
var param = new EntityParameter("WidgetId", System.Data.DbType.Int32);
param.Value = widgetId;
_database.ExecuteFunction("DeleteWidgetCascade", param);
//_database.Delete<Widget>(new Widget { ID = widgetId });
}
private void button42_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var p = new EntityParameter("p0", DbType.String)
{
Value = 11
};
Dump(ctx.ExecuteStoreQuery <OrderDetailProjection>("Select quantity, unitprice, discount from [OrderDetail] d join [order] o on (d.OrderId = d.OrderId) where o.orderid = @p0", 11));
}
}
private static EntityInterface GetInterface()
{
/** IPerson Definition **/
EntityInterface iPerson = new EntityInterface();
iPerson.Name = "IPerson";
iPerson.Visibility = "public";
EntityMethod getDescription = new EntityMethod();
getDescription.Name = "GetPerson";
EntityParameter paramRet = new EntityParameter();
paramRet.IsReturn = true;
paramRet.Type = IntrinsicTypes.Create("System.String");
EntityParameter param = new EntityParameter();
param.IsReturn = false;
param.Type = IntrinsicTypes.Create("System.Int32");
param.Name = "id";
getDescription.ReturnEntity = paramRet;
List <EntityParameter> parames = new List <EntityParameter>();
parames.Add(param);
getDescription.Parameters = parames;
EntityMethod init = new EntityMethod();
init.Name = "Init";
EntityMethod set = new EntityMethod();
set.Name = "SetData";
EntityParameter param1 = new EntityParameter();
param1.IsReturn = true;
param1.Type = IntrinsicTypes.Create("System.String");
param1.Name = "nome";
EntityParameter param2 = new EntityParameter();
param2.IsReturn = false;
param2.Type = IntrinsicTypes.Create("System.Boolean");
param2.Name = "isMale";
List <EntityParameter> parames2 = new List <EntityParameter>();
parames2.Add(param1);
parames2.Add(param2);
set.Parameters = parames2;
iPerson.Methods.Add(getDescription);
iPerson.Methods.Add(init);
iPerson.Methods.Add(set);
return(iPerson);
}
/// <summary>
/// Constructs a EntityParameter from a CQT parameter.
/// </summary>
/// <param name="queryParameter"> </param>
/// <returns> </returns>
private static EntityParameter CreateEntityParameterFromQueryParameter(KeyValuePair <string, TypeUsage> queryParameter)
{
// We really can't have a parameter here that isn't a scalar type...
Debug.Assert(TypeSemantics.IsScalarType(queryParameter.Value), "Non-scalar type used as query parameter type");
var result = new EntityParameter();
result.ParameterName = queryParameter.Key;
PopulateParameterFromTypeUsage(result, queryParameter.Value, isOutParam: false);
return(result);
}
public Int32 GetLoginUsrPwd(string Usr, string pwd)
{
Int32 retValue = 0;
try
{
using (EntityConnection conn = new EntityConnection(ObjectContext.Connection.ConnectionString))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "BollettariEntities.CheckLogin";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
EntityParameter param = new EntityParameter("user", System.Data.DbType.String);
param.Value = Usr;
cmd.Parameters.Add(param);
param = new EntityParameter("pwd", System.Data.DbType.String);
param.Value = pwd;
cmd.Parameters.Add(param);
param = new EntityParameter("ret", System.Data.DbType.Int32);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
retValue = Convert.ToInt32(cmd.Parameters[2].Value.ToString());
//retValue = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
//using (EntityDataReader rdr =cmd.ExecuteReader(CommandBehavior.SequentialAccess))
//{
// retValue =Convert.ToInt32 ( cmd.Parameters[2]);
//}
}
conn.Close();
}
}
catch (Exception ex)
{
throw new ApplicationException("GetLoginUsrPwd " + ex.Message);
}
//The data reader is incompatible with the specified 'BollettariModel.SP_Result'.
//A member of the type, 'ret', does not have a corresponding column in the data reader with the same name.
return(retValue);
}
/// <summary>Adds a new parameter created from the input specified to the command specified</summary>
/// <param name="cmd">The command to add the new parameter to</param>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="length">The length.</param>
/// <param name="direction">The direction.</param>
/// <param name="value">The value for the parameter</param>
private static void AddParameter(DbCommand cmd, string parameterName, int length, ParameterDirection direction, object value)
{
var dummyParam = new EntityParameter()
{
Value = value
};
var parameter = cmd.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Direction = direction;
parameter.Size = length;
parameter.Value = value;
parameter.DbType = dummyParam.DbType;
cmd.Parameters.Add(parameter);
}
static private void ParameterizedQueryWithEntityCommand()
{
//<snippetParameterizedQueryWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create a query that takes two parameters.
string esqlQuery =
@"SELECT VALUE Contact FROM AdventureWorksEntities.Contact
AS Contact WHERE Contact.LastName = @ln AND
Contact.FirstName = @fn";
try
{
using (EntityCommand cmd = new EntityCommand(esqlQuery, conn))
{
// Create two parameters and add them to
// the EntityCommand's Parameters collection
EntityParameter param1 = new EntityParameter();
param1.ParameterName = "ln";
param1.Value = "Adams";
EntityParameter param2 = new EntityParameter();
param2.ParameterName = "fn";
param2.Value = "Frances";
cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Iterate through the collection of Contact items.
while (rdr.Read())
{
Console.WriteLine(rdr["FirstName"]);
Console.WriteLine(rdr["LastName"]);
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
//</snippetParameterizedQueryWithEntityCommand>
}
static public void ComplexTypeWithEntityCommand()
{
//<snippetComplexTypeWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
string esqlQuery = @"SELECT VALUE contacts FROM
AdventureWorksEntities.Contacts AS contacts
WHERE contacts.ContactID == @id";
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
EntityParameter param = new EntityParameter();
param.ParameterName = "id";
param.Value = 3;
cmd.Parameters.Add(param);
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// The result returned by this query contains
// Address complex Types.
while (rdr.Read())
{
// Display CustomerID
Console.WriteLine("Contact ID: {0}",
rdr["ContactID"]);
// Display Address information.
DbDataRecord nestedRecord =
rdr["EmailPhoneComplexProperty"] as DbDataRecord;
Console.WriteLine("Email and Phone Info:");
for (int i = 0; i < nestedRecord.FieldCount; i++)
{
Console.WriteLine(" " + nestedRecord.GetName(i) +
": " + nestedRecord.GetValue(i));
}
}
}
}
conn.Close();
}
//</snippetComplexTypeWithEntityCommand>
}
public EntityDataReader GetITProducts(List <string> lColumnName, List <string> lValue)
{
string strConn1 = GetConnectionString();
List <int> intList = new List <int>();
EntityConnection conn = new EntityConnection("name=AAB_BANKEntities");
conn.Open();
EntityCommand command = conn.CreateCommand();
command.CommandText = "AAB_BANKEntities.spGET_IT_PRODUCT_ALL";
command.CommandType = CommandType.StoredProcedure;
// EntityCommand command = new EntityCommand();
EntityParameter paramObjITProd = new EntityParameter();
EntityDataReader rdr = command.ExecuteReader(CommandBehavior.SequentialAccess);
if (lColumnName.Count == lValue.Count)
{
foreach (string value in lColumnName)
{
paramObjITProd.ParameterName = lColumnName.ToString();
// paramObjITProd.DbType = Convert.ToString(ltype);
paramObjITProd.Value = lValue;
command.Parameters.Add(paramObjITProd);
}
if (rdr.HasRows)
{
//while (rdr.Read())
//{
// RefTypeVisitRecord(rdr as IExtendedDataRecord);
//}
//var s = rdr.OfType<ITProductsOut>().AsEnumerable();
// return (s.ToList());
}
}
//EntityParameter parameterObject = new EntityParameter(strParamName, ltype);
//parameterObject.ParameterName = strParamName;
//parameterObject.DbType = ltype;
//parameterObject.Value = value;
//return parameterObject;
return(rdr);
conn.Close();
}
/// <summary>
/// Updates storeParameter size, precision and scale properties from user provided parameter properties.
/// </summary>
/// <param name="entityParameter"> </param>
/// <param name="storeParameter"> </param>
private static void SyncParameterProperties(
EntityParameter entityParameter, DbParameter storeParameter, DbProviderServices storeProviderServices)
{
IDbDataParameter dbDataParameter = storeParameter;
// DBType is not currently syncable; it's part of the cache key anyway; this is because we can't guarantee
// that the store provider will honor it -- (SqlClient doesn't...)
//if (entityParameter.IsDbTypeSpecified)
//{
// storeParameter.DbType = entityParameter.DbType;
//}
// Give the store provider the opportunity to set the value before any parameter state has been copied from
// the EntityParameter.
var parameterTypeUsage = TypeHelpers.GetPrimitiveTypeUsageForScalar(entityParameter.GetTypeUsage());
storeProviderServices.SetParameterValue(storeParameter, parameterTypeUsage, entityParameter.Value);
// Override the store provider parameter state with any explicitly specified values from the EntityParameter.
if (entityParameter.IsDirectionSpecified)
{
storeParameter.Direction = entityParameter.Direction;
}
if (entityParameter.IsIsNullableSpecified)
{
storeParameter.IsNullable = entityParameter.IsNullable;
}
if (entityParameter.IsSizeSpecified)
{
storeParameter.Size = entityParameter.Size;
}
if (entityParameter.IsPrecisionSpecified)
{
dbDataParameter.Precision = entityParameter.Precision;
}
if (entityParameter.IsScaleSpecified)
{
dbDataParameter.Scale = entityParameter.Scale;
}
}
internal static void PopulateParameterFromTypeUsage(EntityParameter parameter, TypeUsage type, bool isOutParam)
{
// type can be null here if the type provided by the user is not a known model type
if (type != null)
{
PrimitiveTypeKind primitiveTypeKind;
if (Helper.IsEnumType(type.EdmType))
{
type = TypeUsage.Create(Helper.GetUnderlyingEdmTypeForEnumType(type.EdmType));
}
else if (Helper.IsSpatialType(type, out primitiveTypeKind))
{
parameter.EdmType = EdmProviderManifest.Instance.GetPrimitiveType(primitiveTypeKind);
}
}
DbCommandDefinition.PopulateParameterFromTypeUsage(parameter, type, isOutParam);
}
public void Skip_with_params_and_literal()
{
var query = @"
select o.Id
from ArubaContext.Owners as o
order by o.Id desc skip @pInt16 LIMIT 5";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
prm1.Value = 5;
// verifying that there are 5 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, null, prm1))
{
VerifySortDescAndCountInt(reader, 5);
}
}
}
public void testQueryEsql()
{
using (LumenEntities dbContext = new LumenEntities()) {
((IObjectContextAdapter)dbContext).ObjectContext.Connection.Open();
// ----- Ora provo in eSql
string esql = @"SELECT VALUE f
FROM LumenEntities.Fotografie
as f
WHERE f.numero < @quanti";
DbCommand comando = ((IObjectContextAdapter)dbContext).ObjectContext.Connection.CreateCommand();
comando.CommandText = esql;
EntityParameter pquanti = new EntityParameter("quanti", DbType.Int16);
pquanti.Value = 3;
comando.Parameters.Add(pquanti);
using (DbDataReader rdr = comando.ExecuteReader(CommandBehavior.SequentialAccess)) {
// Iterate through the collection of Contact items.
while (rdr.Read())
{
Console.WriteLine(rdr ["nomefile"]);
}
}
// -----
// The following query returns a collection of Fotografia objects.
int [] vettore = { 2, 4, 6 };
string listaNum = string.Join(", ", vettore.Select(r => r.ToString()).ToArray());
string esql2 = @"SELECT VALUE f
FROM LumenEntities.Fotografie
as f
WHERE f.numero in {" + listaNum + "}";
ObjectQuery <Fotografia> query2 = new ObjectQuery <Fotografia>(esql2, ((IObjectContextAdapter)dbContext).ObjectContext, MergeOption.NoTracking);
foreach (Fotografia f in query2)
{
Console.WriteLine(f.nomeFile);
}
}
}
static private void StoredProcWithEntityCommand()
{
//<snippetStoredProcWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
try
{
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "AdventureWorksEntities.GetOrderDetails";
cmd.CommandType = CommandType.StoredProcedure;
EntityParameter param = new EntityParameter();
param.Value = "43659";
param.ParameterName = "SalesOrderHeaderId";
cmd.Parameters.Add(param);
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Read the results returned by the stored procedure.
while (rdr.Read())
{
Console.WriteLine("Header#: {0} " +
"Order#: {1} ProductID: {2} Quantity: {3} Price: {4}",
rdr[0], rdr[1], rdr[2], rdr[3], rdr[4]);
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
//</snippetStoredProcWithEntityCommand>
}
private static void AddComparison(EntityCommand command, StringBuilder segment, string alias, string propertyName, string value)
{
if (value != null)
{
if (segment.Length != 0)
{
segment.Append(" AND ");
}
segment.Append(alias);
segment.Append(".");
segment.Append(propertyName);
segment.Append(" LIKE @");
string parameterName = "p" + command.Parameters.Count.ToString(CultureInfo.InvariantCulture);
segment.Append(parameterName);
EntityParameter parameter = new EntityParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
command.Parameters.Add(parameter);
}
}
private void ParseParameter(IEntity entity, XmlNode element)
{
if (entity is EntityMethod)
{
EntityParameter parameter = CreateEntityParameter(element);
ResolveType(element, parameter, true);
IterateInnerChilds(element, parameter);
if (parameter.IsReturn)
{
((EntityMethod)entity).ReturnEntity = parameter;
}
else
{
((EntityMethod)entity).Parameters.Add(parameter);
}
}
else
{
throw new UnknownContextException("Parameter is being parse in a unknown context.Parameter show be child of UML:Operation");
}
}
/// <summary>
/// Constructs a EntityParameter from a CQT parameter.
/// </summary>
private static EntityParameter CreateEntityParameterFromQueryParameter(KeyValuePair<string, TypeUsage> queryParameter)
{
// We really can't have a parameter here that isn't a scalar type...
Debug.Assert(TypeSemantics.IsScalarType(queryParameter.Value), "Non-scalar type used as query parameter type");
var result = new EntityParameter();
result.ParameterName = queryParameter.Key;
PopulateParameterFromTypeUsage(result, queryParameter.Value, isOutParam: false);
return result;
}
internal static void PopulateParameterFromTypeUsage(EntityParameter parameter, TypeUsage type, bool isOutParam)
{
// type can be null here if the type provided by the user is not a known model type
if (type != null)
{
PrimitiveTypeKind primitiveTypeKind;
if (Helper.IsEnumType(type.EdmType))
{
type = TypeUsage.Create(Helper.GetUnderlyingEdmTypeForEnumType(type.EdmType));
}
else if (Helper.IsSpatialType(type, out primitiveTypeKind))
{
parameter.EdmType = EdmProviderManifest.Instance.GetPrimitiveType(primitiveTypeKind);
}
}
DbCommandDefinition.PopulateParameterFromTypeUsage(parameter, type, isOutParam);
}
/// <summary>
/// Updates storeParameter size, precision and scale properties from user provided parameter properties.
/// </summary>
private static void SyncParameterProperties(
EntityParameter entityParameter, DbParameter storeParameter, DbProviderServices storeProviderServices)
{
IDbDataParameter dbDataParameter = storeParameter;
// DBType is not currently syncable; it's part of the cache key anyway; this is because we can't guarantee
// that the store provider will honor it -- (SqlClient doesn't...)
//if (entityParameter.IsDbTypeSpecified)
//{
// storeParameter.DbType = entityParameter.DbType;
//}
// Give the store provider the opportunity to set the value before any parameter state has been copied from
// the EntityParameter.
var parameterTypeUsage = TypeHelpers.GetPrimitiveTypeUsageForScalar(entityParameter.GetTypeUsage());
storeProviderServices.SetParameterValue(storeParameter, parameterTypeUsage, entityParameter.Value);
// Override the store provider parameter state with any explicitly specified values from the EntityParameter.
if (entityParameter.IsDirectionSpecified)
{
storeParameter.Direction = entityParameter.Direction;
}
if (entityParameter.IsIsNullableSpecified)
{
storeParameter.IsNullable = entityParameter.IsNullable;
}
if (entityParameter.IsSizeSpecified)
{
storeParameter.Size = entityParameter.Size;
}
if (entityParameter.IsPrecisionSpecified)
{
dbDataParameter.Precision = entityParameter.Precision;
}
if (entityParameter.IsScaleSpecified)
{
dbDataParameter.Scale = entityParameter.Scale;
}
}
