//Here is the code for the demo project

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

//add using directives for the class DbConnection
using DataAccess;
//add using directives for the class ArrayList
using System.Collections;

namespace dbtestKod
{
	public partial class Form1 : Form
	{
		public Form1()
		{
				InitializeComponent();
		}

		private void btnGetRecord_Click(object sender, EventArgs e)
		{
			int pk = int.Parse(txtPK.Text);

			//@ in front of a string allows line breaks inside the string
			string cmd = 
				@"SELECT Name, Age
				FROM Student
				WHERE nr = '" + pk.ToString() + "'" ;		

			DbConnection conn = new DbConnection();
			ArrayList al = conn.cmdQuery(cmd);		//one item for every row (Record)

			object obj;
			obj = al[0];		//the first (and only) row

			object[] aRow=(object[])obj;			//create an array
			txtName.Text = aRow[0].ToString();
			txtAge.Text = aRow[1].ToString();
		}

		private void btnSave_Click(object sender, EventArgs e)
		{
			int pk = int.Parse(txtPK.Text);

			string cmd =
				" UPDATE Student" +
				" SET " +
				" Name = '" + txtName.Text + "'," +
				" Age = '" + txtAge.Text + "'" +
				" WHERE nr = '" + pk.ToString() + "'";

			DbConnection conn = new DbConnection();
			int result = conn.CmdNonQuery(cmd);
			if (result == 1)
				MessageBox.Show("OK");
			else
				MessageBox.Show("Fel");
		}

		
		private void btnNmbrOfRecords_Click(object sender,EventArgs e) 
		{
			string cmd = "SELECT COUNT (*) FROM Student";

			DbConnection conn = new DbConnection();
			int nmbr = conn.cmdScalar(cmd);

			MessageBox.Show("Number of records: " + nmbr.ToString());

		}

		private void btnNewRecord_Click(object sender,EventArgs e) 
		{
			try
			{
				string name = txtName.Text;
				string age = txtAge.Text;

				DbConnection conn = new DbConnection();

				//Note the ' characters
				string cmd =
					@"INSERT INTO aTableWithIdentity (Name, Age)
					VALUES ( '" + name + "', '" + age + "' )";

				int result = conn.CmdNonQuery(cmd);

				if(result!=1)	//there should be one record inserted
				{
					MessageBox.Show("Insertion failed");
				}
				else
				{
					//please look into the method CmdNonQuery to see how 
					//the IDENTITY is fetched before the connection is closed.
					//the connection should be open shortest possible time.
					int pk = conn.Identity;
					MessageBox.Show("The new PK is: " + pk.ToString());
				}

			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}
	}
}