// GET: EntityPhotoComments
public ActionResult Index()
{
var Cmd = Conn.CreateCommand();
Cmd.CommandText = "select p.PhotoID, p.Title, p.Description, p.CreatedDate, c.Subject, c.Body, c.UserName " +
"from PhotoSharingEntities.photos as p inner join PhotoSharingEntities.comments as c on p.PhotoID=c.PhotoID";
Conn.Open();
var rd = Cmd.ExecuteReader(CommandBehavior.SequentialAccess);
ArrayList list = new ArrayList();
while (rd.Read())
{
var data = new
{
PhotoID = rd["PhotoID"].ToString(),
Title = rd["Title"].ToString(),
Description = rd["Description"].ToString(),
CreatedDate = rd["CreatedDate"].ToString(),
Subject = rd["Subject"].ToString(),
Body = rd["Body"].ToString(),
UserName = rd["UserName"].ToString()
};
list.Add(data);
}
Conn.Close();
ViewBag.Data = list;
return(View());
}
public ActionResult Index()
{
Conn.ConnectionString = "Name=PhotoSharingEntities"; //需要把webconfig的屬性+連線名稱都寫出來，N要大寫
var Cmd = Conn.CreateCommand();
//寫sqlCommand時，連同資料庫名稱都要寫上去
Cmd.CommandText = "select p.PhotoID,p.title,c.subject,c.body,c.UserName from PhotoSharingEntities.Photos as p inner join PhotoSharingEntities.comments as c on p.PhotoID=c.PhotoID";
Conn.Open();
var rd = Cmd.ExecuteReader(CommandBehavior.SequentialAccess);
//不一定要arraylist來接資料
ArrayList list = new ArrayList();
while (rd.Read())
{
var data = new
{
PhotoID = rd["PhotoID"].ToString(),
title = rd["title"].ToString(),
subject = rd["subject"].ToString(),
body = rd["body"].ToString(),
UserName = rd["UserName"].ToString()
};
list.Add(data);
}
Conn.Close();
ViewBag.Data = list;
return(View());
}
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
}
private static void FunWithEntityDataReader()
{
using (EntityConnection cn = new EntityConnection("name = AutoLotEntities"))
{
cn.Open();
string query = "SELECT VALUE car FROM AutoLotEntities.Cars As Car";
//创建一个命令对象
using (EntityCommand cmd = cn.CreateCommand())
{
cmd.CommandText = query;
//使用EntityDataReader获取得到的数据
using (EntityDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
Console.WriteLine("******Record*******");
Console.WriteLine("ID:{0}", dr["CarID"]);
Console.WriteLine("Make:{0}", dr["Make"]);
Console.WriteLine("Color:{0}", dr["Color"]);
Console.WriteLine("Pet Name:{0}", dr["CarNickName"]);
}
}
}
}
}
//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();
}
}
/// <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);
}
}
}
}
private static void FunWithEntityDataReader()
{
// Make a connection object, based on our *.config file.
using (EntityConnection cn = new EntityConnection("name=AutoLotEntities"))
{
cn.Open();
// Now build an Entity SQL query.
string query = "SELECT VALUE car FROM AutoLotEntities.Cars AS car";
// Create a command object.
using (EntityCommand cmd = cn.CreateCommand())
{
cmd.CommandText = query;
// Finally, get the data reader and process records.
using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
Console.WriteLine("***** RECORD *****");
Console.WriteLine("ID: {0}", dr["CarID"]);
Console.WriteLine("Make: {0}", dr["Make"]);
Console.WriteLine("Color: {0}", dr["Color"]);
Console.WriteLine("Pet Name: {0}", dr["CarNickname"]);
Console.WriteLine();
}
}
}
}
}
static public void NavigateWithNavOperatorWithEntityCommand()
{
//<snippetNavigateWithNavOperatorWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
// Create an Entity SQL query.
string esqlQuery =
@"SELECT address.AddressID, (SELECT VALUE DEREF(soh) FROM
NAVIGATE(address, AdventureWorksModel.FK_SalesOrderHeader_Address_BillToAddressID)
AS soh) FROM AdventureWorksEntities.Addresses AS address";
cmd.CommandText = esqlQuery;
// Execute the command.
using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Start reading.
while (rdr.Read())
{
Console.WriteLine(rdr["AddressID"]);
}
}
}
conn.Close();
}
//</snippetNavigateWithNavOperatorWithEntityCommand>
}
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>
}
//</snippeteSQLStructuralTypes>
//string esqlQuery = @"SELECT REF(p) FROM AdventureWorksEntities.Products as p";
//<snippeteSQLRefTypes>
static public void ExecuteRefTypeQuery(string esqlQuery)
{
if (esqlQuery.Length == 0)
{
Console.WriteLine("The query string is empty.");
return;
}
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Start reading results.
while (rdr.Read())
{
RefTypeVisitRecord(rdr as IExtendedDataRecord);
}
}
}
conn.Close();
}
}
private static void EntityDataReader()
{
using (var entityConnection = new EntityConnection("name=AutoLotEntities")) // Создать объект соединения на основе файла *.config
{
entityConnection.Open();
const string query = "SELECT VALUE car FROM AutoLotEntities.Cars AS car"; // Построить запрос Entity SQL
using (EntityCommand entityCommand = entityConnection.CreateCommand()) // Создать командный объект
{
entityCommand.CommandText = query;
// Получить объект для чтения данных и обработать записи
using (EntityDataReader entityDataReader = entityCommand.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (entityDataReader.Read())
{
Console.WriteLine("----- RECORD -----");
Console.WriteLine("Id: {0}", entityDataReader["CarId"]);
Console.WriteLine("Make: {0}", entityDataReader["Make"]);
Console.WriteLine("Color: {0}", entityDataReader["Color"]);
Console.WriteLine("Pet name: {0}", entityDataReader["CarNickname"]);
Console.WriteLine();
}
}
}
}
}
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();
}
}
//</snippeteSQLRefTypes>
//string esqlQuery = @"SELECT VALUE AVG(p.ListPrice) FROM AdventureWorksEntities.Products as p";
//<snippeteSQLPrimitiveTypes>
static void ExecutePrimitiveTypeQuery(string esqlQuery)
{
if (esqlQuery.Length == 0)
{
Console.WriteLine("The query string is empty.");
return;
}
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Start reading results.
while (rdr.Read())
{
IExtendedDataRecord record = rdr as IExtendedDataRecord;
// For PrimitiveType
// the record contains exactly one field.
int fieldIndex = 0;
Console.WriteLine("Value: " + record.GetValue(fieldIndex));
}
}
}
conn.Close();
}
}
private static void FunWIthEntityDataReader()
{
// 基于*.config文件创建一个连接对象
using (EntityConnection cn = new EntityConnection("name=AutoLotEntities"))
{
cn.Open();
// 构建一个Entity SQL查询
string query = "SELECT VALUE car FROM AutoLotEntities.Cars AS car";
// 创建一个命令对象
using (EntityCommand cmd = cn.CreateCommand())
{
cmd.CommandText = query;
// 最后,获取数据阅读器并处理得到的记录
using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
Console.WriteLine("***** RECORD *****");
Console.WriteLine("ID: {0}", dr["CarID"]);
Console.WriteLine("Make: {0}", dr["Make"]);
Console.WriteLine("Color: {0}", dr["Color"]);
Console.WriteLine("Pet Name: {0}", dr["CarNickname"]);
Console.WriteLine();
}
}
}
}
}
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 void button2_Click(object sender, EventArgs e)
{
cn = new EntityConnection("Name=EcoFarm_DBEntities");
cn.Open();
cmd = cn.CreateCommand();
cmd.CommandText = "SELECT VALUE i FROM EcoFarm_DBEntities.Invoice_products as i WHERE i.Name = @product";
cmd.Parameters.AddWithValue("product", ProductsCB.SelectedItem);
DbDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
dt.Columns.Add("col", "Product code");
dt.Columns.Add("col", "Name");
dt.Columns.Add("col", "Units");
dt.Columns.Add("col", "Number of units");
dt.Columns.Add("col", "Unit price");
dt.Columns.Add("col", "Total price");
dt.Columns.Add("col", "Invoice number");
while (dbReader.Read())
{
dt.Rows.Add(
dbReader["Product_code"].ToString(),
dbReader["Name"].ToString(),
dbReader["Units"].ToString(),
dbReader["Number_of_units"].ToString(),
dbReader["Unit_price"].ToString(),
dbReader["Total_price"].ToString(),
dbReader["Invoice_Number"].ToString()
);
}
dbReader.Close();
cn.Close();
}
public IEnumerable <Book> Get()
{
var books = new List <Book>();
using (var connection = new EntityConnection("name=DataContext")) {
connection.Open();
string esqlQuery = @"SELECT VALUE books FROM DataContext.Books AS books";
using (var command = connection.CreateCommand())
{
command.CommandText = esqlQuery;
using (var dataReader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dataReader.Read())
{
var book = new Book();
book.BookId = (int)dataReader["BookId"];
book.Isbn = dataReader["Isbn"] as string;
book.Title = dataReader["Title"] as string;
books.Add(book);
}
}
}
connection.Close();
}
return(books);
}
public async Task <IEnumerable <User> > GetAllAsync()
{
List <User> users = new List <User>();
//Using EntityConnection, EntityCommand, and Async methods
using (var db = new EntityConnection("name=AppEntities"))
{
await db.OpenAsync();
EntityCommand command = db.CreateCommand();
command.CommandText = "SELECT VALUE u FROM AppEntities.USERS AS u";
using (var entityDataReader =
await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
while (entityDataReader.Read())
{
users.Add(new User()
{
Id = (int)entityDataReader.GetValue(0),
FirstName = (string)entityDataReader.GetValue(1),
LastName = (string)entityDataReader.GetValue(2),
City = (string)entityDataReader.GetValue(3),
Username = (string)entityDataReader.GetValue(4)
});
}
}
}
return(users);
}
static void TestProduct(string esqlQuery, Dictionary <string, object> parametes)
{
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
try
{
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
foreach (KeyValuePair <string, object> kvp in parametes)
{
cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// The result returned by this query contains
// Address complex Types.
while (rdr.Read())
{
int col = rdr.FieldCount;
if (rdr.FieldCount > 3)
{
col = 3;
}
for (int i = 0; i < col; i++)
{
Console.Write("{0} ", rdr[i]);
}
Console.WriteLine();
if (rdr.Depth > 0)
{
// Display Address information.
DbDataRecord nestedRecord =
rdr[0] as DbDataRecord;
for (int i = 0; i < nestedRecord.FieldCount; i++)
{
Console.WriteLine(" " + nestedRecord.GetName(i) +
": " + nestedRecord.GetValue(i));
}
}
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
}
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);
}
public IEnumerable <User> Login(string usernam, string password)
{
using (EntityConnection con = new EntityConnection("Name=user"))
{
con.Open();
EntityCommand command = con.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
return(null);
}
}
static void Main(string[] args)
{
//Database.SetInitializer(new CreateDatabaseIfNotExists<UnderwritingEntitiesContainer>());
//EF_concurrencyfeatures();
using (UnderwritingEntitiesContainer db = new UnderwritingEntitiesContainer())
{
//db.Database.Log = Console.WriteLine;
////use Db table name for Sqlquery
////tightly coupled code
//var headers = db.headers.SqlQuery("Select * from uw.header");
//foreach(var h in headers)
//{
// Console.WriteLine("ID" + h.Id + " value " + h.PolicyReference);
//}
var objectContext = (db as IObjectContextAdapter).ObjectContext;
RunUncompiledQuery(objectContext);
RunCompiledQuery();
//Use EntitySql
//strongly typed
using (EntityConnection connection = objectContext.Connection as EntityConnection)
{
connection.Open();
EntityCommand cmd = connection.CreateCommand();
cmd.CommandText = "Select value header from UnderwritingEntitiesContainer.headers as header";
EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (dr.Read())
{
Console.WriteLine("id " + dr["HeaderId"]);
Console.WriteLine("value " + dr["PolicyReference"]);
}
//ObjectQuery
string query = "select value h from UnderwritingEntitiesContainer.headers as h";
// Use for filter - ObjectParameter
foreach (header h in new ObjectQuery <header>(query, objectContext))
{
Console.WriteLine("id " + h.HeaderId + " policy " + h.PolicyReference);
}
connection.Close();
}
}
Console.ReadKey();
}
public void GetSomeCustomersUsingESQLRawQuerying()
{
var context = new SalesEntities(); //I needed this in the test, but you shouldn't need it in regular code
using (var conn = new EntityConnection("name=SalesEntities"))
{
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT VALUE c FROM SalesEntities.Customers AS c " +
"WHERE c.FirstName='Robert' ORDER BY c.LastName";
EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection);
}
}
static private void ComplexTypeWithEntityCommand()
{
//<snippetComplexTypeWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=CustomerComplexAddrContext"))
{
conn.Open();
// Create a query that returns Address complex type.
string esqlQuery =
@"SELECT VALUE customers FROM
CustomerComplexAddrContext.CCustomers
AS customers WHERE customers.CustomerId < 3";
try
{
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// 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("Customer ID: {0}",
rdr["CustomerId"]);
// Display Address information.
DbDataRecord nestedRecord =
rdr["Address"] as DbDataRecord;
Console.WriteLine("Address:");
for (int i = 0; i < nestedRecord.FieldCount; i++)
{
Console.WriteLine(" " + nestedRecord.GetName(i) +
": " + nestedRecord.GetValue(i));
}
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
//</snippetComplexTypeWithEntityCommand>
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
context.People.AddObject(new Teacher {
Name = "Janet Dietz", IsProfessor = true
});
context.People.AddObject(new Teacher {
Name = "Robert Kline", IsProfessor = false
});
context.People.AddObject(new Lawyer {
Name = "Jenny Dunlap", Cases = 3
});
context.People.AddObject(new Lawyer {
Name = "Karen Eads", Cases = 7
});
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
var esql = "select value p from OfType(People,Recipe4.Teacher) as p";
var teachers = context.CreateQuery <Teacher>(esql);
Console.WriteLine("Teachers...Using Object Services");
foreach (var teacher in teachers)
{
Console.WriteLine("{0} is{1} a professor", teacher.Name, teacher.IsProfessor ? "" : " not");
}
}
Console.WriteLine();
using (var conn = new EntityConnection("name=EFRecipesEntities"))
{
conn.Open();
var esql = "select value p from OfType(EFRecipesEntities.People,EFRecipesModel.Teacher) as p";
var cmd = conn.CreateCommand();
cmd.CommandText = esql;
Console.WriteLine("Teachers...Using EntityClient");
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
Console.WriteLine("{0} is{1} a professor", reader.GetString(1), reader.GetBoolean(2) ? "" : " not");
}
}
}
Console.WriteLine("Press <enter> to continue...");
Console.ReadLine();
}
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);
}
static private void ReturnNestedCollectionWithEntityCommand()
{
//<snippetReturnNestedCollectionWithEntityCommand>
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
try
{
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
// Create a nested query.
string esqlQuery =
@"Select c.ContactID, c.SalesOrderHeader
From AdventureWorksEntities.Contact as c";
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// The result returned by this query contains
// ContactID and a nested collection of SalesOrderHeader items.
// associated with this Contact.
while (rdr.Read())
{
// the first column contains Contact ID.
Console.WriteLine("Contact ID: {0}", rdr["ContactID"]);
// The second column contains a collection of SalesOrderHeader
// items associated with the Contact.
DbDataReader nestedReader = rdr.GetDataReader(1);
while (nestedReader.Read())
{
Console.WriteLine(" SalesOrderID: {0} ", nestedReader["SalesOrderID"]);
Console.WriteLine(" OrderDate: {0} ", nestedReader["OrderDate"]);
}
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
//</snippetReturnNestedCollectionWithEntityCommand>
}
public static void EntitySql()
{
using (var conn = new EntityConnection("Name=SchoolDBEntities"))
{
conn.Open();
var command = conn.CreateCommand();
command.CommandText = "SELECT Name FROM SchoolDBEntities.Student AS c";
var rd = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (rd.Read())
{
Console.WriteLine(rd["Name"]);
}
}
}
public void SqlEntity_Test(NorthwindEntities context)
{
string eSql = "SELECT VALUE c FROM NorthwindEntities.Categories AS c";
ObjectQuery <Category> query = context.CreateQuery <Category>(eSql);
var result = query.ToList();
using (var conn = new EntityConnection("name=NorthwindEntities"))
{
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = eSql;
EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
}
}
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>
}
