SqlDataSourceView.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ 4.0 / 4.0 / DEVDIV_TFS / Dev10 / Releases / RTMRel / ndp / fx / src / xsp / System / Web / UI / WebControls / SqlDataSourceView.cs / 1305376 / SqlDataSourceView.cs

                            //------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

namespace System.Web.UI.WebControls { 
 
    using System;
    using System.Collections; 
    using System.Collections.Specialized;
    using System.ComponentModel;
    using System.Data;
    using System.Data.Common; 
    using System.Data.SqlClient;
    using System.Drawing.Design; 
    using System.Globalization; 
    using System.IO;
    using System.Text; 
    using System.Web;
    using System.Web.Caching;
    using System.Web.UI;
    using System.Web.UI.WebControls; 
    using System.Web.Util;
 
    using ConflictOptions = System.Web.UI.ConflictOptions; 

 
    /// 
    /// Represents a single view of a SqlDataSource.
    /// 
    public class SqlDataSourceView : DataSourceView, IStateManager { 

        private const int MustDeclareVariableSqlExceptionNumber = 137; 
        private const int ProcedureExpectsParameterSqlExceptionNumber = 201; 

        private static readonly object EventDeleted = new object(); 
        private static readonly object EventDeleting = new object();
        private static readonly object EventFiltering = new object();
        private static readonly object EventInserted = new object();
        private static readonly object EventInserting = new object(); 
        private static readonly object EventSelected = new object();
        private static readonly object EventSelecting = new object(); 
        private static readonly object EventUpdated = new object(); 
        private static readonly object EventUpdating = new object();
 
        private HttpContext _context;
        private SqlDataSource _owner;
        private bool _tracking;
 
        private bool _cancelSelectOnNullParameter = true;
        private ConflictOptions _conflictDetection = ConflictOptions.OverwriteChanges; 
        private string _deleteCommand; 
        private SqlDataSourceCommandType _deleteCommandType = SqlDataSourceCommandType.Text;
        private ParameterCollection _deleteParameters; 
        private string _filterExpression;
        private ParameterCollection _filterParameters;
        private string _insertCommand;
        private SqlDataSourceCommandType _insertCommandType = SqlDataSourceCommandType.Text; 
        private ParameterCollection _insertParameters;
        private string _oldValuesParameterFormatString; 
        private string _selectCommand; 
        private SqlDataSourceCommandType _selectCommandType = SqlDataSourceCommandType.Text;
        private ParameterCollection _selectParameters; 
        private string _sortParameterName;
        private string _updateCommand;
        private SqlDataSourceCommandType _updateCommandType = SqlDataSourceCommandType.Text;
        private ParameterCollection _updateParameters; 

 
        ///  
        /// Creates a new instance of SqlDataSourceView.
        ///  
        public SqlDataSourceView(SqlDataSource owner, string name, HttpContext context) : base(owner, name) {
            _owner = owner;
            _context = context;
        } 

 
        public bool CancelSelectOnNullParameter { 
            get {
                return _cancelSelectOnNullParameter; 
            }
            set {
                if (CancelSelectOnNullParameter != value) {
                    _cancelSelectOnNullParameter = value; 
                    OnDataSourceViewChanged(EventArgs.Empty);
                } 
            } 
        }
 
        /// 
        /// Indicates that the view can delete rows.
        /// 
        public override bool CanDelete { 
            get {
                return (DeleteCommand.Length != 0); 
            } 
        }
 
        /// 
        /// Indicates that the view can add new rows.
        /// 
        public override bool CanInsert { 
            get {
                return (InsertCommand.Length != 0); 
            } 
        }
 
        /// 
        /// Indicates that the view can page the datasource on the server.
        /// 
        public override bool CanPage { 
            get {
                return false; 
            } 
        }
 
        /// 
        /// Indicates that the view can return the total number of rows returned by the query.
        /// 
        public override bool CanRetrieveTotalRowCount { 
            get {
                return false; 
            } 
        }
 
        /// 
        /// Indicates that the view can sort rows.
        /// 
        public override bool CanSort { 
            get {
                return (_owner.DataSourceMode == SqlDataSourceMode.DataSet) || (SortParameterName.Length > 0); 
            } 
        }
 
        /// 
        /// Indicates that the view can update rows.
        /// 
        public override bool CanUpdate { 
            get {
                return (UpdateCommand.Length != 0); 
            } 
        }
 
        /// 
        /// Whether commands pass old values in the parameter collection.
        /// 
        public ConflictOptions ConflictDetection { 
            get {
                return _conflictDetection; 
            } 
            set {
                if ((value < ConflictOptions.OverwriteChanges) || (value > ConflictOptions.CompareAllValues)) { 
                    throw new ArgumentOutOfRangeException("value");
                }
                _conflictDetection = value;
                OnDataSourceViewChanged(EventArgs.Empty); 
            }
        } 
 
        /// 
        /// The command to execute when Delete() is called on the SqlDataSourceView. 
        /// 
        public string DeleteCommand {
            get {
                if (_deleteCommand == null) { 
                    return String.Empty;
                } 
                return _deleteCommand; 
            }
            set { 
                _deleteCommand = value;
            }
        }
 
        public SqlDataSourceCommandType DeleteCommandType {
            get { 
                return _deleteCommandType; 
            }
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) {
                    throw new ArgumentOutOfRangeException("value");
                }
                _deleteCommandType = value; 
            }
        } 
 
        /// 
        /// Collection of parameters used in Delete(). 
        /// 
        [
        DefaultValue(null),
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)), 
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_DeleteParameters), 
        ] 
        public ParameterCollection DeleteParameters {
            get { 
                if (_deleteParameters == null) {
                    _deleteParameters = new ParameterCollection();
                }
                return _deleteParameters; 
            }
        } 
 
        /// 
        /// The filter to apply when Select() is called on the SqlDataSourceView. 
        /// 
        public string FilterExpression {
            get {
                if (_filterExpression == null) { 
                    return String.Empty;
                } 
                return _filterExpression; 
            }
            set { 
                if (FilterExpression != value) {
                    _filterExpression = value;
                    OnDataSourceViewChanged(EventArgs.Empty);
                } 
            }
        } 
 
        /// 
        /// Collection of parameters used in the FilterExpression property. 
        /// 
        [
        DefaultValue(null),
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)), 
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_FilterParameters), 
        ] 
        public ParameterCollection FilterParameters {
            get { 
                if (_filterParameters == null) {
                    _filterParameters = new ParameterCollection();

                    _filterParameters.ParametersChanged += new EventHandler(SelectParametersChangedEventHandler); 

                    if (_tracking) { 
                        ((IStateManager)_filterParameters).TrackViewState(); 
                    }
                } 
                return _filterParameters;
            }
        }
 
        /// 
        /// The command to execute when Insert() is called on the SqlDataSourceView. 
        ///  
        public string InsertCommand {
            get { 
                if (_insertCommand == null) {
                    return String.Empty;
                }
                return _insertCommand; 
            }
            set { 
                _insertCommand = value; 
            }
        } 

        public SqlDataSourceCommandType InsertCommandType {
            get {
                return _insertCommandType; 
            }
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) { 
                    throw new ArgumentOutOfRangeException("value");
                } 
                _insertCommandType = value;
            }
        }
 
        /// 
        /// Collection of values used in Insert(). 
        ///  
        [
        DefaultValue(null), 
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)),
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_InsertParameters),
        ] 
        public ParameterCollection InsertParameters {
            get { 
                if (_insertParameters == null) { 
                    _insertParameters = new ParameterCollection();
                } 
                return _insertParameters;
            }
        }
 
        /// 
        /// Returns whether this object is tracking view state. 
        ///  
        protected bool IsTrackingViewState {
            get { 
                return _tracking;
            }
        }
 
        /// 
        /// The format string applied to the names of the old values parameters 
        ///  
        [
        DefaultValue("{0}"), 
        WebCategory("Data"),
        WebSysDescription(SR.DataSource_OldValuesParameterFormatString),
        ]
        public string OldValuesParameterFormatString { 
            get {
                if (_oldValuesParameterFormatString == null) { 
                    return "{0}"; 
                }
                return _oldValuesParameterFormatString; 
            }
            set {
                _oldValuesParameterFormatString = value;
                OnDataSourceViewChanged(EventArgs.Empty); 
            }
        } 
 
        /// 
        /// Indicates the prefix for parameters. 
        /// 
        protected virtual string ParameterPrefix {
            get {
                if (String.IsNullOrEmpty(_owner.ProviderName) || 
                    String.Equals(_owner.ProviderName, "System.Data.SqlClient", StringComparison.OrdinalIgnoreCase)) {
                    return "@"; 
                } 
                else {
                    return String.Empty; 
                }
            }
        }
 
        /// 
        /// The command to execute when Select() is called on the SqlDataSourceView. 
        ///  
        public string SelectCommand {
            get { 
                if (_selectCommand == null) {
                    return String.Empty;
                }
                return _selectCommand; 
            }
            set { 
                if (SelectCommand != value) { 
                    _selectCommand = value;
                    OnDataSourceViewChanged(EventArgs.Empty); 
                }
            }
        }
 
        public SqlDataSourceCommandType SelectCommandType {
            get { 
                return _selectCommandType; 
            }
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) {
                    throw new ArgumentOutOfRangeException("value");
                }
                _selectCommandType = value; 
            }
        } 
 

        ///  
        /// The command to execute when Select is called on the SqlDataSourceView and the total rows is requested.
        /// 
        /*public string SelectCountCommand {
            get { 
                if (_selectCountCommand == null) {
                    return String.Empty; 
                } 
                return _selectCountCommand;
            } 
            set {
                if (SelectCountCommand != value) {
                    _selectCountCommand = value;
                    OnDataSourceViewChanged(EventArgs.Empty); 
                }
            } 
        }*/ 

 
        /// 
        /// Collection of parameters used in Select().
        /// 
        public ParameterCollection SelectParameters { 
            get {
                if (_selectParameters == null) { 
                    _selectParameters = new ParameterCollection(); 

                    _selectParameters.ParametersChanged += new EventHandler(SelectParametersChangedEventHandler); 

                    if (_tracking) {
                        ((IStateManager)_selectParameters).TrackViewState();
                    } 
                }
                return _selectParameters; 
            } 
        }
 

        /// 
        /// The name of the parameter in the SelectCommand that specifies the
        /// sort expression. This parameter's value will be automatically set 
        /// at runtime with the appropriate sort expression. This is only
        /// supported for stored procedure commands. 
        ///  
        public string SortParameterName {
            get { 
                if (_sortParameterName == null) {
                    return String.Empty;
                }
                return _sortParameterName; 
            }
            set { 
                if (SortParameterName != value) { 
                    _sortParameterName = value;
                    OnDataSourceViewChanged(EventArgs.Empty); 
                }
            }
        }
 

        ///  
        /// The command to execute when Update() is called on the SqlDataSourceView. 
        /// 
        public string UpdateCommand { 
            get {
                if (_updateCommand == null) {
                    return String.Empty;
                } 
                return _updateCommand;
            } 
            set { 
                _updateCommand = value;
            } 
        }

        public SqlDataSourceCommandType UpdateCommandType {
            get { 
                return _updateCommandType;
            } 
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) {
                    throw new ArgumentOutOfRangeException("value"); 
                }
                _updateCommandType = value;
            }
        } 

        ///  
        /// Collection of parameters used in Update(). 
        /// 
        [ 
        DefaultValue(null),
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)),
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_UpdateParameters), 
        ]
        public ParameterCollection UpdateParameters { 
            get { 
                if (_updateParameters == null) {
                    _updateParameters = new ParameterCollection(); 
                }
                return _updateParameters;
            }
        } 

 
        ///  
        /// This event is raised after the Delete operation has completed.
        /// Handle this event if you need to examine the values of output parameters. 
        /// 
        public event SqlDataSourceStatusEventHandler Deleted {
            add {
                Events.AddHandler(EventDeleted, value); 
            }
            remove { 
                Events.RemoveHandler(EventDeleted, value); 
            }
        } 

        /// 
        /// This event is raised before the Delete operation has been executed.
        /// Handle this event if you want to perform additional initialization operations 
        /// that are specific to your application. You can also handle this event if you
        /// need to validate the values of parameters or change their values. 
        /// When this event is raised, the database connection is not open yet, and you 
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs
        /// to true. 
        /// 
        public event SqlDataSourceCommandEventHandler Deleting {
            add {
                Events.AddHandler(EventDeleting, value); 
            }
            remove { 
                Events.RemoveHandler(EventDeleting, value); 
            }
        } 

        public event SqlDataSourceFilteringEventHandler Filtering {
            add {
                Events.AddHandler(EventFiltering, value); 
            }
            remove { 
                Events.RemoveHandler(EventFiltering, value); 
            }
        } 

        /// 
        /// This event is raised after the Insert operation has completed.
        /// Handle this event if you need to examine the values of output parameters. 
        /// 
        public event SqlDataSourceStatusEventHandler Inserted { 
            add { 
                Events.AddHandler(EventInserted, value);
            } 
            remove {
                Events.RemoveHandler(EventInserted, value);
            }
        } 

        ///  
        /// This event is raised before the Insert operation has been executed. 
        /// Handle this event if you want to perform additional initialization operations
        /// that are specific to your application. You can also handle this event if you 
        /// need to validate the values of parameters or change their values.
        /// When this event is raised, the database connection is not open yet, and you
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs
        /// to true. 
        /// 
        public event SqlDataSourceCommandEventHandler Inserting { 
            add { 
                Events.AddHandler(EventInserting, value);
            } 
            remove {
                Events.RemoveHandler(EventInserting, value);
            }
        } 

        ///  
        /// This event is raised after the Select operation has completed. 
        /// Handle this event if you need to examine the values of output parameters.
        ///  
        public event SqlDataSourceStatusEventHandler Selected {
            add {
                Events.AddHandler(EventSelected, value);
            } 
            remove {
                Events.RemoveHandler(EventSelected, value); 
            } 
        }
 
        /// 
        /// This event is raised before the Select operation has been executed.
        /// Handle this event if you want to perform additional initialization operations
        /// that are specific to your application. You can also handle this event if you 
        /// need to validate the values of parameters or change their values.
        /// When this event is raised, the database connection is not open yet, and you 
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs 
        /// to true.
        ///  
        public event SqlDataSourceSelectingEventHandler Selecting {
            add {
                Events.AddHandler(EventSelecting, value);
            } 
            remove {
                Events.RemoveHandler(EventSelecting, value); 
            } 
        }
 
        /// 
        /// This event is raised after the Update operation has completed.
        /// Handle this event if you need to examine the values of output parameters.
        ///  
        public event SqlDataSourceStatusEventHandler Updated {
            add { 
                Events.AddHandler(EventUpdated, value); 
            }
            remove { 
                Events.RemoveHandler(EventUpdated, value);
            }
        }
 
        /// 
        /// This event is raised before the Update operation has been executed. 
        /// Handle this event if you want to perform additional initialization operations 
        /// that are specific to your application. You can also handle this event if you
        /// need to validate the values of parameters or change their values. 
        /// When this event is raised, the database connection is not open yet, and you
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs
        /// to true.
        ///  
        public event SqlDataSourceCommandEventHandler Updating {
            add { 
                Events.AddHandler(EventUpdating, value); 
            }
            remove { 
                Events.RemoveHandler(EventUpdating, value);
            }
        }
 

        ///  
        /// Adds parameters to an DbCommand from an IOrderedDictionary. 
        /// The exclusion list contains parameter names that should not be added
        /// to the command's parameter collection. 
        /// 
        private void AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, string oldValuesParameterFormatString) {
            Debug.Assert(command != null);
 
            IDictionary caseInsensitiveExclusionList = null;
            if (exclusionList != null) { 
                caseInsensitiveExclusionList = new ListDictionary(StringComparer.OrdinalIgnoreCase); 
                foreach (DictionaryEntry de in exclusionList) {
                    caseInsensitiveExclusionList.Add(de.Key, de.Value); 
                }
            }

            if (parameters != null) { 
                string parameterPrefix = ParameterPrefix;
                foreach (DictionaryEntry de in parameters) { 
                    string rawParamName = (string)de.Key; 

                    if ((caseInsensitiveExclusionList != null) && (caseInsensitiveExclusionList.Contains(rawParamName))) { 
                        // If we have an exclusion list and it contains this parameter, skip it
                        continue;
                    }
 
                    string formattedParamName;
                    if (oldValuesParameterFormatString == null) { 
                        formattedParamName = rawParamName; 
                    }
                    else { 
                        formattedParamName = String.Format(CultureInfo.InvariantCulture, oldValuesParameterFormatString, rawParamName);
                    }
                    object value = de.Value;
 
                    // If the reference collection contains this parameter, we will use
                    // the Parameter's settings to format the value 
                    Parameter parameter = reference[formattedParamName]; 
                    if (parameter != null) {
                        value = parameter.GetValue(de.Value, false); 
                    }

                    formattedParamName = parameterPrefix + formattedParamName;
 
                    if (command.Parameters.Contains(formattedParamName)) {
                        // We never overwrite an existing value with a null value 
                        if (value != null) { 
                            command.Parameters[formattedParamName].Value = value;
                        } 
                    }
                    else {
                        // Parameter does not exist, add a new one
                        DbParameter dbParameter = _owner.CreateParameter(formattedParamName, value); 
                        command.Parameters.Add(dbParameter);
                    } 
                } 
            }
        } 

        /// 
        /// Builds a custom exception for specific database errors.
        /// Currently the only custom exception text supported is for SQL Server 
        /// when a parameter is present in the command but not in the parameters
        /// collection. 
        /// The isCustomException parameter indicates whether a custom exception 
        /// was created or not. This way the caller can determine whether it wants
        /// to rethrow the original exception or throw the new custom exception. 
        /// 
        private Exception BuildCustomException(Exception ex, DataSourceOperation operation, DbCommand command, out bool isCustomException) {
            System.Data.SqlClient.SqlException sqlException = ex as System.Data.SqlClient.SqlException;
            if (sqlException != null) { 
                if ((sqlException.Number == MustDeclareVariableSqlExceptionNumber) ||
                    (sqlException.Number == ProcedureExpectsParameterSqlExceptionNumber)) { 
                    string parameterNames; 
                    if (command.Parameters.Count > 0) {
                        StringBuilder sb = new StringBuilder(); 
                        bool firstParameter = true;
                        foreach (DbParameter p in command.Parameters) {
                            if (!firstParameter) {
                                sb.Append(", "); 
                            }
                            sb.Append(p.ParameterName); 
                            firstParameter = false; 
                        }
                        parameterNames = sb.ToString(); 
                    }
                    else {
                        parameterNames = SR.GetString(SR.SqlDataSourceView_NoParameters);
                    } 
                    isCustomException = true;
                    return new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_MissingParameters, operation, _owner.ID, parameterNames)); 
                } 
            }
            isCustomException = false; 
            return ex;
        }

        public int Delete(IDictionary keys, IDictionary oldValues) { 
            return ExecuteDelete(keys, oldValues);
        } 
 
        /// 
        /// Executes a DbCommand and returns the number of rows affected. 
        /// 
        private int ExecuteDbCommand(DbCommand command, DataSourceOperation operation) {
            int rowsAffected = 0;
 
            bool eventRaised = false;
            try { 
                if (command.Connection.State != ConnectionState.Open) { 
                    command.Connection.Open();
                } 

                rowsAffected = command.ExecuteNonQuery();

                if (rowsAffected > 0) { 
                    OnDataSourceViewChanged(EventArgs.Empty);
 
                    DataSourceCache cache = _owner.Cache; 
                    if ((cache != null) && (cache.Enabled)) {
                        _owner.InvalidateCacheEntry(); 
                    }
                }

                // Raise appropriate event 
                eventRaised = true;
                SqlDataSourceStatusEventArgs eventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, null); 
                switch (operation) { 
                    case DataSourceOperation.Delete:
                        OnDeleted(eventArgs); 
                        break;
                    case DataSourceOperation.Insert:
                        OnInserted(eventArgs);
                        break; 
                    case DataSourceOperation.Update:
                        OnUpdated(eventArgs); 
                        break; 
                }
            } 
            catch (Exception ex) {
                if (!eventRaised) {
                    // Raise appropriate event
                    SqlDataSourceStatusEventArgs eventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, ex); 
                    switch (operation) {
                        case DataSourceOperation.Delete: 
                            OnDeleted(eventArgs); 
                            break;
                        case DataSourceOperation.Insert: 
                            OnInserted(eventArgs);
                            break;
                        case DataSourceOperation.Update:
                            OnUpdated(eventArgs); 
                            break;
                    } 
                    if (!eventArgs.ExceptionHandled) { 
                        throw;
                    } 
                }
                else {
                    bool isCustomException;
                    ex = BuildCustomException(ex, operation, command, out isCustomException); 
                    if (isCustomException) {
                        throw ex; 
                    } 
                    else {
                        throw; 
                    }
                }
            }
            finally { 
                if (command.Connection.State == ConnectionState.Open) {
                    command.Connection.Close(); 
                } 
            }
 
            return rowsAffected;
        }

 
        /// 
        /// Deletes rows from the data source with given parameters. 
        ///  
        protected override int ExecuteDelete(IDictionary keys, IDictionary oldValues) {
            if (!CanDelete) { 
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_DeleteNotSupported, _owner.ID));
            }

            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString); 

            if (connection == null) { 
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID)); 
            }
 
            // Create command and add parameters
            string oldValuesParameterFormatString = OldValuesParameterFormatString;
            DbCommand command = _owner.CreateCommand(DeleteCommand, connection);
            InitializeParameters(command, DeleteParameters, oldValues); 
            AddParameters(command, DeleteParameters, keys, null, oldValuesParameterFormatString);
            if (ConflictDetection == ConflictOptions.CompareAllValues) { 
                if (oldValues == null || oldValues.Count == 0) { 
                    throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_Pessimistic, SR.GetString(SR.DataSourceView_delete), _owner.ID, "values"));
                } 
                AddParameters(command, DeleteParameters, oldValues, null, oldValuesParameterFormatString);
            }
            command.CommandType = GetCommandType(DeleteCommandType);
 
            // Raise event to allow customization and cancellation
            SqlDataSourceCommandEventArgs eventArgs = new SqlDataSourceCommandEventArgs(command); 
            OnDeleting(eventArgs); 

            // If the operation was cancelled, exit immediately 
            if (eventArgs.Cancel) {
                return 0;
            }
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 
 
            return ExecuteDbCommand(command, DataSourceOperation.Delete);
        } 


        /// 
        /// Inserts a new row with data from a name/value collection. 
        /// 
        protected override int ExecuteInsert(IDictionary values) { 
            if (!CanInsert) { 
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_InsertNotSupported, _owner.ID));
            } 

            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString);

            if (connection == null) { 
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID));
            } 
 
            // Create command and add parameters
            DbCommand command = _owner.CreateCommand(InsertCommand, connection); 
            InitializeParameters(command, InsertParameters, null);
            AddParameters(command, InsertParameters, values, null, null);
            command.CommandType = GetCommandType(InsertCommandType);
 
            // Raise event to allow customization and cancellation
            SqlDataSourceCommandEventArgs eventArgs = new SqlDataSourceCommandEventArgs(command); 
            OnInserting(eventArgs); 

            // If the operation was cancelled, exit immediately 
            if (eventArgs.Cancel) {
                return 0;
            }
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 
 
            return ExecuteDbCommand(command, DataSourceOperation.Insert);
        } 


        /// 
        /// Returns all the rows of the datasource. 
        /// Parameters are taken from the SqlDataSource.Parameters property collection.
        /// If DataSourceMode is set to DataSet then a DataView is returned. 
        /// If DataSourceMode is set to DataReader then a DataReader is returned, and it must be closed when done. 
        /// 
        protected internal override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments) { 
            if (SelectCommand.Length == 0) {
                return null;
            }
 
            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString);
 
            if (connection == null) { 
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID));
            } 

            DataSourceCache cache = _owner.Cache;
            bool cacheEnabled = (cache != null) && (cache.Enabled);
            //int startRowIndex = arguments.StartRowIndex; 
            //int maximumRows = arguments.MaximumRows;
            string sortExpression = arguments.SortExpression; 
 
            if (CanPage) {
                arguments.AddSupportedCapabilities(DataSourceCapabilities.Page); 
            }

            if (CanSort) {
                arguments.AddSupportedCapabilities(DataSourceCapabilities.Sort); 
            }
 
            if (CanRetrieveTotalRowCount) { 
                arguments.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
            } 


            // If caching is enabled, load DataSet from cache
            if (cacheEnabled) { 
                if (_owner.DataSourceMode != SqlDataSourceMode.DataSet) {
                    throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_CacheNotSupported, _owner.ID)); 
                } 

                arguments.RaiseUnsupportedCapabilitiesError(this); 

                DataSet dataSet = _owner.LoadDataFromCache(0, -1) as DataSet;

                if (dataSet != null) { 
                    /*if (arguments.RetrieveTotalRowCount) {
                        int cachedTotalRowCount = _owner.LoadTotalRowCountFromCache(); 
                        if (cachedTotalRowCount >= 0) { 
                            arguments.TotalRowCount = cachedTotalRowCount;
                        } 
                        else {
                            // query for row count and then save it in cache
                            cachedTotalRowCount = QueryTotalRowCount(connection, arguments);
                            arguments.TotalRowCount = cachedTotalRowCount; 
                            _owner.SaveTotalRowCountToCache(cachedTotalRowCount);
                        } 
                    }*/ 
                    IOrderedDictionary parameterValues = FilterParameters.GetValues(_context, _owner);
                    if (FilterExpression.Length > 0) { 
                        SqlDataSourceFilteringEventArgs filterArgs = new SqlDataSourceFilteringEventArgs(parameterValues);
                        OnFiltering(filterArgs);
                        if (filterArgs.Cancel) {
                            return null; 
                        }
                    } 
                    return FilteredDataSetHelper.CreateFilteredDataView(dataSet.Tables[0], sortExpression, FilterExpression, parameterValues); 
                }
            } 

            // Create command and add parameters
            DbCommand command = _owner.CreateCommand(SelectCommand, connection);
            InitializeParameters(command, SelectParameters, null); 
            command.CommandType = GetCommandType(SelectCommandType);
 
            // Raise event to allow customization and cancellation 
            SqlDataSourceSelectingEventArgs selectingEventArgs = new SqlDataSourceSelectingEventArgs(command, arguments);
            OnSelecting(selectingEventArgs); 

            // If the operation was cancelled, exit immediately
            if (selectingEventArgs.Cancel) {
                return null; 
            }
 
            // Add the sort parameter to allow for custom stored procedure sorting, if necessary 
            string sortParameterName = SortParameterName;
            if (sortParameterName.Length > 0) { 
                if (command.CommandType != CommandType.StoredProcedure) {
                    throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_SortParameterRequiresStoredProcedure, _owner.ID));
                }
                command.Parameters.Add(_owner.CreateParameter(ParameterPrefix + sortParameterName, sortExpression)); 

                // We reset the sort expression here so that we pretend as 
                // though we're not really sorting (since the developer is 
                // worrying about it instead of us).
                arguments.SortExpression = String.Empty; 
            }

            arguments.RaiseUnsupportedCapabilitiesError(this);
 
            // reset these values, since they might have changed in the OnSelecting event
            sortExpression = arguments.SortExpression; 
            //startRowIndex = arguments.StartRowIndex; 
            //maximumRows = arguments.MaximumRows;
 
            // Perform null check if user wants to cancel on any null parameter value
            if (CancelSelectOnNullParameter) {
                int paramCount = command.Parameters.Count;
                for (int i = 0; i < paramCount; i++) { 
                    DbParameter parameter = command.Parameters[i];
                    if ((parameter != null) && 
                        (parameter.Value == null) && 
                        ((parameter.Direction == ParameterDirection.Input) || (parameter.Direction == ParameterDirection.InputOutput))) {
                        return null; 
                    }
                }
            }
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 
 
            /*if (arguments.RetrieveTotalRowCount && SelectCountCommand.Length > 0) {
                int cachedTotalRowCount = -1; 
                if (cacheEnabled) {
                    cachedTotalRowCount = _owner.LoadTotalRowCountFromCache();
                    if (cachedTotalRowCount >= 0) {
                        arguments.TotalRowCount = cachedTotalRowCount; 
                    }
                } 
                if (cachedTotalRowCount < 0) { 
                    cachedTotalRowCount = QueryTotalRowCount(connection, arguments);
                    arguments.TotalRowCount = cachedTotalRowCount; 
                    if (cacheEnabled) {
                        _owner.SaveTotalRowCountToCache(cachedTotalRowCount);
                    }
                } 
            }*/
 
            IEnumerable selectResult = null; 

            switch (_owner.DataSourceMode) { 
                case SqlDataSourceMode.DataSet:
                {
                    SqlCacheDependency cacheDependency = null;
                    if (cacheEnabled && cache is SqlDataSourceCache) { 
                        SqlDataSourceCache sqlCache = (SqlDataSourceCache)cache;
                        if (String.Equals(sqlCache.SqlCacheDependency, SqlDataSourceCache.Sql9CacheDependencyDirective, StringComparison.OrdinalIgnoreCase)) { 
                            if (!(command is System.Data.SqlClient.SqlCommand)) { 
                                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CommandNotificationNotSupported, _owner.ID));
                            } 
                            cacheDependency = new SqlCacheDependency((System.Data.SqlClient.SqlCommand)command);
                        }
                    }
 
                    DbDataAdapter adapter = _owner.CreateDataAdapter(command);
 
                    DataSet dataSet = new DataSet(); 
                    int rowsAffected = 0;
 
                    bool eventRaised = false;
                    try {
                        rowsAffected = adapter.Fill(dataSet, Name);
 
                        // Raise the Selected event
                        eventRaised = true; 
                        SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, null); 
                        OnSelected(selectedEventArgs);
                    } 
                    catch (Exception ex) {
                        if (!eventRaised) {
                            // Raise the Selected event
                            SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, ex); 
                            OnSelected(selectedEventArgs);
                            if (!selectedEventArgs.ExceptionHandled) { 
                                throw; 
                            }
                        } 
                        else {
                            bool isCustomException;
                            ex = BuildCustomException(ex, DataSourceOperation.Select, command, out isCustomException);
                            if (isCustomException) { 
                                throw ex;
                            } 
                            else { 
                                throw;
                            } 
                        }
                    }
                    finally {
                        if (connection.State == ConnectionState.Open) { 
                            connection.Close();
                        } 
                    } 

                    // If caching is enabled, save DataSet to cache 
                    DataTable dataTable = (dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null);
                    if (cacheEnabled && dataTable != null) {
                        _owner.SaveDataToCache(0, -1, dataSet, cacheDependency);
                    } 

                    if (dataTable != null) { 
                        IOrderedDictionary parameterValues = FilterParameters.GetValues(_context, _owner); 
                        if (FilterExpression.Length > 0) {
                            SqlDataSourceFilteringEventArgs filterArgs = new SqlDataSourceFilteringEventArgs(parameterValues); 
                            OnFiltering(filterArgs);
                            if (filterArgs.Cancel) {
                                return null;
                            } 
                        }
                        selectResult = FilteredDataSetHelper.CreateFilteredDataView(dataTable, sortExpression, FilterExpression, parameterValues); 
                    } 
                    break;
                } 

                case SqlDataSourceMode.DataReader:
                {
                    if (FilterExpression.Length > 0) { 
                        throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_FilterNotSupported, _owner.ID));
                    } 
 
                    if (sortExpression.Length > 0) {
                        throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_SortNotSupported, _owner.ID)); 
                    }

                    bool eventRaised = false;
                    try { 
                        if (connection.State != ConnectionState.Open) {
                            connection.Open(); 
                        } 

                        selectResult = command.ExecuteReader(CommandBehavior.CloseConnection); 

                        // Raise the Selected event
                        eventRaised = true;
                        SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, 0, null); 
                        OnSelected(selectedEventArgs);
                    } 
                    catch (Exception ex) { 
                        if (!eventRaised) {
                            // Raise the Selected event 
                            SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, 0, ex);
                            OnSelected(selectedEventArgs);
                            if (!selectedEventArgs.ExceptionHandled) {
                                throw; 
                            }
                        } 
                        else { 
                            bool isCustomException;
                            ex = BuildCustomException(ex, DataSourceOperation.Select, command, out isCustomException); 
                            if (isCustomException) {
                                throw ex;
                            }
                            else { 
                                throw;
                            } 
                        } 
                    }
                    break; 
                }
            }
            return selectResult;
        } 

 
        ///  
        /// Updates rows matching the parameter collection and setting new values from the name/value values collection.
        ///  
        protected override int ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) {
            if (!CanUpdate) {
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_UpdateNotSupported, _owner.ID));
            } 

            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString); 
 
            if (connection == null) {
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID)); 
            }

            // Create command and add parameters
            string oldValuesParameterFormatString = OldValuesParameterFormatString; 
            DbCommand command = _owner.CreateCommand(UpdateCommand, connection);
            InitializeParameters(command, UpdateParameters, keys); 
            AddParameters(command, UpdateParameters, values, null, null); 
            AddParameters(command, UpdateParameters, keys, null, oldValuesParameterFormatString);
            if (ConflictDetection == ConflictOptions.CompareAllValues) { 
                if (oldValues == null || oldValues.Count == 0) {
                    throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_Pessimistic, SR.GetString(SR.DataSourceView_update), _owner.ID, "oldValues"));
                }
                AddParameters(command, UpdateParameters, oldValues, null, oldValuesParameterFormatString); 
            }
            command.CommandType = GetCommandType(UpdateCommandType); 
 
            // Raise event to allow customization and cancellation
            SqlDataSourceCommandEventArgs eventArgs = new SqlDataSourceCommandEventArgs(command); 
            OnUpdating(eventArgs);

            // If the operation was cancelled, exit immediately
            if (eventArgs.Cancel) { 
                return 0;
            } 
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 

            return ExecuteDbCommand(command, DataSourceOperation.Update);
        }
 
        /// 
        /// Converts a SqlDataSourceCommandType to a System.Data.CommandType. 
        ///  
        private static CommandType GetCommandType(SqlDataSourceCommandType commandType) {
            if (commandType == SqlDataSourceCommandType.Text) { 
                return CommandType.Text;
            }
            return CommandType.StoredProcedure;
        } 

        ///  
        /// Initializes a DbCommand with parameters from a ParameterCollection. 
        /// The exclusion list contains parameter names that should not be added
        /// to the command's parameter collection. 
        /// 
        private void InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) {
            Debug.Assert(command != null);
            Debug.Assert(parameters != null); 

            string parameterPrefix = ParameterPrefix; 
 
            IDictionary caseInsensitiveExclusionList = null;
            if (exclusionList != null) { 
                caseInsensitiveExclusionList = new ListDictionary(StringComparer.OrdinalIgnoreCase);
                foreach (DictionaryEntry de in exclusionList) {
                    caseInsensitiveExclusionList.Add(de.Key, de.Value);
                } 
            }
 
            IOrderedDictionary values = parameters.GetValues(_context, _owner); 
            for (int i = 0; i < parameters.Count; i++) {
                Parameter parameter = parameters[i]; 
                if ((caseInsensitiveExclusionList == null) || (!caseInsensitiveExclusionList.Contains(parameter.Name))) {
                    DbParameter dbParameter = _owner.CreateParameter(parameterPrefix + parameter.Name, values[i]);
                    dbParameter.Direction = parameter.Direction;
                    dbParameter.Size = parameter.Size; 
                    if (parameter.DbType != DbType.Object || (parameter.Type != TypeCode.Empty && parameter.Type != TypeCode.DBNull)) {
                        SqlParameter sqlParameter = dbParameter as SqlParameter; 
                        if (sqlParameter == null) { 
                            dbParameter.DbType = parameter.GetDatabaseType();
                        } 
                        else {
                            // In Whidbey, the DbType Date and Time members mapped to SqlDbType.DateTime since there
                            // were no SqlDbType equivalents. SqlDbType has since been modified to include the new
                            // Katmai types, including Date and Time. For backwards compatability SqlParameter's DbType 
                            // setter doesn't support Date and Time, so the SqlDbType property should be used instead.
                            // Other new SqlServer 2008 types (DateTime2, DateTimeOffset) can be set using DbType. 
                            DbType dbType = parameter.GetDatabaseType(); 
                            switch (dbType) {
                                case DbType.Time: 
                                    sqlParameter.SqlDbType = SqlDbType.Time;
                                    break;
                                case DbType.Date:
                                    sqlParameter.SqlDbType = SqlDbType.Date; 
                                    break;
                                default: 
                                    dbParameter.DbType = parameter.GetDatabaseType(); 
                                    break;
                            } 
                        }
                    }
                    command.Parameters.Add(dbParameter);
                } 
            }
        } 
 
        public int Insert(IDictionary values) {
            return ExecuteInsert(values); 
        }


        ///  
        /// Loads view state.
        ///  
        protected virtual void LoadViewState(object savedState) { 
            if (savedState == null)
                return; 

            Pair myState = (Pair)savedState;

            if (myState.First != null) 
                ((IStateManager)SelectParameters).LoadViewState(myState.First);
 
            if (myState.Second != null) 
                ((IStateManager)FilterParameters).LoadViewState(myState.Second);
        } 


        /// 
        /// Raises the Deleted event. 
        /// 
        protected virtual void OnDeleted(SqlDataSourceStatusEventArgs e) { 
            SqlDataSourceStatusEventHandler handler = Events[EventDeleted] as SqlDataSourceStatusEventHandler; 
            if (handler != null) {
                handler(this, e); 
            }
        }

 
        /// 
        /// Raises the Deleting event. 
        ///  
        protected virtual void OnDeleting(SqlDataSourceCommandEventArgs e) {
            SqlDataSourceCommandEventHandler handler = Events[EventDeleting] as SqlDataSourceCommandEventHandler; 
            if (handler != null) {
                handler(this, e);
            }
        } 

        protected virtual void OnFiltering(SqlDataSourceFilteringEventArgs e) { 
            SqlDataSourceFilteringEventHandler handler = Events[EventFiltering] as SqlDataSourceFilteringEventHandler; 
            if (handler != null) {
                handler(this, e); 
            }
        }

 
        /// 
        /// Raises the Inserted event. 
        ///  
        protected virtual void OnInserted(SqlDataSourceStatusEventArgs e) {
            SqlDataSourceStatusEventHandler handler = Events[EventInserted] as SqlDataSourceStatusEventHandler; 
            if (handler != null) {
                handler(this, e);
            }
        } 

 
        ///  
        /// Raises the Inserting event.
        ///  
        protected virtual void OnInserting(SqlDataSourceCommandEventArgs e) {
            SqlDataSourceCommandEventHandler handler = Events[EventInserting] as SqlDataSourceCommandEventHandler;
            if (handler != null) {
                handler(this, e); 
            }
        } 
 

        ///  
        /// Raises the Selected event.
        /// 
        protected virtual void OnSelected(SqlDataSourceStatusEventArgs e) {
            SqlDataSourceStatusEventHandler handler = Events[EventSelected] as SqlDataSourceStatusEventHandler; 
            if (handler != null) {
                handler(this, e); 
            } 
        }
 

        /// 
        /// Raises the Selecting event.
        ///  
        protected virtual void OnSelecting(SqlDataSourceSelectingEventArgs e) {
            SqlDataSourceSelectingEventHandler handler = Events[EventSelecting] as SqlDataSourceSelectingEventHandler; 
            if (handler != null) { 
                handler(this, e);
            } 
        }


        ///  
        /// Raises the Updated event.
        ///  
        protected virtual void OnUpdated(SqlDataSourceStatusEventArgs e) { 
            SqlDataSourceStatusEventHandler handler = Events[EventUpdated] as SqlDataSourceStatusEventHandler;
            if (handler != null) { 
                handler(this, e);
            }
        }
 

        ///  
        /// Raises the Updating event. 
        /// 
        protected virtual void OnUpdating(SqlDataSourceCommandEventArgs e) { 
            SqlDataSourceCommandEventHandler handler = Events[EventUpdating] as SqlDataSourceCommandEventHandler;
            if (handler != null) {
                handler(this, e);
            } 
        }
 
 
        /// 
        /// Executes the SelectCountCommand to retrieve the total row count. 
        /// 
        /*protected virtual int QueryTotalRowCount(DbConnection connection, DataSourceSelectArguments arguments) {
            int totalRowCount = 0;
            bool eventRaised = false; 

            if (SelectCountCommand.Length > 0) { 
                // Create command and add parameters 
                DbCommand command = _owner.CreateCommand(SelectCountCommand, connection);
                InitializeParameters(command, SelectParameters); 
                command.CommandType = GetCommandType(SelectCountCommand, SelectCompareString);

                // Raise event to allow customization and cancellation
                SqlDataSourceSelectingEventArgs selectCountingEventArgs = new SqlDataSourceSelectingEventArgs(command, arguments, true); 
                OnSelecting(selectCountingEventArgs);
 
                // If the operation was cancelled, exit immediately 
                if (selectCountingEventArgs.Cancel) {
                    return totalRowCount; 
                }

                // the arguments may have been changed
                arguments.RaiseUnsupportedCapabilitiesError(this); 

                // 
 

 



 

 
 

 



 

 
 

 



 

 
 

 



 

 
 

*/ 


        protected internal override void RaiseUnsupportedCapabilityError(DataSourceCapabilities capability) {
            if (!CanPage && ((capability & DataSourceCapabilities.Page) != 0)) { 
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_NoPaging, _owner.ID));
            } 
 
            if (!CanSort && ((capability & DataSourceCapabilities.Sort) != 0)) {
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_NoSorting, _owner.ID)); 
            }

            if (!CanRetrieveTotalRowCount && ((capability & DataSourceCapabilities.RetrieveTotalRowCount) != 0)) {
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_NoRowCount, _owner.ID)); 
            }
            base.RaiseUnsupportedCapabilityError(capability); 
        } 

        ///  
        /// Replace null values in parameters with DBNull.Value.
        /// 
        private void ReplaceNullValues(DbCommand command) {
            int paramCount = command.Parameters.Count; 
            foreach (DbParameter parameter in command.Parameters) {
                if (parameter.Value == null) { 
                    parameter.Value = DBNull.Value; 
                }
            } 
        }

        /// 
        /// Saves view state. 
        /// 
        protected virtual object SaveViewState() { 
            Pair myState = new Pair(); 

            myState.First = (_selectParameters != null) ? ((IStateManager)_selectParameters).SaveViewState() : null; 
            myState.Second = (_filterParameters != null) ? ((IStateManager)_filterParameters).SaveViewState() : null;

            if ((myState.First == null) &&
                (myState.Second == null)) { 
                return null;
            } 
 
            return myState;
        } 

        public IEnumerable Select(DataSourceSelectArguments arguments) {
            return ExecuteSelect(arguments);
        } 

        ///  
        /// Event handler for SelectParametersChanged event. 
        /// 
        private void SelectParametersChangedEventHandler(object o, EventArgs e) { 
            OnDataSourceViewChanged(EventArgs.Empty);
        }

 
        /// 
        /// Starts tracking view state. 
        ///  
        protected virtual void TrackViewState() {
            _tracking = true; 

            if (_selectParameters != null) {
                ((IStateManager)_selectParameters).TrackViewState();
            } 
            if (_filterParameters != null) {
                ((IStateManager)_filterParameters).TrackViewState(); 
            } 
        }
 
        public int Update(IDictionary keys, IDictionary values, IDictionary oldValues) {
            return ExecuteUpdate(keys, values, oldValues);
        }
 

        #region IStateManager implementation 
        bool IStateManager.IsTrackingViewState { 
            get {
                return IsTrackingViewState; 
            }
        }

        void IStateManager.LoadViewState(object savedState) { 
            LoadViewState(savedState);
        } 
 
        object IStateManager.SaveViewState() {
            return SaveViewState(); 
        }

        void IStateManager.TrackViewState() {
            TrackViewState(); 
        }
        #endregion 
    } 
}
 

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

namespace System.Web.UI.WebControls { 
 
    using System;
    using System.Collections; 
    using System.Collections.Specialized;
    using System.ComponentModel;
    using System.Data;
    using System.Data.Common; 
    using System.Data.SqlClient;
    using System.Drawing.Design; 
    using System.Globalization; 
    using System.IO;
    using System.Text; 
    using System.Web;
    using System.Web.Caching;
    using System.Web.UI;
    using System.Web.UI.WebControls; 
    using System.Web.Util;
 
    using ConflictOptions = System.Web.UI.ConflictOptions; 

 
    /// 
    /// Represents a single view of a SqlDataSource.
    /// 
    public class SqlDataSourceView : DataSourceView, IStateManager { 

        private const int MustDeclareVariableSqlExceptionNumber = 137; 
        private const int ProcedureExpectsParameterSqlExceptionNumber = 201; 

        private static readonly object EventDeleted = new object(); 
        private static readonly object EventDeleting = new object();
        private static readonly object EventFiltering = new object();
        private static readonly object EventInserted = new object();
        private static readonly object EventInserting = new object(); 
        private static readonly object EventSelected = new object();
        private static readonly object EventSelecting = new object(); 
        private static readonly object EventUpdated = new object(); 
        private static readonly object EventUpdating = new object();
 
        private HttpContext _context;
        private SqlDataSource _owner;
        private bool _tracking;
 
        private bool _cancelSelectOnNullParameter = true;
        private ConflictOptions _conflictDetection = ConflictOptions.OverwriteChanges; 
        private string _deleteCommand; 
        private SqlDataSourceCommandType _deleteCommandType = SqlDataSourceCommandType.Text;
        private ParameterCollection _deleteParameters; 
        private string _filterExpression;
        private ParameterCollection _filterParameters;
        private string _insertCommand;
        private SqlDataSourceCommandType _insertCommandType = SqlDataSourceCommandType.Text; 
        private ParameterCollection _insertParameters;
        private string _oldValuesParameterFormatString; 
        private string _selectCommand; 
        private SqlDataSourceCommandType _selectCommandType = SqlDataSourceCommandType.Text;
        private ParameterCollection _selectParameters; 
        private string _sortParameterName;
        private string _updateCommand;
        private SqlDataSourceCommandType _updateCommandType = SqlDataSourceCommandType.Text;
        private ParameterCollection _updateParameters; 

 
        ///  
        /// Creates a new instance of SqlDataSourceView.
        ///  
        public SqlDataSourceView(SqlDataSource owner, string name, HttpContext context) : base(owner, name) {
            _owner = owner;
            _context = context;
        } 

 
        public bool CancelSelectOnNullParameter { 
            get {
                return _cancelSelectOnNullParameter; 
            }
            set {
                if (CancelSelectOnNullParameter != value) {
                    _cancelSelectOnNullParameter = value; 
                    OnDataSourceViewChanged(EventArgs.Empty);
                } 
            } 
        }
 
        /// 
        /// Indicates that the view can delete rows.
        /// 
        public override bool CanDelete { 
            get {
                return (DeleteCommand.Length != 0); 
            } 
        }
 
        /// 
        /// Indicates that the view can add new rows.
        /// 
        public override bool CanInsert { 
            get {
                return (InsertCommand.Length != 0); 
            } 
        }
 
        /// 
        /// Indicates that the view can page the datasource on the server.
        /// 
        public override bool CanPage { 
            get {
                return false; 
            } 
        }
 
        /// 
        /// Indicates that the view can return the total number of rows returned by the query.
        /// 
        public override bool CanRetrieveTotalRowCount { 
            get {
                return false; 
            } 
        }
 
        /// 
        /// Indicates that the view can sort rows.
        /// 
        public override bool CanSort { 
            get {
                return (_owner.DataSourceMode == SqlDataSourceMode.DataSet) || (SortParameterName.Length > 0); 
            } 
        }
 
        /// 
        /// Indicates that the view can update rows.
        /// 
        public override bool CanUpdate { 
            get {
                return (UpdateCommand.Length != 0); 
            } 
        }
 
        /// 
        /// Whether commands pass old values in the parameter collection.
        /// 
        public ConflictOptions ConflictDetection { 
            get {
                return _conflictDetection; 
            } 
            set {
                if ((value < ConflictOptions.OverwriteChanges) || (value > ConflictOptions.CompareAllValues)) { 
                    throw new ArgumentOutOfRangeException("value");
                }
                _conflictDetection = value;
                OnDataSourceViewChanged(EventArgs.Empty); 
            }
        } 
 
        /// 
        /// The command to execute when Delete() is called on the SqlDataSourceView. 
        /// 
        public string DeleteCommand {
            get {
                if (_deleteCommand == null) { 
                    return String.Empty;
                } 
                return _deleteCommand; 
            }
            set { 
                _deleteCommand = value;
            }
        }
 
        public SqlDataSourceCommandType DeleteCommandType {
            get { 
                return _deleteCommandType; 
            }
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) {
                    throw new ArgumentOutOfRangeException("value");
                }
                _deleteCommandType = value; 
            }
        } 
 
        /// 
        /// Collection of parameters used in Delete(). 
        /// 
        [
        DefaultValue(null),
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)), 
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_DeleteParameters), 
        ] 
        public ParameterCollection DeleteParameters {
            get { 
                if (_deleteParameters == null) {
                    _deleteParameters = new ParameterCollection();
                }
                return _deleteParameters; 
            }
        } 
 
        /// 
        /// The filter to apply when Select() is called on the SqlDataSourceView. 
        /// 
        public string FilterExpression {
            get {
                if (_filterExpression == null) { 
                    return String.Empty;
                } 
                return _filterExpression; 
            }
            set { 
                if (FilterExpression != value) {
                    _filterExpression = value;
                    OnDataSourceViewChanged(EventArgs.Empty);
                } 
            }
        } 
 
        /// 
        /// Collection of parameters used in the FilterExpression property. 
        /// 
        [
        DefaultValue(null),
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)), 
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_FilterParameters), 
        ] 
        public ParameterCollection FilterParameters {
            get { 
                if (_filterParameters == null) {
                    _filterParameters = new ParameterCollection();

                    _filterParameters.ParametersChanged += new EventHandler(SelectParametersChangedEventHandler); 

                    if (_tracking) { 
                        ((IStateManager)_filterParameters).TrackViewState(); 
                    }
                } 
                return _filterParameters;
            }
        }
 
        /// 
        /// The command to execute when Insert() is called on the SqlDataSourceView. 
        ///  
        public string InsertCommand {
            get { 
                if (_insertCommand == null) {
                    return String.Empty;
                }
                return _insertCommand; 
            }
            set { 
                _insertCommand = value; 
            }
        } 

        public SqlDataSourceCommandType InsertCommandType {
            get {
                return _insertCommandType; 
            }
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) { 
                    throw new ArgumentOutOfRangeException("value");
                } 
                _insertCommandType = value;
            }
        }
 
        /// 
        /// Collection of values used in Insert(). 
        ///  
        [
        DefaultValue(null), 
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)),
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_InsertParameters),
        ] 
        public ParameterCollection InsertParameters {
            get { 
                if (_insertParameters == null) { 
                    _insertParameters = new ParameterCollection();
                } 
                return _insertParameters;
            }
        }
 
        /// 
        /// Returns whether this object is tracking view state. 
        ///  
        protected bool IsTrackingViewState {
            get { 
                return _tracking;
            }
        }
 
        /// 
        /// The format string applied to the names of the old values parameters 
        ///  
        [
        DefaultValue("{0}"), 
        WebCategory("Data"),
        WebSysDescription(SR.DataSource_OldValuesParameterFormatString),
        ]
        public string OldValuesParameterFormatString { 
            get {
                if (_oldValuesParameterFormatString == null) { 
                    return "{0}"; 
                }
                return _oldValuesParameterFormatString; 
            }
            set {
                _oldValuesParameterFormatString = value;
                OnDataSourceViewChanged(EventArgs.Empty); 
            }
        } 
 
        /// 
        /// Indicates the prefix for parameters. 
        /// 
        protected virtual string ParameterPrefix {
            get {
                if (String.IsNullOrEmpty(_owner.ProviderName) || 
                    String.Equals(_owner.ProviderName, "System.Data.SqlClient", StringComparison.OrdinalIgnoreCase)) {
                    return "@"; 
                } 
                else {
                    return String.Empty; 
                }
            }
        }
 
        /// 
        /// The command to execute when Select() is called on the SqlDataSourceView. 
        ///  
        public string SelectCommand {
            get { 
                if (_selectCommand == null) {
                    return String.Empty;
                }
                return _selectCommand; 
            }
            set { 
                if (SelectCommand != value) { 
                    _selectCommand = value;
                    OnDataSourceViewChanged(EventArgs.Empty); 
                }
            }
        }
 
        public SqlDataSourceCommandType SelectCommandType {
            get { 
                return _selectCommandType; 
            }
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) {
                    throw new ArgumentOutOfRangeException("value");
                }
                _selectCommandType = value; 
            }
        } 
 

        ///  
        /// The command to execute when Select is called on the SqlDataSourceView and the total rows is requested.
        /// 
        /*public string SelectCountCommand {
            get { 
                if (_selectCountCommand == null) {
                    return String.Empty; 
                } 
                return _selectCountCommand;
            } 
            set {
                if (SelectCountCommand != value) {
                    _selectCountCommand = value;
                    OnDataSourceViewChanged(EventArgs.Empty); 
                }
            } 
        }*/ 

 
        /// 
        /// Collection of parameters used in Select().
        /// 
        public ParameterCollection SelectParameters { 
            get {
                if (_selectParameters == null) { 
                    _selectParameters = new ParameterCollection(); 

                    _selectParameters.ParametersChanged += new EventHandler(SelectParametersChangedEventHandler); 

                    if (_tracking) {
                        ((IStateManager)_selectParameters).TrackViewState();
                    } 
                }
                return _selectParameters; 
            } 
        }
 

        /// 
        /// The name of the parameter in the SelectCommand that specifies the
        /// sort expression. This parameter's value will be automatically set 
        /// at runtime with the appropriate sort expression. This is only
        /// supported for stored procedure commands. 
        ///  
        public string SortParameterName {
            get { 
                if (_sortParameterName == null) {
                    return String.Empty;
                }
                return _sortParameterName; 
            }
            set { 
                if (SortParameterName != value) { 
                    _sortParameterName = value;
                    OnDataSourceViewChanged(EventArgs.Empty); 
                }
            }
        }
 

        ///  
        /// The command to execute when Update() is called on the SqlDataSourceView. 
        /// 
        public string UpdateCommand { 
            get {
                if (_updateCommand == null) {
                    return String.Empty;
                } 
                return _updateCommand;
            } 
            set { 
                _updateCommand = value;
            } 
        }

        public SqlDataSourceCommandType UpdateCommandType {
            get { 
                return _updateCommandType;
            } 
            set { 
                if ((value < SqlDataSourceCommandType.Text) || (value > SqlDataSourceCommandType.StoredProcedure)) {
                    throw new ArgumentOutOfRangeException("value"); 
                }
                _updateCommandType = value;
            }
        } 

        ///  
        /// Collection of parameters used in Update(). 
        /// 
        [ 
        DefaultValue(null),
        Editor("System.Web.UI.Design.WebControls.ParameterCollectionEditor, " + AssemblyRef.SystemDesign, typeof(UITypeEditor)),
        PersistenceMode(PersistenceMode.InnerProperty),
        WebSysDescription(SR.SqlDataSource_UpdateParameters), 
        ]
        public ParameterCollection UpdateParameters { 
            get { 
                if (_updateParameters == null) {
                    _updateParameters = new ParameterCollection(); 
                }
                return _updateParameters;
            }
        } 

 
        ///  
        /// This event is raised after the Delete operation has completed.
        /// Handle this event if you need to examine the values of output parameters. 
        /// 
        public event SqlDataSourceStatusEventHandler Deleted {
            add {
                Events.AddHandler(EventDeleted, value); 
            }
            remove { 
                Events.RemoveHandler(EventDeleted, value); 
            }
        } 

        /// 
        /// This event is raised before the Delete operation has been executed.
        /// Handle this event if you want to perform additional initialization operations 
        /// that are specific to your application. You can also handle this event if you
        /// need to validate the values of parameters or change their values. 
        /// When this event is raised, the database connection is not open yet, and you 
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs
        /// to true. 
        /// 
        public event SqlDataSourceCommandEventHandler Deleting {
            add {
                Events.AddHandler(EventDeleting, value); 
            }
            remove { 
                Events.RemoveHandler(EventDeleting, value); 
            }
        } 

        public event SqlDataSourceFilteringEventHandler Filtering {
            add {
                Events.AddHandler(EventFiltering, value); 
            }
            remove { 
                Events.RemoveHandler(EventFiltering, value); 
            }
        } 

        /// 
        /// This event is raised after the Insert operation has completed.
        /// Handle this event if you need to examine the values of output parameters. 
        /// 
        public event SqlDataSourceStatusEventHandler Inserted { 
            add { 
                Events.AddHandler(EventInserted, value);
            } 
            remove {
                Events.RemoveHandler(EventInserted, value);
            }
        } 

        ///  
        /// This event is raised before the Insert operation has been executed. 
        /// Handle this event if you want to perform additional initialization operations
        /// that are specific to your application. You can also handle this event if you 
        /// need to validate the values of parameters or change their values.
        /// When this event is raised, the database connection is not open yet, and you
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs
        /// to true. 
        /// 
        public event SqlDataSourceCommandEventHandler Inserting { 
            add { 
                Events.AddHandler(EventInserting, value);
            } 
            remove {
                Events.RemoveHandler(EventInserting, value);
            }
        } 

        ///  
        /// This event is raised after the Select operation has completed. 
        /// Handle this event if you need to examine the values of output parameters.
        ///  
        public event SqlDataSourceStatusEventHandler Selected {
            add {
                Events.AddHandler(EventSelected, value);
            } 
            remove {
                Events.RemoveHandler(EventSelected, value); 
            } 
        }
 
        /// 
        /// This event is raised before the Select operation has been executed.
        /// Handle this event if you want to perform additional initialization operations
        /// that are specific to your application. You can also handle this event if you 
        /// need to validate the values of parameters or change their values.
        /// When this event is raised, the database connection is not open yet, and you 
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs 
        /// to true.
        ///  
        public event SqlDataSourceSelectingEventHandler Selecting {
            add {
                Events.AddHandler(EventSelecting, value);
            } 
            remove {
                Events.RemoveHandler(EventSelecting, value); 
            } 
        }
 
        /// 
        /// This event is raised after the Update operation has completed.
        /// Handle this event if you need to examine the values of output parameters.
        ///  
        public event SqlDataSourceStatusEventHandler Updated {
            add { 
                Events.AddHandler(EventUpdated, value); 
            }
            remove { 
                Events.RemoveHandler(EventUpdated, value);
            }
        }
 
        /// 
        /// This event is raised before the Update operation has been executed. 
        /// Handle this event if you want to perform additional initialization operations 
        /// that are specific to your application. You can also handle this event if you
        /// need to validate the values of parameters or change their values. 
        /// When this event is raised, the database connection is not open yet, and you
        /// can cancel the event by setting the Cancel property of the DataCommandEventArgs
        /// to true.
        ///  
        public event SqlDataSourceCommandEventHandler Updating {
            add { 
                Events.AddHandler(EventUpdating, value); 
            }
            remove { 
                Events.RemoveHandler(EventUpdating, value);
            }
        }
 

        ///  
        /// Adds parameters to an DbCommand from an IOrderedDictionary. 
        /// The exclusion list contains parameter names that should not be added
        /// to the command's parameter collection. 
        /// 
        private void AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, string oldValuesParameterFormatString) {
            Debug.Assert(command != null);
 
            IDictionary caseInsensitiveExclusionList = null;
            if (exclusionList != null) { 
                caseInsensitiveExclusionList = new ListDictionary(StringComparer.OrdinalIgnoreCase); 
                foreach (DictionaryEntry de in exclusionList) {
                    caseInsensitiveExclusionList.Add(de.Key, de.Value); 
                }
            }

            if (parameters != null) { 
                string parameterPrefix = ParameterPrefix;
                foreach (DictionaryEntry de in parameters) { 
                    string rawParamName = (string)de.Key; 

                    if ((caseInsensitiveExclusionList != null) && (caseInsensitiveExclusionList.Contains(rawParamName))) { 
                        // If we have an exclusion list and it contains this parameter, skip it
                        continue;
                    }
 
                    string formattedParamName;
                    if (oldValuesParameterFormatString == null) { 
                        formattedParamName = rawParamName; 
                    }
                    else { 
                        formattedParamName = String.Format(CultureInfo.InvariantCulture, oldValuesParameterFormatString, rawParamName);
                    }
                    object value = de.Value;
 
                    // If the reference collection contains this parameter, we will use
                    // the Parameter's settings to format the value 
                    Parameter parameter = reference[formattedParamName]; 
                    if (parameter != null) {
                        value = parameter.GetValue(de.Value, false); 
                    }

                    formattedParamName = parameterPrefix + formattedParamName;
 
                    if (command.Parameters.Contains(formattedParamName)) {
                        // We never overwrite an existing value with a null value 
                        if (value != null) { 
                            command.Parameters[formattedParamName].Value = value;
                        } 
                    }
                    else {
                        // Parameter does not exist, add a new one
                        DbParameter dbParameter = _owner.CreateParameter(formattedParamName, value); 
                        command.Parameters.Add(dbParameter);
                    } 
                } 
            }
        } 

        /// 
        /// Builds a custom exception for specific database errors.
        /// Currently the only custom exception text supported is for SQL Server 
        /// when a parameter is present in the command but not in the parameters
        /// collection. 
        /// The isCustomException parameter indicates whether a custom exception 
        /// was created or not. This way the caller can determine whether it wants
        /// to rethrow the original exception or throw the new custom exception. 
        /// 
        private Exception BuildCustomException(Exception ex, DataSourceOperation operation, DbCommand command, out bool isCustomException) {
            System.Data.SqlClient.SqlException sqlException = ex as System.Data.SqlClient.SqlException;
            if (sqlException != null) { 
                if ((sqlException.Number == MustDeclareVariableSqlExceptionNumber) ||
                    (sqlException.Number == ProcedureExpectsParameterSqlExceptionNumber)) { 
                    string parameterNames; 
                    if (command.Parameters.Count > 0) {
                        StringBuilder sb = new StringBuilder(); 
                        bool firstParameter = true;
                        foreach (DbParameter p in command.Parameters) {
                            if (!firstParameter) {
                                sb.Append(", "); 
                            }
                            sb.Append(p.ParameterName); 
                            firstParameter = false; 
                        }
                        parameterNames = sb.ToString(); 
                    }
                    else {
                        parameterNames = SR.GetString(SR.SqlDataSourceView_NoParameters);
                    } 
                    isCustomException = true;
                    return new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_MissingParameters, operation, _owner.ID, parameterNames)); 
                } 
            }
            isCustomException = false; 
            return ex;
        }

        public int Delete(IDictionary keys, IDictionary oldValues) { 
            return ExecuteDelete(keys, oldValues);
        } 
 
        /// 
        /// Executes a DbCommand and returns the number of rows affected. 
        /// 
        private int ExecuteDbCommand(DbCommand command, DataSourceOperation operation) {
            int rowsAffected = 0;
 
            bool eventRaised = false;
            try { 
                if (command.Connection.State != ConnectionState.Open) { 
                    command.Connection.Open();
                } 

                rowsAffected = command.ExecuteNonQuery();

                if (rowsAffected > 0) { 
                    OnDataSourceViewChanged(EventArgs.Empty);
 
                    DataSourceCache cache = _owner.Cache; 
                    if ((cache != null) && (cache.Enabled)) {
                        _owner.InvalidateCacheEntry(); 
                    }
                }

                // Raise appropriate event 
                eventRaised = true;
                SqlDataSourceStatusEventArgs eventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, null); 
                switch (operation) { 
                    case DataSourceOperation.Delete:
                        OnDeleted(eventArgs); 
                        break;
                    case DataSourceOperation.Insert:
                        OnInserted(eventArgs);
                        break; 
                    case DataSourceOperation.Update:
                        OnUpdated(eventArgs); 
                        break; 
                }
            } 
            catch (Exception ex) {
                if (!eventRaised) {
                    // Raise appropriate event
                    SqlDataSourceStatusEventArgs eventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, ex); 
                    switch (operation) {
                        case DataSourceOperation.Delete: 
                            OnDeleted(eventArgs); 
                            break;
                        case DataSourceOperation.Insert: 
                            OnInserted(eventArgs);
                            break;
                        case DataSourceOperation.Update:
                            OnUpdated(eventArgs); 
                            break;
                    } 
                    if (!eventArgs.ExceptionHandled) { 
                        throw;
                    } 
                }
                else {
                    bool isCustomException;
                    ex = BuildCustomException(ex, operation, command, out isCustomException); 
                    if (isCustomException) {
                        throw ex; 
                    } 
                    else {
                        throw; 
                    }
                }
            }
            finally { 
                if (command.Connection.State == ConnectionState.Open) {
                    command.Connection.Close(); 
                } 
            }
 
            return rowsAffected;
        }

 
        /// 
        /// Deletes rows from the data source with given parameters. 
        ///  
        protected override int ExecuteDelete(IDictionary keys, IDictionary oldValues) {
            if (!CanDelete) { 
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_DeleteNotSupported, _owner.ID));
            }

            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString); 

            if (connection == null) { 
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID)); 
            }
 
            // Create command and add parameters
            string oldValuesParameterFormatString = OldValuesParameterFormatString;
            DbCommand command = _owner.CreateCommand(DeleteCommand, connection);
            InitializeParameters(command, DeleteParameters, oldValues); 
            AddParameters(command, DeleteParameters, keys, null, oldValuesParameterFormatString);
            if (ConflictDetection == ConflictOptions.CompareAllValues) { 
                if (oldValues == null || oldValues.Count == 0) { 
                    throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_Pessimistic, SR.GetString(SR.DataSourceView_delete), _owner.ID, "values"));
                } 
                AddParameters(command, DeleteParameters, oldValues, null, oldValuesParameterFormatString);
            }
            command.CommandType = GetCommandType(DeleteCommandType);
 
            // Raise event to allow customization and cancellation
            SqlDataSourceCommandEventArgs eventArgs = new SqlDataSourceCommandEventArgs(command); 
            OnDeleting(eventArgs); 

            // If the operation was cancelled, exit immediately 
            if (eventArgs.Cancel) {
                return 0;
            }
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 
 
            return ExecuteDbCommand(command, DataSourceOperation.Delete);
        } 


        /// 
        /// Inserts a new row with data from a name/value collection. 
        /// 
        protected override int ExecuteInsert(IDictionary values) { 
            if (!CanInsert) { 
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_InsertNotSupported, _owner.ID));
            } 

            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString);

            if (connection == null) { 
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID));
            } 
 
            // Create command and add parameters
            DbCommand command = _owner.CreateCommand(InsertCommand, connection); 
            InitializeParameters(command, InsertParameters, null);
            AddParameters(command, InsertParameters, values, null, null);
            command.CommandType = GetCommandType(InsertCommandType);
 
            // Raise event to allow customization and cancellation
            SqlDataSourceCommandEventArgs eventArgs = new SqlDataSourceCommandEventArgs(command); 
            OnInserting(eventArgs); 

            // If the operation was cancelled, exit immediately 
            if (eventArgs.Cancel) {
                return 0;
            }
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 
 
            return ExecuteDbCommand(command, DataSourceOperation.Insert);
        } 


        /// 
        /// Returns all the rows of the datasource. 
        /// Parameters are taken from the SqlDataSource.Parameters property collection.
        /// If DataSourceMode is set to DataSet then a DataView is returned. 
        /// If DataSourceMode is set to DataReader then a DataReader is returned, and it must be closed when done. 
        /// 
        protected internal override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments) { 
            if (SelectCommand.Length == 0) {
                return null;
            }
 
            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString);
 
            if (connection == null) { 
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID));
            } 

            DataSourceCache cache = _owner.Cache;
            bool cacheEnabled = (cache != null) && (cache.Enabled);
            //int startRowIndex = arguments.StartRowIndex; 
            //int maximumRows = arguments.MaximumRows;
            string sortExpression = arguments.SortExpression; 
 
            if (CanPage) {
                arguments.AddSupportedCapabilities(DataSourceCapabilities.Page); 
            }

            if (CanSort) {
                arguments.AddSupportedCapabilities(DataSourceCapabilities.Sort); 
            }
 
            if (CanRetrieveTotalRowCount) { 
                arguments.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
            } 


            // If caching is enabled, load DataSet from cache
            if (cacheEnabled) { 
                if (_owner.DataSourceMode != SqlDataSourceMode.DataSet) {
                    throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_CacheNotSupported, _owner.ID)); 
                } 

                arguments.RaiseUnsupportedCapabilitiesError(this); 

                DataSet dataSet = _owner.LoadDataFromCache(0, -1) as DataSet;

                if (dataSet != null) { 
                    /*if (arguments.RetrieveTotalRowCount) {
                        int cachedTotalRowCount = _owner.LoadTotalRowCountFromCache(); 
                        if (cachedTotalRowCount >= 0) { 
                            arguments.TotalRowCount = cachedTotalRowCount;
                        } 
                        else {
                            // query for row count and then save it in cache
                            cachedTotalRowCount = QueryTotalRowCount(connection, arguments);
                            arguments.TotalRowCount = cachedTotalRowCount; 
                            _owner.SaveTotalRowCountToCache(cachedTotalRowCount);
                        } 
                    }*/ 
                    IOrderedDictionary parameterValues = FilterParameters.GetValues(_context, _owner);
                    if (FilterExpression.Length > 0) { 
                        SqlDataSourceFilteringEventArgs filterArgs = new SqlDataSourceFilteringEventArgs(parameterValues);
                        OnFiltering(filterArgs);
                        if (filterArgs.Cancel) {
                            return null; 
                        }
                    } 
                    return FilteredDataSetHelper.CreateFilteredDataView(dataSet.Tables[0], sortExpression, FilterExpression, parameterValues); 
                }
            } 

            // Create command and add parameters
            DbCommand command = _owner.CreateCommand(SelectCommand, connection);
            InitializeParameters(command, SelectParameters, null); 
            command.CommandType = GetCommandType(SelectCommandType);
 
            // Raise event to allow customization and cancellation 
            SqlDataSourceSelectingEventArgs selectingEventArgs = new SqlDataSourceSelectingEventArgs(command, arguments);
            OnSelecting(selectingEventArgs); 

            // If the operation was cancelled, exit immediately
            if (selectingEventArgs.Cancel) {
                return null; 
            }
 
            // Add the sort parameter to allow for custom stored procedure sorting, if necessary 
            string sortParameterName = SortParameterName;
            if (sortParameterName.Length > 0) { 
                if (command.CommandType != CommandType.StoredProcedure) {
                    throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_SortParameterRequiresStoredProcedure, _owner.ID));
                }
                command.Parameters.Add(_owner.CreateParameter(ParameterPrefix + sortParameterName, sortExpression)); 

                // We reset the sort expression here so that we pretend as 
                // though we're not really sorting (since the developer is 
                // worrying about it instead of us).
                arguments.SortExpression = String.Empty; 
            }

            arguments.RaiseUnsupportedCapabilitiesError(this);
 
            // reset these values, since they might have changed in the OnSelecting event
            sortExpression = arguments.SortExpression; 
            //startRowIndex = arguments.StartRowIndex; 
            //maximumRows = arguments.MaximumRows;
 
            // Perform null check if user wants to cancel on any null parameter value
            if (CancelSelectOnNullParameter) {
                int paramCount = command.Parameters.Count;
                for (int i = 0; i < paramCount; i++) { 
                    DbParameter parameter = command.Parameters[i];
                    if ((parameter != null) && 
                        (parameter.Value == null) && 
                        ((parameter.Direction == ParameterDirection.Input) || (parameter.Direction == ParameterDirection.InputOutput))) {
                        return null; 
                    }
                }
            }
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 
 
            /*if (arguments.RetrieveTotalRowCount && SelectCountCommand.Length > 0) {
                int cachedTotalRowCount = -1; 
                if (cacheEnabled) {
                    cachedTotalRowCount = _owner.LoadTotalRowCountFromCache();
                    if (cachedTotalRowCount >= 0) {
                        arguments.TotalRowCount = cachedTotalRowCount; 
                    }
                } 
                if (cachedTotalRowCount < 0) { 
                    cachedTotalRowCount = QueryTotalRowCount(connection, arguments);
                    arguments.TotalRowCount = cachedTotalRowCount; 
                    if (cacheEnabled) {
                        _owner.SaveTotalRowCountToCache(cachedTotalRowCount);
                    }
                } 
            }*/
 
            IEnumerable selectResult = null; 

            switch (_owner.DataSourceMode) { 
                case SqlDataSourceMode.DataSet:
                {
                    SqlCacheDependency cacheDependency = null;
                    if (cacheEnabled && cache is SqlDataSourceCache) { 
                        SqlDataSourceCache sqlCache = (SqlDataSourceCache)cache;
                        if (String.Equals(sqlCache.SqlCacheDependency, SqlDataSourceCache.Sql9CacheDependencyDirective, StringComparison.OrdinalIgnoreCase)) { 
                            if (!(command is System.Data.SqlClient.SqlCommand)) { 
                                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CommandNotificationNotSupported, _owner.ID));
                            } 
                            cacheDependency = new SqlCacheDependency((System.Data.SqlClient.SqlCommand)command);
                        }
                    }
 
                    DbDataAdapter adapter = _owner.CreateDataAdapter(command);
 
                    DataSet dataSet = new DataSet(); 
                    int rowsAffected = 0;
 
                    bool eventRaised = false;
                    try {
                        rowsAffected = adapter.Fill(dataSet, Name);
 
                        // Raise the Selected event
                        eventRaised = true; 
                        SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, null); 
                        OnSelected(selectedEventArgs);
                    } 
                    catch (Exception ex) {
                        if (!eventRaised) {
                            // Raise the Selected event
                            SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, rowsAffected, ex); 
                            OnSelected(selectedEventArgs);
                            if (!selectedEventArgs.ExceptionHandled) { 
                                throw; 
                            }
                        } 
                        else {
                            bool isCustomException;
                            ex = BuildCustomException(ex, DataSourceOperation.Select, command, out isCustomException);
                            if (isCustomException) { 
                                throw ex;
                            } 
                            else { 
                                throw;
                            } 
                        }
                    }
                    finally {
                        if (connection.State == ConnectionState.Open) { 
                            connection.Close();
                        } 
                    } 

                    // If caching is enabled, save DataSet to cache 
                    DataTable dataTable = (dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null);
                    if (cacheEnabled && dataTable != null) {
                        _owner.SaveDataToCache(0, -1, dataSet, cacheDependency);
                    } 

                    if (dataTable != null) { 
                        IOrderedDictionary parameterValues = FilterParameters.GetValues(_context, _owner); 
                        if (FilterExpression.Length > 0) {
                            SqlDataSourceFilteringEventArgs filterArgs = new SqlDataSourceFilteringEventArgs(parameterValues); 
                            OnFiltering(filterArgs);
                            if (filterArgs.Cancel) {
                                return null;
                            } 
                        }
                        selectResult = FilteredDataSetHelper.CreateFilteredDataView(dataTable, sortExpression, FilterExpression, parameterValues); 
                    } 
                    break;
                } 

                case SqlDataSourceMode.DataReader:
                {
                    if (FilterExpression.Length > 0) { 
                        throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_FilterNotSupported, _owner.ID));
                    } 
 
                    if (sortExpression.Length > 0) {
                        throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_SortNotSupported, _owner.ID)); 
                    }

                    bool eventRaised = false;
                    try { 
                        if (connection.State != ConnectionState.Open) {
                            connection.Open(); 
                        } 

                        selectResult = command.ExecuteReader(CommandBehavior.CloseConnection); 

                        // Raise the Selected event
                        eventRaised = true;
                        SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, 0, null); 
                        OnSelected(selectedEventArgs);
                    } 
                    catch (Exception ex) { 
                        if (!eventRaised) {
                            // Raise the Selected event 
                            SqlDataSourceStatusEventArgs selectedEventArgs = new SqlDataSourceStatusEventArgs(command, 0, ex);
                            OnSelected(selectedEventArgs);
                            if (!selectedEventArgs.ExceptionHandled) {
                                throw; 
                            }
                        } 
                        else { 
                            bool isCustomException;
                            ex = BuildCustomException(ex, DataSourceOperation.Select, command, out isCustomException); 
                            if (isCustomException) {
                                throw ex;
                            }
                            else { 
                                throw;
                            } 
                        } 
                    }
                    break; 
                }
            }
            return selectResult;
        } 

 
        ///  
        /// Updates rows matching the parameter collection and setting new values from the name/value values collection.
        ///  
        protected override int ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) {
            if (!CanUpdate) {
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_UpdateNotSupported, _owner.ID));
            } 

            DbConnection connection = _owner.CreateConnection(_owner.ConnectionString); 
 
            if (connection == null) {
                throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_CouldNotCreateConnection, _owner.ID)); 
            }

            // Create command and add parameters
            string oldValuesParameterFormatString = OldValuesParameterFormatString; 
            DbCommand command = _owner.CreateCommand(UpdateCommand, connection);
            InitializeParameters(command, UpdateParameters, keys); 
            AddParameters(command, UpdateParameters, values, null, null); 
            AddParameters(command, UpdateParameters, keys, null, oldValuesParameterFormatString);
            if (ConflictDetection == ConflictOptions.CompareAllValues) { 
                if (oldValues == null || oldValues.Count == 0) {
                    throw new InvalidOperationException(SR.GetString(SR.SqlDataSourceView_Pessimistic, SR.GetString(SR.DataSourceView_update), _owner.ID, "oldValues"));
                }
                AddParameters(command, UpdateParameters, oldValues, null, oldValuesParameterFormatString); 
            }
            command.CommandType = GetCommandType(UpdateCommandType); 
 
            // Raise event to allow customization and cancellation
            SqlDataSourceCommandEventArgs eventArgs = new SqlDataSourceCommandEventArgs(command); 
            OnUpdating(eventArgs);

            // If the operation was cancelled, exit immediately
            if (eventArgs.Cancel) { 
                return 0;
            } 
 
            // Replace null values in parameters with DBNull.Value
            ReplaceNullValues(command); 

            return ExecuteDbCommand(command, DataSourceOperation.Update);
        }
 
        /// 
        /// Converts a SqlDataSourceCommandType to a System.Data.CommandType. 
        ///  
        private static CommandType GetCommandType(SqlDataSourceCommandType commandType) {
            if (commandType == SqlDataSourceCommandType.Text) { 
                return CommandType.Text;
            }
            return CommandType.StoredProcedure;
        } 

        ///  
        /// Initializes a DbCommand with parameters from a ParameterCollection. 
        /// The exclusion list contains parameter names that should not be added
        /// to the command's parameter collection. 
        /// 
        private void InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) {
            Debug.Assert(command != null);
            Debug.Assert(parameters != null); 

            string parameterPrefix = ParameterPrefix; 
 
            IDictionary caseInsensitiveExclusionList = null;
            if (exclusionList != null) { 
                caseInsensitiveExclusionList = new ListDictionary(StringComparer.OrdinalIgnoreCase);
                foreach (DictionaryEntry de in exclusionList) {
                    caseInsensitiveExclusionList.Add(de.Key, de.Value);
                } 
            }
 
            IOrderedDictionary values = parameters.GetValues(_context, _owner); 
            for (int i = 0; i < parameters.Count; i++) {
                Parameter parameter = parameters[i]; 
                if ((caseInsensitiveExclusionList == null) || (!caseInsensitiveExclusionList.Contains(parameter.Name))) {
                    DbParameter dbParameter = _owner.CreateParameter(parameterPrefix + parameter.Name, values[i]);
                    dbParameter.Direction = parameter.Direction;
                    dbParameter.Size = parameter.Size; 
                    if (parameter.DbType != DbType.Object || (parameter.Type != TypeCode.Empty && parameter.Type != TypeCode.DBNull)) {
                        SqlParameter sqlParameter = dbParameter as SqlParameter; 
                        if (sqlParameter == null) { 
                            dbParameter.DbType = parameter.GetDatabaseType();
                        } 
                        else {
                            // In Whidbey, the DbType Date and Time members mapped to SqlDbType.DateTime since there
                            // were no SqlDbType equivalents. SqlDbType has since been modified to include the new
                            // Katmai types, including Date and Time. For backwards compatability SqlParameter's DbType 
                            // setter doesn't support Date and Time, so the SqlDbType property should be used instead.
                            // Other new SqlServer 2008 types (DateTime2, DateTimeOffset) can be set using DbType. 
                            DbType dbType = parameter.GetDatabaseType(); 
                            switch (dbType) {
                                case DbType.Time: 
                                    sqlParameter.SqlDbType = SqlDbType.Time;
                                    break;
                                case DbType.Date:
                                    sqlParameter.SqlDbType = SqlDbType.Date; 
                                    break;
                                default: 
                                    dbParameter.DbType = parameter.GetDatabaseType(); 
                                    break;
                            } 
                        }
                    }
                    command.Parameters.Add(dbParameter);
                } 
            }
        } 
 
        public int Insert(IDictionary values) {
            return ExecuteInsert(values); 
        }


        ///  
        /// Loads view state.
        ///  
        protected virtual void LoadViewState(object savedState) { 
            if (savedState == null)
                return; 

            Pair myState = (Pair)savedState;

            if (myState.First != null) 
                ((IStateManager)SelectParameters).LoadViewState(myState.First);
 
            if (myState.Second != null) 
                ((IStateManager)FilterParameters).LoadViewState(myState.Second);
        } 


        /// 
        /// Raises the Deleted event. 
        /// 
        protected virtual void OnDeleted(SqlDataSourceStatusEventArgs e) { 
            SqlDataSourceStatusEventHandler handler = Events[EventDeleted] as SqlDataSourceStatusEventHandler; 
            if (handler != null) {
                handler(this, e); 
            }
        }

 
        /// 
        /// Raises the Deleting event. 
        ///  
        protected virtual void OnDeleting(SqlDataSourceCommandEventArgs e) {
            SqlDataSourceCommandEventHandler handler = Events[EventDeleting] as SqlDataSourceCommandEventHandler; 
            if (handler != null) {
                handler(this, e);
            }
        } 

        protected virtual void OnFiltering(SqlDataSourceFilteringEventArgs e) { 
            SqlDataSourceFilteringEventHandler handler = Events[EventFiltering] as SqlDataSourceFilteringEventHandler; 
            if (handler != null) {
                handler(this, e); 
            }
        }

 
        /// 
        /// Raises the Inserted event. 
        ///  
        protected virtual void OnInserted(SqlDataSourceStatusEventArgs e) {
            SqlDataSourceStatusEventHandler handler = Events[EventInserted] as SqlDataSourceStatusEventHandler; 
            if (handler != null) {
                handler(this, e);
            }
        } 

 
        ///  
        /// Raises the Inserting event.
        ///  
        protected virtual void OnInserting(SqlDataSourceCommandEventArgs e) {
            SqlDataSourceCommandEventHandler handler = Events[EventInserting] as SqlDataSourceCommandEventHandler;
            if (handler != null) {
                handler(this, e); 
            }
        } 
 

        ///  
        /// Raises the Selected event.
        /// 
        protected virtual void OnSelected(SqlDataSourceStatusEventArgs e) {
            SqlDataSourceStatusEventHandler handler = Events[EventSelected] as SqlDataSourceStatusEventHandler; 
            if (handler != null) {
                handler(this, e); 
            } 
        }
 

        /// 
        /// Raises the Selecting event.
        ///  
        protected virtual void OnSelecting(SqlDataSourceSelectingEventArgs e) {
            SqlDataSourceSelectingEventHandler handler = Events[EventSelecting] as SqlDataSourceSelectingEventHandler; 
            if (handler != null) { 
                handler(this, e);
            } 
        }


        ///  
        /// Raises the Updated event.
        ///  
        protected virtual void OnUpdated(SqlDataSourceStatusEventArgs e) { 
            SqlDataSourceStatusEventHandler handler = Events[EventUpdated] as SqlDataSourceStatusEventHandler;
            if (handler != null) { 
                handler(this, e);
            }
        }
 

        ///  
        /// Raises the Updating event. 
        /// 
        protected virtual void OnUpdating(SqlDataSourceCommandEventArgs e) { 
            SqlDataSourceCommandEventHandler handler = Events[EventUpdating] as SqlDataSourceCommandEventHandler;
            if (handler != null) {
                handler(this, e);
            } 
        }
 
 
        /// 
        /// Executes the SelectCountCommand to retrieve the total row count. 
        /// 
        /*protected virtual int QueryTotalRowCount(DbConnection connection, DataSourceSelectArguments arguments) {
            int totalRowCount = 0;
            bool eventRaised = false; 

            if (SelectCountCommand.Length > 0) { 
                // Create command and add parameters 
                DbCommand command = _owner.CreateCommand(SelectCountCommand, connection);
                InitializeParameters(command, SelectParameters); 
                command.CommandType = GetCommandType(SelectCountCommand, SelectCompareString);

                // Raise event to allow customization and cancellation
                SqlDataSourceSelectingEventArgs selectCountingEventArgs = new SqlDataSourceSelectingEventArgs(command, arguments, true); 
                OnSelecting(selectCountingEventArgs);
 
                // If the operation was cancelled, exit immediately 
                if (selectCountingEventArgs.Cancel) {
                    return totalRowCount; 
                }

                // the arguments may have been changed
                arguments.RaiseUnsupportedCapabilitiesError(this); 

                // 
 

 



 

 
 

 



 

 
 

 



 

 
 

 



 

 
 

*/ 


        protected internal override void RaiseUnsupportedCapabilityError(DataSourceCapabilities capability) {
            if (!CanPage && ((capability & DataSourceCapabilities.Page) != 0)) { 
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_NoPaging, _owner.ID));
            } 
 
            if (!CanSort && ((capability & DataSourceCapabilities.Sort) != 0)) {
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_NoSorting, _owner.ID)); 
            }

            if (!CanRetrieveTotalRowCount && ((capability & DataSourceCapabilities.RetrieveTotalRowCount) != 0)) {
                throw new NotSupportedException(SR.GetString(SR.SqlDataSourceView_NoRowCount, _owner.ID)); 
            }
            base.RaiseUnsupportedCapabilityError(capability); 
        } 

        ///  
        /// Replace null values in parameters with DBNull.Value.
        /// 
        private void ReplaceNullValues(DbCommand command) {
            int paramCount = command.Parameters.Count; 
            foreach (DbParameter parameter in command.Parameters) {
                if (parameter.Value == null) { 
                    parameter.Value = DBNull.Value; 
                }
            } 
        }

        /// 
        /// Saves view state. 
        /// 
        protected virtual object SaveViewState() { 
            Pair myState = new Pair(); 

            myState.First = (_selectParameters != null) ? ((IStateManager)_selectParameters).SaveViewState() : null; 
            myState.Second = (_filterParameters != null) ? ((IStateManager)_filterParameters).SaveViewState() : null;

            if ((myState.First == null) &&
                (myState.Second == null)) { 
                return null;
            } 
 
            return myState;
        } 

        public IEnumerable Select(DataSourceSelectArguments arguments) {
            return ExecuteSelect(arguments);
        } 

        ///  
        /// Event handler for SelectParametersChanged event. 
        /// 
        private void SelectParametersChangedEventHandler(object o, EventArgs e) { 
            OnDataSourceViewChanged(EventArgs.Empty);
        }

 
        /// 
        /// Starts tracking view state. 
        ///  
        protected virtual void TrackViewState() {
            _tracking = true; 

            if (_selectParameters != null) {
                ((IStateManager)_selectParameters).TrackViewState();
            } 
            if (_filterParameters != null) {
                ((IStateManager)_filterParameters).TrackViewState(); 
            } 
        }
 
        public int Update(IDictionary keys, IDictionary values, IDictionary oldValues) {
            return ExecuteUpdate(keys, values, oldValues);
        }
 

        #region IStateManager implementation 
        bool IStateManager.IsTrackingViewState { 
            get {
                return IsTrackingViewState; 
            }
        }

        void IStateManager.LoadViewState(object savedState) { 
            LoadViewState(savedState);
        } 
 
        object IStateManager.SaveViewState() {
            return SaveViewState(); 
        }

        void IStateManager.TrackViewState() {
            TrackViewState(); 
        }
        #endregion 
    } 
}
 

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.

                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK