public DataTable Select(Exercise_Info_Class d) { SqlConnection conn = new SqlConnection(myconnstrng); DataTable dt = new DataTable(); try { // string sql = "INSERT INTO Exercise_Info(Customer_Name,Address,Phone_No,Email_Id,Gender,Age,Biceps_Size,Calf_Size,Chest_Size,Hip_Size,Weight,Height,Fat_Percent,Package,Personal_Trainer,Time_Preference,BMI) VALUES(@Customer_Name, @Address, @Phone_No, @Email_Id, @Gender, @Age, @Biceps_Size, @Calf_Size, @Chest_Size, @Hip_Size,@Weight,@Height,@Fat_Percent,@Package,@Personal_Trainer,@Time_Preference,@BMI)"; string sql = "Select Exercise , Daily_Expected_Count FROM Exercise_Info WHERE Body_Parts= @Body_Parts "; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Body_Parts", d.Body_Parts); SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); int rows = cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException sqlException) { System.Windows.Forms.MessageBox.Show(sqlException.Message); } finally { conn.Close(); } return(dt); }
public bool Update(Exercise_Info_Class c) { bool isSuccess = true; SqlConnection conn = new SqlConnection(myconnstrng); try { // string sql = "INSERT INTO Customer_Info(Customer_Name,Address,Phone_No,Email_Id,Gender,Age,Biceps_Size,Calf_Size,Chest_Size,Hip_Size,Weight,Height,Fat_Percent,Package,Personal_Trainer,Time_Preference,BMI) VALUES(@Customer_Name, @Address, @Phone_No, @Email_Id, @Gender, @Age, @Biceps_Size, @Calf_Size, @Chest_Size, @Hip_Size,@Weight,@Height,@Fat_Percent,@Package,@Personal_Trainer,@Time_Preference,@BMI)"; string sql = "UPDATE Exercise_Info SET Daily_Expected_Count=@Daily_Expected_Count WHERE Exercise=@UPDExercise "; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Exercise", c.Exercise); cmd.Parameters.AddWithValue("@UPDExercise", c.UPDExercise); cmd.Parameters.AddWithValue("@Daily_Expected_Count", c.Daily_Expected_Count); //---------------------------------------------------------------------------- conn.Open(); Console.WriteLine(sql); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (System.Data.SqlClient.SqlException sqlException) { System.Windows.Forms.MessageBox.Show(sqlException.Message); } finally { conn.Close(); } return(isSuccess); }
public bool Insert(Exercise_Info_Class c) { bool isSuccess = true; SqlConnection conn = new SqlConnection(myconnstrng); try { // string sql = "INSERT INTO Exercise_Info(Customer_Name,Address,Phone_No,Email_Id,Gender,Age,Biceps_Size,Calf_Size,Chest_Size,Hip_Size,Weight,Height,Fat_Percent,Package,Personal_Trainer,Time_Preference,BMI) VALUES(@Customer_Name, @Address, @Phone_No, @Email_Id, @Gender, @Age, @Biceps_Size, @Calf_Size, @Chest_Size, @Hip_Size,@Weight,@Height,@Fat_Percent,@Package,@Personal_Trainer,@Time_Preference,@BMI)"; string sql = "INSERT INTO Exercise_Info(Body_Parts,Exercise,Daily_Expected_Count) VALUES(@Body_Parts , @Exercise, @Daily_Expected_Count)"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Body_Parts", c.Body_Parts); cmd.Parameters.AddWithValue("@Exercise", c.Exercise); cmd.Parameters.AddWithValue("@Daily_Expected_Count", c.Daily_Expected_Count); //--------------------------------------------------------- if (c.Body_Parts.ToLower() == "shoulder") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Shoulder_Info ADD ' + @Exercise + ' NUMERIC(18,0) NULL ')"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "chest") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Chest_Info ADD ' + @Exercise + ' NUMERIC(18,0) NULL ')"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "back") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Back_Info ADD ' + @Exercise + ' NUMERIC(18,0) NULL ')"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "biceps") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Biceps_Info ADD ' + @Exercise + ' NUMERIC(18,0) NULL ')"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "triceps") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Triceps_Info ADD ' + @Exercise + ' NUMERIC(18,0) NULL ')"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "legs") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Cardio_Legs_Info ADD ' + @Exercise + ' NUMERIC(18,0) NULL ')"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } //-------------------------------------------------------- conn.Open(); int rows = cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (System.Data.SqlClient.SqlException sqlException) { System.Windows.Forms.MessageBox.Show(sqlException.Message); } finally { conn.Close(); } return(isSuccess); }
public bool Delete(Exercise_Info_Class c) { bool isSuccess = false; SqlConnection conn = new SqlConnection(myconnstrng); try { if (c.Body_Parts.ToLower() == "shoulder") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Shoulder_Info DROP COLUMN ' + @Exercise )"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "chest") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Chest_Info DROP COLUMN ' + @Exercise )"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "biceps") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Biceps_Info DROP COLUMN ' + @Exercise )"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "triceps") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Triceps_Info DROP COLUMN ' + @Exercise )"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "back") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Back_Info DROP COLUMN ' + @Exercise )"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } if (c.Body_Parts.ToLower() == "legs") { // sql2 = " 'ALTER TABLE Shoulder_Info ADD '+' @Exercise'+' VARCHAR(4)'"; sql2 = "EXEC ('ALTER TABLE Cardio_Legs_Info DROP COLUMN ' + @Exercise )"; cmd2 = new SqlCommand(sql2, conn); cmd2.Parameters.AddWithValue("@Exercise", c.Exercise); } string sql = "DELETE FROM Exercise_Info WHERE Exercise=@Exercise"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Exercise", c.Exercise); conn.Open(); int rows = cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (System.Data.SqlClient.SqlException sqlException) { System.Windows.Forms.MessageBox.Show(sqlException.Message); } finally { conn.Close(); } return(isSuccess); }