public String createSelect(Enum[] field, Net7.Tables table, Enum idField, String value, Int32 queryCount)
{
String query = "";
foreach (Enum enumField in field)
{
if (query.Length == 0)
{
query = SELECT;
}
else
{
query += ",";
}
query += ColumnData.GetName(enumField);
}
query += FROM
+ table.ToString()
+ WHERE
+ ColumnData.GetName(idField)
+ " = "
+ DB.QueryParameterCharacter
+ idField.ToString() + queryCount.ToString()
+ ";";
//System.Windows.Forms.MessageBox.Show(query, value);
return(query);
}
public String getVersion(String toolName)
{
String toolParameter = "ToolName";
String query = "SELECT "
+ ColumnData.GetName(Net7.Table_versions._Version)
+ " FROM "
+ Net7.Tables.versions.ToString()
+ " WHERE "
+ ColumnData.GetName(Net7.Table_versions._EName)
+ " = "
+ DB.QueryParameterCharacter + toolParameter;
DataTable dataTable = DB.Instance.executeQuery(query, new String[] { toolParameter }, new String[] { toolName });
return(dataTable.Rows.Count == 0
? ""
: ColumnData.GetString(dataTable.Rows[0], Net7.Table_versions._Version));
}
public void setVersion(String toolName, String version)
{
String toolNameParameter = "ToolName";
String toolVersionParameter = "ToolVersion";
String query = "UPDATE "
+ Net7.Tables.versions
+ " SET "
+ ColumnData.GetName(Net7.Table_versions._Version)
+ " = " + DB.QueryParameterCharacter + toolVersionParameter
+ " WHERE "
+ ColumnData.GetName(Net7.Table_versions._EName)
+ " = "
+ DB.QueryParameterCharacter + toolNameParameter;
int rowsAffected = DB.Instance.executeCommand(query, new String[] { toolVersionParameter, toolNameParameter }, new String[] { version, toolName });
if (rowsAffected == 0)
{
// INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob')
query = "INSERT INTO "
+ Net7.Tables.versions
+ " ("
+ ColumnData.GetName(Net7.Table_versions._EName) + ","
+ ColumnData.GetName(Net7.Table_versions._Version)
+ ") VALUES ("
+ DB.QueryParameterCharacter + toolNameParameter + ","
+ DB.QueryParameterCharacter + toolVersionParameter
+ ")";
rowsAffected = DB.Instance.executeCommand(query, new String[] { toolVersionParameter, toolNameParameter }, new String[] { version, toolName });
if (rowsAffected == 0)
{
System.Windows.Forms.MessageBox.Show("Unable to update/create the version information into the "
+ Net7.Tables.versions
+ " table");
}
}
}
/// <summary>
/// <para>Convert a database structure into various enumerations.</para>
/// </summary>
/// <param name="databaseName">The database name for which to generate the code.</param>
/// <remarks>This method overwrites the contents of the <databaseName>.cs file.
/// The goal of this approach is to easily handle schema changes without having
/// to hunt through code in order to locate strings that now point to obsolete
/// names. Instead those will now generate compile-time errors.</remarks>
public void makeDatabaseVariables()
{
String query;
String tableName;
String tableEnum;
String columnAlignedPosition;
DataTable dataTable;
dataTable = executeQuery("SELECT DISTINCT table_name "
+ "FROM information_schema.columns "
+ "WHERE table_schema = '"
+ DATABASE_NAME
+ "'", null, null);
System.IO.FileInfo fileInfo = new System.IO.FileInfo("..\\..\\..\\..\\CommonTools\\Database\\" + DATABASE_NAME + ".cs");
System.IO.StreamWriter streamWriter = fileInfo.CreateText();
streamWriter.WriteLine("// This file was automatically generated by Database.makeDatabaseVariables() on "
+ DateTime.Now.Year.ToString() + "/"
+ DateTime.Now.Month.ToString() + "/"
+ DateTime.Now.Day.ToString() + " "
+ DateTime.Now.Hour.ToString() + ":"
+ DateTime.Now.Minute.ToString() + ":"
+ DateTime.Now.Second.ToString());
streamWriter.WriteLine("namespace CommonTools.Database");
streamWriter.WriteLine("{");
streamWriter.WriteLine(" public static class " + DATABASE_NAME);
streamWriter.WriteLine(" {");
tableEnum = " public enum Tables { ";
columnAlignedPosition = new String(' ', tableEnum.Length);
streamWriter.Write(tableEnum);
DataRow dataRow;
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; ++rowIndex)
{
dataRow = dataTable.Rows[rowIndex];
tableName = dataRow["table_name"].ToString();
if (rowIndex != dataTable.Rows.Count - 1)
{
tableName += ", ";
}
streamWriter.Write(tableName);
if (rowIndex != 0 && (rowIndex % 5) == 0)
{
streamWriter.WriteLine("");
streamWriter.Write(columnAlignedPosition);
}
}
streamWriter.WriteLine(" };");
streamWriter.WriteLine("");
query = "SELECT table_name, column_name, data_type "
+ "FROM information_schema.columns "
+ "WHERE table_schema = '"
+ DATABASE_NAME
+ "' "
+ "ORDER BY table_name, ordinal_position";
makeDatabaseEnum(streamWriter, query, columnAlignedPosition, false, true);
query = "SELECT "
+ "'item_type' as 'table_name', "
+ "name as 'column_name', "
+ "id as 'data_type' "
+ "FROM "
+ Net7.Tables.item_type.ToString()
+ " ORDER BY "
+ ColumnData.GetName(Net7.Table_item_type._id);
makeDatabaseEnum(streamWriter, query, columnAlignedPosition, true, true);
streamWriter.WriteLine(" }");
streamWriter.WriteLine("}");
streamWriter.Close();
}