public static string SimpleTextQuery(string TableName, string Where, string OrderBy, string Columns)
{
string strCommand = "";
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
strCommand = "SELECT " + Columns + " FROM " + TableName + " " + Where + " " + OrderBy;
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.CommandText = strCommand;
Comm.Connection = Conn;
string returnValue = Comm.ExecuteScalar().ToString();
Conn.Close();
Comm.Dispose();
return(returnValue);
}
}
catch
{
Console.WriteLine("SimpleTextQuery hata <!error!>(" + strCommand + ")");
OlayGunluk.Olay("SimpleTextQuery hata <!error!>(" + strCommand + ")");
return("<!error!>");
}
}
public static Hashtable DeleteData(string TableName, string Where)
{
Hashtable HT = new Hashtable();
string strCommand = "";
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.CommandText = "DELETE From " + TableName + " " + Where;
strCommand = Comm.CommandText;
Comm.Connection = Conn;
Comm.ExecuteNonQuery();
Conn.Close();
Comm.Dispose();
}
}
catch (Exception Ex)
{
Console.WriteLine("DeleteData hatasý:(" + strCommand + ")" + Ex.Message);
OlayGunluk.Olay("DeleteData hatasý:(" + strCommand + ")" + Ex.Message);
HT.Add("Error", Ex.Message);
}
return(HT);
}
public static DataSet ExecuteDataset(string strSqlCommand)
{
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DbDataAdapter DAP = Settings.Factory.CreateDataAdapter();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.CommandText = strSqlCommand;
Comm.Connection = Conn;
DAP.SelectCommand = Comm;
DAP.Fill(DS);
return(DS);
}
}
catch
{
Console.WriteLine("ExecuteDataset hatasý:(" + strSqlCommand + ")");
OlayGunluk.Olay("ExecuExecuteDataset hatasý:(" + strSqlCommand + ")");
return(null);
}
}
public static Hashtable ExecuteSQL(string strSqlCommand)
{
Hashtable HT = new Hashtable();
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.CommandText = strSqlCommand;
Comm.Connection = Conn;
HT.Add("Value", Comm.ExecuteScalar());
Conn.Close();
Comm.Dispose();
}
}
catch (Exception Ex)
{
Console.WriteLine("ExecuteSQL hatasý:(" + strSqlCommand + ")" + Ex.Message);
OlayGunluk.Olay("ExecuteSQL hatasý:(" + strSqlCommand + ")" + Ex.Message);
HT.Add("Error", Ex.Message);
}
return(HT);
}
public static Hashtable SecurityQuery(string TableName, Hashtable Where, string OrderBy, string Columns)
{
Hashtable HT = new Hashtable();
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbDataAdapter DAP = Settings.Factory.CreateDataAdapter();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
Comm.CommandText = "SELECT " + Columns + " FROM " + TableName + " {0} " + OrderBy;
string strWhere = "";
int qCount = 0;
foreach (string var in Where.Keys as ICollection)
{
if (var[0] != '@')
{
if ((qCount / 2) == 0)
{
strWhere += " " + Where["@" + qCount] + " ";
}
strWhere += "[" + var + "] = @" + var + " ";
qCount++;
DbParameter Parameter = Settings.Factory.CreateParameter();
Parameter.ParameterName = "@" + var;
Parameter.Value = Where[var];
Comm.Parameters.Add(Parameter);
}
}
strWhere = "Where " + strWhere;
string strCommand = string.Format(Comm.CommandText, strWhere);
Comm.CommandText = strCommand;
DAP.SelectCommand = Comm;
DAP.Fill(DS);
DAP.Fill(DT);
HT.Add("DataSet", DS);
HT.Add("DataTable", DT);
HT.Add("DataCount", DS.Tables[0].Rows.Count);
Conn.Close();
DAP.Dispose();
Comm.Dispose();
}
}
catch (Exception Ex)
{
Console.WriteLine("SecurityQuery hata:" + Ex.Message);
OlayGunluk.Olay("SecurityQuery hata:" + Ex.Message);
HT.Add("Error", Ex.Message);
}
return(HT);
}
public static bool SendResult(
RequestCommadType _resultRequest, bool _result, string _resultReason, IPAddress _ipAddress)
{
string vs_resultRequest;
string vs_result;
byte[] outStream = null;
TcpClient tcpClient = null;
NetworkStream serverStream = null;
try
{
vs_resultRequest = ((int)_resultRequest).ToString("X3");
vs_result = Convert.ToInt16(_result).ToString("X3");
tcpClient = new TcpClient();
tcpClient.Connect(_ipAddress, 506);
serverStream = tcpClient.GetStream();
string CommandDatas = string.Format(
"{0}#{1}#{2}#{3}", "004", vs_resultRequest, vs_result, _resultReason);
outStream = Encoding.ASCII.GetBytes(CommandDatas);
serverStream.Write(outStream, 0, outStream.Length);
return(true);
}
catch (Exception ex)
{
OlayGunluk.Olay("QLUClientCommunication SendResult:" + ex.Message);
return(false);
}
finally
{
if (serverStream != null)
{
serverStream.Flush();
}
if (tcpClient.Connected)
{
tcpClient.Close();
}
}
}
public static Hashtable ExecuteWithParameter(string strSqlCommand, Dictionary <string, object> dicParams)
{
Hashtable HT = new Hashtable();
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbDataAdapter DAP = Settings.Factory.CreateDataAdapter();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
Comm.CommandText = strSqlCommand;
DAP.SelectCommand = Comm;
foreach (var item in dicParams)
{
DbParameter Param = Settings.Factory.CreateParameter();
Param.ParameterName = item.Key;
Param.Value = item.Value;
Comm.Parameters.Add(Param);
}
DAP.Fill(DS);
DAP.Fill(DT);
HT.Add("Command", Comm.CommandText);
HT.Add("DataSet", DS);
HT.Add("DataTable", DT);
HT.Add("DataCount", DS.Tables[0].Rows.Count);
Conn.Close();
DAP.Dispose();
Comm.Dispose();
}
}
catch (Exception Ex)
{
Console.WriteLine("ExecuteWithParameter hatasý:(" + strSqlCommand + ")" + Ex.Message);
OlayGunluk.Olay("ExecuteWithParameter hatasý:(" + strSqlCommand + ")" + Ex.Message);
HT.Add("Error", Ex.Message);
}
return(HT);
}
public static Hashtable UpdateData(string TableName, string Where, Hashtable ColumnsValues)
{
Hashtable rHt = new Hashtable();
string strCommand = "";
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
string ColVal = "";
int i = 0;
foreach (string var in ColumnsValues.Keys as ICollection)
{
i++;
ColVal += var + " = @" + var;
DbParameter Parameter = Settings.Factory.CreateParameter();
Parameter.ParameterName = "@" + var;
Parameter.Value = ColumnsValues[var];
Comm.Parameters.Add(Parameter);
if (i != ColumnsValues.Keys.Count)
{
ColVal += ",";
}
}
Comm.CommandText = "UPDATE " + TableName + " SET " + ColVal + " " + Where;
strCommand = Comm.CommandText;
Comm.ExecuteNonQuery();
Conn.Close();
Comm.Dispose();
return(rHt);
}
}
catch (Exception Ex)
{
Console.WriteLine("UpdateData hatasý:(" + strCommand + ")" + Ex.Message);
OlayGunluk.Olay("UpdateData hatasý:(" + strCommand + ")" + Ex.Message);
rHt.Add("Error", Ex.Message);
return(rHt);
}
}
public static Hashtable SimpleQuery(string TableName, string Where, string OrderBy,
string Columns)
{
Hashtable HT = new Hashtable();
string strCommand = "";
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
strCommand = "SELECT " + Columns + " FROM " + TableName + " " + Where + " " + OrderBy;
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbDataAdapter DAP = Settings.Factory.CreateDataAdapter();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
Comm.CommandText = strCommand;
DAP.SelectCommand = Comm;
DAP.Fill(DS);
DAP.Fill(DT);
HT.Add("DataSet", DS);
HT.Add("DataTable", DT);
HT.Add("DataCount", DS.Tables[0].Rows.Count);
Conn.Close();
DAP.Dispose();
Comm.Dispose();
}
}
catch (Exception Ex)
{
Console.WriteLine("SimpleQuery hata:(" + strCommand + ")" + Ex.Message);
OlayGunluk.Olay("SimpleQuery hata:(" + strCommand + ")" + Ex.Message);
HT.Add("Error", Ex.Message);
}
return(HT);
}
public static Hashtable InsertCheckedUpdate(string TableName, Hashtable ColumnsValues, string Where, string IdentityColumn)
{
Hashtable rHt = new Hashtable();
string strCommand = "";
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DbCommand Comm = Settings.Factory.CreateCommand();
string strIdentity = "";
Comm.CommandText = "SELECT " + IdentityColumn + " From " + TableName + " Where " + Where;
Comm.Connection = Conn;
DbDataReader DR = Comm.ExecuteReader();
if (DR.Read())
{
strIdentity = DR[IdentityColumn].ToString();
}
DR.Dispose();
Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
string Values = "";
string Columns = "";
int i = 0;
foreach (string var in ColumnsValues.Keys as ICollection)
{
i++;
Columns += "[" + var + "]";
Values += "@" + var;
if (i != ColumnsValues.Keys.Count)
{
Columns += ",";
Values += ",";
}
DbParameter Parameter = Settings.Factory.CreateParameter();
Parameter.ParameterName = "@" + var;
Parameter.Value = ColumnsValues[var];
Comm.Parameters.Add(Parameter);
}
if (strIdentity == "")
{
Comm.CommandText = "INSERT INTO " + TableName + "(" + Columns + ") VALUES(" + Values + ")";
}
else
{
strCommand = "";
string[] sColumns = Columns.Split(',');
string[] sValues = Values.Split(',');
for (i = 0; i < sColumns.Length; i++)
{
strCommand += sColumns[i] + "=" + sValues[i];
strCommand += i != sColumns.Length - 1 ? "," : "";
}
Comm.CommandText = "UPDATE " + TableName + " SET " + strCommand + " WHERE " + Where;
}
Comm.ExecuteNonQuery();
if (strIdentity == "")
{
DbCommand IComm = Settings.Factory.CreateCommand();
IComm.CommandText = "SELECT @@IDENTITY";
IComm.Connection = Conn;
strIdentity = IComm.ExecuteScalar().ToString();
rHt.Add("BeforeAdded", false);
IComm.Dispose();
}
Conn.Close();
Comm.Dispose();
if (strIdentity != "")
{
rHt.Add("BeforeAdded", true);
}
rHt.Add("Identity", Convert.ToInt32(strIdentity));
Conn.Close();
Comm.Dispose();
return(rHt);
}
}
catch (Exception Ex)
{
Console.WriteLine("InsertCheckedUpdate hatasý:(" + strCommand + ")" + Ex.Message);
OlayGunluk.Olay("InsertCheckedUpdate hatasý:(" + strCommand + ")" + Ex.Message);
rHt.Add("Error", Ex.Message);
return(rHt);
}
}
public static Hashtable InsertData(string TableName, Hashtable ColumnsValues)
{
Hashtable rHt = new Hashtable();
string strCommand = "";
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
string Values = "";
string Columns = "";
int i = 0;
foreach (string var in ColumnsValues.Keys as ICollection)
{
i++;
Columns += var;
Values += "@" + var;
if (i != ColumnsValues.Keys.Count)
{
Columns += ",";
Values += ",";
}
DbParameter Parameter = Settings.Factory.CreateParameter();
Parameter.ParameterName = "@" + var;
Parameter.Value = ColumnsValues[var];
Comm.Parameters.Add(Parameter);
}
strCommand = "INSERT INTO " + TableName + "(" + Columns + ") VALUES(" + Values + ")";
Comm.CommandText = strCommand;
Comm.ExecuteNonQuery();
DbCommand IComm = Settings.Factory.CreateCommand(); IComm.CommandText = "SELECT @@IDENTITY";
IComm.Connection = Conn;
try
{
int intIdentity = Convert.ToInt32(IComm.ExecuteScalar());
rHt.Add("Identity", intIdentity);
}
catch
{
Console.WriteLine("InsertData hatasý:" + strCommand);
OlayGunluk.Olay("InsertData hatasý:" + strCommand);
}
Conn.Close();
Comm.Dispose();
IComm.Dispose();
return(rHt);
}
}
catch (Exception Ex)
{
Console.WriteLine("InsertData hatasý:(" + strCommand + ")" + Ex.Message);
OlayGunluk.Olay("InsertData hatasý:(" + strCommand + ")" + Ex.Message);
rHt.Add("Error", Ex.Message);
return(rHt);
}
}
public static bool SendTicketInfToLCD(int _elTermID, string _ticketNumber)
{
byte[] outStream = null;
TcpClient tcpClient = null;
NetworkStream serverStream = null;
int port = 506;
try
{
List <LCDClient> sendingTicketInfToClients = FindRelatedClients(_elTermID);
foreach (LCDClient item in sendingTicketInfToClients)
{
//portu bulalım
//string dataSource = QPU_SerialPort.Properties.Settings.Default.DataSource;
//string dbName = QPU_SerialPort.Properties.Settings.Default.DbName;
//string dbUserName = QPU_SerialPort.Properties.Settings.Default.DBUserName;
//string dbPass = QPU_SerialPort.Properties.Settings.Default.DBPass;
//string queryString = "select distinct port from TERMINALLER T "
// + " JOIN ANATABLO_YON Y ON T.TID = Y.TID "
// + " where ELTID = " + _elTermID.ToString();
//string conStr = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", dataSource, dbName, dbUserName, dbPass);
//using (SqlConnection connection = new SqlConnection(conStr))
//{
// SqlCommand command = new SqlCommand(queryString, connection);
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// try
// {
// if (reader.HasRows)
// {
// while (reader.Read())
// {
tcpClient = new TcpClient();
if (tcpClient.Connected)
{
tcpClient.Close();
}
port = Convert.ToInt32(item.Port); // reader["port"]);
tcpClient.Connect(item.IPAdres, port); //506***);
serverStream = tcpClient.GetStream();
string CommandDatas = string.Format(
"{0}#{1}#{2}#{3}", "001", _elTermID, item.VezneNoYonOku[_elTermID], _ticketNumber);
outStream = Encoding.ASCII.GetBytes(CommandDatas);
serverStream.Write(outStream, 0, outStream.Length);
if (serverStream != null)
{
serverStream.Flush();
}
if (tcpClient.Connected)
{
tcpClient.Close();
}
// }
// }
// }
// catch (Exception ex)
// {
// port = 506;
// tcpClient.Connect(item.IPAdres, port); //506***);
// serverStream = tcpClient.GetStream();
// string CommandDatas = string.Format(
// "{0}#{1}#{2}#{3}", "001", _elTermID, item.VezneNoYonOku[_elTermID], _ticketNumber);
// outStream = Encoding.ASCII.GetBytes(CommandDatas);
// serverStream.Write(outStream, 0, outStream.Length);
// if (serverStream != null)
// serverStream.Flush();
// if (tcpClient.Connected)
// tcpClient.Close();
// }
//}
//ESKİ HALİ
//tcpClient.Connect(item.IPAdres, port); //506***);
//serverStream = tcpClient.GetStream();
//string CommandDatas = string.Format(
// "{0}#{1}#{2}#{3}", "001", _elTermID, item.VezneNoYonOku[_elTermID], _ticketNumber);
//outStream = Encoding.ASCII.GetBytes(CommandDatas);
//serverStream.Write(outStream, 0, outStream.Length);
//if (serverStream != null)
// serverStream.Flush();
//if (tcpClient.Connected)
// tcpClient.Close();
}
return(true);
}
catch (Exception ex)
{
OlayGunluk.Olay("QLUClientCommunication SendTicketInfToLCD:" + ex.Message);
return(false);
}
finally
{
if (serverStream != null)
{
serverStream.Flush();
}
if (tcpClient != null && tcpClient.Connected)
{
tcpClient.Close();
}
}
}