示例#1
0
        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);
        }
示例#2
0
        /// <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();
            }
        }
示例#3
0
        /// <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); }
 }
示例#5
0
        /// <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);
            }
        }