public void Test()
{
string con = "name = EmptyModelContainer";
using (EntityConnection econn = new EntityConnection(con))
{
string esql = "Select VALUE c from EmptyModelContainer.Teacher as c where c.CustomerID='ALFKI'";
econn.Open();
EntityCommand ecmd = new EntityCommand(esql, econn);
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
if (ereader.Read())
{
Console.WriteLine(ereader["CustomerID"]);
}
Console.WriteLine(ecmd.ToTraceString());
}
}
//</snippeteSQLStructuralTypes>
//string esqlQuery = @"SELECT REF(p) FROM AdventureWorksEntities.Product as p";
//<snippeteSQLRefTypes>
static 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();
try
{
// 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);
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
}
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));
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="esqlQuery">sample : "SELECT VALUE AVG(p.ListPrice) FROM AdventureWorksEntities.Product as p" </param>
/// <param name="act"></param>
public static void ExecuteReader(this ObjectContext context, string esqlQuery, Action <IExtendedDataRecord> act)
{
EntityConnection conn = (EntityConnection)context.Connection;
conn.Open();
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Start reading results.
while (rdr.Read())
{
act(rdr as IExtendedDataRecord);
}
}
}
conn.Close();
}
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);
}
}
}
}
static int Main(string[] args)
{
try
{
Console.WriteLine($"Profiler attached: {Instrumentation.ProfilerAttached}");
using (var ctx = new SchoolDbContextEntities())
{
// create database if missing
ctx.Database.CreateIfNotExists();
var student = new Student()
{
StudentName = "Bill", Age = 12
};
ctx.Students.Add(student);
ctx.SaveChanges();
}
// Specify the provider name, server and database.
string providerName = "System.Data.SqlClient";
string serverName = @"(localdb)\MSSQLLocalDB";
string databaseName = "SchoolDbContext";
// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();
// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;
// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();
// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();
//Set the provider name.
entityBuilder.Provider = providerName;
// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;
// Set the Metadata location.
entityBuilder.Metadata = @"res://*/SchoolModel.csdl|
res://*/SchoolModel.ssdl|
res://*/SchoolModel.msl";
Console.WriteLine(entityBuilder.ToString());
using (EntityConnection conn =
new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
using (EntityCommand cmd = conn.CreateCommand())
{
Console.WriteLine("Creating an EntityCommand with this EntityConnection.");
cmd.CommandText = "SELECT VALUE AVG(s.Age) FROM SchoolDbContextEntities.Students as s";
// 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();
}
}
}
catch (Exception ex)
{
Console.Error.WriteLine(ex);
return((int)ExitCode.UnknownError);
}
return((int)ExitCode.Success);
}
private void ActDataTable_Click(System.Object sender, System.EventArgs e)
{
// ----- Query that returns an ADO.NET DataTable.
DataRow oneRow;
DataTable resultsAsTable;
EntityCommand query;
EntityDataReader results = null;
string sqlText;
// ----- Clear any previous results.
DisplayResults.DataSource = null;
// ----- Build a storage area for the results.
resultsAsTable = new DataTable();
resultsAsTable.Columns.Add("CustomerID", typeof(long));
resultsAsTable.Columns.Add("CustomerName", typeof(string));
resultsAsTable.Columns.Add("AnnualFee", typeof(decimal));
// ----- Connect to the entity provider.
using (EntityConnection linkToDB = new EntityConnection(GetConnectionString()))
{
try
{
linkToDB.Open();
}
catch (Exception ex)
{
MessageBox.Show("Error opening the data connection: " + ex.Message);
return;
}
// ----- Retrieve the data via a parameterized query.
try
{
}
catch (Exception ex)
{
MessageBox.Show("Error processing parameterized query: " + ex.Message);
return;
}
try
{
// ----- Move each row into the DataTable.
}
catch (Exception ex)
{
MessageBox.Show("Error saving query results: " + ex.Message);
return;
}
finally
{
results.Close();
}
// ----- Display the results to the user.
try
{
DisplayResults.DataSource = resultsAsTable;
MessageBox.Show(resultsAsTable.Rows.Count + " result(s)");
}
catch (Exception ex)
{
MessageBox.Show("Error displaying query results: " + ex.Message);
}
}
}
private static void VerifyAgainstBaselineResults(EntityDataReader reader, IEnumerable <double> expectedResults)
{
VerifyAgainstBaselineResults(reader, expectedResults.Cast <object>());
}
static int Main(string[] args)
{
try
{
using (var ctx = new SchoolDbContextEntities())
{
// create database if missing
ctx.Database.CreateIfNotExists();
var student = new Student()
{
StudentName = "Bill", Age = 12
};
ctx.Students.Add(student);
ctx.SaveChanges();
// Call ObjectContext.ExecuteStoreQuery which invokes the bad behavior
// where a branch jumps directly to the method call instruction, which
// throws an InvalidProgramException if we incorrectly load our custom
// method arguments BEFORE the original method call instruction.
var objContext = (ctx as IObjectContextAdapter).ObjectContext;
if (objContext != null)
{
SqlParameter paramName = new SqlParameter("name", "Bill");
var results = objContext.ExecuteStoreQuery <Student>("SELECT * FROM dbo.Students WHERE StudentName = @name", new ExecutionOptions(MergeOption.AppendOnly), paramName);
foreach (var result in results)
{
Console.WriteLine($"ExecuteStoreQuery<Student> result: StudentName={result.StudentName},Age={result.Age}");
}
}
}
// Specify the provider name, server and database.
string providerName = "System.Data.SqlClient";
string serverName = @"(localdb)\MSSQLLocalDB";
string databaseName = "SchoolDbContext";
// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();
// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;
// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();
// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();
//Set the provider name.
entityBuilder.Provider = providerName;
// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;
// Set the Metadata location.
entityBuilder.Metadata = @"res://*/SchoolModel.csdl|
res://*/SchoolModel.ssdl|
res://*/SchoolModel.msl";
Console.WriteLine(entityBuilder.ToString());
using (EntityConnection conn =
new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
using (EntityCommand cmd = conn.CreateCommand())
{
Console.WriteLine("Creating an EntityCommand with this EntityConnection.");
cmd.CommandText = "SELECT VALUE AVG(s.Age) FROM SchoolDbContextEntities.Students as s";
// 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();
}
}
}
catch (Exception ex)
{
Console.Error.WriteLine(ex);
return((int)ExitCode.UnknownError);
}
#if NETCOREAPP2_1
// Add a delay to avoid a race condition on shutdown: https://github.com/dotnet/coreclr/pull/22712
// This would cause a segmentation fault on .net core 2.x
System.Threading.Thread.Sleep(5000);
#endif
return((int)ExitCode.Success);
}
private void ActDataTable_Click(System.Object sender, System.EventArgs e)
{
// ----- Query that returns an ADO.NET DataTable.
DataRow oneRow;
DataTable resultsAsTable;
EntityCommand query;
EntityDataReader results = null;
string sqlText;
// ----- Clear any previous results.
DisplayResults.DataSource = null;
// ----- Build a storage area for the results.
resultsAsTable = new DataTable();
resultsAsTable.Columns.Add("CustomerID", typeof(long));
resultsAsTable.Columns.Add("CustomerName", typeof(string));
resultsAsTable.Columns.Add("AnnualFee", typeof(decimal));
// ----- Connect to the entity provider.
using (EntityConnection linkToDB = new EntityConnection(GetConnectionString()))
{
try
{
linkToDB.Open();
}
catch (Exception ex)
{
MessageBox.Show("Error opening the data connection: " + ex.Message);
return;
}
// ----- Retrieve the data via a parameterized query.
sqlText = @"SELECT CU.ID, CU.FullName, CU.AnnualFee
FROM SalesOrderEntities.Customers AS CU
WHERE CU.AnnualFee >= @MinFee ORDER BY CU.FullName";
try
{
query = new EntityCommand(sqlText, linkToDB);
query.Parameters.AddWithValue("MinFee", 200);
results = query.ExecuteReader(CommandBehavior.SequentialAccess);
}
catch (Exception ex)
{
MessageBox.Show("Error processing parameterized query: " + ex.Message);
return;
}
try
{
// ----- Move each row into the DataTable.
while (results.Read())
{
oneRow = resultsAsTable.NewRow();
oneRow["CustomerID"] = (long)results["ID"];
oneRow["CustomerName"] = (string)results["FullName"];
oneRow["AnnualFee"] = (decimal)results["AnnualFee"];
resultsAsTable.Rows.Add(oneRow);
}
}
catch (Exception ex)
{
MessageBox.Show("Error saving query results: " + ex.Message);
return;
}
finally
{
results.Close();
}
// ----- Display the results to the user.
try
{
DisplayResults.DataSource = resultsAsTable;
MessageBox.Show(resultsAsTable.Rows.Count + " result(s)");
}
catch (Exception ex)
{
MessageBox.Show("Error displaying query results: " + ex.Message);
}
}
}
