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