private void AddPersonByIndex()
{
people.AddByIndex(newPerson, IndexForAdd);
db.addByIndex(new PersonDataBase()
{
LastName = newPerson.LastName,
DateOfBirth = newPerson.DateOfBirth,
Height = newPerson.Height,
}, (int)IndexForAdd);
NewPerson = new Person("", new DateTime(2001, 01, 01), 0);
}
private void AddPersonByIndex()
{
string ID, prID = "";
people.AddByIndex(newPerson, IndexForAdd);
var conn = new SqlConnection("server = SMSK01DB09\\DEV; " + "Trusted_Connection=yes;" + "database=TrainingDatabase; ");
try
{
conn.Open();
var cmdFindFirst = new SqlCommand("SELECT * FROM FirstTable WHERE PrevID IS NULL", conn);
var reader = cmdFindFirst.ExecuteReader(); reader.Read();
ID = reader.HasRows ? reader["PersonID"].ToString() : null;
for (int i = 1; i < IndexForAdd; ++i)
{
conn.Close();
conn.Open();
var cmd = new SqlCommand("SELECT * FROM FirstTable WHERE PrevID=" + ID, conn);
reader = cmd.ExecuteReader(); reader.Read();
ID = reader.HasRows ? reader["PersonID"].ToString() : null;
prID = reader["PrevID"].ToString();
}
conn.Close();
conn.Open();
if (IndexForAdd == 1)
{
var cmd = new SqlCommand("INSERT INTO FirstTable (LastName,DateOfBirth,Height,NextID,PrevID,IsHead) VALUES(@lastName, @dateOfBirth,@height,@next ,@prev,@ishead)", conn);
cmd.Parameters.Add(new SqlParameter("lastName", newPerson.LastName));
cmd.Parameters.Add(new SqlParameter("dateOfBirth", newPerson.DateOfBirth));
cmd.Parameters.Add(new SqlParameter("height", (int)newPerson.Height));
cmd.Parameters.Add(new SqlParameter("next", ID));
cmd.Parameters.Add(new SqlParameter("prev", DBNull.Value));
cmd.Parameters.Add(new SqlParameter("ishead", 1));
cmd.ExecuteNonQuery();
conn.Close(); conn.Open();
cmd = new SqlCommand("SELECT PersonID FROM FirstTable WHERE IsHead=1 AND NOT PersonID=" + ID, conn);
reader = cmd.ExecuteReader(); reader.Read();
var NewHeadID = reader["PersonID"];
conn.Close(); conn.Open();
cmd = new SqlCommand("UPDATE FirstTable SET PrevID=" + NewHeadID.ToString() + ", IsHead=0" + " " + " WHERE PersonID=" + ID, conn);
cmd.ExecuteNonQuery();
}
else
{
var cmd = new SqlCommand("INSERT INTO FirstTable (LastName,DateOfBirth,Height,NextID,PrevID,IsHead) VALUES(@lastName, @dateOfBirth,@height,@next ,@prev,@ishead)", conn);
cmd.Parameters.Add(new SqlParameter("lastName", newPerson.LastName));
cmd.Parameters.Add(new SqlParameter("dateOfBirth", newPerson.DateOfBirth));
cmd.Parameters.Add(new SqlParameter("height", (int)newPerson.Height));
cmd.Parameters.Add(new SqlParameter("next", ID));
cmd.Parameters.Add(new SqlParameter("prev", prID));
cmd.Parameters.Add(new SqlParameter("ishead", false));
cmd.ExecuteNonQuery();
conn.Close(); conn.Open();
cmd = new SqlCommand("SELECT PersonID FROM FirstTable WHERE NextID=" + ID + " AND PrevID=" + prID, conn);
reader = cmd.ExecuteReader(); reader.Read();
var NewHeadID = reader["PersonID"];
conn.Close(); conn.Open();
cmd = new SqlCommand("UPDATE FirstTable SET PrevID=" + NewHeadID.ToString() + " " + " WHERE PersonID=" + ID, conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("UPDATE FirstTable SET NextID=" + NewHeadID.ToString() + " " + " WHERE PersonID=" + prID, conn);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
NewPerson = new Person("", new DateTime(2001, 01, 01), 0);
}