Problems assigning nulls to TypedView values

Posts   
 
    
JSobell
User
Posts: 136
Joined: 07-Jan-2006
# Posted on: 04-Mar-2022 06:51:09   

I posted something about this a few years ago, but I'm wondering if it needs 'fixing' or if there's a reason it is the way it is.

Here's the code generated for a date field in a TypedView. The underlying database field is nullable, but the property type here is DateTime, so the only way to get Null in there is to call SetExpectedPaymentDateNull. This is extremely cludgy in code, particularly when populating many properties in a TypeViewRow. If the column is marked as Optional in the TypedView definition should this not be DateTime? with a conditional setter such as set { this[_parent.ExpectedPaymentDateColumn] = value ?? System.Convert.DBNull; } ?

I can use myNewRow["ExpectedPaymentDate"] = src.ExpectedPaymentDate ?? System.Convert.DBNull; in my data transfer process but I hate using the string identifier for the column name.

Cheers, Jason

        public System.DateTime ExpectedPaymentDate 
        {
            get { return IsExpectedPaymentDateNull() ? (System.DateTime)TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)) : (System.DateTime)this[_parent.ExpectedPaymentDateColumn]; }
            set { this[_parent.ExpectedPaymentDateColumn] = value; }
        }

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38963
Joined: 17-Aug-2003
# Posted on: 04-Mar-2022 10:14:24   

I recon this is a typed view based on a datatable (the original format we've generated typedviews since the beginning) ? The main reason this is done this way is to stay in sync with how typed datasets are doing this. This feature was designed in 2002, in the 'glory days' of typed datasets and we basically used that design for the typedview (and typed list) classes, as people would know what to do as typed datasets use the same pattern.

Nowadays no-one has even heard of typed datasets anymore of course but we kept the design because it would potentially break a lot of code if you change it. Instead we added an alternative for typed views, namely poco classes which are more in line with what people use nowadays. You can change to poco classes either at the project settings level, in the LLBLGen Pro Runtime Framework settings, or per typed view in their own settings, if you want to migrate away from the legacy setup. See for more info https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/Using%20TypedViews,%20TypedLists%20and%20Dynamic%20Lists/gencode_usingtypedview_adapter.htm#choosing-the-typed-view-class-type

Frans Bouma | Lead developer LLBLGen Pro
JSobell
User
Posts: 136
Joined: 07-Jan-2006
# Posted on: 05-Mar-2022 08:47:36   

Yes, it is indeed based on the DataTable, because amazingly enough the DataTable is the most efficient way I have found of pushing bulk data to SQL Server. I'm using a SqlBulkCopy instance to write tens of thousands of lines in a single call, and that takes a DataTable, array of DataRows, or DbDataReader/IDataReader as a parameter. Any structure supporting any of these interfaces will work fine, but the DataTable based TypedView obviously can be used without any modification. I can understand the desire to drop this antiquated feature (which I only ever use for this specific purpose) but is there an alternative process using the new POCO based implementation that doesn't require writing dozens of mapping systems to convert them to something SqlBulkCopy compatible?

JSobell
User
Posts: 136
Joined: 07-Jan-2006
# Posted on: 05-Mar-2022 11:24:21   

Well it looks like this might work. I haven't tested it fully, but the idea looks sound. It's not ideal, as it uses reflection to populate the DataTable, but that's still relatively quick compared to the database write:

    public DataTable CreateDatatable<T>(IEnumerable<T> data) where T:class
    {
        DataTable datatable = new DataTable();
        var properties = typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);
        foreach (var p in properties)
        {
            DataColumn dc = new DataColumn(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType);
            datatable.Columns.Add(dc);
        }
        foreach (T row in data)
        {
            var newRow = datatable.NewRow();
            newRow.ItemArray = properties.Select(p => p.GetValue(row) ?? Convert.DBNull).ToArray();
            datatable.Rows.Add(newRow);
        }
        return datatable;
    }

This can be called with

            var testdata = db.typedListAccount.ToList();
            var dt = CreateDatatable<typedListAccountRow>(testdata);
JSobell
User
Posts: 136
Joined: 07-Jan-2006
# Posted on: 06-Mar-2022 05:54:55   

So the only downsides of this approach are:

  1. You must disable the CamelCase in the designer, because the column names don't match and you can't use ordinal positions because:

  2. There is no underlying ordinal data associated with the properties in the POCO, so you have to write superfluous looking mapping values to get the BCPCopy to use names instead of the default positions.

For anyone wanting to use this method, here's an example of how you call the remote server:

    public void BulkUpload<T>(IEnumerable<T> dataArray, string destinationTableName) where T:class
    {
        var datatable = CreateDatatable(dataArray);
        using var bulkCopy = new SqlBulkCopy(_databaseAdapter.ConnectionString);
        foreach(DataColumn col in datatable.Columns)
            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
        bulkCopy.DestinationTableName = destinationTableName;
        bulkCopy.WriteToServer(datatable);
    }
Otis avatar
Otis
LLBLGen Pro Team
Posts: 38963
Joined: 17-Aug-2003
# Posted on: 06-Mar-2022 10:59:51   

I think the mechanism we already have is sufficient too simple_smile If you use the PocoWithQuerySpecQuery output type for the typedview, you get a poco and also you can fetch the query on the typedview as a datatable.

var qf = new QueryFactory();
var q = qf.Invoices
            .From(QueryTarget.InnerJoin(qf.Order)
                .On(InvoicesFields.OrderId.Equal(OrderFields.OrderId)))
            .Where(OrderFields.EmployeeId == 2);
var resultsAsDataTable = new DataAccessAdapter().FetchQueryAsDataTable(q);   

This will automatically build a datatable based on the projection in the query.

For Linq we don't have this mechanism, sadly. The advantage of the queryspec route is that you don't have to project the data first to poco instances, it's directly fetched into a datatable with columns based on the resultset. There's also an overload which accepts a pre-defined datatable if you want.

Frans Bouma | Lead developer LLBLGen Pro
JSobell
User
Posts: 136
Joined: 07-Jan-2006
# Posted on: 07-Mar-2022 11:50:01   

Yes, that would have been handy to know about before I wrote the Linq equivalent. I may switch it over at some point.

Cheers, Jason