public IType BuscarPorUsuario(IType iType, int idUsuario, int idCampanha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_BUSCA_HIERARQUIA_POR_CREDENCIAL"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@idcredencial"; paran.Value = idUsuario; Cmd.Parameters.Add(paran); DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idcampanha"; paran1.Value = idCampanha; Cmd.Parameters.Add(paran1); SetObjectToGet(iType); return(iType); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public IType BuscarHomePagina(IType iType, int idCliente) { Cn = DataBaseGeneric.CreateConnection(BaseType, iType.InstanceConectionString); Cn.Open(); try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_BuscarHomePagina"; DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idCliente"; paran2.Value = idCliente; Cmd.Parameters.Add(paran2); OpenConnection(iType); DbDataReader dr = Cmd.ExecuteReader(); if (dr.Read()) { return(SetObject(dr, (IType)Activator.CreateInstance(iType.GetType()))); } return(null); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public LIType ListaByHierarquia(IType iType, int idPessoa, int idCampanha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_Ambev_Lista_RelacionaPessoa"; DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idPessoa"; paran1.Value = idPessoa; Cmd.Parameters.Add(paran1); DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idcampanha"; paran2.Value = idCampanha; Cmd.Parameters.Add(paran2); return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
private void CreateForeignKey(IType iType, PropertyInfo fk) { var className = fk.Name.Substring(2, fk.Name.Length - 2); var assemblyQualifiedName = "Didox.Business." + className + ", Business"; var typeForeignKeyClass = Type.GetType(assemblyQualifiedName); var iTypeForeignKeyClass = (IType)Activator.CreateInstance(typeForeignKeyClass); CreatePrimaryKeyIfNotExist(iTypeForeignKeyClass); var pk = GetPk(iTypeForeignKeyClass); string tableName = CType.GetTableName(iType); string tableNameForeignKey = CType.GetTableName(iTypeForeignKeyClass); string propertyName = CType.GetPropertyName(pk); try { DeleteForeignKey(iType, pk, tableName, tableNameForeignKey); Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; var sqlCommand = "ALTER TABLE " + tableName + " ADD CONSTRAINT fk_" + tableName + "_" + tableNameForeignKey + " FOREIGN KEY (" + propertyName + ") REFERENCES " + tableNameForeignKey + "(" + propertyName + ")"; Cmd.CommandText = sqlCommand; OpenConnection(iType); Cmd.ExecuteNonQuery(); } catch (DbException dbError) { } catch (Exception err) { throw err; } finally { CloseConnection(iType); } }
public LIType BuscarPorHierarquiaENivel(IType iType, int idHierarquia, int idSubNivel, int idCampanha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_Ambev_Lista_Nivel_Hierarquia_dsPessoa"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@nivel"; paran.Value = idSubNivel; Cmd.Parameters.Add(paran); DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idHierarquiaPai"; paran1.Value = idHierarquia; Cmd.Parameters.Add(paran1); DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idcampanha"; paran2.Value = idCampanha; Cmd.Parameters.Add(paran2); return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public void DeletePai(IType iType, int idPessoa, int idPessoaPai) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_DeleteEstruturaPessoaPai"; DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idPessoaPai"; paran1.Value = idPessoaPai; Cmd.Parameters.Add(paran1); DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idPessoa"; paran2.Value = idPessoa; Cmd.Parameters.Add(paran2); OpenConnectionTrans(iType); Cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public IType BuscarHomePaginaRestrito(IType iType, int idUsuario, int idCampanha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_BUSCA_HIERARQUIA_POR_CREDENCIAL"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@idcredencial"; paran.Value = idUsuario; Cmd.Parameters.Add(paran); DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idcampanha"; paran1.Value = idCampanha; Cmd.Parameters.Add(paran1); OpenConnection(iType); DbDataReader dr = Cmd.ExecuteReader(); if (dr.Read()) { return(SetObject(dr, (IType)Activator.CreateInstance(iType.GetType()))); } return(null); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public bool EFilhoDaPessoaCargo(IType iType, int idPessoaPai, int idPessoa) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_PessoaEFilhoDaPessoaCargo"; DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idPessoaPai"; paran1.Value = idPessoaPai; Cmd.Parameters.Add(paran1); DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idPessoa"; paran2.Value = idPessoa; Cmd.Parameters.Add(paran2); SetObjectToGet(iType); return(iType.Id != null); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public IType GetPessoaCampanha(IType iType, int IDUsuario, int idCampanha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_GetPessoaPorCampanha"; DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idCampanha"; paran1.Value = idCampanha; Cmd.Parameters.Add(paran1); DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@idCredencial"; paran.Value = IDUsuario; Cmd.Parameters.Add(paran); SetObjectToGet(iType); return(iType); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
/// <summary> /// Busca os dados da base através dos parâmetros por SQL conditions /// </summary> public virtual LIType FindBySql(ref IType iType, string sql) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; Cmd.CommandText = sql; return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
public LIType BuscarPaginas(IType iType, string dsPagina, bool restrito, int?idCliente, int?idPrograma, int?idCampanha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_BuscarPaginas"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@dsPagina"; paran.Value = dsPagina; Cmd.Parameters.Add(paran); DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idCliente"; paran1.Value = (int)idCliente; Cmd.Parameters.Add(paran1); if (restrito) { DbParameter paran3 = Cmd.CreateParameter(); paran3.ParameterName = "@restrito"; paran3.Value = restrito; Cmd.Parameters.Add(paran3); } if (idPrograma != null) { DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idPrograma"; paran2.Value = (int)idPrograma; Cmd.Parameters.Add(paran2); } if (idCampanha != null) { DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idCampanha"; paran2.Value = (int)idCampanha; Cmd.Parameters.Add(paran2); } return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
/// <summary> /// Executa codigo SQL /// </summary> public virtual void ExecSql(ref IType iType, string sql) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; Cmd.CommandText = sql; OpenConnectionTrans(iType); Cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
/// <summary> /// Busca os dados da base através dos parâmetros /// </summary> public virtual LIType Find(ref IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_" + iType.GetType().Name + "_Buscar"; SetParanGet(iType); return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
public IType BuscarPaginaRestrita(IType iType, string slugPagina, int idCliente, int?idPrograma, int?idCampanha, int?idUsuario) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_BuscarPaginaRestrito"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@idCliente"; paran.Value = idCliente; Cmd.Parameters.Add(paran); DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idUsuario"; paran1.Value = (int)idUsuario; Cmd.Parameters.Add(paran1); DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@slugPagina"; paran2.Value = slugPagina; Cmd.Parameters.Add(paran2); if (idPrograma != null) { DbParameter paran3 = Cmd.CreateParameter(); paran3.ParameterName = "@idPrograma"; paran3.Value = (int)idPrograma; Cmd.Parameters.Add(paran3); } if (idCampanha != null) { DbParameter paran3 = Cmd.CreateParameter(); paran3.ParameterName = "@idCampanha"; paran3.Value = (int)idCampanha; Cmd.Parameters.Add(paran3); } SetObjectToGet(iType); return(iType); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
/// <summary> /// Exclui os dados na base através dos parâmetros /// </summary> public virtual void Delete(ref IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_" + iType.GetType().Name + "_Excluir"; SetParanDelete(iType); OpenConnectionTrans(iType); Cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
/// <summary> /// Busca os dados da base através dos parâmetros por condição /// </summary> public virtual LIType FindByConditions(ref IType iType, string conditions) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_" + iType.GetType().Name + "_BuscarConditions"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@conditions"; paran.Value = conditions; Cmd.Parameters.Add(paran); return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
/// <summary> /// Cria tabela no banco de dados /// </summary> public void CreateTable(IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; string tableName = CType.GetTableName(iType); string sqlCommand = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[" + tableName + "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)" + "DROP TABLE " + tableName; OpenConnection(iType); Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); string pkField = null; List <string> sqlCommandParam = new List <string>(); sqlCommand = "CREATE TABLE " + tableName + "("; foreach (PropertyInfo pi in iType.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic)) { PropertyAttribute[] pAttProperty = (PropertyAttribute[])pi.GetCustomAttributes(typeof(PropertyAttribute), false); if (pAttProperty != null && pAttProperty.Length > 0) { if (pAttProperty[0].IsField) { addSqlParam(ref sqlCommandParam, pi, false, true, pAttProperty[0].Size); } else if (pAttProperty[0].IsPk) { pkField = CType.GetPropertyName(pi); sqlCommand += CType.GetPropertyName(pi) + " " + GetIntDataType(pi) + " IDENTITY(1,1) NOT NULL, "; } } } sqlCommand += string.Join(", ", sqlCommandParam.ToArray()); sqlCommand = sqlCommand.Replace("@", ""); sqlCommand += ") ON [PRIMARY]"; OpenConnection(iType); Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
public LIType GetPrimeiroNoCargo(IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_GetPrimeiroNoCargo"; return(MakeListToGet(iType)); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public int BuscaQuantidadeAcesso(IType iType, int idCliente, int?idPrograma, int?idCampanha, int?idUsuario) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_BuscaQuantidadeAcesso"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@idCliente"; paran.Value = idCliente; Cmd.Parameters.Add(paran); DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idUsuario"; paran1.Value = (int)idUsuario; Cmd.Parameters.Add(paran1); if (idPrograma != null) { DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idPrograma"; paran2.Value = (int)idPrograma; Cmd.Parameters.Add(paran2); } if (idCampanha != null) { DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@idCampanha"; paran2.Value = (int)idCampanha; Cmd.Parameters.Add(paran2); } OpenConnection(iType); return(Convert.ToInt32(Cmd.ExecuteScalar())); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
public IType Validate(IType iType, int idCampanha, string chave, string senha) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; DbParameter paran1 = Cmd.CreateParameter(); paran1.ParameterName = "@idCampanha"; paran1.Value = idCampanha; Cmd.Parameters.Add(paran1); DbParameter paran2 = Cmd.CreateParameter(); paran2.ParameterName = "@chave"; paran2.Value = chave; Cmd.Parameters.Add(paran2); if (senha != null) { Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_SYS_ValidateChaveAtivacao"; DbParameter paran3 = Cmd.CreateParameter(); paran3.ParameterName = "@senha"; paran3.Value = senha; Cmd.Parameters.Add(paran3); } else { Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "Sp_SYS_ValidateChaveAtivacaoSemSenha"; } SetObjectToGet(iType); return(iType); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
/// <summary> /// Busca os dados da base e seta a referencia /// </summary> public virtual IType Get(ref IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "usp_Get" + iType.GetType().Name; SetParanGet(iType); OpenConnection(); LIType l = new LIType(); DbDataReader dr = Cmd.ExecuteReader(); if (dr != null && dr.Read()) { SetObject(dr, iType); } return(iType); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
private void DeleteForeignKey(IType iType, PropertyInfo pk, string tableName, string tableNameForeignKey) { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; if (pk == null) { return; } string sqlCommand = "ALTER TABLE " + tableName + " DROP CONSTRAINT fk_" + tableName + "_" + tableNameForeignKey + ""; OpenConnection(iType); Cmd.CommandText = sqlCommand; try { Cmd.ExecuteNonQuery(); } catch (DbException dbError) { } catch (Exception err) { throw err; } finally { CloseConnection(iType); } }
/// <summary> /// Salva os dados na base através de parâmetros /// </summary> public virtual void Save(ref IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_SYS_" + iType.GetType().Name + "_Salvar"; PropertyInfo cPk = SetParanSave(iType); OpenConnectionTrans(iType); if (cPk != null && cPk.GetValue(iType, null) == null) { cPk.SetValue(iType, iType.PrepareValueProperty(Cmd.ExecuteScalar(), cPk.PropertyType), null); } else { Cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }
private void CreatePrimaryKeyIfNotExist(IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; string tableName = CType.GetTableName(iType); var pk = GetPk(iType); if (pk == null) { return; } string sqlCommand = "ALTER TABLE " + tableName + " ADD CONSTRAINT pk_" + tableName + "_" + CType.GetPropertyName(pk) + " PRIMARY KEY (" + CType.GetPropertyName(pk) + ")"; OpenConnection(iType); Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); } catch { } finally { CloseConnection(iType); } }
public IType GetCargoPorPessoa(IType iType, int idPessoa) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.Connection = Cn; Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "sp_GetCargoPorPessoa"; DbParameter paran = Cmd.CreateParameter(); paran.ParameterName = "@idPessoa"; paran.Value = idPessoa; Cmd.Parameters.Add(paran); SetObjectToGet(iType); return(iType); } catch (Exception ex) { throw ex; } finally { Cn.Close(); Cn.Dispose(); } }
/// <summary> /// Cria procedures no banco de dados /// </summary> public virtual void CreateProcs(IType iType) { try { Cmd = DataBaseGeneric.CreateCommand(BaseType); Cmd.CommandType = CommandType.Text; OpenConnection(iType); string className = iType.GetType().Name; string sqlCommand = " IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_SYS_" + className + "_Excluir]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)" + "DROP PROCEDURE [dbo].[sp_SYS_" + className + "_Excluir] "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); sqlCommand = " IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_SYS_" + className + "_Buscar]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)" + "DROP PROCEDURE [dbo].[sp_SYS_" + className + "_Buscar] "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); sqlCommand = " IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_SYS_" + className + "_BuscarConditions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)" + "DROP PROCEDURE [dbo].[sp_SYS_" + className + "_BuscarConditions] "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); sqlCommand = " IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_SYS_" + className + "_Salvar]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)" + "DROP PROCEDURE [dbo].[sp_SYS_" + className + "_Salvar] "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); string pkField = null; List <string> sqlCommandWhereDel = new List <string>(); List <string> sqlCommandParamDel = new List <string>(); List <ParamGetSql> sqlCommandWhereGet = new List <ParamGetSql>(); List <string> sqlCommandParamGet = new List <string>(); List <string> sqlCommandSave = new List <string>(); List <string> sqlCommandParamSave = new List <string>(); List <string> sqlCommandOrderGet = new List <string>(); foreach (PropertyInfo pi in iType.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic)) { PropertyAttribute[] pAttProperty = (PropertyAttribute[])pi.GetCustomAttributes(typeof(PropertyAttribute), false); if (pAttProperty != null && pAttProperty.Length > 0) { if (pAttProperty[0].IsField) { OperationsAttribute[] pOpeProperty = (OperationsAttribute[])pi.GetCustomAttributes(typeof(OperationsAttribute), false); if (pOpeProperty != null && pOpeProperty.Length > 0) { if (pOpeProperty[0].UseDelete) { addSqlParam(ref sqlCommandParamDel, pi, true, false, pAttProperty[0].Size); sqlCommandWhereDel.Add(CType.GetPropertyName(pi)); } if (pOpeProperty[0].UseSave) { addSqlParam(ref sqlCommandParamSave, pi, true, false, pAttProperty[0].Size); sqlCommandSave.Add(CType.GetPropertyName(pi)); } if (pOpeProperty[0].UseGet) { addSqlParam(ref sqlCommandParamGet, pi, true, false, pAttProperty[0].Size); sqlCommandWhereGet.Add( new ParamGetSql(CType.GetPropertyName(pi), (pi.PropertyType.Name == "String" && !pAttProperty[0].DontUseLikeWithStrings && !pAttProperty[0].IsText))); } if (pAttProperty[0].IsOrderField) { sqlCommandOrderGet.Insert(pAttProperty[0].OrderOfPriority, CType.GetPropertyName(pi) + " " + (pAttProperty[0].DescOrder ? "desc" : "asc")); } } } else if (pAttProperty[0].IsPk) { pkField = CType.GetPropertyName(pi); string dataType = GetIntDataType(pi); sqlCommandParamDel.Add("@" + pkField + " " + dataType + " = null"); sqlCommandWhereDel.Add(CType.GetPropertyName(pi)); sqlCommandParamGet.Add("@" + pkField + " " + dataType + " = null"); sqlCommandWhereGet.Add(new ParamGetSql(CType.GetPropertyName(pi), false)); sqlCommandParamSave.Add("@" + pkField + " " + dataType + " = null"); } } } sqlCommand = "CREATE PROCEDURE sp_SYS_" + className + "_Excluir "; sqlCommand += string.Join(", ", sqlCommandParamDel.ToArray()) + " "; sqlCommand += "AS BEGIN "; sqlCommand += "DELETE FROM " + CType.GetTableName(iType) + " "; sqlCommand += "WHERE " + pkField + " IS NOT NULL AND "; List <string> sqlCommandSetDel = new List <string>(); sqlCommandWhereDel.ForEach(c => sqlCommandSetDel.Add("( @" + c + " IS NULL OR " + c + " = @" + c + " )")); sqlCommand += string.Join("AND ", sqlCommandSetDel.ToArray()) + " "; sqlCommand += "END "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); sqlCommand = "CREATE PROCEDURE sp_SYS_" + className + "_Salvar "; sqlCommand += string.Join(", ", sqlCommandParamSave.ToArray()) + " "; sqlCommand += "AS BEGIN "; sqlCommand += "IF(@" + pkField + " is null) "; sqlCommand += "BEGIN "; sqlCommand += "INSERT INTO " + CType.GetTableName(iType) + " "; List <string> fieldsSave = new List <string>(); sqlCommandSave.ForEach(c => fieldsSave.Add("@" + c)); sqlCommand += "( " + string.Join(", ", sqlCommandSave.ToArray()) + ", DataCriacao, DataAtualizacao ) Values ( " + string.Join(", ", fieldsSave.ToArray()) + ", getdate(), getdate() ) SELECT @@IDENTITY "; sqlCommand += "END "; sqlCommand += "ELSE BEGIN "; sqlCommand += "UPDATE " + CType.GetTableName(iType) + " SET "; List <string> sqlCommandSetSave = new List <string>(); sqlCommandSave.ForEach(cSave => sqlCommandSetSave.Add(cSave + " = @" + cSave)); sqlCommand += " " + string.Join(", ", sqlCommandSetSave.ToArray()) + ", DataAtualizacao = getdate() "; sqlCommand += "WHERE " + pkField + " = @" + pkField + " "; sqlCommand += " END "; sqlCommand += " END "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); sqlCommand = "CREATE PROCEDURE sp_SYS_" + className + "_Buscar "; sqlCommand += string.Join(", ", sqlCommandParamGet.ToArray()) + " "; sqlCommand += "AS BEGIN "; sqlCommand += "SELECT * FROM " + CType.GetTableName(iType) + " "; sqlCommand += "WHERE " + pkField + " IS NOT NULL AND "; // (DataExclusao >= getDate() OR DataExclusao IS NULL) AND List <string> sqlCommandSetGet = new List <string>(); sqlCommandWhereGet.ForEach(c => sqlCommandSetGet.Add( (c.UseLike ? "( @" + c.SqlParam + " IS NULL OR " + c.SqlParam + " like '%' + @" + c.SqlParam + " + '%' ) " : "( @" + c.SqlParam + " IS NULL OR " + c.SqlParam + " = @" + c.SqlParam + " ) ") )); sqlCommand += string.Join("AND ", sqlCommandSetGet.ToArray()) + " "; if (sqlCommandOrderGet.Count > 0) { sqlCommand += " order by " + string.Join(", ", sqlCommandOrderGet.ToArray()) + " "; } sqlCommand += "END "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); sqlCommand = "CREATE PROCEDURE sp_SYS_" + className + "_BuscarConditions "; sqlCommand += "@conditions varchar(2000)"; sqlCommand += "AS BEGIN "; sqlCommand += "DECLARE @SQL varchar(3000) "; sqlCommand += "set @SQL = 'SELECT * FROM " + CType.GetTableName(iType) + " "; sqlCommand += "WHERE " + pkField + " IS NOT NULL AND ' + @conditions + ' ' "; if (sqlCommandOrderGet.Count > 0) { sqlCommand += "set @SQL += ' order by " + string.Join(", ", sqlCommandOrderGet.ToArray()) + " ' "; } sqlCommand += "EXEC(@SQL) "; sqlCommand += "END "; Cmd.CommandText = sqlCommand; Cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { CloseConnection(iType); } }