How to turn a sql record into an object
-
If you're using raw sql (not entity framework)
If you're going to do that, you should probably create a typed DataSet--there's a wizard that can generate one off a table for you.
ETA: Yes, that doesn't address all of Captain's question. No, it probably doesn't scale if he needs arbitrary queries, either, but if he only needed a few, it could be part of his solution.
-
Though you won't get objects, you'll get a list of values (strings, integers, datetimes, etc.). Instantiating an object with those values is pretty easy. The only annoying thing in Python is to get a dictionary of field => value pairs; you normally have to get the field list from the cursor and then build the dictionary yourself. I think MSSQL might be the exception, though, and gives you objects with the field names as properties.
-
Here is a full dynamic example, to remove any ambiguity. Basically it takes the passed in SQL query, dynamically creates a type based off of the schema table and then uses EF to run the sql table/instantiate the type for pretty printing. This example also allows you to create new instances to append to the list in case you want to muck about. (Instantiation code jacked/modified from the commented source since I didn't want to type it all)
class Program { private static SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder() {DataSource = @"DataSource From Your App.Config", InitialCatalog = "testdb", IntegratedSecurity = true}; // This can be UN/PW credentials, you just need to fix it to w/e you need. static void Main(string[] args) { using (var entity = new Entities()) { //var tableData = entity.testdb.Where(x => x.fake == 2).ToList(); var sql = "select a.ID, a.fake, b.placeholder from dbo.testdb as a inner join dbo.testtable2 as b on a.id = b.id"; var tableData = entity.Database.SqlQuery(MyTypeBuilder.CompileResultType(sql), sql); Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented)); } Console.ReadLine(); } /// <summary> /// Taken (and modified) from http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery /// </summary> public static class MyTypeBuilder { public static void CreateNewObject(string sql) { var myType = CompileResultType(sql); var myObject = Activator.CreateInstance(myType); } public static Type CompileResultType(string sql) { TypeBuilder tb = GetTypeBuilder(); ConstructorBuilder constructor = tb.DefineDefaultConstructor(MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.RTSpecialName); using (var conn = new SqlConnection(connString.ToString())) // This is a SqlConnectionString object { conn.Open(); using(var command = new SqlCommand(sql, conn)) { using (SqlDataReader sqlReader = command.ExecuteReader()) { sqlReader.Read(); var schema = sqlReader.GetSchemaTable(); for (var i = 0; i < sqlReader.FieldCount; i++) { var columnName = sqlReader.GetName(i); var columnType = sqlReader.GetValue(i).GetType(); CreateProperty(tb, columnName, columnType); } } } } Type objectType = tb.CreateType(); return objectType; } private static TypeBuilder GetTypeBuilder() { var typeSignature = "MyDynamicType"; var an = new AssemblyName(typeSignature); AssemblyBuilder assemblyBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(an, AssemblyBuilderAccess.Run); ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule("MainModule"); TypeBuilder tb = moduleBuilder.DefineType(typeSignature , TypeAttributes.Public | TypeAttributes.Class | TypeAttributes.AutoClass | TypeAttributes.AnsiClass | TypeAttributes.BeforeFieldInit | TypeAttributes.AutoLayout , null); return tb; } private static void CreateProperty(TypeBuilder tb, string propertyName, Type propertyType) { FieldBuilder fieldBuilder = tb.DefineField("_" + propertyName, propertyType, FieldAttributes.Private); PropertyBuilder propertyBuilder = tb.DefineProperty(propertyName, PropertyAttributes.HasDefault, propertyType, null); MethodBuilder getPropMthdBldr = tb.DefineMethod("get_" + propertyName, MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig, propertyType, Type.EmptyTypes); ILGenerator getIl = getPropMthdBldr.GetILGenerator(); getIl.Emit(OpCodes.Ldarg_0); getIl.Emit(OpCodes.Ldfld, fieldBuilder); getIl.Emit(OpCodes.Ret); MethodBuilder setPropMthdBldr = tb.DefineMethod("set_" + propertyName, MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig, null, new[] { propertyType }); ILGenerator setIl = setPropMthdBldr.GetILGenerator(); Label modifyProperty = setIl.DefineLabel(); Label exitSet = setIl.DefineLabel(); setIl.MarkLabel(modifyProperty); setIl.Emit(OpCodes.Ldarg_0); setIl.Emit(OpCodes.Ldarg_1); setIl.Emit(OpCodes.Stfld, fieldBuilder); setIl.Emit(OpCodes.Nop); setIl.MarkLabel(exitSet); setIl.Emit(OpCodes.Ret); propertyBuilder.SetGetMethod(getPropMthdBldr); propertyBuilder.SetSetMethod(setPropMthdBldr); } }@captain (Ping, because spam)
-
There are ORMs for Python as well that can probably do it for you.
Not familiar enough with Python to know if any of them are light-weight though.
-
All that, just to run a simple query through EF?
If it was up to me, I'd just use ADO.NET and read into a DataTable.
-
No, all that to create a 100% dynamic sql query method to return an arbitrary class object since apparently the rules became (Per BlakeyRat, not Captain)
- Don't use EF syntax (raw sql only)
- Don't require manually typing class values
- Don't require any modification to raw sql queries
- Just make it all magically happen.
Basically this would turn into the main method being turned into a private static dynamic GetClassListFromArbitrarySql(string sql) and you call it however millions of times you want with random sql.
-
Neat, somebody should make a library so I don't have to type it.
-
I'm pretty sure it's called 'Entity Framework'
-
All that, just to run a simple query through EF?
What a shocker. It's almost as if that dummy-head Blakeyrat, when about 473 posts ago he said "that's more difficult than not using EF at all", was correct. But that's impossible! He's so stupid!
-
Have you just reimplemented
dynamic, but clunkier? You can't even do anything with your object without reflecting all over it, since you don't have the type defined until runtime.
-
Basically, yes, because you can't pass dynamic directly to EF. But per the rules lord blakeyrat assigned, that's pretty much what you end up with.
Though since it's in JSON, you can access it directly from web UI's by key value, so you don't have to mess with any of the fields you don't care about.
If you scroll up you'll see several more 'sane' implementations.
-
I recommend Massive, assuming your DB is MS SQL Server and you want to stick with C#. Arbitrary query directly to an
IEnuerable<dynamic>of the rows.var rows = Massive.DB.Current.Query("select * from table"); foreach (var row in rows) { Console.WriteLine("MyData: Col1: " + row.Col1 + " Col2: " + row.Col2); }About as simple as can be. It picks up the only connection string in your app.config. Or specify the connection string name explicitly with:
var db = Massive.DynamicModel.Open("myConnectionStringName");And call Query on that.
We're using it on a pretty big project, and everything works great.
-
Ordinals? Yuck!
Relying on the position of a column in a table violates 1NF and is a great way to get burned if you have to work with many similar but not identical databases.
-
No, all that to create a 100% dynamic sql query method to return an arbitrary class object since apparently the rules became (Per BlakeyRat, not Captain)
- Don't use EF syntax (raw sql only)
are you talking about?Suppose you wrote a long, complicated SQL query, with lots of fields (and you put in field names).
-
So I've worked on many .net projects that turn SQL rows into objects. I've wondered, quite often, "could there be something that does this automagically for me?"
I start to go down that rabbit hole, and think about Reflection and third party frameworks and shit like that. And then I realize, "none of these will work right".
Why? Because at some point, I'm going to have to add a new field to the query. And that field will then have to be added to the object. And then what? I'll have to change the definition somewhere and recompile, or how will design-time tools (like Intellisense) know about the field?
Or if it's all done at runtime, I've broken Intellisense.
And then I realize the time it took me to even think about this, I could have written the object and all it's get/set methods.
So now I just do that.
Select * from SomeDumbfuckTable
edit fuck you discourse you dumb fucking braindead parsing asslicker!
Then in code
Dumbfucks = new DumbfucksCollection() For Each row in dt.rows DumbfuckInstance = InstantiateDumbfuck(row) DumbfucksCollection.Add(DumbFuckInstance) Next For Function InstantiateDumbfuck(datarow) as Dumbfuck DubmfuckInstance = new Dumbfuck() DubmfuckInstance.ID = odr("id") DubmfuckInstance.Name = odr("name") DubmfuckInstance.Date = ... Return DubmfuckInstance End ForThrow in a sprinkle of DBNull checking, date conversion, etc. I can bang out an object with a hundred public properties in under an hour. Hell, you can write a simple Excel spreadsheet, paste in the row names and field definitions, and have it generate the code for you.
This is one of those instances where it'll almost certainly be more difficult and cost you more in setup and maintenance time than just writing it by hand, for a project of almost any size.
-
Suppose
Suppose
"product"
would you use
If
I think
SWIM wants to use SQL, but isn't sure how much to use...SWIM hears that SQL is lots of fun if you use the right amount...
-
are you talking about?You're just now figuring out that Matches obviously never read the OP before he started offering suggestions?
-
C#, use reflection to pick which sqlreader.get[type] method to choose,
Make a static class that contains the reflection code and takes in a generic type and the sql results, and done.
Make a column attribute for each variable.All you ever have to do is update the class when the sql result changes, or not, as long as the order of the returned columns stays the same.
Matter of fact, you could probably also use reflection on the same class to build the sql query too.
-
I'd probably just use Excel assuming that is pretty enough and fulfills the requirements (not even
... for a one off it might be enough)
-
@Lorne_Kates said:
And then I realize the time it took me to even think about this, I could have written the object and all it's get/set methods.
Entity Framework effectively does that for you - you can simply point the designer to the database and have it pull the schema and autogenerate simple objects from it. It really simplified things for me in the last project, since the DB changed on a whim - all you need to do is right click, "Update Model from Database" (at least in the old version that still used EDMXes), next, next, done.
You don't even need to query via EF after that - you can
new()those objects and toss them around just fine, though it might raise an eyebrow or two.As for the original question... it's a little confusing. @Captain, two questions:
a) Is it a constant query?
b) Is it important that the objects be objects (i.e. strongly typed)?Because from what I read, you want to have a strongly-typed object based on the result on a stringly-typed query, and that just ain't gonna work. If I were you, I'd simply fuck reflection and all that crap and pushed all the data into a simple
Dictionary<string, object>- you can pretty-print that just fine, hell, it'll probably be easier.Mucking around with reflection and runtime type defining will be pretty pointless, since in the end you'll still be accessing the keys via strings - you don't get any of the compile-time strong typing benefits if the compiler doesn't know about your type, duh.
-
Entity Framework effectively does that for you - you can simply point the designer to the database and have it pull the schema and autogenerate simple objects from it. It really simplified things for me in the last project, since the DB changed on a whim - all you need to do is right click, "Update Model from Database" (at least in the old version that still used EDMXes), next, next, done.
I wouldn't mind having a design-time way of automating that. Generate the code, then fuck off. Because otherwise something is going to break. Such as...
You don't even need to query via EF after that - you can new() those objects and toss them around just fine, though it might raise an eyebrow or two.
If that's anything at all like Microsoft Report Designer, then it's going to break. You do the same thing-- create a query or point it at a table, and it'll generate an object you can use when designing the form.
Except half of the time, the data source and/or data connection wouldn't work right, so it wouldn't connect to the db.
Then if you wanted to change the fields being selected, it was an absolute nightmare to modify the data object.
And if you did manage to change the data object, sometimes the data connector just would never update, and you couldn't use the new field in your report. Sometimes restarting visual studio would work. Sometimes not.
And if the db schema ever changed-- or if you tried to deploy the object to a copy of the db that didn't have the field-- EXPLODE.
So I don't like that very, very, very fragile link between code and DB. Code generation? OK. Coupling? No.
