internal static NoFuture.Sql.Mssql.Md.PsMetadata SerializedTableMetadata()
{
var psmd = new NoFuture.Sql.Mssql.Md.PsMetadata
{
IsComputedKeys = new List <string>(),
TickKeys = new List <string>
{
"PersonType",
"Title",
"FirstName",
"MiddleName",
"LastName",
"Suffix",
"Demographics",
"rowguid",
"ModifiedDate"
},
PkKeys = new Dictionary <string, string> {
{ "BusinessEntityID", "int" }
},
FkKeys = new Dictionary <string, string> {
{ "BusinessEntityID", "int" }
}
};
return(psmd);
}
/// <summary>
/// Will wrap the <see cref="val"/> in single-quotes if the <see cref="key"/> is found in
/// the <see cref="PsMetadata.TickKeys"/> list and the <see cref="val"/> does not equal the string
/// "NULL" (case-insensitive).
/// </summary>
/// <param name="metaData"></param>
/// <param name="key"></param>
/// <param name="val"></param>
/// <param name="maxLength">
/// Optional field to limit the size of string literals, set it to zero or less
/// to not truncate any text from <see cref="val"/>
/// </param>
/// <returns></returns>
public static string GetQryValueWrappedWithLimit(PsMetadata metaData, string key, string val, int maxLength)
{
if (string.IsNullOrWhiteSpace(key))
return val;
if (metaData == null)
return val;
if (metaData.AutoNumKeys.Any(x => string.Equals(x, key, Oic)) ||
metaData.IsComputedKeys.Any(x => string.Equals(x, key, Oic)))
return val;
if (!metaData.TickKeys.Any(x => string.Equals(x, key, Oic)) || string.Equals(val, "NULL", Oic))
return val;
if (val.Contains("'"))
val = val.Replace("'", "''");
return val.Length > maxLength && maxLength > 0
? $"'{val.Substring(0, maxLength)}'"
: $"'{val}'";
}
/// <summary>
/// Composes SQL syntax as an insert, update or merge (based on the <see cref="stmtType"/>) statement given the various inputs.
/// </summary>
/// <param name="sqlStmt"></param>
/// <param name="len"></param>
/// <param name="stmtType"></param>
/// <param name="metaData"></param>
/// <param name="results"></param>
/// <returns></returns>
public static string ScriptDataBody(string sqlStmt, int len, ExportToStatementType stmtType, PsMetadata metaData,
DataRow[] results)
{
if (results == null || results.Length <= 0)
throw new ItsDeadJim("No records found.");
//#get the table and schema names out of the expression
var tempPsObj = GetTableSchemaAndNameFromExpression(sqlStmt);
if (string.IsNullOrWhiteSpace(tempPsObj.TableName))
{
throw new RahRowRagee("The sql expression is either invalid or the 'from' statement " +
"does not specify a fully qualified name ([catalog].[dbo].[tablename])");
}
var tableName = tempPsObj.TableName;
var tableSchema = tempPsObj.SchemaName;
List<string> colNames = null;
var insertSyntax = new StringBuilder();
var updateSyntax = new StringBuilder();
var mergeDataLine = new List<string>();
//#for each database record
foreach (DataRow currec in results)
{
if(colNames == null)
colNames = currec.Table.Columns.OfType<DataColumn>().Select(x => x.ColumnName).ToList();
var updateWhere = new List<string>();
var setStmt = new List<string>();
var insertStmt = new List<string>();
var insertVals = new List<string>();
var mergeVals = new List<string>();
var counter = 0;
foreach (var key in colNames)
{
if (string.IsNullOrWhiteSpace(key))
continue;
if (metaData.AutoNumKeys.Any(x => string.Equals(x, key, Oic)) ||
metaData.IsComputedKeys.Any(x => string.Equals(x, key, Oic)))
continue;
//#eval out all 'DBNull.Value'
var val = string.IsNullOrWhiteSpace(currec[key].ToString()) ? "NULL" : currec[key].ToString();
//#truncate varcharesque values then wrap them in single quotes
val = GetQryValueWrappedWithLimit(metaData, key, val, len);
Tuple<string, string> formattedPair;
//#format both key and value with extra padding
if (stmtType == ExportToStatementType.UPDATE)
{
formattedPair = FormatKeyValue(key, val, len + 2, counter++);
var uws = $"{formattedPair.Item1} = {formattedPair.Item2}";
//#set a where statement off the primary key if that primary key was present in the select stmt
if (metaData.PkKeys.Keys.Any(x => string.Equals(x, key, Oic)))
updateWhere.Add(uws);
else
setStmt.Add(uws);
}
if (stmtType == ExportToStatementType.INSERT)
{
formattedPair = FormatKeyValue(key, val, len + 2, counter++, true);
//#set the insert values neatly printed into text columns
insertStmt.Add($"{formattedPair.Item1}");
insertVals.Add($"{formattedPair.Item2}");
}
if (stmtType == ExportToStatementType.MERGE)
{
formattedPair = FormatKeyValue(key, val, len + 2, counter++, true);
mergeVals.Add(formattedPair.Item2);
}
} //#end foreach datakey
if (stmtType == ExportToStatementType.INSERT)
{
//#close the blocks
insertSyntax.AppendFormat("\nINSERT INTO [{0}].[{1}]\n(\n\t", tableSchema, tableName);
insertSyntax.Append(string.Join(",\n\t", insertStmt));
insertSyntax.Append("\n)\nVALUES\n(\n\t");
insertSyntax.Append(string.Join(",\n\t", insertVals));
insertSyntax.Append("\n)\n");
continue;
}
if (stmtType == ExportToStatementType.UPDATE)
{
updateSyntax.AppendFormat("\nUPDATE [{0}].[{1}]\nSET {2}\nWHERE {3}\n", tableSchema, tableName,
string.Join(",\n ", setStmt), string.Join(" AND ", updateWhere));
}
if (stmtType == ExportToStatementType.MERGE)
{
mergeDataLine.Add($"({string.Join(",\n ", mergeVals)})");
}
}
if(stmtType == ExportToStatementType.INSERT || stmtType == ExportToStatementType.UPDATE)
return stmtType == ExportToStatementType.INSERT ? insertSyntax.ToString() : updateSyntax.ToString();
//draft the MERGE statement
var mergeData = string.Join("\n ,", mergeDataLine);
var matchOnColumn = new List<string>();
var otherColumn = new List<string>();
//divide column names
if (colNames == null)
return null;
foreach (var key in colNames)
{
if (string.IsNullOrWhiteSpace(key))
continue;
if (metaData.AutoNumKeys.Any(x => string.Equals(x, key, Oic)) || metaData.IsComputedKeys.Any(x => string.Equals(x, key, Oic)))
continue;
if (metaData.PkKeys.Keys.Any(x => string.Equals(x, key, Oic)))
matchOnColumn.Add($"[{key}]");
else
otherColumn.Add($"[{key}]");
}
var matchOnList = matchOnColumn.Select(matchOn => string.Format("target.{0} = source.{0}", matchOn)).ToList();
var updateSetList = otherColumn.Select(ot => string.Format("{0} = source.{0}", ot)).ToList();
var insertNameList = metaData.IsIdentityInsert ? otherColumn : colNames;
var insertValList = metaData.IsIdentityInsert
? otherColumn.Select(ot => $"source.{ot}").ToList()
: colNames.Select(cn => $"source.{cn}").ToList();
var mergeStatementBuilder = new StringBuilder();
mergeStatementBuilder.AppendFormat("MERGE [{0}].[{1}] AS target\n", tableSchema, tableName);
mergeStatementBuilder.Append("USING (\n");
mergeStatementBuilder.Append("VALUES\n ");
mergeStatementBuilder.Append(mergeData);
mergeStatementBuilder.AppendFormat("\n ) AS source (\n {0})\n", string.Join(",\n ", colNames));
mergeStatementBuilder.AppendFormat("ON ({0})\n", string.Join(" AND ", matchOnList));
mergeStatementBuilder.AppendLine("WHEN MATCHED THEN");
mergeStatementBuilder.AppendFormat(" UPDATE SET {0}\n", string.Join(",\n ", updateSetList));
mergeStatementBuilder.AppendLine("WHEN NOT MATCHED THEN");
mergeStatementBuilder.AppendFormat(" INSERT ( {0}\n )\n",
string.Join(",\n ", insertNameList));
mergeStatementBuilder.AppendFormat(" VALUES ( {0}\n );\n",
string.Join(",\n ", insertValList));
return mergeStatementBuilder.ToString();
}
internal static NoFuture.Sql.Mssql.Md.PsMetadata SerializedTableMetadata()
{
var psmd = new NoFuture.Sql.Mssql.Md.PsMetadata
{
IsComputedKeys = new List<string>(),
TickKeys = new List<string>
{
"PersonType",
"Title",
"FirstName",
"MiddleName",
"LastName",
"Suffix",
"Demographics",
"rowguid",
"ModifiedDate"
},
PkKeys = new Dictionary<string, string> {{"BusinessEntityID", "int"}},
FkKeys = new Dictionary<string, string> {{"BusinessEntityID", "int"}}
};
return psmd;
}
