public IDbDataAdapter ExecuteQuery(string sqlQuery, Hashtable htParameters, DataSet objDS, string tablename)
{
IDbDataAdapter dbAdapter = null;
IDbCommand dbCommand = null;
//create command
dbCommand = CreateCommand(sqlQuery);
//create adapter
dbAdapter = CreateAdapter(dbCommand, CommandType.SelectCommand);
//setup parameters
// GenerateParameters(dbCommand, htParameters);
try
{
//run query
((OleDbDataAdapter)dbAdapter).Fill(objDS, tablename);
return(dbAdapter);
}
catch (Exception genEx)
{
Shared_Functions.Logged_ErrorMessage(genEx.Message);
throw genEx;
}
finally
{
dbCommand.Connection.Close();
}
}
public string ExecuteQuery(string sqlQuery)
{
string value = string.Empty;
IDbCommand dbCommand = null;
//create command
try
{
dbCommand = CreateCommand(sqlQuery);
OleDbDataReader odr = (OleDbDataReader)dbCommand.ExecuteReader();
while (odr.Read())
{
value = odr[0].ToString();
}
return(value);
}
catch (Exception ex)
{
Shared_Functions.Logged_ErrorMessage(ex.Message);
//dbCommand.Connection.Close();
//dbCommand.Connection.Dispose();
}
finally
{
//close con
dbCommand.Connection.Close();
dbCommand.Connection.Dispose();
}
return(string.Empty);
}
public object ExecuteNonQuery(string sqlQuery, Hashtable htParameters)
{
IDbCommand dbCommand = default(IDbCommand);
object result = null;
//create command
dbCommand = CreateCommand(sqlQuery);
//create parameters for the query
// GenerateParameters(dbCommand, htParameters);
try
{
//run
result = dbCommand.ExecuteNonQuery();
return(result);
}
catch (Exception genEx)
{
Shared_Functions.Logged_ErrorMessage(genEx.Message);
// throw (genEx);
}
finally
{
//close con
//close con
dbCommand.Connection.Close();
dbCommand.Connection.Dispose();
}
return(result);
}
private void tmrDateTime_Tick(object sender, EventArgs e)
{
try
{
this.lblDateTime.Text = DateTime.Now.ToString("g");
}
catch (Exception ex)
{ Shared_Functions.Logged_ErrorMessage(ex.Message); }
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
f_title = new System.Drawing.Font("Arial", 15, FontStyle.Bold); //GraphicsUnit.Point);
DataTable dt = new DataTable();
//dt = CommonFunctions.GetDataSet("Select * from Printermaster where id='1'").Tables[0];
StringBuilder header = new StringBuilder();
//header.Append(dt.Rows[0][1].ToString() + "\n" + dt.Rows[0][2].ToString() + "\n" + dt.Rows[0][3].ToString());
header.Append(Environment.NewLine);
header.Append("-----------------------------------------------------------------------------------------------------------");
header.Append(Environment.NewLine);
header.Append("Rake No\t : " + txtRakeNo.Text);
header.Append(Environment.NewLine);
header.Append("Direction\t: " + "" + "\n"); //In
header.Append(Environment.NewLine);
var cdt = DateTime.Now;
header.Append("Weighment Date : " + txtDate.Text + " " + txtTime.Text);
header.Append(Environment.NewLine);
header.Append("Print Date : " + cdt.ToString("g"));
header.Append(Environment.NewLine);
header.Append("------------------------------------------------------------------------------------------------------------");
header.Append(Environment.NewLine);
header.Append(Environment.NewLine);
StringBuilder footer = new StringBuilder();
footer.Append(Environment.NewLine);
footer.Append(Environment.NewLine);
footer.Append("NS=>Normal Speed,MS=>Marginal Speed,OS=>Over Speed");
footer.Append(Environment.NewLine);
footer.Append("Come Again");
footer.Append(Environment.NewLine);
footer.Append("All The Best");
footer.Append(Environment.NewLine);
footer.Append(Environment.NewLine);
//if (op.ToLower() != "admin")
// footer.Append("Operator : " + op);
//else
footer.Append("Operator:" + "Operator");
footer.Append(Environment.NewLine);
footer.Append("Signature:");
footer.Append(Environment.NewLine);
if (SetupThePrinting(header.ToString(), footer.ToString()))
{
PrintPreviewDialog MyPrintPreviewDialog = new PrintPreviewDialog();
MyPrintPreviewDialog.Document = printDocument1;
MyPrintPreviewDialog.ShowDialog();
}
}
catch (Exception ex)
{
Shared_Functions.Logged_ErrorMessage(ex.Message);
MessageBox.Show(ex.Message.ToString(), ex.Message.GetType().ToString());
}
}
// The method that calls all other functions
public bool DrawDataGridView(Graphics g)
{
try
{
Calculate(g);
//if (headerf != 1)
//{
DrawHeader(g);
//}
bool bContinue = DrawRows(g);
return(bContinue);
}
catch (Exception ex)
{
Shared_Functions.Logged_ErrorMessage(ex.Message);
MessageBox.Show("Operation failed: " + ex.Message.ToString(), Application.ProductName + " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
public void ExecuteNonQuery(string sqlQuery)
{
IDbCommand dbCommand = default(IDbCommand);
dbCommand = CreateCommand(sqlQuery);
try
{
dbCommand.ExecuteNonQuery();
}
catch (Exception genEx)
{
Shared_Functions.Logged_ErrorMessage(genEx.Message);
throw (genEx);
}
finally
{
//close con
dbCommand.Connection.Close();
dbCommand.Connection.Dispose();
}
}
public IDbConnection CreateConnection()
{
// SqlConnection dbConn = new SqlConnection();
var dbConn = new OleDbConnection();
dbConn.ConnectionString = ConnectionString;
try
{
dbConn.Open();
return(dbConn);
}
catch (Exception ex)
{
Shared_Functions.Logged_ErrorMessage(ex.Message);
dbConn.Close();
dbConn.Dispose();
throw ex;
}
}
private void GenerateParameters(IDbCommand dbCommand, Hashtable htParameters)
{
SqlParameter parameter = default(SqlParameter);
Regex re = default(Regex);
Match regMatch = default(Match);
string sqlText = null;
string expText = null;
string attribute = null;
string ioType = null;
bool isUpdateStmt = false;
int indexOfWhere = 0;
dbCommand.CommandTimeout = 3600;
//
//check parameters
// Validator.AssertNotNull(Me, dbCommand)
try
{
//regular exressions are used to parse the sql string and get the tokens in each parameter
//following regular expression will extract all values between { and } into attrib
//
expText = "{(?<attrib>[^{]*)}";
sqlText = dbCommand.CommandText;
//if the sql command is UPDATE, DELETE , all parameters after the WHERE clause should
// have sourceversion property as original. In the case of an INSERT statement
// all parameters will have sourceversion property as original.
if (sqlText.IndexOf("UPDATE") > -1 | sqlText.IndexOf("DELETE") > -1)
{
isUpdateStmt = true;
indexOfWhere = sqlText.IndexOf("WHERE");
}
else if (sqlText.IndexOf("INSERT") > -1)
{
isUpdateStmt = true;
indexOfWhere = 0;
}
else
{
isUpdateStmt = false;
}
//for each parameter value in the hashtable, create a parameter object for associated
//tokens in the sql string
//
if ((htParameters != null))
{
foreach (string item in htParameters.Keys)
{
//check if parameter is in the format of '@paramname.columnname
//set attributes of the sqlparameter object appropriately
//
parameter = new SqlParameter();
re = new Regex("(?<paramname>\\w+)\\.?(?<columnname>\\w+)?");
regMatch = re.Match(item);
if (regMatch.Success)
{
parameter.ParameterName = regMatch.Result("${paramname}");
if (string.IsNullOrEmpty(regMatch.Result("${columnname}")))
{
parameter.SourceColumn = regMatch.Result("${paramname}");
}
else
{
parameter.SourceColumn = regMatch.Result("${columnname}");
}
}
//set the value
if ((htParameters[item] != null))
{
parameter.Value = htParameters[item];
}
//find the attribute { } associated witht he parameter token
re = new Regex(item + expText, RegexOptions.IgnoreCase);
regMatch = re.Match(sqlText);
if (regMatch.Success)
{
//if found after WHERE, update sourceversion property
if (isUpdateStmt & regMatch.Index > indexOfWhere)
{
parameter.SourceVersion = DataRowVersion.Original;
}
//get attribute within { and }
attribute = regMatch.Result("${attrib}");
//extract i/o/io/t
re = new Regex("(\\bi(o)?\\b)|(\\bo\\b)|(\\br\\b)|(\\bt\\b)");
regMatch = re.Match(attribute);
if (regMatch.Success)
{
ioType = regMatch.Value;
if (ioType == "i")
{
parameter.Direction = ParameterDirection.Input;
}
else if (ioType == "o")
{
parameter.Direction = ParameterDirection.Output;
}
else if (ioType == "io")
{
parameter.Direction = ParameterDirection.InputOutput;
}
else if (ioType == "r")
{
parameter.Direction = ParameterDirection.ReturnValue;
}
else if (ioType == "t")
{
parameter.Value = null;
}
}
//get size attribute for parameter
re = new Regex("\\d+\\b");
regMatch = re.Match(attribute);
if (regMatch.Success)
{
parameter.Size = int.Parse(regMatch.Value);
}
}
dbCommand.Parameters.Add(parameter);
}
//change the sql string to appropriate format by removing all custom tokens.
expText = "{[^{]*}";
re = new Regex(expText, RegexOptions.IgnoreCase);
sqlText = re.Replace(sqlText, "");
if (dbCommand.CommandType == System.Data.CommandType.StoredProcedure)
{
re = new Regex("(?<commandName>[^\\s]*)");
dbCommand.CommandText = re.Match(dbCommand.CommandText).Result("${commandName}");
}
else
{
re = new Regex("(?<paramname>\\w+)\\.\\w+");
dbCommand.CommandText = re.Replace(sqlText, "$1");
}
}
}
catch (Exception genEx)
{
Shared_Functions.Logged_ErrorMessage(genEx.Message);
}
}
