//******************************************************************************************************
//  DataExtensions.cs - Gbtc
//
//  Copyright © 2012, Grid Protection Alliance.  All Rights Reserved.
//
//  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See
//  the NOTICE file distributed with this work for additional information regarding copyright ownership.
//  The GPA licenses this file to you under the Eclipse Public License -v 1.0 (the "License"); you may
//  not use this file except in compliance with the License. You may obtain a copy of the License at:
//
//      http://www.opensource.org/licenses/eclipse-1.0.php
//
//  Unless agreed to in writing, the subject software distributed under the License is distributed on an
//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the
//  License for the specific language governing permissions and limitations.
//
//  Code Modification History:
//  ----------------------------------------------------------------------------------------------------
//  02/05/2003 - J. Ritchie Carroll
//       Generated original version of source code.
//  05/25/2004 - J. Ritchie Carroll
//       Added "with parameters" overloads to all basic query functions.
//  12/10/2004 - Tim M Shults
//       Added several new WithParameters overloads that allow a programmer to send just the
//       parameter values instead of creating a series of parameter objects and then sending
//       them through. Easy way to cut down on the amount of code.
//       This code is just for calls to Stored Procedures and will not work for in-line SQL.
//  03/28/2006 - Pinal C. Patel
//       Migrated 2.0 version of source code from 1.1 source (GSF.Database.Common).
//  08/21/2007 - Darrell Zuercher
//       Edited code comments.
//  09/15/2008 - J. Ritchie Carroll
//       Converted to C# extensions.
//  09/29/2008 - Pinal C. Patel
//       Reviewed code comments.
//  09/09/2009 - J. Ritchie Carroll
//       Added extensions for ODBC providers.
//  09/14/2009 - Stephen C. Wills
//       Added new header and license agreement.
//  12/02/2009 - Stephen C. Wills
//       Added disposal of database command objects.
//  09/28/2010 - J. Ritchie Carroll
//       Added Stephen's CreateParameterizedCommand connection extension.
//  04/07/2011 - J. Ritchie Carroll
//       Added Mehul's AddParameterWithValue command extension. Added overloads for all
//       PopulateParameters() command extensions so that they could take a "params" style
//       array of values after initial value for ease-of-use. Added "params" style array
//       to all templated IDbConnection that will use the CreateParameterizedCommand
//       connection extension with optional parameters.
//  06/16/2011 - Pinal C. Patel
//       Modified AddParameterWithValue() to be backwards compatible.
//  07/18/2011 - Stephen C. Wills
//       Added DataRow extension functions to automatically convert from types that
//       implement the IConvertible interface.
//  08/12/2011 - Pinal C. Patel
//       Modified AddParameterWithValue() to correctly implement backwards compatible.
//  09/19/2011 - Stephen C. Wills
//       Modified AddParametersWithValues() to parse parameters prefixed
//       with a colon for Oracle database compatibility.
//  09/21/2011 - J. Ritchie Carroll
//       Added Mono implementation exception regions.
//  12/14/2012 - Starlynn Danyelle Gilliam
//       Modified Header.
//
//******************************************************************************************************

using System;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace GSF.Data
{
    /// <summary>
    /// Defines extension functions related to database and SQL interaction.
    /// </summary>
    public static class DataExtensions
    {
        /// <summary>
        /// The default timeout duration used for executing SQL statements when timeout duration is not specified.
        /// </summary>
        public const int DefaultTimeoutDuration = 30;

        #region [ SQL Encoding String Extension ]

        /// <summary>
        /// Performs SQL encoding on given T-SQL string.
        /// </summary>
        /// <param name="sql">The string on which SQL encoding is to be performed.</param>
        /// <returns>The SQL encoded string.</returns>
        public static string SqlEncode(this string sql)
        {
            return sql.Replace("\'", "\'\'").Replace("/*", "").Replace("--", "");
        }

        #endregion

        #region [ ExecuteNonQuery Overloaded Extension ]

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>The number of rows affected.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static int ExecuteNonQuery<TConnection>(this TConnection connection, string sql, params object[] parameters) where TConnection : IDbConnection
        {
            return connection.ExecuteNonQuery(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="IDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>The number of rows affected.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static int ExecuteNonQuery<TConnection>(this TConnection connection, string sql, int timeout, params object[] parameters) where TConnection : IDbConnection
        {
            using (IDbCommand command = connection.CreateParameterizedCommand(sql, parameters))
            {
                command.CommandTimeout = timeout;
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OleDbConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this OleDbConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteNonQuery(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OleDbConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this OleDbConnection connection, string sql, int timeout, params object[] parameters)
        {
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OdbcConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OdbcConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this OdbcConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteNonQuery(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OdbcConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OdbcConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this OdbcConnection connection, string sql, int timeout, params object[] parameters)
        {
            using (OdbcCommand command = new OdbcCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="SqlConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="SqlConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this SqlConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteNonQuery(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="SqlConnection"/>, and returns the number of rows affected.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="SqlConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQuery(this SqlConnection connection, string sql, int timeout, params object[] parameters)
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteNonQuery();
            }
        }

        #endregion

        #region [ ExecuteReader Overloaded Extensions ]

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and builds a <see cref="IDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="IDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>A <see cref="IDataReader"/> object.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static IDataReader ExecuteReader<TConnection>(this TConnection connection, string sql, params object[] parameters) where TConnection : IDbConnection
        {
            return connection.ExecuteReader(sql, CommandBehavior.Default, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and builds a <see cref="IDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="IDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>A <see cref="IDataReader"/> object.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static IDataReader ExecuteReader<TConnection>(this TConnection connection, string sql, int timeout, params object[] parameters) where TConnection : IDbConnection
        {
            return connection.ExecuteReader(sql, CommandBehavior.Default, timeout, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and builds a <see cref="IDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="IDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="behavior">One of the <see cref="CommandBehavior"/> values.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>A <see cref="IDataReader"/> object.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static IDataReader ExecuteReader<TConnection>(this TConnection connection, string sql, CommandBehavior behavior, int timeout, params object[] parameters) where TConnection : IDbConnection
        {
            using (IDbCommand command = connection.CreateParameterizedCommand(sql, parameters))
            {
                command.CommandTimeout = timeout;
                return command.ExecuteReader(behavior);
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OleDbConnection"/>, and builds a <see cref="OleDbDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>A <see cref="OleDbDataReader"/> object.</returns>
        public static OleDbDataReader ExecuteReader(this OleDbConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteReader(sql, CommandBehavior.Default, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OleDbConnection"/>, and builds a <see cref="OleDbDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="behavior">One of the <see cref="CommandBehavior"/> values.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>A <see cref="OleDbDataReader"/> object.</returns>
        public static OleDbDataReader ExecuteReader(this OleDbConnection connection, string sql, CommandBehavior behavior, int timeout, params object[] parameters)
        {
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteReader(behavior);
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OdbcConnection"/>, and builds a <see cref="OdbcDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>A <see cref="OdbcDataReader"/> object.</returns>
        public static OdbcDataReader ExecuteReader(this OdbcConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteReader(sql, CommandBehavior.Default, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OdbcConnection"/>, and builds a <see cref="OdbcDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OdbcConnection"/> to use for executing the SQL statement.</param>
        /// <param name="behavior">One of the <see cref="CommandBehavior"/> values.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>A <see cref="OdbcDataReader"/> object.</returns>
        public static OdbcDataReader ExecuteReader(this OdbcConnection connection, string sql, CommandBehavior behavior, int timeout, params object[] parameters)
        {
            using (OdbcCommand command = new OdbcCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteReader(behavior);
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="SqlConnection"/>, and builds a <see cref="SqlDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="SqlConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>A <see cref="SqlDataReader"/> object.</returns>
        public static SqlDataReader ExecuteReader(this SqlConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteReader(sql, CommandBehavior.Default, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="SqlConnection"/>, and builds a <see cref="SqlDataReader"/>.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="SqlConnection"/> to use for executing the SQL statement.</param>
        /// <param name="behavior">One of the <see cref="CommandBehavior"/> values.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>A <see cref="SqlDataReader"/> object.</returns>
        public static SqlDataReader ExecuteReader(this SqlConnection connection, string sql, CommandBehavior behavior, int timeout, params object[] parameters)
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteReader(behavior);
            }
        }

        #endregion

        #region [ ExecuteScalar Overloaded Extensions ]

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="IDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static object ExecuteScalar<TConnection>(this TConnection connection, string sql, params object[] parameters) where TConnection : IDbConnection
        {
            return connection.ExecuteScalar(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="IDbConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="IDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        /// <typeparam name="TConnection">Type of <see cref="IDbConnection"/> to use.</typeparam>
        public static object ExecuteScalar<TConnection>(this TConnection connection, string sql, int timeout, params object[] parameters) where TConnection : IDbConnection
        {
            using (IDbCommand command = connection.CreateParameterizedCommand(sql, parameters))
            {
                command.CommandTimeout = timeout;
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OleDbConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        public static object ExecuteScalar(this OleDbConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteScalar(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OleDbConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OleDbConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        public static object ExecuteScalar(this OleDbConnection connection, string sql, int timeout, params object[] parameters)
        {
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OdbcConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OdbcConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        public static object ExecuteScalar(this OdbcConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteScalar(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="OdbcConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="OdbcConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        public static object ExecuteScalar(this OdbcConnection connection, string sql, int timeout, params object[] parameters)
        {
            using (OdbcCommand command = new OdbcCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="SqlConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="SqlConnection"/> to use for executing the SQL statement.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        public static object ExecuteScalar(this SqlConnection connection, string sql, params object[] parameters)
        {
            return connection.ExecuteScalar(sql, DefaultTimeoutDuration, parameters);
        }

        /// <summary>
        /// Executes the SQL statement using <see cref="SqlConnection"/>, and returns the value in the first column 
        /// of the first row in the resultset.
        /// </summary>
        /// <param name="sql">The SQL statement to be executed.</param>
        /// <param name="connection">The <see cref="SqlConnection"/> to use for executing the SQL statement.</param>
        /// <param name="timeout">The time in seconds to wait for the SQL statement to execute.</param>
        /// <param name="parameters">The parameter values to be used to fill in <see cref="IDbDataParameter"/> parameters identified by '@' prefix in <paramref name="sql"/> expression -or- the parameter values to be passed into stored procedure being executed.</param>
        /// <returns>Value in the first column of the first row in the resultset.</returns>
        public static object ExecuteScalar(this SqlConnection connection, string sql, int timeout, params object[] parameters)
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandTimeout = timeout;
                command.PopulateParameters(parameters);
                return command.ExecuteScalar();
            }
        }

Lines 1–500 of 1823. Load more