public Detail_form(int id)
{
InitializeComponent();
this.Id = id;
controller = new Controller();
dbDetailForm = new DBDetailForm(id);
}
public bool UpdateData(string tableName, DBDetailForm dbDetailForm)
{
bool output = false;
this.OpenConnection();
try {
string sqlString = "update " + tableName + " set "
+ "Name = @Name, Level = @Level, Deadline = @Deadline, Status = @Status, "
+ "Description = @Description, TaskList = @TaskList "
+ "where Id = @Id";
sqlCmd = new SqlCommand(sqlString, conn);
sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = dbDetailForm.Name;
sqlCmd.Parameters.Add("@Level", SqlDbType.Int).Value = dbDetailForm.Level;
sqlCmd.Parameters.Add("@Deadline", SqlDbType.Date).Value = dbDetailForm.Deadline;
sqlCmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = dbDetailForm.Status;
sqlCmd.Parameters.Add("@Description", SqlDbType.NVarChar, -1).Value = dbDetailForm.Description;
sqlCmd.Parameters.Add("@TaskList", SqlDbType.NVarChar, -1).Value = dbDetailForm.TaskList;
sqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = dbDetailForm.Id;
if (sqlCmd.ExecuteNonQuery() >= 1)
{
output = true;
}
} catch (Exception e) {
MessageBox.Show(e.ToString());
} finally {
this.CloseConnection();
}
return(output);
}
public event EventHandler eNew_Instance_Created; //Send to main to register event of new Detail_form instance
public Detail_form()
{
InitializeComponent();
controller = new Controller();
dbDetailForm = new DBDetailForm();
Id = -1;
}
private void detail_form_Load(object sender, EventArgs e)
{
//Load DB detail_form
//Id = ((Detail_form)sender).Id; //Use this to keep data always shows on 1 form
//dbDetailForm.Id = Id; //Use this to keep data always shows on 1 form
dbDetailForm = controller.QueryDataWithId(DBDetailForm.DB_TABLE_NAME, dbDetailForm);
txtName.Text = dbDetailForm.Name;
txtId.Text = dbDetailForm.Id.ToString();
txtLevel.Text = dbDetailForm.Level.ToString();
txtStatus.Text = dbDetailForm.Status;
txtDescription.Text = dbDetailForm.Description;
dtPickerDeadline.Value = dbDetailForm.Deadline;
txtParent.Tag = dbDetailForm.ParentId; //set Tag (Id) for txtParent
txtParent.Text = controller.QueryParentName(DBDetailForm.DB_TABLE_NAME, (int)txtParent.Tag); //get parent name
tasksList.Items.Clear(); //Reset taskList
string strTaskList = dbDetailForm.TaskList; //Start working on taskList
if (dbDetailForm.TaskList != "")
{
List <string> list = strTaskList.Split('-').ToList <string>(); //Character '-' devides taskList into single tasks
foreach (string item in list)
{
if (item.Contains('+')) //Character '+' indicates check status = true
{
string[] subItem = item.Split('+');
tasksList.Items.Add(subItem[0], CheckState.Checked);
}
else
{
tasksList.Items.Add(item);
}
}
}//Stop working on taskList
childrenList.Clear(); //Start working on ChildrenList
Dictionary <int, string> dictionary = controller.QueryAllChildren(DBDetailForm.DB_TABLE_NAME, Id);
//Add to ListView
foreach (KeyValuePair <int, string> item in dictionary)
{
ListViewItem childItem = new ListViewItem();
childItem.Tag = item.Key;
childItem.Text = item.Value;
childrenList.Items.Add(childItem);
} //Stop working on ChildrenList
this.Text = txtName.Text;
this.setDisableFields();
}
public SqlDataReader QueryDataWithId(string tableName, DBDetailForm dbDetailForm)
{
this.OpenConnection();
try {
string sqlString = "select * from " + tableName + " where Id = @Id";
sqlCmd = new SqlCommand(sqlString, conn);
sqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = dbDetailForm.Id;
dataReader = sqlCmd.ExecuteReader();
} catch (Exception e) {
MessageBox.Show(e.ToString());
}
return(dataReader);
//Keep connection opening, only close connection after completing reading SqlDataReader
}
public bool InsertData(string tableName, DBDetailForm dbDetailForm)
{
bool output = false;
this.OpenConnection();
try {
string sqlString = "insert into " + tableName + "(Name, Id, Level, Deadline, Status, Description, TaskList, ParentId)"
+ " values(@Name, @Id, @Level, @Deadline, @Status, @Description, @TaskList, @ParentId)";
sqlCmd = new SqlCommand(sqlString, conn);
sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = dbDetailForm.Name;
sqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = dbDetailForm.Id;
sqlCmd.Parameters.Add("@Level", SqlDbType.Int).Value = dbDetailForm.Level;
sqlCmd.Parameters.Add("@Deadline", SqlDbType.Date).Value = dbDetailForm.Deadline;
sqlCmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = dbDetailForm.Status;
sqlCmd.Parameters.Add("@Description", SqlDbType.NVarChar, -1).Value = dbDetailForm.Description;
sqlCmd.Parameters.Add("@TaskList", SqlDbType.NVarChar, -1).Value = dbDetailForm.TaskList;
sqlCmd.Parameters.Add("@ParentId", SqlDbType.Int).Value = dbDetailForm.ParentId;
if (sqlCmd.ExecuteNonQuery() >= 1)
{
output = true;
}
} catch (Exception e) {
if (e is SqlException)
{
//Cast e from 'Exeption' to 'SqlExeption', to get the 'Number' of exeption
SqlException innerExeption = e as SqlException;
// Exception number 2627 = Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.
// Exception number 2601 = Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.
// See http://msdn.microsoft.com/en-us/library/cc645603.aspx for more information and possible exception numbers
if (innerExeption != null && (innerExeption.Number == 2627 || innerExeption.Number == 2601))
{
MessageBox.Show("Conflict with existed Id, re-type another Id!");
}
}
else
{
MessageBox.Show(e.ToString());
}
} finally {
this.CloseConnection();
}
return(output);
}
/// <summary>
/// Handle events from CreateNew_form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>MyEventArgs object contains Name & Id of new child form
/// <returns></returns>
private void eCreateNew_BtnOK_Click(object sender, MyEventArgs e)
{
//Insert DB
DBDetailForm dbDetailForm = new DBDetailForm(e.Id);
dbDetailForm.Name = e.Name;
dbDetailForm.ParentId = this.Id;
if (controller.InsertData(DBDetailForm.DB_TABLE_NAME, dbDetailForm))
{
//Update List View for children
detail_form_Load(sender, null);
createNew_form.Close();
//send event to main form
if (eComplete_Creating_Child != null)
{
eComplete_Creating_Child.Invoke(this, e);
}
}
}
public DBDetailForm QueryDataWithId(string tableName, DBDetailForm dbDetailForm)
{
DBDetailForm dbOutput = new DBDetailForm();
dbOutput.Id = dbDetailForm.Id;
SqlDataReader dr = connFactory.QueryDataWithId(tableName, dbDetailForm);
while (dr.Read())
{
dbOutput.Name = this.GetName(dr);
dbOutput.Level = this.GetLevel(dr);
dbOutput.Deadline = this.GetDeadline(dr);
dbOutput.Status = this.GetStatus(dr);
dbOutput.Description = this.GetDescription(dr);
dbOutput.TaskList = this.GetTaskList(dr);
dbOutput.ParentId = this.GetParentId(dr);
}
this.CloseConnection();
return(dbOutput);
}
/// <sumary>
/// Utilities functions
/// </sumary>
/// <summary>
/// Remove UI which has deleted DB
/// </summary>
/// <param name="tag"></param>Tag of the object need to be deleted
/// <returns></returns>
private void AddUI(string tag, DBDetailForm dbDetailForm)
{
Panel panel = new Panel();
panel.Dock = DockStyle.Fill;
//Create 2 buttons to add to Panel
Button btnForm = new Button();
Button btnDelete = new Button();
//Set Tag value as id
panel.Tag = dbDetailForm.Id;
btnForm.Tag = dbDetailForm.Id;
btnDelete.Tag = dbDetailForm.Id;
//Query 'Name' to update UI
dbDetailForm = controller.QueryDataWithId(DBDetailForm.DB_TABLE_NAME, dbDetailForm);
btnForm.Text = dbDetailForm.Name;
btnDelete.Text = "X";
btnForm.Dock = DockStyle.Fill;
btnDelete.ForeColor = Color.White;
btnDelete.BackColor = Color.Red;
btnDelete.Font = new Font("Microsoft Sans Serif", 12, FontStyle.Bold);
btnDelete.Size = new System.Drawing.Size(40, 35);
btnForm.Click += new EventHandler(btnForm_Click);
btnDelete.Click += new EventHandler(btnDelete_Click);
//Main_form currentMain = (Main_form)Application.OpenForms["main_form"];
foreach (Control tbLayoutP in this.Controls.OfType <TableLayoutPanel>())
{
if (tbLayoutP.Tag == tag)
{
tbLayoutP.Controls.Add(panel);
panel.Controls.Add(btnDelete); //btnDelete is over btnForm
panel.Controls.Add(btnForm);
break;
}
}
}//End AddUI()
public List <DBDetailForm> QueryAllData(string tableName)
{
List <DBDetailForm> listDb = new List <DBDetailForm>();
SqlDataReader dr = connFactory.QueryAllData(tableName);
DBDetailForm db = new DBDetailForm();
while (dr.Read())
{
db.Name = this.GetName(dr);
db.Id = this.GetId(dr);
db.Level = this.GetLevel(dr);
db.Deadline = this.GetDeadline(dr);
db.Status = this.GetStatus(dr);
db.Description = this.GetDescription(dr);
db.TaskList = this.GetTaskList(dr);
db.ParentId = this.GetParentId(dr);
listDb.Add(new DBDetailForm(db));
}
this.CloseConnection();
return(listDb);
}
public bool UpdateData(string tableName, DBDetailForm dbDetailForm)
{
return(connFactory.UpdateData(tableName, dbDetailForm));
}
public bool InsertData(string tableName, DBDetailForm dbDetailForm)
{
return(connFactory.InsertData(tableName, dbDetailForm));
}
private const string TABLE_LAYOUT_PANEL_TAG_CHILD = "childForms"; //determine child table panel will contain new created form
public Main_form()
{
InitializeComponent();
controller = new Controller();
dbDetailForm = new DBDetailForm();
}
