public void TestMethod02() // SP de valor nulo
{
string query = null;
String ConnectionStr = ConfigurationManager.AppSettings["ConnStr"];
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
String Code = ErrorsAndExceptionsCatalog._602_Code;
String Message = ErrorsAndExceptionsCatalog._602_StoredProdecureNotFound;
DataAccessException resultEX = new DataAccessException();
try
{
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.StoredProcedure, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public void TestMethod03() // query mal estructurado
{
string query = "delect count(*) from Albums";
String ConnectionStr = ConfigurationManager.AppSettings["ConnStr"];
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
String Code = ErrorsAndExceptionsCatalog._603_Code;
String Message = ErrorsAndExceptionsCatalog._603_InvalidQuery;
DataAccessException resultEX = new DataAccessException();
try
{
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.Text, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public void TestMethod013() // error no controlado
{
string query = "Select count(*) from Albums";
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
String ConnectionStr = "data source =./; initial catalog = MvcMusicStore; integrated security = True; MultipleActiveResultSets = True;";
String Code = ErrorsAndExceptionsCatalog._613_Code;
String Message = ErrorsAndExceptionsCatalog._613_ErrorNotHandled;
DataAccessException resultEX = new DataAccessException();
try
{
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.Text, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public void TestMethod011() // no se pudo ejecutar la sentencia
{
string query = "Select count(*) from Albums";
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
String ConnectionStr = "data source=./;initial catalog=MvcMusicStore;integrated security=True;MultipleActiveResultSets=True;";
String Code = ErrorsAndExceptionsCatalog._611_Code;
String Message = ErrorsAndExceptionsCatalog._611_InvalidSentenceExecution;
DataAccessException resultEX = new DataAccessException();
try
{
bool result = DataAccessADO.ExecuteNonQuery(query, CommandType.Text, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public void TestMethod012() // conexion no establecida
{
string query = "Select count(*) from Albums";
String ConnectionStr = "sdfnksdf";
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
String Code = ErrorsAndExceptionsCatalog._612_Code;
String Message = ErrorsAndExceptionsCatalog._612_ConnectionFailed;
DataAccessException resultEX = new DataAccessException();
try
{
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.Text, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public void TestMethod06() // cadena de conexion mal estructurada
{
string query = "Select count(*) from Albums";
String ConnectionStr = "user/Caro";
String Code = ErrorsAndExceptionsCatalog._606_Code;
String Message = ErrorsAndExceptionsCatalog._606__InvalidConnectionString;
DataAccessException resultEX = new DataAccessException();
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
try
{
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.Text, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public void TestMethod01() // SP mal estructurado
{
// var
String ConnectionStr = ConfigurationManager.AppSettings["ConnStr"];
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter()
{
ParameterName = "Caro"
});
String Code = ErrorsAndExceptionsCatalog._601_Code;
String Message = ErrorsAndExceptionsCatalog._601_InvalidStoredProcedure;
DataAccessException resultEX = new DataAccessException();
//act
try
{
DataTable dt = DataAccessADO.GetDataTable("CountAlbums", CommandType.StoredProcedure, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
// arrange
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}
public JsonResult GetStoredProceduresFromSysObjects(string txt, int idProject)
{
string query = "GetStoredProcedures";
Tb_Projects tb = db.Tb_Projects.Find(idProject);
string ConnStr;
if (tb.LocalConnection.Value)
{
ConnStr = string.Format("data source=./;initial catalog={0};integrated security=True;MultipleActiveResultSets=True;App=EntityFramework", tb.ProjectDatabase);
}
else
{
ConnStr = string.Format("data source={0};initial catalog={1};persist security info=True;user id={2};password={3};multipleactiveresultsets=True;application name=EntityFramework", tb.Server, tb.ProjectDatabase, tb.ProjectUser, tb.Password);
}
//Parameters
List <SqlParameter> listparams = new List <SqlParameter>();
listparams.Add(new SqlParameter("txt", txt));
List <SelectListItem> list = new List <SelectListItem>();
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.StoredProcedure, listparams, ConnStr, null);
list = dt.AsEnumerable().Select(x => new SelectListItem()
{
Text = x.Field <string>("name"), Value = x.Field <string>("id")
}).ToList();
return(Json(list, JsonRequestBehavior.DenyGet));
}
public ActionResult Edit([Bind(Include = "ID_Object,ID_Context,ObjectName,Entity,ObjDescription,ID_AuxObject")] Tb_Objects tb_Objects, int idProject)
{
if (ModelState.IsValid)
{
try
{
db.Entry(tb_Objects).State = EntityState.Modified;
db.SaveChanges();
//Eliminamos de la base de datos los parametros agregados con anterioridad
List <Tb_Parameters> tbParameters = db.Tb_Parameters.Where(x => x.ID_Object == tb_Objects.ID_Object).ToList();
if (tbParameters.Count > 0)
{
db.Tb_Parameters.RemoveRange(tbParameters);
db.SaveChanges();
}
string ConnStr;
Tb_Projects tb = db.Tb_Projects.Find(idProject);
if (tb.LocalConnection.Value)
{
ConnStr = string.Format("data source=./;initial catalog={0};integrated security=True;MultipleActiveResultSets=True;App=EntityFramework", tb.ProjectDatabase);
}
else
{
ConnStr = string.Format("data source={0};initial catalog={1};persist security info=True;user id={2};password={3};multipleactiveresultsets=True;application name=EntityFramework", tb.Server, tb.ProjectDatabase, tb.ProjectUser, tb.Password);
};
List <SqlParameter> queryParams = new List <SqlParameter>();
queryParams.Add(new SqlParameter("idObject", tb_Objects.ID_AuxObject));
List <Tb_Parameters> listparams = new List <Tb_Parameters>();
DataTable dt = DataAccessADO.GetDataTable("GetParametersFromSP", CommandType.StoredProcedure, queryParams, ConnStr, null);
listparams = dt.AsEnumerable().Select(x => new Tb_Parameters()
{
ParameterName = x.Field <string>("name"), DataType = x.Field <string>("name"), Length = x.Field <int>("max_length"), Presition = x.Field <int>("precision"), Nullable = x.Field <bool>("is_nullable"), IsOut = x.Field <bool>("is_output")
}).ToList();
for (var i = 0; i < listparams.Count; i++)
{
listparams[i].ID_Object = tb_Objects.ID_Object;
}
db.Tb_Parameters.AddRange(listparams);
db.SaveChanges();
return(RedirectToAction("Index", new { idContext = tb_Objects.ID_Context, idProject = idProject }));
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}
}
ViewBag.ID_Context = new SelectList(db.Tb_Contexts, "ID_Context", "ContextName", tb_Objects.ID_Context);
return(View(tb_Objects));
}
public void GetResultSet(string SP_name, string ConnStr, int idObject)
{
// obtener mediante idObj la lista de parametros
//convertir a sql parameters: valores dummies (decimal p/e: 0.0)
List <Tb_Parameters> parameters;
List <SqlParameter> queryParams = new List <SqlParameter>();
parameters = db.Tb_Parameters.Where(x => x.ID_Object == idObject).ToList();
foreach (var item in parameters)
{
queryParams.Add(new SqlParameter(item.ParameterName, item.DataType));
}
int consecutiveNumbr = 1;
DataSet DS = DataAccessADO.GetDataSet(SP_name, CommandType.StoredProcedure, queryParams, ConnStr, null); // tercer paso
//List<Tb_ResultSets> RS = new List<Tb_ResultSets>();
List <Tb_ResultSetColumns> RSC = new List <Tb_ResultSetColumns>();
foreach (DataTable resultSet in DS.Tables) // itero de DS casa DT
{
Tb_ResultSets tbResult = new Tb_ResultSets();
tbResult.ID_Object = idObject;
tbResult.ResultSetName = "SET " + consecutiveNumbr;
//RS.Add(tbResult);
db.Tb_ResultSets.Add(tbResult);
db.SaveChanges();
foreach (DataColumn column in resultSet.Columns) // decada DT, itero cada DC
{
Tb_ResultSetColumns tbColumn = new Tb_ResultSetColumns();
tbColumn.ID_ResultSet = tbResult.ID_ResultSet;
tbColumn.ParameterName = column.ColumnName;
tbColumn.DataType = column.DataType.Name;
tbColumn.Length = column.MaxLength;
tbColumn.Nullable = column.AllowDBNull;
tbColumn.IsOut = false;
tbColumn.Presition = 0;
RSC.Add(tbColumn);
}
consecutiveNumbr++;
}
//db.Tb_ResultSets.AddRange(RS); // almaceno los Result Sets
//db.SaveChanges();
db.Tb_ResultSetColumns.AddRange(RSC); // almaceno los Result Set Columns
db.SaveChanges();
}
public void TestMethod014() // parametros nulos
{
string query = "Select count(*) from Albums";
String ConnectionStr = ConfigurationManager.AppSettings["ConnStr"];
List <SqlParameter> listparams = new List <SqlParameter>();
String Code = ErrorsAndExceptionsCatalog._614_Code;
String Message = ErrorsAndExceptionsCatalog._614_ParametersNotFound;
DataAccessException resultEX = new DataAccessException();
try
{
DataTable dt = DataAccessADO.GetDataTable(query, CommandType.Text, listparams, ConnectionStr, null);
}
catch (DataAccessException ex)
{
resultEX = ex;
}
Assert.AreEqual(Code, resultEX.code);
Assert.AreEqual(Message, resultEX.messageCode);
}