public static List<Vendor> GetVendor() { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; List<Vendor> vendorList = new List<Vendor>(); string vendorSQL = "SELECT vendor_id, vendor_name, vendor_phone FROM Vendor"; SqlCommand objVCommand = null; SqlConnection objVConn = null; SqlDataReader vendReader = null; try { using (objVConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objVConn.Open(); //Command object created with the SQL statement using (objVCommand = new SqlCommand(vendorSQL, objVConn)) { //Execute the SQL and return a DataReader using ((vendReader = objVCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (vendReader.Read()) { Vendor objVendor = new Vendor(); objVendor.VendorId = vendReader["vendor_id"].ToString(); objVendor.VendorName = vendReader["vendor_name"].ToString(); objVendor.VendorPhone = vendReader["vendor_phone"].ToString(); //Add Vendor to collection vendorList.Add(objVendor); } } } return vendorList; } } catch (SqlException ex) { throw ex; } finally { if (objVConn != null) { objVConn.Close(); } } }
private void btnDelVendor_Click(object sender, EventArgs e) { if (txtVendorID.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Vendor ID #.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtVendorID.Focus(); return; } Vendor objVendor = new Vendor(); objVendor.VendorId = txtVendorID.Text.Trim();; try { bool status = VendorDB.DeleteVendor(objVendor); if (status) //You can use this syntax as well..if (status ==true) { MessageBox.Show("Vendor deleted from the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); ClearVendorFields(); VendorListLoad(); } else { MessageBox.Show("Vendor was not deleted from the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public static Vendor GetVendor(int vendorNumber) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; string vendorSQL = "SELECT vendor_id, vendor_name, vendor_phone FROM Vendor" + " WHERE vendor_id = @vendor_id"; ; SqlCommand objVendCommand = null; SqlConnection objVendConn = null; SqlDataReader vendReader = null; Vendor objVendor = null; try { using (objVendConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objVendConn.Open(); //Create a command object with the SQL statement using (objVendCommand = new SqlCommand(vendorSQL, objVendConn)) { //Set command parameter objVendCommand.Parameters.AddWithValue("@vendor_id", vendorNumber); //Execute the SQL and return a DataReader using ((vendReader = objVendCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (vendReader.Read()) { objVendor = new Vendor(); //Fill the customer object if found objVendor.VendorId = vendReader["vendor_id"].ToString(); objVendor.VendorName = vendReader["vendor_name"].ToString(); objVendor.VendorPhone = vendReader["vendor_phone"].ToString(); } } } return objVendor; } } catch (SqlException ex) { throw ex; } finally { if (objVendConn != null) { objVendConn.Close(); } } }
public static bool DeleteVendor(Vendor objVendor) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; int rowsAffected = 0; SqlConnection objVendConn = null; SqlCommand objVendCommand = null; string VendorSQL; try { using (objVendConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objVendConn.Open(); VendorSQL = "DELETE Vendor WHERE vendor_id = @vendor_id"; //Create a command object with the SQL statement using (objVendCommand = new SqlCommand(VendorSQL, objVendConn)) { //Use the command parameters method to set the paramater values of the SQL Insert statement objVendCommand.Parameters.AddWithValue("@vendor_id", objVendor.VendorId); //Execute the SQL and return the number of rows affected rowsAffected = objVendCommand.ExecuteNonQuery(); //Close the database connection objVendConn.Close(); if (rowsAffected > 0) { return true; } else { return false; } } } } catch (SqlException ex) { throw ex; } finally { if (objVendConn != null) { objVendConn.Close(); } } }
public static bool UpdateVendor(Vendor objVendor) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; int rowsAffected = 0; string vendorSQL; SqlCommand objVendCommand = null; SqlConnection objVendConn = null; try { using (objVendConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objVendConn.Open(); vendorSQL = "UPDATE Vendor " + Environment.NewLine + " SET vendor_id = @vendor_id, " + Environment.NewLine + " vendor_name = @vendor_name, " + Environment.NewLine + " vendor_phone = @vendor_phone " + Environment.NewLine + " WHERE vendor_id = @vendor_id "; //Create a command object with the SQL statement using (objVendCommand = new SqlCommand(vendorSQL, objVendConn)) { //Use the command parameters method to set the paramater values of the SQL Insert statement objVendCommand.Parameters.AddWithValue("@vendor_id", Convert.ToInt16(objVendor.VendorId)); objVendCommand.Parameters.AddWithValue("@vendor_name", objVendor.VendorName); objVendCommand.Parameters.AddWithValue("@vendor_phone", objVendor.VendorPhone); //Execute the SQL and return the number of rows affected rowsAffected = objVendCommand.ExecuteNonQuery(); } if (rowsAffected > 0) { return true; } else { return false; } } } catch (SqlException ex) { throw ex; } finally { if (objVendConn != null) { objVendConn.Close(); } } }