public int SPExecutorSQLClient(string sqlCommand, string connString) { int returnValue = 0; SqlResults ucSQLResults = new SqlResults(); try { using (SqlConnection con = new SqlConnection(connString)) //here is the error { con.Open(); SqlCommand oldbCmd = new SqlCommand(sqlCommand, con); oldbCmd.CommandType = CommandType.StoredProcedure; SqlParameter rParam = new SqlParameter(); rParam.Direction = ParameterDirection.ReturnValue; rParam.DbType = DbType.Int64; oldbCmd.Parameters.Add(rParam); Object res = oldbCmd.ExecuteScalar(); con.Close(); returnValue = Convert.ToInt32(rParam.Value); } } catch (Exception ex) { return(-1); } return(returnValue); }
public frmSQLResults(SqlResults sr) { InitializeComponent(); this.sqlResult.AssignDataSourceToDGV(sr.GetQueryDataSource(), sr.GetConnectionString()); this.sqlResult.AddInputQuery(sr.GetQueryString(), true, false); this.sqlResult.AddTextToMessages(sr.GetMessage(), true, false); sqlResult.ShowSizeButtons(false); this.Text = this.Text + " ( " + GetFromText(sr.GetQueryString()) + " )"; }
private string ExecuteSQL(string sqlCommand, out int numOfRows) { numOfRows = 0; string returnString = ""; SqlResults ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlCommand, con); OleDbDataAdapter oldbDA = new OleDbDataAdapter(oldbCmd); DataSet ds = new DataSet(); int numRows = oldbDA.Fill(ds); numOfRows = numRows; if (ds.Tables[0].Rows.Count > 20) { int waitDebug = 0; numOfRows = 0; return(""); } if (numRows > 1) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { foreach (object item in ds.Tables[0].Rows[j].ItemArray) { returnString += (item.ToString() + Environment.NewLine); } } //return returnString; } else if (numRows == 1) { foreach (object item in ds.Tables[0].Rows[0].ItemArray) { returnString += (item.ToString() + Environment.NewLine); } // returnString += (ds.Tables[0].Rows[0].ToString() + Environment.NewLine); } con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } return(returnString); }
private void ShowSQLResultsInNewForm(SqlResults sr) { frmSQLResults fsr = new frmSQLResults(sr); try { fsr.ShowDialog(); } catch (Exception ex) { fsr.ShowDialog(); } }
private string GetEnglishString(int id) { string sqlCommand = "Select english from sm_text where pk_sm_text = " + id.ToString(); string returnString = ""; SqlResults ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlCommand, con); OleDbDataAdapter oldbDA = new OleDbDataAdapter(oldbCmd); DataSet ds = new DataSet(); int numRows = oldbDA.Fill(ds); if (numRows == 1) { foreach (object item in ds.Tables[0].Rows[0].ItemArray) { returnString += (item.ToString().Trim() + Environment.NewLine); } } con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } if (returnString.Contains("«") || returnString.Contains(";")) { ReplaceFunnyChars(id, true); } string paramString = ProcessVariables(returnString, true); return(returnString + Environment.NewLine + /*"-----------" + Environment.NewLine*/ ">> " + paramString); // return returnString; }
public bool CommandExecutor(string sqlCommand, string connString) { bool returnValue = true; SqlResults ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlCommand, con); oldbCmd.ExecuteNonQuery(); con.Close(); } } catch (Exception ex) { return(false); } return(returnValue); }
private string GetFrenchString(int id) { string dbfFile = "E:\\client_data\\Nossack\\sm_text"; // string sqlCommand = "Select french from sm_text where pk_sm_text = " + id.ToString(); string sqlCommand = "Select french from " + dbfFile + " where pk_sm_text = " + id.ToString(); string returnString = ""; SqlResults ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlCommand, con); OleDbDataAdapter oldbDA = new OleDbDataAdapter(oldbCmd); DataSet ds = new DataSet(); int numRows = oldbDA.Fill(ds); if (numRows == 1) { for (int i = 0; i < ds.Tables[0].Rows[0].ItemArray.Count(); i++)//each (object item in ds.Tables[0].Rows[0].ItemArray) { string sentance = ds.Tables[0].Rows[0].ItemArray[i].ToString().Trim(); string[] sentances = sentance.Split('+'); sentance = ""; for (int j = 0; j < sentances.Length; j++) { if (sentances[j].Trim().Length <= 0) { continue; } if (!sentances[j].Contains('(') && !sentances[j].Contains(')')) { if (sentances[j].Trim()[0] != '\"') { sentances[j] = ("\"" + sentances[j]); } if (sentances[j].Trim()[sentances[j].Trim().Length - 1] != '\"') { sentances[j] = (sentances[j] + "\""); } } sentance += (sentances[j] + "+"); } if (sentance[sentance.Length - 1] == '+') { sentance = sentance.Substring(0, sentance.Length - 1); } returnString += (sentance.ToString().Trim() + Environment.NewLine); } } con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } if (returnString.Contains("«") || returnString.Contains(";")) { ReplaceFunnyChars(id, false); } string paramString = ProcessVariables(returnString, true); return(returnString + /*Environment.NewLine + "----------" +*/ Environment.NewLine + ">> " + paramString + Environment.NewLine + "----------------------------------------------------------------------------------------------" + Environment.NewLine + "English " + Environment.NewLine + "----------------------------------------------------------------------------------------------" + Environment.NewLine); }
private void ReplaceFunnyChars(int id, bool bEnglish) { string sqlCommand = ""; if (bEnglish) { sqlCommand = "Select english from sm_text where pk_sm_text = " + id.ToString(); } else { sqlCommand = "Select french from sm_text where pk_sm_text = " + id.ToString(); } string sentance = ""; SqlResults ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlCommand, con); OleDbDataAdapter oldbDA = new OleDbDataAdapter(oldbCmd); DataSet ds = new DataSet(); int numRows = oldbDA.Fill(ds); if (numRows == 1) { for (int i = 0; i < ds.Tables[0].Rows[0].ItemArray.Count(); i++) { sentance = ds.Tables[0].Rows[0].ItemArray[i].ToString().Trim(); } } con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } sentance = sentance.Replace("«", "\""); sentance = sentance.Replace(";", String.Empty); sentance = sentance.Replace("'", "''"); if (bEnglish) { sqlCommand = "update sm_text set english = '" + sentance + "' where pk_sm_text = " + id.ToString(); } else { sqlCommand = "update sm_text set french = '" + sentance + "' where pk_sm_text = " + id.ToString(); } ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlCommand, con); oldbCmd.ExecuteNonQuery(); con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public List <SqlResults> DataExecutor(string selectCommand, string connString, bool treatSemicolonAsNewLine = false) { List <SqlResults> sqlResults = new List <SqlResults>(); string[] sqlStrs = new string[] { string.Empty }; if (!treatSemicolonAsNewLine) { sqlStrs = selectCommand.Split(';'); } else { sqlStrs[0] = selectCommand.Replace(';', ' '); } for (int i = 0; i < sqlStrs.Length; i++) { using (StreamWriter w = File.AppendText(SQLExecutor.sqlCommandLogFile)) { w.WriteLine(sqlStrs[i]); } sqlStrs[i] = sqlStrs[i].Replace(Environment.NewLine, ""); if (String.IsNullOrEmpty(sqlStrs[i])) { continue; } SqlResults ucSQLResults = new SqlResults(); try { using (OleDbConnection con = new OleDbConnection(connString)) //here is the error { con.Open(); OleDbCommand oldbCmd = new OleDbCommand(sqlStrs[i], con); OleDbDataAdapter oldbDA = new OleDbDataAdapter(oldbCmd); DataSet ds = new DataSet(); int numRows = oldbDA.Fill(ds); string sqlMsg = "Total Rows returned (" + numRows.ToString() + ")."; if (ds.Tables.Count > 0) { ucSQLResults.AssignDataSourceToDGV(ds.Tables[0], connString); } ucSQLResults.AddTextToMessages(sqlMsg, true, false); ucSQLResults.AddInputQuery(sqlStrs[i], true, false); con.Close(); } } catch (Exception ex) { string queryCommand = sqlStrs[i]; ucSQLResults.AddTextToMessages(queryCommand, true, false); string sqlMsg = "Error: " + ex.ToString(); ucSQLResults.AddTextToMessages(sqlMsg, false, true); ucSQLResults.AddInputQuery(queryCommand, false, true); ucSQLResults.AddConnString(connString); } ucSQLResults.AdjustHeightBasedOnTotalRows(); sqlResults.Add(ucSQLResults); } return(sqlResults); }