protected int UpdateCitation(PubIdTitle pc) { //string connectionStr = ConfigurationManager.AppSettings.Get("ConnectionString"); string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection conn = new SqlConnection(connectionStr); string sqlStatement; int citation = Helper.ReadCitation(pc.articleTitle); if (citation == -1) { return(-1); } DateTime today = DateTime.Now; sqlStatement = "update publication_processing" + " set citation = @citation" + ", citation_date = @citation_date" + " where publication_id = @publication_id"; SqlCommand command = new SqlCommand(sqlStatement, conn); SqlParameter publication_idParameter = new SqlParameter(); publication_idParameter.ParameterName = "@publication_id"; publication_idParameter.SqlDbType = SqlDbType.Int; publication_idParameter.Value = pc.publcationId; command.Parameters.Add(publication_idParameter); SqlParameter citationParameter = new SqlParameter(); citationParameter.ParameterName = "@citation"; citationParameter.SqlDbType = SqlDbType.Int; citationParameter.Value = citation; command.Parameters.Add(citationParameter); SqlParameter citation_dateParameter = new SqlParameter(); citation_dateParameter.ParameterName = "@citation_date"; citation_dateParameter.SqlDbType = SqlDbType.DateTime; citation_dateParameter.Value = today; command.Parameters.Add(citation_dateParameter); conn.Open(); command.ExecuteNonQuery(); conn.Close(); return(citation); }
protected void btnCitation_Click(object sender, EventArgs e) { ErrorMessage.Text = ""; //string connectionStr = ConfigurationManager.AppSettings.Get("ConnectionString"); string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection conn = new SqlConnection(connectionStr); string sqlStatement; string startDate = ""; string endDate = ""; if (txtStartDate.Text != "") { startDate = txtStartDate.Text; } else { ErrorMessage.Text = "Please give start date."; return; } if (txtEndDate.Text != "") { endDate = txtEndDate.Text; } else { ErrorMessage.Text = "Please give end date."; return; } string clientIdStr = ""; if (ddlMember.SelectedIndex != 0 && ddlMember.SelectedIndex != -1) { clientIdStr = ddlMember.SelectedValue.ToString(); } else { ErrorMessage.Text = "Please select member."; return; } sqlStatement = "select pd.publication_id," + " p.article_title" + " from publication_processing pd" + " inner join publication p" + " on pd.publication_id = p.publication_id" + " inner join publication_author pa" + " on pd.publication_id = pa.publication_id" + " inner join author a" + " on pa.author_id = a.author_id" + " and a.client_id = " + clientIdStr + " where ((pd.publication_date >= '" + startDate + "' and pd.publication_date <= '" + endDate + "')) order by p.article_title"; /* * sqlStatement = * "select pd.publication_id," + * " p.article_title" + * " from publication_processing pd" + * " inner join publication p" + * " on pd.publication_id = p.publication_id" + * " inner join publication_author pa" + * " on pd.publication_id = pa.publication_id" + * " inner join author a" + * " on pa.author_id = a.author_id" + * " and a.client_id = " + * clientIdStr + * " where pd.publication_date >= '" + * startDate + * "' and pd.publication_date <= '" + * endDate + * "'"; */ SqlCommand myCommand = new SqlCommand(sqlStatement, conn); conn.Open(); SqlDataReader myReader; myReader = myCommand.ExecuteReader(); List <PubIdTitle> idTitleList = new List <PubIdTitle>(); try { while (myReader.Read()) { PubIdTitle pc = new PubIdTitle(); pc.publcationId = Convert.ToInt32(myReader["publication_id"]); pc.articleTitle = myReader["article_title"].ToString(); idTitleList.Add(pc); } } finally { myReader.Close(); conn.Close(); } int citationGot = 0; foreach (PubIdTitle pc in idTitleList) { int citation = UpdateCitation(pc); if (citation != -1) { citationGot++; } } int totalPub = idTitleList.Count; //string msg = "Among " + totalPub.ToString() + ", successfully got " + citationGot + " citation counts."; //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + msg + "');", true); HttpCookie _dateCookies = new HttpCookie("dates"); _dateCookies["startDate"] = txtStartDate.Text; _dateCookies["endDate"] = txtEndDate.Text; _dateCookies.Expires = DateTime.Now.AddDays(5); Response.Cookies.Add(_dateCookies); GetCitationStat(); }