Posted on behalf of user toddgleason :

At first we tried the solution mentioned, involving aSqlServerProviderHelper (not sure if I got that class name right). This actually worked fine for most tables,but for any tables with XML columns, those would no longer come back correctlyfrom the DB. They needed to be encoded in NVARCHAR and no longer were.

 

 

 

  

 

        ///<summary>

 

        /// Thismethod is not intended to be called directly from your code.

 

        ///</summary>

 

        ///<param name="pDataSet">The dataset holding fetcheddata</param>

 

        ///<param name="pDataSourceKey">The data source key containingconnection information for the datasource.</param>

 

        ///<remarks>

 

        /// Thismethod executes on the server side of the Persistence divide to

 

        ///retrieve data from the backend datasource.

 

        ///</remarks>

 

        publicoverride void Fetch(DataSet pDataSet, IDataSourceKey pDataSourceKey)

 

        {

 

           AdoHelper adoHelper = ((RdbKey)pDataSourceKey).AdoHelper;

 

           RdbQuerySqlFormatter formatter = new RdbQuerySqlFormatter(adoHelper,(base.QueryStrategy == null) ? null : base.QueryStrategy.TransactionSettings);

 

           CanonicalSqlQuery pQuery = formatter.BuildCanonicalQuery(this);

 

           ParameterizedSql pParamSql = formatter.BuildSqlSelect(pQuery);

 

 

 

            // SQLhas an issue with sending varchar columns as nvarchar; see

 

            // http://lostechies.com/jimmybogard/2012/07/18/troubleshooting-sql-index-performance-on-varchar-columns/

 

            // Aglobal type mapping change can be done,

 

            // butit fails because XML columns need to be sent in Unicode instead of using the DBcollation, and conversion to ANSI breaks compatibility with such columns.

 

            // Sothe solution is to instead, at fetch time, replace parameters marked asNVarChar with VarChar

 

            // It'sunlikely we actually query by those XML parameters so this should be relativelysafe.  This only affects

 

            // theparameters sent out in the query, not the return type mappings.

 

            // Oneother thing we do is that in our own PersistenceManager subclass's GetEntity()/GetEntities()calls, we "promote" all

 

            //EntityQuery and RdbQuery objects to our derived objects, to guarantee invokingthis class.

 

            foreach(var param in pParamSql.Parameters)

 

            {

 

                if(param.DbType == DbType.String)

 

                   param.DbType = DbType.AnsiString;

 

            }

 

 

 

            using(formatter)

 

            {

 

               formatter.Fetch(base.EntityType, pDataSet, pParamSql,this.CommandTimeout);

 

               this.FetchSpans(pQuery, pDataSet, adoHelper, formatter);

 

                if(base.ContainsSubquery && !this.SuppressQueryInversion)

 

                {

 

                   this.FetchInverted(pDataSet, formatter, this);

 

                }

 

            }

 

        }

 

 

 

You may also note that in here we could put extraintelligence in if we needed to apply this logic conditionally, such as byparameter name, or we could add to the query class to control the behavior. 

 

The resulting solution has zero effect on the data typetransfer in either direction; it only affects the query parameters, which isexactly what we want.

 

The code snippet in the second post in this thread is actually complete - you can copy the SampleProviderHelper class into a file, add the file to a project, and be sure the project's assembly is included in the probe assemblies for your RdbKey.  DevForce will use your implementation if its found.  There are a number of other members which can be overridden too.

 

If you're running into problems please provide more specifics on what you need to do or what's not working.

 

In ADO.NET the "String" CLR type is considered a Unicode string, which is then interpreted as an nvarchar database type. So DevForce is just allowing this default to occur.   You can work around the problem with a custom "AdoProviderHelper" which will allow you to take control of the database type.

If you're using the SQLOLEDB provider you can implement the helper like this: