public static void ReadFromDatabas(string tableName) { SqlConnection mycon = Conection.MakeCon(); List <Animal> mylist = new List <Animal>(); String select = "select * from " + tableName; SqlCommand cmd = new SqlCommand(select, mycon); mycon.Open(); SqlDataReader Result = cmd.ExecuteReader(); while (Result.Read()) { Animal a = new Animal(); a.PatientID = Result.GetInt32(0); a.Name = Result.GetString(1); a.Type = Result.GetString(2); a.Dob = Result.GetDateTime(3); a.OwnerID = Result.GetInt32(4); mylist.Add(a); } mycon.Close(); foreach (Animal x in mylist) { Console.WriteLine(x.PatientID + " " + x.Name + " " + x.Type + " " + x.Dob + " " + x.OwnerID); } }
public static void DeleteRow(string tableName, string columnName, int id) { SqlConnection mycon = Conection.MakeCon(); string deleteById = "DELETE FROM " + tableName + " WHERE " + columnName + "=" + id; SqlCommand cmd = new SqlCommand(deleteById, mycon); mycon.Open(); cmd.ExecuteNonQuery(); mycon.Close(); }
protected void UpdatePatient(int id, string tableName, ArrayList values, List <string> keys) { SqlConnection mycon = Conection.MakeCon(); string build = ""; for (int i = 0; i < keys.Count; i++) { if (i == keys.Count - 1) { build += keys[i] + " = @" + keys[i]; } else { build += keys[i] + " = @" + keys[i] + ","; } } string update = "update Animal set " + build + " where patientId = " + id; // "update " + tableName + "SET "+build+" where patientId = "+ id ; SqlCommand cmd = new SqlCommand(update, mycon); for (int i = 0; i < keys.Count; i++) { cmd.Parameters.AddWithValue("@" + keys[i], values[i]); } try { mycon.Open(); cmd.ExecuteNonQuery(); mycon.Close(); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { Console.WriteLine(ex.Errors[i].Message); Console.WriteLine(ex.Errors[i].LineNumber); Console.WriteLine(ex.Errors[i].Source); Console.WriteLine(ex.Errors[i].Number); } } }
protected int InsertIntoDatabase(string tableName, ArrayList values, List <string> keys) { mycon = Conection.MakeCon(); string fieldNames = string.Join(",", keys); string parameters = "@" + string.Join(",@", keys); string quary = "INSERT INTO " + tableName + "(" + fieldNames + ")" + " OUTPUT INSERTED.PatientID " + "VALUES" + "(" + parameters + ") "; SqlCommand cmd = new SqlCommand(quary, mycon); for (int i = 0; i < keys.Count; i++) { cmd.Parameters.AddWithValue("@" + keys[i], values[i]); } try { mycon.Open(); int ID = (int)cmd.ExecuteScalar(); mycon.Close(); return(ID); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { Console.WriteLine(ex.Errors[i].Message); Console.WriteLine(ex.Errors[i].LineNumber); Console.WriteLine(ex.Errors[i].Source); Console.WriteLine(ex.Errors[i].Number); } } return(0); }