Basically the problem i have is that i want to run a query in a database that it's not a representation of my model.
This is my code to create the connection to another database:
public static OtherContext GetNewContextGeneric(string connectionString)
{
var builder = new DbContextOptionsBuilder();
builder.UseSqlServer(connectionString);
OtherContext db = new OtherContext(builder.Options);
return db;
}
And this is my code to execute the query:
public List<IQueryble> Query (string connectionString, string query)
{
try
{
using(var contextGeneric = ContextFactory.GetNewContextGeneric(connectionString))
{
//I want something like this
return contextGeneric.Query(query).ToList();
}
}
catch(System.Data.SqlClient.SqlException ex)
{
throw new SQLIncorrectException(ex);
}
catch(System.InvalidOperationException ex)
{
throw new NotImplementedException();
}
}
Can somebody help me?
It worked like this:
private void SqlCommand (string connectionString, string query)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
var a = reader[0];
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
}
}
Or
using (var connection = ContextFactory.GetNewContextGeneric(connectionString).Database.GetDbConnection())
{
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = query;
using (var reader = command.ExecuteReader())
{
// Do something with result
reader.Read(); // Read first row
var firstColumnObject = reader.GetValue(0);
/*var secondColumnObject = reader.GetValue(1);
reader.Read(); // Read second row
firstColumnObject = reader.GetValue(0);
secondColumnObject = reader.GetValue(1);*/
connection.Close();
return firstColumnObject.ToString();
}
}
In the question you say:
Basically the problem i have is that i want to run a query in a database that it's not a representation of my model.
and then in comments you add:
Because i don't know how is created the database, i don't know what tables are in the database i want to insert the sql query
Well, if you don't know the database, then you cannot use Entity Framework, as it requires you to have a detailed knowledge of the database you are connecting to.
You should use plain ADO.NET (or Dapper if you want to map results back to a known class) for this.