How to turn a sql record into an object



  • Okay, maybe this is a really basic question, but I never work at this layer. Suppose you wrote a long, complicated SQL query, with lots of fields (and you put in field names). Suppose that you could pick the language you wanted (I'm leaning towards Python, or maybe C#) to wrap the query. What "product" would you use the wrap the query? I just want to pass in a sql query, and have the "product" return an array/list of objects for me to pretty print.

    If I went with C#, I think I'd use Entity Framework to turn each result row into an object, and transform it into text, etc. (C# might not be a possibility, though -- I think the OBDC driver for the database engine I'm using is way out of date).

    Is there an "equivalent" Python thing? I just want to pass in a sql query, and have it return an array/list of objects for me to pretty-print.



  • Entity Framework will generate a usable c# object,

    You can use a sql reader to loop over the record set and read into an object (you can also automate this by defining sql data types to c# data types, so string = varchar/nvarchar)

    You can use linq to sql

    There's lots of choices.

    Entity framework is the easiest if you're on .Net 4+ because you can generate everything automatically from the database, but it has some oddities to saving, reading is pretty safe though.



  • If you're using raw sql (not entity framework)

    Enjoy discosyntax

    public class demo
            {
                public int ID { get; set; }
                public int? NullableID { get; set; }
                public DateTime RecordDate { get; set; }
                public string Name { get; set; }
                public string Address { get; set; }
            }
    
    var connString = "";
                using (var conn = new SqlConnection(connString))
                {
                    var sql = "select * from table";
                    conn.Open();
                    using (var command = new SqlCommand(sql, conn))
                    {
                        // Execute only, IE: Insert = command.ExecuteNonQuery();
                        // Otherwise, execute command and get reader data
                        using (var reader = command.ExecuteReader())
                        {
                            var demoObj = new demo();
                            while (reader.Read())
                            {
                                demoObj.ID = reader.GetInt32(0);
                                demoObj.NullableID = reader.GetValue(1) as int?;
                                demoObj.RecordDate = reader.GetDateTime(2);
                                demoObj.Name = reader.GetString(3);
                                demoObj.Address = reader.GetString(4);
                            }
                        }
                    }
                }
    


  • Python has modules for pretty much every DBMS. Typically, your code will be something like this:

    conn = db.connection()
    cursor = conn.cursor()
    cursor.execute(sql)
    for row in cursor:
        # Do stuff with row, which a list of column values
    


  • EntityFramework will work for this. It's also way huger than you actually need, but meh. Not sure if you care about that. (Also: it will only work with stored procedures and views, so your query has to be in that format.)

    If literally all you're doing is pretty-print, you could just manually assemble the object using an SQLReader, and avoid the megabytes of EF code. EDIT: like matches' example.

    Or you could look for a micro-ORM. There's about 47 kajillion of those.



  • 'Way Huger' can be a relative concept. Depending on quick and dirty requirements, and if the columns truly are in the hundreds, it's potentially quite a time save. For a handful of fields, it's pretty meh.



  • Yeah, but if he can't make his query into a stored procedure, then it becomes a huge PITA in EF. So. Tradeoffs.



  • Yeah, I don't care how big the application is. (In this context) Developer time is much more important than megabytes. I'd rather avoid doing anything manually, especially if code can do it for me.

    The stored procedure/view requirement takes EF off the table, though. 😦



  • I can't recommend any of these, sorry.



  • Fair enough, still a good keyword to look at, thanks.



  • Closed as "not constructive" obviously. We wouldn't want there to be actually useful information on StackOverflow!


  • Discourse touched me in a no-no place

    @Captain said:

    Suppose you wrote a long, complicated SQL query, with lots of fields (and you put in field names). Suppose that you could pick the language you wanted (I'm leaning towards Python, or maybe C#) to wrap the query. What "product" would you use the wrap the query? I just want to pass in a sql query, and have the "product" return an array/list of objects for me to pretty print.

    That's a piece of cake in Java, so assume it's the same in something like C# too.



  • For reading, you don't need stored procedure.

    Follow these screenshots: (VS 2013, YMMV)

    DB

    create database testdb
    go
    use testdb
    go
    create table dbo.testdb
    (
    	ID int not null identity(1, 1),
    	fake int null,
    	blah nvarchar(255) not null
    )
    insert into dbo.testdb (fake, blah) values (null, 'first')
    insert into dbo.testdb (fake, blah) values (2, 'second')
    
    1. Create a new project (VS 2013)

    2. Right click project, nuget, add entity framework

    3. Right click project -> add -> new item -> data -> ADO.NET Entity Data Model (mine is called entities)

    4. EF Designer from database

    5. New connection (Your connection string) - Save App.Config

    6. Uncheck pluralize and select your table -> finish


  • Discourse touched me in a no-no place

    @blakeyrat said:

    We wouldn't want there to be actually useful information on StackOverflow!

    :doing_it_wrong:



  • Pretty super. I think I have to use VS2013 to connect to this database anyway. Thanks!



  • ... and then?

    You missed the part where you put the query into EF and get POCO as your result. So far all you've done is given it a connection string.

    EDIT: plus we don't know if the result of captain's query lines up with the schema of a specific table. So he might have to make a "junk table" of the correct schema. And if he could do that, he could install it as a sproc. So.



  • My bad.

    1. Open the designer (it should open by default - if you selected your tables, it should be there.
      1b. If not, right click -> Update model from database (select tables) and finish
    static void Main(string[] args)
            {
                using (var entity = new Entities())
                {
                    var tableData = entity.testdb.Where(x => x.fake == 2).ToList().First().ID;
                    Console.WriteLine(tableData);
                }
     Console.ReadLine();
            }
    


  • ... and then?

    You're still not executing his query and turning the result into a POCO. One of us is confused about the requirements here.



  • tableData is a c# object. He wants to pretty print the data, include JSON.net and just Console.WriteLine(JsonConvert.SerializeObject(tableData))

    [EDIT] Since you (blakey) seem pretty lazy,

    class Program
        {
            static void Main(string[] args)
            {
                using (var entity = new Entities())
                {
                    var tableData = entity.testdb.Where(x => x.fake == 2).ToList();
                    Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented));
                }
                Console.ReadLine();
            }
        }
    


  • @Matches said:

    tableData is a c# object.

    Yes, but it isn't the results of his SQL query, is it? Your code is fine, but it relies on the data Captain wants already being in a SQL table.

    Let's take this from the top:

    • Captain has read access to a database (no access to create stored procedures or views)
    • Captain has a SQL query he wants to run on the database
    • The SQL query produces many rows of results, which may or may not match the schema of any tables in the database
    • Captain wants to execute the query, then pretty-print the results

    You're not even close to meeting those requirements. Unless I TOTALLY misunderstand the problem here.

    Doing the bulleted points there using EntityFramework is nightmarishly difficult, because you'd have to override pretty much 100% of its auto-generation stuff.

    If Captain could install his query as an sproc, he'd be in good shape since EF is capable of automatically generating a POCO from the output of a sproc. But he can't, so that's out the window.



  • Entity framework supports raw sql,

    static void Main(string[] args)
            {
                using (var entity = new Entities())
                {
                    //var tableData = entity.testdb.Where(x => x.fake == 2).ToList();
                    var tableData = entity.testdb.SqlQuery("select * from dbo.testdb"); // This supports parameters too.
                    Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented));
                }
                Console.ReadLine();
            }
    


  • Yes but we don't know that the output of Captain's query matches the schema of any of the tab-- you know what, I give up. I've explained this all like 3 times.



  • Are you complaining about lazy loading? Because that's why .ToList() is called.



  • No; I'm complaining that your solution doesn't satisfy the requirements of the problem.

    I've never said your code is wrong or incorrect or stupid; it's not. I've only been saying it doesn't fulfill the requirements.



  • @Matches said:

    If you're using raw sql (not entity framework)

    Enjoy discosyntax

    public class demo
            {
                public int ID { get; set; }
                public int? NullableID { get; set; }
                public DateTime RecordDate { get; set; }
                public string Name { get; set; }
                public string Address { get; set; }
            }
    
    var connString = "";
                using (var conn = new SqlConnection(connString))
                {
                    var sql = "select * from table";
                    conn.Open();
                    using (var command = new SqlCommand(sql, conn))
                    {
                        // Execute only, IE: Insert = command.ExecuteNonQuery();
                        // Otherwise, execute command and get reader data
                        using (var reader = command.ExecuteReader())
                        {
                            var demoObj = new demo();
                            while (reader.Read())
                            {
                                demoObj.ID = reader.GetInt32(0);
                                demoObj.NullableID = reader.GetValue(1) as int?;
                                demoObj.RecordDate = reader.GetDateTime(2);
                                demoObj.Name = reader.GetString(3);
                                demoObj.Address = reader.GetString(4);
                            }
                        }
                    }
                }
    ```</blockquote>
    
    Ordinals? Yuck!
    
    @Matches <a href="/t/via-quote/54012/23">said</a>:<blockquote>Are you complaining about lazy loading? Because that's why .ToList() is called.</blockquote>
    
    No, what @blakeyrat says is that TS has a *custom* query which gives some results. Maybe it contains a JOIN or something, which would produce an object of an entirely different type. You can (re)write that in LINQ, of course, but that's probably not what OP wants.
    
    Idea: create a stored procedure out of the query, and add that SP to EF's designer. Then just invoke the stored procedure like a method, and you'll get a list of objects.
    
    edit: Fuck you, DickHorse, with your crappy MD5-riddled Markdown parser which behaves differently when in a blockquote. Fuck you.


  • " I just want to pass in a sql query, and have the "product" return an array/list of objects for me to pretty print."

    Where "product" is 'arbitrary code solution that executes my SQL that I pass in'

    I feel like what I've posted does exactly that.



  • I used ordinals instead of reader["key"] because it's faster to parse, but if the query is likely to change at some point for order, feel free to use the key.



  • @Matches said:

    I feel like what I've posted does exactly that.

    I suppose it's possible that the code in this post:

    @Matches said:

    using (var entity = new Entities())
    {
    //var tableData = entity.testdb.Where(x => x.fake == 2).ToList();
    var tableData = entity.testdb.SqlQuery("select * from dbo.testdb"); // This supports parameters too.
    Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented));
    }
    Console.ReadLine();

    does, but you certainly haven't demonstrated it. You're explicitly telling EF that it should expect the query to return "testdb" columns.



  • You realize that when you're using raw sql, you can do inner joins in any way that you want? You can call .ToList and cast as any arbitrary type using new <classobject>(x => field assignments)

    I feel like captain isn't an idiot, and can figure out exactly what he needs from the amount of information I've provided. You clearly have no concept of how entity framework works, which is cool, but fuck off in coming up with your derived problems that literally only exist in your head.



  • How do you tell EF to execute a query that returns an arbitrary schema?

    Not a schema which already exists as a table in the database. An arbitrary schema the query invents based on its SELECT line. Say, for example, the SELECT contains a bunch of CASEs.

    If you demonstrate that EF is capable of that, then I concede entirely. To my knowledge, it is not. (Well-- you could do code-first mode, and manually create your POCO and then tell EF about it, but that's more work than not using EF at all.)



  • Assuming you aren't trolling:

     static void Main(string[] args)
            {
                using (var entity = new Entities())
                {
                    //var tableData = entity.testdb.Where(x => x.fake == 2).ToList();
                    var tableData = entity.testdb.SqlQuery("select * from dbo.testdb").ToList().Select(x => new demo()
                    {
                        ID = x.ID,
                        NullableID = null,
                        RecordDate = DateTime.UtcNow,
                        Name = x.blah,
                        Address = x.fake.ToString()
                    }); // This supports parameters too.
                    Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented));
                }
                Console.ReadLine();
            }
    
            public class demo
            {
                public int ID { get; set; }
                public int? NullableID { get; set; }
                public DateTime RecordDate { get; set; }
                public string Name { get; set; }
                public string Address { get; set; }
            }
    


  • This post is deleted!


  • That still doesn't demonstrate what I'm asking for. You're still explicitly telling EF that the query returns the "testdb" type.


  • ♿ (Parody)

    What blakey is saying is that your example:
    @blakeyrat said:

    var tableData = entity.testdb.SqlQuery("select * from dbo.testdb");

    Doesn't give a lot of confidence to someone not familiar with EF that you could do this:

    var tableData = entity.testdb.SqlQuery(
        "select foo.baz, bar.bat, foo.name from foo join bar on bar.foo_id = foo.id ");
    

    ...and then get an object with baz, bat and name fields. As someone not familiar with EF, I agree with what he said.



  • That doesn't work as shown - that would be a compilation error. (Note: the below is an inner join)

    class Program
        {
            static void Main(string[] args)
            {
                using (var entity = new Entities())
                {
                    //var tableData = entity.testdb.Where(x => x.fake == 2).ToList();
                    var tableData = entity.testdb.SqlQuery("select * from dbo.testdb")
                        .Join(entity.testtable2.SqlQuery("select * from dbo.testtable2"), x => x.ID, y => y.ID, (x, y) =>
                            new demo()
                        {
                            ID = x.ID,
                            NullableID = null,
                            RecordDate = DateTime.UtcNow,
                            Name = x.fake == null ? y.Placeholder : x.blah,
                            Address = x.fake.ToString()
                        }).ToList(); // This supports parameters too.
                    Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented));
                }
                Console.ReadLine();
            }
    
            public class demo
            {
                public int ID { get; set; }
                public int? NullableID { get; set; }
                public DateTime RecordDate { get; set; }
                public string Name { get; set; }
                public string Address { get; set; }
            }
    

    @blakeyrat (ping)



  • Since Python is dynamic, you don't need a DAO or model. Launch the query as @Dragnslcr said and the collection of objects should be there.



  • Are you sure that this SQL is run directly against the database? IIRC, the whole idea of EF is that you can change providers while leaving your queries intact, so the query that you toss into SqlQuery would be 'Entity SQL', which is translated to the SQL dialect of your specific provider.



  • @Matches said:

    That doesn't work as shown

    Yes, that is kind of the point I've been laboring to make for what feels like 6 ice ages.



  • EF will lazy load data until an instruction is sent that says 'I need to run this now to determine the results) - In the example above, .ToList() is the trigger that makes the entire query run, instead of pieces lazy loaded. If it's more efficient to fetch a single item than bulk, don't call .ToList()

    When you say 'SqlQuery' you are overriding the default behavior of EF saying 'I know better than your code generation. Fuck off and run this'



  • Did you read the example? The example I provided WORKS AS SHOWN



  • Yes, I know. But that was not what I asked.

    The intent of my post was to ask: you don't write T-SQL, you write EntitySQL instead, which then gets translated to T-SQL when the query is executed. Right?



  • So your advice to Captain would be "totally and completely re-write your query so everything other than the simple SELECT bits is done in LINQ", which, again, takes significantly longer than simply not using EF in the first place.

    This is why I can't get along with other programmers.



  • I wrote T-Sql, which isn't translated because I'm passing a raw query.



  • Great, thanks!

    Now do this one:

    SELECT  c.[Name] AS [Customer Name], 
            p.[Name] AS [Product Name]
    FROM    [CustomerOrders] co
    JOIN    [Customers] c ON c.[CustomerId] = co.[CustomerId]
    JOIN    [Orders] o ON o.[OrderId] = co.[OrderId]
    JOIN    [OrderLines] ol ON ol.[OrderId] = o.[OrderId]
    JOIN    [Products] p ON p.[ProductId] = ol.[ProductId]
    WHERE   c.[Name] = 'blakeyrat'
    


  • Bitch bitch bitch,

    static void Main(string[] args)
            {
                using (var entity = new Entities())
                {
                    //var tableData = entity.testdb.Where(x => x.fake == 2).ToList();
                    var tableData = entity.Database.SqlQuery<demo>("select * from dbo.testdb as a inner join dbo.testtable2 as b on a.id = b.id").ToList();
                    Console.WriteLine(JsonConvert.SerializeObject(tableData, Formatting.Indented));
                }
                Console.ReadLine();
            }
    
            public class demo
            {
                public int ID { get; set; }
                public int? NullableID { get; set; }
                public DateTime RecordDate { get; set; }
                public string Placeholder { get; set; }
                public string Address { get; set; }
            }
    

    ** Important note here: The field names must match exactly, otherwise the data doesn't populate. You should make sure your fields are specified correctly here.



  • Ok; now you're getting close. But you've yet to explain how this is faster for Captain than not using EF at all.

    I already brought up the fact that if you do it this way you need to create your own POCO manually, which defeats like 85% of the point of using an ORM in the first place.



  • Because this allows you to create a dynamic object tabledata (uncasted as anything) and dynamically create an object at run time that matches the schema, and pretty prints the result. It would take ~20 lines of code



  • If you had just demonstrated that like 45 posts ago, this conversation might have gone much smoother. Lo and behold, that seems to actually match Captain's requirements. Give yourself a pat on the back.



  • I give captain credit for being smarter than you.



  • That's true, I'm such a stupid dunderhead stinky brain, that must be the problem here.


Log in to reply