//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;
}
}