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(); }