sexta-feira, 17 de abril de 2015

Saving pictures to an Access database

What is the best method for storing images in a server? That's a discussion that we will never see the end.

In my opinion, has in everything in life, it depends. But in this post we will not contribute to that dilemma. The objective of this post is to show how to store the pictures in an Access database.

We start by creating a new project in VS 2013.

Next, we create a class that will manage the database.

In this class we will need a reference to a DLL that is responsible for creating the database file.


In the Reference Manager with search in the COM section.

 
 Back in the class with add a namespace: using ADOX;

Now let's add three properties to the class: the path to the database file, a connection string and an object to make the connection with the database.

class BaseDados
    {
        string caminhoBD;
        string strLigacao;
        OleDbConnection ligacaoBD;
    }

 The connection object needs another namespace: using System.Data.OleDb;

In the constructor of the class will be defined the connection string, the path to the database file and three function to create the database, open the database and create the table.

         //construtor
        public BaseDados()
        {
            caminhoBD = Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData) + @"\BD_Access";
            if (Directory.Exists(caminhoBD) == false)
                Directory.CreateDirectory(caminhoBD);

            string nomeBD = @"\myAccessFile.accdb";
            caminhoBD += nomeBD;
            Console.WriteLine(caminhoBD);
            strLigacao = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + caminhoBD + ";";
            strLigacao += "Jet OLEDB:Database Password='12345';";  
            createDB();
            openDB();
            createTables();
        }

 In the connection string is defined a password for added security, although this password isn't secure.

In the next step we will define the three functions:


        private void createDB()
        {
            if(File.Exists(caminhoBD)==false)
            {
                Catalog cat = new Catalog();
                cat.Create(strLigacao);
            }
        }

        private void openDB()
        {
            try
            {
                ligacaoBD = new OleDbConnection(strLigacao);
                ligacaoBD.Open();
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
            }
        }

        private void createTables()
        {
            string strSQL = "CREATE TABLE Images(";
            strSQL += "_id COUNTER,";
            strSQL += "_name VARCHAR(200),";
            strSQL += "_image OLEOBJECT,";
            strSQL += "PRIMARY KEY(_id));";
            OleDbCommand comando = new OleDbCommand(strSQL, ligacaoBD);
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
            }
            comando.Dispose();
            comando = null;
        }

There should be some caution with the name of the fields because Access Engine won't allow some reserved words.

To close the connection with the database we will create a destructor.

//destrutor
        ~BaseDados()
        {
            closeDB();
        }

        private void closeDB()
        {
            try
            {
                ligacaoBD.Close();
                ligacaoBD.Dispose();
                ligacaoBD = null;
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
            }
        }

 And now we need two functions. One that will read the image file and returns a vector with the content, that vector will be inserted in the OLEOBJECT field. The other function will get the vector and create a file with it.
This functions will be static and created in a helper class.

class Helper
    {
        static public byte[] ImagemParaVetor(string imagem)
        {
            FileStream fs = new FileStream(imagem, FileMode.Open, FileAccess.Read);
            byte[] dados = new byte[fs.Length];
            fs.Read(dados, 0, (int)fs.Length);
            fs.Close();
            fs.Dispose();
            return dados;
        }

        static public void VetorParaImagem(byte[] imagem, string nome)
        {
            FileStream fs = new FileStream(nome, FileMode.Create, FileAccess.Write);
            fs.Write(imagem, 0, imagem.GetUpperBound(0));
            fs.Close();
            fs.Dispose();
        }
    }

To insert a image in the database will create a form like this.

The first button let's the user choose the picture.

         private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ficheiro = new OpenFileDialog();

            ficheiro.Filter = "Imagens|*.JPG;*.PNG";
            if (ficheiro.ShowDialog() == DialogResult.Cancel) return;
            string nome = ficheiro.FileName;

            pictureBox1.Image = Image.FromFile(nome);
            lbFile.Text = nome;
        }

 The second one will call the function in the helper class that returns the vector and call a new function in the class database that executes the SQL statement to create a new record in the table.
         private void button2_Click(object sender, EventArgs e)
        {
            byte[] image = Helper.ImagemParaVetor(lbFile.Text);
            bd.insertImage(textBox1.Text, image);
        }

The function in the class.
 public bool insertImage(string name, byte[] foto)
        {
            string strSQL = "INSERT INTO Images (_name,_image) VALUES (?,?);";
            OleDbCommand comando = null;
            try
            {
                comando = new OleDbCommand(strSQL, ligacaoBD);
                ////////////////////////////////fill the parameters
                comando.Parameters.AddWithValue("?", name);
                comando.Parameters.AddWithValue("?", foto);
                ////////////////////////////////execute
                comando.ExecuteNonQuery();
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
                comando.Dispose();
                comando = null;
                return false;
            }
            comando.Dispose();
            comando = null;
            return true;
        }

To be able to view the inserted pictures we will create a new form, that shows all the pictures with a scroll.

 All the controls are added dynamically. There is a panel that holds all the pictures boxes with the images.

 public partial class Form2 : Form
    {
        Form1 f = Application.OpenForms[0] as Form1;
        BaseDados bd;
        Panel panel1 = new Panel();

        public Form2()
        {

            InitializeComponent();
            bd = f.bd;
            panel1.Dock = DockStyle.Fill;
            panel1.AutoScroll = true;
            this.Controls.Add(panel1);
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            DataTable dados = bd.listImages();
            int count=0;
            foreach(DataRow row in dados.Rows)
            {
                PictureBox pb = new PictureBox();
                pb.Name = row[0].ToString();
                pb.Parent = this.panel1;
                pb.Size = new Size(320, 240);
                pb.SizeMode = PictureBoxSizeMode.StretchImage;
                pb.Location = new Point(0, count * 240);

                //criar um ficheiro com a imagem
                byte[] image=(byte[])row[2];
                Helper.VetorParaImagem(image, "temp"+count+".jpg");
                pb.Image = Image.FromFile("temp"+count+".jpg");

                count++;
            }
        }

        private void Form2_FormClosing(object sender, FormClosingEventArgs e)
        {
            foreach(Control ctrl in this.Controls)
            {
                ctrl.Dispose();
            }
            GC.Collect();
        }
    }

To access the database we use the object that is created in the main form, for that we use a reference to the form and to the object.

We need one last function that returns a data table with all the records in the database.

        public DataTable listImages()
        {
            DataTable dados = new DataTable();
            string strSQL = "SELECT * FROM Images;";
            OleDbDataReader registos;
            OleDbCommand comando = new OleDbCommand(strSQL, ligacaoBD);

            registos = comando.ExecuteReader();

            dados.Load(registos);

            return dados;
        }

 When the form is closed the garbage collector is called and all the controls in the form are disposed.

The final result.

The project.

sexta-feira, 27 de março de 2015

C# Web Service

After the last post on PHP web service now we develop a web service with Visual Studio 2013 in C#.

Let's start by creating a ASP.NET project.




In the next screen wee choose an Empty application.

Now in we add a new item to our project.


And next choose Web Service


By now the Visual Studio have created the basic structure of our web service with one method called HelloWorld as an example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace WebApplication2
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    // [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
    }
}

In this post were going to create a new method call listUsers that will receive one parameter indicating the type of output to be generated, XML or JSON.

        [WebMethod]
        public string listUsers(string op)
        {
            if(op=="json"||op=="JSON")
                return devolveRegistosJSON();

            return devolveRegistosXML();
        }

As is easy to see in the code there are two function that get called, one return a JSON string and the other a XML string. Let's look at that code.

Starting with de JSON.

        public string devolveRegistosJSON()
        {
            var jsonSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            string registos = "";
            string strligacao = "Database=database;Server=server;UID=user;PWD=password;";
            MySqlConnection ligacao = new MySqlConnection(strligacao);
            ligacao.Open();
            string strSQL = "SELECT * FROM test_users;";
            MySqlCommand comando = new MySqlCommand(strSQL, ligacao);
            MySqlDataReader dados = comando.ExecuteReader();
            List<Registo> lDados = new List<Registo>();
            while (dados.Read())
            {
                lDados.Add(new Registo(int.Parse(dados[0].ToString()), dados[1].ToString(), dados[2].ToString()));
            }
            registos = jsonSerializer.Serialize(lDados);
            lDados.Clear();
            dados.Dispose();
            comando.Dispose();
            ligacao.Close();
            ligacao.Dispose();
            return registos;
        }

This functions uses a serializer to generate the JSON string from a list of objects with the data.
Each object is an instance of the class Registo.

        [Serializable]
        public class Registo
        {
            public int id { get; set; }
            public string name { get; set; }
            public string country { get; set; }
            public Registo(int id, string name, string country)
            {
                this.id = id;
                this.name = name;
                this.country = country;
            }
        }

Next the XML version.

public string devolveRegistosXML()
        {
            string registos = "";
            string strligacao = "Database=database;Server=server;UID=user;PWD=password;";
            MySqlConnection ligacao = new MySqlConnection(strligacao);
            ligacao.Open();
            string strSQL="SELECT * FROM test_users;";
            MySqlCommand comando = new MySqlCommand(strSQL,ligacao);
            MySqlDataReader dados = comando.ExecuteReader();
            while (dados.Read())
            {
                string registo = "";
                registo=adicionarTag("id", dados[0].ToString());
                registo += adicionarTag("name", dados[1].ToString());
                registo += adicionarTag("country", dados[2].ToString());
                registos += adicionarTag("registo", registo);
            }
            registos = adicionarTag("registos", registos);
            dados.Dispose();
            comando.Dispose();
            ligacao.Close();
            ligacao.Dispose();
            return registos;
        }

This functions makes use of a helper function to generate the xml tags.

        public string adicionarTag(string tag, string texto)
        {
            string temp = "<" + tag + ">" + texto + "</" + tag + ">";
            return temp;
        }

When testing the service this is the page presented.


If we choose the new method the browser shows us a form with a field for the parameter of the service.

By choosing XML we get.
If the choice is JSON.
Get the project here.

PHP Web Service

In this post we will be developing a web service with PHP. The data is in a MySQL database and the output format is optional between XML and JSON.

In order to collect the data we will be using MySQLi.

The service won't be requiring any credentials and there won't be any limits to the number of requests although in some cases it would be necessary to consider that possiblities.

First we must define the values needed to connect with the MySQL server.

<?php
$server="server";
$user="userid";
$password="userpassword";
$database="database";
?>

Next is the code that implements de service per say.

<?php

require "config.php";
//connect to the database server
$ligacao=new mysqli($server,$user,$password,$database);
//check connection
if($ligacao->connect_error)
die("Error connecting: ".$ligacao->connect_error);

//options
if(isset($_GET['format']))
$output=$_GET['format'];
else
$output="xml"; //by default
if($output!='JSON'&&$output!='json'&&$output!='xml'&&$output!='XML')
$output="xml";

//data
$sql="SELECT * FROM test_users";
$resultado=$ligacao->query($sql);
if($resultado->num_rows>0){
while($registo=$resultado->fetch_assoc())
$registos[]=array("record"=>$registo);

header("Cache-Control: no-cache, must-revalidate");
if($output=="xml"||$output=="XML"){
$conteudo="Content-type: text/xml; charset=utf-8";
header($conteudo);
$linhas="";
$linha="";
foreach($registos as $index => $registo)
{
if(is_array($registo)){
foreach($registo as $campo => $valor){
if(is_array($valor)){
foreach($valor as $tag => $val)
$linha.=adicionarTag($tag,htmlentities($val));
}
$linha=adicionarTag("record",$linha);
}
}
$linhas = $linhas.$linha;
$linha="";
}
echo adicionarTag("records",$linhas);
}else{
$conteudo="Content-type: application/json; charset=utf-8";
header($conteudo);
echo json_encode(array('records'=>$registos));
}
}
function adicionarTag($tag,$texto){
$temp="<".$tag.">".$texto."</".$tag.">";
return $temp;
}
?>

The project can be downloaded here.

quinta-feira, 26 de março de 2015

Consuming a web service

In this post we will be talking about on how to consume a web service with a desktop application developed with C#.

A web service is an API that is available through the internet and it can be used within the rules established. Some services are free and don't require identification others have to be payed or have some limitations on the number of calls or data produced.

To demonstrate this concept we will be using a free service that the European Central Bank maintains in the following url http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml.
This service returns the Euro currency rate over other countries currencies. This information is encapsulated in XML.

Since we are making a desktop app we have to keep in mind the responsiveness of the user interface (GUI). So the request of the data must not occur in the main thread of the application which could block the GUI during the wait that is associated with exchange of information through the internet.

The main code is this:

async void downloadXML()
        {
            string url = "http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml";
            WebRequest pedido = WebRequest.Create(url);
            WebResponse resposta = await pedido.GetResponseAsync();
            XDocument documento = XDocument.Load(resposta.GetResponseStream());
            textBox1.Text = documento.ToString();

            //preencher a listview
            XmlDocument xml = new XmlDocument();
            xml.LoadXml(documento.ToString());
            XmlNodeList lista = xml.GetElementsByTagName("Cube");
            foreach (XmlNode no in lista)
            {
                if (no.Attributes.Count > 1)
                {
                    ListViewItem novo = new ListViewItem(no.Attributes[0].InnerXml);
                    novo.SubItems.Add(no.Attributes[1].InnerXml);
                    listView1.Items.Add(novo);
                }
            }
        }

This function is asynchronous. The execution starts normally and goes until the line where the reserved word AWAIT define a process that can promote a delay so the control is return to the line that call this asynchronous function. When this request terminates the function concludes the processing and updates the UI with the data returned (learn more).

The code that lays ahead is simply to manipulate the XML recived.

The project