// not caching Command
public DataSet RunQuery(String strQueryCommand)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strQueryCommand))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (DataQueryProvider.IsSQLConnection(db) == false)
{
strQueryCommand = SQLiteDatabaseHelper.RepairSelectSQLite(strQueryCommand);
strQueryCommand = strQueryCommand.Replace("N'", "'");
}
DbCommand cmd = GetQuery(strQueryCommand);
if (cmd == null)
{
return(null);
}
return(RunCommand(cmd));
}
public DataSet RunCommand(DbCommand cmd)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(cmd.CommandText))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
try
{
return(db.ExecuteDataSet(cmd));
}
catch (System.Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
if (((System.Data.SqlClient.SqlException)ex).ErrorCode == -2146232060 && ex.Message.Contains("TCP Provider"))
{
ShowDisconnectWaitingDialog();
return(null);
}
}
//ABCHelper.ABCMessageBox.Show( ex.Message , "Message" , MessageBoxButtons.OK , MessageBoxIcon.Stop );
return(null);
}
}
public DbCommand GetSPCommand(String strSPName)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strSPName))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
DbCommand cmd = null;
if (SPCommandList.TryGetValue(strSPName, out cmd))
{
return(cmd);
}
cmd = db.GetStoredProcCommand(strSPName);
SPCommandList.Add(strSPName, cmd);
return(cmd);
}
public DbCommand GetQuery(String strQueryCommand)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strQueryCommand))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
try
{
return(db.GetSqlStringCommand(strQueryCommand));
}
catch (Exception ex)
{
ABCHelper.ABCMessageBox.Show("Have some mistakes with Database connection.");
}
return(null);
}
public DbCommand GetScriptCommand(String strScriptQuery)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strScriptQuery))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
DbCommand cmd = null;
if (ScriptCommandList.TryGetValue(strScriptQuery, out cmd))
{
return(cmd);
}
cmd = db.GetSqlStringCommand(strScriptQuery);
cmd.CommandType = CommandType.Text;
ScriptCommandList.Add(strScriptQuery, cmd);
return(cmd);
}
public object RunStoredProcedure(string strSPName, BusinessObject obj)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strSPName))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (String.IsNullOrWhiteSpace(strSPName))
{
return(null);
}
try
{
DbCommand cmd = GetSPCommand(strSPName);
AddParameterForObject(obj, cmd);
db.ExecuteNonQuery(cmd);
String strIDCol = DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName);
if (!String.IsNullOrWhiteSpace(strIDCol))
{
Guid ret = ABCHelper.DataConverter.ConvertToGuid(db.GetParameterValue(cmd, strIDCol));
return(ret);
}
return(null);
}
catch (System.Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
if (((System.Data.SqlClient.SqlException)ex).ErrorCode == -2146232060 && ex.Message.Contains("TCP Provider"))
{
ShowDisconnectWaitingDialog();
return(0);
}
}
ABCHelper.ABCMessageBox.Show(ex.Source + ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
public object RunScript(string strScriptQuery, BusinessObject obj)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strScriptQuery))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (String.IsNullOrWhiteSpace(strScriptQuery))
{
return(null);
}
if (DataQueryProvider.IsSQLConnection(db) == false)
{
strScriptQuery = SQLiteDatabaseHelper.RepairSelectSQLite(strScriptQuery);
}
try
{
DbCommand cmd = GetScriptCommand(strScriptQuery);
AddParameterForObject(obj, cmd);
db.ExecuteNonQuery(cmd);
Guid ret = ABCHelper.DataConverter.ConvertToGuid(db.GetParameterValue(cmd, DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName)));
return(ret);
}
catch (System.Exception ex)
{
ABCHelper.ABCMessageBox.Show(ex.Source + ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
public DataSet RunStoredProcedure(string strSPName)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strSPName))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (String.IsNullOrWhiteSpace(strSPName))
{
return(null);
}
try
{
DbCommand cmd = GetSPCommand(strSPName);
return(db.ExecuteDataSet(cmd));
}
catch (System.Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
if (((System.Data.SqlClient.SqlException)ex).ErrorCode == -2146232060 && ex.Message.Contains("TCP Provider"))
{
ShowDisconnectWaitingDialog();
return(null);
}
}
ABCHelper.ABCMessageBox.Show(ex.Message);
return(null);
}
}
public string ExecuteScript(String strScript)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strScript))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
try
{
if (DataQueryProvider.IsSQLConnection(db) == false)
{
strScript = SQLiteDatabaseHelper.RepairSelectSQLite(strScript);
}
db.ExecuteNonQuery(CommandType.Text, strScript);
return("Command excute succesfully!");
}
catch (System.Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
if (((System.Data.SqlClient.SqlException)ex).ErrorCode == -2146232060 && ex.Message.Contains("TCP Provider"))
{
ShowDisconnectWaitingDialog();
}
}
return(ex.Message);
}
}
// caching Command
public DataSet RunScript(string strScriptQuery)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(strScriptQuery))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (String.IsNullOrWhiteSpace(strScriptQuery))
{
return(null);
}
if (DataQueryProvider.IsSQLConnection(db) == false)
{
strScriptQuery = SQLiteDatabaseHelper.RepairSelectSQLite(strScriptQuery);
}
try
{
DbCommand cmd = GetScriptCommand(strScriptQuery);
return(db.ExecuteDataSet(cmd));
}
catch (System.Exception ex)
{
ABCHelper.ABCMessageBox.Show(ex.Message);
return(null);
}
}
public object RunCommand(DbCommand cmd, string retVariable)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(cmd.CommandText))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
try
{
db.ExecuteNonQuery(cmd);
return((object)db.GetParameterValue(cmd, retVariable));
}
catch (System.Exception ex)
{
if (ex is System.Data.SqlClient.SqlException)
{
if (((System.Data.SqlClient.SqlException)ex).ErrorCode == -2146232060 && ex.Message.Contains("TCP Provider"))
{
ShowDisconnectWaitingDialog();
return(null);
}
}
ABCHelper.ABCMessageBox.Show(ex.Message);
return(null);
}
}
public void AddInParameter(DbCommand cmd, PropertyInfo property, object objValue)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(cmd.CommandText))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
String strProperty = property.Name;
if (BusinessObjectHelper.IsBaseProperty(strProperty))
{
return;
}
if (property.PropertyType.Equals(typeof(Guid)) || property.PropertyType.Equals(typeof(Nullable <Guid>)))
{
if (objValue == null || objValue == DBNull.Value || ABCHelper.DataConverter.ConvertToGuid(objValue) == Guid.Empty)
{
db.AddInParameter(cmd, strProperty, DbType.Guid, null);
}
else
{
db.AddInParameter(cmd, strProperty, DbType.Guid, objValue);
}
}
else if (property.PropertyType.Equals(typeof(Int32)) || property.PropertyType.Equals(typeof(Nullable <int>)))
{
if (objValue == null || objValue == DBNull.Value || (int)objValue == int.MinValue)
{
db.AddInParameter(cmd, strProperty, DbType.Int32, null);
}
else
{
db.AddInParameter(cmd, strProperty, DbType.Int32, objValue);
}
}
else if (property.PropertyType.Equals(typeof(Boolean)))
{
db.AddInParameter(cmd, strProperty, DbType.Boolean, objValue);
}
else if (property.PropertyType.Equals(typeof(short)))
{
db.AddInParameter(cmd, strProperty, DbType.Int16, objValue);
}
else if (property.PropertyType.Equals(typeof(double)))
{
db.AddInParameter(cmd, strProperty, DbType.Double, objValue);
}
else if (property.PropertyType.Equals(typeof(decimal)))
{
db.AddInParameter(cmd, strProperty, DbType.Decimal, objValue);
}
else if (property.PropertyType.Equals(typeof(byte[])))
{
db.AddInParameter(cmd, strProperty, DbType.Binary, objValue);
}
else if ((property.PropertyType.Equals(typeof(String))) || (property.PropertyType.Equals(typeof(string))))
{
db.AddInParameter(cmd, strProperty, DbType.String, objValue);
}
else if (property.PropertyType.Equals(typeof(DateTime)))
{
if ((DateTime)objValue == DateTime.MinValue)
{
db.AddInParameter(cmd, strProperty, DbType.DateTime, new DateTime(1, 1, 1));
}
else
{
db.AddInParameter(cmd, strProperty, DbType.DateTime, objValue);
}
}
else if (property.PropertyType.Equals(typeof(Nullable <DateTime>)))
{
if (objValue == null || objValue == DBNull.Value || (DateTime)objValue == DateTime.MinValue)
{
db.AddInParameter(cmd, strProperty, DbType.DateTime, null);
}
else
{
db.AddInParameter(cmd, strProperty, DbType.DateTime, objValue);
}
}
}
public void AddParameterForObject(BusinessObject obj, DbCommand cmd)
{
Database db = CurrentDatabase;
if (CurrentDatabase == null)
{
if (DataStructureProvider.IsQuerySystemDB(cmd.CommandText))
{
db = DataQueryProvider.SystemDatabase;
}
else
{
db = DataQueryProvider.CompanyDatabase;
}
}
if (cmd.Parameters.Count == 0)
{
BusinessObjectHelper.InitPropertyList(obj.AATableName);
foreach (PropertyInfo property in BusinessObjectHelper.PropertyList[obj.AATableName].Values)
{
if (property.Name == DataStructureProvider.GetPrimaryKeyColumn(obj.AATableName) && cmd.CommandText.Contains("Insert"))
{
db.AddOutParameter(cmd, property.Name, DbType.Guid, 64);
// AddOutParameter( cmd , property.Name , SqlDbType.Int , 8 );
}
else
{
object objValue = ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, property);
if (DataStructureProvider.IsForeignKey(obj.AATableName, property.Name))
{
if (objValue == null || ABCHelper.DataConverter.ConvertToGuid(objValue) == Guid.Empty)
{
objValue = DBNull.Value;
}
}
if (objValue == null)
{
objValue = DBNull.Value;
}
AddInParameter(cmd, property, objValue);
}
}
}
else
{
foreach (DbParameter param in cmd.Parameters)
{
if (param.Direction == ParameterDirection.Input)
{
String strFieldName = param.ParameterName.Replace("@", "");
object objValue = ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, strFieldName);
if (DataStructureProvider.IsForeignKey(obj.AATableName, strFieldName))
{
if (objValue == null || ABCHelper.DataConverter.ConvertToGuid(objValue) == Guid.Empty)
{
objValue = DBNull.Value;
}
}
if (objValue == null)
{
objValue = DBNull.Value;
}
if (param.Value != objValue && objValue.ToString() != param.Value.ToString())
{
db.SetParameterValue(cmd, param.ParameterName, objValue);
}
}
}
}
}
