Aeries PIC Table – Saving Student Pictures with C#

If you are a school district using the Aeries PIC table for saving student pictures, you may have the need to extract pictures for use in other applications.

As districts switch over to use the “new” PIC table, this will become especially important if your district has a site that may update it’s pictures frequently or has a large turnover of students and new pictures are added throughout the year.
 

Overview

When the PIC table first came out I created a simple .net application that allowed for viewing and exporting the picture using a GUI interface. That application is still available, here. But if you want to create an application for your self, here is a simple way to pull the data.

This is going to be very high-level and is just some sample code… I’m making the assumption that you already know how to code… If you don’t and need help with this, please feel free to contact me. I have various tools already built that can help school districts with these types of things, as well as many other processes for Aeries.

SQL Query

The first thing you will need to do is create a query, I have a very simple query that I use to get the “most recent” pictures for students from the PIC table.

SELECT
	PIC.ID, PIC.RB, PIC.XT 
FROM 
	PIC INNER JOIN 
		(SELECT PIC.ID, MAX(PIC.YR) AS YR FROM PIC WHERE PIC.DEL = 0 GROUP BY PIC.ID) AS PIC1 
			ON PIC.ID = PIC1.ID
WHERE 
	PIC.DEL = 0
	AND PIC.YR = PIC1.YR

Using this query you can get a list of the students, pictures and the file extension for the pictures. You could link in other sets of data if you need to do something special with the file names, but for this example, I’m simply going to use the Student ID as the file name… so everything we need is in the PIC table.

C# Code

Once you have the query, the rest of the code is relatively simple. It’s just some basic code for loading the data from SQL Server and then saving the data to the files.

string sql = "... SQL QUERY ...";
string strConn = "... CONNECTION STRING ...";
string savePath = "C:\\Pictures";

using (SqlConnection con = new SqlConnection(strConn)) {
	con.Open();
	
	using (SqlCommand cmd = new SqlCommand(sql, con)) {
		using (SqlDataReader reader = cmd.ExecuteReader()) {
			DataTable data = new DataTable();
			data.Load(reader);
			if (data != null && data.Rows != null && data.Rows.Count > 0) {
			
				// create the save directory if it doesn't exist.
				if (!Directory.Exists(savePath)) {
					Directory.CreateDirectory(savePath);
				}
				
				// loop and save all of the files
				foreach (DataRow r in data.Rows) {
				
					// create the new picture path, ID.XT (e.g., 9940001.jpg)
					string file = Path.Combine(savePath, r["ID"].ToString() + "." + r["XT"].ToString());
					
					// if the file already exists, delete it
					if (File.Exists(file)) {
						File.Delete(file);
					}
					
					// save the new file
					using (FileStream fs = new FileStream(file, FileMode.OpenOrCreate)) {
						using (BinaryWriter bWrite = new BinaryWriter(fs)) {
							bWrite.Write(r.Field<byte[]>("RB"));
							bWrite.Close();
						}
						fs.Close();
					}
				}
			}
		}
	}

	con.Close();
}

That’s pretty much it. That code can be modified to do a lot of custom things, depending on what your needs may be.

The files are saved as the “PIC.ID”.”PIC.XT” which is the “Student ID”.”File Extension”. You can include other data in your query and code to change the file names or paths into a lot of other things, basically categorizing the data however you need it.

Once the data is loaded to a DataTable, simply looping through the records and using a FileStream and BinaryWriter to write the files is used. It’s simple, fast and reasonably reliable… You would probably want to add some error handling in case of data issues, etc. But for the most basic processes, this should work.