List Box Control Database Access

FOR SECURE DB TRANSACTION REFER TO THE MAY 2009,CODES SECTION:

Use the namespace of the ADO.NET SQL Server Data Provider :

using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page{
private string connectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";
private string[] employeeFields = new string[3];
private char[] splitChar = new char[1] { ',' };

protected void Page_Load(object sender, EventArgs e){
if (!IsPostBack){
PopulateList(false);
}
}

protected void ListBox1_SelectedIndexChanged(object sender,
EventArgs e){
employeeFields = ListBox1.SelectedItem.Value.Split
(splitChar);
EmployeeIDTextBox.Text = employeeFields[0];
LastNameTextBox.Text = employeeFields[1];
FirstNameTextBox.Text = employeeFields[2];
}

protected void GetEmployeesRecords(){
try{
using (SqlConnection connection = new SqlConnection
(connectionString)){
string commandText = "SELECT EmployeeID, LastName,
FirstName FROM Employees";
SqlCommand command = new SqlCommand(commandText,
connection);

connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader
()){
while (dataReader.Read()){
ListBox1.Items.Add(dataReader["EmployeeID"] + "," +
dataReader["LastName"] + "," + dataReader["FirstName"]);
}
}
}
}
catch (Exception ex){
MessageLabel.Text = ex.Message;
}
}

private void PopulateList(bool operation){
GetEmployeesRecords();
if (operation)
ListBox1.Items[ListBox1.Items.Count - 1].Selected = true;
else
ListBox1.Items[0].Selected = true;

ListBox1_SelectedIndexChanged(this, EventArgs.Empty);

}

protected void NewEmployeeButton_Click(object sender, EventArgs e){
if (LastNameTextBox.Text != String.Empty &
FirstNameTextBox.Text != String.Empty){
InsertNewEmployee();
DeleteEmployeeButton.Enabled = true;
}
}
protected void DeleteEmployeeButton_Click(object sender,
EventArgs e){
DeleteEmployee();
}

private void InsertNewEmployee(){
try{
using (SqlConnection connection = new SqlConnection
(connectionString)){
string commandText = "INSERT INTO Employees(LastName,
FirstName) VALUES" + "(@LastName, @FirstName)";
SqlCommand command = new SqlCommand(commandText,
connection);

SqlParameter firstParameter = new SqlParameter();
firstParameter.ParameterName = "@LastName";
firstParameter.Value = LastNameTextBox.Text;
command.Parameters.Add(firstParameter);

SqlParameter secondParameter = new SqlParameter();
secondParameter.ParameterName = "@FirstName";
secondParameter.Value = FirstNameTextBox.Text;
command.Parameters.Add(secondParameter);

//command.Parameters.AddWithValue("@LastName",
LastNameTextBox.Text);
//command.Parameters.AddWithValue("@FirstName",
FirstNameTextBox.Text);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1){
ListBox1.Enabled = true;
ListBox1.Items.Clear();
PopulateList(true);
}
NewEmployeeButton.Enabled = false;
NewButton.Enabled = true;
}
}
catch (Exception ex){
MessageLabel.Text = ex.Message;
}
}

private void DeleteEmployee(){
try{
using (SqlConnection connection = new SqlConnection
(connectionString)){
string commandText = "DELETE FROM Employees WHERE
EmployeeID = @EmployeeID";
SqlCommand command = new SqlCommand(commandText,
connection);

SqlParameter parameter = new SqlParameter("@EmployeeID",
EmployeeIDTextBox.Text);
command.Parameters.Add(parameter);

connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1){
ListBox1.Items.Clear();
PopulateList(true);
}
}
}
catch (Exception ex){
MessageLabel.Text = ex.Message;
}
}

protected void NewButton_Click(object sender, EventArgs e){
ListBox1.Enabled = false;
ListBox1.SelectedIndex = -1;
EmployeeIDTextBox.Text = "You can't enter a value here";
LastNameTextBox.Text = String.Empty;
FirstNameTextBox.Text = String.Empty;
NewButton.Enabled = false;
NewEmployeeButton.Enabled = true;
DeleteEmployeeButton.Enabled = false;
}
}

When you run the page, after replacing the auto-generated code with the above code, you will get a populated list of employees and, as in the previous article, when you select another employee the text boxes are updated to reflect that new selection as shown in the next screen shot.

When you click the New Employee button on the ListBox control, the New Employee button itself and the DELETE Employee button will all be disabled, and the Insert Employee button will be enabled with empty text boxes so you can enter the first name and the last name of the employee as shown in the next screen shot.

Enter the first name and the last name for the new employee.

Note that you don't have any choice that allows you to cancel the operation, except closing the browser or navigating to another website. If you want this behavior you can provide a cancel button, and in its click event handler enable the controls and set the ListBox-selected index to the first item or the last item or something similar. When you click on the Insert Employee button, the new employee record will be entered in the database's Employees table and the ListBox's SelectedIndex has been set to that new employee item.

Your result maybe different from mine if you have made changes to the Northwind database. I myself make a lot of changes to this database, especially if I'm writing an article, so don't worry about it as long as it's working as it should be. You can download the T-SQL script that creates this database from Microsoft's web site, if you haven't done that already. Now you can delete that new record by clicking the DELETE Employee button.

Note how the ListBox control selects the last item only when you INSERT or DELETE a record, but when the page is first loaded it's set to the first item on the list.