I am working on a help-desk ticketing system. I'm having a problem saving the value of the ticketID as a session variable because it's an identity column in the Helpdesk table.
Im saving tickets to the helpdesk table fine but i need to save the value of the ticketID because the File table references the Helpdesk table's TickeID as a one to many relationship,
so that one ticket can have many files attached to them. I am trying to use the "Upload and Download Files with SQL Server in ASP.NET" from CodeProject as an example except it doesn't
show me how to save the session variable.
My code for the Utilities/FIleUtilites.cs is as follows
public class FileUtilities
{
private static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["status"].ConnectionString;
}
private static void OpenConnection(SqlConnection connection)
{
connection.ConnectionString = GetConnectionString();
connection.Open();
}
public static DataTable GetFileList(int TicketID)
{
var fileList = new DataTable();
using (var connection = new SqlConnection())
{
OpenConnection(connection);
var cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandTimeout = 0;
cmd.CommandText = "SELECT FileID, TicketID, ContentType, Size, Data FROM Files WHERE TicketID=@TicketID";
cmd.CommandType = CommandType.Text;
var adapter = new SqlDataAdapter();
cmd.Parameters.Add("@TicketID", TicketID);
adapter.SelectCommand = cmd;
adapter.Fill(fileList);
connection.Close();
}
return fileList;
}
public static void SaveFile(string name, int ticketID, string contentType,
int size, byte[] data)
{
using (var connection = new SqlConnection())
{
OpenConnection(connection);
var cmd = new SqlCommand { Connection = connection, CommandTimeout = 0 };
const string commandText = "INSERT INTO Files(FileID, TicketID, ContentType, Size, Data, Name) " +
"VALUES( @TicketID, @ContentType, @Size, @Data, @Name)";
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@TicketID", SqlDbType.Int);
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Size", SqlDbType.Int);
cmd.Parameters.Add("@Data", SqlDbType.VarBinary);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd.Parameters["@TicketID"].Value = ticketID;
cmd.Parameters["@ContentType"].Value = contentType;
cmd.Parameters["@size"].Value = size;
cmd.Parameters["@Data"].Value = data;
cmd.Parameters["@Name"].Value = name;
cmd.ExecuteNonQuery();
connection.Close();
}
}
public static DataTable GetAFile(int FileID, int TicketID)
{
var file = new DataTable();
using (var connection = new SqlConnection())
{
connection.Open();
var cmd = new SqlCommand
{
Connection = connection,
CommandTimeout = 0,
CommandText = "SELECT Files.FileID, Files.TicketID, Files.ContentType, Files.Size, Files.Data, Files.Name, HelpDesk.TicketID FROM Files " +
"INNER JOIN HelpDesk"+
"ON Files.TicketID= HelpDesk.TicketID"+
" WHERE HelpDesk.TicketID =@TicketID and Files.FileID=@FileID",
CommandType = CommandType.Text
};
var adapter = new SqlDataAdapter();
cmd.Parameters.Add("@TicketID", SqlDbType.Int);
cmd.Parameters.Add("@FileID", SqlDbType.Int);
cmd.Parameters["@TicketID"].Value = TicketID;
cmd.Parameters["@FileID"].Value =FileID;
adapter.SelectCommand = cmd;
adapter.Fill(file);
connection.Close();
}
return file;
}
}
}
now i need to save a ticket first so that i can reference the TicketID as a session variable so that i can refference that session variable in my SaveFile method from the fileUtilities class.
the code for the BtnSave_Click Event for the NewTicket.aspx.cs is as follows
protected void btnSave_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
btnUpload.Visible = true;
lblUpload.Visible = true;
FileUpload1.Visible = true;
try
{
int FromUser = ddlFromUser.SelectedIndex + 1;
int AssignedTo = ddlAssignTo.SelectedIndex + 1;
int Category = ddlCategory.SelectedIndex + 1;
string subject = txtSubject.Text;
string description = txtBody.Text;
string DateAdded = DateTime.Now.ToString(@"yyyy-MM-dd hh:mm tt");
string dt = txtDatePicker.Text;
DateTime dtDueDate = Convert.ToDateTime(dt);
string DueDate = dtDueDate.ToString(@"yyyy-MM-dd");
string priority = rblPriority.SelectedItem.Text;
SqlConnection conn;
SqlCommand comm;
var constring = ConfigurationManager.ConnectionStrings["status"].ConnectionString;
conn = new SqlConnection(constring);
string query =
"INSERT INTO HelpDesk(EmployeeIDFrom, EmployeeIDAssignedTo, CategoryID, Subject, Description, DateAdded, DueDate, Status, Priority)" +
" VALUES('" + FromUser + "','" + AssignedTo + "','" + Category + "','" + subject + "','" +
description + "','" + DateAdded + "','" + DueDate + "','" + "Open" + "','" + priority + "')"
;
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = "Select @@Identity";
var TicketID = cmd.ExecuteScalar();
Session["NewTicketID"] = TicketID;
conn.Close();
sendMail();
}
catch (Exception ex)
{
dbErrorMessage.Text = ex.Message;
}
}
}
Now I just need to know how to save the session variable so that i can refference it in my btnUpload_Click event
which goes like this:
protected void btnUpload_Click(object sender, EventArgs e)
{
HttpFileCollection files = Request.Files;
foreach (string fileTagName in files)
{
HttpPostedFile file = Request.Files[fileTagName];
if (file.ContentLength > 0)
{
int size = file.ContentLength;
string name = file.FileName;
int position = name.LastIndexOf("\\");
name = name.Substring(position + 1);
string contentType = file.ContentType;
byte[] fileData = new byte[size];
int ticketID = int.Parse()
file.InputStream.Read(fileData, 0, size);
FileUtilities.SaveFile(name, ticketID, contentType, size, fileData );
}
}
}
Pleasen help me fix this horrible head ache