private bool QueryExecuter(string listStringQuery, int commandTimeout = 30) { bool bReturn = false; sbResultAll.Clear(); List <string> querys = TranString.ReadQuery(listStringQuery); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append(DateTime.Now + Environment.NewLine); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); foreach (var query in querys) { try { sbResultAll.Append("-->>---------------------------------"); sbResultAll.Append(query + Environment.NewLine); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); if (query.Trim().ToUpper().StartsWith("USE")) { string[] database = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None); connectionString = SetConnectionString(database[1]); comboBoxDatabase.InvokeIfRequired(s => { s.Text = ""; s.SelectedText = database[1].ToString(); }); } string result = string.Empty; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); conn.InfoMessage += Conn_InfoMessage; // message hook (like backup message) using (SqlCommand cmd = conn.CreateCommand()) { cmd.StatementCompleted += Cmd_StatementCompleted; // retrive row count cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.CommandTimeout = commandTimeout; SqlDataReader reader = cmd.ExecuteReader(); int recordsAffected = reader.RecordsAffected; do { StringBuilder sb = new StringBuilder(); string Header = string.Empty; string Line = string.Empty; DataTable schemaTable = reader.GetSchemaTable(); if (schemaTable != null) { try { foreach (DataRow row in schemaTable.Rows) { foreach (DataColumn column in schemaTable.Columns) { if (column.ColumnName == "ColumnName") { Header = Header + row[column] + "\t"; Line = Line + "------- "; } } } Header = Header + Environment.NewLine; Line = Line + Environment.NewLine; sb.Append(Header); sb.Append(Line); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetValue(i).ToString() == "System.Byte[]") { sb.Append("0x" + BitConverter.ToString((byte[])reader.GetValue(i)).Replace("-", "")); } else { sb.Append(reader.GetValue(i).ToString()); } sb.Append("\t"); } sb.Append(Environment.NewLine); } } catch (SqlException ex) { errorCnt++; sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("--SQL Exception" + Environment.NewLine); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); for (int i = 0; i < ex.Errors.Count; i++) { sbResultAll.Append("Inner SqlException No #" + i + Environment.NewLine + "Message: " + ex.Errors[i].Message + Environment.NewLine + "Source: " + ex.Errors[i].Source + Environment.NewLine + "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine); } } finally { sb.Append(Environment.NewLine); sbResultAll.Append(sb); sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows")); } } else { string[] Query = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None); if ( Query[0].Equals("update", StringComparison.OrdinalIgnoreCase) || Query[0].Equals("insert", StringComparison.OrdinalIgnoreCase) || Query[0].Equals("delete", StringComparison.OrdinalIgnoreCase) || Query[1].Equals("update", StringComparison.OrdinalIgnoreCase) || Query[1].Equals("insert", StringComparison.OrdinalIgnoreCase) || Query[1].Equals("delete", StringComparison.OrdinalIgnoreCase) ) { sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows")); } else { sbResultAll.Append(string.Format("Commands completed successfully." + Environment.NewLine + Environment.NewLine)); } } reader.NextResult(); } while (reader.HasRows); } conn.Close(); bReturn = true; } if (checkBoxResultUpdateByGo.Checked) { fastColoredTextBoxResult.InvokeIfRequired(s => { s.Text = sbResultAll.ToString(); }); } } catch (SqlException ex) { errorCnt++; sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("--SQL Exception" + Environment.NewLine); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); for (int i = 0; i < ex.Errors.Count; i++) { sbResultAll.Append("SqlException No #" + i + Environment.NewLine + "Message: " + ex.Errors[i].Message + Environment.NewLine + "Source: " + ex.Errors[i].Source + Environment.NewLine + "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine); } sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); if (checkBoxResultUpdateByGo.Checked) { fastColoredTextBoxResult.InvokeIfRequired(s => { s.Text = sbResultAll.ToString(); }); } bReturn = false; } catch (Exception ex) { errorCnt++; sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("--Exception" + Environment.NewLine); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append(ex.Message); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); if (checkBoxResultUpdateByGo.Checked) { fastColoredTextBoxResult.InvokeIfRequired(s => { s.Text = sbResultAll.ToString(); }); } bReturn = false; } } fastColoredTextBoxResult.InvokeIfRequired(s => { s.Text = sbResultAll.ToString(); }); return(bReturn); }
private bool QueryExecuter(string listStringQuery, int commandTimeout = 30) { bool bReturn = false; sbResultAll.Clear(); List <string> querys = TranString.ReadQuery(listStringQuery); startTime = DateTime.Now; foreach (var query in querys) { try { if (CancelRequested) { break; } sbResultAll.Append("-->>---------------------------------"); sbResultAll.Append(query + Environment.NewLine); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); if (query.Trim().ToUpper().StartsWith("USE")) { string[] database = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None); ConnectionString = SetConnectionString(database[1]); } string result = string.Empty; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); conn.InfoMessage += Conn_InfoMessage; // message hook (like backup message) using (SqlCommand cmd = conn.CreateCommand()) { cmd.StatementCompleted += Cmd_StatementCompleted; // retrive row count cmd.CommandType = CommandType.Text; //// execute only estimation plan text //cmd.CommandText = "SET SHOWPLAN_ALL ON"; //cmd.ExecuteNonQuery(); //// execute only estimation plan xml //cmd.CommandText = "SET SHOWPLAN_XML ON"; //cmd.ExecuteNonQuery(); cmd.CommandText = query; SqlDataReader reader = cmd.ExecuteReader(); do { StringBuilder sb = new StringBuilder(); string Header = string.Empty; string Line = string.Empty; DataTable schemaTable = reader.GetSchemaTable(); if (schemaTable != null) { try { foreach (DataRow row in schemaTable.Rows) { foreach (DataColumn column in schemaTable.Columns) { if (column.ColumnName == "ColumnName") { Header = Header + row[column] + ColumnDelimiter; } } } Header = Header + Environment.NewLine; // Line = Line + Environment.NewLine; sb.Append(Header); sb.Append(Line); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetValue(i).ToString() == "System.Byte[]") { sb.Append("0x" + BitConverter.ToString((byte[])reader.GetValue(i)).Replace("-", "")); } else { sb.Append(reader.GetValue(i).ToString()); } sb.Append(ColumnDelimiter); } sb.Append(Environment.NewLine); } } catch (SqlException ex) { ErrorCnt++; sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("--SQL Exception" + Environment.NewLine); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); for (int i = 0; i < ex.Errors.Count; i++) { sbResultAll.Append("Inner SqlException No #" + i + Environment.NewLine + "Message: " + ex.Errors[i].Message + Environment.NewLine + "Source: " + ex.Errors[i].Source + Environment.NewLine + "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine); } } finally { sb.Append(Environment.NewLine); sbResultAll.Append(sb); sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows")); } } else { string[] Query = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None); if ( Query[0].Equals("update", StringComparison.OrdinalIgnoreCase) || Query[0].Equals("insert", StringComparison.OrdinalIgnoreCase) || Query[0].Equals("delete", StringComparison.OrdinalIgnoreCase) || Query[1].Equals("update", StringComparison.OrdinalIgnoreCase) || Query[1].Equals("insert", StringComparison.OrdinalIgnoreCase) || Query[1].Equals("delete", StringComparison.OrdinalIgnoreCase) ) { sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows")); } else { sbResultAll.Append(string.Format("Commands completed successfully." + Environment.NewLine + Environment.NewLine)); } } } while (reader.NextResult()); } conn.Close(); bReturn = true; } } catch (SqlException ex) { ErrorCnt++; sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("--SQL Exception" + Environment.NewLine); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); for (int i = 0; i < ex.Errors.Count; i++) { sbResultAll.Append("SqlException No #" + i + Environment.NewLine + "Message: " + ex.Errors[i].Message + Environment.NewLine + "Source: " + ex.Errors[i].Source + Environment.NewLine + "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine); } sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); bReturn = false; } catch (Exception ex) { ErrorCnt++; sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("--Exception" + Environment.NewLine); sbResultAll.Append("-->>---------------------------------" + Environment.NewLine); sbResultAll.Append(ex.Message); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); bReturn = false; } } //sbResultAll.Append(Environment.NewLine + "-->>---------------------------------" + Environment.NewLine); endTime = DateTime.Now; //sbResultAll.Append("EndTime : " + endTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + Environment.NewLine); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); sbResultAll.Append(Environment.NewLine + "-->>---------------------------------" + Environment.NewLine); sbResultAll.Append("StartTime : " + startTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + Environment.NewLine); sbResultAll.Append("EndTime : " + endTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + Environment.NewLine); TimeSpan diff = endTime - startTime; string formatted = string.Format( "TotalExecutionTime : {0} days, {1} hours, {2} minutes, {3} seconds, {4} miliseconds", diff.Days, diff.Hours, diff.Minutes, diff.Seconds, diff.Milliseconds ); sbResultAll.Append(formatted + Environment.NewLine); sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine); return(bReturn); }