public int PostFile(FileModel fileModel)
{
int result = 0;
using (SqlConnection conn = GetConnection())
{
using (SqlTransaction trn = conn.BeginTransaction())
{
SqlCommand cmdInsert = new SqlCommand(
@"INSERT INTO "+TableName+@" (
Name,
Path,
ContentType)
values (
@name,
@path,
@contentType);Select Scope_Identity();", conn, trn);
cmdInsert.Parameters.Add("@name", SqlDbType.VarChar, 256);
cmdInsert.Parameters["@name"].Value = fileModel.Name;
cmdInsert.Parameters.Add("@path", SqlDbType.VarChar, 256);
cmdInsert.Parameters["@path"].Value = fileModel.Path;
cmdInsert.Parameters.Add("@contentType", SqlDbType.VarChar, 256);
cmdInsert.Parameters["@contentType"].Value = fileModel.ContentType;
result = Convert.ToInt32(cmdInsert.ExecuteScalar());
SqlCommand cmdFirstData = new SqlCommand(
@"UPDATE "+TableName+@"
SET content = @data
WHERE id = @id", conn, trn);
cmdFirstData.Parameters.Add("@data", SqlDbType.VarBinary, -1);
cmdFirstData.Parameters.Add("@id", SqlDbType.Int);
cmdFirstData.Parameters["@id"].Value = result;
SqlCommand cmdUpdate = new SqlCommand(
@"UPDATE "+TableName+@"
SET content.write (@data, NULL, NULL)
WHERE id = @id", conn, trn);
cmdUpdate.Parameters.Add("@data", SqlDbType.VarBinary, -1);
cmdUpdate.Parameters.Add("@id", SqlDbType.Int);
cmdUpdate.Parameters["@id"].Value = result;
using (System.IO.Stream uploadStream = new BufferedStream(
new SqlStreamUpload
{
InsertCommand = cmdFirstData,
InsertDataParam = cmdFirstData.Parameters["@data"],
InsertIdParam = cmdFirstData.Parameters["@id"],
UpdateCommand = cmdUpdate,
UpdateDataParam = cmdUpdate.Parameters["@data"],
UpdateIdParam = cmdUpdate.Parameters["@id"]
}, 8040))
{
fileModel.InputStream.CopyTo(uploadStream);
}
trn.Commit();
}
}
return result;
}
public FileModel GetFile(int id)
{
SqlConnection conn = GetConnection();
FileModel result = null;
try
{
SqlCommand cmd = new SqlCommand(
@"SELECT name,
path,
contentType,
content
FROM "+TableName+@"
WHERE id = @id;", conn);
//content_coding, DATALENGTH(content) as content_length,
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters["@id"].Value = id;
SqlDataReader reader = cmd.ExecuteReader(
CommandBehavior.SequentialAccess |
CommandBehavior.SingleResult |
CommandBehavior.SingleRow |
CommandBehavior.CloseConnection);
if (false == reader.Read())
{
reader.Dispose();
conn = null;
return null;
}
string name = reader["name"].ToString();
string path = reader["path"].ToString();
string contentType = reader["ContentType"].ToString();
System.IO.Stream contentStream = new SqlReaderStream(reader, 3);
result = new FileModel
{
Name = name,
Path = path,
ContentType = contentType,
OutputStream = contentStream,
};
conn = null; // ownership transfered to the reader/stream
return result;
}
finally
{
if (null != conn)
{
conn.Dispose();
}
}
}
