explorest · August 29, 2015 14:13
diff --git a/SqliteDemoClass b/SqliteDemoClass


 /*

 Dated: 10 January 2015


 Reference dlls: System, System.Data, Mono.Data.Sqlite
 Resources: Platform specific sqlite3 binary executable should be there in the application directory


 Introduction: The following code uses the sqlite provider to create a sqlite db file , 
 connect to the db file, execute commands to write some data, closes the connection, 
 re-opens the connection, reads data and then finally again closes everything. 



 */


 using System;
 using System.IO;
 using System.Data;
 using Mono.Data.Sqlite;



 
 	public class SqliteDemo
 	{
 		 
 		public static void Main()
 		{


 			latest ();


 		}




 		public static void latest()
 		{


 			// CREATE DB FILE

 			string db = "latest.db"; 
 			SqliteConnection.CreateFile (db); // ...db created in the current working directory

 			/*  

 			   For other folders (like the home folder) we can set up the db variable as ...

 				var documents = Environment.GetFolderPath (
 					Environment.SpecialFolder.Personal);
 				string db = Path.Combine (documents, "mydb.db3");

 			*/




 			// CONNECTION

 			string connectionString = string.Format("Data Source={0}, version=3", db) ;

 			IDbConnection connection = new SqliteConnection(connectionString);


 			// NON-QUERY COMMAND STRINGS  (TO BE EXECUTED SUBSEQUENTLY   )

 			string[] commandStringsArray = new[] {

 					"CREATE TABLE People (PersonID INTEGER NOT NULL, FirstName ntext, LastName ntext)",
 			"INSERT INTO People (PersonID, FirstName, LastName) VALUES (1, 'Michael', 'Jackson')",
 			"INSERT INTO People (PersonID, FirstName, LastName) VALUES (2, 'Mark', 'Shuttleworth')",
 			"INSERT INTO People (PersonID, FirstName, LastName) VALUES (3, 'Steve', 'Jobs')",
 				};






 			// EXECUTING NON-QUERY COMMANDS


 			connection.Open ();


 			//get hold of a command object over the opened connection
 					
 			using (IDbCommand command = connection.CreateCommand()) 
 				// a single command object is now available to be assigned different command strings for execution

 			{

 						

 						foreach (string commandString in commandStringsArray) 
 						{
 											
 									command.CommandText = commandString;
 									command.CommandType = CommandType.Text; 



 									command.ExecuteNonQuery ();
 								



 						}


 			}

 				


 			connection.Close ();






 			// READING FROM THE DB




 			connection.Open ();



 			//get hold of a default command object over the opened connection

 			using (IDbCommand command = connection.CreateCommand ()) 
 			{

 				command.CommandText = "SELECT * FROM People";
 				command.CommandType = CommandType.Text;

 				using (IDataReader reader = command.ExecuteReader ()) 
 				{
 										 

 					/* 


 						NOTE: 

 						Before starting to pull in  any data from the result set  into our app, we must always call the Read() 
 						method of the DataReader to make the reader point at the very first record in the result set; 
 						("point" in the sense that the second call to Read() will make the DataReader point to the second record , 
 						the third call to Read() will make the DataReader point to the third record , so on and so forth!) 


 						Calling Read() will also return a bool value indicating whether or not the DataReader is now pointing to 
 						an existing record (true value means that the record that the DataReader is now pointing to 
 						actually exists and can be consumed after this Read() call; a false return value means the DataReader is now pointing to
 						a non-existent record.)

 						Once the Read() call returns true (and  the DataReader pointing to an existing record), we can 
 						now call different methods of the DataReader to pull in the data (like the fields in the current record) 
 						and check its different properties to gain 	insight into the current state of affairs.


 					*/ 
 				 

 					while (reader.Read ()) // reads in one whole  record at a time  
 					{ 
 						Console.WriteLine ();

 						//iterate through fields of the current record the reader is pointing to 
 						for (int i = 0; i < reader.FieldCount; ++i) 
 						{

 							Console.Write("Colname:{0}, Value: {1}    ", reader.GetName(i), reader [i]);


 						}

 					}

 				 


 				}
 				connection.Close ();


 			}


 		}





 	}


	/*

	Dated: 10 January 2015


	Reference dlls: System, System.Data, Mono.Data.Sqlite
	Resources: Platform specific sqlite3 binary executable should be there in the application directory


	Introduction: The following code uses the sqlite provider to create a sqlite db file ,
	connect to the db file, execute commands to write some data, closes the connection,
	re-opens the connection, reads data and then finally again closes everything.



	*/


	using System;
	using System.IO;
	using System.Data;
	using Mono.Data.Sqlite;




	public class SqliteDemo
	{

	public static void Main()
	{


	latest ();


	}




	public static void latest()
	{


	// CREATE DB FILE

	string db = "latest.db";
	SqliteConnection.CreateFile (db); // ...db created in the current working directory

	/*

	For other folders (like the home folder) we can set up the db variable as ...

	var documents = Environment.GetFolderPath (
	Environment.SpecialFolder.Personal);
	string db = Path.Combine (documents, "mydb.db3");

	*/




	// CONNECTION

	string connectionString = string.Format("Data Source={0}, version=3", db) ;

	IDbConnection connection = new SqliteConnection(connectionString);


	// NON-QUERY COMMAND STRINGS (TO BE EXECUTED SUBSEQUENTLY )

	string[] commandStringsArray = new[] {

	"CREATE TABLE People (PersonID INTEGER NOT NULL, FirstName ntext, LastName ntext)",
	"INSERT INTO People (PersonID, FirstName, LastName) VALUES (1, 'Michael', 'Jackson')",
	"INSERT INTO People (PersonID, FirstName, LastName) VALUES (2, 'Mark', 'Shuttleworth')",
	"INSERT INTO People (PersonID, FirstName, LastName) VALUES (3, 'Steve', 'Jobs')",
	};






	// EXECUTING NON-QUERY COMMANDS


	connection.Open ();


	//get hold of a command object over the opened connection

	using (IDbCommand command = connection.CreateCommand())
	// a single command object is now available to be assigned different command strings for execution

	{



	foreach (string commandString in commandStringsArray)
	{

	command.CommandText = commandString;
	command.CommandType = CommandType.Text;



	command.ExecuteNonQuery ();




	}


	}




	connection.Close ();






	// READING FROM THE DB




	connection.Open ();



	//get hold of a default command object over the opened connection

	using (IDbCommand command = connection.CreateCommand ())
	{

	command.CommandText = "SELECT * FROM People";
	command.CommandType = CommandType.Text;

	using (IDataReader reader = command.ExecuteReader ())
	{


	/*


	NOTE:

	Before starting to pull in any data from the result set into our app, we must always call the Read()
	method of the DataReader to make the reader point at the very first record in the result set;
	("point" in the sense that the second call to Read() will make the DataReader point to the second record ,
	the third call to Read() will make the DataReader point to the third record , so on and so forth!)


	Calling Read() will also return a bool value indicating whether or not the DataReader is now pointing to
	an existing record (true value means that the record that the DataReader is now pointing to
	actually exists and can be consumed after this Read() call; a false return value means the DataReader is now pointing to
	a non-existent record.)

	Once the Read() call returns true (and the DataReader pointing to an existing record), we can
	now call different methods of the DataReader to pull in the data (like the fields in the current record)
	and check its different properties to gain insight into the current state of affairs.


	*/


	while (reader.Read ()) // reads in one whole record at a time
	{
	Console.WriteLine ();

	//iterate through fields of the current record the reader is pointing to
	for (int i = 0; i < reader.FieldCount; ++i)
	{

	Console.Write("Colname:{0}, Value: {1} ", reader.GetName(i), reader [i]);


	}

	}




	}
	connection.Close ();


	}


	}





	}