public ActionResult PopulateTables(PopulateTablesViewModel vm)
{
List <SelectListItem> items = new List <SelectListItem>();
var conxString = String.Empty;
conxString = GetConnectionString(vm);
DataAccessSql.ConnectionString = conxString;
try
{
foreach (string str2 in DataAccessSql.GetTableNamesFromDatabase())
{
if (!str2.StartsWith("sys"))
{
var item = new SelectListItem
{
Value = str2,
Text = str2
};
items.Add(item);
}
}
}
catch (Exception ex)
{
throw ex;
}
tableList = new MultiSelectList(items.OrderBy(i => i.Text), "Value", "Text");
vm.Tables = tableList;
return(PartialView("_selectTables", vm));
}
public void Test_Invalid_LastName()
{
var dataacessLayer = new DataAccessSql();
Registration user = new Registration {
FirstName = "AmitTset", Pass = "******", EmailId = "*****@*****.**"
};
Assert.Throws <ArgumentNullException>(() => dataacessLayer.AddUser(user));
}
public virtual bool Insertar()
{
string cmdTxt = "INSERT INTO " + this.strTabla + "(";
string values = "";
List <SqlParameter> parameters = new List <SqlParameter>();
int i = 0;
foreach (string key in this.hashDatos.Keys)
{
if (key.ToLower().CompareTo(this.idColumn.ToLower()) == 0 ||
ignoreColumnsIds().Contains(key.ToLower()))
{
continue;
}
cmdTxt += key + ",";
values += "@" + key + ",";
if (this.hashDatos[key] == null)
{
parameters.Add(new SqlParameter("@" + key, DBNull.Value));
}
else
{
parameters.Add(new SqlParameter("@" + key, this.hashDatos[key]));
}
i++;
}
cmdTxt = cmdTxt.Substring(0, cmdTxt.Length - 1);
values = values.Substring(0, values.Length - 1);
cmdTxt += ") VALUES (" + values + ")";
cmdTxt += "; SELECT SCOPE_IDENTITY()";
//Ejecutamos la consulta y si ha insertado algún registro, devolvemos true. Si no, devolvemos false.
try
{
object scalar = DataAccessSql.ExecuteScalar(Constantes.sqlConnectionString, CommandType.Text, cmdTxt, parameters.ToArray());
if (scalar == null)
{
return(false);
}
this.setColumn(this.idColumn, scalar);
return(true);
}
catch (SqlException)
{
return(false);
}
catch (Exception)
{
return(false);
}
}
protected static DBOCollection ExecuteSelectQuery(String connectionString, DBOSql instance, string cmdTxt, SqlParameter[] parameters)
{
DataTable dt = DataAccessSql.ExecuteDataTable(connectionString, CommandType.Text, cmdTxt, parameters); //Ejecutamos la consulta y obtenemos el DataSet correspondiente.
//Preparamos un contenedor para los resultados, es decir, una lista.
DBOCollection rowList = (DBOCollection)instance.getCollection();
foreach (DataRow row in dt.Rows) //Para cada uno de los registros obtenidos...
{
DBOSql dbo = (DBOSql)instance.getObject();
dbo.FillObjectFromDataRow(dt, row);
//Añadimos el objeto a la lista (contenedor).
rowList.Add(row[instance.idColumn].ToString(), dbo);
}
return(rowList); //Devolvemos la lista .
}
public virtual bool Eliminar()
{
string cmdTxt = "DELETE FROM " + this.strTabla + " WHERE " + this.idColumn + " = @ID ";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@ID", this.getColumn(idColumn));
try
{
bool eliminado = DataAccessSql.ExecuteNonQuery(Constantes.sqlConnectionString, CommandType.Text, cmdTxt, parameters) > 0;
return(eliminado);
}
catch (SqlException)
{
return(false);
}
catch (Exception)
{
return(false);
}
}
public virtual bool Load()
{
string cmdTxt = "SELECT * FROM " + this.strTabla + " WHERE " + this.idColumn + " = @ID";
//Creamos los parámetros.
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@ID", this.getColumn(this.idColumn));
//Si el usuario no existe o hay algún problema saltará una excepción.
try
{
DataTable dt = DataAccessSql.ExecuteDataTable(Constantes.sqlConnectionString, CommandType.Text, cmdTxt, parameters);
DataRow row = dt.Rows[0];
FillObjectFromDataRow(dt, row);
return(true); //Delvolvemos true.
}
catch (Exception)
{
return(false); //...devolvemos false.
}
}