private static void DrFillSongVerseObj(FbDataReader dr, MigrationSongVerse vo)
{
vo.autoNumber = dr.GetInt32(0);
vo.orderNumber = dr.GetInt32(1);
vo.isChorus = dr.GetBoolean(2);
vo.verse = dr.GetString(3);
}
/// <summary>
/// Gets the Data.
/// </summary>
private void GetData()
{
try
{
// Clear existing items.
dataGridView1.Rows.Clear();
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items from the People table.
List <Person> people = new List <Person>();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM People WHERE IsDeleted = 0", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store the items in a Person list.
while (fbSqlDataReader.Read())
{
people.Add(new Person(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetString(1), fbSqlDataReader.GetString(2), fbSqlDataReader.GetString(3),
fbSqlDataReader.GetDateTime(4), fbSqlDataReader.GetBoolean(5)));
}
// Close the datareader and the connection.
fbSqlDataReader.Close();
fbSqlConnection.Close();
// Fill the dataGridView rows with the values of the People table.
foreach (var oneItem in people)
{
DataGridViewRow row = new DataGridViewRow();
DataGridViewCell cell1 = new DataGridViewTextBoxCell();
DataGridViewCell cell2 = new DataGridViewTextBoxCell();
DataGridViewCell cell3 = new DataGridViewTextBoxCell();
DataGridViewCell cell4 = new DataGridViewTextBoxCell();
DataGridViewCell cell5 = new DataGridViewTextBoxCell();
cell1.Value = oneItem.Id.ToString();
cell2.Value = oneItem.Name;
cell3.Value = oneItem.Mothername;
cell4.Value = oneItem.Location;
cell5.Value = oneItem.Birthdate.ToString();
row.Cells.Add(cell1);
row.Cells.Add(cell2);
row.Cells.Add(cell3);
row.Cells.Add(cell4);
row.Cells.Add(cell5);
dataGridView1.Rows.Add(row);
}
}
catch
{ }
}
/// <summary>
/// Handles the CellClick event of the dataGridView1 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="DataGridViewCellEventArgs"/> instance containing the event data.</param>
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
// An item must be selected in the dataGridView.
if (dataGridView1.CurrentCellAddress.X >= 0)
{
try
{
// Get the Id value of the selected item.
int id = int.Parse(dataGridView1.Rows[dataGridView1.SelectedCells[0].RowIndex].Cells["Column1"].Value.ToString());
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the item from the Relations table.
Relation relation = new Relation();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM Relations WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store it in a Relation object.
while (fbSqlDataReader.Read())
{
relation = new Relation(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetInt32(1), fbSqlDataReader.GetInt32(2),
fbSqlDataReader.GetInt32(3), fbSqlDataReader.GetBoolean(4));
}
// Give the comboBox SelectedItems the selected values.
comboBox1.SelectedItem = relation.Person1;
comboBox2.SelectedItem = relation.Relationship;
comboBox3.SelectedItem = relation.Person2;
// Close the connection.
fbSqlConnection.Close();
}
catch
{
// Empty the SelectedItems value.
comboBox1.SelectedItem = null;
comboBox2.SelectedItem = null;
comboBox3.SelectedItem = null;
}
}
else
{
// Empty the SelectedItems value.
comboBox1.SelectedItem = null;
comboBox2.SelectedItem = null;
comboBox3.SelectedItem = null;
}
}
private void DrFillSongObj(FbDataReader dr, MigrationSong mgo)
{
mgo.autoNumber = dr.GetInt32(0);
mgo.number = dr.GetInt32(1);
mgo.title = dr.GetString(2);
mgo.chorus = dr.GetString(3);
mgo.location = dr.GetString(4);
mgo.displayDefault = dr.GetBoolean(5);
mgo.imageId = dr.GetInt32(6);
mgo.overlay = dr.GetInt32(7);
mgo.copyright = dr.GetString(8);
}
public bool ConvertDBValueToBoolean(FbDataReader reader, int index)
{
return(reader.IsDBNull(index) ? false : reader.GetBoolean(index));
}
private ProfileInfoCollection GetProfilesForQuery(FbParameter[] args, ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("The pageIndex must be greater than or equal to zero.");
}
if (pageSize < 1)
{
throw new ArgumentException("The pageSize must be greater than zero.");
}
totalRecords = 0;
long upperBound = (long)pageIndex * pageSize + pageSize - 1;
if (upperBound > Int32.MaxValue)
{
throw new ArgumentException("The combination of pageIndex and pageSize cannot exceed the maximum value of System.Int32.");
}
ProfileInfoCollection profiles = new ProfileInfoCollection();
using (FbConnection conn = new FbConnection(this.connectionString))
{
conn.Open();
using (FbCommand
cmd1 = new FbCommand("Profiles_GetCountProfiles", conn),
cmd2 = new FbCommand("Profiles_GetProfiles", conn))
{
cmd1.CommandTimeout = this.commandTimeout;
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add(CreateInputParam("@ApplicationName", FbDbType.VarChar, ApplicationName));
cmd1.Parameters.Add(CreateInputParam("@ProfileAuthOptions", FbDbType.Integer, (int)authenticationOption));
foreach (FbParameter arg in args)
{
cmd1.Parameters.Add(arg);
}
totalRecords = (int)cmd1.ExecuteScalar();
cmd2.CommandTimeout = this.commandTimeout;
cmd2.CommandType = CommandType.StoredProcedure;
foreach (FbParameter p in cmd1.Parameters)
{
cmd2.Parameters.Add(p);
}
cmd2.Parameters.Add(CreateInputParam("@PageIndex", FbDbType.Integer, pageIndex));
cmd2.Parameters.Add(CreateInputParam("@PageSize", FbDbType.Integer, pageSize));
using (FbDataReader reader = cmd2.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
string username;
DateTime dtLastActivity, dtLastUpdated;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, -1));
}
}
}
}
return(profiles);
}
/// <summary>
/// Handles the Click event of the button2 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void button2_Click(object sender, EventArgs e)
{
// Add a record.
try
{
// The field must not be empty.
if (!string.IsNullOrEmpty(name))
{
bool matchFound = false;
bool deletedFound = false;
id = 0;
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items from the Relationships table.
FbCommand fbSqlCommand1 = new FbCommand("SELECT * FROM Relationships", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader1 = fbSqlCommand1.ExecuteReader();
// Get the number of the existing items.
while (fbSqlDataReader1.Read())
{
id++;
// Check if the new item already exists.
if (fbSqlDataReader1.GetString(1) == name && !matchFound && fbSqlDataReader1.GetBoolean(3) == false)
{
matchFound = true;
break;
}
// Check if the new item already exists.
if (fbSqlDataReader1.GetString(1) == name && !deletedFound && fbSqlDataReader1.GetBoolean(3) == true)
{
deletedFound = true;
id = fbSqlDataReader1.GetInt32(0);
break;
}
}
// Close the dataReader.
fbSqlDataReader1.Close();
// If no match was found among the active items.
if (!matchFound)
{
// Add a record if no deleted item found else restore the deleted item.
if (!deletedFound)
{
// Add new item to the Relationship table.
FbCommand fbSqlCommand = new FbCommand("INSERT INTO Relationships (Id, Name, IsDeleted) VALUES (@param1, @param2, @param3)", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id + 1);
fbSqlCommand.Parameters.AddWithValue("@param2", name);
fbSqlCommand.Parameters.AddWithValue("@param3", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
else
{
// Edit the IsDeleted value to false.
FbCommand fbSqlCommand = new FbCommand("UPDATE Relationships SET IsDeleted = @param2 WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id);
fbSqlCommand.Parameters.AddWithValue("@param2", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
}
// Close the connection.
fbSqlConnection.Close();
// Refresh.
GetData();
}
}
catch
{ }
}
/// <summary>
/// Gets the Data.
/// </summary>
private void GetData()
{
try
{
// Clear existing items.
dataGridView1.Rows.Clear();
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items from the Relationships table.
List <Relationship> relationships = new List <Relationship>();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM Relationships WHERE IsDeleted = 0", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store the items in a Relationship list.
while (fbSqlDataReader.Read())
{
relationships.Add(new Relationship(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetString(1), fbSqlDataReader.GetBoolean(2)));
}
// Close the dataReader and the connection.
fbSqlDataReader.Close();
fbSqlConnection.Close();
// Fill the dataGridView rows with the values of the Relationships table.
foreach (var oneItem in relationships)
{
DataGridViewRow row = new DataGridViewRow();
DataGridViewCell cell1 = new DataGridViewTextBoxCell();
DataGridViewCell cell2 = new DataGridViewTextBoxCell();
cell1.Value = oneItem.Id.ToString();
cell2.Value = oneItem.Name;
row.Cells.Add(cell1);
row.Cells.Add(cell2);
dataGridView1.Rows.Add(row);
}
}
catch
{ }
}
public bool GetNullableBool(FbDataReader reader, int col)
{
return reader.IsDBNull(col) == false ? reader.GetBoolean(col) : false;
}
/// <summary>
/// Handles the Click event of the button2 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void button2_Click(object sender, EventArgs e)
{
// Add a record.
try
{
// The fields must not be empty.
if (!string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(mothername) && !string.IsNullOrEmpty(location) && !string.IsNullOrEmpty(birthdate))
{
id = 0;
bool matchFound = false;
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items from the People table.
FbCommand fbSqlCommand1 = new FbCommand("SELECT * FROM People", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader1 = fbSqlCommand1.ExecuteReader();
// Get the number of the existing items.
while (fbSqlDataReader1.Read())
{
id++;
// Search for deleted matches.
if (!matchFound &&
fbSqlDataReader1.GetString(1) == name &&
fbSqlDataReader1.GetString(2) == mothername &&
fbSqlDataReader1.GetString(3) == location &&
fbSqlDataReader1.GetDateTime(4) == DateTime.Parse(birthdate) &&
fbSqlDataReader1.GetBoolean(5) == true)
{
matchFound = true;
id = fbSqlDataReader1.GetInt32(0);
break;
}
}
// Close the dataReader.
fbSqlDataReader1.Close();
// Add a new item if no match exists or restore the deleted item.
if (!matchFound)
{
// Add new item to the People table.
FbCommand fbSqlCommand = new FbCommand("INSERT INTO People (Id, Name, Mothername, Location, Birthdate, IsDeleted) VALUES (@param1, @param2, @param3, @param4, @param5, @param6)", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id + 1);
fbSqlCommand.Parameters.AddWithValue("@param2", name);
fbSqlCommand.Parameters.AddWithValue("@param3", mothername);
fbSqlCommand.Parameters.AddWithValue("@param4", location);
fbSqlCommand.Parameters.AddWithValue("@param5", DateTime.Parse(birthdate));
fbSqlCommand.Parameters.AddWithValue("@param6", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
else
{
// Edit the IsDeleted value to false.
FbCommand fbSqlCommand = new FbCommand("UPDATE People SET IsDeleted = @param2 WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id);
fbSqlCommand.Parameters.AddWithValue("@param2", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
// Close the connection.
fbSqlConnection.Close();
// Refresh.
GetData();
}
}
catch
{ }
}
/// <summary>
/// Handles the Click event of the button2 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void button2_Click(object sender, EventArgs e)
{
// Add a record.
try
{
// The fileds must not be empty and the names must not match.
if (name1 != 0 && name2 != 0 && relationship != 0 && name1 != name2)
{
id = 0;
bool matchFound = false;
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items of the Relations table.
FbCommand fbSqlCommand1 = new FbCommand("SELECT * FROM Relations", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader1 = fbSqlCommand1.ExecuteReader();
// Get the number of the existing items.
while (fbSqlDataReader1.Read())
{
id++;
// Search for a deleted mathing item.
if (!matchFound &&
fbSqlDataReader1.GetInt32(1) == name1 &&
fbSqlDataReader1.GetInt32(2) == relationship &&
fbSqlDataReader1.GetInt32(3) == name2 &&
fbSqlDataReader1.GetBoolean(4) == true)
{
matchFound = true;
id = fbSqlDataReader1.GetInt32(0);
break;
}
}
// Close the dataReader.
fbSqlDataReader1.Close();
// Add an item if no deleted found else restore the item.
if (!matchFound)
{
// Add a new item into the Relations table.
FbCommand fbSqlCommand = new FbCommand("INSERT INTO Relations (Id, Person1, Relationship, Person2, IsDeleted) VALUES (@param1, @param2, @param3, @param4, @param5)", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id + 1);
fbSqlCommand.Parameters.AddWithValue("@param2", name1);
fbSqlCommand.Parameters.AddWithValue("@param3", relationship);
fbSqlCommand.Parameters.AddWithValue("@param4", name2);
fbSqlCommand.Parameters.AddWithValue("@param5", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
else
{
// Edit the IsDeleted value to true.
FbCommand fbSqlCommand = new FbCommand("UPDATE Relations SET IsDeleted = @param2 WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id);
fbSqlCommand.Parameters.AddWithValue("@param2", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
// Close the connection.
fbSqlConnection.Close();
// Refresh.
GetData();
}
}
catch
{ }
}
/// <summary>
/// Gets the Data.
/// </summary>
private void GetData()
{
try
{
// Clear the existing items.
dataGridView1.Rows.Clear();
comboBox1.Items.Clear();
comboBox2.Items.Clear();
comboBox3.Items.Clear();
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items from the People table.
List <Person> people = new List <Person>();
FbCommand fbSqlCommand1 = new FbCommand("SELECT * FROM People", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader1 = fbSqlCommand1.ExecuteReader();
// Store the items in a Person list.
while (fbSqlDataReader1.Read())
{
people.Add(new Person(fbSqlDataReader1.GetInt32(0), fbSqlDataReader1.GetString(1), fbSqlDataReader1.GetString(2),
fbSqlDataReader1.GetString(3), fbSqlDataReader1.GetDateTime(4), fbSqlDataReader1.GetBoolean(5)));
}
// Close the dataReader.
fbSqlDataReader1.Close();
// Get the items from the Relationships table.
List <Relationship> relationships = new List <Relationship>();
FbCommand fbSqlCommand2 = new FbCommand("SELECT * FROM Relationships", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader2 = fbSqlCommand2.ExecuteReader();
// Store the items in a Relationship list.
while (fbSqlDataReader2.Read())
{
relationships.Add(new Relationship(fbSqlDataReader2.GetInt32(0), fbSqlDataReader2.GetString(1), fbSqlDataReader2.GetBoolean(2)));
}
// Close the dataReader.
fbSqlDataReader2.Close();
// Get items from the Relations table.
List <Relation> relations = new List <Relation>();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM Relations WHERE IsDeleted = 0", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store the items in a Relation list.
while (fbSqlDataReader.Read())
{
relations.Add(new Relation(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetInt32(1), fbSqlDataReader.GetInt32(2),
fbSqlDataReader.GetInt32(3), fbSqlDataReader.GetBoolean(4)));
}
// Close the dataReader and the connection.
fbSqlDataReader.Close();
fbSqlConnection.Close();
// Fill the dataGridView rows with the values of the Relations table.
foreach (var oneItem in relations)
{
DataGridViewRow row = new DataGridViewRow();
DataGridViewCell cell1 = new DataGridViewTextBoxCell();
DataGridViewCell cell2 = new DataGridViewTextBoxCell();
DataGridViewCell cell3 = new DataGridViewTextBoxCell();
DataGridViewCell cell4 = new DataGridViewTextBoxCell();
cell1.Value = oneItem.Id.ToString();
// Substitute the Person1 and Person2 ids with their names.
foreach (var onePerson in people)
{
if (oneItem.Person1 == onePerson.Id)
{
cell2.Value = onePerson.Name;
}
if (oneItem.Person2 == onePerson.Id)
{
cell4.Value = onePerson.Name;
}
}
// Substitute the Relationship ids with their names.
foreach (var oneRelationship in relationships)
{
if (oneItem.Relationship == oneRelationship.Id)
{
cell3.Value = oneRelationship.Name;
}
}
row.Cells.Add(cell1);
row.Cells.Add(cell2);
row.Cells.Add(cell3);
row.Cells.Add(cell4);
dataGridView1.Rows.Add(row);
}
// Fill the comboBox items with the values of the People table.
foreach (var oneItem in people)
{
if (!oneItem.IsDeleted)
{
comboBox1.Items.Add(oneItem.Id);
comboBox3.Items.Add(oneItem.Id);
}
}
// Fill the comboBox items with the values of the Relationships table.
foreach (var oneItem in relationships)
{
if (!oneItem.IsDeleted)
{
comboBox2.Items.Add(oneItem.Id);
}
}
}
catch
{ }
}
/// <summary>
/// Handles the SelectedIndexChanged event of the comboBox3 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
// Store the comboBox SelectedItem.
name2 = int.Parse(comboBox3.SelectedItem.ToString());
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the item from the People table.
Person retVal = new Person();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM People WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", name2);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store the item in a Person object.
while (fbSqlDataReader.Read())
{
retVal = new Person(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetString(1), fbSqlDataReader.GetString(2),
fbSqlDataReader.GetString(3), fbSqlDataReader.GetDateTime(4), fbSqlDataReader.GetBoolean(5));
}
// Give the helper label the selected value.
label9.Text = retVal.Name;
// Close the connection.
fbSqlConnection.Close();
}
catch
{ }
}
/// <summary>
/// Handles the SelectedIndexChanged event of the comboBox2 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
// Store the comboBox SelectedItem.
relationship = int.Parse(comboBox2.SelectedItem.ToString());
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the item from the Relationships table.
Relationship retVal = new Relationship();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM Relationships WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", relationship);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store the item in a Relationship object.
while (fbSqlDataReader.Read())
{
retVal = new Relationship(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetString(1), fbSqlDataReader.GetBoolean(2));
}
// Give the helper label the selected value.
label8.Text = retVal.Name;
// Close the connection.
fbSqlConnection.Close();
}
catch
{ }
}
