public List <Routines> GetListRoutines(string cadenaconexion)
{
List <Routines> RoutineList = new List <Routines>();
DALSQLNative bd = new DALSQLNative(cadenaconexion);
StringBuilder _SQL = new StringBuilder();
_SQL.Append(" select ROUTINE_TYPE, ");
_SQL.Append(" ROUTINE_SCHEMA+'.'+ROUTINE_NAME ROUTINE_NAME, ");
_SQL.Append(" ROUTINE_DEFINITION ");
_SQL.Append(" from information_schema.routines");
try
{
bd.conectar();
using (SqlCommand _comando = bd.crearComandoText(_SQL.ToString()))
{
using (System.Data.SqlClient.SqlDataReader reader = _comando.ExecuteReader())
{
if (reader.HasRows)
{
Object[] RegistOfSelect = new Object[reader.FieldCount];
int IdxROUTINE_TYPE = reader.GetOrdinal("ROUTINE_TYPE");
int IdxROUTINE_NAME = reader.GetOrdinal("ROUTINE_NAME");
int IdxROUTINE_DEFINITION = reader.GetOrdinal("ROUTINE_DEFINITION");
while (reader.Read())
{
Routines _Routines = new Routines();
reader.GetValues(RegistOfSelect);
if (!(RegistOfSelect[IdxROUTINE_TYPE] is DBNull))
{
_Routines.Type = RegistOfSelect[IdxROUTINE_TYPE].ToString();
}
if (!(RegistOfSelect[IdxROUTINE_NAME] is DBNull))
{
_Routines.Name = RegistOfSelect[IdxROUTINE_NAME].ToString();
}
if (!(RegistOfSelect[IdxROUTINE_DEFINITION] is DBNull))
{
_Routines.Definition = RegistOfSelect[IdxROUTINE_DEFINITION].ToString();
}
RoutineList.Add(_Routines);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
bd.Desconectar();
}
return(RoutineList);
}
public List <DefitionItem> GetDefinitionObj(string cadenaconexion, Routines routine)
{
List <DefitionItem> ListDefinition = new List <DefitionItem>();
DALSQLNative bd = new DALSQLNative(cadenaconexion);
StringBuilder _SQL = new StringBuilder();
_SQL.Append(" EXEC sp_helptext '" + routine.Name + "'; ");
try
{
bd.conectar();
using (SqlCommand _comando = bd.crearComandoText(_SQL.ToString()))
{
using (System.Data.SqlClient.SqlDataReader reader = _comando.ExecuteReader())
{
if (reader.HasRows)
{
Object[] RegistOfSelect = new Object[reader.FieldCount];
int IdxText = reader.GetOrdinal("Text");
while (reader.Read())
{
DefitionItem _Definition = new DefitionItem();
reader.GetValues(RegistOfSelect);
if (!(RegistOfSelect[IdxText] is DBNull))
{
//_Definition.Text = RegistOfSelect[IdxText].ToString().ToUpper().Replace("CREATE PROC", "ALTER PROC");
_Definition.Text = RegistOfSelect[IdxText].ToString();
}
ListDefinition.Add(_Definition);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
bd.Desconectar();
}
return(ListDefinition);
}
public long ExecuteCommandSQLText(string prmSQLText)
{
DALSQLNative bd = new DALSQLNative();
long Result = 0;
try
{
bd.conectar();
bd.crearComandoText(prmSQLText);
bd.ejecutarComandoNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
bd.Desconectar();
}
return(Result);
}