R – How to return a Dataset using Subsonic 3

datasetsubsonic-active-recordsubsonic3

I have some old code that was using Subsonic 1.x and want to migrate to 3. Some of my old methods used to return a Dataset using the old Subsonic Query object and then just calling ExecuteDataset().

I still need to support those methods, since they're called by other code…however, I can't find anywhere how to to a code query with Subsonic that will let me return a Dataset. Or is that completely gone??

Can anyone help? Thank you!

Best Answer

You can return execute a Reader and then Load the data from the reader to the datatable, something like this:

    SubSonic.Query.SqlQuery qry= new Select().From<Evento>().Where(EventosTable.FechaInicioColumn).IsEqual(3);
    System.Data.IDataReader reader = qry.ExecuteReader();
    System.Data.DataTable table = new System.Data.DataTable();
    table.Load(reader);

Related Solutions

Inserting a new record pattern in SubSonic 3

First and foremost: the t4 templates are there for you to change as needed with SS3. That was the main idea with using T4 - I don't want to back you into my silliness :).

To the question at hand - I think this might be a bug in our templates that refuses to stick a value into the PK field:

    ISqlQuery BuildInsertQuery(T item) {
        ITable tbl = _db.FindTable(typeof(T).Name);
        Insert query = null;
        if (tbl != null) {
            var hashed = item.ToDictionary();
            query = new Insert(_db.Provider).Into<T>(tbl); ;
            foreach (string key in hashed.Keys) {
                IColumn col = tbl.GetColumn(key);
                if (!col.IsPrimaryKey) {
                    query.Value(key, hashed[key]);
                }
            }
        }
        return query;
    }

In this, our check should actually be...

                if (!col.IsPrimaryKey && !col.AutoIncrement) {
                    query.Value(key, hashed[key]);
                }

In this way, the non-identity will be inserted. But in reading your issue here, it sounds to me like you're not trying to insert into a non-identity.

The email you sent me doesn't say anything about PKs as identity - your PK was a thing called "NAme" which is a string type and not and identity (auto-increment).

I'm wondering about when I cannot get around this issue--when I have to add a new record to the database and have an identity as my primary key.

This is what SubSonic assumes - that your PK is an IDENTITY column. If you ONLY have an IDENTITY column, we can't help you because this is a deadlocked table in that you can't insert any value into it, therefor you can't tick the IDENTITY column. Your only recourse at this point is to SET IDENTITY INSERT="off", which defeats the purpose.

Hopefully this will answer your question? If I'm not getting it - can you do this for me:

One sentence: what can't you do and what's the error
What did you expect

Thanks Will and I hope I'm not being thick.

Sql – Subsonic 3, SimpleRepository, SQL Server: How to find rows with a null field

You can use:

var visits = myRepository.All<Visit>().Where(x => x.SynchronizedOn == null).ToList();

Best Answer

Related Solutions

Inserting a new record pattern in SubSonic 3

Sql – Subsonic 3, SimpleRepository, SQL Server: How to find rows with a null field

Related Topic