Using C# and VistaDB on the Raspberry Pi
In this post, I thought I’d show you how to transfer your C# and SQL Server skills onto the Raspberry Pi.
The Raspberry Pi is a credit card sized single-board computer developed in the UK by the Raspberry Pi Foundation with the intention of stimulating the teaching of basic computer science in schools. It’s great fun, and your current skills are directly transferable – using the tools you know and love – here’s how.
First, let’s get a simple example going…
Open the VistaDB Data Builder and create this simple database and table to store a map of Twitter handle to full name:
Next, in Visual Studio, add some code to show an empty database, add some rows, prove you added them, then delete them and show that they’re gone:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VistaDB.Provider;
namespace VistaDBRaspberryPiTest
{
class Test
{
static void Main(string[] args)
{
ShowData();
AddData();
ShowData();
RemoveData();
ShowData();
}
private static void RemoveData()
{
Console.WriteLine("Removing test data...");
using (VistaDBConnection conn = GetConnection())
{
string commandText = "DELETE FROM POSTERS";
conn.Open();
using (VistaDBCommand cmd = new VistaDBCommand(commandText, conn))
{
cmd.ExecuteScalar();
}
}
}
private static void ShowData()
{
Console.WriteLine("Showing test data...");
using (VistaDBConnection conn = GetConnection())
{
string commandText = "SELECT * FROM POSTERS";
conn.Open();
using (VistaDBCommand cmd = new VistaDBCommand(commandText, conn))
{
VistaDBDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0} -> {1}", reader.GetString(0), reader.GetString(1));
}
}
else
{
Console.WriteLine("No data found!");
}
reader.Close();
}
}
}
private static void AddData()
{
Console.WriteLine("Adding test data...");
using (VistaDBConnection conn = GetConnection())
{
string commandText = "INSERT INTO [Posters](TwitterID, FullName) VALUES (@TwitterID, @FullName)";
conn.Open();
using( VistaDBCommand cmd = new VistaDBCommand(commandText, conn ))
{
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@TwitterID";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@FullName";
cmd.Parameters["@TwitterID"].Value = "@GaryShort";
cmd.Parameters["@FullName"].Value = "Gary Short";
cmd.ExecuteScalar();
}
conn.Close();
}
}
private static VistaDBConnection GetConnection()
{
return new VistaDBConnection("Data Source=Test.vdb4;");
}
}
}
Okay, compile and run that to show that it’s working on your machine:
Now that we know it works on our dev. machine let’s move it to the Raspberry Pi. First, SSH into your Pi and install the mono framework, by typing sudo apt-get install mono-devel. If you see something similar to the image below, you are good to to go.
Okay, now connect to your Pi with WinSCP or similar. Create a working folder and drag across the .cs file you just created plus your .vdb4 file and the VistaDB.4.dll file. Then compile the code with the gmcs compiler, remembering to add a reference to the VistaDB DLL…
Then run it with the mono interpreter…
And that’s it! You should see the exact same result, because it’s the exact same database and .cs file that you created on your dev machine, with your favourite tools, now running on the Raspberry Pi, how cool is that?!
That’s all for this time, until next time, happy coding!