Adding Sybase 12.5.x DAAB Database Provider

Topics: Data Access Application Block
Mar 24, 2009 at 3:00 AM
Edited Mar 24, 2009 at 3:04 AM
I´m want extend DAAB adding Sybase Ado provider support. Currently I have AseDatabase, AseDatabaseAssembler and AseClientFactory classes, i try to do basing my code from this articles

http://entlib.codeplex.com/Thread/View.aspx?ThreadId=48745
http://msdn.microsoft.com/en-us/library/dd203348.aspx

but when try to compile from VS2008 Pro the compiler show me this error  message

cannot convert from 'Sybase.Data.AseClient.AseCommand' to 'System.Data.Common.DbCommand'

How I can solved this issue? Any idea or suggestion?

these are my classes code

//
// AseDatabaseAssembler
//
using System;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;

namespace Microsoft.Practices.EnterpriseLibrary.Data.Sybase
{
    /// <summary>
    /// Represents the process to build an instance of <see cref="AseDatabase"/> described by configuration information.
    /// </summary>
    public class AseDatabaseAssembler : IDatabaseAssembler
    {
        /// <summary>
        /// Builds an instance of <see cref="AseDatabase"/>, based on the provided connection string.
        /// </summary>
        /// <param name="name">The name for the new database instance.</param>
        /// <param name="connectionStringSettings">The connection string for the new database instance.</param>
        /// <param name="configurationSource">The source for any additional configuration information.</param>
        /// <returns>The new sql database instance.</returns>
        public Database Assemble(string name, ConnectionStringSettings connectionStringSettings, IConfigurationSource configurationSource)
        {
            return new AseDatabase(connectionStringSettings.ConnectionString);
        }
    }
}

//
// AseDatabase class
//
using System;
using System.Data;
using System.Data.Common;
using System.Security.Permissions;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Common.Instrumentation;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation;
using Microsoft.Practices.EnterpriseLibrary.Data.Properties;
using System.Transactions;
using Sybase.Data.AseClient;

namespace Microsoft.Practices.EnterpriseLibrary.Data.Sybase
{
    /// <summary>
    /// <para>Represents a Sybase Ase database.</para>
    /// </summary>
    /// <remarks>
    /// <para>
    /// Internally uses Ase Data Provider from Sybase (Sybase.Data.AseClient) to connect to the database.
    /// </para>  
    /// </remarks>
    //[AseClientPermission(SecurityAction.Demand)]
    [DatabaseAssembler(typeof(AseDatabaseAssembler))]
    //[ContainerPolicyCreator(typeof(AseDatabasePolicyCreator))]
    public class AseDatabase : Database
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="AseDatabase"/> class with a connection string.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public AseDatabase(string connectionString)
            : base(connectionString, AseClientFactory.Instance)
        {
        }

        /// <summary>
        /// <para>Gets the parameter token used to delimit parameters for the Ase database.</para>
        /// </summary>
        /// <value>
        /// <para>The '@' symbol.</para>
        /// </value>
        protected char ParameterToken
        {
            get { return '@'; }
        }

        /// <summary>
        /// <para>Executes the <see cref="AseCommand"/> and returns a new <see cref="XmlReader"/>.</para>
        /// </summary>
        /// <remarks>
        ///        Unlike other Execute... methods that take a <see cref="DbCommand"/> instance, this method
        ///        does not set the command behavior to close the connection when you close the reader.
        ///        That means you'll need to close the connection yourself, by calling the
        ///        command.Connection.Close() method.
        ///        <para>
        ///            There is one exception to the rule above. If you're using <see cref="TransactionScope"/> to provide
        ///            implicit transactions, you should NOT close the connection on this reader when you're
        ///            done. Only close the connection if <see cref="Transaction"/>.Current is null.
        ///        </para>
        /// </remarks>
        /// <param name="command">
        /// <para>The <see cref="AseCommand"/> to execute.</para>
        /// </param>
        /// <returns>
        /// <para>An <see cref="XmlReader"/> object.</para>
        /// </returns>
        public XmlReader ExecuteXmlReader(DbCommand command)
        {
            AseCommand aseCommand = CheckIfAseCommand(command);

            ConnectionWrapper wrapper = GetOpenConnection(false);
            PrepareCommand(command, wrapper.Connection);
            return DoExecuteXmlReader(aseCommand);
        }

        /// <summary>
        /// <para>Executes the <see cref="AseCommand"/> in a transaction and returns a new <see cref="XmlReader"/>.</para>
        /// </summary>
        /// <remarks>
        ///        Unlike other Execute... methods that take a <see cref="DbCommand"/> instance, this method
        ///        does not set the command behavior to close the connection when you close the reader.
        ///        That means you'll need to close the connection yourself, by calling the
        ///        command.Connection.Close() method.
        /// </remarks>
        /// <param name="command">
        /// <para>The <see cref="AseCommand"/> to execute.</para>
        /// </param>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.</para>
        /// </param>
        /// <returns>
        /// <para>An <see cref="XmlReader"/> object.</para>
        /// </returns>
        public XmlReader ExecuteXmlReader(DbCommand command, DbTransaction transaction)
        {
            AseCommand aseCommand = CheckIfAseCommand(command);
            PrepareCommand(aseCommand, transaction);
            return DoExecuteXmlReader(aseCommand);
        }

        /// <devdoc>
        /// Execute the actual XML Reader call.
        /// </devdoc>        
        private XmlReader DoExecuteXmlReader(AseCommand aseCommand)
        {
            try
            {
                DateTime startTime = DateTime.Now;
                XmlReader reader = aseCommand.ExecuteXmlReader();
                instrumentationProvider.FireCommandExecutedEvent(startTime);
                return reader;
            }
            catch (Exception e)
            {
                instrumentationProvider.FireCommandFailedEvent(aseCommand.CommandText, ConnectionStringNoCredentials, e);
                throw;
            }
        }

        private static AseCommand CheckIfAseCommand(DbCommand command)
        {
            AseCommand aseCommand = (AseCommand) command; //as AseCommand;
            if (aseCommand == null) throw new ArgumentException("The command should be a AseCommand");
            return aseCommand;
        }

        /// <devdoc>
        /// Listens for the RowUpdate event on a dataadapter to support UpdateBehavior.Continue
        /// </devdoc>
        private void OnAseRowUpdated(object sender, AseRowUpdatedEventArgs rowThatCouldNotBeWritten)
        {
            if (rowThatCouldNotBeWritten.RecordsAffected == 0)
            {
                if (rowThatCouldNotBeWritten.Errors != null)
                {
                    rowThatCouldNotBeWritten.Row.RowError = "Failed to update row";//Resources.ExceptionMessageUpdateDataSetRowFailure;
                    rowThatCouldNotBeWritten.Status = UpdateStatus.SkipCurrentRow;
                }
            }
        }

        /// <summary>
        /// Retrieves parameter information from the stored procedure specified in the <see cref="DbCommand"/> and populates the Parameters collection of the specified <see cref="DbCommand"/> object.
        /// </summary>
        /// <param name="discoveryCommand">The <see cref="DbCommand"/> to do the discovery.</param>
        /// <remarks>The <see cref="DbCommand"/> must be a <see cref="SqlCommand"/> instance.</remarks>
        protected override void DeriveParameters(DbCommand discoveryCommand)
        {
            AseCommandBuilder.DeriveParameters((AseCommand)discoveryCommand);
        }

        /// <summary>
        /// Returns the starting index for parameters in a command.
        /// </summary>
        /// <returns>The starting index for parameters in a command.</returns>
        protected override int UserParametersStartIndex()
        {
            string conns = ConnectionString.ToUpper();

            Int16 userparamstartindex = 1;
            if (conns.Contains("SERVERTYPE=UNI"))
            {
                // if UniData or UniVerse databases, this needs to be 0
                userparamstartindex = 0;
            }
            return userparamstartindex;
        }

        /// <summary>
        /// Builds a value parameter name for the current database.
        /// </summary>
        /// <param name="name">The name of the parameter.</param>
        /// <returns>A correctly formated parameter name.</returns>
        public override string BuildParameterName(string name)
        {
            //if (name[0] != this.ParameterToken)
            //{
            //    return name.Insert(0, new string(this.ParameterToken, 1));
            //}
            return name;
        }

        /// <summary>
        /// Sets the RowUpdated event for the data adapter.
        /// </summary>
        /// <param name="adapter">The <see cref="DbDataAdapter"/> to set the event.</param>
        protected override void SetUpRowUpdatedEvent(DbDataAdapter adapter)
        {
            ((AseDataAdapter)adapter).RowUpdated += new AseRowUpdatedEventHandler(OnAseRowUpdated);
        }

        /// <summary>
        /// Determines if the number of parameters in the command matches the array of parameter values.
        /// </summary>
        /// <param name="command">The <see cref="DbCommand"/> containing the parameters.</param>
        /// <param name="values">The array of parameter values.</param>
        /// <returns><see langword="true"/> if the number of parameters and values match; otherwise, <see langword="false"/>.</returns>
        protected override bool SameNumberOfParametersAndValues(DbCommand command, object[] values)
        {
            int returnParameterCount = 1;
            string conns = ConnectionString.ToUpper();

            if (conns.Contains("SERVERTYPE=UNI") && (values.Length == 1) && (command.Parameters.Count == 1))
            {
                // if UniData or UniVerse databases, set to 0 for ExecuteScalar
                returnParameterCount = 0;
            }

            int numberOfParametersToStoredProcedure = command.Parameters.Count - returnParameterCount;
            int numberOfValuesProvidedForStoredProcedure = values.Length;
            return numberOfParametersToStoredProcedure == numberOfValuesProvidedForStoredProcedure;
        }

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object to the command.</para>
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>
        /// <param name="size"><para>The maximum size of the data within the column.</para></param>
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
        /// <param name="nullable"><para>A value indicating whether the parameter accepts <see langword="null"/> (<b>Nothing</b> in Visual Basic) values.</para></param>
        /// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
        /// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>       
        public virtual void AddParameter(DbCommand command, string name, AseDbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            DbParameter parameter = CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
            command.Parameters.Add(parameter);
        }

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object to the command.</para>
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>        
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>    
        public void AddParameter(DbCommand command, string name, AseDbType dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            AddParameter(command, name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
        }

        /// <summary>
        /// Adds a new Out <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the out parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>        
        /// <param name="size"><para>The maximum size of the data within the column.</para></param>        
        public void AddOutParameter(DbCommand command, string name, AseDbType dbType, int size)
        {
            ParameterDirection direction = ParameterDirection.Output;
            string conns = ConnectionString.ToUpper();
            if (conns.Contains("SERVERTYPE=UNIDATA") && direction == ParameterDirection.Output)
            {
                direction = ParameterDirection.InputOutput;
            }
            // standard set of OutParameter for DB2 and IDS and UniVerse
            AddParameter(command, name, dbType, size, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the in parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>                
        /// <remarks>
        /// <para>This version of the method is used when you can have the same parameter object multiple times with different values.</para>
        /// </remarks>        
        public void AddInParameter(DbCommand command, string name, AseDbType dbType)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The commmand to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>                
        /// <param name="value"><para>The value of the parameter.</para></param>      
        public void AddInParameter(DbCommand command, string name, AseDbType dbType, object value)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the value.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        public void AddInParameter(DbCommand command, string name, AseDbType dbType, string sourceColumn, DataRowVersion sourceVersion)
        {
            AddParameter(command, name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
        }

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object.</para>
        /// </summary>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>
        /// <param name="size"><para>The maximum size of the data within the column.</para></param>
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
        /// <param name="nullable"><para>A value indicating whether the parameter accepts <see langword="null"/> (<b>Nothing</b> in Visual Basic) values.</para></param>
        /// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
        /// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>  
        protected DbParameter CreateParameter(string name, AseDbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            AseParameter param = CreateParameter(name);
            ConfigureParameter(param, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
            return param;
        }

        /// <summary>
        /// Configures a given <see cref="DbParameter"/>.
        /// </summary>
        /// <param name="param">The <see cref="AseParameter"/> to configure.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="AseDbType"/> values.</para></param>
        /// <param name="size"><para>The maximum size of the data within the column.</para></param>
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
        /// <param name="nullable"><para>A value indicating whether the parameter accepts <see langword="null"/> (<b>Nothing</b> in Visual Basic) values.</para></param>
        /// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
        /// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>

        protected virtual void ConfigureParameter(DbParameter param, string name, AseDbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            param.DbType = (DbType) dbType;
            param.Size = size;
            param.Value = (value == null) ? DBNull.Value : value;
            param.Direction = direction;
            param.IsNullable = nullable;
            param.SourceColumn = sourceColumn;
            param.SourceVersion = sourceVersion;
        }

        /// Does nothing in SQL Server. Only here for compliance with
        /// DBCommandWrapper ///
        /// Db Command
        /// Ref Cursor for Oracle
        public override void AddCursorOutParameter(DbCommand command, string RefCursorName)
        {
            // Do Nothing
        }


    }
}

//
// AseClientFactory
//
using System;
using System.Data.Common;
using Sybase.Data.AseClient;

namespace Microsoft.Practices.EnterpriseLibrary.Data.Sybase
{
    class AseClientFactory: DbProviderFactory
    {
        public static readonly AseClientFactory Instance = new AseClientFactory();

        public override bool CanCreateDataSourceEnumerator
        {
            get { return false; }
        }

        private AseClientFactory()
            : base()
        {
        }

        //public override DbCommand CreateCommand()
        //{
        //    AseCommand aseCommand = new AseCommand();
        //    return new DbCommand();
        //}

        //public override DbConnection CreateConnection()
        //{
        //    return new DbConnection();
        //}

        //public override DbConnectionStringBuilder CreateConnectionStringBuilder()
        //{
        //    return new DbConnectionStringBuilder();
        //}

        public override DbDataAdapter CreateDataAdapter()
        {
            return new AseDataAdapter();
        }

        //public override DbParameter CreateParameter()
        //{
        //    return new DbParameter();
        //}

        ////public override System.Security.CodeAccessPermission CreatePermission(System.Security.Permissions.PermissionState state)
        ////{
        ////    return new FirebirdClientPermission(state);
        ////}

        //public override DbCommandBuilder CreateCommandBuilder()
        //{
        //    return new DbCommandBuilder();
        //}
    }
}


Mar 24, 2009 at 6:57 AM

Hi,

I’m not familiar with Sybase so want to learn also. Where could I found the assembly for Sybase.Data.AseClient? Could this be downloaded? So maybe I could replicate and provide help why you encounter problem casting Systbase.Data.AseClient.AseCommand to .Net DBCommand. If this was working, maybe you could use AseCommand instead of DBCommand they might provide the same functionality, just a thought. J
Mar 24, 2009 at 3:38 PM
The Sybase.Data.AseClient assembly is installed when you install the
Sybase�s Open Client. You can download the Sybase 15.0 Developer�s Edition
(Server+Client) from http://www.sybase.com/ase_1500devel
Mar 25, 2009 at 1:06 AM

Hi,

Thank you for the link. I’ll download the Sybase 15.0 DE and start learning. Hope this works with Windows. Now I could replicate your error. J
Mar 25, 2009 at 10:58 AM
Hi Icruz,

I'm not sure if this is the best approach... Have you tried casting the DbCommand to its interface then casting it back to AseCommand? 

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Mar 26, 2009 at 2:57 PM
How I do that? Can you post some code or example?

-=--=--=--=--=--=--=--=--=--=-=-
Luis Espinoza Cruz
Email:lcruz@corp-ods.com
blog: luisespinozacruz.blogspot.com
Optimized Data Solutions
Mar 27, 2009 at 2:45 PM
Oopps... it seems casting wont work... i tried it and it didn't work. anyway, i made it work somehow with a bit of code changes, here is a sample.

 

private static AseCommand CheckIfAseCommand(IDbCommand command)
{
    AseCommand aseCommand = command as AseCommand;
    if (aseCommand == null) throw new ArgumentException("The command should be a AseCommand");
    return aseCommand;
}

I replaced the Type of the DbCommand to accept a interface which is the IDbCommand which is the interface of both AseCommand and DbCommand. Can you please try this out and see if it works for you.

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Feb 14, 2011 at 7:08 AM

It didn't work with me some errors appeared so of it worked with you could u please give us any feed back

Feb 14, 2011 at 8:12 AM

I forgot to ask what version of EntLib are you using.  The code above won't work for version 5.0 since the assembler classes were removed from it.  Creating custom providers in EntLib 5.0 is documented here

If you're using version lower than 5.0, you can check the EntLib Contrib project for samples of custom database providers.  You can also based your EntLib 5.0 implementation from that, you just need to ignore the assembler class and focus only on the class inheriting from the Database and DatabaseData class. 

We really don't have any sample for a Sybase provider, but the sample from EntLib Contrib or the documentation in EntLib 5.0 should help you so I suggest you try it out. We could assist you if you have more specific questions.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com