private static string columnName(FlexResult result, int zeroBasedColumnIndex) { if (String.IsNullOrEmpty(result.schema[zeroBasedColumnIndex].ColumnName)) { return("anonymousColumn" + (zeroBasedColumnIndex + 1).ToString()); } return(escapeForXML(result.schema[zeroBasedColumnIndex].ColumnName)); }
private static string columnName(FlexResult result, int zeroBasedColumnIndex) { string headerName = (string)result.schema.Rows[zeroBasedColumnIndex].ItemArray[(int)FieldScripting.FieldInfo.Name]; if (headerName == "") { return("anonymousColumn" + (zeroBasedColumnIndex + 1).ToString()); } return(escapeForXML(headerName)); }
private static string columnName(FlexResult result, int zeroBasedColumnIndex) { string headerName = (string)result.schema[zeroBasedColumnIndex].ColumnName; if (headerName == "") { return("anonymousColumn" + (zeroBasedColumnIndex + 1).ToString()); } return(escapeForCSV(headerName)); }
private static void processSchemaInfo(SqlDataReader reader, FlexResult result) { try { result.schema = SQLColumnList.CreateFromSchemaTable(reader.GetSchemaTable()); } catch (Exception ex) { result.exceptions.Add(ex); } }
private static void processSchemaInfo(SqlDataReader reader, FlexResult result) { try { var st = reader.GetSchemaTable(); result.schema = st; } catch (Exception ex) { result.exceptions.Add(ex); } }
public static StringBuilder ScriptResultDataAsInsert(FlexResult result, string tableName, int maxRowsInValuesClause) { if (!ResultIsRenderableAsCreateTable(result)) { return(new StringBuilder("--No schema for result from query.")); } if (!ResultIsRenderableAsScriptedData(result)) { return(new StringBuilder("--No rows were returned from the query.")); } return(scriptDataAsInsertForSQL2008Plus(tableName, result, maxRowsInValuesClause)); }
private static void processData(SqlDataReader reader, FlexResult result) { try { int fieldCount = reader.FieldCount; var data = new List <Object[]>(); while (reader.Read()) { Object[] values = new Object[fieldCount]; reader.GetValues(values); data.Add(values); } result.data = data; } catch (Exception ex) { result.exceptions.Add(ex); } }
public static void renderAsCSharp(FlexResultSet resultSet, SqlRunParameters srp) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { int columnCount = result.visibleColumnCount; var columnNamesAndCounts = new Dictionary <string, int>(); sb.Append(String.Format(classHeader, generateValidCSharpClassName(i))); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { var s = result.schema[colIndex]; sb.Append(DisambiguateAndRenderCSharpProperty(s, columnNamesAndCounts)); } sb.Append(classFooter); } } srp.resultsText = sb; }
public static Boolean ResultIsRenderableAsCreateTable(FlexResult result) { return !(result.schema == null || result.schema.Count == 0); }
public static void renderAsCSV(FlexResultSet resultSet, SqlRunParameters srp) { int writtenResultSets = 0; for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { writtenResultSets += 1; if (writtenResultSets > 1) { srp.openNewOutputStream(); } int columnCount = result.visibleColumnCount; //do header for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { srp.WriteToStream(columnName(result, colIndex)); if (colIndex + 1 < columnCount) { srp.WriteToStream(","); } else { srp.WriteToStream("\r\n"); } } //do data rows for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1) { for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { //todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work). object fieldData = result.data[rowIndex][colIndex]; SQLColumn fieldInfo = result.schema[colIndex]; if (fieldData == null || fieldData is DBNull) { //do nothing } else if (numberLikeDataTypesForCSV.Contains(fieldInfo.DataType)) { //todo: may be bug in German culture where they use , as the decimal separator. srp.WriteToStream(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)); } else if (dateLikeDataTypesForCSV.Contains(fieldInfo.DataType)) { srp.WriteToStream(escapeForCSV(String.Format("{0}.{1}", ((DateTime)fieldData).ToString("s"), ((DateTime)fieldData).ToString("fff") ))); } else if (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "timestamp") { byte[] d = (byte[])result.data[rowIndex][colIndex]; srp.WriteToStream(escapeForCSV(FieldScripting.formatBinary(d, d.Length))); } else if (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image") { srp.WriteToStream(escapeForCSV(FieldScripting.formatVarbinary(fieldData))); } else { srp.WriteToStream(escapeForCSV(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false))); } if (colIndex + 1 < columnCount) { srp.WriteToStream(","); } else { srp.WriteToStream("\r\n"); }; } } srp.worksheetIsValid = true; } } }
public static Boolean ResultIsRenderableAsCreateTable(FlexResult result) { return(!(result.schema == null || result.schema.Count == 0)); }
public static void renderAsXMLSpreadsheet(FlexResultSet resultSet, SqlRunParameters srp) { //todo: refactor this and FlexResultSet to to share code and have test coverage. srp.WriteToStream(Utils.GetResourceByName("TSqlFlex.Core.Resources.XMLSpreadsheetTemplateHeader.txt")); for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { int columnCount = result.visibleColumnCount; srp.WriteToStream(String.Format("<Worksheet ss:Name=\"Sheet{0}\">", i + 1)); srp.WriteToStream(String.Format("<Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">", columnCount, result.data.Count + 1 /* include header row */) ); //do header srp.WriteToStream("<Row>"); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", columnName(result, colIndex))); } srp.WriteToStream("</Row>\r\n"); //do data rows for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1) { srp.WriteToStream("<Row>"); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { //todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work). object fieldData = result.data[rowIndex][colIndex]; object[] fieldInfo = result.schema.Rows[colIndex].ItemArray; string fieldTypeName = fieldInfo[(int)FieldScripting.FieldInfo.DataType].ToString(); if (fieldData == null || fieldData is DBNull) { srp.WriteToStream("<Cell/>"); } else if (fieldTypeName == "bigint" || fieldTypeName == "numeric" || fieldTypeName == "smallint" || fieldTypeName == "decimal" || fieldTypeName == "smallmoney" || fieldTypeName == "int" || fieldTypeName == "tinyint" || fieldTypeName == "float" || fieldTypeName == "real" || fieldTypeName == "money" || fieldTypeName == "bit") { srp.WriteToStream(String.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)))); } else if (fieldTypeName == "date" || fieldTypeName == "datetime2" || fieldTypeName == "time" || fieldTypeName == "datetime" || fieldTypeName == "smalldatetime") { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}.{1}</Data></Cell>\r\n", escapeForXML(((DateTime)fieldData).ToString("s")), escapeForXML(((DateTime)fieldData).ToString("fff")) )); } else if (fieldTypeName == "binary" || fieldTypeName == "rowversion" || fieldTypeName == "timestamp") { byte[] d = (byte[])result.data[rowIndex][colIndex]; srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatBinary(d, d.Length)))); } else if (fieldTypeName == "varbinary" || fieldTypeName == "image") { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatVarbinary(fieldData)))); } else { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)))); } } srp.WriteToStream("</Row>\r\n"); } srp.WriteToStream("</Table></Worksheet>\r\n"); srp.worksheetIsValid = true; } } srp.WriteToStream("</Workbook>\r\n"); }
public static Boolean ResultIsRenderableAsScriptedData(FlexResult result) { return (result.schema != null && result.data != null && result.data.Count > 0); }
private static string columnName(FlexResult result, int zeroBasedColumnIndex) { if (String.IsNullOrEmpty(result.schema[zeroBasedColumnIndex].ColumnName)) { return "anonymousColumn" + (zeroBasedColumnIndex + 1).ToString(); } return escapeForXML(result.schema[zeroBasedColumnIndex].ColumnName); }
public static StringBuilder scriptDataAsInsertForSQL2008Plus(string tableName, FlexResult result, int MaxRowsInValuesClause) { const int INITIAL_CAPACITY = 50000; //This is small enough that it won't matter, but big enough to ensure minimal initial resizes. int calibrateBufferCapacityAfterRow = 0; DataTable schema = result.schema; List<object[]> data = result.data; int columnCount = result.visibleColumnCount; int rowCount = data.Count; StringBuilder buffer = new StringBuilder(INITIAL_CAPACITY); for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) { if (rowIndex % MaxRowsInValuesClause == 0) { buffer.Append("INSERT INTO " + tableName + " VALUES\r\n"); } buffer.Append(" ("); for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) { buffer.Append(valueAsTSQLLiteral(data[rowIndex][columnIndex], schema.Rows[columnIndex].ItemArray)); if (columnIndex + 1 < columnCount) { buffer.Append(","); } } if (rowIndex + 1 == rowCount || (rowIndex + 1) % MaxRowsInValuesClause == 0) { buffer.Append(");\r\n\r\n"); } else { buffer.Append("),\r\n"); } if (rowIndex == calibrateBufferCapacityAfterRow && rowIndex + 1 < rowCount) { //We are going to attempt to ensure there is an appropriate capacity to // minimize the number of capacity adjustments required. const int CHARACTERS_IN_INSERT_BLOCK = 27; const double TEN_PERCENT_SLACK = 1.1; int singleInsertLineLength = CHARACTERS_IN_INSERT_BLOCK + tableName.Length; int averageDataRowLength = (buffer.Length - singleInsertLineLength) / (rowIndex + 1); int totalInsertLineLengths = singleInsertLineLength * rowCount / 100; int totalDataRowLengths = (Int32)(averageDataRowLength * rowCount * TEN_PERCENT_SLACK); int newCapacity = totalInsertLineLengths + totalDataRowLengths; if (newCapacity > buffer.Capacity) { buffer.EnsureCapacity(newCapacity); } //re-evaluate after the first "calibrateBufferCapacityAfterRow" rows to hopefully get a better average. if (calibrateBufferCapacityAfterRow == 0) { calibrateBufferCapacityAfterRow = 99; } else if (calibrateBufferCapacityAfterRow == 99) { calibrateBufferCapacityAfterRow = 999; } else if (calibrateBufferCapacityAfterRow == 999) { calibrateBufferCapacityAfterRow = 9999; } else { calibrateBufferCapacityAfterRow = -1; //no more re-evaluations. } } } return buffer; }
public static StringBuilder scriptDataAsInsertForSQL2008Plus(string tableName, FlexResult result, int MaxRowsInValuesClause) { const int INITIAL_CAPACITY = 50000; //This is small enough that it won't matter, but big enough to ensure minimal initial resizes. int calibrateBufferCapacityAfterRow = 0; DataTable schema = result.schema; List <object[]> data = result.data; int columnCount = result.visibleColumnCount; int rowCount = data.Count; StringBuilder buffer = new StringBuilder(INITIAL_CAPACITY); for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) { if (rowIndex % MaxRowsInValuesClause == 0) { buffer.Append("INSERT INTO " + tableName + " VALUES\r\n"); } buffer.Append(" ("); for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) { buffer.Append(valueAsTSQLLiteral(data[rowIndex][columnIndex], schema.Rows[columnIndex].ItemArray)); if (columnIndex + 1 < columnCount) { buffer.Append(","); } } if (rowIndex + 1 == rowCount || (rowIndex + 1) % MaxRowsInValuesClause == 0) { buffer.Append(");\r\n\r\n"); } else { buffer.Append("),\r\n"); } if (rowIndex == calibrateBufferCapacityAfterRow && rowIndex + 1 < rowCount) { //We are going to attempt to ensure there is an appropriate capacity to // minimize the number of capacity adjustments required. const int CHARACTERS_IN_INSERT_BLOCK = 27; const double TEN_PERCENT_SLACK = 1.1; int singleInsertLineLength = CHARACTERS_IN_INSERT_BLOCK + tableName.Length; int averageDataRowLength = (buffer.Length - singleInsertLineLength) / (rowIndex + 1); int totalInsertLineLengths = singleInsertLineLength * rowCount / 100; int totalDataRowLengths = (Int32)(averageDataRowLength * rowCount * TEN_PERCENT_SLACK); int newCapacity = totalInsertLineLengths + totalDataRowLengths; if (newCapacity > buffer.Capacity) { buffer.EnsureCapacity(newCapacity); } //re-evaluate after the first "calibrateBufferCapacityAfterRow" rows to hopefully get a better average. if (calibrateBufferCapacityAfterRow == 0) { calibrateBufferCapacityAfterRow = 99; } else if (calibrateBufferCapacityAfterRow == 99) { calibrateBufferCapacityAfterRow = 999; } else if (calibrateBufferCapacityAfterRow == 999) { calibrateBufferCapacityAfterRow = 9999; } else { calibrateBufferCapacityAfterRow = -1; //no more re-evaluations. } } } return(buffer); }
public static Boolean ResultIsRenderableAsScriptedData(FlexResult result) { return(result.schema != null && result.data != null && result.data.Count > 0); }
public static Boolean ResultIsRenderableAsCreateTable(FlexResult result) { return(result.schema != null); }
private static void processData(SqlDataReader reader, FlexResult result) { try { int fieldCount = reader.FieldCount; var data = new List<Object[]>(); while (reader.Read()) { Object[] values = new Object[fieldCount]; reader.GetValues(values); data.Add(values); } result.data = data; } catch (Exception ex) { result.exceptions.Add(ex); } }
public static FlexResultSet AnalyzeResultWithRollback(SqlConnection openConnection, SqlRunParameters srp, BackgroundWorker bw = null) { FlexResultSet resultSet = new FlexResultSet(); throwExceptionIfConnectionIsNotOpen(openConnection); SqlTransaction transaction = openConnection.BeginTransaction("Tran"); SqlDataReader reader = null; try { SqlCommand cmd = new SqlCommand(srp.sqlToRun, openConnection, transaction); //todo: this is a bad way of doing this. Need to abstract further. bw.ReportProgress(5, "Running query..."); reader = executeSQL(resultSet, cmd, reader); int progress = 50; bw.ReportProgress(progress, "Processing results..."); do { FlexResult result = new FlexResult(); if (reader != null) { try { result.recordsAffected = reader.RecordsAffected; processSchemaInfo(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); processData(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } } foreach (Exception ex in result.exceptions) { resultSet.exceptions.Add(ex); } resultSet.results.Add(result); } while (reader != null && reader.NextResult()); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } finally { cleanupReader(reader); rollbackTransaction(transaction); } return(resultSet); }
public static FlexResultSet AnalyzeResultWithRollback(SqlConnection openConnection, SqlRunParameters srp, BackgroundWorker bw = null) { FlexResultSet resultSet = new FlexResultSet(); throwExceptionIfConnectionIsNotOpen(openConnection); SqlTransaction transaction = openConnection.BeginTransaction("Tran"); SqlDataReader reader = null; try { srp.command = new SqlCommand(srp.sqlToRun, openConnection, transaction); var cmd = srp.command; cmd.CommandTimeout = 0; // wait forever. //todo: this is a bad way of doing this. Need to abstract further. bw.ReportProgress(5, "Running query..."); reader = executeSQL(resultSet, cmd, reader); int progress = 50; bw.ReportProgress(progress, "Processing results..."); do { FlexResult result = new FlexResult(); if (reader != null) { try { result.recordsAffected = reader.RecordsAffected; processSchemaInfo(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); processData(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } } foreach (Exception ex in result.exceptions) { resultSet.exceptions.Add(ex); } resultSet.results.Add(result); } while (reader != null && reader.NextResult()); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } finally { cleanupReader(reader); rollbackTransaction(transaction); } return resultSet; }
public static Boolean ResultIsRenderableAsCreateTable(FlexResult result) { return (result.schema != null); }
private static string columnName(FlexResult result, int zeroBasedColumnIndex) { string headerName = (string)result.schema.Rows[zeroBasedColumnIndex].ItemArray[(int)FieldScripting.FieldInfo.Name]; if (headerName == "") { return "anonymousColumn" + (zeroBasedColumnIndex + 1).ToString(); } return escapeForCSV(headerName); }
public static StringBuilder ScriptResultDataAsInsert(FlexResult result, string tableName, int maxRowsInValuesClause) { if (!ResultIsRenderableAsCreateTable(result)) { return new StringBuilder("--No schema for result from query."); } if (!ResultIsRenderableAsScriptedData(result)) { return new StringBuilder("--No rows were returned from the query."); } return scriptDataAsInsertForSQL2008Plus(tableName, result, maxRowsInValuesClause); }
private static string columnName(FlexResult result, int zeroBasedColumnIndex) { string headerName = (string)result.schema[zeroBasedColumnIndex].ColumnName; if (headerName == "") { return "anonymousColumn" + (zeroBasedColumnIndex + 1).ToString(); } return escapeForCSV(headerName); }