public Customer Select(int id)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_ID);
command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
command.Parameters["@p_id"].Value = id;
SqlDataReader reader = db.Select(command);
try
{
reader.Read();
Customer cust = new Customer();
cust.id = reader.GetInt32(0);
cust.email = reader.GetString(1);
cust.fname = reader.GetString(2);
cust.lname = reader.GetString(3);
cust.phone = reader.GetString(4);
cust.adress = reader.GetString(5);
cust.city = reader.GetString(6);
cust.postaCode = reader.GetInt32(7);
reader.Close();
db.Close();
return cust;
}
catch
{
return null;
}
}
public NamesForm()
{
InitializeComponent();
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["sl"];
_schoolDatabase = new SchoolDatabase(connectionStringSettings.ConnectionString);
}
public void Setup()
{
schoolDatabase = new SchoolDatabase();
studentMock = new Mock <Student>();
student = studentMock.Object;
}
public void studentPutFact()
{
SchoolDatabase schoolDatabase = new SchoolDatabase();
Student student = new Student(10, "Krystian", "Dab", 5);
Assert.True(schoolDatabase.PutStudent(student));
}
public void studentListCheckFact()
{
SchoolDatabase schoolDatabase = new SchoolDatabase();
List <Student> listOfStudents = schoolDatabase.GetStudents();
Assert.NotEmpty(listOfStudents);
}
public void SaveCourse(CourseDTO trab)
{
string query = "INSERT INTO TB_CURSO (nm_nome, nm_endereco, nm_celular, nr_data, nr_telefone) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}' )";
query = string.Format(query, trab.Name, trab.Phone, trab.Address, trab.CellPhone, trab.renewal);
SchoolDatabase d = new SchoolDatabase();
d.ExecuteInsert(query);
}
public int Insert(StoredWheels st)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, st);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public int Insert(OkPneuTire tire)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, tire);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
static void Main(string[] args)
{
int x = Properties.Settings.Default.Interval;
ConnectionStringSettings cxnstring = ConfigurationManager.ConnectionStrings["sl"];
SchoolDatabase td = new SchoolDatabase(cxnstring.ConnectionString);
foreach (string s in td.Students("Lower 8"))
{
Console.WriteLine(s);
}
Console.ReadKey();
}
//public int Delete(int id)
//{
// Database db = new Database(connectionString);
// db.Connect();
// SqlCommand command = db.CreateCommand(SQL_DELETE);
// command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
// command.Parameters["@p_id"].Value = id;
// int ret = db.ExecuteNonQuery(command);
// db.Close();
// return ret;
//}
public int Insert(Customer cust)
{
if (!FindCustomerByEmail(cust.email))
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, cust);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
else return -2;
}
public List<StoredWheels> Select(int id)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
command.Parameters.Add(new SqlParameter("@p_Customers_id", SqlDbType.VarChar, 20));
command.Parameters["@p_Customers_id"].Value = id;
SqlDataReader reader = db.Select(command);
List<StoredWheels> cust = Read(reader);
reader.Close();
db.Close();
return cust;
}
protected void Button1_Click(object sender, EventArgs e)
{
if (DoUpdate)
{
strBuilCommand.Append(" where @p_id = id");
SchoolDatabase db = new SchoolDatabase();
db.Connect();
string tmp = strBuilCommand.ToString();
SqlCommand command = db.CreateCommand(tmp);
PrepareCommand(command, emp);
int ret = db.ExecuteNonQuery(command);
db.Close();
}
Response.Redirect("~/Employees/ListEmployee.aspx");
}
public bool IsGarageEmpty(int gar)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_IS_GARAGE_EMPTY);
command.Parameters.Add(new SqlParameter("@p_gar", SqlDbType.Int));
command.Parameters["@p_gar"].Value = gar;
SqlDataReader reader = db.Select(command);
reader.Read();
int tmp = reader.GetInt32(0);
reader.Close();
db.Close();
if (tmp == 0) return true;
else return false;
}
public List<ListItem> FindManufacturers()
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_FIND_MANUFACTURER);
SqlDataReader reader = db.Select(command);
List<ListItem> tires = new List<ListItem>();
tires.Add(new ListItem("Všichni výrobci", "x", true));
while (reader.Read())
{
ListItem l1 = new ListItem(reader.GetString(0), "x", true);
tires.Add(l1);
}
db.Close();
return tires;
}
public bool UnstoreCheck(int id)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_UNSTORE_CHECK);
command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
command.Parameters["@p_id"].Value = id;
SqlDataReader reader = db.Select(command);
reader.Read();
int ret = reader.GetInt32(0);
db.Close();
if (ret == 1) return true;
else return false;
}
public int Exit(Contract con)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_EXIT);
command.Parameters.Add(new SqlParameter("@p_GarageNumber", SqlDbType.SmallInt));
command.Parameters["@p_GarageNumber"].Value = con.GarageNumber;
command.Parameters.Add(new SqlParameter("@p_Payment", SqlDbType.SmallInt));
command.Parameters["@p_Payment"].Value = con.Payment;
command.Parameters.Add(new SqlParameter("@p_VAT", SqlDbType.Bit));
command.Parameters["@p_VAT"].Value = con.VAT;
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public int Arrival(Contract con)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_ARRIVAL);
command.Parameters.Add(new SqlParameter("@p_CustomerID", SqlDbType.Int));
command.Parameters["@p_CustomerID"].Value = con.CustomerId;
command.Parameters.Add(new SqlParameter("@p_EmployeesID", SqlDbType.Int));
command.Parameters["@p_EmployeesID"].Value = con.EmployeesId;
command.Parameters.Add(new SqlParameter("@p_GarageNumber", SqlDbType.SmallInt));
command.Parameters["@p_GarageNumber"].Value = con.GarageNumber;
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public bool UnstoreTires(int id, int EmployeeID)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_UNSTORE);
command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
command.Parameters["@p_id"].Value = id;
command.Parameters.Add(new SqlParameter("@p_Unstore_id", SqlDbType.Int));
command.Parameters["@p_Unstore_id"].Value = EmployeeID;
int ret = db.ExecuteNonQuery(command);
db.Close();
if (ret == 1) return true;
else return false;
}
public OkPneuTire Select(string EAN)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_FIND_BY_EAN);
command.Parameters.Add(new SqlParameter("@p_EAN", SqlDbType.VarChar, 14));
command.Parameters["@p_EAN"].Value = EAN;
SqlDataReader reader = db.Select(command);
reader.Read();
OkPneuTire tire = new OkPneuTire();
tire.Name = reader.GetString(0);
tire.ProdejniCena = reader.GetDecimal(1);
reader.Close();
db.Close();
return tire;
}
public List <CourseDTO> listAllCourses()
{
string query = "Select * from tb_Curso";
SchoolDatabase d = new SchoolDatabase();
MySqlDataReader r = d.ExecuteSelect(query);
List <CourseDTO> itens = new List <CourseDTO>();
while (r.Read())
{
CourseDTO t = new CourseDTO();
t.ID = r.GetInt32("id_trab");
t.Name = r.GetString("nm_nome");
t.Phone = r.GetString("nr_telefone");
t.Address = r.GetString("nm_endereco");
t.CellPhone = r.GetString("nm_celular");
t.Renewal = r.GetInt32("nr_data");
itens.Add(t);
}
return(itens);
}
public SubjectsController(SchoolDatabase db)
{
_db = db;
}
public List<OkPneuTire> Select(int size)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_DETAIL);
command.Parameters.Add(new SqlParameter("@p_Size", SqlDbType.Int));
command.Parameters["@p_Size"].Value = size;
SqlDataReader reader = db.Select(command);
List<OkPneuTire> tires = Read(reader);
reader.Close();
db.Close();
return tires;
}
public void deleteCheck(int id)
{
SchoolDatabase schoolDatabase = new SchoolDatabase();
Assert.False(schoolDatabase.DeleteStudent(id));
}
public PersonController(SchoolDatabase dbContext)
{
_dbContext = dbContext;
}
public void deleteCheckFact()
{
SchoolDatabase schoolDatabase = new SchoolDatabase();
Assert.False(schoolDatabase.DeleteStudent(99990));
}
public List<OkPneuTire> Select(string strsize, char season, bool OnExternalStore, bool OnLokalStore, string manu)
{
if (strsize == null) return null;
string pattern = "[1-3][0-9][0,5][3-9][0,5][1,2][0-9]";
Regex currencyRegex = new Regex(pattern);
if (currencyRegex.IsMatch(strsize))
{
int size = Int32.Parse(strsize);
int min;
if (OnExternalStore) min = 4;
else min = 0;
int minL;
if (OnLokalStore) minL = 4;
else minL = 0;
SchoolDatabase db = new SchoolDatabase();
db.Connect();
if (manu != "Všichni výrobci")
{
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_DETAIL_SIZE);
command.Parameters.Add(new SqlParameter("@p_Size", SqlDbType.Int));
command.Parameters["@p_Size"].Value = size;
command.Parameters.Add(new SqlParameter("@p_Season", SqlDbType.Char, 1));
command.Parameters["@p_Season"].Value = season;
command.Parameters.Add(new SqlParameter("@p_OnExternalStore", SqlDbType.Int));
command.Parameters["@p_OnExternalStore"].Value = min;
command.Parameters.Add(new SqlParameter("@p_OnLocalStore", SqlDbType.Int));
command.Parameters["@p_OnLocalStore"].Value = minL;
command.Parameters.Add(new SqlParameter("@p_manu", SqlDbType.VarChar, 10));
command.Parameters["@p_manu"].Value = manu;
SqlDataReader reader = db.Select(command);
List<OkPneuTire> tires = Read(reader);
reader.Close();
db.Close();
return tires;
}
else
{
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_DETAIL_SIZE_WITHOUT_MANU);
command.Parameters.Add(new SqlParameter("@p_Size", SqlDbType.Int));
command.Parameters["@p_Size"].Value = size;
command.Parameters.Add(new SqlParameter("@p_Season", SqlDbType.Char, 1));
command.Parameters["@p_Season"].Value = season;
command.Parameters.Add(new SqlParameter("@p_OnExternalStore", SqlDbType.Int));
command.Parameters["@p_OnExternalStore"].Value = min;
command.Parameters.Add(new SqlParameter("@p_OnLocalStore", SqlDbType.Int));
command.Parameters["@p_OnLocalStore"].Value = minL;
SqlDataReader reader = db.Select(command);
List<OkPneuTire> tires = Read(reader);
reader.Close();
db.Close();
return tires;
}
}
else return null;
}
public int Update(Customer cust)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, cust);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public StudentsController(SchoolDatabase db)
{
_db = db;
}
public void Setup()
{
schoolDatabase = new SchoolDatabase();
studentMock = Substitute.For <Student>();
}
public List<Contract> Select()
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
List<Contract> emp = Read(reader);
reader.Close();
db.Close();
return emp;
}
private bool FindCustomerByEmail(string email)
{
int tmp;
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_CUSTOMER_EXISTS);
command.Parameters.Add(new SqlParameter("@p_email", SqlDbType.VarChar, 50));
command.Parameters["@p_email"].Value = email;
SqlDataReader reader = db.Select(command);
reader.Read();
tmp = reader.GetInt32(0);
reader.Close();
db.Close();
if (tmp > 0) return true;
else return false;
}
public List<Customer> Select(string lname)
{
string tmp = lname + '%';
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
command.Parameters.Add(new SqlParameter("@p_lname", SqlDbType.VarChar, 20));
command.Parameters["@p_lname"].Value = tmp;
SqlDataReader reader = db.Select(command);
List<Customer> cust = Read(reader);
reader.Close();
db.Close();
return cust;
}
public TeacherController(SchoolDatabase db)
{
_db = db;
}
public StudentController(SchoolDatabase dbContext)
{
_dbContext = dbContext;
}
