public void CreateTable(string connectionString, string databaseName, string tableName, List <JsonNode> jsonNodes, List <JsonNode> allJsonNodes, List <TopicNode> topicNodes)
{
CheckDBResults results = CheckDB(connectionString, databaseName, tableName, jsonNodes, allJsonNodes, topicNodes);
if (!results.success)
{
throw new Exception("Errors verifying database:\n" + results.messages);
}
if (!results.hasTable)
{
// Not table, so we just create the lot.
List <DatabaseData> columns = (from node in allJsonNodes where node.children.Count == 0 && node.inUse select(DatabaseData) node).ToList();
columns.AddRange(from node in topicNodes select(DatabaseData) node);
List <DatabaseData> primaryKeys = (from node in allJsonNodes where node.primaryKey select(DatabaseData) node).ToList();
primaryKeys.AddRange(from node in topicNodes where node.primaryKey select(DatabaseData) node);
string query = "";
query += $"CREATE TABLE [dbo].[{tableName}](\n";
bool first = true;
foreach (var col in columns)
{
if (!first)
{
query += ",\n";
}
string isNullable = col.primaryKey ? "NOT NULL" : "NULL";
string len = col.dbType == SqlDbType.VarChar ? $"({col.dbSize})" : "";
query += $"[{col.dbColumn}] [{col.dbType.ToString()}] {len} {isNullable}";
first = false;
}
if (primaryKeys.Count() > 0)
{
query += $",\nCONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED (\n";
first = true;
foreach (var col in primaryKeys)
{
if (!first)
{
query += ",\n";
}
query += $"[{col.dbColumn}] ASC";
first = false;
}
query += $"\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]";
}
query += $"\n) ON [PRIMARY]\n";
System.Diagnostics.Debug.WriteLine(query);
lastCommand = query;
using (var con = new SqlConnection(connectionString))
{
con.Open();
using (var cmd = new SqlCommand($"use {databaseName}", con))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new SqlCommand(query, con))
{
cmd.ExecuteNonQuery();
}
con.Close();
}
}
}
public CheckDBResults CheckDB(string connectionString, string databaseName, string tableName, List <JsonNode> jsonNodes, List <JsonNode> allJsonNodes, List <TopicNode> topicNodes)
{
CheckDBResults results = new CheckDBResults();
results.success = false;
// Check the connection
try
{
using (var con = new SqlConnection(connectionString))
{
con.Open();
con.Close();
}
}
catch (Exception ex)
{
results.messages = "Failed to connect to database server: " + ex.Message;
return(results);
}
// Check the database
try
{
using (var con = new SqlConnection(connectionString))
{
con.Open();
using (var cmd = new SqlCommand($"use {databaseName}", con))
{
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch (Exception ex)
{
results.messages = "Failed to access database: " + ex.Message;
return(results);
}
// Check the table - we need to check if it exists, if the columns are there (and correct) and if the primary key is set.
try
{
using (var con = new SqlConnection(connectionString))
{
con.Open();
using (var cmd = new SqlCommand($"use {databaseName}", con))
{
cmd.ExecuteNonQuery();
}
// Does table exist
using (var cmd = new SqlCommand($"select OBJECT_ID(N'{tableName}',N'U')", con))
{
var ret = cmd.ExecuteScalar();
if (DBNull.Value.Equals(ret))
{
// No table
results.hasTable = false;
results.messages = $"No table '{tableName}'";
results.success = true;
return(results);
}
else
{
results.hasTable = true;
}
}
// Is there data
using (var cmd = new SqlCommand($"select top 1 1 from {tableName}", con))
{
var ret = cmd.ExecuteScalar();
if (DBNull.Value.Equals(ret))
{
// No data
System.Diagnostics.Debug.WriteLine($"No data in table '{tableName}'");
results.hasData = false;
}
else
{
// data
System.Diagnostics.Debug.WriteLine($"Table has data '{tableName}'");
results.hasData = true;
}
}
// Check columns
Dictionary <string, DatabaseData> columns = (from node in allJsonNodes where node.children.Count == 0 && node.inUse select node).ToDictionary(p => p.dbColumn, p => (DatabaseData)p);
columns.AddRange((from node in topicNodes select node).ToDictionary(p => p.dbColumn, p => (DatabaseData)p));
Dictionary <string, DatabaseData> primaryKeys = (from node in allJsonNodes where node.primaryKey select node).ToDictionary(p => p.dbColumn, p => (DatabaseData)p);
primaryKeys.AddRange((from node in topicNodes where node.primaryKey select node).ToDictionary(p => p.dbColumn, p => (DatabaseData)p));
using (var cmd = new SqlCommand($"sp_help {tableName}", con))
{
using (var rdr = cmd.ExecuteReader())
{
do
{
while (rdr.Read())
{
bool foundColumns = false;
bool foundConstraints = false;
for (int i = 0; i < rdr.FieldCount; i++)
{
if (rdr.GetName(i) == "Column_name")
{
foundColumns = true;
break;
}
else if (rdr.GetName(i) == "constraint_type")
{
foundConstraints = true;
break;
}
}
if (foundColumns)
{
// We have the table result set.
do
{
string columnName = rdr.GetString(rdr.GetOrdinal("Column_name"));
string type = rdr.GetString(rdr.GetOrdinal("type"));
// check column
DatabaseData col = null;
if (!columns.TryGetValue(columnName, out col))
{
results.tooManyColumns.Add(columnName);
results.messages += $"Column in database is not in our list '{columnName}'\n";
}
else
{
if (col.dbType.ToString().ToLower() != type.ToLower())
{
results.wrongTypeColumns.Add(columnName);
results.messages += $"Column '{columnName}' has wrong type DB='{col.dbType}' JSON='{type}'\n";
}
columns.Remove(columnName);
}
}while (rdr.Read());
// list missing columns
foreach (var kvp in columns)
{
results.missingColumns.Add(kvp.Value.dbColumn);
results.messages += $"Missing column '{kvp.Value.dbColumn}'\n";
}
}
if (foundConstraints)
{
// We have the constraints - Primary key
do
{
string constraintType = rdr.GetString(rdr.GetOrdinal("constraint_type"));
string constraintKeys = rdr.GetString(rdr.GetOrdinal("constraint_keys"));
if (constraintType == "PRIMARY KEY (clustered)")
{
// check keys
string[] keys = constraintKeys.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var key in keys)
{
DatabaseData col = null;
if (!primaryKeys.TryGetValue(key, out col))
{
results.messages += $"Column '{key}' is not a primary key\n";
results.wrongPrimaryKey.Add(key);
}
else
{
primaryKeys.Remove(key);
}
}
}
}while (rdr.Read());
// list missing columns
foreach (var kvp in primaryKeys)
{
results.messages += $"Primary key not in definition '{kvp.Value.dbColumn}'\n";
results.missingPrimaryKey.Add(kvp.Value.dbColumn);
}
}
}
}while (rdr.NextResult());
rdr.Close();
}
}
con.Close();
}
results.success = true;
}
catch (Exception ex)
{
results.messages += "Failed to access database: " + ex.Message;
}
return(results);
}
