//Add a topic to sql server public bool Add(Topic t) { //connect to DB SqlConnection con = new SqlConnection(); con.ConnectionString = conStr; con.Open(); //save t into DB string sql = "INSERT INTO Topics (TopicID, TopicName) "+ "VALUES (@TopicID, @TopicName)"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql; cmd.Connection = con; //define query parameters cmd.Parameters.Add("TopicID", SqlDbType.NVarChar); cmd.Parameters.Add("TopicName", SqlDbType.NVarChar); //set value to parameters cmd.Parameters["TopicID"].Value = t.TopicID; cmd.Parameters["TopicName"].Value = t.TopicName; //execute query int ret=cmd.ExecuteNonQuery(); con.Close(); //return success status return ret == 1; }
//========================================================================================== //==================================Button functions======================================== //========================================================================================== private void btAdd_Click(object sender, EventArgs e) { //validate inputs if (topicIDList.Contains(txtTopicID.Text)==false) { //create Topic object Topic t = new Topic(); t.TopicID = this.txtTopicID.Text; t.TopicName = this.txtTopicName.Text; //insert topic object into Topic table BOTopic bot = new BOTopic(); bool success = bot.Add(t); if (success) { MessageBox.Show("Add topic success!"); loadTopicList(); } else MessageBox.Show("Failed to add a topic!"); txtTopicID.Clear(); txtTopicName.Clear(); } else MessageBox.Show("The topic code is exist"); }
private void btUpdate_Click(object sender, EventArgs e) { int i = lbTopic.SelectedIndex; if (i == -1) { MessageBox.Show("Select a topic to change"); return; } else { string oldTopicID = topicList[i].TopicID; BOTopic bot = new BOTopic(); Topic t = new Topic(txtTopicID.Text, txtTopicName.Text); bool success =bot.Update(t, oldTopicID); if (success) { MessageBox.Show("Update topic success!"); loadTopicList(); } else MessageBox.Show("Failed to update a topic!"); txtTopicID.Clear(); txtTopicName.Clear(); btUpdate.Enabled = false; btDelete.Enabled = false; loadTopicList(); } }
//Load all topic in sql server public List<Topic> ListAll() { List<Topic> list = new List<Topic>(); //connect to DB SqlConnection con = new SqlConnection(); con.ConnectionString = conStr; con.Open(); //Select all topics string sql = "SELECT * FROM Topics"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql; cmd.Connection = con; //execute SqlDataReader dr = cmd.ExecuteReader(); //add to list while (dr.Read()) { //create topic Topic t = new Topic(); t.TopicID = dr["TopicID"].ToString(); t.TopicName = dr["TopicName"].ToString(); //add to list list.Add(t); } dr.Close(); con.Close(); return list; }
//Update a topic in sql server public bool Update(Topic t, string oldTopicID) { //connect to DB SqlConnection con = new SqlConnection(); con.ConnectionString = conStr; con.Open(); //update t in DB using (SqlCommand cmd = new SqlCommand("UPDATE Topics SET TopicID=@NewTopicID, TopicName = @NewTopicName" + " WHERE TopicID = @OldTopicID", con)) { //set value to parameters cmd.Parameters.AddWithValue("@NewTopicID", t.TopicID); cmd.Parameters.AddWithValue("@NewTopicName", t.TopicName); cmd.Parameters.AddWithValue("@OldTopicID", oldTopicID); //execute query int ret = cmd.ExecuteNonQuery(); con.Close(); //return success status return ret == 1; } }