/// <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()); }
/// <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(); }