/// <summary>
/// Use this function for disconnected commands. The result will be stored as a table in the data set
/// </summary>
/// <param name="command"></param>
/// <param name="tblName">name of the table, which will store the result in the dataSet </param>
/// <returns> The number of records the new table has.</returns>
public int Execute(CommandVirtualization command, string tblName)
{
int retVal = 0;
DbDataAdapter adapter = null;
DbCommand dbCommand = null;
tableCommands currTableCommands;
if (objDS.Tables.Contains(tblName))
{
objDS.Tables[tblName].Clear();
objDS.Tables[tblName].Dispose();
objDS.Tables.Remove(tblName);
}
try
{
adapter = GetNewAdapter();
if (command.addSchema)
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
dbCommand = GetNewCommand();
currTableCommands = new tableCommands();
currTableCommands.selectSql = command.DisconnectedSqls.sqlQuery;
currTableCommands.insertSql = command.DisconnectedSqls.sqlInsert;
currTableCommands.updateSql = command.DisconnectedSqls.sqlUpdate;
currTableCommands.deleteSql = command.DisconnectedSqls.sqlDelete;
if (transactionMode)
{
dbCommand.Transaction = dbTransaction;
}
else
{
conn.Open();
}
dbCommand.Connection = conn;
if (m_usedStroedProcedures)
{
dbCommand.CommandType = CommandType.StoredProcedure;
}
dbCommand.CommandText = command.DisconnectedSqls.sqlQuery;
SetParameters(command.Parameters, dbCommand);
adapter.SelectCommand = dbCommand;
//join query on adama project throw an exception. We omit this row until bug detection
//adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
retVal = adapter.Fill(objDS, tblName); //create new table in the data set
if (m_tablesCommands.ContainsKey(tblName))
{
m_tablesCommands.Remove(tblName);
}
m_tablesCommands.Add(tblName, currTableCommands); //add table properties to dictionary
return(retVal);
}
catch (System.OutOfMemoryException outOfMemEx)
{
throw outOfMemEx;
}
catch (Exception ex)
{
throw ex;
}
finally
{
//#1380
if (adapter != null)
{
adapter.Dispose();
}
if (dbCommand != null)
{
dbCommand.Parameters.Clear();
dbCommand.Dispose();
}
//End #1380
if (!transactionMode)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
//public virtual void ClearConnection()
//{
// objDS.Clear();
// objDS.Dispose();
// objDS = null;
// GC.Collect();
//}
/// <summary>
/// Update specific table in the data set.
/// all fields must be wrriten in the sql statment for insert and update.
/// the order of the wrriten fields is according to order they were selected by the select statment.
/// i.e ,select * : the order is according to the fields order in the table.
///
/// Notes:
/// 1. You must not use NOW() in the queries, you should set the DateTime fields directly in the data table.
/// If you use NOW() concurreny exception is thrown.
/// 2. an integer field in the db is refered to int16.
/// if you pass as a parameter integer(the default is int32) you will get overflow error.
/// </summary>
/// <param name="tblName"></param>
/// <returns></returns>
public int Update(string tblName)
{
int result = 0;
tableCommands currentTableCommands;
DbCommand updateCommand;
DbCommand deleteCommand;
DbCommand insertCommand;
DbDataAdapter dataAdapter;
try
{
updateCommand = GetNewCommand();
deleteCommand = GetNewCommand();
insertCommand = GetNewCommand();
if (m_usedStroedProcedures)
{
updateCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.CommandType = CommandType.StoredProcedure;
insertCommand.CommandType = CommandType.StoredProcedure;
}
dataAdapter = GetNewAdapter();
currentTableCommands = new tableCommands();
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.DeleteCommand = deleteCommand;
dataAdapter.InsertCommand = insertCommand;
if (transactionMode)
{
dataAdapter.UpdateCommand.Transaction = dbTransaction;
dataAdapter.DeleteCommand.Transaction = dbTransaction;
dataAdapter.InsertCommand.Transaction = dbTransaction;
}
else
{
conn.Open();
}
updateCommand.Connection = conn;
deleteCommand.Connection = conn;
insertCommand.Connection = conn;
m_tablesCommands.TryGetValue(tblName, out currentTableCommands);
updateCommand.CommandText = currentTableCommands.updateSql;
deleteCommand.CommandText = currentTableCommands.deleteSql;
insertCommand.CommandText = currentTableCommands.insertSql;
SetParametersForUpdate(ref updateCommand, objDS.Tables[tblName], QueryType.Update);
SetParametersForUpdate(ref deleteCommand, objDS.Tables[tblName], QueryType.Delete);
SetParametersForUpdate(ref insertCommand, objDS.Tables[tblName], QueryType.Insert);
result = dataAdapter.Update(objDS, tblName);
return(result);
}
catch (OutOfMemoryException outOfMemE)
{
throw outOfMemE;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!(transactionMode))
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
//return result;
}
