locked
Custom IDbConnection with DataContext RRS feed

  • Question

  • Hi, DataContext takes a IDbConnection as a constructor but when I try to create my own implementation as below and pass it to the constructor it complains that it is not of (sealed) SqlConnection or string...what can I do?I want to execute the REVERT sql  command before connection pooling calls sp_reset...my inspiration came from http://www.eggheadcafe.com/software/aspnet/31897235/connection-pooling-revert---connection-reset-in-net-20-sp1.aspx

    please help!!
    Oli

    class CCConnection : IDbConnection, IDisposable {

            public IDbConnection connection;

            public CCConnection() {
                this.connection = new SqlConnection(this.ConnectionString);
            }

            #region IDisposable Members

            public void Dispose() {
                if (connection.State == ConnectionState.Open) {
                    IDbCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "REVERT";
                    cmd.ExecuteNonQuery();
                }
                connection.Dispose();
            }
            #endregion

            #region IDbConnection Members
            public IDbTransaction BeginTransaction(IsolationLevel il) {
                return connection.BeginTransaction(il);
            }

            public IDbTransaction BeginTransaction() {
                return connection.BeginTransaction();
            }

            public void ChangeDatabase(string databaseName) {
                connection.ChangeDatabase(databaseName);
            }

            public void Close() {
                connection.Close();
            }

            public string ConnectionString {
                get { return "Data Source=.blahblahbah  ; }
                set { connection.ConnectionString = value; }
            }

            public int ConnectionTimeout {
                get { return connection.ConnectionTimeout; }
            }

            public IDbCommand CreateCommand() {
                return connection.CreateCommand();
            }

            public string Database {
                get { return connection.Database; }
            }

            public void Open() {
                connection.Open();
            }

            public ConnectionState State {
                get { return connection.State; }
            }
            #endregion
        }
    }

     

    Tuesday, February 15, 2011 4:58 PM

Answers

  • OK....I needed to inherit from the abstract class DbConnection rather than its' interface counterpart....then all is well...d'oh! see amended class below. This should be useful for anyone who might want to use a single login to sql server database and multiple "users without logins", typically (although not in my case) utilizing application roles in sql server..what this custom connection allows me to do is revert the user context of the connection before it is subjected to sp_reset by the connection pooling, thus avoiding sql error 18059 : The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context..

    I've not thoroughly tested this but it appears to work.

     

    For completeness, I am not using application roles (as I would have to specify the password in the connection string). I have a sql server login mapped to the virtual Application pool identity in IIS 7...

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

    namespace CC.Linq {
        internal class CCConnection : DbConnection, IDisposable {

            private SqlConnection connection;

            public CCConnection() {
                this.connection = new SqlConnection(this.ConnectionString);
            }

            #region DbConnection Members
            public void Dispose() {
                if (connection.State == ConnectionState.Open) {
                    IDbCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "REVERT";
                    cmd.ExecuteNonQuery();
                }
                connection.Dispose();
            }
            #endregion

            #region DbConnection Members
            public DbTransaction BeginTransaction(IsolationLevel il) {
                return connection.BeginTransaction(il);
            }

            public DbTransaction BeginTransaction() {
                return connection.BeginTransaction();
            }

            public override void ChangeDatabase(string databaseName) {
                connection.ChangeDatabase(databaseName);
            }

            public override void Close() {
                var cmd = connection.CreateCommand();
                cmd.CommandText = "REVERT";
                cmd.ExecuteNonQuery();
                connection.Close();
            }

            public override string ConnectionString {
                get { return "Data Source=.\\SQLEXPRESS;Initial Catalog=YourDb;Integrated Security=SSPI;"; }
                set { connection.ConnectionString = value; }
            }

            public override int ConnectionTimeout {
                get { return connection.ConnectionTimeout; }
            }

            public DbCommand CreateCommand() {
                return connection.CreateCommand();
            }

            public override string Database {
                get { return connection.Database; }
            }

            public override void Open() {
                connection.Open();
                var c = connection.CreateCommand();
                c.CommandText = "EXECUTE AS USER='someuser'";
                c.ExecuteNonQuery();
            }

            public override ConnectionState State {
                get { return connection.State; }
            }
            #endregion

            protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) {
                return connection.BeginTransaction();
            }

            protected override DbCommand CreateDbCommand() {
                return connection.CreateCommand();
            }

            public override string DataSource {
                get { return connection.DataSource; }
            }

            public override string ServerVersion {
                get { return connection.ServerVersion; }
            }
        }
    }

    • Marked as answer by dogzb0110x Tuesday, February 15, 2011 11:16 PM
    Tuesday, February 15, 2011 11:16 PM

All replies

  • OK....I needed to inherit from the abstract class DbConnection rather than its' interface counterpart....then all is well...d'oh! see amended class below. This should be useful for anyone who might want to use a single login to sql server database and multiple "users without logins", typically (although not in my case) utilizing application roles in sql server..what this custom connection allows me to do is revert the user context of the connection before it is subjected to sp_reset by the connection pooling, thus avoiding sql error 18059 : The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context..

    I've not thoroughly tested this but it appears to work.

     

    For completeness, I am not using application roles (as I would have to specify the password in the connection string). I have a sql server login mapped to the virtual Application pool identity in IIS 7...

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

    namespace CC.Linq {
        internal class CCConnection : DbConnection, IDisposable {

            private SqlConnection connection;

            public CCConnection() {
                this.connection = new SqlConnection(this.ConnectionString);
            }

            #region DbConnection Members
            public void Dispose() {
                if (connection.State == ConnectionState.Open) {
                    IDbCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "REVERT";
                    cmd.ExecuteNonQuery();
                }
                connection.Dispose();
            }
            #endregion

            #region DbConnection Members
            public DbTransaction BeginTransaction(IsolationLevel il) {
                return connection.BeginTransaction(il);
            }

            public DbTransaction BeginTransaction() {
                return connection.BeginTransaction();
            }

            public override void ChangeDatabase(string databaseName) {
                connection.ChangeDatabase(databaseName);
            }

            public override void Close() {
                var cmd = connection.CreateCommand();
                cmd.CommandText = "REVERT";
                cmd.ExecuteNonQuery();
                connection.Close();
            }

            public override string ConnectionString {
                get { return "Data Source=.\\SQLEXPRESS;Initial Catalog=YourDb;Integrated Security=SSPI;"; }
                set { connection.ConnectionString = value; }
            }

            public override int ConnectionTimeout {
                get { return connection.ConnectionTimeout; }
            }

            public DbCommand CreateCommand() {
                return connection.CreateCommand();
            }

            public override string Database {
                get { return connection.Database; }
            }

            public override void Open() {
                connection.Open();
                var c = connection.CreateCommand();
                c.CommandText = "EXECUTE AS USER='someuser'";
                c.ExecuteNonQuery();
            }

            public override ConnectionState State {
                get { return connection.State; }
            }
            #endregion

            protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) {
                return connection.BeginTransaction();
            }

            protected override DbCommand CreateDbCommand() {
                return connection.CreateCommand();
            }

            public override string DataSource {
                get { return connection.DataSource; }
            }

            public override string ServerVersion {
                get { return connection.ServerVersion; }
            }
        }
    }

    • Marked as answer by dogzb0110x Tuesday, February 15, 2011 11:16 PM
    Tuesday, February 15, 2011 11:16 PM
  • Hi dogzb0110x,

     I’m glad to hear that you got it working. Thank you for sharing your experience here.

    It will be very beneficial for other community members having the similar questions.

     

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 16, 2011 1:32 AM