|
//------------------------------------------------------------------------------
// <copyright file="SqlDataSourceView.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation. All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
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;
/// <devdoc>
/// Represents a single view of a SqlDataSource.
/// </devdoc>
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;
/// <devdoc>
/// Creates a new instance of SqlDataSourceView.
/// </devdoc>
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);
}
}
}
/// <devdoc>
/// Indicates that the view can delete rows.
/// </devdoc>
public override bool CanDelete {
get {
return (DeleteCommand.Length != 0);
}
}
/// <devdoc>
/// Indicates that the view can add new rows.
/// </devdoc>
public override bool CanInsert {
get {
return (InsertCommand.Length != 0);
}
}
/// <devdoc>
/// Indicates that the view can page the datasource on the server.
/// </devdoc>
public override bool CanPage {
get {
return false;
}
}
/// <devdoc>
/// Indicates that the view can return the total number of rows returned by the query.
/// </devdoc>
public override bool CanRetrieveTotalRowCount {
get {
return false;
}
}
/// <devdoc>
/// Indicates that the view can sort rows.
/// </devdoc>
public override bool CanSort {
get {
return (_owner.DataSourceMode == SqlDataSourceMode.DataSet) || (SortParameterName.Length > 0);
}
}
/// <devdoc>
/// Indicates that the view can update rows.
/// </devdoc>
public override bool CanUpdate {
get {
return (UpdateCommand.Length != 0);
}
}
/// <devdoc>
/// Whether commands pass old values in the parameter collection.
/// </devdoc>
public ConflictOptions ConflictDetection {
get {
return _conflictDetection;
}
set {
if ((value < ConflictOptions.OverwriteChanges) || (value > ConflictOptions.CompareAllValues)) {
throw new ArgumentOutOfRangeException("value");
}
_conflictDetection = value;
OnDataSourceViewChanged(EventArgs.Empty);
}
}
/// <devdoc>
/// The command to execute when Delete() is called on the SqlDataSourceView.
/// </devdoc>
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;
}
}
/// <devdoc>
/// Collection of parameters used in Delete().
/// </devdoc>
[
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;
}
}
/// <devdoc>
/// The filter to apply when Select() is called on the SqlDataSourceView.
/// </devdoc>
public string FilterExpression {
get {
if (_filterExpression == null) {
return String.Empty;
}
return _filterExpression;
}
set {
if (FilterExpression != value) {
_filterExpression = value;
OnDataSourceViewChanged(EventArgs.Empty);
}
}
}
/// <devdoc>
/// Collection of parameters used in the FilterExpression property.
/// </devdoc>
[
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;
}
}
/// <devdoc>
/// The command to execute when Insert() is called on the SqlDataSourceView.
/// </devdoc>
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;
}
}
/// <devdoc>
/// Collection of values used in Insert().
/// </devdoc>
[
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;
}
}
/// <devdoc>
/// Returns whether this object is tracking view state.
/// </devdoc>
protected bool IsTrackingViewState {
get {
return _tracking;
}
}
/// <devdoc>
/// The format string applied to the names of the old values parameters
/// </devdoc>
[
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);
}
}
/// <devdoc>
/// Indicates the prefix for parameters.
/// </devdoc>
protected virtual string ParameterPrefix {
get {
if (String.IsNullOrEmpty(_owner.ProviderName) ||
String.Equals(_owner.ProviderName, "System.Data.SqlClient", StringComparison.OrdinalIgnoreCase)) {
return "@";
}
else {
return String.Empty;
}
}
}
/// <devdoc>
/// The command to execute when Select() is called on the SqlDataSourceView.
/// </devdoc>
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;
}
}
/// <devdoc>
/// The command to execute when Select is called on the SqlDataSourceView and the total rows is requested.
/// </devdoc>
/*public string SelectCountCommand {
get {
if (_selectCountCommand == null) {
return String.Empty;
}
return _selectCountCommand;
}
set {
if (SelectCountCommand != value) {
_selectCountCommand = value;
OnDataSourceViewChanged(EventArgs.Empty);
}
}
}*/
/// <devdoc>
/// Collection of parameters used in Select().
/// </devdoc>
public ParameterCollection SelectParameters {
get {
if (_selectParameters == null) {
_selectParameters = new ParameterCollection();
_selectParameters.ParametersChanged += new EventHandler(SelectParametersChangedEventHandler);
if (_tracking) {
((IStateManager)_selectParameters).TrackViewState();
}
}
return _selectParameters;
}
}
/// <devdoc>
/// 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.
/// </devdoc>
public string SortParameterName {
get {
if (_sortParameterName == null) {
return String.Empty;
}
return _sortParameterName;
}
set {
if (SortParameterName != value) {
_sortParameterName = value;
OnDataSourceViewChanged(EventArgs.Empty);
}
}
}
/// <devdoc>
/// The command to execute when Update() is called on the SqlDataSourceView.
/// </devdoc>
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;
}
}
/// <devdoc>
/// Collection of parameters used in Update().
/// </devdoc>
[
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;
}
}
/// <devdoc>
/// This event is raised after the Delete operation has completed.
/// Handle this event if you need to examine the values of output parameters.
/// </devdoc>
public event SqlDataSourceStatusEventHandler Deleted {
add {
Events.AddHandler(EventDeleted, value);
}
remove {
Events.RemoveHandler(EventDeleted, value);
}
}
/// <devdoc>
/// 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.
/// </devdoc>
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);
}
}
/// <devdoc>
/// This event is raised after the Insert operation has completed.
/// Handle this event if you need to examine the values of output parameters.
/// </devdoc>
public event SqlDataSourceStatusEventHandler Inserted {
add {
Events.AddHandler(EventInserted, value);
}
remove {
Events.RemoveHandler(EventInserted, value);
}
}
/// <devdoc>
/// 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.
/// </devdoc>
public event SqlDataSourceCommandEventHandler Inserting {
add {
Events.AddHandler(EventInserting, value);
}
remove {
Events.RemoveHandler(EventInserting, value);
}
}
/// <devdoc>
/// This event is raised after the Select operation has completed.
/// Handle this event if you need to examine the values of output parameters.
/// </devdoc>
public event SqlDataSourceStatusEventHandler Selected {
add {
Events.AddHandler(EventSelected, value);
}
remove {
Events.RemoveHandler(EventSelected, value);
}
}
/// <devdoc>
/// 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.
/// </devdoc>
public event SqlDataSourceSelectingEventHandler Selecting {
add {
Events.AddHandler(EventSelecting, value);
}
remove {
Events.RemoveHandler(EventSelecting, value);
}
}
/// <devdoc>
/// This event is raised after the Update operation has completed.
/// Handle this event if you need to examine the values of output parameters.
/// </devdoc>
public event SqlDataSourceStatusEventHandler Updated {
add {
Events.AddHandler(EventUpdated, value);
}
remove {
Events.RemoveHandler(EventUpdated, value);
}
}
/// <devdoc>
/// 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.
/// </devdoc>
public event SqlDataSourceCommandEventHandler Updating {
add {
Events.AddHandler(EventUpdating, value);
}
remove {
Events.RemoveHandler(EventUpdating, value);
}
}
/// <devdoc>
/// 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.
/// </devdoc>
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);
}
}
}
}
/// <devdoc>
/// 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.
/// </devdoc>
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);
}
/// <devdoc>
/// Executes a DbCommand and returns the number of rows affected.
/// </devdoc>
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;
}
/// <devdoc>
/// Deletes rows from the data source with given parameters.
/// </devdoc>
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);
}
/// <devdoc>
/// Inserts a new row with data from a name/value collection.
/// </devdoc>
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);
}
/// <devdoc>
/// 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.
/// </devdoc>
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;
}
/// <devdoc>
/// Updates rows matching the parameter collection and setting new values from the name/value values collection.
/// </devdoc>
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);
}
/// <devdoc>
/// Converts a SqlDataSourceCommandType to a System.Data.CommandType.
/// </devdoc>
private static CommandType GetCommandType(SqlDataSourceCommandType commandType) {
if (commandType == SqlDataSourceCommandType.Text) {
return CommandType.Text;
}
return CommandType.StoredProcedure;
}
/// <devdoc>
/// 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.
/// </devdoc>
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);
}
/// <devdoc>
/// Loads view state.
/// </devdoc>
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);
}
/// <devdoc>
/// Raises the Deleted event.
/// </devdoc>
protected virtual void OnDeleted(SqlDataSourceStatusEventArgs e) {
SqlDataSourceStatusEventHandler handler = Events[EventDeleted] as SqlDataSourceStatusEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Raises the Deleting event.
/// </devdoc>
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);
}
}
/// <devdoc>
/// Raises the Inserted event.
/// </devdoc>
protected virtual void OnInserted(SqlDataSourceStatusEventArgs e) {
SqlDataSourceStatusEventHandler handler = Events[EventInserted] as SqlDataSourceStatusEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Raises the Inserting event.
/// </devdoc>
protected virtual void OnInserting(SqlDataSourceCommandEventArgs e) {
SqlDataSourceCommandEventHandler handler = Events[EventInserting] as SqlDataSourceCommandEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Raises the Selected event.
/// </devdoc>
protected virtual void OnSelected(SqlDataSourceStatusEventArgs e) {
SqlDataSourceStatusEventHandler handler = Events[EventSelected] as SqlDataSourceStatusEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Raises the Selecting event.
/// </devdoc>
protected virtual void OnSelecting(SqlDataSourceSelectingEventArgs e) {
SqlDataSourceSelectingEventHandler handler = Events[EventSelecting] as SqlDataSourceSelectingEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Raises the Updated event.
/// </devdoc>
protected virtual void OnUpdated(SqlDataSourceStatusEventArgs e) {
SqlDataSourceStatusEventHandler handler = Events[EventUpdated] as SqlDataSourceStatusEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Raises the Updating event.
/// </devdoc>
protected virtual void OnUpdating(SqlDataSourceCommandEventArgs e) {
SqlDataSourceCommandEventHandler handler = Events[EventUpdating] as SqlDataSourceCommandEventHandler;
if (handler != null) {
handler(this, e);
}
}
/// <devdoc>
/// Executes the SelectCountCommand to retrieve the total row count.
/// </devdoc>
/*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);
}
/// <devdoc>
/// Replace null values in parameters with DBNull.Value.
/// </devdoc>
private void ReplaceNullValues(DbCommand command) {
int paramCount = command.Parameters.Count;
foreach (DbParameter parameter in command.Parameters) {
if (parameter.Value == null) {
parameter.Value = DBNull.Value;
}
}
}
/// <devdoc>
/// Saves view state.
/// </devdoc>
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);
}
/// <devdoc>
/// Event handler for SelectParametersChanged event.
/// </devdoc>
private void SelectParametersChangedEventHandler(object o, EventArgs e) {
OnDataSourceViewChanged(EventArgs.Empty);
}
/// <devdoc>
/// Starts tracking view state.
/// </devdoc>
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
}
}
|