protected void DelectPubs(List <int> pubIdList) { string pubIdListStr = string.Join(",", pubIdList.Select(n => n.ToString()).ToArray()); string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionStr); string sqlStatement; foreach (int pubId in pubIdList) { ProcessPub.SaveRejectOnPubId(pubId); } sqlStatement = "delete from publication_author where publication_id in (" + pubIdListStr + ")" + "; delete from publication_processing where publication_id in (" + pubIdListStr + ")" + "; delete from publication_program where publication_id in (" + pubIdListStr + ")" + "; delete from publication_programmatic where publication_id in (" + pubIdListStr + ")" + "; delete from publication_pubtype where publication_id in (" + pubIdListStr + ")" + "; delete from publication_resource where publication_id in (" + pubIdListStr + ")" + "; delete from publication where publication_id in (" + pubIdListStr + ")" + "; delete from author where author_id not in (select author_id from publication_author)"; SqlCommand command = new SqlCommand(sqlStatement, myConnection); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); FillPublicationGrid(); int cnt = pubIdList.Count; ErrorMessage.Text = cnt.ToString() + " publications are deleted."; }
protected void gvAuthor_RowDeleting(object sender, GridViewDeleteEventArgs e) { string idStr; Label lblIdTemp = null; lblIdTemp = (Label)gvAuthor.Rows[e.RowIndex].FindControl("lblId"); if (lblIdTemp != null) { idStr = lblIdTemp.Text; } else { return; } List <int> pubIdList = GetPubIdListByAuthorId(idStr).ToList(); //string connectionStr = ConfigurationManager.AppSettings.Get("ConnectionString"); string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionStr); string sqlStatement; foreach (int pubId in pubIdList) { ProcessPub.SaveRejectOnPubId(pubId); sqlStatement = "delete from publication_author where publication_id = " + pubId + "; delete from publication_processing where publication_id = " + pubId + "; delete from publication_program where publication_id = " + pubId + "; delete from publication_programmatic where publication_id = " + pubId + "; delete from publication_pubtype where publication_id = " + pubId + "; delete from publication_resource where publication_id = " + pubId + "; delete from publication where publication_id = " + pubId; SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection); myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } sqlStatement = "delete from author where author_id = " + idStr; SqlCommand command = new SqlCommand(sqlStatement, myConnection); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); FillAuthorGrid(txtLastName.Text, txtFirstName.Text); }
protected void gvPublication_RowDeleting(object sender, GridViewDeleteEventArgs e) { string idStr; Label lblIdTemp = null; lblIdTemp = (Label)gvPublication.Rows[e.RowIndex].FindControl("lblId"); if (lblIdTemp != null) { idStr = lblIdTemp.Text; } else { return; } string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionStr); string sqlStatement; ProcessPub.SaveRejectOnPubId(Convert.ToInt32(idStr)); sqlStatement = "delete from publication_author where publication_id = " + idStr + "; delete from publication_processing where publication_id = " + idStr + "; delete from publication_program where publication_id = " + idStr + "; delete from publication_programmatic where publication_id = " + idStr + "; delete from publication_pubtype where publication_id = " + idStr + "; delete from publication_resource where publication_id = " + idStr + "; delete from publication where publication_id = " + idStr + "; delete from author where author_id not in (select author_id from publication_author)"; SqlCommand command = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_program_idParameter = new SqlParameter(); publication_program_idParameter.ParameterName = "@publication_program_id"; publication_program_idParameter.SqlDbType = SqlDbType.Int; publication_program_idParameter.Value = Convert.ToInt32(idStr); command.Parameters.Add(publication_program_idParameter); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); gvPublication.EditIndex = -1; FillPublicationGrid(); }
protected void SaveYesNoSelection(pubid_ours mp) { int publicationId = mp.pubId; int yesNoId = mp.yesNoId; string sqlStatement; if (yesNoId == 2) { string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionStr); ProcessPub.SaveRejectOnPubId(publicationId); sqlStatement = "delete from publication_processing where publication_id = @publication_id" + "; delete from publication_author where publication_id = @publication_id" + "; delete from publication_program where publication_id = @publication_id" + "; delete from publication_programmatic where publication_id = @publication_id" + "; delete from publication_pubtype where publication_id = @publication_id" + "; delete from publication_resource where publication_id = @publication_id" + "; delete from publication where publication_id = @publication_id" + "; delete from author where author_id not in (select author_id from publication_author)"; SqlCommand command = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_idParameter = new SqlParameter(); publication_idParameter.ParameterName = "@publication_id"; publication_idParameter.SqlDbType = SqlDbType.Int; publication_idParameter.Value = publicationId; command.Parameters.Add(publication_idParameter); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); } else { sqlStatement = "Update publication_processing" + " SET final_confirm_id=@final_confirm_id" + " WHERE (publication_id = @publication_id)"; string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionStr); SqlCommand command = new SqlCommand(sqlStatement, myConnection); SqlParameter final_confirm_idParameter = new SqlParameter(); final_confirm_idParameter.ParameterName = "@final_confirm_id"; final_confirm_idParameter.SqlDbType = SqlDbType.Int; final_confirm_idParameter.Value = yesNoId; command.Parameters.Add(final_confirm_idParameter); SqlParameter publication_idParameter = new SqlParameter(); publication_idParameter.ParameterName = "@publication_id"; publication_idParameter.SqlDbType = SqlDbType.Int; publication_idParameter.Value = publicationId; command.Parameters.Add(publication_idParameter); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); } gvPublication.EditIndex = -1; FillPublicationGrid(); }