public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter = null)
{
SqlCommand myCommand = new SqlCommand();
DataTable dataTable = new DataTable();
dataTable = null;
DataSet ds = new DataSet();
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _query;
if (sqlParameter != null)
{
myCommand.Parameters.AddRange(sqlParameter);
}
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
}
catch (SqlException e)
{
WebAPIUtils.WriteLog("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return(null);
}
finally
{
//CloseConnection();
}
return(dataTable);
}
/// <method>
/// Update Query
/// </method>
public int executeUpdateQuery(String _query, List <KeyValuePair <string, object> > paramList = null)
{
SqlParameter[] sqlParameter = getParameter(paramList);
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _query;
if (sqlParameter != null)
{
myCommand.Parameters.AddRange(sqlParameter);
}
myAdapter.UpdateCommand = myCommand;
return(myCommand.ExecuteNonQuery());
}
catch (SqlException e)
{
WebAPIUtils.WriteLog("Error - Connection.executeUpdateQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return(0);
}
finally
{
//CloseConnection();
}
}
/// <method>
/// Execute stored proceduce to select data
/// </method>
public bool executeStoredProceduce(string procName, List <KeyValuePair <string, object> > paramList = null)
{
SqlParameter[] sqlParameter = getParameter(paramList);
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand myCommand = new SqlCommand(procName, conn))
{
myCommand.CommandType = CommandType.StoredProcedure;
conn.Open();
if (sqlParameter != null && sqlParameter.Any())
{
myCommand.Parameters.AddRange(sqlParameter);
}
bool result = myCommand.ExecuteNonQuery() > 0;
conn.Close();
conn.Dispose();
return(result);
}
}
}
catch (SqlException e)
{
WebAPIUtils.WriteLog("Error - Connection.executeStoredProceduce \nException: " + e.StackTrace.ToString());
}
return(false);
}
public CrawlDataService()
{
InitializeComponent();
try
{
listFields = ConfigurationManager.AppSettings["listFields"].Replace(",MSGXML", "").Replace("MSGXML,", "").Replace(",SOTK", "").Replace("SOTK,", "");
rowAmount = int.Parse(ConfigurationManager.AppSettings["rowAmount"]);
baseAddress = ConfigurationManager.AppSettings["baseAPIAddress"];
procName = ConfigurationManager.AppSettings["procSelectOneRowName"];
timeBetweenRuns = double.Parse(ConfigurationManager.AppSettings["timeBetweenRuns"]);
apiFunctionPath = ConfigurationManager.AppSettings["apiFunctionPath"];
limit = int.Parse(ConfigurationManager.AppSettings["limit_MSGXML"]);
apiUtils.InitWebClient(listFields, baseAddress, procName, timeBetweenRuns);
}
catch (Exception ex) { WebAPIUtils.WriteLog(ex); }
}
/// <method>
/// Execute stored proceduce to select data
/// </method>
public DataTable executeStoredProceduceSelectData(string procName, List <KeyValuePair <string, object> > paramList = null)
{
SqlParameter[] sqlParameter = getParameter(paramList);
DataTable dataTable = new DataTable();
dataTable = null;
DataSet ds = new DataSet();
try
{
using (SqlConnection conn = new SqlConnection(connStr)) {
using (SqlCommand myCommand = new SqlCommand(procName, conn))
{
myCommand.CommandType = CommandType.StoredProcedure;
conn.Open();
if (sqlParameter != null && sqlParameter.Any())
{
myCommand.Parameters.AddRange(sqlParameter);
}
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
conn.Close();
conn.Dispose();
}
}
}
catch (SqlException e)
{
WebAPIUtils.WriteLog("Error - Connection.executeStoredProceduceSelectData - ProcName: " + procName + " \nException: " + e.StackTrace.ToString());
return(null);
}
return(dataTable);
}
private void PostDataToAPI()
{
try
{
PostData dataPost = new PostData();
dataPost.Status = "overover";
DataTable dataToPost = cpn.GetAllDataNewWithStatusZero(rowAmount, listFields);
//DataTable copyDataTable;
//copyDataTable = dataToPost.Copy();
//try
//{
// cpn.UpdateSotokhai(copyDataTable);
//}catch(Exception ex) { }
if (dataToPost != null && dataToPost.Rows.Count > 0)
{
DataTable copyDataTable;
copyDataTable = dataToPost.Copy();
//JObject json = JObject.Parse(apiUtils.GetJson(dataToPost));
dataPost.ShipmentNo = apiUtils.GetJsonFromDataTable(copyDataTable, limit);
HttpWebResponse response = (HttpWebResponse)apiUtils.CallToPostWebAPI(dataPost, apiFunctionPath);
if (response.StatusCode == HttpStatusCode.OK)
{
// Get the request stream.
Stream dataStream = response.GetResponseStream();
// Open the stream using a StreamReader for easy access.
StreamReader reader = new StreamReader(response.GetResponseStream());
// Read the content.
string responseFromServer = reader.ReadToEnd();
// Display the content.
//Console.WriteLine(responseFromServer);
string res = string.Empty;
// ... Read the string.
//Task<string> result = content.ReadAsStringAsync();
//res = result.Result;
JObject json = JObject.Parse(responseFromServer);
if (json["code"].ToString() == "success")
{
WebAPIUtils.WriteLog("\n******Post data success: " + DateTime.Now);
StringBuilder ids = new StringBuilder();
StringBuilder shipmentIdList = new StringBuilder();
int totalRow = copyDataTable.Rows.Count;
for (int i = 0; i < totalRow; i++)
{
if (ids.Length == 0)
{
ids.Append(copyDataTable.Rows[i]["Id"]);
shipmentIdList.Append(copyDataTable.Rows[i]["ShipmentID"]);
}
else
{
ids.Append(",");
ids.Append(copyDataTable.Rows[i]["Id"]);
shipmentIdList.Append(",");
shipmentIdList.Append(copyDataTable.Rows[i]["ShipmentID"]);
}
}
int updateData = cpn.UpdateDataAfterSuccess(ids.ToString());
if (updateData > 0)
{
WebAPIUtils.WriteLog("\n ******Success update " + totalRow + "(" + updateData + ") rows to database. List id = " + ids);
}
else
{
WebAPIUtils.WriteLog("\n ******Fail update " + totalRow + " rows to database list id = " + ids);
}
}
else
{
WebAPIUtils.WriteLog("\n ******Post data success but get error: " + json.Values("message").ToString());
}
// Clean up the streams.
reader.Close();
dataStream.Close();
response.Close();
}
else
{
WebAPIUtils.WriteLog("\n******Post data fail : " + "Error Code");
//response.StatusCode + " : Message - " + response.ReasonPhrase);
}
}
}
catch (Exception ex)
{
WebAPIUtils.WriteLog(ex);
}
}