public List <CIssue> getIssueslist()
{
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SPIssueDetails", myConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IDIssue", SqlDbType.Int, 4));
cmd.Parameters["@IDIssue"].Value = 0;
myConnection.Open();
try
{
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
List <CIssue> results = new List <CIssue>();
while (reader.Read())
{
CIssue objCIssue = new CIssue();
// Issuetbl.IDIssue, Issuetbl.FIPSCounty, County.County, County.State, FileType.FileType,
// IssueTypetbl.IssueType, ProcessingTypetbl.ProcessingType, Issuetbl.Edition, Issuetbl.Version,
// Issuetbl.Title, Issuetbl.IssueDetails, Issuetbl.Resolution, Issuetbl.Submitter, Issuetbl.Relatedlink,
//Issuetbl.ICP,Issuetbl.IssueCreatedDate
objCIssue.IDIssue = Convert.ToInt32(reader["IDIssue"]);
objCIssue.FIPSCounty = reader["FIPSCounty"].ToString();
objCIssue.County = reader["County"].ToString();
objCIssue.State = reader["State"].ToString();
objCIssue.FileType = reader["FileType"].ToString();
objCIssue.IssueType = reader["IssueType"].ToString();
objCIssue.ProcessingType = reader["ProcessingType"].ToString();
objCIssue.Edition = Convert.ToInt32(reader["Edition"]);
objCIssue.Version = Convert.ToInt32(reader["Version"]);
objCIssue.Title = reader["Title"].ToString();
objCIssue.IssueDetails = reader["IssueDetails"].ToString();
objCIssue.Resolution = reader["Resolution"].ToString();
objCIssue.Submitter = reader["Submitter"].ToString();
objCIssue.Relatedlink = reader["Relatedlink"].ToString();
objCIssue.ICP = Convert.ToInt32(reader["ICP"]);
objCIssue.IssueCreatedDate = Convert.ToDateTime(reader["IssueCreatedDate"].ToString());
//if (reader["objCIssue"].Equals(DBNull.Value))
// objCIssue.UnitPrice = 0;
results.Add(objCIssue);
}
reader.Close();
myConnection.Close();
return(results);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// Function to retrieve issue record with issue ID
/// </summary>
/// <param name="idissue">ID Issue</param>
/// <returns></returns>
public CIssue getIssuerecord(int idissue)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SPIssueDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IDIssue", SqlDbType.Int, 4));
cmd.Parameters["@IDIssue"].Value = idissue;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// Check if the query returned a record.
if (!reader.HasRows)
{
return(null);
}
// Get the first row.
reader.Read();
CIssue objissue = new CIssue();
objissue.IDIssue = (int)reader["IDIssue"];
objissue.FIPSCounty = (string)reader["FIPSCounty"];
objissue.County = (string)reader["County"];
objissue.State = (string)reader["State"];
objissue.FileType = (string)reader["FileType"];
objissue.IssueType = (string)reader["IssueType"];
objissue.ProcessingType = (string)reader["ProcessingType"];
objissue.Edition = (int)reader["Edition"];
objissue.Version = (int)reader["Version"];
objissue.Title = (string)reader["Title"];
objissue.IssueDetails = (string)reader["IssueDetails"];
objissue.Resolution = (string)reader["Resolution"];
objissue.Submitter = (string)reader["Submitter"];
objissue.Relatedlink = (string)reader["Relatedlink"];
objissue.ICP = (int)reader["ICP"];
objissue.IssueCreatedDate = (DateTime)reader["IssueCreatedDate"];
reader.Close();
return(objissue);
}
catch (SqlException err)
{
return(null);
}
finally
{
con.Close();
}
}
/// <summary>
/// Insertissue Function inserts a issue record into the Issue's table
/// </summary>
/// <param name="objissue">object type issue</param>
/// <returns></returns>
public int InsertIssue(CIssue objissue)
{
int retval = 0;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SPInsertIssuetbl", con);
cmd.CommandType = CommandType.StoredProcedure;
//command parameters for the Stored Procedure SPInsertIssuetbl
cmd.Parameters.Add(new SqlParameter("@FIPSCounty", SqlDbType.VarChar, 5));
cmd.Parameters["@FIPSCounty"].Value = objissue.FIPSCounty;
cmd.Parameters.Add(new SqlParameter("@IDProcessingType", SqlDbType.Int, 4));
cmd.Parameters["@IDProcessingType"].Value = objissue.IDProcessingType;
cmd.Parameters.Add(new SqlParameter("@IDFileType", SqlDbType.Int, 4));
cmd.Parameters["@IDFileType "].Value = objissue.IDFileType;
cmd.Parameters.Add(new SqlParameter("@IDIssueType", SqlDbType.Int, 4));
cmd.Parameters["@IDIssueType"].Value = objissue.IDIssueType;
cmd.Parameters.Add(new SqlParameter("@Edition", SqlDbType.Int, 4));
cmd.Parameters["@Edition"].Value = objissue.Edition;
cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Int, 4));
cmd.Parameters["@Version"].Value = objissue.Version;
cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 100));
cmd.Parameters["@Title"].Value = objissue.Title;
cmd.Parameters.Add(new SqlParameter("@IssueDetails", SqlDbType.VarChar, -1));
cmd.Parameters["@IssueDetails"].Value = objissue.IssueDetails;
cmd.Parameters.Add(new SqlParameter("@Resolution", SqlDbType.VarChar, -1));
cmd.Parameters["@Resolution"].Value = objissue.Resolution;
cmd.Parameters.Add(new SqlParameter("@Submitter", SqlDbType.VarChar, 50));
cmd.Parameters["@Submitter"].Value = objissue.Submitter;
cmd.Parameters.Add(new SqlParameter("@Relatedlink", SqlDbType.VarChar, -1));
cmd.Parameters["@Relatedlink"].Value = objissue.Relatedlink;
cmd.Parameters.Add(new SqlParameter("@ICP", SqlDbType.Int, 4));
cmd.Parameters["@ICP"].Value = objissue.ICP;
cmd.Parameters.Add(new SqlParameter("@IssueCreatedDate", SqlDbType.DateTime));
cmd.Parameters["@IssueCreatedDate"].Value = objissue.IssueCreatedDate;
cmd.Parameters.Add(new SqlParameter("@IssueCreatedUser", SqlDbType.Int, 4));
cmd.Parameters["@IssueCreatedUser"].Value = objissue.IssueCreatedUser;
cmd.Parameters.Add(new SqlParameter("@IssueUpdatedDate", SqlDbType.DateTime));
cmd.Parameters["@IssueUpdatedDate"].Value = objissue.IssueUpdatedDate;
cmd.Parameters.Add(new SqlParameter("@IssueUpdatedUser ", SqlDbType.Int, 4));
cmd.Parameters["@IssueUpdatedUser"].Value = objissue.IssueUpdatedUser;
cmd.Parameters.Add(new SqlParameter("@Isuplodedfile", SqlDbType.Bit));
cmd.Parameters["@Isuplodedfile"].Value = objissue.Isuplodedfile;
cmd.Parameters.Add(new SqlParameter("@IDuploadedfile", SqlDbType.Int, 4));
cmd.Parameters["@IDuploadedfile"].Value = objissue.IDuploadedfile;
cmd.Parameters.Add(new SqlParameter("@IDIssue", SqlDbType.Int, 4));
cmd.Parameters["@IDIssue"].Direction = ParameterDirection.Output;
try
{
con.Open();
cmd.ExecuteNonQuery();
retval = (int)cmd.Parameters["@IDIssue"].Value;
}
catch (SqlException err)
{
retval = -1;
}
finally
{
con.Close();
}
return(retval);
}