using System;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Diagnostics;
using System.Runtime;
using System.Runtime.Serialization;
using Tdo;
using Tdo.Common;
using Tdo.Common.Programmability;
using Tdo.Common.Entities;
using Tdo.Common.Entities.Tables;
using Tdo.Common.Entities.Views;
using Tdo.Common.Helper;
using Tdo.Common.TdoSqlExpressionDom;
using Tdo.Common.TdoTypes;
namespace Tdo.Common.Helper{
/// <summary>
/// Base class for TDOHelperClass
/// </summary>
[Serializable()]
[CLSCompliant(true)]
[DesignTimeVisible(true)]
[Browsable(true)]
public class TdoHelperBase : IComponent, ITdoHelper
{
#region Fields
private ConnectionState pConnectionStateBackup;
private bool pAutomaticOpenCloseConnectionBackup;
private bool pAutomaticTransactionBackup;
/// <summary>
/// Gets or Sets if TdoHelper must Open and Close Connection automatically.
/// </summary>
protected bool pAutomaticOpenCloseConnection;
/// <summary>
/// Gets or Sets a bool value indicating if TdoHelper must execute Command automatically in Transaction.
/// </summary>
protected bool pAutomaticTransaction;
/// <summary>
/// SqlCommandHashCollection with async SqlCommands (Begin/End Methods)
/// </summary>
[NonSerialized()]
private Hashtable pSqlCommandAsyncStore;
/// <summary>
/// Get or Set SqlCommand time out (default is 60 seconds)
/// </summary>
protected int pCommandTimeOut;
/// <summary>
/// Get Sql Server version
/// </summary>
private string pServerVersion;
/// <summary>
/// ISite Property for IComponent
/// </summary>
[NonSerialized()]
private ISite piSite;
/// <summary>
/// Disposed Component Event
/// </summary>
public event EventHandler Disposed;
/// <summary>
/// Current SqlConnection
/// </summary>
[NonSerialized]
private SqlConnection pConnection;
/// <summary>
/// Current SqlTransaction
/// </summary>
[NonSerialized]
private SqlTransaction pTransaction;
/// <summary>
/// Sql Connection string
/// </summary>
private string pConnectionString;
#endregion Fields
#region Constructors
/// <summary>
/// TdoHelperBase default constructor
/// </summary>
public TdoHelperBase()
{
this.pCommandTimeOut = 60;
this.pSqlCommandAsyncStore = Hashtable.Synchronized(new Hashtable());
this.pAutomaticOpenCloseConnection = this.pAutomaticOpenCloseConnectionBackup= true;
this.pAutomaticTransaction = this.pAutomaticTransactionBackup= false;
this.pConnectionStateBackup = ConnectionState.Closed;
}
/// <summary>
/// TdoHelperBase Constructor
/// </summary>
/// <param name="connectionString">Sql Connection string</param>
public TdoHelperBase(string connectionString) : this()
{
this.ConnectionString = connectionString;
}
#endregion Constructors
#region Properties
/// <summary>
/// Gets or Sets if TdoHelper must Open and Close Connection automatically. Default is true.
/// </summary>
[Browsable(false)]
public bool AutomaticOpenCloseConnection
{
get
{
return this.pAutomaticOpenCloseConnection;
}
set
{
this.pAutomaticOpenCloseConnection = value;
}
}
/// <summary>
/// Gets or Sets a bool value indicating if TdoHelper must execute Command automatically in Transaction. Default is false.
/// </summary>
[Browsable(false)]
public bool AutomaticTransaction
{
get
{
return this.pAutomaticTransaction;
}
set
{
this.pAutomaticTransaction = value;
}
}
/// <summary>
/// Get Sql Server version
/// </summary>
[Browsable(false)]
public string ServerVersion
{
get
{
return this.pServerVersion;
}
}
///// <summary>
///// IComponent ISite Property
///// </summary>
//[XmlIgnore]
//public new ISite Site
//{
// get
// {
// return this.piSite;
// }
// set
// {
// this.piSite = value;
// }
//}
/// <summary>
/// Sql Connection string
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public string ConnectionString
{
get
{
return this.pConnectionString;
}
set
{
this.pConnectionString = value;
this.pConnection = new SqlConnection(this.pConnectionString);
this.pConnection.StateChange += new StateChangeEventHandler(this.internalConnectionStateChangeEventHandler);
}
}
/// <summary>
/// SqlCommand HashTable with async SqlCommands (Begin/End Methods)
/// </summary>
[XmlIgnore()]
Hashtable ITdoHelper.SqlCommandAsyncStore
{
get
{
return this.pSqlCommandAsyncStore;
}
}
#endregion Properties
#region Methods
/// <summary>
/// <code>Open DataBase Connection</code>
/// </summary>
public virtual void OpenConnection()
{
if (this.pConnection.State != ConnectionState.Open)
{
this.pAutomaticOpenCloseConnectionBackup = this.AutomaticOpenCloseConnection;
this.AutomaticOpenCloseConnection = false;
this.pConnection.Open();
}
this.pConnectionStateBackup = this.pConnection.State;
if (String.IsNullOrEmpty(this.pServerVersion))
this.pServerVersion = this.pConnection.ServerVersion;
//new SqlCommand("SET ANSI_NULLS OFF",this.pConnection).ExecuteNonQuery(); //Otherwise it doesn't work where Campo=@p1 con @p1=NULL
}
/// <summary>
/// Close DataBase Connection
/// </summary>
public void CloseConnection()
{
((ITdoHelper)this).UnPrepareAutomaticTransaction();
if (this.pConnection.State != ConnectionState.Closed)
{
this.pConnection.Close();
}
this.pConnectionStateBackup = this.pConnection.State;
this.AutomaticOpenCloseConnection = this.pAutomaticOpenCloseConnectionBackup;
}
/// <summary>
/// Begin a commandText-transaction
/// </summary>
/// <param name="isolationLevel">Transaction Isolation Level</param>
/// <param name="transactionName">Transaction Name</param>
public void BeginTransaction(System.Data.IsolationLevel isolationLevel, string transactionName)
{
this.pAutomaticTransactionBackup = this.AutomaticTransaction;
this.pTransaction = this.pConnection.BeginTransaction(isolationLevel, transactionName);
}
/// <summary>
/// Creates a savepoint in the transaction that can be used to roll back a portion of the transaction, and specifies the savepoint name.
/// </summary>
/// <param name="savePointName">The name of the savepoint. </param>
/// <exception cref="System.InvalidOperationException">The transaction has already been committed or rolled back.-or- The connection is broken.</exception>
/// <exception cref="System.Exception">An error occurred while trying to commit the transaction. </exception>
public void SaveTransaction(string savePointName)
{
this.pTransaction.Save(savePointName);
}
/// <summary>
/// Begin a commandText-transaction
/// </summary>
/// <param name="isolationLevel">Transaction Isolation Level</param>
public void BeginTransaction(System.Data.IsolationLevel isolationLevel)
{
this.pAutomaticTransactionBackup = this.AutomaticTransaction;
this.pTransaction = this.pConnection.BeginTransaction(isolationLevel);
}
/// <summary>
/// Begin a commandText-transaction
/// </summary>
public void BeginTransaction()
{
this.pAutomaticTransactionBackup = this.AutomaticTransaction;
this.pTransaction = this.pConnection.BeginTransaction();
}
/// <summary>
/// Begin a commandText-transaction
/// </summary>
/// <param name="transactionName">Transaction Name</param>
public void BeginTransaction(string transactionName)
{
this.pAutomaticTransactionBackup = this.AutomaticTransaction;
this.pTransaction = this.pConnection.BeginTransaction(transactionName);
}
/// <summary>
/// Commit current transaction
/// </summary>
public void CommitTransaction()
{
try
{
this.pTransaction.Commit();
this.AutomaticTransaction = this.pAutomaticTransactionBackup;
}
finally
{
this.pTransaction = null;
}
}
/// <summary>
/// Commit current transaction
/// </summary>
public virtual void RollBackTransaction()
{
try
{
this.pTransaction.Rollback();
this.AutomaticTransaction = this.pAutomaticTransactionBackup;
}
finally
{
this.pTransaction = null;
}
}
/// <summary>
/// Commit transactionName transaction
/// </summary>
/// <param name="transactionName">Transaction Name</param>
public virtual void RollBackTransaction(string transactionName)
{
try
{
this.pTransaction.Rollback(transactionName);
this.AutomaticTransaction = this.pAutomaticTransactionBackup;
}
finally
{
this.pTransaction = null;
}
}
/// <summary>
/// Reference for current SqlTransaction
/// </summary>
[XmlIgnore]
[Browsable(false)]
public SqlTransaction Transaction
{
get
{
return this.pTransaction;
}
protected set
{
this.pTransaction = value;
}
}
/// <summary>
/// Returns an array of ITdoTable
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public virtual ITdoTable[] TdoTables
{
get
{
return null;
}
}
/// <summary>
/// Returns an array of ITdoTableFunction
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public virtual ITdoTableFunction[] TdoTableFunctions
{
get
{
return null;
}
}
/// <summary>
/// Returns an array of ITdoView
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public virtual ITdoView[] TdoViews
{
get
{
return null;
}
}
/// <summary>
/// Returns an array of ITdoStoredProcedure
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public virtual ITdoStoredProcedure[] TdoStoredProcedures
{
get
{
return null;
}
}
/// <summary>
/// Current SqlConnection
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public SqlConnection Connection
{
get
{
return this.pConnection;
}
protected set
{
this.pConnection = value;
}
}
/// <summary>
/// Get or Set SqlCommand time out (default is 60 seconds)
/// </summary>
[XmlIgnore()]
[Browsable(false)]
public virtual int CommandTimeOut
{
get
{
return this.pCommandTimeOut;
}
set
{
this.pCommandTimeOut = value;
}
}
/// <summary>
/// Executes commandText query and returns a SqlDataReader reference
/// </summary>
/// <param name="commandText">Sql statement to execute</param>
/// <returns>SqlDataReader for reading results</returns>
public virtual SqlDataReader ExecuteReader(string commandText)
{
SqlCommand sqlCommand = this.CreateCommand(commandText);
sqlCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
return sqlCommand.ExecuteReader();
}
/// <summary>
/// Executes commandText query and returns a SqlDataReader reference
/// </summary>
/// <param name="commandText">Sql statement to execute</param>
/// <param name="commandBeahvior">Command Beahvior</param>
/// <returns>SqlDataReader for reading results</returns>
public virtual SqlDataReader ExecuteReader(string commandText, System.Data.CommandBehavior commandBeahvior)
{
SqlCommand sqlCommand = this.CreateCommand(commandText);
sqlCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
return sqlCommand.ExecuteReader(commandBeahvior);
}
/// <summary>
/// Executes commandText query and returns a SqlDataReader reference
/// </summary>
/// <param name="commandText">Sql statement to execute</param>
/// <returns>SqlDataReader for reading results</returns>
public virtual XmlReader ExecuteXmlReader(string commandText)
{
SqlCommand sqlCommand = this.CreateCommand(commandText);
sqlCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
return sqlCommand.ExecuteXmlReader();
}
/// <summary>
/// Creates and returns a System.Data.SqlClient.SqlCommand object associated supplied values.
/// </summary>
/// <param name="commandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
/// <param name="sqlParameters">The parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.</param>
/// <returns></returns>
public virtual SqlCommand CreateCommand(string commandText, params SqlParameter[] sqlParameters)
{
return this.CreateCommand(commandText, CommandType.Text, null, false, sqlParameters);
}
/// <summary>
/// Creates and returns a System.Data.SqlClient.SqlCommand object associated supplied values.
/// </summary>
/// <param name="commandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
/// <param name="commandType">a value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
/// <param name="sqlParameters">The parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.</param>
/// <returns></returns>
public virtual SqlCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
{
return this.CreateCommand(commandText, commandType, null, false, sqlParameters);
}
/// <summary>
/// Creates and returns a System.Data.SqlClient.SqlCommand object associated supplied values.
/// </summary>
/// <param name="commandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
/// <param name="commandType">a value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
/// <param name="sqlNotificationRequest">a value that specifies the System.Data.Sql.SqlNotificationRequest object bound to this command.</param>
/// <param name="notificationAutoEnlist">[To be supplied.]</param>
/// <param name="sqlParameters">The parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.</param>
/// <returns></returns>
public virtual SqlCommand CreateCommand(string commandText, CommandType commandType, System.Data.Sql.SqlNotificationRequest sqlNotificationRequest, bool notificationAutoEnlist, params SqlParameter[] sqlParameters)
{
SqlCommand sqlCommand = new SqlCommand(commandText, this.pConnection, this.pTransaction);
sqlCommand.CommandTimeout = this.pCommandTimeOut;
sqlCommand.CommandType = commandType;
sqlCommand.Notification = sqlNotificationRequest;
sqlCommand.NotificationAutoEnlist = notificationAutoEnlist;
if (sqlParameters != null & sqlParameters.Length > 0)
{
foreach (SqlParameter sqlParameter in sqlParameters)
{
if (
((sqlParameter.Direction == ParameterDirection.Input || sqlParameter.Direction == ParameterDirection.InputOutput) && sqlParameter.Value != null)
||
sqlParameter.Direction == ParameterDirection.Output ||
sqlParameter.Direction == ParameterDirection.ReturnValue)
sqlCommand.Parameters.Add(TdoHelperBase.cloneSqlParameter(sqlParameter));
}
}
return sqlCommand;
}
internal static SqlParameter cloneSqlParameter(SqlParameter p)
{
SqlParameter r = new SqlParameter();
r.ParameterName = p.ParameterName;
r.DbType = r.DbType;
r.Direction = p.Direction;
r.IsNullable = p.IsNullable;
r.Precision = p.Precision;
r.Scale = p.Scale;
r.Size = p.Size;
r.SourceColumn = p.SourceColumn;
r.SourceColumnNullMapping = p.SourceColumnNullMapping;
r.SourceVersion = p.SourceVersion;
r.SqlDbType = p.SqlDbType;
r.SqlValue = p.SqlValue;
r.Value = p.Value;
return r;
}
/// <summary>
/// Executes commandText query and returns the number of rows affected.
/// </summary>
/// <param name="commandText">Sql statement to execute</param>
/// <returns>Number of rows affected</returns>
public virtual int ExecuteNonQuery(string commandText)
{
try
{
((ITdoHelper)this).PrepareAutomaticConnection();
((ITdoHelper)this).PrepareAutomaticTransaction();
SqlCommand sqlCommand = this.CreateCommand(commandText);
sqlCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
return sqlCommand.ExecuteNonQuery();
}
finally
{
((ITdoHelper)this).UnPrepareAutomaticTransaction();
((ITdoHelper)this).UnPrepareAutomaticConnection();
}
}
/// <summary>
/// Runs the query and returns the first actualValue at the left top of the "rectangular" result.
/// </summary>
/// <param name="commandText">statement SQL to execute</param>
/// <returns>first actualValue at the left top of the "rectangular" result.</returns>
public virtual object ExecuteScalar(string commandText)
{
try
{
((ITdoHelper)this).PrepareAutomaticConnection();
((ITdoHelper)this).PrepareAutomaticTransaction();
SqlCommand sqlCommand = this.CreateCommand(commandText);
sqlCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
return sqlCommand.ExecuteScalar();
}
finally
{
((ITdoHelper)this).UnPrepareAutomaticTransaction();
((ITdoHelper)this).UnPrepareAutomaticConnection();
}
}
/// <summary>
/// Runs the query and returns a DataTable object with the selected records
/// </summary>
/// <param name="selectCommandText">Select Statement</param>
/// <param name="entityName">Table name to assign to DataTable object</param>
/// <returns>DataTable containing the selected records</returns>
public virtual DataTable Fill(string selectCommandText, string entityName)
{
DataTable dt = new DataTable(entityName);
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
dt.BeginLoadData();
SqlDataAdapter da = this.CreateDataAdapter(selectCommandText);
da.SelectCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
da.FillError += new FillErrorEventHandler(this.internalFillErrorEventHandler);
da.RowUpdated += new SqlRowUpdatedEventHandler(this.internalRowUpdatedEventHandler);
da.RowUpdating += new SqlRowUpdatingEventHandler(this.internalRowUpdatingEventHandler);
da.Fill(dt);
dt.EndLoadData();
return dt;
}
/// <summary>
/// Gets a new instance of the System.Data.SqlClient.SqlDataAdapter class with supplied values.
/// </summary>
/// <param name="selectCommandText">Sets a Transact-SQL statement or stored procedure used to select records in the data source.</param>
/// <param name="sqlParameters">Sets the parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.</param>
/// <returns>System.Data.SqlClient.SqlDataAdapter class with a set of data commands and a database connection that are used to fill the System.Data.DataSet.</returns>
public virtual SqlDataAdapter CreateDataAdapter(
string selectCommandText,
params SqlParameter[] sqlParameters)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = this.CreateCommand(selectCommandText, sqlParameters);
return sqlDataAdapter;
}
/// <summary>
/// Gets a new instance of the System.Data.SqlClient.SqlDataAdapter class with supplied values.
/// </summary>
/// <param name="selectCommand">Sets a Transact-SQL statement or stored procedure used to select records in the data source.</param>
/// <param name="insertCommand">Sets a Transact-SQL statement or stored procedure to insert new records into the data source.</param>
/// <param name="updateCommand">Sets a Transact-SQL statement or stored procedure used to update records in the data source.</param>
/// <param name="deleteCommand">Sets a Transact-SQL statement or stored procedure to delete records from the data set.</param>
/// <returns>System.Data.SqlClient.SqlDataAdapter class with a set of data commands and a database connection that are used to fill the System.Data.DataSet and update a SQL Server database.</returns>
public virtual SqlDataAdapter CreateDataAdapter(
SqlCommand selectCommand,
SqlCommand insertCommand,
SqlCommand updateCommand,
SqlCommand deleteCommand)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.DeleteCommand = deleteCommand;
sqlDataAdapter.InsertCommand = insertCommand;
sqlDataAdapter.SelectCommand = selectCommand;
sqlDataAdapter.UpdateCommand = updateCommand;
return sqlDataAdapter;
}
/// <summary>
/// Gets a new instance of the System.Data.SqlClient.SqlDataAdapter class with supplied values.
/// </summary>
/// <param name="selectCommand">Sets a Transact-SQL statement or stored procedure used to select records in the data source.</param>
/// <param name="insertCommand">Sets a Transact-SQL statement or stored procedure to insert new records into the data source.</param>
/// <param name="updateCommand">Sets a Transact-SQL statement or stored procedure used to update records in the data source.</param>
/// <param name="deleteCommand">Sets a Transact-SQL statement or stored procedure to delete records from the data set.</param>
/// <param name="acceptChangesDuringFill">Sets a value indicating whether System.Data.FillDataRow.AcceptChanges() is called on a System.Data.FillDataRow after it is added to the System.Data.DataTable during any of the Fill operations.</param>
/// <param name="acceptChangesDuringUpdate">Sets a value indicating whether System.Data.FillDataRow.AcceptChanges() is called on a System.Data.FillDataRow after it is updated into the System.Data.DataTable during any of the Fill operations.</param>
/// <param name="continueUpdateOnError">Sets a value that specifies whether to generate an exception when an error is encountered during a row update.</param>
/// <param name="fillLoadOption">[To be supplied.]</param>
/// <param name="missingMappingAction">Determines the action to take when incoming data does not have a matching table or column. The default is Passthrough.</param>
/// <param name="missingSchemaAction">Determines the action to take when existing System.Data.DataSet schema does not match incoming data.</param>
/// <param name="returnProviderSpecificTypes">[To be supplied.]</param>
/// <param name="updateBatchSize">Sets a value that enables or disables batch processing support, and specifies the number of commands that can be executed in a batch.</param>
/// <returns>System.Data.SqlClient.SqlDataAdapter class with a set of data commands and a database connection that are used to fill the System.Data.DataSet and update a SQL Server database.</returns>
public virtual SqlDataAdapter CreateDataAdapter(
SqlCommand selectCommand,
SqlCommand insertCommand,
SqlCommand updateCommand,
SqlCommand deleteCommand,
bool acceptChangesDuringFill,
bool acceptChangesDuringUpdate,
bool continueUpdateOnError,
LoadOption fillLoadOption,
MissingMappingAction missingMappingAction,
MissingSchemaAction missingSchemaAction,
bool returnProviderSpecificTypes,
int updateBatchSize)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.AcceptChangesDuringFill = acceptChangesDuringFill;
sqlDataAdapter.AcceptChangesDuringUpdate = acceptChangesDuringUpdate;
sqlDataAdapter.ContinueUpdateOnError = continueUpdateOnError;
sqlDataAdapter.DeleteCommand = deleteCommand;
sqlDataAdapter.FillLoadOption = fillLoadOption;
sqlDataAdapter.InsertCommand = insertCommand;
sqlDataAdapter.MissingMappingAction = missingMappingAction;
sqlDataAdapter.MissingSchemaAction = missingSchemaAction;
sqlDataAdapter.ReturnProviderSpecificTypes = returnProviderSpecificTypes;
sqlDataAdapter.SelectCommand = selectCommand;
sqlDataAdapter.UpdateBatchSize = updateBatchSize;
sqlDataAdapter.UpdateCommand = updateCommand;
return sqlDataAdapter;
}
/// <summary>
/// Runs the query and returns a DataTable object with the selected records
/// </summary>
/// <param name="selectCommandText">Select Statement</param>
/// <returns>DataTable containing the selected records</returns>
public virtual DataTable Fill(string selectCommandText)
{
DataTable dt = new DataTable();
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
dt.BeginLoadData();
SqlDataAdapter da = this.CreateDataAdapter(selectCommandText);
da.SelectCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
da.FillError += new FillErrorEventHandler(this.internalFillErrorEventHandler);
da.RowUpdated += new SqlRowUpdatedEventHandler(this.internalRowUpdatedEventHandler);
da.RowUpdating += new SqlRowUpdatingEventHandler(this.internalRowUpdatingEventHandler);
da.Fill(dt);
dt.EndLoadData();
return dt;
}
/// <summary>
/// Executes the scalar function.
/// </summary>
/// <param name="scalarFunction">The scalar function.</param>
/// <returns></returns>
public virtual object ExecuteScalarFunction(ITdoColumn scalarFunction)
{
return this.ExecuteScalar("SELECT " + scalarFunction.ColumnName + " " + scalarFunction.Caption);
}
/// <summary>
/// Executes the scalar functions.
/// </summary>
/// <param name="scalarFunctions">The scalar functions.</param>
/// <returns></returns>
public virtual DataTable ExecuteScalarFunctions(params ITdoColumn[] scalarFunctions)
{
string[] columns = new string[scalarFunctions.Length];
for (int i=0;i<scalarFunctions.Length;i++)
{
columns[i] = scalarFunctions[i].ColumnName + " " +scalarFunctions[i].Caption;
}
return this.Fill("SELECT " + String.Join(",", columns));
}
/// <summary>
/// Fill Schema Table/View for an ITdoEntity with given schemaType
/// </summary>
/// <param name="entity">must be an ITdoEntity type</param>
/// <param name="schemaType">Mapped or Source</param>
/// <returns></returns>
public DataTable FillSchema(ITdoEntity entity, SchemaType schemaType)
{
DataTable dt = new DataTable();
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
SqlCommand comm = this.CreateCommand("select * from " + entity.FullEntityName);
comm.CommandTimeout = this.pCommandTimeOut;
SqlDataAdapter da = this.CreateDataAdapter(comm, null, null, null, false, false, false, LoadOption.PreserveChanges, MissingMappingAction.Passthrough, MissingSchemaAction.AddWithKey, true, 0);
da.SelectCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
da.FillError += new FillErrorEventHandler(this.internalFillErrorEventHandler);
da.RowUpdated += new SqlRowUpdatedEventHandler(this.internalRowUpdatedEventHandler);
da.RowUpdating += new SqlRowUpdatingEventHandler(this.internalRowUpdatingEventHandler);
da.FillSchema(dt, schemaType);
dt.TableName = entity.EntityName;
return dt;
}
/// <summary>
/// Creates a DataTable instance using Join Operator
/// </summary>
/// <param name="joinInfo">JoinHelper Class for Select Join Information</param>
/// <param name="clauses">Selection clause for SELECT</param>
/// <param name="selectFields">fields list (ITdoType) to select</param>
/// <returns>DataTable with Select Join resulting records </returns>
public virtual DataTable SelectJoin(JoinHelper joinInfo, string clauses, params ITdoColumn[] selectFields)
{
string sfields = TdoEntityBase.FieldsJoinList(selectFields);
ConnectionState cs = this.Connection.State; //save connection state
SqlCommand comm = new SqlCommand();
comm.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
comm.Connection = this.pConnection;
comm.Transaction = this.pTransaction;
comm.CommandTimeout = this.CommandTimeOut;
string tablesname = "";
string sjoin = "";
string onoperator = "";
tablesname += joinInfo.tables[0].FullEntityName; //prima
for (int i = 0; i < joinInfo.joins.Length; i++)
{
switch (joinInfo.joins[i])
{
case (Join.InnerJoin):
default:
sjoin = " INNER JOIN "; break;
case (Join.FullOuterJoin): sjoin = " FULL OUTER JOIN "; break;
case (Join.LeftOuterJoin): sjoin = " LEFT OUTER JOIN "; break;
case (Join.RightOuterJoin): sjoin = " RIGHT OUTER JOIN "; break;
case (Join.CrossJoin): sjoin = " CROSS JOIN "; break;
}
tablesname += sjoin;
if (joinInfo.tables[i * 2 + 1] as TdoTableFunctionBase != null)
{
tablesname += ((TdoTableFunctionBase)joinInfo.tables[i * 2 + 1]).FullSelectEntityName();
}
else if (joinInfo.tables[i * 2 + 1] as TdoEntityBase != null)
{
tablesname += ((TdoEntityBase)joinInfo.tables[i * 2 + 1]).FullSelectEntityName();
}
else
{
tablesname += joinInfo.tables[i * 2 + 1].EntityName;
}
onoperator = " ON ";
onoperator += joinInfo.fields[i * 2].TdoEntity.EntityName + "." + joinInfo.fields[i * 2].ColumnName;
onoperator += " " + (string)new SqlOperatorHelper(joinInfo.sqlOperators[i]) + " ";
onoperator += joinInfo.fields[i * 2 + 1].TdoEntity.EntityName + "." + joinInfo.fields[i * 2 + 1].ColumnName + " ";
tablesname += onoperator + " ";
}
comm.CommandText = "select " + sfields + " from " + tablesname + clauses;
comm.CommandType = CommandType.Text;
foreach (TdoTableBase table in joinInfo.tables)
{
comm.Parameters.AddRange(table.pTdoParameterCollection.ParameterCollection.ToArray());
}
SqlDataAdapter da = new SqlDataAdapter(comm);
da.SelectCommand.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
da.FillError += new FillErrorEventHandler(this.internalFillErrorEventHandler);
da.RowUpdated += new SqlRowUpdatedEventHandler(this.internalRowUpdatedEventHandler);
da.RowUpdating += new SqlRowUpdatingEventHandler(this.internalRowUpdatingEventHandler);
DataTable dt = new DataTable();
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
da.SelectCommand.Transaction = this.pTransaction;
try
{
if (cs != ConnectionState.Open) this.Connection.Open();
dt.BeginLoadData();
da.Fill(dt);
dt.EndLoadData();
}
finally
{
if (cs == ConnectionState.Open && this.Connection.State == ConnectionState.Closed) this.Connection.Open();
if (cs == ConnectionState.Closed && this.Connection.State == ConnectionState.Open) this.Connection.Close();
comm.Parameters.Clear();
//RESET DEI PARAMETRI
foreach (TdoTableBase table in joinInfo.tables)
{
table.pTdoParameterCollection.Reset();
}
}
return dt;
}
/// <summary>
/// Creates a DataTable instance using Join Operator
/// </summary>
/// <param name="joinInfo">JoinHelper Class for Select Join Information</param>
/// <param name="selectFields">fields list (ITdoType) to select</param>
/// <returns>DataTable with Select Join resulting records </returns>
public virtual DataTable SelectJoin(JoinHelper joinInfo, params ITdoColumn[] selectFields)
{
return this.SelectJoin(joinInfo, "", selectFields);
}
/// <summary>
/// Creates an SqlDataReader instance using Join Operator
/// </summary>
/// <param name="joinInfo">JoinHelper Class for Select Join Information</param>
/// <param name="clauses">Selection clause for SELECT</param>
/// <param name="selectFields">fields list (ITdoType) to select</param>
/// <returns>DataTable with Select Join resulting records </returns>
public virtual SqlDataReader SelectJoinReader(JoinHelper joinInfo, string clauses, params ITdoColumn[] selectFields)
{
string sfields = TdoEntityBase.FieldsJoinList(selectFields);
SqlCommand comm = new SqlCommand();
comm.StatementCompleted += new StatementCompletedEventHandler(((ITdoHelper)(this)).internalStatementCompletedEventHandler);
comm.Connection = this.pConnection;
comm.Transaction = this.pTransaction;
comm.CommandTimeout = this.CommandTimeOut;
string tablesname = "";
string sjoin = "";
string onoperator = "";
tablesname += joinInfo.tables[0].FullEntityName; //prima
for (int i = 0; i < joinInfo.joins.Length; i++)
{
switch (joinInfo.joins[i])
{
case (Join.InnerJoin):
default:
sjoin = " INNER JOIN "; break;
case (Join.FullOuterJoin): sjoin = " FULL OUTER JOIN "; break;
case (Join.LeftOuterJoin): sjoin = " LEFT OUTER JOIN "; break;
case (Join.RightOuterJoin): sjoin = " RIGHT OUTER JOIN "; break;
case (Join.CrossJoin): sjoin = " CROSS JOIN "; break;
}
tablesname += sjoin;
if (joinInfo.tables[i * 2 + 1] as TdoTableFunctionBase != null)
{
tablesname += ((TdoTableFunctionBase)joinInfo.tables[i * 2 + 1]).FullSelectEntityName();
}
else if (joinInfo.tables[i * 2 + 1] as TdoEntityBase != null)
{
tablesname += ((TdoEntityBase)joinInfo.tables[i * 2 + 1]).FullSelectEntityName();
}
else
{
tablesname += joinInfo.tables[i * 2 + 1].EntityName;
}
onoperator = " ON ";
onoperator += joinInfo.fields[i * 2].TdoEntity.EntityName + "." + joinInfo.fields[i * 2].ColumnName;
onoperator += " " + (string)new SqlOperatorHelper(joinInfo.sqlOperators[i]) + " ";
onoperator += joinInfo.fields[i * 2 + 1].TdoEntity.EntityName + "." + joinInfo.fields[i * 2 + 1].ColumnName + " ";
tablesname += onoperator + " ";
}
comm.CommandText = "select " + sfields + " from " + tablesname + clauses;
comm.CommandType = CommandType.Text;
foreach (TdoTableBase table in joinInfo.tables)
{
comm.Parameters.AddRange(table.pTdoParameterCollection.ParameterCollection.ToArray());
}
comm.Transaction = this.pTransaction;
SqlDataReader dr;
try
{
dr = comm.ExecuteReader();
return dr;
}
finally
{
//RESET DEI PARAMETRI
foreach (TdoTableBase table in joinInfo.tables)
table.pTdoParameterCollection.Reset();
}
}
/// <summary>
/// Creates an SqlDataReader instance using Join Operator
/// </summary>
/// <param name="joinInfo">JoinHelper Class for Select Join Information</param>
/// <param name="selectFields">fields list (ITdoType) to select</param>
/// <returns>DataTable with Select Join resulting records </returns>
public virtual SqlDataReader SelectJoinReader(JoinHelper joinInfo, params ITdoColumn[] selectFields)
{
return this.SelectJoinReader(joinInfo, "", selectFields);
}
/// <summary>
/// TdoHelperBase Dispose
/// </summary>
public void Dispose()
{
try
{
//base.Dispose();
if (this.pTransaction != null)
this.pTransaction.Dispose();
if (this.pConnection.State == ConnectionState.Open)
this.pConnection.Close();
}
finally
{
if (this.Disposed != null)
this.Disposed(this, EventArgs.Empty); //raise Disposed event.
GC.SuppressFinalize(this); //Request the system do not invoke Finalize for TdoHelper
}
}
/// <summary>
/// Prepare Automatic Connection.
/// </summary>
void ITdoHelper.PrepareAutomaticConnection()
{
if (this.AutomaticOpenCloseConnection)
{
if (this.pConnection.State != ConnectionState.Open)
{
this.pConnection.Open();
if (String.IsNullOrEmpty(this.pServerVersion))
this.pServerVersion = this.pConnection.ServerVersion;
}
}
}
/// <summary>
/// Unprepare Automatic Connection.
/// </summary>
void ITdoHelper.UnPrepareAutomaticConnection()
{
if (this.AutomaticOpenCloseConnection)
{
if (this.Connection.State == ConnectionState.Open && this.Transaction == null)
{
this.pConnection.Close();
}
}
}
/// <summary>
/// Unprepare Automatic Transaction.
/// </summary>
void ITdoHelper.PrepareAutomaticTransaction()
{
if (this.AutomaticTransaction)
{
if (this.Transaction == null && this.pConnection.State == ConnectionState.Open)
{
this.pTransaction = this.pConnection.BeginTransaction();
}
}
}
/// <summary>
/// Prepare Automatic Transaction.
/// </summary>
void ITdoHelper.UnPrepareAutomaticTransaction()
{
if (this.AutomaticTransaction)
{
if (this.Transaction != null)
{
this.pTransaction.Commit();
}
}
}
#endregion Methods
#region Events and Events Handlers
/// <summary>
/// Occurs when SQL Server connection state change.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields")]
public event StateChangeEventHandler ConnectionStateChange;
private void internalConnectionStateChangeEventHandler(object sender, StateChangeEventArgs e)
{
if (this.ConnectionStateChange != null)
this.ConnectionStateChange(sender, e);
}
/// <summary>
/// Occurs when SQL Server returns a warning or informational message.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields")]
public event SqlInfoMessageEventHandler InfoMessage;
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1811:AvoidUncalledPrivateCode")]
private void internalInfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
{
if (this.InfoMessage != null)
this.InfoMessage(sender, e);
}
/// <summary>
/// Occurs after a command is executed against the data source.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields")]
public event StatementCompletedEventHandler StatementCompleted;
/// <summary>
/// [Internal] use only.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void ITdoHelper.internalStatementCompletedEventHandler(object sender, StatementCompletedEventArgs e)
{
if (this.StatementCompleted != null)
this.StatementCompleted(sender, e);
}
/// <summary>
/// Occurs during Fill operations with errors.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields")]
public event FillErrorEventHandler FillError;
private void internalFillErrorEventHandler(object sender, FillErrorEventArgs e)
{
if (this.FillError != null)
this.FillError(sender, e);
}
/// <summary>
/// Occurs during Update after a command is executed against the data source. The attempt to update is made, so the event fires.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields")]
public event SqlRowUpdatedEventHandler RowUpdated;
private void internalRowUpdatedEventHandler(object sender, SqlRowUpdatedEventArgs e)
{
if (this.RowUpdated != null)
{
this.RowUpdated(sender, e);
}
}
/// <summary>
/// Occurs during Update after a command is executed against the data source. The attempt to update is made, so the event fires.
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2235:MarkAllNonSerializableFields")]
public event SqlRowUpdatingEventHandler RowUpdating;
private void internalRowUpdatingEventHandler(object sender, SqlRowUpdatingEventArgs e)
{
if (this.RowUpdating != null)
{
this.RowUpdating(sender, e);
}
}
#endregion Events and Events Handlers
#region OnDeserializedMethod
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1801:AvoidUnusedParameters"), OnDeserialized()]
private void OnDeserializedMethod(StreamingContext context)
{
//Restore SqlConnection object by recreating from pConnectionString
if (!String.IsNullOrEmpty(this.pConnectionString))
{
this.ConnectionString = this.pConnectionString;
//Restore SqlConnection.State from this.pConnectionState
if (this.pConnectionStateBackup == ConnectionState.Open)
this.OpenConnection();
else
this.pConnectionStateBackup = ConnectionState.Closed;
}
}
#endregion OnDeserializedMethod
#region IComponent Members
/// <summary>
/// Gets or sets the <see cref="T:System.ComponentModel.ISite"></see> associated with the <see cref="T:System.ComponentModel.IComponent"></see>.
/// </summary>
/// <value></value>
/// <returns>The <see cref="T:System.ComponentModel.ISite"></see> object associated with the component; or null, if the component does not have a site.</returns>
[XmlIgnore()]
public ISite Site
{
get
{
return this.piSite;
}
set
{
this.piSite = value;
}
}
#endregion
}
}
|