Extending SqlDataProvider

listonic · Post by **listonic** » Tue 06 Sep 2011 08:48

I want to extend SqlDataProvider. I need to ovverride CreateCommand method so it creates implementation of SqlCommand that includes retry logic.

Tried to do something like this :

public class ReliableSqlDataProvider : SqlDataProvider
{
protected override IDbCommand CreateCommand(string commandText, IDbConnection connection, bool forBatch)
{
ReliableSqlCommand cmd = new ReliableSqlCommand();
cmd.Connection = connection;
cmd.CommandTimeout = GetCommandTimeout(connection, 15);
cmd.CommandText = commandText;
return cmd;
}
}

The problem is that Linq Connect seems to assume that transaction is assigned to SqlCommand in this method, as default implementation of this method seems to be as follows :

protected internal override IDbCommand CreateCommand(string commandText, IDbConnection connection, bool forBatch)
{
SqlCommand command = (SqlCommand) base.CreateCommand(commandText, connection, forBatch);
if (command == null)
{
command = new SqlCommand(commandText, (SqlConnection) connection);
}
command.Transaction = (SqlTransaction) this.a();
command.CommandTimeout = this.CommandTimeout;
return command;
}

The problem is that method for transaction retrieval seems to be internal or private.

How can i retrieve Transaction information from SqlDataProvider ?

Regards
Piotr Wójcicki

StanislavK · Post by **StanislavK** » Fri 09 Sep 2011 16:50

You can execute the base implementation of the method and then copy the needed properties from the command returned by it:

Code: Select all

var command = base.CreateCommand(commandText, connection, forBatch);
ReliableSqlCommand cmd = new ReliableSqlCommand(); 
cmd.Connection = command.connection;
...

Could you please describe the functionality you want to implement by modifying the SqlDataProvider class in more details?

listonic · Post by **listonic** » Fri 09 Sep 2011 20:01

Thank you for your response.

I am trying to use the library : http://code.msdn.microsoft.com/Transien ... g-b209151f with Linq Connection.

We are using Sql Azure as our database. The problem is that sql azure may drop connection at any time due to load balacing or failures.

Becuase of that i want to incorporate retry logic for creating connection and executing commands.

This library provides ReliableSqlAzureConnection which implements IDbConnection - this allows retry during connection opening (timeout may happend during connection opening)

This library also provides extension methods for SqlCommand. Instead of ExecuteReader we have ExecuteReaderWithRetry. Becyase of that i wanted to create my implementation of SqlCommand which would invoke extension methods instead of standard SqlCommand Execute connections. Your solution for SqlCommands seems reasonable and i will try to use it on monday.

listonic · Post by **listonic** » Mon 12 Sep 2011 06:55

I have one more problem regarding custom DataProvider. Using entity developer i created DataContext. It uses SqlDataProvider. What should i do to make this datacontext to use my custom DataProvider each time it generates code after some changes ? I can modify the Context.Disigner.cs file manualy to set up new dataprovider, but this file is replaced next time i modify datacontext.

Regards
Piotr Wójcicki

StanislavK · Post by **StanislavK** » Mon 12 Sep 2011 16:52

Thank you for the clarification.

To change the provider set in the generated code, you can, e.g., modify the template. Provided that you are using the 'LinqConnect' code generation template, you can, e.g., replace the following line

Code: Select all

[ProviderAttribute(typeof())]

by

Code: Select all

[ProviderAttribute(typeof(MyAssembly.MyDataProvider))]

(set the namespace and class name of your custom data provider instead of MyAssembly.MyDataProvider here).

Please tell us if this helps.

listonic · Post by **listonic** » Thu 15 Sep 2011 13:20

It is helpfull partialy. I do not known where should i modify the template you mentioned.
I found file DevartLinqConnection.zip in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\ItemTemplates\CSharp\Data but modifying its content does not seem to have any effect. It does also not contain string [ProviderAttribute(typeof())].

Regards
Piotr Wójcicki

StanislavK · Post by **StanislavK** » Tue 20 Sep 2011 16:51

Sorry for the delay. The active templates are available in the 'Templates' node of the Model Explorer tool window. To modify a template, please select the 'Copy to Model Folder' item of its context menu, and then double-click the template.

For the detailed information about the code generation templates, please refer to the 'Common Concepts' -> 'Working with Templates' section of the Entity Developer documentation.

listonic · Post by **listonic** » Thu 22 Sep 2011 10:15

Ok finally got it. Had older version installed (2.20) so there was no template section.

One more thing i could suggest:
Please add ability to change conncetionstringname in entity developer. I can change it in edps file, but it would be nice to be able to change it through ui.

Regards
Piotr Wójcicki

StanislavK · Post by **StanislavK** » Fri 23 Sep 2011 10:54

Thank you for your suggestion, we will implement this option. We will post here when the corresponding build is available.

StanislavK · Post by **StanislavK** » Wed 16 Nov 2011 18:11

We have implemented the possibility of specifying the name of the connection string saved to app.config. This change is available in the latest 3.0.14 build of LinqConnect. The new build can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial and free versions) or from Registered Users' Area (for users with active subscription only).

For the detailed information about the fixes and improvements available in LinqConnect 3.0.14, please refer to
http://www.devart.com/forums/viewtopic.php?t=22548