Well just incorporate some checking logic so that the spurious file creation
can be prevented. Working fine now. App. is checking if the directory and
file pre-exists and if not, proceeds to create them. But I am having issues
inserting data into table. The exception I am getting is --> No connection
associated with this command. . First let me post the code block for
DBHandler.cs. Here is the code block.
using System;
using System.IO;
using System.Drawing;
using System.Data;
using Mono.Data.Tds;
using Mono.Data.Sqlite;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Linq;
using System.Linq;
using System.Linq.Expressions;
using MonoTouch.Foundation;
using MonoTouch.UIKit;
namespace ASTONAPP
{
public class DBHandler
{
SqliteConnection sconn;
SqliteCommand scmd;
string pathtowrite;
string filepath;
string dirName;
string dbFile;
string db;
string dbConn;
public DBHandler ()
{
}
public void SetDBConstants ()
{
pathtowrite = System.Environment.GetFolderPath
(System.Environment.SpecialFolder.Personal);
dirName = Path.Combine (pathtowrite, "ASTON
Repository");
filepath=Path.Combine (dirName, "BLL_User.xml");
dbFile = "ASTON_DB_V1.db3";
db = Path.Combine (dirName, dbFile);
dbConn = String.Format ("Data Source={0}", db);
sconn=new SqliteConnection();
scmd=new SqliteCommand();
}
public void CreateDBConnection ()
{
try
{
this.SetDBConstants ();
bool flag1=Directory.Exists (dirName);
bool flag2=File.Exists (db);
if(flag1)
{
if(flag2)
{
return;
}
else
{
SqliteConnection.CreateFile
(db);
}
}
else
{
Directory.CreateDirectory (dirName);
SqliteConnection.CreateFile (db);
}
sconn.Open ();
}
catch (Exception exp)
{
throw exp;
}
}
public void CloseDBConnection ()
{
sconn.Close ();
}
public void InitializeDatabase()
{
this.CreateDBConnection ();
}
public void InitializeClientData ()
{
try
{
this.CreateDBConnection();
XmlDocument xmldoc = new XmlDocument ();
xmldoc.Load (filepath);
XmlNamespaceManager nmsmgr = new
XmlNamespaceManager (xmldoc.NameTable);
nmsmgr.AddNamespace ("nmspc",
"http://tempuri.org/";);
XmlNode xmlnl1 = xmldoc.SelectSingleNode
("//nmspc:ClientUserID",
nmsmgr);
XmlNode xmlnl2 = xmldoc.SelectSingleNode
("//nmspc:UserName", nmsmgr);
XmlNode xmlnl3 = xmldoc.SelectSingleNode
("//nmspc:DiyCode", nmsmgr);
XmlNode xmlnl4 = xmldoc.SelectSingleNode
("//nmspc:DiyPassword",
nmsmgr);
XmlNode xmlnl5 = xmldoc.SelectSingleNode
("//nmspc:Address", nmsmgr);
XmlNode xmlnl6 = xmldoc.SelectSingleNode
("//nmspc:City", nmsmgr);
XmlNode xmlnl7 = xmldoc.SelectSingleNode
("//nmspc:State", nmsmgr);
XmlNode xmlnl8 = xmldoc.SelectSingleNode
("//nmspc:Country", nmsmgr);
XmlNode xmlnl9 = xmldoc.SelectSingleNode
("//nmspc:PostCode", nmsmgr);
XmlNode xmlnl10 = xmldoc.SelectSingleNode
("//nmspc:Email", nmsmgr);
XmlNode xmlnl11 = xmldoc.SelectSingleNode
("//nmspc:Phone1", nmsmgr);
XmlNode xmlnl12 = xmldoc.SelectSingleNode
("//nmspc:Phone2", nmsmgr);
XmlNode xmlnl13 = xmldoc.SelectSingleNode
("//nmspc:IsAssessor",
nmsmgr);
XmlNode xmlnl14 = xmldoc.SelectSingleNode
("//nmspc:ClientID", nmsmgr);
XmlNode xmlnl15 = xmldoc.SelectSingleNode
("//nmspc:ModifiedBy",
nmsmgr);
XmlNode xmlnl16 = xmldoc.SelectSingleNode
("//nmspc:CreatedBy", nmsmgr);
XmlNode xmlnl17 = xmldoc.SelectSingleNode
("//nmspc:IsDeleted", nmsmgr);
int ClientUserId = Convert.ToInt32
(xmlnl1.InnerText.ToString ());
string UserName = xmlnl2.InnerText.ToString ();
string DiyCode = xmlnl3.InnerText.ToString ();
string DiyPassword = xmlnl4.InnerText.ToString
();
string Address = xmlnl5.InnerText.ToString ();
string City = xmlnl6.InnerText.ToString ();
string State = xmlnl7.InnerText.ToString ();
string Country = xmlnl8.InnerText.ToString ();
string PostCode = xmlnl9.InnerText.ToString ();
string Email = xmlnl10.InnerText.ToString ();
string Phone1 = xmlnl11.InnerText.ToString ();
string Phone2 = xmlnl12.InnerText.ToString ();
string IsAssessor = xmlnl13.InnerText.ToString
();
int ClientID = Convert.ToInt32
(xmlnl14.InnerText.ToString ());
int ModifiedBy = Convert.ToInt32
(xmlnl15.InnerText.ToString ());
int CreatedBy = Convert.ToInt32
(xmlnl16.InnerText.ToString ());
string IsAdmin = xmlnl17.InnerText.ToString ();
string[] sql1 = new string[]{"CREATE TABLE IF
NOT EXISTS
ClientInfo('ClientOfflineID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
" +
"'ClientUserID' INTEGER, 'UserName'
TEXT, 'DiyCode' TEXT, 'DiyPassword'
TEXT, 'Address' TEXT, 'City' TEXT, 'State' TEXT, 'Country' TEXT, " +
"'PostCode' TEXT, 'Email' TEXT,
'Phone1' TEXT, 'Phone2' TEXT,
'IsAssessor' TEXT, 'ClientID' INTEGER, 'ModifiedBy' TEXT, 'CreatedBy' TEXT,
'IsAdmin' TEXT)",
String.Format ("insert into
ClientInfo(ClientUserID, UserName, DiyCode,
DiyPassword, Address, City, State, " +
"Country, PostCode, Email, Phone1,
Phone2, IsAssessor, ClientID,
ModifiedBy, CreatedBy, IsAdmin) " +
"values('{0}', '{1}', '{2}', '{3}',
'{4}', '{5}', '{6}', '{7}', '{8}',
'{9}', '{10}', '{11}', '{12}', '{13}', " +
"'{14}', '{15}', '{16}')",
ClientUserId, UserName, DiyCode,
DiyPassword, Address, City, State, Country,
PostCode, Email, Phone1, Phone2, IsAssessor, ClientID,
ModifiedBy, CreatedBy, IsAdmin)
} ;
foreach (string s in sql1)
{
using (var c=sconn.CreateCommand ())
{
scmd.CommandText = s;
scmd.CommandType =
CommandType.Text;
scmd.ExecuteNonQuery ();
this.CloseDBConnection ();
}
UIAlertView uv=new
UIAlertView("Notification", "Client Data Inserted
Successfully", null, "OK", null);
uv.Show ();
}
UIAlertView uav=new UIAlertView("Notification",
""+ClientUserId.ToString
()+", "+Email.ToString ()+", "+UserName.ToString ()+",
"+DiyPassword.ToString ()+"", null, "OK", null);
uav.Show ();
}
catch(Exception exp)
{
UIAlertView uv=new UIAlertView("Notification",
"Unexpected Database
Error! "+exp.GetBaseException ().ToString ()+"", null, "OK", null);
uv.Show ();
}
//this.CloseDBConnection ();
}
public DataSet CheckForData (string strsql)
{
DataSet ds = new DataSet ();
this.CreateDBConnection ();
sconn.ConnectionString=dbConn;
scmd.Connection=sconn;
scmd.CommandText=strsql;
scmd.CommandType=CommandType.Text;
SqliteDataAdapter sda=new SqliteDataAdapter(scmd);
sda.Fill (ds);
this.CloseDBConnection ();
return ds;
}
}
}
Now I am calling the function InitializeClientData like this -->
objdbh.InitializeClientData(); where objdbh is an instance of DBHandler.cs.
But when I am using breakpoints I noticed that the debugger isn't entering
the block for InitializeClientData AT ALL!!! When I expanded the quick view
for the above line and expanded the sconn properties node, I found out that
ConnectionString ="" and DataSource=null.!!
Strange!! I understand there must be some loophole in the coding logic that
I am using, but I can't seem to pinpoint it. It may be that the code is
facing ambiguity due to multiple connection objects with same name and as
such throwing an exception.
Look forward to receiving some help on this.
Many Thanks.
--
View this message in context:
http://monotouch.2284126.n4.nabble.com/MonoTouch-with-Sqlite-Critical-Scenario-Help-Needed-tp4655574p4655652.html
Sent from the MonoTouch mailing list archive at Nabble.com.
_______________________________________________
MonoTouch mailing list
[email protected]
http://lists.ximian.com/mailman/listinfo/monotouch
