public UserRole GetRole(int roleId)
{
Connection<UserRole> conn = new Connection<UserRole>(Connection<string>.connectionString);
try
{
conn.OpenConnection();
UserRole role = conn.LoadData("SELECT Role FROM Role where RoleId = '" + roleId + "';").FirstOrDefault();
return role;
}
catch (Exception ex)
{
Log.Error(ex.Message);
return null;
}
finally
{
conn.CloseConnection();
}
}
/// <summary>
/// GetUser
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <returns></returns>
public User GetUser(string name, string password)
{
Connection<User> conn = new Connection<User>(Connection<string>.connectionString);
try
{
conn.OpenConnection();
User user = conn.LoadData("SELECT * FROM User where UserName = '******' and Password = '******';").FirstOrDefault();
user.Role = new UserRole().GetRole(user.RoleId).Role;
return user;
}
catch (Exception ex)
{
Log.Error(ex.Message);
return null;
}
finally
{
conn.CloseConnection();
}
}
public int Ekle(Sevkiyat sevkiyat)
{
using (SqlCommand command = new SqlCommand("INSERT INTO SevkiyatTakip.Sevkiyatlar (MusteriId,AracId,YuklemeTipId,PlakaNo,Adet,Aciklama,LojistikFirma,SiparisNo,TerminTarih,DurumId) " +
"VALUES(@MusteriId,@AracId,@YuklemeTipId,@PlakaNo,@Adet,@Aciklama,@LojistikFirma,@SiparisNo,@TerminTarih,@DurumId) ", Connection.sqlConn))
{
command.Parameters.AddWithValue("@MusteriId", sevkiyat.MusteriId);
command.Parameters.AddWithValue("@AracId", sevkiyat.AracId);
command.Parameters.AddWithValue("@YuklemeTipId", sevkiyat.YuklemeTipId);
command.Parameters.AddWithValue("@PlakaNo", sevkiyat.PlakaNo);
command.Parameters.AddWithValue("@Adet", sevkiyat.Adet);
command.Parameters.AddWithValue("@Aciklama", sevkiyat.Aciklama);
command.Parameters.AddWithValue("@LojistikFirma", sevkiyat.LojistikFirma);
command.Parameters.AddWithValue("@SiparisNo", sevkiyat.SiparisNo);
command.Parameters.AddWithValue("@TerminTarih", sevkiyat.TerminTarih);
command.Parameters.AddWithValue("@DurumId", sevkiyat.DurumId);
Connection.OpenConnection();
int result = command.ExecuteNonQuery();
Connection.sqlConn.Close();
return(result);
}
}
public bool Update(Tarefa tarefa)
{
SqlCommand command = Connection.OpenConnection();
command.CommandText = @"UPDATE tarefas SET
id_categoria = @ID_CATEGORIA,
id_usuario = @ID_USUARIO,
id_projeto = @ID_PROJETO,
titulo = @TITULO,
descricao = @DESCRICAO,
duracao = @DURACAO WHERE id = @ID";
command.Parameters.AddWithValue("@ID_CATEGORIA", tarefa.IdCategoria);
command.Parameters.AddWithValue("@ID_USUARIO", tarefa.IdUsuario);
command.Parameters.AddWithValue("@ID_PROJETO", tarefa.IdProjeto);
command.Parameters.AddWithValue("@TITULO", tarefa.Titulo);
command.Parameters.AddWithValue("@DESCRICAO", tarefa.Descricao);
command.Parameters.AddWithValue("@DURACAO", tarefa.Duracao);
command.Parameters.AddWithValue("@ID", tarefa.Id);
int quantidade = command.ExecuteNonQuery();
command.Connection.Close();
return(quantidade == 1);
}
public int UpdateTermin(Termin termin)
{
int terminID;
Connection con = new Connection();
SqlCommand command = new SqlCommand();
command.Connection = con.PoolConnection();
command.CommandType = CommandType.Text;
command.CommandText = "Update Termin set VremeOd = @VremeOd, VremeDo = @VremeDo, DatumTermina = @DatumTermina, IDBazen = @IDBazen, IDRezervacija = @IDRezervacija, IDAdmin = @IDAdmin where IDTermin = @IDTermin";
command.Parameters.Add("@IDTermin", SqlDbType.Int).Value = termin.IDTermin;
command.Parameters.Add("@VremeOd", SqlDbType.Time).Value = termin.VremeOd;
command.Parameters.Add("@VremeDo", SqlDbType.Time).Value = termin.VremeDo;
command.Parameters.Add("@DatumTermina", SqlDbType.Date).Value = termin.DatumTermina;
command.Parameters.Add("@IDBazen", SqlDbType.Int).Value = termin.IDBazen;
command.Parameters.Add("@IDRezervacija", SqlDbType.Int).Value = termin.IDRezervacija;
command.Parameters.Add("@IDAdmin", SqlDbType.Int).Value = termin.IDAdmin;
try
{
con.OpenConnection();
terminID = Convert.ToInt32(command.ExecuteScalar());
logger.LogInfo(DateTime.Now, "UpdateTermin method has sucessfully invoked.");
return(0);
}
catch (Exception ex)
{
logger.LogError(DateTime.Now, "Error while trying to update termin." + ex.Message);
throw new Exception("Error while trying to update termin." + ex.Message);
}
finally
{
con.CloseConnection();
}
}
private void ExecuteQuery()
{
Connection.OpenConnection();
try
{
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запит успішно виконано!");
}
else
{
MessageBox.Show("Помилка запиту");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);// + String.Empty + ex.InnerException.ToString());
//Console.WriteLine(ex.Message);
}
finally
{
Connection.CloseConnection();
}
}
public IEnumerable <Patient> GetAllPacients()
{
var con = new Connection {
StrCommand = { CommandText = @"SELECT * FROM TabPatient" }
};
con.OpenConnection();
_dr = con.StrCommand.ExecuteReader();
var list = new List <Patient>();
while (_dr.Read())
{
list.Add(new Patient
{
Id = _dr.GetInt32(0),
Name = _dr.GetString(1),
Genere = _dr.GetString(2),
BirthDate = _dr.GetString(3),
Cpf = _dr.GetString(4),
Telephone = _dr.GetString(5),
ZipCode = _dr.GetString(6),
Street = _dr.GetString(7),
Number = _dr.GetString(8),
Country = _dr.GetString(9),
State = _dr.GetString(10),
City = _dr.GetString(11),
Notice = _dr.GetString(12),
Specialization = _dr.GetString(13)
});
}
con.CloseConnection();
return(list);
}
public Cidade ObterPeloId(int id)
{
SqlCommand command = Connection.OpenConnection();
command.CommandText = @"SELECT * FROM cidades WHERE id = @ID";
command.Parameters.AddWithValue("@ID", id);
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
command.Connection.Close();
if (table.Rows.Count == 0)
{
return(null);
}
DataRow row = table.Rows[0];
Cidade cidade = new Cidade();
cidade.Id = Convert.ToInt32(row["id"]);
cidade.Nome = row["nome"].ToString();
cidade.NumeroHabitantes = Convert.ToInt32(row["numero_habitantes"]);
cidade.EstadoId = Convert.ToInt32(row["estado_id"]);
return(cidade);
}
public List <Categoria> ObterTodos()
{
SqlCommand command = Connection.OpenConnection();
command.CommandText = "SELECT * FROM categorias";
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
List <Categoria> categorias = new List <Categoria>();
command.Connection.Close();
foreach (DataRow row in table.Rows)
{
Categoria categoria = new Categoria()
{
Id = Convert.ToInt32(row["id"]),
Nome = row["nome"].ToString()
};
categorias.Add(categoria);
}
return(categorias);
}
// check if the student number is already registered
public static bool CheckStudentNumber(string studentNumber)
{
Connection.OpenConnection();
using (SqlCommand cmd = Connection.conn.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM cl_students WHERE student_id=@studentNumber";
cmd.Parameters.AddWithValue("@studentNumber", SqlDbType.VarChar).Value = studentNumber;
if (Connection.conn.State == ConnectionState.Closed)
{
Connection.conn.Open();
}
try
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (Convert.ToInt32(reader[0].ToString()) > 0)
{
Connection.conn.Close();
return(true);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Connection.conn.Close();
}
return(false);
}
public string DeleteObject(User e)
{
string result;
Connection objCon = new Connection();
MySqlConnection Conn = new MySqlConnection();
Conn = objCon.OpenConnection();
MySqlCommand command = Conn.CreateCommand();
command.CommandText = "delete from User WHERE id = " & e.id & "";
int verify = cmd.ExecuteNonQuery();
if (verify != -1)
{
result = "Data deleted into database successfully!";
}
else
{
result = "Error";
}
command.Dispose();
return(result);
}
public List <Contabilidade> ObterTodos()
{
SqlCommand command = Connection.OpenConnection();
command.CommandText = "SELECT * FROM contabilidades";
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
List <Contabilidade> contabilidades = new List <Contabilidade>();
command.Connection.Close();
foreach (DataRow row in table.Rows)
{
Contabilidade contabilidade = new Contabilidade()
{
Id = Convert.ToInt32(row["id"]),
Nome = row["nome"].ToString()
};
contabilidades.Add(contabilidade);
}
return(contabilidades);
}
public Projeto ObterPeloId(int id)
{
SqlCommand command = Connection.OpenConnection();
command.CommandText = @"SELECT * FROM projetos WHERE id = @ID";
command.Parameters.AddWithValue("@ID", id);
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
command.Connection.Close();
if (table.Rows.Count == 0)
{
return(null);
}
DataRow row = table.Rows[0];
Projeto projeto = new Projeto();
projeto.Id = Convert.ToInt32(row["id"]);
projeto.Nome = row["nome"].ToString();
projeto.DataCriacaoProjeto = Convert.ToDateTime(row["data_criacao_projeto"]);
projeto.DataFinalizacao = Convert.ToDateTime(row["data_finalizacao"]);
projeto.IdCliente = Convert.ToInt32(row["id_cliente"]);
return(projeto);
}
public bool AddUrlToScannedTable()
{
if (string.IsNullOrWhiteSpace(TbWeb.Text))
{
MessageBox.Show("Empty URL!" + Environment.NewLine + "Where did you take this data from?");
return(false);
}
if (string.IsNullOrWhiteSpace(TbUrlName.Text))
{
MessageBox.Show("Empty Data Name!" + Environment.NewLine + "Please enter book name/data name for this data!");
return(false);
}
bool ret = false;
var connection = new Connection();
connection.OpenConnection();
var has = connection.HasScannedEntry(TbWeb.Text, TbUrlName.Text);
if (has)
{
MessageBox.Show("You have already added this data, If you want to add it again, please change the url and Data name.");
lstItems.Items.Clear();
}
else
{
connection.CreateScannedEntry(TbWeb.Text, TbUrlName.Text, 1);
ret = true;
}
connection.CloseConnection();
return(ret);
}
public string UpdateObject(User e)
{
string result;
Connection objCon = new Connection();
MySqlConnection Conn = new MySqlConnection();
Conn = objCon.OpenConnection();
try{
string SQL = "UPDATE User SET id = @id , name = @name , email = @email , password = @password , phone = @phone , cellPhone = @cellPhone , ZipCodeDelivery_idZipCodeDelivery = @ZipCodeDelivery_idZipCodeDelivery , number = @number , complement = @complement , statusUser = @statusUser , dataTime = @dataTime WHERE id = e.id";
MySqlCommand cmd = new MySqlCommand(SQL, Conn);
cmd.Parameters.AddWithValue("@id", e.id);
cmd.Parameters.AddWithValue("@name", e.name);
cmd.Parameters.AddWithValue("@email", e.email);
cmd.Parameters.AddWithValue("@password", e.password);
cmd.Parameters.AddWithValue("@phone", e.phone);
cmd.Parameters.AddWithValue("@cellPhone", e.cellPhone);
cmd.Parameters.AddWithValue("@ZipCodeDelivery_idZipCodeDelivery", e.ZipCodeDelivery_idZipCodeDelivery);
cmd.Parameters.AddWithValue("@number", e.number);
cmd.Parameters.AddWithValue("@complement", e.complement);
cmd.Parameters.AddWithValue("@statusUser", e.statusUser);
cmd.Parameters.AddWithValue("@dataTime", e.dataTime);
int verify = cmd.ExecuteNonQuery();
if (verify != -1)
{
result = "Data updated into database successfully!";
}
else
{
result = "Error";
}
command.Dispose();
}catch (MySql.Data.MySqlClient.MySqlException ex) {
result = "Error " + ex.Number + " has occurred: " + ex.Message;
}
return(result);
}
public static bool ChangePass(int Id, string OldPass, string NewPass, string UserName, bool Choose)
{
try
{
bool Found = false;
OldPass = Password.Encrypt(OldPass);
Connection ConnectAndRun = new Connection();
using (ConnectAndRun.OpenConnection())
{
string Query = null;
if (Choose == true)
{
Query = "Select KullaniciAdi, Sifre from Akademisyen";
}
else
{
Query = "Select KullaniciAdi, Sifre from Ogrenci";
}
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query);
SqlDataReader sqlDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
if (UserName == sqlDataReader["KullaniciAdi"].ToString() && OldPass == sqlDataReader["Sifre"].ToString())
{
Found = true;
break;
}
}
}
if (Found == true)
{
using (ConnectAndRun.OpenConnection())
{
NewPass = Password.Encrypt(NewPass);
string Query = null;
if (Choose == true)
{
Query = "UPDATE Akademisyen set Sifre = @Sifre where Id = " + Id.ToString();
}
else
{
Query = "UPDATE Ogrenci set Sifre = @Sifre where Id = " + Id.ToString();
}
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query);
Cmd.Parameters.Add("@Sifre", SqlDbType.NVarChar).Value = NewPass;
Cmd.ExecuteNonQuery();
}
return(true);
}
else
{
MessageBox.Show("Girdiğiniz Şifre Hatalı", "Hata !", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
return(false);
}
}
public void ExcuteCommand(string command)
{
Connection.OpenConnection();
Console.WriteLine(Command);
Connection.CloseConnection();
}
public TestBase()
{
SqlConnection = Connection.OpenConnection();
}
public static DataTable GetScoresForAcademician(int Id, int AId)
{
try
{
List <int> GettedData = new List <int>();
string dummy = null;
Connection ConnectAndRun = new Connection();
using (ConnectAndRun.OpenConnection())
{
string Query = "Select AlanOgrenciler from ders where Aktif = 1 and AlanOgrenciler is NOT NULL and Id = " + Id.ToString();
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query);
SqlDataReader sqlDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
for (int i = 0; i < sqlDataReader["AlanOgrenciler"].ToString().Length; i += 2)
{
dummy = sqlDataReader["AlanOgrenciler"].ToString().Substring(i, 2);
GettedData.Add(Convert.ToInt32(dummy));
}
}
}
string Query2 = "IF NOT EXISTS (select * from notlar where OgrenciId = @OgrenciId and DersId = @DersId)" +
"INSERT INTO Notlar values (@OgrenciId, @Vize, @Final,@DersId, @Aktif)";
for (int i = 0; i < GettedData.Count; i++)
{
using (ConnectAndRun.OpenConnection())
{
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query2);
int data = GettedData[i];
Cmd.Parameters.Add("@OgrenciId", SqlDbType.Int).Value = data;
Cmd.Parameters.Add("@DersId", SqlDbType.Int).Value = Id;
Cmd.Parameters.Add("@Vize", SqlDbType.Int).Value = DBNull.Value;
Cmd.Parameters.Add("@Final", SqlDbType.Int).Value = DBNull.Value;
Cmd.Parameters.Add("@Aktif", SqlDbType.Bit).Value = true;
Cmd.ExecuteNonQuery();
}
}
using (ConnectAndRun.OpenConnection())
{
string Query = "Select Notlar.Id, OgrenciId, Ogrenci.Ad, Ogrenci.Soyad, Ogrenci.Numara, Ders.Ad as 'Ders Adı', Kredi, Vize, Final, Ortalama from Ders " +
" inner join Notlar on Ders.Id = Notlar.DersId " +
"inner join Ogrenci on Notlar.OgrenciId = Ogrenci.Id " +
"where AkademisyenId = @AkademisyenId and Ogrenci.Aktif = 1 and Ders.Aktif = 1 and DersId = @DersId";
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query);
Cmd.Parameters.Add("@AkademisyenId", SqlDbType.Int).Value = AId;
Cmd.Parameters.Add("@DersId", SqlDbType.Int).Value = Id;
var dt = new DataTable();
dt.Load(Cmd.ExecuteReader());
dt.Columns["Id"].ColumnMapping = MappingType.Hidden;
dt.Columns["OgrenciId"].ColumnMapping = MappingType.Hidden;
return(dt);
}
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
return(null);
}
}
private void button2_Click(object sender, EventArgs e)
{
int licznik = 0;
String QUES = "SELECT DISTINCT MARKA, MODEL, ROK_PROD FROM samochody WHERE ";
if (listBox1.Text != "")
{
QUES += "MARKA = '" + listBox1.Text.Trim() + "' AND ";
licznik++;
}
if (listBox2.Text != "")
{
QUES += "MODEL = '" + listBox2.Text.Trim() + "' AND ";
licznik++;
}
if (listBox3.Text != "")
{
QUES += "ROK_PROD >= " + listBox3.Text.Trim() + " AND ";
licznik++;
}
if (listBox4.Text != "")
{
QUES += "ROK_PROD <= " + listBox4.Text.Trim() + " AND ";
licznik++;
}
if (listBox5.Text != "")
{
QUES += "KRAJ_PROD = '" + listBox5.Text.Trim() + "' AND ";
licznik++;
}
if (textBox1.Text != "")
{
QUES += "POJ_SIL < " + textBox1.Text.Trim() + " AND ";
licznik++;
}
if (textBox2.Text != "")
{
QUES += "KOSZT_DNIA < " + textBox2.Text.Trim() + " AND ";
licznik++;
}
string hierKomm = "", dieSonne = "", year = "";
Connection conn = new Connection();
DataTable dtReg = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
conn.OpenConnection();
if (licznik != 0)
{
command.CommandText = QUES.Substring(0, QUES.Length - 5) + " AND DOSTEPNOSC = 1";
}
else
{
command.CommandText = QUES.Substring(0, QUES.Length - 6) + " WHERE DOSTEPNOSC = 1";
}
command.Connection = conn.GetConnection();
adapter.SelectCommand = command;
adapter.Fill(dtReg);
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
try
{
foreach (DataRow row in dtReg.Rows)
{
hierKomm = row["MARKA"].ToString();
dieSonne = row["MODEL"].ToString();
year = row["ROK_PROD"].ToString();
DirectoryInfo directory = new DirectoryInfo("C:/Users/g580/Documents/Programming/C++, C#/C#/Applications/RentCar/RentCar/RentCar/bin/Debug/Cars");
DirectoryInfo[] directories = directory.GetDirectories();
Bitmap img;
DirectoryInfo d = new DirectoryInfo(@"C:/Users/g580/Documents/Programming/C++, C#/C#/Applications/RentCar/RentCar/RentCar/bin/Debug/Cars/" + hierKomm + " " + dieSonne + " " + year);//Assuming Test is your Folder
FileInfo[] Files = d.GetFiles("*.bmp");
string str = "";
str = Files[0].Name;
img = new Bitmap(@"C:/Users/g580/Documents/Programming/C++, C#/C#/Applications/RentCar/RentCar/RentCar/bin/Debug/Cars/" + hierKomm + " " + dieSonne + " " + year + "/" + str);
var lines = File.ReadLines(@"C:/Users/g580/Documents/Programming/C++, C#/C#/Applications/RentCar/RentCar/RentCar/bin/Debug/Cars/" + hierKomm + " " + dieSonne + " " + year + "/auto.txt");
int counter = 0;
string marka = "", model = "";
foreach (var line in lines)
{
if (counter == 0)
{
marka = line.ToString().Trim();
}
if (counter == 1)
{
model = line.ToString().Trim();
}
counter++;
}
dataGridView1.Rows.Add(img, marka, model, year);
}
}
catch
{
}
conn.CloseConnection();
}
public static bool AddUpdateDeleteStudent(Ogrenci ogrenci, int Choose)
{
try
{
Connection ConnectAndRun = new Connection();
using (ConnectAndRun.OpenConnection())
{
string Query = null;
if (Choose == 0)
{
Query = "INSERT INTO Ogrenci values (@Ad, @Soyad, @Numara, @Cinsiyet, @FakulteId, @BolumId, @AlinanDersler, @KullaniciAdi, @Sifre, @Onay, @Aktif, @Donem, @OnceOnay)";
}
else if (Choose == 1)
{
Query = "UPDATE Ogrenci set Ad = @Ad, Soyad = @Soyad, Numara = @Numara, Cinsiyet = @Cinsiyet where Id = @Id";
}
else
{
Query = "UPDATE Ogrenci set Aktif = 0 where Id = @Id";
}
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query);
if (Choose == 0)
{
Cmd.Parameters.Add("@Ad", SqlDbType.NVarChar).Value = ogrenci.Ad;
Cmd.Parameters.Add("@Soyad", SqlDbType.NVarChar).Value = ogrenci.Soyad;
Cmd.Parameters.Add("@Numara", SqlDbType.NChar).Value = ogrenci.Numara;
Cmd.Parameters.Add("@Cinsiyet", SqlDbType.Bit).Value = ogrenci.Cinsiyet;
Cmd.Parameters.Add("@FakulteId", SqlDbType.Int).Value = ogrenci.FakulteId;
Cmd.Parameters.Add("@BolumId", SqlDbType.Int).Value = ogrenci.BolumId;
Cmd.Parameters.Add("@AlinanDersler", SqlDbType.NVarChar).Value = ogrenci.AlinanDersler;
Cmd.Parameters.Add("@KullaniciAdi", SqlDbType.NVarChar).Value = ogrenci.KullaniciAdi;
Cmd.Parameters.Add("@Sifre", SqlDbType.NVarChar).Value = ogrenci.Sifre;
Cmd.Parameters.Add("@Onay", SqlDbType.Bit).Value = ogrenci.Onay;
Cmd.Parameters.Add("@Aktif", SqlDbType.Bit).Value = ogrenci.Aktif;
Cmd.Parameters.Add("@Donem", SqlDbType.TinyInt).Value = ogrenci.Donem;
Cmd.Parameters.Add("@OnceOnay", SqlDbType.Bit).Value = false;
}
else if (Choose == 1)
{
Cmd.Parameters.Add("@Ad", SqlDbType.NVarChar).Value = ogrenci.Ad;
Cmd.Parameters.Add("@Soyad", SqlDbType.NVarChar).Value = ogrenci.Soyad;
Cmd.Parameters.Add("@Numara", SqlDbType.NChar).Value = ogrenci.Numara;
Cmd.Parameters.Add("@Cinsiyet", SqlDbType.Bit).Value = ogrenci.Cinsiyet;
Cmd.Parameters.Add("@Id", SqlDbType.Int).Value = ogrenci.Id;
}
else
{
Cmd.Parameters.Add("@Id", SqlDbType.Int).Value = ogrenci.Id;
Cmd.Parameters.Add("@Aktif", SqlDbType.Bit).Value = ogrenci.Aktif;
}
Cmd.ExecuteNonQuery();
}
if (Choose == 0)
{
int Id = 0;
using (ConnectAndRun.OpenConnection())
{
SqlCommand Cmd = ConnectAndRun.CreateQuery("select max(ID) as Id from ogrenci");
SqlDataReader sqlDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
Id = Convert.ToInt32(sqlDataReader["Id"]);
}
}
int dummy = Id;
int basamak = 0;
string Add;
while (dummy >= 1)
{
dummy /= 10;
basamak++;
}
if (basamak == 1)
{
Add = "0" + Id.ToString();
}
else
{
Add = Id.ToString();
}
using (ConnectAndRun.OpenConnection())
{
string Query2 = "UPDATE ders set AlanOgrenciler = CONCAT(AlanOgrenciler, @AlanOgrenciler) where Id = @Id";
List <int> DersIds = new List <int>();
string dummyString = null;
for (int i = 0; i < ogrenci.AlinanDersler.Length; i += 2)
{
dummyString = ogrenci.AlinanDersler.Substring(i, 2);
DersIds.Add(Convert.ToInt32(dummyString));
}
for (int i = 0; i < DersIds.Count; i++)
{
using (ConnectAndRun.OpenConnection())
{
SqlCommand Cmd2 = ConnectAndRun.CreateQuery(Query2);
Cmd2.Parameters.Add("@AlanOgrenciler", SqlDbType.NVarChar).Value = Add;
Cmd2.Parameters.Add("@Id", SqlDbType.Int).Value = DersIds[i];
Cmd2.ExecuteNonQuery();
}
}
}
}
return(true);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
return(false);
}
}
public static bool AddLessons(int Id, string Classes)
{
try
{
Connection ConnectAndRun = new Connection();
using (ConnectAndRun.OpenConnection())
{
string Query = "UPDATE Ogrenci set AlinanDersler = @AlinanDersler, OnceOnay = 1 where Id = @Id";
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query);
Cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
Cmd.Parameters.Add("@AlinanDersler", SqlDbType.NVarChar).Value = Classes;
Cmd.ExecuteNonQuery();
}
int dummy = Id;
int basamak = 0;
string Add;
while (dummy >= 1)
{
dummy /= 10;
basamak++;
}
if (basamak == 1)
{
Add = "0" + Id.ToString();
}
else
{
Add = Id.ToString();
}
string Query2 = "UPDATE ders set AlanOgrenciler = CONCAT(AlanOgrenciler, @AlanOgrenciler) where Id = @Id";
List <int> DersIds = new List <int>();
string dummyString = null;
for (int i = 0; i < Classes.Length; i += 2)
{
dummyString = Classes.Substring(i, 2);
DersIds.Add(Convert.ToInt32(dummyString));
}
for (int i = 0; i < DersIds.Count; i++)
{
using (ConnectAndRun.OpenConnection())
{
SqlCommand Cmd = ConnectAndRun.CreateQuery(Query2);
Cmd.Parameters.Add("@AlanOgrenciler", SqlDbType.NVarChar).Value = Add;
Cmd.Parameters.Add("@Id", SqlDbType.Int).Value = DersIds[i];
Cmd.ExecuteNonQuery();
}
}
return(true);
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
return(false);
}
}
protected void btnSave_Click(object sender, ImageClickEventArgs e)
{
try
{
DataTable dt = new DataTable();
if (HidAutoId.Value == "")
{
string sql = "select LoginID from Com_Login_Mst where LoginID ='" + txtLoginId.Text.Trim() + "'";
dt = com.executeSqlQry(sql);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["LoginID"].ToString().ToLower() == txtLoginId.Text.Trim().ToLower())
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, "Login ID is already created.", 125, 300);
return;
}
}
dt = null;
}
else
{
if (ViewState["LoginId"].ToString().ToLower() != txtLoginId.Text.Trim().ToLower())
{
string sql = "select LoginID from Com_Login_Mst where LoginID ='" + txtLoginId.Text.Trim() + "'";
dt = com.executeSqlQry(sql);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["LoginID"].ToString().ToLower() == txtLoginId.Text.Trim().ToLower())
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, "Login ID is already created.", 125, 300);
return;
}
}
dt = null;
}
}
#region Line Item Records
DataTable dtlineitem = new DataTable();
dtlineitem = (DataTable)ViewState["Group"];
int TotalLineItem = dtlineitem.Rows.Count;
if (dtlineitem.Rows.Count > 0)
{
for (int i = TotalLineItem; i > 0; i--)
{
dtlineitem.Rows.RemoveAt(i - 1);
}
}
for (int i = 0; i < Convert.ToInt32(ViewState["TotalGroup"].ToString()); i++)
{
if (listBoxUserGroup.Items[i].Selected == true)
{
DataRow objdrLineItem = dtlineitem.NewRow();
objdrLineItem["GroupID"] = com.STRToInt(listBoxUserGroup.Items[i].Value);
dtlineitem.Rows.Add(objdrLineItem);
}
}
if (dtlineitem.Rows.Count == 0)
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, "Select atleast one user group.", 125, 300);
return;
}
#endregion
objConnectionClass.OpenConnection();
SqlCommand cmd;
cmd = new SqlCommand();
cmd.Connection = objConnectionClass.PolypexSqlConnection;
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.StoredProcedure;
if (HidAutoId.Value == "")
{
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = 0;
}
else
{
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(HidAutoId.Value);
}
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = txtEmployeeName.Text.Trim();
cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = Convert.ToInt32(ddlEmployeeLocation.SelectedValue);
cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = txtEmailID.Text.Trim();
cmd.Parameters.Add("@LoginID", SqlDbType.VarChar).Value = txtLoginId.Text.Trim();
//Added by Lalit 9July 2013
cmd.Parameters.Add("@UserTypeId", SqlDbType.VarChar).Value = DdlUserType.SelectedValue;
//End
#region Table Parameter
cmd.Parameters.AddWithValue("@dtLineItemsOfGroup", dtlineitem);
#endregion
cmd.Parameters.Add("@ActiveStatus", SqlDbType.Bit).Value = ChkStatus.Checked;
cmd.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = Convert.ToInt32(Session["UserId"].ToString());
cmd.Parameters.Add("@ModifiedBy", SqlDbType.Int).Value = Convert.ToInt32(Session["UserId"].ToString());
cmd.Parameters.Add(new SqlParameter("@ErrorStatus", SqlDbType.VarChar, 10));
cmd.Parameters["@ErrorStatus"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@NewEmployeeCode", SqlDbType.VarChar, 30);
cmd.Parameters["@NewEmployeeCode"].Direction = ParameterDirection.Output;
cmd.CommandText = "SP_InsertUpdate_In_Com_UserAndLogin_Mst";
cmd.ExecuteNonQuery();
ErrorStatus = cmd.Parameters["@ErrorStatus"].Value.ToString();
RecordNo = cmd.Parameters["@NewEmployeeCode"].Value.ToString();
if (ErrorStatus == "0")
{
if (RecordNo != "0")
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordSaved + ". User Code is:" + RecordNo, 125, 300);
}
else
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordSaved, 125, 300);
}
#region Clear All records after save
ClearAll();
#endregion
}
else
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordNotSaved, 125, 300);
return;
}
ErrorStatus = "";
RecordNo = "";
}
catch (Exception ex) {
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordNotSaved, 125, 300);
}
}
protected void ImgBtnSave_Click(object sender, ImageClickEventArgs e)
{
/// <summary>
/// This event is used to save all records in database.
/// </summary>
try
{
#region Check if mapping exist in table***************************************
if (HidAutoId.Value == "0")
{
DataTable dt = new DataTable();
string query = @"select AutoId from tblWarehouseMapping where WareHouseId ='" + HidWarehouseId.Value + "' and CustomerId ='" + HidCustomerId.Value + "'";
dt = com.executeSqlQry(query);
if (dt.Rows.Count > 0)
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, "Mapping is already exist. Please check it.", 125, 300);
return;
}
dt = null;
}
#endregion ********************************************************************
objConnectionClass.OpenConnection();
SqlCommand cmd;
cmd = new SqlCommand();
cmd.Connection = objConnectionClass.PolypexSqlConnection;
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.StoredProcedure;
#region All Parameters
if (HidAutoId.Value == "0")
{
cmd.Parameters.Add("@AutoId", SqlDbType.Int).Value = 0;
}
else
{
cmd.Parameters.Add("@AutoId", SqlDbType.Int).Value = com.STRToInt(HidAutoId.Value);
}
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = com.STRToInt(HidCustomerId.Value);
cmd.Parameters.Add("@PlantId", SqlDbType.Int).Value = com.STRToInt(ddlPlant.SelectedValue);
cmd.Parameters.Add("@WareHouseId", SqlDbType.Int).Value = com.STRToInt(HidWarehouseId.Value);
cmd.Parameters.Add("@ActiveStatus", SqlDbType.Bit).Value = chkActive.Checked;
cmd.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = com.STRToInt(Session["UserId"].ToString());
cmd.Parameters.Add("@ModifiedBy", SqlDbType.Int).Value = com.STRToInt(Session["UserId"].ToString());
cmd.Parameters.Add(new SqlParameter("@ErrorStatus", SqlDbType.VarChar, 10));
cmd.Parameters["@ErrorStatus"].Direction = ParameterDirection.Output;
cmd.CommandText = "SP_InsertUpdate_In_tblWarehouseMapping";
cmd.ExecuteNonQuery();
ErrorStatus = cmd.Parameters["@ErrorStatus"].Value.ToString();
if (ErrorStatus == "0")
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordSaved, 125, 300);
#region Clear All records after save
ClearFields();
makeGrid();
#endregion
}
else
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordNotSaved, 125, 300);
}
ErrorStatus = "";
#endregion
}
catch
{
MyMessageBoxInfo.Show(MyMessageBox.MessageType.Info, objcommonmessage.RecordNotSaved, 125, 300);
}
finally
{
objConnectionClass.CloseConnection();
}
}
private void button3_Click(object sender, EventArgs e)
{
if (button3.Text.Equals("Kliknij by zmienić dane"))
{
button3.Text = "Zatwierdź zmiany";
textBox1.Enabled = true; textBox1.ReadOnly = false;
textBox2.Enabled = true; textBox2.ReadOnly = false;
textBox3.Enabled = true; textBox3.ReadOnly = false;
textBox4.Enabled = true; textBox4.ReadOnly = false;
textBox5.Enabled = true; textBox5.ReadOnly = false;
textBox7.Enabled = true; textBox7.ReadOnly = false;
textBox8.Enabled = true; textBox8.ReadOnly = false;
textBox9.Enabled = true; textBox9.ReadOnly = false;
}
else if (button3.Text.Equals("Zatwierdź zmiany"))
{
String updateChanges = "UPDATE `klienci` SET `IMIE`= @imie,`NAZWISKO`= @nazw,`NR_PRAWA_JAZDY`= @nr,`KATEGORIA_PJ`= @kat,`MIEJSCOWOSC`= @miejsc,`ULICA`= @ul WHERE ID_KLI = @ID";
String updatePass = "******";
Connection conn = new Connection();
DataTable dtReg = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
MySqlCommand command2 = new MySqlCommand();
conn.OpenConnection();
command.CommandText = updateChanges;
command.Connection = conn.GetConnection();
command.Parameters.Add("@imie", MySqlDbType.VarChar).Value = textBox1.Text;
command.Parameters.Add("@nazw", MySqlDbType.VarChar).Value = textBox2.Text;
command.Parameters.Add("@nr", MySqlDbType.VarChar).Value = textBox4.Text;
command.Parameters.Add("@kat", MySqlDbType.VarChar).Value = textBox3.Text;
command.Parameters.Add("@miejsc", MySqlDbType.VarChar).Value = textBox8.Text;
command.Parameters.Add("@ul", MySqlDbType.VarChar).Value = textBox7.Text;
command.Parameters.Add("@ID", MySqlDbType.Int16).Value = Convert.ToInt16(Form1.gimme());
command.ExecuteNonQuery();
if (textBox9.Text != "")
{
if (textBox9.Text.Equals(textBox5.Text))
{
command2.CommandText = updatePass;
command2.Connection = conn.GetConnection();
command2.Parameters.Add("@haslo", MySqlDbType.VarChar).Value = textBox9.Text;
command2.Parameters.Add("@ID", MySqlDbType.Int16).Value = Convert.ToInt16(Form1.gimme());
command2.ExecuteNonQuery();
MessageBox.Show("Password changed correctly");
}
else
{
MessageBox.Show("Passwords are not equal!");
}
}
conn.CloseConnection();
Init();
button3.Text = "Kliknij by zmienić dane";
}
}
private void button4_Click(object sender, EventArgs e)
{
Connection conn = new Connection();
DataTable dtReg = new DataTable();
DataTable dtReg2 = new DataTable();
MySqlDataAdapter msda = new MySqlDataAdapter();
MySqlCommand msc = new MySqlCommand();
MySqlCommand msc2 = new MySqlCommand();
String Question = "SELECT * FROM `uzytkownicy` WHERE `LOGIN` = @RegName";
String MaxValue = "SELECT MAX(ID_UZYT+1) FROM uzytkownicy";
String InsertValues = "INSERT INTO `uzytkownicy` VALUES (@ID, @InUser, @InPass)";
String InsertClientData = "INSERT INTO `klienci` VALUES (@ID, @UserName, @UserSurname, @DrivingNo, 'B', @City, @Street)";
String maxVal = "";
conn.OpenConnection();
msc.CommandText = MaxValue;
msc.Connection = conn.GetConnection();
msda.SelectCommand = msc;
msda.Fill(dtReg2);
if (dtReg2.Rows.Count != 0)
{
DataRow row = dtReg2.Rows[0];
maxVal = row["MAX(ID_UZYT+1)"].ToString();
}
msc.CommandText = Question;
msc.Connection = conn.GetConnection();
msc.Parameters.Add("@RegName", MySqlDbType.VarChar).Value = textBox8.Text;
msda.SelectCommand = msc;
if (textBox3.Text.Equals("") || textBox4.Text.Equals("") || textBox5.Text.Equals("") || textBox6.Text.Equals("") || textBox7.Text.Equals("") || textBox8.Text.Equals("") || textBox9.Text.Equals("") || textBox10.Text.Equals(""))
{
MessageBox.Show("Fill all gaps!");
}
else
{
if (!textBox9.Text.Equals(textBox10.Text))
{
MessageBox.Show("Check if you write the same passwords");
}
else
{
try
{
msda.Fill(dtReg);
if (dtReg.Rows.Count != 0)
{
MessageBox.Show("Given username already exists!", "REGISTER ERROR!", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (textBox9.Text.Trim().Equals("") || textBox9.Text.Trim().Equals(""))
{
MessageBox.Show("Fill all gaps!", "DATA ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
msc.CommandText = InsertValues;
msc.Connection = conn.GetConnection();
msc.Parameters.Add("@ID", MySqlDbType.VarChar).Value = maxVal;
msc.Parameters.Add("@InUser", MySqlDbType.VarChar).Value = textBox8.Text;
msc.Parameters.Add("@InPass", MySqlDbType.VarChar).Value = textBox9.Text;
msc2.CommandText = InsertClientData;
msc2.Connection = conn.GetConnection();
msc2.Parameters.Add("@ID", MySqlDbType.VarChar).Value = maxVal;
msc2.Parameters.Add("@UserName", MySqlDbType.VarChar).Value = textBox3.Text;
msc2.Parameters.Add("@UserSurname", MySqlDbType.VarChar).Value = textBox4.Text;
msc2.Parameters.Add("@DrivingNo", MySqlDbType.VarChar).Value = textBox5.Text;
msc2.Parameters.Add("@City", MySqlDbType.VarChar).Value = textBox6.Text;
msc2.Parameters.Add("@Street", MySqlDbType.VarChar).Value = textBox7.Text;
if ((msc.ExecuteNonQuery() == 1) && (msc2.ExecuteNonQuery() == 1))
{
conn.CloseConnection();
MessageBox.Show("Welcome!", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
setToLogin();
}
else
{
MessageBox.Show("Something goes wrong! Try again!", "Failure", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch
{
MessageBox.Show("Something goes wrong! Try again later!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
conn.CloseConnection();
}