private void Load(string table_name, string sql_query)
{
ColumnNames = new List <string>();
Rows = new List <DatabaseRow>();
TableName = table_name;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); // Grab the conection string for accessing the database.
SqlCommand command = new SqlCommand(sql_query, connection); // Associate the given sql query with the connection.
connection.Open();
SqlDataReader reader = command.ExecuteReader(); // Send the sql query to the database for processing.
for (int n = 0; n < reader.FieldCount; ++n)
{
ColumnNames.Add(reader.GetName(n));
}
while (reader.Read()) // Access each student in turn (continue until there are no students left to read).
{
DatabaseRow dr = new DatabaseRow();
for (int column = 0; column < ColumnCount; ++column)
{
dr.Add(ColumnNames[column], reader[column].ToString());
}
Rows.Add(dr);
}
connection.Close();
}
/// <summary>
/// Update a given row. Assumes that ID field is present and has a unique value for each row.
/// </summary>
/// <param name="row_to_insert">The new DatabaseRow to update. Assumes this contains appropriate data.</param>
public void Update(DatabaseRow row_to_update)
{
// Create a properly formatted SQL UPDATE command...
string u = "";
for (int n = 0; n < ColumnCount; ++n)
{
if (row_to_update[n] != "" && ColumnNames[n] != "ID")
{
u += ColumnNames[n] + "='" + row_to_update[n] + "', ";
}
}
u = u.Substring(0, u.Length - 2);
// Send the UPDATE to the database...
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); // Grab the conection string for accessing the database.
string sql_command = "UPDATE " + TableName + " SET " + u + " WHERE ID='" + row_to_update["ID"] + "';";
SqlCommand command = new SqlCommand(sql_command, connection); // Associate the sql query with the connection.
connection.Open();
command.ExecuteNonQuery();
connection.Close();
Rows[FindRowIndex("ID", row_to_update["ID"])] = row_to_update; // Rewrite this new row over the existing row in the in-memory database.
}
/// <summary>
/// Insert a new row into the table and the underlying database.
/// </summary>
/// <param name="row_to_insert">The new DatabaseRow to insert. Assumes this contains appropriate data.</param>
public void Insert(DatabaseRow row_to_insert)
{
// Create a properly formatted SQL INSERT command...
string columns = "(";
string values = "(";
for (int n = 0; n < ColumnCount; ++n)
{
if (row_to_insert[n] != "")
{
columns += ColumnNames[n] + ", ";
values += "'" + row_to_insert[n] + "', ";
}
}
columns = columns.Substring(0, columns.Length - 2) + ")";
values = values.Substring(0, values.Length - 2) + ")";
// Send the INSERT...
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); // Grab the conection string for accessing the database.
string sql_command = "INSERT INTO " + TableName + " " + columns + " VALUES " + values; // The SQL statement to process - the INSERT statement.
SqlCommand command = new SqlCommand(sql_command, connection); // Associate the sql query with the connection.
connection.Open();
command.ExecuteNonQuery();
connection.Close();
Rows.Add(row_to_insert);
}
/// <summary>
/// Returns a new/blank DatabaseRow with same columns as the owner table.
/// </summary>
/// <returns>DatabaseRow</returns>
public DatabaseRow NewRow()
{
DatabaseRow dr = new DatabaseRow();
for (int column = 0; column < ColumnCount; ++column)
{
dr.Add(ColumnNames[column], "");
}
return(dr);
}
/// <summary>
/// Find a given row and return the index for this in the table (i.e. which row number is this). Zero based.
/// </summary>
/// <param name="field"></param>
/// <param name="value"></param>
/// <returns></returns>
public int FindRowIndex(string field, string value)
{
for (int r = 0; r < RowCount; ++r)
{
DatabaseRow dr = GetRow(r);
if (dr.ContainsField(field))
{
if (dr[field] == value)
{
return(r);
}
}
}
return(0);
}
public User(DatabaseRow r)
{
this.Fields = r.Fields;
}