//Source code from the Code Associate C# code library, Full documentation and latest updates can be found
//@ http://www.codeassociate.com/caapi/
//===============================================================================
// Code Associate Data Access Block for .NET
// DataAccessCore.cs
// 
//===============================================================================
// Copyright (C) 2002-2010 Ravin Enterprises Ltd. 
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace CA.Blocks.DataAccess
{
    public class SqlServerDataAccess : DataAccessCore
    {

        public SqlServerDataAccess(string connectionString) : base(connectionString) 
        {

        }

        protected override bool PrepCommand(IDbCommand cmd)
        {
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            cmd.Connection = sqlConnection;
            return true;
        }

        protected override DbDataAdapter GetDataAdapter(IDbCommand cmd)
        {
            return (new SqlDataAdapter((SqlCommand)cmd));
        }

+StoredProcedureHelpers-        #region StoredProcedureHelpers
         public SqlCommand CreateBlankStoredProcedureCommand(string strStoredProcedureName, bool bolIncludeReturnValue)
         {
             SqlCommand sqlcmd = new SqlCommand
                                                 {
                                                     CommandText = strStoredProcedureName,
                                                     CommandType = CommandType.StoredProcedure
                                                 };
             if (bolIncludeReturnValue)
             {
                 SqlParameter sqlparam = sqlcmd.CreateParameter();
                 sqlparam.ParameterName = "Return";
                 sqlparam.SqlDbType = SqlDbType.Int;
                 sqlparam.Direction = ParameterDirection.ReturnValue;
                 sqlcmd.Parameters.Add(sqlparam);
             }
             return (sqlcmd);
         }
 
         #endregion StoredProcedureHelpers

+CommandType Helpers-        #region CommandType Helpers
         public SqlCommand CreateTextCommand(string sql)
         {
         SqlCommand sqlcmd = new SqlCommand
                                 {
                                     CommandText = sql,
                                     CommandType = CommandType.Text
                                 };
             return (sqlcmd);
         }
 
         #endregion StoredProcedureHelpers

+ParemeterHelpers-        #region ParemeterHelpers
 
         public SqlParameter AddInputParamCommand(SqlCommand cmd, string strParameterName, object objParameterValue, DbType odbType, int maxParamSize)
         {
             SqlParameter sqlparam = new SqlParameter(strParameterName, odbType);
             sqlparam.Direction = ParameterDirection.Input;
 
             if (maxParamSize > 0) sqlparam.Size = maxParamSize;
 
             if ((objParameterValue == null || objParameterValue == DBNull.Value))
             {
                 sqlparam.Value = DBNull.Value;
                 //Added the following as sometimes the type is changed to int32
                 sqlparam.DbType = odbType;
             }
             else
                 sqlparam.Value = objParameterValue;
 
             cmd.Parameters.Add(sqlparam);
 
             return (sqlparam);
         }
 
         public SqlParameter AddInputParamCommand(SqlCommand cmd, string strParameterName, object objParameterValue, SqlDbType odbType, int maxParamSize)
         {
             SqlParameter sqlparam = new SqlParameter(strParameterName, odbType);
             sqlparam.Direction = ParameterDirection.Input;
 
             if (maxParamSize > 0) sqlparam.Size = maxParamSize;
 
             if ((objParameterValue == null || objParameterValue == DBNull.Value))
             {
                 sqlparam.Value = DBNull.Value;
                 //Added the following as sometimes the type is changed to int32
                 sqlparam.SqlDbType = odbType;
             }
             else
                 sqlparam.Value = objParameterValue;
 
             cmd.Parameters.Add(sqlparam);
 
             return (sqlparam);
         }
 
 
         public SqlParameter AddOutputParamCommand(SqlCommand cmd, string strParameterName, DbType odbType, Int32 maxParamSize)
         {
             SqlParameter sqlparam = new SqlParameter(strParameterName, odbType);
             sqlparam.Direction = ParameterDirection.Output;
             if (maxParamSize > 0)
                 sqlparam.Size = maxParamSize;
             cmd.Parameters.Add(sqlparam);
 
             return (sqlparam);
         }
 
         public SqlParameter AddOutputParamCommand(SqlCommand cmd, string strParameterName, SqlDbType odbType, Int32 maxParamSize)
         {
             SqlParameter sqlparam = new SqlParameter(strParameterName, odbType);
             sqlparam.Direction = ParameterDirection.Output;
             if (maxParamSize > 0)
                 sqlparam.Size = maxParamSize;
             cmd.Parameters.Add(sqlparam);
 
             return (sqlparam);
         }
 
 
         public SqlParameter AddAdapterInputParamCommand(SqlCommand cmd, string strParameterName, string sourceColName, DataTable sourceDataTable)
         {       
             SqlParameter sqlparam;
 
             if (sourceDataTable.Columns.Contains(sourceColName))
             {
                 DataColumn dc = sourceDataTable.Columns[sourceColName];
                 sqlparam = new SqlParameter(strParameterName, dc.DataType);
                 sqlparam.Direction = ParameterDirection.Input;
 
                 sqlparam.SourceColumn = sourceColName;
 
                 sqlparam.SourceVersion = DataRowVersion.Current;
 
                 cmd.Parameters.Add(sqlparam);
             }
             else
             {
                 throw new Exception(string.Format("SourceColName {0} does not exist in the SourceDataTable as such cannot be added as a parameter!", sourceColName));
             }
             return (sqlparam);
         }
 
         public SqlParameter AddAdapterInputParamCommand(SqlCommand cmd, string strParameterName, DataTable sourceDataTable)
         {
             return
                 AddAdapterInputParamCommand(cmd, strParameterName, strParameterName.Replace("@", string.Empty),
                                             sourceDataTable);
         }
 
         #endregion ParemeterHelpers 

+SQL Bulk Update Methods-        #region SQL Bulk Update Methods
 
         public SqlDataAdapter CreateBulkInsertAdapter(string storedProcedureName, int batchSize)
         {
             SqlDataAdapter result = new SqlDataAdapter();
             result.UpdateBatchSize = batchSize;
             SqlCommand cmd = CreateBlankStoredProcedureCommand(storedProcedureName, false);
             cmd.UpdatedRowSource = UpdateRowSource.None;
             result.InsertCommand = cmd;
             return result;
         }
 
 
 
         // gets the first col which has an expression on.  
         // This will need to be refactored if you have expressions based on expressions as you will need to be aware of dependency order
         // if no expressions are found it will return null. 
         private DataColumn GetColunmWithExpression(DataTable dt)
         {
             DataColumn result = null;
             foreach (DataColumn dcloop in dt.Columns)
             {
                 if (!string.IsNullOrEmpty(dcloop.Expression))
                 {
                     result = dcloop;
                     break;
                 }
             }
             return result;
         }
 
         public void CementExpressionsAsValues(DataTable dt)
         {
             DataColumn colWithExpression = GetColunmWithExpression(dt);
             int excapeCounter = 0;
             while (colWithExpression != null && excapeCounter < dt.Columns.Count)
             {
 
                 string tempColName = colWithExpression.ColumnName + Guid.NewGuid().ToString();
                 dt.Columns.Add(tempColName, colWithExpression.DataType);
 
                 foreach(DataRow dr in dt.Rows)
                 {
                     dr[tempColName] = dr[colWithExpression.ColumnName];
                 }
                 dt.Columns.Remove(colWithExpression);
                 dt.Columns[tempColName].ColumnName = colWithExpression.ColumnName;
 
                 colWithExpression = GetColunmWithExpression(dt);
                 excapeCounter++;
             }
         }
 
         public void ExecuteBulkInsertAdapter(SqlDataAdapter bulkAdapter, DataTable dt)
         {
             try
             {
                 PrepCommand(bulkAdapter.InsertCommand);
                 // possibly move this function out as it nos not really belong here 
                 CementExpressionsAsValues(dt);
                 bulkAdapter.Update(dt);
             }
             finally
             {
                 WrapUp(bulkAdapter.InsertCommand.Connection, true);
             }     
         }
         #endregion SQL Bulk Update Methods


    }
}