public string Base64Encode()
{
_selectBlobCmd.Parameters["@id"].Value = id;
using (SQLiteDataReader reader = _selectBlobCmd.Command.ExecuteReader(System.Data.CommandBehavior.KeyInfo))
{
if (reader.Read())
{
using (SQLiteBlob blob = reader.GetBlob(0, true))
{
int length = reader.GetInt32(1);
using (MemoryStream ms = new MemoryStream())
{
int position = 0;
byte[] buf = new byte[1024 * 1024]; // 1 MB buffer
while (position < length)
{
int bytesRead = Math.Min(buf.Length, length - position);
blob.Read(buf, bytesRead, position);
ms.Write(buf, 0, bytesRead);
position += bytesRead;
}
return(Convert.ToBase64String(ms.GetBuffer()));
}
}
}
}
return("");
}
public void ExportToFile(string filename)
{
_selectBlobCmd.Parameters["@id"].Value = id;
using (SQLiteDataReader reader = _selectBlobCmd.Command.ExecuteReader(System.Data.CommandBehavior.KeyInfo))
{
if (reader.Read())
{
using (SQLiteBlob blob = reader.GetBlob(0, true))
{
int length = reader.GetInt32(1);
using (FileStream fs = File.Open(filename, FileMode.Create))
{
int position = 0;
byte[] buf = new byte[1024 * 1024]; // 1 MB buffer
while (position < length)
{
int bytesRead = Math.Min(buf.Length, length - position);
blob.Read(buf, bytesRead, position);
fs.Write(buf, 0, bytesRead);
position += bytesRead;
}
}
}
}
}
}
public void LoadFile(string filename)
{
using (FileStream fs = File.Open(filename, FileMode.Open))
{
// Init blob.
_zeroBlobCmd.Parameters["@id"].Value = id;
_zeroBlobCmd.Parameters["@blobSize"].Value = fs.Length;
_zeroBlobCmd.Command.ExecuteNonQuery();
// Write file to blob.
_selectBlobCmd.Parameters["@id"].Value = id;
using (SQLiteDataReader reader = _selectBlobCmd.Command.ExecuteReader(System.Data.CommandBehavior.KeyInfo))
{
if (reader.Read())
{
using (SQLiteBlob blob = reader.GetBlob(0, false))
{
long length = fs.Length;
int position = 0;
byte[] buf = new byte[1024 * 1024]; // 1 MB buffer
while (position < length)
{
int bytesRead = (int)Math.Min(buf.Length, length - position);
bytesRead = fs.Read(buf, 0, bytesRead);
blob.Write(buf, bytesRead, position);
position += bytesRead;
}
}
}
}
}
}
public Dictionary <string, object> ObtenerDatosPrenda(int cod)
{
Dictionary <string, object> datos = new Dictionary <string, object>();
using (SQLiteConnection conexionSQL = new SQLiteConnection(cadenaConexion))
{
using (SQLiteCommand cmdConsulta = new SQLiteCommand("SELECT * FROM Prendas WHERE PrendaId=@Id", conexionSQL))
{
conexionSQL.Open();
cmdConsulta.Parameters.AddWithValue("@Id", cod);
using (SQLiteDataReader reader = cmdConsulta.ExecuteReader(System.Data.CommandBehavior.KeyInfo))
{
if (reader.Read())
{
datos.Add("Imagen", reader.GetBlob(1, true));
datos.Add("Nombre", reader.GetString(2));
datos.Add("Marca", reader.GetString(3));
datos.Add("Stock", reader.GetInt32(4));
datos.Add("Talla", reader.GetString(5));
datos.Add("Categoria", reader.GetInt32(6));
datos.Add("Proveedor", reader.GetInt32(7));
datos.Add("PrecioCompra", reader.GetDouble(8));
datos.Add("PrecioVenta", reader.GetDouble(9));
}
reader.Close();
}
}
}
return(datos);
}
/// <summary>
/// Получает один продукт из БД
/// </summary>
/// <param name="index">Номер продукта</param>
/// <param name="pType">Тип возвращаемого продукта</param>
/// <returns></returns>
public Product getProduct(int index)
{
SQLiteDataReader reader = execRead("SELECT * FROM ImageDB WHERE id = " + index);
SQLiteBlob blob = reader.GetBlob(3, false);
int blobSize = blob.GetCount();
Byte[] imageData = new Byte[blobSize];
blob.Read(imageData, blobSize, 0);
Bitmap image = ByteArrayToBitmap(imageData);
string name = reader.GetString(1);
string info = reader.GetString(2);
return(new Product(image, name, info));
}
/// <summary>
/// получает все продукты из БД
/// </summary>
/// <returns></returns>
public Product[] getAllImages()
{
List <Product> products = new List <Product>();
SQLiteDataReader reader = execRead("SELECT * FROM ImageDB");
while (reader.Read())
{
SQLiteBlob blob = reader.GetBlob(3, false);
int blobSize = blob.GetCount();
Byte[] imageData = new Byte[blobSize];
blob.Read(imageData, blobSize, 0);
Bitmap image = ByteArrayToBitmap(imageData);
string name = reader.GetString(1);
string info = reader.GetString(2);
products.Add(new Product(image, name, info));
}
reader.Close();
return(products.ToArray());
}
/// <summary>
/// Read in a binary blob; Per convention, must be formatted in "ID, FileName (Contain type) Content";
/// A complete copy of the binary data is saved in the memory
/// User responsible for disposing the memory stream.
/// </summary>
public static BinaryData SingleBlob(this SQLiteDataReader reader)
{
// If there is no row and then return null
if (!reader.HasRows)
{
reader.Close(); // Close reader
return(null);
}
// Type check
if (reader.FieldCount != 3 ||
reader.GetOrdinal("ID") == -1 ||
reader.GetOrdinal("FileName") == -1 ||
reader.GetOrdinal("Content") == -1)
{
throw new ArgumentException("Input data table must have ID, Name, Type and Content fields.");
}
BinaryData data = null;
while (reader.Read()) // Loop is necessary to close the reading even if we have only one row
{
SQLiteBlob blob = reader.GetBlob(reader.GetOrdinal("Content"), true);
if (blob.GetCount() != 0)
{
string filename = reader.GetString(reader.GetOrdinal("FileName"));
string type = Path.GetExtension(filename).Replace(".", "");
string name = Path.GetFileNameWithoutExtension(filename);
byte[] buffer = new byte[blob.GetCount()];
blob.Read(buffer, blob.GetCount(), 0);
data = new BinaryData(name, type, buffer);
}
blob.Close();
blob.Dispose();
}
reader.Close();
return(data);
}
public void ObtenerInventario(ref MetroGrid grid)
{
using (SQLiteConnection conexionSQL = new SQLiteConnection(cadenaConexion))
{
using (SQLiteCommand cmdConsulta = new SQLiteCommand("SELECT * FROM Vista_Productos", conexionSQL))
{
conexionSQL.Open();
using (SQLiteDataReader reader = cmdConsulta.ExecuteReader(System.Data.CommandBehavior.KeyInfo))
{
grid.Rows.Clear();
while (reader.Read())
{
int indice = grid.Rows.Add();
grid.Rows[indice].Cells["colCod"].Value = reader.GetInt32(0);
var blob = reader.GetBlob(1, true);
byte[] rawData = new byte[blob.GetCount()];
blob.Read(rawData, blob.GetCount(), 0);
MemoryStream memStream = new MemoryStream(rawData, 0, blob.GetCount(), true);
Image img = Image.FromStream(memStream);
blob.Dispose();
grid.Rows[indice].Cells["colImagen"].Value = img;
grid.Rows[indice].Cells["colNombre"].Value = reader.GetString(2);
grid.Rows[indice].Cells["colMarca"].Value = reader.GetString(3);
grid.Rows[indice].Cells["colTalla"].Value = reader.GetString(4);
grid.Rows[indice].Cells["colStock"].Value = reader.GetInt32(5);
grid.Rows[indice].Cells["colCategoria"].Value = reader.GetString(6);
grid.Rows[indice].Cells["colProveedor"].Value = reader.GetString(7);
grid.Rows[indice].Cells["colPrecioVenta"].Value = reader.GetDouble(8);
grid.Rows[indice].Cells["colPrecioCompra"].Value = reader.GetDouble(9);
}
reader.Close();
}
}
}
}
