private static void QueryEntityClient()
{
using (EntityConnection conn = new EntityConnection(
"name=SampleEntities"))
{
conn.Open();
var query = "SELECT VALUE c " +
"FROM SampleEntities.Contacts AS c " +
"WHERE c.FirstName = 'Robert'";
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
using (EntityDataReader reader = cmd.ExecuteReader(
CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
{
while (reader.Read())
{
var firstname = reader.GetString(1);
var lastname = reader.GetString(2);
var title = reader.GetString(3);
Console.WriteLine("{0} {1} {2}", title.Trim(), firstname.Trim(), lastname);
}
}
conn.Close();
}
}
//Querying with EntityClient to return Streamed Data
public void QueryContacts()
{
using (EntityConnection conn = new EntityConnection("name=PEF"))
{
conn.Open();
var queryString = "SELECT VALUE c " +
"FROM PEF.Contact AS c " +
"WHERE c.FirstName = 'Robert'";
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = queryString;
using (EntityDataReader rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess |
System.Data.CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
var firsName = rdr.GetString(1);
var lastName = rdr.GetString(2);
var tittle = rdr.GetString(3);
Console.WriteLine("{0} {1} {2}", tittle.Trim(), firsName.Trim(), lastName);
}
}
conn.Close();
Console.Write("Presiona Enter");
Console.ReadLine();
}
}
public void QueryContactsEntityConecction()
{
using (var con = new EntityConnection("name=PEF"))
{
con.Open();
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT VALUE c " +
"FROM PEF.Contact AS c " +
"WHERE c.FirstName = 'Robert'";
Dictionary <int, string> dict = new Dictionary <int, string>();
using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
int a = rdr.GetInt32(0);
var b = rdr.GetString(1);
dict.Add(a, b);
Console.WriteLine("{0} {1}",
a,
b);
}
}
Console.Write("Press Enter...");
Console.ReadLine();
}
#endregion
}
/// <summary>
/// ESQL
/// </summary>
static void RSQL()
{
// 这个就是App.config中的数据库连接串
using (var con = new EntityConnection("name=SchoolDBEntities"))
{
//打开连接
con.Open();
//通过连接创建一个命令对象
EntityCommand cmd = con.CreateCommand();
//设置要执行的SQL语句或存储过程
cmd.CommandText = "select value s from SchoolDBEntities.StudentSets as s";
//定义一个接收字典
Dictionary <int, string> dic = new Dictionary <int, string>();
//创建一个reader来进行数据读取
using (EntityDataReader rd = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection))
{
while (rd.Read())
{
int a = rd.GetInt32(0);
string b = rd.GetString(1);
dic.Add(a, b);
}
}
}
}
public void EntitySQLExample()
{
using (EntityConnection conn = new EntityConnection("name=MyEntities")) {
conn.Open();
var queryString = "SELECT VALUE p " +
"FROM MyEntities.People AS p " +
"WHERE p.FirstName='Robert'";
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = queryString;
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess |
CommandBehavior.CloseConnection)) {
while (rdr.Read())
{
string firstname = rdr.GetString(1);
string lastname = rdr.GetString(2);
Console.WriteLine("{0} {1}", firstname, lastname);
}
}
}
}
//Querying with Object Services and Entity SQL
//This doesn't work
//static void QryOS()
//{
// string sqlString = "SELECT VALUE st FROM SchoolDBEntities.Students " +
// "AS st WHERE st.StudenName == 'Steve'";
// IObjectContextAdapter ctx = new
// var objctx = (ctx as new IObjectContextAdapter).ObjectContext;
// ObjectQuery<Student> student = objctx.CreateQuery<Student>(sqlString);
// Student newStudent = student.First<Student>();
//}
static void EntityConnection()
{
using (var con = new EntityConnection("name=SchoolDBEntities"))
{
con.Open();
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT VALUE st FROM SchoolDBEntities.Students as st where st.StudentName='Steve'";
Dictionary <int, string> dict = new Dictionary <int, string>();
using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
int a = rdr.GetInt32(0);
var b = rdr.GetString(1);
dict.Add(a, b);
}
}
}
}
/// <summary>
/// EntityDataReader
/// </summary>
/// <returns></returns>
public Dictionary <int, string> GetAllCityByEntityDataReader()
{
using (var con = new EntityConnection("name=ManagerSiContext")) {
con.Open();
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT VALUE st FROM ManagerSiContext.BASE_CITY as st WHERE st.STATE == '1'";
Dictionary <int, string> dict = new Dictionary <int, string>();
using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection)) {
while (rdr.Read())
{
int a = rdr.GetInt32(0);
var b = rdr.GetString(1);
dict.Add(a, b);
}
}
return(dict);
}
}
public static List <string> ShowSchema()
{
string sqlQuery = "select t.treatm_name from EsToDoEntities.TREATMs as t";
//string esql = "select e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY from HREntities.EMPLOYEEs as e where e.EMPLOYEE_ID < " + max_id;
List <string> list = new List <string>();
EntityConnection econn = new EntityConnection("name=EsToDoEntities");
econn.Open();
EntityCommand ecmd = econn.CreateCommand();
ecmd.CommandText = sqlQuery;
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
Console.WriteLine("Entity SQL Result");
while (ereader.Read())
{
list.Add(ereader.GetString(0));
}
return(list);
}
public static void EntitySQLUsingEntityConnectionDemo()
{
Console.WriteLine("*** EntitySQLUsingEntityConnectionDemo Start ***");
using (var con = new EntityConnection("name=SchoolDBEntities"))
{
con.Open();
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT VALUE st FROM SchoolDBEntities.Students as st where st.StudentID = 1";
Dictionary <int, string> dict = new Dictionary <int, string>();
using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
int a = rdr.GetInt32(0);
var b = rdr.GetString(1);
dict.Add(a, b);
}
}
}
Console.WriteLine("*** EntitySQLUsingEntityConnectionDemo Finished ***");
}
private static void VerifySortDescAndCountString(EntityDataReader reader, int expectedCount, bool distinct = false)
{
string name = null;
var count = 0;
var items = new HashSet <string>();
while (reader.Read())
{
var newName = reader.GetString(0);
if (name != null)
{
Assert.True(name.CompareTo(newName) >= 0);
}
if (distinct)
{
Assert.False(items.Contains(newName));
items.Add(newName);
}
name = newName;
count++;
}
Assert.Equal(expectedCount, count);
}
private static void SimpleNinjaQueryEntitySQL_Reader()
{
using (EntityConnection conn = new EntityConnection("name=NinjaObjectContext"))
{
//context.Database.Log = Console.WriteLine;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT VALUE n
FROM NinjaObjectContext.Ninjas AS n
WHERE n.DateOfBirth > @dob";
cmd.Parameters.AddWithValue("dob", new DateTime(1984, 1, 1));
using (EntityDataReader dr = cmd.ExecuteReader(
System.Data.CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
Console.Out.WriteLine(dr.GetString(1) + " " + dr.GetDateTime(4));
}
}
}
}
