public static List <Publisher> Lista()
{
List <Publisher> lista = new List <Publisher>();
// preparacion query sql
string consulta = "SELECT [pub_id],[pub_name],[city],[state],[country] FROM[dbo].[publishers]";
// comando
SqlCommand comando = new SqlCommand(consulta, AdminDB.conexion());
// reader
SqlDataReader reader = comando.ExecuteReader();
// leer los datos
while (reader.Read())
{
lista.Add(new Publisher(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString()));
}
reader.Close();
AdminDB.conexion().Close();
return(lista);
}
protected void btnQueryRun_Click(object sender, EventArgs e)
{
AdminDB db = new AdminDB();
//string sql = "SELECT Prisplan, Ben, `PP-lpnr` as PPlpnr FROM `lager-prisplan` ORDER BY Prisplan";
string sql = txtQueryInput.Text;
if (sql.Trim() != "")
{
if (db.GetDataByDataTable(sql) != null)
{
gvQueryResult.Visible = true;
gvQueryResult.DataSource = db.GetDataByDataTable(sql);
gvQueryResult.DataBind();
lblQueryInvalid.Text = "";
// Header
//gvQueryResultHeader.Visible = true;
//gvQueryResultHeader.DataSource = db.GetDataByDataTable(sql);
//gvQueryResultHeader.DataBind();
}
else
{
gvQueryResult.Visible = false;
lblQueryInvalid.Text = "Invalid Query";
txtQueryInput.Focus();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack)
{
string login = Request["username"];
string password = Request["password"];
AdminDB db = new AdminDB();
List <Admin> a = new List <Admin>();
if (login != "" && password != "")
{
if (db.Check(login).Count > 0)
{
a = db.Check(login);
string pwd = a[0].Admin_pwd;
if (pwd.Equals(password))
{
Session["user"] = login.ToString();
Response.Redirect("ManagerIndex.aspx");
}
else
{
Response.Write("请确认用户名和密码！");
}
}
}
else
{
Response.Write("用户名密码不能为空");
}
}
else
{
Session.Clear();
}
}
public static List <Author> Lista()
{
List <Author> lista = new List <Author>();
// preparacion query sql
string consulta = "SELECT [au_id],[au_lname],[au_fname],[phone],[address],[city],[state],[zip],[contract] FROM [dbo].[authors]";
// comando
SqlCommand comando = new SqlCommand(consulta, AdminDB.conexion());
// reader
SqlDataReader reader = comando.ExecuteReader();
// leer los datos
while (reader.Read())
{
lista.Add(new Author(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), Convert.ToBoolean(reader[8])));
}
reader.Close();
AdminDB.conexion().Close();
return(lista);
}
public static Establishment getEstablishmentByID(string establishmentID)
{
Establishment e = new Establishment();
try
{
SqlCommand command = new SqlCommand("Select * from establishment where establishmentID = @establishmentID");
command.Parameters.AddWithValue("@establishmentID", establishmentID);
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
e.ID = reader["establishmentID"].ToString();
e.Email = reader["email"].ToString();
e.Name = reader["name"].ToString();
e.Password = reader["password"].ToString();
e.Type = reader["type"].ToString();
e.Phone = Convert.ToInt32(reader["phone"]);
e.Address = reader["address"].ToString();
e.Status = reader["status"].ToString();
Admins a = AdminDB.getAdminbyID(reader["adminID"].ToString());
e.Admin = a;
}
reader.Close();
}
finally
{
connection.Close();
}
return(e);
}
protected void btnAUpdate_Click(object sender, EventArgs e)
{
string tempemail = tbxAEmail.Text;
if (tempemail == Session["email"].ToString())
{
lblAOutput.Text = "Admin cannot update his/her own status ";
return;
}
else
{
Admins tmpAdmin = AdminDB.getAdminbyEmail(tempemail);
tmpAdmin.status = ddlAStatus.SelectedValue;
int num = AdminDB.updateAdmin(tmpAdmin);
if (num != 1)
{
lblAOutput.Text = "Cannot Update Admin!";
}
else
{
lblUpdateShow.Text = "Update Successful!";
bindAdmin();
pAdminDetail.Visible = false;
gvAdmin.SelectedIndex = -1;
}
}
}
public static DataSet TraerTodosDataSet()
{
string consultaSQL = "sp_CategoriaTraerTodos"; //query o nombre de sp
//1 adapter 1 query. Se pasa la consulta, la conexion.
//Y devuelve el resultado
SqlDataAdapter adapter = new SqlDataAdapter(consultaSQL, AdminDB.conexion());
//Por defecto, la consulta la toma como una query normal.
//Entonces hay que decirle al adaptador,
//que lo que pasamos anteriormente es un stored procedure
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
//El dataSet q vamos a usar, el nombre que queremos ponerle a la
//tabla en memoria, puede llamarse igual o diferente a la tabla posta
adapter.Fill(ds, "Category");
//adater.Fill: conecta a la db, ejecuta el sp, llena
//el datatable y se desconecta
//En este caso devuelve el dataSet completo, con todas las tablas
//que tiene dentro
return(ds);
}
public static int Agregar(Author author)
{
// query insert
/* string consulta = "INSERT INTO [dbo].[publishers]([pub_id],[pub_name],[city],[state],[country]) VALUES (@pub_id,@pub_name, @city,@state,@country)";*/
string consulta = "INSERT INTO [dbo].[authors]([au_id],[au_lname],[au_fname],[phone],[address],[city],[state],[zip],[contract]) VALUES (@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)";
// sqlcommand
SqlCommand comando = new SqlCommand(consulta, AdminDB.conexion());
// parametros
comando.Parameters.Add("@au_id", SqlDbType.VarChar, 11).Value = author.Au_id;
comando.Parameters.Add("@au_lname", SqlDbType.VarChar, 40).Value = author.Au_lname;
comando.Parameters.Add("@au_fname", SqlDbType.VarChar, 20).Value = author.Au_fname;
comando.Parameters.Add("@phone", SqlDbType.Char, 12).Value = author.Phone;
comando.Parameters.Add("@address", SqlDbType.VarChar, 40).Value = author.Address;
comando.Parameters.Add("@city", SqlDbType.VarChar, 20).Value = author.City;
comando.Parameters.Add("@state", SqlDbType.Char, 2).Value = author.State;
comando.Parameters.Add("@zip", SqlDbType.Char, 5).Value = author.Zip;
comando.Parameters.Add("@contract", SqlDbType.Bit, 1).Value = author.Contract;
// ejecutar el comando para enviar los datos a db
int fulasAfectadas = comando.ExecuteNonQuery();
AdminDB.conexion().Close();
return(fulasAfectadas);
}
protected void gvAdmin_SelectedIndexChanged(object sender, EventArgs e)
{
lblUpdateShow.Text = "";
pAdminDetail.Visible = true;
if (Session["atemp"] == null)
{
List <Admins> adminlists = AdminDB.getallAdmins();
Admins m = adminlists[gvAdmin.PageSize * gvAdmin.PageIndex + gvAdmin.SelectedIndex];
tbxAdminId.Text = m.adminId;
tbxAEmail.Text = m.email;
tbxAName.Text = m.name;
tbxAAddress.Text = m.Address;
tbxAdminPhone.Text = m.phone.ToString();
ddlAStatus.SelectedValue = m.status;
}
else
{
Admins a = AdminDB.getAdminbyEmail(tbxSearch.Text);
tbxAdminId.Text = a.adminId;
tbxAEmail.Text = a.email;
tbxAName.Text = a.name;
tbxAAddress.Text = a.Address;
tbxAdminPhone.Text = a.phone.ToString();
ddlAStatus.SelectedValue = a.status;
}
}
public static int CmdInsert(Producto producto)
{
string query = "INSERT INTO dbo.Productos (Nombre ,Precio ,IdCategoria) VALUES (@Nombre, @Precio, @IdCategoria); SELECT SCOPE_IDENTITY()";
SqlCommand command = new SqlCommand(query, AdminDB.ConectarDB());
command.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = producto.Nombre;
command.Parameters.Add("@Precio", SqlDbType.Money).Value = producto.Precio;
command.Parameters.Add("@IdCategoria", SqlDbType.Int).Value = producto.IdCategoria;
object filasAfectadas = command.ExecuteScalar(); //Invocar: inser into-update-delete
//Cerrar connecion de la DB
AdminDB.ConectarDB().Close();
if (filasAfectadas != null)
{
return(Convert.ToInt32(filasAfectadas));
}
else
{
return(0);
}
}
public static List <Alumno> TraerTodos()
{
//1 connection
//2 sql o spu
string query = "SELECT [Id],[Nombre],[Apellido],[DNI],[Email],[Ciudad],[NroLegajo]FROM[dbo].[Alumno]";
//3 comando SqlCommand
SqlCommand command = new SqlCommand(query, AdminDB.ConectarDB());
//4 objeto reader
//5 invcocar SqlCommand y trabajar con el reader
SqlDataReader reader = command.ExecuteReader();
//6 crear la lista
List <Alumno> lista = new List <Alumno>();
while (reader.Read())
{
lista.Add(new Alumno(
reader["Nombre"].ToString(),
reader["Apellido"].ToString(),
int.Parse(reader["DNI"].ToString()),
reader["Email"].ToString(),
reader["Ciudad"].ToString(),
int.Parse(reader["NroLegajo"].ToString())
));
}
AdminDB.CerrarConexion();
return(lista);
}
public static List <Alumno> TraerTodos(string ciudad)
{
string procedure = "TraerTodosPorCiudad";
SqlCommand command = new SqlCommand(procedure, AdminDB.ConectarDB());
//definir tipo de comando
command.CommandType = CommandType.StoredProcedure;
//definir parametro
command.Parameters.Add("@ciudad", SqlDbType.VarChar, 50).Value = ciudad;
SqlDataReader reader = command.ExecuteReader();
List <Alumno> lista = new List <Alumno>();
while (reader.Read())
{
lista.Add(new Alumno(
reader["Nombre"].ToString(),
reader["Apellido"].ToString(),
int.Parse(reader["DNI"].ToString()),
reader["Email"].ToString(),
reader["Ciudad"].ToString(),
int.Parse(reader["NroLegajo"].ToString())
));
}
AdminDB.CerrarConexion();
return(lista);
}
private void btnConfirm_Click_1(object sender, EventArgs e)
{
// Создаём тест
TestDB test = new TestDB();
test.name = tbName.Text;
test.group_id = Convert.ToInt32(((KeyValuePair <string, string>)cbGroup.SelectedItem).Key);
MainForm main_fm = (MainForm)Application.OpenForms["MainForm"];
AdminDB user = new AdminDB(main_fm.login);
test.owner_id = user.id;
test.date_created = DateTime.Now;
test.date_last_saved = DateTime.Now;
test.AddToDB();
this.Close();
EditTest et = new EditTest(test.name);
test = null;
et.Show();
main_fm.UpdateDGV_Tests();
}
public ActionResult SaveParams2(Page2Objs getParams)
{
UserInfo oUser = new UserInfo();
oUser = UserController.GetUserInfo(User.Identity.Name);
AdminDB AdminDB = new AdminDB();
//check userid
bool insertUserId = false;
bool checkUSerId = AdminDB.CheckUserId(oUser.Id);
if (!checkUSerId)
{
//insert userid
insertUserId = AdminDB.InertUserId(oUser.Id);
}
//save
bool isSave = AdminDB.SavePage2(oUser.Id, getParams);
if (isSave)
{
return(Json("", JsonRequestBehavior.AllowGet));
}
else
{
return(Json("", JsonRequestBehavior.DenyGet));
}
}
public static int Eliminar(int productoId)
{
string sql = "DELETE FROM Producto WHERE id=@idProducto";
SqlCommand comando = new SqlCommand(sql, AdminDB.conexion());
comando.Parameters.Add("@idProducto", SqlDbType.Int).Value = productoId;
return(comando.ExecuteNonQuery());
}
public static int Eliminar(int idAlumno)
{
string query = "DeleteAlumnoPorId";
SqlCommand dmlQuery = new SqlCommand(query, AdminDB.ConectarDB());
dmlQuery.CommandType = CommandType.StoredProcedure;
dmlQuery.Parameters.Add("@Id", SqlDbType.Int).Value = idAlumno;
return(dmlQuery.ExecuteNonQuery());
}
public AdminAddEdit(string username)
{
InitializeComponent();
this.db = new AdminDB();
this.loggedInAs = username;
lst_AdminList.ItemsSource = db.FindAdmins();
this.MouseDown += WindowMouseDown;
}
/// <summary>
/// Retorna un DataTable con todos los registros de la tabla Clientes
/// </summary>
/// <returns></returns>
public static DataTable TraerTodos()
{
string consulta = "SELECT id, nombre, apellido, ciudad FROM Cliente";
SqlDataAdapter adapter = new SqlDataAdapter(consulta, AdminDB.Conexion());
DataSet ds = new DataSet();
adapter.Fill(ds, "Cliente");
return(ds.Tables["Cliente"]);
}
public static DataTable TraerCiudades()
{
string consulta = "SELECT DISTINCT(Ciudad) FROM Cliente";
SqlDataAdapter adapter = new SqlDataAdapter(consulta, AdminDB.Conexion());
DataSet ds = new DataSet();
adapter.Fill(ds, "Ciudad");
return(ds.Tables["Ciudad"]);
}
public UserLogic()
{
userDB = new UserDB();
account = new Account();
cl = new CustomerLogic();
adminDB = new AdminDB();
productDB = new ProductDB();
orderLineDB = new OrderLineDB();
productLogic = new ProductLogic();
}
private void btnConfirm_Click_1(object sender, EventArgs e)
{
// Тип расписания(id)
int raspisanie_type_id = 1;
DateTime dateStart;
DateTime dateFinish;
if (rbRaspType1.Checked)
{
raspisanie_type_id = 1;
dateStart = rasp1_dateStart.Value;
dateFinish = rasp1_dateFinish.Value;
}
else if (rbRaspType2.Checked)
{
raspisanie_type_id = 2;
dateStart = rasp2_dateStart.Value;
dateFinish = rasp2_dateFinish.Value;
}
else
{
raspisanie_type_id = 3;
dateStart = rasp3_dateStart.Value;
dateFinish = rasp3_dateFinish.Value;
}
// Id авторизованного админа
MainForm main_fm = (MainForm)Application.OpenForms["MainForm"];
string name_admin = main_fm.getAdminName();
AdminDB admin = new AdminDB(name_admin);
// Получаем id выбранной группы тестов
int test_group_id_selected = Convert.ToInt32(((KeyValuePair <string, string>)cbRulesGroup.SelectedItem).Key);
// Количество попыток
int _number_attempt = 10000000;
if (cbNumberAttempt.Checked)
{
_number_attempt = Convert.ToInt32(cbNumberAttemptValue.Text);
}
//Находим правило
editable_rule.name = tb_Name.Text;
editable_rule.description = tbDescription.Text;
editable_rule.raspisanie_type_id = raspisanie_type_id;
editable_rule.time_start = dateStart;
editable_rule.time_finish = dateFinish;
editable_rule.number_attempt = _number_attempt;
editable_rule.admin_id = admin.id;
editable_rule.group_id = Convert.ToInt32(((KeyValuePair <string, string>)cbRulesGroup.SelectedItem).Key);
editable_rule.Save();
main_fm.UpdateDGV_Rules();
this.Close();
}
//TODO Falta completar Insertar
/// <summary>
/// Metodo para insertar un registro en la tabla Categoria,
/// de la base de datos dbCurso
/// </summary>
/// <param name="categoria">Requiere de un objeto Categoria</param>
/// <returns></returns>
public static int Insertar(Categoria categoria)
{
string sql = "sp_CategoriaInsertar";
SqlCommand comando = new SqlCommand(sql, AdminDB.conexion());
comando.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = categoria.Nombre;
comando.CommandType = CommandType.StoredProcedure;
int filasAfectadas = comando.ExecuteNonQuery();
return(filasAfectadas);
}
public static int Eliminar(int idCategoria)
{
string sql = "sp_CategoriaEliminar";
SqlCommand comando = new SqlCommand(sql, AdminDB.conexion());
comando.Parameters.Add("@Id", SqlDbType.Int).Value = idCategoria;
comando.CommandType = CommandType.StoredProcedure;
int filasAfectadas = comando.ExecuteNonQuery();
return(filasAfectadas);
}
public static int Insertar(Producto producto)
{
string sql = "INSERT INTO Producto(Nombre,CategoriaID,Proveedor)" +
"VALUES (@nombre,@catID,@proveedor)";
SqlCommand comando = new SqlCommand(sql, AdminDB.conexion());
comando.Parameters.Add("@nombre", SqlDbType.VarChar, 50).Value = producto.Nombre;
comando.Parameters.Add("@catID", SqlDbType.Int).Value = producto.CategoriaID;
comando.Parameters.Add("@proveedor", SqlDbType.VarChar, 50).Value = producto.Proveedor;
return(comando.ExecuteNonQuery());
}
public static int Agregar(Alumno alumno)
{
string query = "INSERT INTO Alumno(Nombre, Apellido, DNI, Email, Ciudad, NroLegajo)VALUES(@Nombre, @Apellido, @DNI, @Email, @Ciudad, @NroLegajo)";
SqlCommand dmlQuery = new SqlCommand(query, AdminDB.ConectarDB());
dmlQuery.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = alumno.Nombre;
dmlQuery.Parameters.Add("@Apellido", SqlDbType.VarChar, 50).Value = alumno.Apellido;
dmlQuery.Parameters.Add("@DNI", SqlDbType.Char, 11).Value = alumno.DNI;
dmlQuery.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = alumno.Email;
dmlQuery.Parameters.Add("@Ciudad", SqlDbType.VarChar, 50).Value = alumno.Ciudad;
dmlQuery.Parameters.Add("@NroLegajo", SqlDbType.Int).Value = alumno.NroLegajo;
return(dmlQuery.ExecuteNonQuery());
}
public static int Actualizar(Producto producto)
{
string sql = "UPDATE Producto SET Nombre = @nombre, " +
"CategoriaID = @catid, Proveedor = @proveedor " +
"WHERE Id = @id";
SqlCommand comando = new SqlCommand(sql, AdminDB.conexion());
comando.Parameters.Add("@nombre", SqlDbType.VarChar, 50).Value = producto.Nombre;
comando.Parameters.Add("@catID", SqlDbType.Int).Value = producto.CategoriaID;
comando.Parameters.Add("@proveedor", SqlDbType.VarChar, 50).Value = producto.Proveedor;
comando.Parameters.Add("@id", SqlDbType.Int).Value = producto.Id;
return(comando.ExecuteNonQuery());
}
/// <summary>
/// Amin: Update Submit time of a tape from log
/// </summary>
/// <param name="Schedule_ID"></param>
/// <returns></returns>
public bool UpdateTapeSubmittoDB()
{
int AffectedRows = -1;
string CommandString;
long IDToBEUpdated = GetScheudleTapeID();
if (TapeID > 0 && ScheduleID > 0 && IDToBEUpdated > 0)
{
CommandString = "Update scheduled_tapes Set SUBMITED_AT='" + AdminDB.ChangeDateTimeFormat(this.TapeSubmitTimeDB) + "'"
+ " Where SCHEDULED_TAPE_ID = " + IDToBEUpdated;
(new AdminDB()).CheckDB(CommandString, out AffectedRows);
}
return(AffectedRows > 0 ? true : false);
}
public static int Modificar(Alumno alumno)
{
string query = "UPDATE Alumno SET Nombre = @Nombre, Apellido = @Apellido, Dni = @Dni, Ciudad = @Ciudad, Email = @Email, NroLegajo = @NroLegajo WHERE ID = @Id";
SqlCommand dmlQuery = new SqlCommand(query, AdminDB.ConectarDB());
dmlQuery.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = alumno.Nombre;
dmlQuery.Parameters.Add("@Apellido", SqlDbType.VarChar, 50).Value = alumno.Apellido;
dmlQuery.Parameters.Add("@Dni", SqlDbType.Char, 11).Value = alumno.DNI;
dmlQuery.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = alumno.Email;
dmlQuery.Parameters.Add("@Ciudad", SqlDbType.VarChar, 50).Value = alumno.Ciudad;
dmlQuery.Parameters.Add("@NroLegajo", SqlDbType.Int).Value = alumno.NroLegajo;
dmlQuery.Parameters.Add("@Id", SqlDbType.Int).Value = alumno.Id;
return(dmlQuery.ExecuteNonQuery());
}
public static int Eliminar(int pId)
{
string consulta = "DELETE FROM dbo.Categoria WHERE Id=@Id";
comando = new SqlCommand(consulta, AdminDB.ConectarDB());
comando.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = pId;
int filasAfectadas = comando.ExecuteNonQuery();
AdminDB.ConectarDB().Close();
return(filasAfectadas);
}
public static int Agregar(Categoria nuevo)
{
string consulta = "INSERT INTO dbo.Categoria (Nombre,Descripcion) VALUES (@Nombre, @Descripcion)";
comando = new SqlCommand(consulta, AdminDB.ConectarDB());
comando.Parameters.Add("@Nombre", System.Data.SqlDbType.VarChar, 50).Value = nuevo.Nombre;
comando.Parameters.Add("@Descripcion", System.Data.SqlDbType.VarChar, 50).Value = nuevo.Descripcion;
int filasAfectadas = comando.ExecuteNonQuery();
AdminDB.ConectarDB().Close();
return(filasAfectadas);
}