public void InsertItem(IGeneralDataProvider BaseInterface, string GridId, Hashtable GridData)
{
GeneralDataProviderItem Item = null;
try
{
this.GridData = GridData;
Item = BaseInterface.LoadItemFromGridControl(true, GridId);
if (Item.Errors.Count > 0)
{
PageErrors = new NameValueCollection();
PageErrors.Add(Item.Errors);
}
else
{
DataProvider.InsertItem(Item);
}
}
catch (Exception ex)
{
Item.Errors.Add("grid", ex.Message);
if (PageErrors == null)
{
PageErrors = new NameValueCollection();
}
PageErrors.Add("Error", ex.Message);
}
}
public bool PTab(string Filter)
{
DataSet ds = new DataSet();
string selectList = "";
Item = BasePage.GetDataProviderItem(this);
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
selectList += Select.Dao.PoeColAspas(FieldName) + ",";
}
ds = Dao.RunSql("select " + selectList.TrimEnd(',') + " from " + Dao.PoeColAspas(TableName) + (Filter != "" ? " where " + Filter : ""), 0, 1);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
foreach (string Key in Item.Fields.Keys)
{
FieldBase Field = Item.Fields[Key];
string FieldName = Field.Name;
string format = (Item.Fields[Key].FieldType == FieldType.Date ? Select.DateFormat : "");
Item.Fields[Key].SetValue(dr[FieldName], format);
}
LocateRecord(Item);
return(true);
}
else
{
LocateRecord(Item);
PageNumber = 0;
return(false);
}
}
public void RaiseSetRelationFields(object sender, GeneralDataProviderItem Item)
{
if (SetRelationFields != null)
{
SetRelationFields(sender, Item);
}
}
private void InitializeDataProvider(IGeneralDataProvider BasePage, string TableName, string DataBaseName, string IndexName, string SqlFrom)
{
this.TableName = TableName;
this.DataBaseName = DataBaseName;
this.BasePage = BasePage;
this.IndexName = IndexName;
if (SqlFrom != null && SqlFrom != "")
{
IsView = true;
this.SqlFrom = "(" + SqlFrom + ") as " + TableName;
}
else
{
IsView = false;
this.SqlFrom = Dao.PoeColAspas(TableName);
}
// vamos encher lista de par??metros
PrepareSelectCountCommands();
this.SelectCommand = Select;
if (BasePage != null)
{
Item = BasePage.GetDataProviderItem(this);
}
}
public void DeleteItem(IGeneralDataProvider BaseInterface, string GridId, Hashtable GridData, Hashtable GridDataParameters)
{
GeneralDataProviderItem Item = null;
try
{
this.GridData = GridData;
this.GridDataParameters = GridDataParameters;
Item = BaseInterface.LoadItemFromGridControl(false, GridId);
if (Item.Errors.Count > 0)
{
((GeneralDataPage)BaseInterface).ShowErrors();
return;
}
DataProvider.DeleteItem(Item);
}
catch (Exception ex)
{
if (PageErrors == null)
{
PageErrors = new NameValueCollection();
}
PageErrors.Add("Error", ex.Message);
}
}
public void UpdateItem(IGeneralDataProvider BaseInterface, string GridId, Hashtable GridData, Hashtable GridDataParameters)
{
GeneralDataProviderItem Item = null;
try
{
this.GridData = GridData;
this.GridDataParameters = GridDataParameters;
DataProvider.SelectItem(false, 0, FormPositioningEnum.Current);
Item = BaseInterface.LoadItemFromGridControl(true, GridId);
if (Item.Errors.Count > 0)
{
PageErrors = new NameValueCollection();
PageErrors.Add(Item.Errors);
}
else
{
DataProvider.UpdateItem(Item);
}
}
catch (Exception ex)
{
if (PageErrors == null)
{
PageErrors = new NameValueCollection();
}
PageErrors.Add("Error", ex.Message);
}
}
public void LocateRecord(GeneralDataProviderItem Item, bool UpdateFromUI)
{
this.Item = Item;
if (BasePage != null)
{
BasePage.OnSelectedItem(this, Item, UpdateFromUI);
}
}
public void OnSelectedItem(GeneralDataProvider Provider, GeneralDataProviderItem Item, bool UpdateFromUI)
{
if (Provider == DataProvider)
{
InitializeAlias(Item);
FillAuxiliarTables();
ShowFormulas();
SetLinks();
}
}
public int DeleteItem(GeneralDataProviderItem Item)
{
if (IsView)
{
return(0);
}
this.Item = Item;
PrepareDeleteCommand();
SetParameters((TableCommand)Delete, ParameterType.Delete, true, this);
return(DoDeleteItem());
}
public void LocateRecordByRow(DataRow Row, bool CreateItem)
{
if (CreateItem)
{
Item = BasePage.GetDataProviderItem(this);
}
foreach (string Key in Item.Fields.Keys)
{
FieldBase Field = Item.Fields[Key];
string FieldName = Field.Name;
string format = (Item.Fields[Key].FieldType == FieldType.Date ? Select.DateFormat : "");
Item.Fields[Key].SetValue(Row[FieldName], format);
}
LocateRecord(Item);
}
public void LocateRecord(IGeneralDataProvider BaseInterface, string GridId, Hashtable GridData)
{
GeneralDataProviderItem Item = null;
try
{
this.GridData = GridData;
Item = BaseInterface.LoadItemFromGridControl(false, GridId);
if (Item.Errors.Count > 0)
{
((GeneralDataPage)BaseInterface).ShowErrors();
return;
}
DataProvider.LocateRecord(Item);
}
catch (Exception ex)
{
}
}
public int DeleteItem(GeneralDataProviderItem Item, bool isPreDef)
{
if (!isPreDef)
{
return(DeleteItem(Item));
}
if (IsView)
{
return(0);
}
this.Item = Item;
PrepareDeleteCommand();
foreach (string ParamKey in Parameters.Keys)
{
Parameters[ParamKey].Parameter.SetValue(Item[ParamKey].GetValue());
}
SetParameters((TableCommand)Delete, ParameterType.Delete);
return((int)ExecuteDelete());
}
/// <summary>
/// Efetua o select no banco de acordo com o filtro
/// </summary>
public DataSet SelectAllItems(GeneralDataProviderItem GeneralItem)
{
//Zerando todos os parametros antes de usar novamente pois estava pegando os parametros dos selects feitos anteriormente
Select.Parameters.Clear();
Select.Dao = Dao;
Count.Dao = Dao;
string selectList = "";
if (GeneralItem == null)
{
Item = BasePage.GetDataProviderItem(this);
}
else
{
Item = GeneralItem;
}
Select.FiltroWhere = FiltroAtual;
SetWhere(FiltroAtual);
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
selectList += Select.Dao.PoeColAspas(FieldName) + ",";
}
Select.SqlQuery.Replace("{SQL_Select}", selectList.TrimEnd(','));
Select.OrderBy = SelectCommand.OrderBy;
if (Select.OrderBy.Length == 0 && OrderBy != null)
{
Select.OrderBy = OrderBy;
}
if (Parameters.Count > 0)
{
SetParameters((TableCommand)Select, ParameterType.Select, false, this);
}
return(ExecuteSelect());
}
public DataTable SelectItems(int CurrentPageIndex, int PageSize, out int TotalRecords)
{
//Zerando todos os parametros antes de usar novamente pois estava pegando os parametros dos selects feitos anteriormente
Select.Parameters.Clear();
string selectList = "";
Item = BasePage.GetDataProviderItem(this);
//vamos igualar os objetos de DAO para a mesma transacao
Select.Dao = Dao;
Count.Dao = Dao;
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
selectList += Select.Dao.PoeColAspas(FieldName) + ",";
}
Select.SqlQuery.Replace("{SQL_Select}", selectList.TrimEnd(','));
SetParameters((TableCommand)Select, ParameterType.Select, false, this);
SetWhere(FiltroAtual);
if (OrderBy != null)
{
Select.OrderBy = OrderBy;
}
PageCount = ExecuteCount();
TotalRecords = PageCount;
if (CurrentPageIndex == -1)
{
CurrentPageIndex = (int)Math.Ceiling((decimal)TotalRecords / (decimal)PageSize) - 1;
}
PageNumber = CurrentPageIndex;
if (PageCount > 0)
{
return(ExecuteSelect(PageNumber * PageSize, PageSize).Tables[0]);
}
return(new DataTable());
}
/// <summary>
/// Efetua o select no banco de acordo com o filtro
/// </summary>
public List <GeneralDataProviderItem> SelectAllItems(bool retList)
{
DataSet ds = SelectAllItems();
List <GeneralDataProviderItem> List = new List <GeneralDataProviderItem>();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
GeneralDataProviderItem itm = BasePage.GetDataProviderItem(this);
foreach (string Key in Item.Fields.Keys)
{
FieldBase Field = itm.Fields[Key];
string FieldName = Field.Name;
string format = (itm.Fields[Key].FieldType == FieldType.Date ? Select.DateFormat : "");
itm.Fields[Key].SetValue(dr[FieldName], format);
}
List.Add(itm);
}
}
return(List);
}
/// <summary>
/// Efetua o select no banco de acordo com o filtro e numero da pagina
/// </summary>
/// <returns>Retorna item com o conteudo da celula que retornou no select</returns>
public int InsertItem(GeneralDataProviderItem Item)
{
if (IsView)
{
return(0);
}
this.Item = Item;
this.Item.IsNew = true;
string fieldsList = "";
string valuesList = "";
Insert = new TableCommand("INSERT INTO " + Dao.PoeColAspas(TableName) + "{Fields}{Values}", new string[0], Dao);
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
string format = (Field.FieldType == FieldType.Date ? Insert.DateFormat : "");
if (Field.FieldType == FieldType.Boolean)
{
format = Insert.BoolFormat;
}
if (!Field.IsEmpty && Field.IsUpdatable && (Field is BinaryField || Insert.Dao.ToSql(Field.GetFormattedValue(format), Field.FieldType, Field.AllowEmpty) != "NULL"))
{
if (!(Field is BinaryField) || ((BinaryField)Field).BinaryValue != null)
{
fieldsList += Dao.PoeColAspas(FieldName) + ",";
}
if (Field is BinaryField)
{
if (((BinaryField)Field).BinaryValue != null)
{
valuesList += Dao.ParameterDelimiter + Utility.PoeUnderLines(FieldName).ToUpper() + ",";
Insert.Images.Add(Utility.PoeUnderLines(FieldName).ToUpper(), (byte[])((BinaryField)Field).BinaryValue);
}
}
else
{
valuesList += Insert.Dao.ToSql(Field.GetFormattedValue(format), Field.FieldType, Field.AllowEmpty) + ",";
}
}
}
if (fieldsList.Length > 0)
{
Insert.SqlQuery.Replace("{Fields}", "(" + fieldsList.TrimEnd(',') + ")");
Insert.SqlQuery.Replace("{Values}", " VALUES (" + valuesList.TrimEnd(',') + ")");
}
else
{
Insert.SqlQuery.Replace("{Fields}{Values}", " DEFAULT VALUES");
}
object result = 0;
bool InTransaction = false;
try
{
Dao.OpenConnection();
if (Dao.Transaction == null)
{
Dao.BeginTrans();
InTransaction = true;
}
result = ExecuteInsert();
int i = 0;
if (InTransaction)
{
BasePage.OnCommiting();
Dao.CommitTrans();
}
}
catch (Exception e)
{
if (InTransaction)
{
BasePage.OnRollbacking();
Dao.RollBack();
}
throw (e);
}
finally
{
Dao.CloseConnection();
}
return((int)result);
}
public void LocateRecord(GeneralDataProviderItem Item)
{
LocateRecord(Item, true);
}
public virtual string GetMediaQuery(GeneralDataProviderItem Item, string Column)
{
return("");
}
public virtual bool Validate(GeneralDataProviderItem ProviderItem)
{
return(true);
}
public virtual void InitializeAlias(GeneralDataProviderItem Item)
{
}
public void FindRecord(Dictionary <string, object> Params)
{
this.IndexName = IndexName;
Item = BasePage.GetDataProviderItem(this);
string selectList = "";
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
selectList += Select.Dao.PoeColAspas(FieldName) + ",";
}
Select.SqlQuery.Replace("{SQL_Select}", selectList.TrimEnd(','));
int ParamIndex = 0;
this.Parameters.Clear();
List <string> ParamsKeys = new List <string>();
Dictionary <string, FieldBase> ItemField = CreateItemFields();
foreach (string ParamKey in Params.Keys)
{
if (ParamsKeys.Count > 0)
{
ParamsKeys.Add("AND");
}
CreateParameter(ParamKey);
string alias = GetFieldAlias(ItemField, ParamKey);
this.Parameters[alias].Parameter.SetValue(Params[ParamKey]);
string paramformat = "";
if (this.Parameters[alias].Parameter is DateParameter)
{
paramformat = Select.DateFormat;
}
((TableCommand)Select).AddParameter(alias, this.Parameters[alias].Parameter, paramformat, Dao.PoeColAspas(this.Parameters[alias].Name), Condition.Equal, false);
ParamIndex++;
ParamsKeys.Add(alias);
}
((TableCommand)Select).WhereTemplate = ParamsKeys.ToArray();
PageCount = ExecuteCount();
PageNumber = 1;
if (PageNumber < 1)
{
PageNumber = 1;
}
if (PageNumber > PageCount)
{
this.PageNumber = PageCount;
}
DataSet ds = null;
if (PageCount > 0)
{
ds = ExecuteSelect(0, 1);
}
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
LocateRecordByRow(ds.Tables[0].Rows[0], false);
}
else
{
PageNumber = 0;
}
}
public abstract void SetOldParameters(GeneralDataProviderItem Item);
public void FindRecord(string IndexName, bool ShouldLocateRecord, params object[] Parameters)
{
this.IndexName = IndexName;
Item = BasePage.GetDataProviderItem(this);
string selectList = "";
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
selectList += Select.Dao.PoeColAspas(FieldName) + ",";
}
Select.SqlQuery.Replace("{SQL_Select}", selectList.TrimEnd(','));
RaiseCreatingUniqueParameters(this);
int ParamIndex = 0;
List <string> ParamsKeys = new List <string>();
foreach (string ParamKey in this.Parameters.Keys)
{
if (ParamsKeys.Count > 0)
{
ParamsKeys.Add("AND");
}
this.Parameters[ParamKey].Parameter.SetValue(Parameters[ParamIndex]);
string paramformat = "";
if (this.Parameters[ParamKey].Parameter is DateParameter)
{
paramformat = Select.DateFormat;
}
((TableCommand)Select).AddParameter(ParamKey, this.Parameters[ParamKey].Parameter, paramformat, Dao.PoeColAspas(this.Parameters[ParamKey].Name), Condition.Equal, false);
ParamIndex++;
ParamsKeys.Add(ParamKey);
}
((TableCommand)Select).WhereTemplate = ParamsKeys.ToArray();
PageCount = ExecuteCount();
PageNumber = 1;
if (PageNumber < 1)
{
PageNumber = 1;
}
if (PageNumber > PageCount)
{
this.PageNumber = PageCount;
}
DataSet ds = null;
if (PageCount > 0)
{
ds = ExecuteSelect(0, 1);
}
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
foreach (string Key in Item.Fields.Keys)
{
FieldBase Field = Item.Fields[Key];
string FieldName = Field.Name;
string format = (Item.Fields[Key].FieldType == FieldType.Date ? Select.DateFormat : "");
Item.Fields[Key].SetValue(dr[FieldName], format);
}
RaiseCreatingParameters(this);
if (ShouldLocateRecord)
{
LocateRecord(Item);
}
}
else
{
PageNumber = 0;
}
}
/// <summary>
/// Efetua update de um registro no banco
/// </summary>
/// <returns>Retorna se o update funcionou ou nao</returns>
protected int DoUpdateItem()
{
if (IsView)
{
return(0);
}
object result = 0;
bool InTransaction = false;
try
{
Dao.OpenConnection();
if (Dao.Transaction == null)
{
Dao.BeginTrans();
InTransaction = true;
}
GeneralDataProviderItem NewItem = Item;
bool ShouldReposition = false;
if (ShouldReposition)
{
// restaura o item que esta atualizado com valores da tela
Item = NewItem;
PageProvider.PositionParentsProvider();
LocateRecord(Item);
}
// prepara para o update
string valuesList = "";
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
string format = (Field.FieldType == FieldType.Date ? Update.DateFormat : "");
if (Field.FieldType == FieldType.Boolean)
{
format = Update.BoolFormat;
}
if (Field.IsUpdatable && (Field.SettedValue))
{
if (Field is BinaryField)
{
if (((BinaryField)Field).BinaryValue != null && !String.IsNullOrEmpty(((BinaryField)Field).BinaryValue.ToString()))
{
valuesList += Dao.PoeColAspas(FieldName) + "=" + Dao.ParameterDelimiter + Utility.PoeUnderLines(FieldName).ToUpper() + ",";
Update.Images.Add(Utility.PoeUnderLines(FieldName).ToUpper(), (byte[])((BinaryField)Field).BinaryValue);
}
else
{
valuesList += Dao.PoeColAspas(FieldName) + "= null,";
}
}
else
{
if (Field.AllowEmpty && (Field.FieldType == FieldType.Text || Field.FieldType == FieldType.Decimal || Field.FieldType == FieldType.Integer || Field.FieldType == FieldType.Long) && (Field.GetValue() == null || Field.GetValue() is DBNull))
{
if (Field.FieldType == FieldType.Text)
{
valuesList += Dao.PoeColAspas(FieldName) + "= '',";
}
else
{
valuesList += Dao.PoeColAspas(FieldName) + "= 0,";
}
}
else
{
valuesList += Dao.PoeColAspas(FieldName) + "=" + Update.Dao.ToSql(Field.GetFormattedValue(format), Field.FieldType) + ",";
}
}
}
}
if (!CheckParameters())
{
throw new Exception("Erro em Parametros Indice nao definido Tabela, Pagina ou Grid");
}
Update.SqlQuery.Replace("{Values}", valuesList.TrimEnd(','));
result = ExecuteUpdate();
if (PageProvider != null && PageProvider.MainProvider != null)
{
PageProvider.MainProvider.SetOldParameters(NewItem);
}
if (InTransaction)
{
BasePage.OnCommiting();
Dao.CommitTrans();
}
}
catch (Exception e)
{
if (InTransaction)
{
BasePage.OnRollbacking();
Dao.RollBack();
}
throw (e);
}
finally
{
Dao.CloseConnection();
}
return((int)result);
}
/// <summary>
/// Valida se todos os campos foram preenchidos corretamente
/// </summary>
/// <param name="provider">Provider que vai ser usado para carregar os itens da p?gina</param>
public override bool Validate(GeneralDataProviderItem ProviderItem)
{
return(ProviderItem.Errors.Count == 0);
}
public GeneralDataProviderItem SelectItem(bool ShouldLocateRecord, int CurrentPageNumber, FormPositioningEnum Positioning, bool UpdateFromUI)
{
//Zerando todos os parametros antes de usar novamente pois estava pegando os parametros dos selects feitos anteriormente
Select.Parameters.Clear();
string selectList = "";
Item = BasePage.GetDataProviderItem(this);
//vamos igualar os objetos de DAO para a mesma transacao
Select.Dao = Dao;
Count.Dao = Dao;
foreach (FieldBase Field in Item.Fields.Values)
{
string FieldName = Field.Name;
selectList += Select.Dao.PoeColAspas(FieldName) + ",";
}
Select.SqlQuery.Replace("{SQL_Select}", selectList.TrimEnd(','));
SetParameters((TableCommand)Select, ParameterType.Select, (Positioning == FormPositioningEnum.Current), this);
SetWhere(FiltroAtual);
PageCount = ExecuteCount();
switch (Positioning)
{
case FormPositioningEnum.None:
PageNumber = CurrentPageNumber;
break;
case FormPositioningEnum.Current:
PageNumber = CurrentPageNumber;
break;
case FormPositioningEnum.First:
PageNumber = 1;
break;
case FormPositioningEnum.Previous:
PageNumber = CurrentPageNumber - 1;
break;
case FormPositioningEnum.Next:
PageNumber = CurrentPageNumber + 1;
break;
case FormPositioningEnum.Last:
PageNumber = PageCount;
break;
default:
PageNumber = CurrentPageNumber;
break;
}
if (PageNumber < 1)
{
PageNumber = 1;
}
if (PageNumber > PageCount)
{
this.PageNumber = PageCount;
}
DataSet ds = null;
if (PageCount > 0)
{
if (Positioning == FormPositioningEnum.Current)
{
ds = ExecuteSelect(0, 1);
}
else
{
ds = ExecuteSelect(PageNumber - 1, 1);
}
}
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
foreach (string Key in Item.Fields.Keys)
{
FieldBase Field = Item.Fields[Key];
string FieldName = Field.Name;
string format = (Item.Fields[Key].FieldType == FieldType.Date ? Select.DateFormat : "");
Item.Fields[Key].SetValue(dr[FieldName], format);
}
if (ShouldLocateRecord)
{
LocateRecord(Item, UpdateFromUI);
}
return(Item);
}
else
{
PageNumber = 0;
return(null);
}
}
