static void Main(string[] args)
{
using (var c = new EntityConnection("name=AdventureWorksEntities"))
{
c.StateChange += EStateChange;
var cmd = "SELECT VALUE C FROM AdventureWorksEntities.Contatos AS C WHERE C.ContactID=@ContactID";
using (var k = new EntityCommand(cmd, c))
{
k.Parameters.AddWithValue("ContactID", 1);
c.Open();
var dr = k.ExecuteReader(CommandBehavior.SequentialAccess);
if (dr.Read()) //while se existir mais de um registro
{
Console.WriteLine(dr["Nome"]);
}
if (c.State != ConnectionState.Closed) c.Close();
}
}
Console.ReadKey();
}
public void Test1()
{
string SELECT_PERSONS_ALL = "SELECT p.id as person_id, p.fname, p.lname, ph.id as phone_id , " +
"ph.phonevalue as phonevalue, a.id as address_id , a.addressvalue as addressvalue " +
"FROM Entities.People as p INNER JOIN Entities.Addresses as a ON a.personid = p.id " +
"INNER JOIN Entities.Phones as ph ON ph.personid = p.id";
EntityConnection m_connection = new EntityConnection("name=Entities");
List<Mock.PhoneBook.Phone> list = new List<Mock.PhoneBook.Phone>();
m_connection.Open();
Infrastructure.PhoneBook.IMockConvertor convertor = new ERDBArch.Modules.PhoneBook.BLL.PersonConvertor();
using (EntityCommand cmd = new EntityCommand(SELECT_PERSONS_ALL, m_connection))
{
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
System.Diagnostics.Debug.WriteLine(reader["person_id"]);
}
}
}
m_connection.Close();
}
internal static void ExecuteESqlQuery(string cxString, string query)
{
EntityConnection connection = new EntityConnection(cxString);
EntityCommand command = new EntityCommand(query, connection);
connection.Open();
try
{
command.ExecuteReader(CommandBehavior.SequentialAccess).Dump<EntityDataReader>();
}
finally
{
connection.Close();
}
}
protected int GetEntityCount(string esqlQuery)
{
int count = 0;
using (var connection = new EntityConnection(AboutInheritanceEntitiesConnectionString))
{
connection.Open();
using (var command = new EntityCommand(esqlQuery, connection))
{
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
count = count + 1;
}
}
}
}
return count;
}
public override void ExecuteESqlQuery(IConnectionInfo cxInfo, string query)
{
EntityConnection connection;
object dbContext = base.InstantiateBaseContext(cxInfo);
CheckForUnpatchedCx(dbContext);
string eSqlCxString = GetESqlCxString(dbContext);
EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder(eSqlCxString);
string str2 = builder["metadata"] as string;
if ((str2 != null) && str2.Trim().ToUpperInvariant().StartsWith("READER:"))
{
connection = new EntityConnection(GetObjectContext(dbContext).MetadataWorkspace, (DbConnection) GetIDbConnection(dbContext));
}
else
{
connection = new EntityConnection(eSqlCxString);
}
using (connection)
{
EntityCommand command = new EntityCommand(query, connection);
connection.Open();
command.ExecuteReader(CommandBehavior.SequentialAccess).Dump<EntityDataReader>();
}
}
public void UserDefinedFunction()
{
using (EntityConnection conn = new EntityConnection("name=testEntities"))
{
conn.Open();
string query = @"SELECT e.FirstName AS Name FROM testEntities.Employees AS e
WHERE testModel.Store.spFunc(e.Id, '') = 6";
using (EntityCommand cmd = new EntityCommand(query, conn))
{
EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
Assert.IsTrue(reader.Read());
Assert.AreEqual("Scooby", reader[0]);
}
}
}
/// <summary>
/// Gets all Persons form DB with JOIN
/// </summary>
/// <returns>List of Person</returns>
public List<Model.Person> GetPersonsJoin()
{
List<Model.Person> res = new List<Model.Person>();
m_connection.Open();
Infrastructure.PhoneBook.IMockConvertor convertor = new BLL.PersonConvertor();
using (EntityCommand cmd = new EntityCommand(SELECT_PERSONS_ALL, m_connection))
{
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
Dictionary<string, object> dic = Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName, reader.GetValue);
Mock.PhoneBook.Person person = new BLL.PersonConvertor().ToObject(dic) as Mock.PhoneBook.Person;
if (person.ID == 0)
person = Mock.PhoneBook.Person.Null;
Mock.PhoneBook.Phone phone = new BLL.PhoneConvertor().ToObject(dic) as Mock.PhoneBook.Phone;
if (phone.ID == 0)
person = Mock.PhoneBook.Person.Null;
Mock.PhoneBook.Address address = new BLL.AddressConvertor().ToObject(dic) as Mock.PhoneBook.Address;
if (address.ID == 0)
person = Mock.PhoneBook.Person.Null;
res.Add(new Model.Person(person, phone, address));
}
}
}
m_connection.Close();
return res;
}
/// <summary>
/// Sectes all Phones from person table into List
/// </summary>
/// <returns>List of Phone mocks</returns>
protected List<Mock.PhoneBook.Phone> GetPhones()
{
List<Mock.PhoneBook.Phone> list = new List<Mock.PhoneBook.Phone>();
m_connection.Open();
Infrastructure.PhoneBook.IMockConvertor convertor = new BLL.PhoneConvertor();
using (EntityCommand cmd = new EntityCommand(SELECT_PHONES_ALL, m_connection))
{
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
list.Add(convertor.ToObject(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName, reader.GetValue)) as Mock.PhoneBook.Phone);
}
}
}
m_connection.Close();
return list;
}
public void Verify_querying_database_with_EntityClient_works()
{
const string commandText =
"SELECT C.CompanyName, C.Location FROM NorthwindEntities.Customers AS C WHERE C.CustomerID LIKE @CustomerID";
using (var connection = new EntityConnection(NorthwindEntitiesConnectionString))
{
connection.Open();
using (var command = new EntityCommand(commandText, connection))
{
command.Parameters.AddWithValue("CustomerID", "A%");
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
foreach (var expectedResult in expectedResults)
{
reader.Read();
Assert.Equal(expectedResult.Name, reader["CompanyName"]);
Assert.Equal(expectedResult.Location, ((DbGeography)reader["Location"]).AsText());
}
Assert.False(reader.Read());
}
}
}
Console.WriteLine();
}