public int InsertImageItem(ImageItem item)
{
using (var con = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand("InsertImageItem", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FileName", SqlDbType.NVarChar));
cmd.Parameters["@FileName"].Value = item.FileName;
cmd.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar));
cmd.Parameters["@Url"].Value = item.Url;
cmd.Parameters.Add(new SqlParameter("@ImageData", SqlDbType.Image));
cmd.Parameters["@ImageData"].Value = item.ImageData;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
try
{
con.Open();
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["@ID"].Value;
}
catch (SqlException)
{
throw new ApplicationException("Data access error");
}
}
}
}
public void DeleteImageItem(ImageItem item)
{
using (var con = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand("DeleteImageItem", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
cmd.Parameters["@ID"].Value = item.ID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException)
{
throw new ApplicationException("Data access error");
}
}
}
}
public ImageItem GetImageItem(int id)
{
using (var con = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand("GetImageItem", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4));
cmd.Parameters["@ID"].Value = id;
try
{
con.Open();
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
reader.Read();
ImageItem image = new ImageItem
{
ID = (int)reader["ID"],
Url = (string)reader["Url"],
FileName = (string)reader["FileName"],
ImageData = (byte[])reader["ImageData"]
};
return image;
}
}
catch (SqlException)
{
throw new ApplicationException("Data access error");
}
}
}
}
public List<ImageItem> GetAllImageItems()
{
using (var con = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand("GetAllImageItems", con))
{
cmd.CommandType = CommandType.StoredProcedure;
List<ImageItem> items = new List<ImageItem>();
try
{
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ImageItem taskItem = new ImageItem
{
ID = (int)reader["ID"],
Url = (string)reader["Url"],
FileName = (string)reader["FileName"],
ImageData = (byte[])reader["ImageData"]
};
items.Add(taskItem);
}
return items;
}
}
catch (SqlException)
{
throw new ApplicationException("Data access error");
}
}
}
}