SybaseHelper class ?

Question

I am trying to connect to sybase ASE 15 and call a SP that does some DML. I was wondering if anyone has any pointers to a sybase helper class analogous to SQLhelper.cs or if someone has any pointers to any blog / sample code.

Eventually I will be migrating the solution to SQl Server 2008 R2 (couple of months from now) so I want to create a generic implementation which can be used without much change even after migration.

Answer 1

public class SybaseDBHelper : ISybaseDBHelper
    {
        private AseConnection conn;
        private AseCommand cmd;
        private AseDataAdapter adapter;
        private DataSet outDS;
        protected static readonly ILog _logger = LogManager.GetLogger(typeof (SybaseDBHelper));

        #region InsertData
        public int InsertDataUsingStoredProcedure(string storedProcedureName, DatabaseEnum dbName, List<AseParameter> parameters)
        {
            var connFactory = new ConnectionFactory();
            int _errorCode = 0;
            string connectionString = connFactory.GetConnectionString(dbName);
            using (conn = connFactory.GetAseConnectionString(connectionString))
            {
                try
                {
                    conn.Open();
                    if (conn.State == ConnectionState.Open)
                    {
                        using (cmd = conn.CreateCommand())
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = storedProcedureName;
                            if (parameters != null )
                            {
                                foreach (AseParameter param in parameters)
                                {
                                    cmd.Parameters.Add(param);
                                }
                            }
                           _errorCode = cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (AseException ex)
                {
                    _logger.ErrorFormat("Error Inserting Data into Database {0}", ex);
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
            return _errorCode;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                conn.Dispose();
                conn = null;
                GC.SuppressFinalize(this);
            }
        }

        #endregion

} }

Interface

using System;
using System.Collections.Generic;
using System.Data;
using LiabilitiesMI.Common.DataObjects;
using Sybase.Data.AseClient;

namespace LiabilitiesMI.Common.Interfaces
{
    public interface ISybaseDBHelper : IDisposable
    {
        DataSet GetDataUsingStoredProcedure(string storedProcedureName, DatabaseEnum dbName, List<AseParameter> parameters);
        int InsertDataUsingStoredProcedure(string storedProcedureName, DatabaseEnum dbName, List<AseParameter> parameters);
    }
}

--Calling it this way will invoke the expliciti garbage Collection

using (ISybaseDBHelper _DBHelper = ObjectFactory.GetDBHelper())
            {
                _DBHelper.InsertDataUsingStoredProcedure("usp_load_fx_spot_rate_into_staging", DatabaseEnum.Staging, input);
            }

Answer 2

1. If you're developing SQL for Sybase (or MS) you are best off using a Developer tool, such as SQLProgrammer or Rapid SQL. It improves productivity by eliminating the manual labour.

2. If you do not have that, then the utilities that come with Sybase are quite adequate, but you need to learn to use the catalogue. Either isql for a character interface or DBISQL which provides Command and ResultSet windows in a GUI interface.

3. Once you have a session open on the server, type the following SQL commands. These execute system stored procedures that interrogate the catalogue:

exec sp_help (object_name) -- including tables and sprocs

exec sp_helptext

There are 30 such interrogatories.

I take it you realise that sprocs can do much more than simple INSERTS; handle various Datatypes as parameters, and that your posted code is only a simple example, not the full quid. Generally, to enable stored procs for execution from Java apps, we need to provide a wrapper (on the SQL side).