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); } } }