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();
}
}
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();
}
}
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 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 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); }
}
/// <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); }
}
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>
/// 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); }
}
/// <summary>
/// Abre a conexão com o banco de dados
/// </summary>
protected internal void OpenConnection(IType iType)
{
string conectionString = ConnectionString;
if (iType != null)
{
string iTypeConnection = getConectionStringFromIType(iType);
if (iTypeConnection != null)
{
conectionString = iTypeConnection;
}
}
Cn = DataBaseGeneric.CreateConnection(BaseType, conectionString);
Cn.Open();
Cmd.Connection = Cn;
}
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); }
}
/// <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 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); }
}
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>
/// Abre a conexão com o banco de dados
/// </summary>
protected internal void OpenConnection()
{
Cn = DataBaseGeneric.CreateConnection(BaseType, ConnectionString);
Cn.Open();
Cmd.Connection = Cn;
}
/// <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); }
}
