/// <summary> /// Creates the text of the SQL command to create a table in SQLite /// </summary> /// <param name="table">Table Metadata to create</param> /// <returns>The DML sentence (SQL) to create the given table in a SQLite schema</returns> private string CreateTableDML(TableMetaData table) { StringBuilder stmtBuilder = new StringBuilder(); string primary_keys_string = string.Empty; stmtBuilder.Append("CREATE TABLE " + SchemaTablesMetaData.EscapeIdentifier(table.tableName) + " ("); for (int i = 0; i < table.columns.Count; i++) { ColumnMetaData column = table.columns[i]; stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.columnName)); stmtBuilder.Append(" "); stmtBuilder.Append(ConvertDbTypeToSQLiteType(column)); if (!column.isNullable) { stmtBuilder.Append(" NOT NULL"); } if (column.hasDefault) { stmtBuilder.Append(" DEFAULT "); if (IsLiteralType(column)) { stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.defaultValue)); } else { stmtBuilder.Append(column.defaultValue); } } if (column.hasForeignKey) { stmtBuilder.Append(String.Format(" REFERENCES {0} ({1})", SchemaTablesMetaData.EscapeIdentifier(column.fkTable), SchemaTablesMetaData.EscapeIdentifier(column.fkColumn))); } if (column.isPrimaryKey) { if (string.IsNullOrEmpty(primary_keys_string)) { primary_keys_string = SchemaTablesMetaData.EscapeIdentifier(column.columnName); } else { primary_keys_string = String.Format("{0},{1}", primary_keys_string, SchemaTablesMetaData.EscapeIdentifier(column.columnName)); } } if (i + 1 < table.columns.Count || !string.IsNullOrEmpty(primary_keys_string)) { stmtBuilder.Append(", "); } } if (!string.IsNullOrEmpty(primary_keys_string)) { stmtBuilder.Append(String.Format("PRIMARY KEY({0})", primary_keys_string)); } stmtBuilder.Append(")"); return(stmtBuilder.ToString()); }
/// <summary> /// Creates a SQL INSERT sentence to insert a row of data to a table in the target database. /// </summary> /// <param name="table">Table metadata from the table to copy</param> /// <returns>SQL Sentence of the insertion in the target database</returns> private string CreateInsertQuery(TableMetaData table) { string csvColumList = string.Empty; string csvParamsList = string.Empty; foreach (ColumnMetaData column in table.columns) { csvColumList = String.Format("{0}{1},", csvColumList, SchemaTablesMetaData.EscapeIdentifier(column.columnName)); csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName)); } string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1)); return(query); }