/// <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}'");
}
public static void Run(
String openPath, // source PDF document
String savePath, // output XML document
String configPath // configuration file
)
{
Pdfix pdfix = PdfixEngine.Instance;
PdfDoc doc = pdfix.OpenDoc(openPath, "");
if (doc == null)
{
throw new Exception(pdfix.GetError());
}
StreamWriter file = new System.IO.StreamWriter(savePath);
//pdfix.CreateCustomStream()
// XML headers
file.Write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
file.Write("<!--Created from PDF via PDFix-->\n");
file.Write("<Document>\n");
PsMetadata metadata = doc.GetMetadata();
// iterate through pages and parse each page individually
for (int i = 0; i < doc.GetNumPages(); i++)
{
PdfPage page = doc.AcquirePage(i);
if (page == null)
{
throw new Exception(pdfix.GetError());
}
ParsePage(pdfix, page, file);
page.Release();
}
file.Close();
doc.Close();
}
/// <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>
/// <param name="binaryData2Literal">
/// Optional, have binary data written in the T-SQL format (e.g. 0x00FF), passed over otherwise
/// </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>();
Func <PsMetadata, string, bool> isSkipColumn =
(metadata, key) =>
{
return((metaData.AutoNumKeys?.Any(x => String.Equals(x, key, Oic)) ?? false) ||
(metaData.IsComputedKeys?.Any(x => String.Equals(x, key, Oic)) ?? false) ||
(metaData.TimestampColumns?.Any(x => String.Equals(x, key, Oic)) ?? false));
};
//#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 (isSkipColumn(metaData, key))
{
continue;
}
//#eval out all 'DBNull.Value'
var val = String.IsNullOrWhiteSpace(currec[key].ToString()) ? "NULL" : currec[key].ToString();
//handle byte arrays different since ToString is not a value-literal
if (String.Equals(val, "System.Byte[]", Oic))
{
val = "0x" + ByteArray.PrintByteArray((byte[])currec[key]);
}
//#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 (isSkipColumn(metaData, key))
{
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 mergeSrcList = colNames.Where(c => !String.IsNullOrWhiteSpace(c) && !isSkipColumn(metaData, c));
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 ", mergeSrcList));
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());
}
