GTranslate

 

DBExample2

 

 DBExample2Example1 Plus DateTime Handling & Ability To Add Rows To The Foreign Key Table

Source files: DBExample2.zip

This example takes the second example and adds a column to the main table to store a date as an integer. It also adds the ability to add entries to the `Sources` table, which are then made available immediately to the Spinner control.

The expanded set of capabilities afforded in accessing the database of pet-dog information are now these:

  • get a starting/central list of a existing dog records by name – MainActivity
  • view individual dog’s info, with option to delete the record – ViewDogActivity
  • add a dog – AddDogActivity
  • edit a dog’s info – EditDogActivity
  • add a source – AddSourceActivity

 

However, using a single table in this first example will show the basic set-up of all the examples. And it will ease those who have had limited database-development instruction or experience, into the concepts underlying the organization of data within larger, real-world applications.

 

DBExample2.filetreeDatabase

As in DBExample1, data stored about the source of each dog is contained within the `Sources` table. To store data about specific dogs, we have continue to five fields in the “master” table:

  • each record’s unique database-row id
  • the dog’s Name
  • any info we want to add about this pup
  • the ID of the row in the `Sources` table that details where we got the dog from, and
  • an integer holding the “ticks” used by the DateTime control to encode dates.

The database schema for this example still starts with the SQLite creation string for the `Sources` table:

1

CREATE TABLE IF NOT EXISTS 

2

     Sources (

3

         _id INTEGER PRIMARY KEY AUTOINCREMENT,

4

         SourceName   VARCHAR(32) NOT NULL UNIQUE

5

     );

 

However, the creation string for the `Dogs` table has been changed to the following:

1

CREATE TABLE IF NOT EXISTS 

2

     Dogs (

3

         _id INTEGER PRIMARY KEY AUTOINCREMENT,

4

         Name     TEXT NOT NULL UNIQUE,

5

         Info     TEXT NOT NULL,

6

         IDSource INTEGER  NULL,

7

         DTBirth  BIGINT   NULL,

8

         CONSTRAINT `IDSourceDogs_FK`

9

             FOREIGNKEY (`IDSource`)

10

             REFERENCES `Sources` (`_id`)

11

             ON DELETE NO ACTION

12

             ON UPDATE CASCADE

13

     );

 

 

The rationale behind using a BIGINT column to hold the date/time value in the SQLite table is that doing so means you know exactly how it's going to be stored there (so you can avoid any questions arising from SQLite's loose typing). And it allows you to use the built-in functionality of the C# DateTime class across all platforms, giving you dependable code-consistency.

 

Because we’ve added a column, the classes holding this information internally also have to be edited. Both the DFDog(DataFormatDog class), and the Dog class have been extended with fields and other data.

 

1

// Dog.cs

2

using System;

3

 

4

namespace DBExample2

5

{

6

    public class Dog

7

    {

8

        public long     DB_id      { getset; }

9

        public string   Name       { getset; }

10

        public string   Info       { getset; }

11

        public long     DBIDSource { getset; }

12

        public DateTime DTBirth    { getset; }

13

 

14

        public Dog()

15

        {

16

            DB_id      = -1;

17

            Name       = string.Empty;

18

            Info       = string.Empty;

19

            DBIDSource = -1;

20

            DTBirth    = DateTime.MinValue;

21

        }

22

 

23

        public Dog(Dog d)

24

        {

25

            DB_id      = d.DB_id;

26

            Name       = d.Name;

27

            Info       = d.Info;

28

            DBIDSource = d.DBIDSource;

29

            DTBirth    = d.DTBirth;

30

        }

31

 

32

        public Dog(long id, string name, string info, DateTime dt, long idSrc)

33

        {

34

            DB_id      = id;

35

            Name       = name;

36

            Info       = info;

37

            DBIDSource = idSrc;

38

            DTBirth    = new DateTime(dt.Ticks);

39

        }

40

 

41

        public override string ToString()

42

        {

43

            return Name;

44

        }

45

 

46

        public string ToDebugString()

47

        {

48

            return string.Format(

49

                "Dog{{ DB_id({0}), Name({1}), Info({2}), DBIDSource({3}),"

50

              + "DTBirth({4} [{5}L]) }}",

51

                       DB_id, Name, Info, DBIDSource, 

52

                       DTBirth.ToShortDateString(), DTBirth.Ticks);

53

        }

54

    }

55

 

56

    public class Source

57

    {

58

        public long   DB_id      { getset; }

59

        public string SourceName { getset; }

60

 

61

        public Source()

62

        {

63

            DB_id      = -1;

64

            SourceName = string.Empty;

65

        }

66

 

67

        public Source(long id, string name)

68

        {

69

            DB_id      = id;

70

            SourceName = name;

71

        }

72

 

73

        public Source(Source s)

74

        {

75

            DB_id      = s.DB_id;

76

            SourceName = s.SourceName;

77

        }

78

 

79

        public override string ToString()

80

        {

81

            return SourceName;

82

        }

83

 

84

        public string ToDebugString()

85

        {

86

            return string.Format("Source {{ DB_id={0}, SourceName={1} }}"

87

                                 DB_id, SourceName);

88

        }

89

 

90

        // object equality depends on the paired field values

91

        //

92

        public override bool Equals(object o)

93

        {

94

            if(o == null)

95

                return false;

96

 

97

            Source s = o as Source;

98

            if(s == null)

99

                return false;

100

 

101

            return (DB_id == s.DB_id) 

102

                && (SourceName == s.SourceName);

103

        }

104

 

105

        public bool Equals(Source s)

106

        {

107

            if(s == null)

108

                return false;

109

 

110

            return (DB_id      == s.DB_id) 

111

                && (SourceName == s.SourceName);

112

        }

113

 

114

        public bool Equals(string s)

115

        {

116

            return (s == null

117

                 || s != SourceName)

118

                   ? false

119

                   : true;

120

        }

121

 

122

        public override int GetHashCode()

123

        {

124

            return SourceName.GetHashCode();

125

        }

126

    }

127

}

 

The DFDog.cs file has been augmented with a class, public static class DBEU, (the ‘U’ is for utility) that defines extension methods for the otherwise unmodifiable Android.Content.Intent class. (If you have not worked with C# extension methods yet, you might start here: http://msdn.microsoft.com/en-us/library/vstudio/bb383977.aspx).

 

 

1

// DFDog.cs

2

using System;

3

using System.Collections;

4

using System.Collections.Generic;

5

using Android.Content;

6

 

7

 

8

namespace DBExample2

9

{

10

    public static class DBEU

11

    {

12

        public static DateTime GetDTExtra(this Intent data, string strID)

13

        {

14

            return new DateTime(data.GetLongExtra(strID, DateTime.MinValue.Ticks));

15

        }

16

 

17

        public static void PutExtra(this Intent data, string strID, DateTime dt)

18

        {

19

            data.PutExtra(strID, dt.Ticks);

20

        }

21

    }

22

 

23

    public class DFDog : Dog

24

    {

25

        public static string[] dataIdxData0 = {

26

            "dogDBID0""dogName0""dogInfo0"

27

            "dogDOB0""dogSrcDBID0""dogSrcName0"

28

        };

29

        public static string[] dataIdxData1 = { 

30

            "dogDBID1""dogName1""dogInfo1"

31

            "dogDOB1""dogSrcDBID1""dogSrcName1"

32

        };

33

 

34

        public long   DogPos  { getset; } //position in view list

35

        public string SrcName { getset; }

36

 

37

        public DFDog() : base()

38

        {

39

            SrcName = string.Empty;

40

            DogPos  = 0;

41

        }

42

 

43

        public DFDog(Dog d) : base(d)

44

        {

45

            SrcName = string.Empty;

46

            DogPos  = 0;

47

        }

48

 

49

        public DFDog(Dog d, string src) : base(d)

50

        {

51

            SrcName = src;

52

            DogPos  = 0;

53

        }

54

 

55

        public DFDog(long     dogID,

56

                     string   dogName, 

57

                     string   dogInfo, 

58

                     DateTime dtBirth,

59

                     string   srcName, 

60

                     long     srcDBID)

61

            : base(dogID, dogName, dogInfo, dtBirth, srcDBID)

62

        {

63

            SrcName = srcName;

64

            DogPos  = 0;

65

        }

66

 

67

        public DFDog(Intent data, string[] dataIdx)

68

        {

69

            DB_id      = data.GetLongExtra(dataIdx[0], -1);

70

            Name       = data.GetStringExtra(dataIdx[1]);

71

            Info       = data.GetStringExtra(dataIdx[2]);

72

            DTBirth    = data.GetDTExtra(dataIdx[3]);

73

            DBIDSource = data.GetLongExtra(dataIdx[4], 0);

74

 

75

            SrcName    = data.GetStringExtra(dataIdx[5]);

76

        }

77

 

78

        public void ToData(Intent data, string[] dataIdx)

79

        {

80

            data.PutExtra(dataIdx[0], DB_id);

81

            data.PutExtra(dataIdx[1], Name);

82

            data.PutExtra(dataIdx[2], Info);

83

            data.PutExtra(dataIdx[3], DTBirth);

84

            data.PutExtra(dataIdx[4], DBIDSource);

85

 

86

            data.PutExtra(dataIdx[5], SrcName);

87

        }

88

 

89

        public new string ToDebugString()

90

        {

91

            return string.Format("DFDog: {{ [ {0} ], SrcName({1}) }}",

92

                                 base.ToDebugString(), SrcName);

93

        }

94

 

95

        public bool Equals(DFDog dfd)

96

        {

97

            if(dfd == null)

98

                return false;

99

 

100

            Dog d = dfd as DFDog;

101

 

102

            if(d == null

103

            || dfd.SrcName != SrcName)

104

                return false;

105

 

106

            return base.Equals(d);

107

        }

108

 

109

        public override bool Equals(object o)

110

        {

111

            DFDog dfd = o as DFDog;

112

            if(dfd == null)

113

                return false;

114

 

115

            if(dfd.SrcName != SrcName)

116

                return false;

117

 

118

            return base.Equals(o);

119

        }

120

 

121

        public override int GetHashCode()

122

        {

123

            return base.GetHashCode();

124

        }

125

    }

126

 

127

    public class DFSource : Source

128

    {

129

        public long DFSPItemID { getset; }

130

 

131

        public DFSource() : base() { DFSPItemID = -1; }

132

 

133

        public DFSource(string srcName, long dbRowID, long dfID)

134

            : base(dbRowID, srcName)

135

        {

136

            DFSPItemID = dfID;

137

        }

138

 

139

        public DFSource(Source src, long dfID) : base(src)

140

        {

141

            DFSPItemID = dfID;

142

        }

143

 

144

        public DFSource(DFSource dfs) : base(dfs.DB_id, dfs.SourceName)

145

        {

146

            DFSPItemID = dfs.DFSPItemID;

147

        }

148

 

149

        // This is the method that the Spinner control will call

150

        //  to get the value to display for each of its options:

151

        public override string ToString()

152

        {

153

            return SourceName;

154

        }

155

 

156

        public new string ToDebugString()

157

        {

158

            return base.ToDebugString()

159

                 + string.Format(" DFItemID: {0}", DFSPItemID);

160

        }

161

 

162

        public bool Equals(DFSource dfs)

163

        {

164

            return base.Equals((Source)dfs);

165

        }

166

 

167

        public override bool Equals(object o)

168

        {

169

            return base.Equals(o);

170

        }

171

 

172

        public override int GetHashCode()

173

        {

174

            return base.GetHashCode();

175

        }

176

    }

177

 

178

    public static class DFDogIntentData

179

    {

180

        public static void GetOriginalData(Intent data, out DFDog d)

181

        {

182

            d = new DFDog(data, DFDog.dataIdxData0);

183

        }

184

 

185

        public static void SetOriginalData(Intent data, DFDog d)

186

        {

187

            d.ToData(data, DFDog.dataIdxData0);

188

        }

189

 

190

        public static void GetNewData(Intent data, out DFDog d)

191

        {

192

            d = new DFDog(data, DFDog.dataIdxData1);

193

        }

194

 

195

        public static void SetNewData(Intent data, DFDog d)

196

        {

197

            d.ToData(data, DFDog.dataIdxData1);

198

        }

199

    }

200

}

 

 

Example2’s declaration of the DBMS interface adds parameters for handling the DateTime values:

 

1

// IDogRepository.cs

2

using System;

3

using System.Collections.Generic;

4

 

5

 

6

namespace DBExample2

7

{

8

    public interface IDogRepository

9

    {

10

        List<DFDog>  GetAllDogs();

11

        List<Source> GetAllSources();

12

 

13

        bool AddDog(string name, string info, DateTime dtBirth,  string source);

14

        bool AddDog(string name, string info, long     birthTix, string source);

15

        bool AddDog(string name, string info, DateTime dtBirth,  long   sourceID);

16

 

17

        long GetRowCount(string tableName);

18

 

19

        bool UpdateDog(string dogName, string updCol, object updVal);

20

        bool UpdateDog(string pivotCol, object pivotVal, string updCol, object updVal);

21

        bool UpdateDog(string dogName, string updCol, DateTime updVal);

22

 

23

        bool DeleteDog(long rowID);

24

        bool DeleteDog(string dogName);

25

        bool DeleteDog(string pivotCol, object pivotVal);

26

 

27

        bool AddSource(string srcName);

28

    }

29

}

 

 

Main.cs

The MainApp class and the starting activity (and screen) have no changes between the base version and this version.

 

 

TheDogRepository.cs: Implementation of IDogRepository Further Expanded:

 

The constructor of the DogRepository class sets up the two readonly fields in that class: it creates the string holding the full database path, and then uses it to set up the _dataSource field for the use of the private method _GetConnection(). Then it calls the private method _SetupDatabase(), which creates the database if it doesn’t exist, and does a reset of the database and clean initialization of it during debugging, if desired.

 

Data is read into instances of the Dog class using the SqliteDataReader class. The text-indices into rdr are the names of the columns, as given in the schema used to create the table.  One of the indexers within the SqliteDataReader class uses the name given to look up the value in the data obtained by its parent (DbDataReader) from the SELECT command.

There are two sources of differences within the DBMS code starting with this example. One is that the second table is created, initialized and referenced (possibly using a JOIN) during CRUD operations. The other is when initializing the connection to the DBMS: the connection string chosen is one that either does or does not cause the DBMS to enforce the FOREIGN KEY constraint listed in the new creation string of the main table, Dogs. (Major changes are marked below by the left-pointing arrow in the rightmost column.)

 

1

// TheDogRepository.cs

2

using System;

3

using System.Collections.Generic;

4

using System.IO;

5

using Mono.Data.Sqlite;

6

using Android.Util;

7

using Android.Widget;

8

 

9

namespace DBExample2

10

{

11

    public class DogRepository : IDogRepository

12

    {

13

        #if DEBUG

14

        static int dbgInitializeData = 0;

15

        #endif

16

 

17

        private readonly string _databasePath;

18

        private readonly string _dataSource;

19

        private readonly string _dataSourceFK;

20

 

21

        public DogRepository()

22

        {

23

            _databasePath = 

24

                Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal),

25

                             MainApp.DBName);

26

 

27

            // create two datasource strings, one that will have the DBMS enforce

28

            // the foreign key constraint, another in which it’s relaxed

29

            _dataSource   = "Data Source=" + _databasePath;

30

            _dataSourceFK = "Data Source=" + _databasePath + ";foreign keys=true;";

31

 

32

            _SetupDatabase();

33

        }

34

 

35

        private SqliteConnection _GetConnection(bool withForeignKeySupport)

36

        {

37

            try

38

            {

39

                if(withForeignKeySupport)

40

                    return new SqliteConnection(_dataSourceFK);

41

                else

42

                    return new SqliteConnection(_dataSource);

43

            }

44

            catch(SqliteException e)

45

            {

46

                Log.Debug("Could not create instance of SqliteConnection({0}): "

47

                          _dataSource, e.Message);

48

                return null;

49

            }

50

        }

51

 

52

        private bool _SetupDatabase()

53

        {

54

            if(!File.Exists(_databasePath))

55

            {

56

                Log.Debug("_SetupDatabase""CreateFile({0})", _databasePath);

57

 

58

                try

59

                {

60

                    SqliteConnection.CreateFile(_databasePath);

61

                }

62

                catch(Exception e) // if thrown, will come from File.Create call, 

63

                {                  //  not from SQLite lib

64

                    Log.Debug("_SetupDatabase"

65

                              "SqliteConnection.CreateFile({0}) failed: {1}"

66

                              _databasePath, e.Message);

67

                    return false;

68

                }

69

 

70

            #if DEBUG

71

                dbgInitializeData = 1;

72

            #endif

73

 

74

            }

75

 

76

            // The following database accesses are set up so as not to fail, so

77

            //  they shouldn't need try/catch blocks

78

 

79

            #if DEBUG

80

            {

81

                // While debugging, set a breakpoint on this 'if' statement and change 

82

                //   the value  of 'dbgInitializeData' to 1 before it's tested   

83

                //   if you need to reinitialize an existing database to known 

84

                //   values, which is done below, at next test of 'dbgInitializeData'.

85

                //

86

                if(dbgInitializeData == 1)

87

                {

88

                    Log.Debug("_SetupDatabase""Deleting table definitions.");

89

                    using(var connection = _GetConnection(false))

90

                        using(var command = connection.CreateCommand())

91

                        {

92

                            connection.Open();

93

                            command.CommandText =

94

                                @"DROP TABLE IF EXISTS Sources;

95

                                  DROP TABLE IF EXISTS Dogs;";

96

 

97

                            command.ExecuteNonQuery();

98

                            connection.Close();

99

                        }

100

 

101

                    Log.Debug("_SetupDatabase""Done.");

102

                }

103

            }

104

            #endif

105

 

106

            Log.Debug("_SetupDatabase""Creating `Sources` table if not exists.");

107

            using(var connection = _GetConnection(false))

108

                using(var command = connection.CreateCommand())

109

                {

110

                    connection.Open();

111

                    command.CommandText =

112

                        @"CREATE TABLE IF NOT EXISTS

113

                          Sources (

114

                              _id INTEGER PRIMARY KEY AUTOINCREMENT, 

115

                              SourceName VARCHAR(32) UNIQUE NOT NULL

116

                          );";

117

 

118

                    command.ExecuteNonQuery();

119

                    connection.Close();

120

                }

121

 

122

            Log.Debug("_SetupDatabase""Inserting values into `Sources` table.");

123

            using(var connection = _GetConnection(false))

124

                using(var command = connection.CreateCommand())

125

                {

126

                    // JTBS: Use INSERT OR IGNORE instead of simple INSERT.

127

                    // (If the record is already present, it does no good 

128

                    //  to throw an exception,

129

                    //  so just have SQLite ignore it.

130

                    connection.Open();

131

                    command.CommandText =

132

                   @"INSERT OR IGNORE INTO Sources ( SourceName ) VALUES ( 'breeder' );

133

                      INSERT OR IGNORE INTO Sources ( SourceName ) VALUES ( 'Animal Rescue' );

134

                      INSERT OR IGNORE INTO Sources ( SourceName ) VALUES ( 'Boxer Rescue' );";

135

 

136

                    command.ExecuteNonQuery();

137

                    connection.Close();

138

                }

139

 

140

            Log.Debug("_SetupDatabase""Creating `Dogs` table if not exists.");

141

            using(var connection = _GetConnection(false))

142

                using(var command = connection.CreateCommand())

143

                {

144

                    connection.Open();

145

                    command.CommandText =

146

                        @"CREATE TABLE IF NOT EXISTS 

147

                            Dogs (

148

                                _id INTEGER PRIMARY KEY AUTOINCREMENT,

149

                                Name     TEXT    NOT NULL UNIQUE,

150

                                Info     TEXT    NOT NULL,

151

                                IDSource INTEGER     NULL,

152

                                DTBirth  BIGINT      NULL,

153

                                CONSTRAINT `IDSourceDogs_FK`

154

                                    FOREIGN KEY (`IDSource`)

155

                                    REFERENCES `Sources` (`_id`)

156

                                    ON DELETE NO ACTION 

157

                                    ON UPDATE CASCADE

158

                            )";

159

 

160

                    command.ExecuteNonQuery();

161

                    connection.Close();

162

                }

163

 

164

            #if DEBUG

165

            {

166

                if(dbgInitializeData == 1)

167

                {

168

                    string[,] testDog = {

169

                        { "Maggie Bear""fawn Boxer female",     "breeder"       },

170

                        { "Jason",       "fawn Boxer male",       "Boxer Rescue"  },

171

                        { "Wicket",      "Brussels Griffon male""breeder"       },

172

                        { "Nyx",         "Chug male",             "Animal Rescue" }

173

                    };

174

 

175

                    long[] testDogDOB = {

176

                        DateTime.MinValue.Ticks,           

177

                        (new DateTime(2007, 10,  1)).Ticks,

178

                        (new DateTime(2006,  1, 23)).Ticks,

179

                        (new DateTime(2006,  2, 14)).Ticks,

180

                    };

181

 

182

                    for(int i = 0; i < testDog.GetLength(0); i++)

183

                        AddDog(testDog[i, 0], testDog[i, 1], testDogDOB[i], testDog[i, 2]);

184

                }

185

            }

186

            #endif

187

 

188

            Log.Debug("_SetupDatabase""Done.");

189

            return true;

190

        }

191

 

192

        public List<DFDog> GetAllDogs()

193

        {

194

            var dogList = new List<DFDog>();

195

 

196

            Log.Debug("GetAllDogs""Retrieving.");

197

 

198

            try

199

            {

200

                using(var connection = _GetConnection(true))

201

                    using(var command = connection.CreateCommand())

202

                    {

203

                        connection.Open();

204

                        command.CommandText = 

205

                            "SELECT d._id AS Id, Name, Info, DTBirth, IDSource, SourceName "

206

                          + "FROM Dogs AS d JOIN Sources AS s ON (d.IDSource = s._id) "

207

                          + "ORDER BY Name;";

208

 

209

                        using(var reader = command.ExecuteReader())

210

                            while(reader.Read())

211

                                dogList.Add(_ReadDog(reader));

212

 

213

                        connection.Close();

214

                    }

215

            }

216

            catch(SqliteException e)

217

            {

218

                Log.Debug("GetAllDogs""SqliteException '{0}' caught: {1}"

219

                          e.ErrorCode.ToString(), e.Message);

220

 

221

                // TODO: check the ErrorCode: if it's not a constraint error, 

222

                //          there may be something more serious wrong!

223

 

224

                dogList.Clear();

225

                return dogList;

226

            }

227

 

228

            Log.Debug("GetAllDogs"

229

                      "Success, with records: DB(n) = {0}; list(n) = {1}"

230

                      GetRowCount("Dogs"), dogList.Count);

231

 

232

            return dogList;

233

        }

234

 

235

        private DFDog _ReadDog(SqliteDataReader rdr)

236

        {

237

            //retrieve values using column name as index

238

                                                              // use 'AS' alias as index

239

            Log.Debug("_ReadDog""d._id = {0}",        (long)rdr["Id"]);

240

            Log.Debug("_ReadDog""Name = {0}",       (string)rdr["Name"]);

241

            Log.Debug("_ReadDog""Info = {0}",       (string)rdr["Info"]); 

242

            Log.Debug("_ReadDog""DTBirth = {0}",      (long)rdr["DTBirth"]); 

243

            Log.Debug("_ReadDog""IDSource = {0}",     (long)rdr["IDSource"]);

244

            Log.Debug("_ReadDog""SourceName = {0}", (string)rdr["SourceName"]);

245

 

246

            return new DFDog {

247

                DB_id      =              (long)rdr["Id"],

248

                Name       =            (string)rdr["Name"],

249

                Info       =            (string)rdr["Info"],

250

                DTBirth    = new DateTime((long)rdr["DTBirth"]),

251

                DBIDSource =              (long)rdr["IDSource"],

252

                SrcName    =            (string)rdr["SourceName"]

253

            };

254

        }

255

 

256

 

257

        public long GetRowCount(string tableName)

258

        {

259

            long rows = 0;

260

 

261

            string cmd = string.Format("SELECT COUNT(*) AS num FROM {0}",

262

                                       tableName);

263

 

264

            try

265

            {

266

                using(var connection = _GetConnection(false))

267

                    using(var command = connection.CreateCommand())

268

                    {

269

                        connection.Open();

270

 

271

                        command.CommandText = cmd;

272

                        var reader = command.ExecuteReader();

273

 

274

                        if(reader.Read())

275

                            rows = (long)Convert.ToInt32(reader["num"]);

276

 

277

                        connection.Close();

278

                    }

279

            }

280

            catch(SqliteException e)

281

            {

282

                Log.Debug("GetRowCount""SqliteException '{0}' caught: {1}"

283

                          e.ErrorCode.ToString(), e.Message);

284

                // this will keep you from bombing if you misspell a 

285

                //   table name, but still give you a chance to catch it

286

                //   during development

287

            }

288

 

289

            return rows;

290

        }

291

 

292

        public List<Source> GetAllSources()

293

        {

294

            var sources = new List<Source>();

295

 

296

            Log.Debug("GetAllSources""Retrieving.");

297

 

298

            using(var connection = _GetConnection(true))

299

                using(var command = connection.CreateCommand())

300

                {

301

                    connection.Open();

302

                    command.CommandText = "SELECT * FROM Sources ORDER BY SourceName;";

303

 

304

                    using(var reader = command.ExecuteReader())

305

                        while(reader.Read())

306

                            sources.Add(new Source {

307

                                DB_id      =   (long)reader["_id"],

308

                                SourceName = (string)reader["SourceName"]

309

                            });

310

 

311

                    connection.Close();

312

                }

313

 

314

            Log.Debug("GetAllSources""Success: n = {0}", sources.Count);

315

            return sources;

316

        }

317

 

318

        public bool AddDog(string name, string info, DateTime dt, string source)

319

        {

320

            return AddDog(name, info, dt.Ticks, source); 

321

        }

322

 

323

        public bool AddDog(string name, string info, long birthTix, string source)

324

        {

325

            Log.Debug("AddDog[0]"

326

                      "CommandText: INSERT INTO Dogs ( Name, Info, DTBirth, IDSource ) "

327

                    + "VALUES ( {0}, {1}, {2}, "

328

                    + "(SELECT _id FROM Sources WHERE SourceName = {3}) );"

329

                      name, info, birthTix, source);

330

 

331

            string strCommand = "INSERT INTO Dogs ( Name, Info, DTBirth, IDSource ) "

332

                              + "VALUES ( @name, @info, @dtTicks, "

333

                              + "( SELECT _id FROM Sources WHERE SourceName = @source ) );";

334

 

335

            try

336

            {

337

                using(var connection = _GetConnection(true))

338

                    using(var command = connection.CreateCommand())

339

                    {

340

                        connection.Open();

341

 

342

                        command.CommandText = strCommand;

343

                        command.Parameters.AddWithValue("@name",    name);

344

                        command.Parameters.AddWithValue("@info",    info);

345

                        command.Parameters.AddWithValue("@dtTicks", birthTix);

346

                        command.Parameters.AddWithValue("@source",  source);

347

 

348

                        command.ExecuteNonQuery();

349

 

350

                        connection.Close();

351

                    }

352

            }

353

            catch(SqliteException e)

354

            {

355

                Log.Debug("AddDog""SqliteException #{0} caught: {1}",

356

                          e.ErrorCode.ToString(), e.Message);

357

 

358

                // TODO: check the ErrorCode: if it's not a constraint error, 

359

                //          there may be something more serious wrong!

360

 

361

                return false;

362

            }

363

 

364

            Log.Debug("AddDog[0]""{0}: Success.", name);

365

            return true;

366

        }

367

 

368

        public bool AddDog(string name, string info, DateTime dt, long sourceID)

369

        {

370

            Log.Debug("AddDog[1]"

371

                      "CommandText: INSERT INTO Dogs (Name, Info, DTBirth, IDSource)"

372

                    + " VALUES ( {0}, {1}, {2}[{3}L], {4} )"

373

                      name, info, dt.ToShortDateString(), dt.Ticks, sourceID);

374

 

375

            string strCommand = 

376

                      "INSERT INTO Dogs ( Name, Info, DTBirth, IDSource ) "

377

                    + "VALUES ( @name, @info, @dtTicks, @srcID );";

378

 

379

            try

380

            {

381

                using(var connection = _GetConnection(true))

382

                    using(var command = connection.CreateCommand())

383

                    {

384

                        connection.Open();

385

 

386

                        command.CommandText = strCommand;

387

                        command.Parameters.AddWithValue("@name",    name);

388

                        command.Parameters.AddWithValue("@info",    info);

389

                        command.Parameters.AddWithValue("@dtTicks", dt.Ticks);

390

                        command.Parameters.AddWithValue("@srcID",   sourceID);

391

 

392

                        command.ExecuteNonQuery();

393

 

394

                        connection.Close();

395

                    }

396

            }

397

            catch(SqliteException e)

398

            {

399

                Log.Debug("AddDog[1]""SqliteException #{0} caught: {1}",

400

                          e.ErrorCode.ToString(), e.Message);

401

 

402

                // TODO: check the ErrorCode: if it's not a constraint error, 

403

                //          there may be something more serious wrong!

404

 

405

                return false;

406

            }

407

 

408

            Log.Debug("AddDog[1]""Success.");

409

            return true;

410

        }

411

 

412

        public bool UpdateDog(string dogName, string updCol, object updVal)

413

        {

414

            return UpdateDog("Name", dogName, updCol, updVal);

415

        }

416

 

417

        public bool UpdateDog(string pivotCol, object pivotVal, 

418

                              string updCol,   object updVal)

419

        {

420

            Log.Debug("UpdateDog"

421

                      "CommandText: UPDATE Dogs SET {0} = '{1}' WHERE {2} = '{3}';",

422

                      updCol, updVal.ToString(), pivotCol, pivotVal.ToString());

423

 

424

            string strCommand = 

425

                    string.Format("UPDATE Dogs "

426

                                + "SET   {0} = @updValue "

427

                                + "WHERE {1} = @pivotValue;",

428

                                  updCol, pivotCol);

429

 

430

            try

431

            {

432

                using(var connection = _GetConnection(true))

433

                    using(var command = connection.CreateCommand())

434

                    {

435

                        connection.Open();

436

 

437

                        command.CommandText = strCommand;

438

                        command.Parameters.AddWithValue("@updValue",    updVal);

439

                        command.Parameters.AddWithValue("@pivotValue",  pivotVal);

440

 

441

                        command.ExecuteNonQuery();

442

 

443

                        connection.Close();

444

                    }

445

            }

446

            catch(SqliteException e)

447

            {

448

                Log.Debug("UpdateDog""SqliteException #{0} caught: {1}"

449

                          e.ErrorCode.ToString(), e.Message);

450

 

451

                // TODO: check the ErrorCode: if it's not a constraint error, 

452

                //          there may be something more serious wrong!

453

 

454

                return false;

455

            }

456

 

457

            Log.Debug("UpdateDog""Success.");

458

            return true;

459

        }

460

 

461

        public bool UpdateDog(string dogName, string updCol, DateTime updVal)

462

        {

463

            Log.Debug("UpdateDog"

464

                      "CommandText: UPDATE Dogs SET {0} = '{1}[{2}L]' WHERE Name = '{3}';",

465

                      updCol, updVal.ToShortDateString(), updVal.Ticks, dogName);

466

 

467

            string strCommand = 

468

                string.Format("UPDATE Dogs SET {0} = @updValue WHERE Name = @dogName;",

469

                              updCol);

470

 

471

            using(var connection = _GetConnection(true))

472

                using(var command = connection.CreateCommand())

473

                {

474

                    connection.Open();

475

 

476

                    command.CommandText = strCommand;

477

                    command.Parameters.AddWithValue("@updValue", updVal.Ticks);

478

                    command.Parameters.AddWithValue("@dogName",  dogName);

479

 

480

                    command.ExecuteNonQuery();

481

 

482

                    connection.Close();

483

                }

484

 

485

            Log.Debug("UpdateDog""Done.");

486

            return true;

487

        }

488

 

489

        public bool DeleteDog(long id)

490

        {

491

            return DeleteDog("_id", (object)id);

492

        }

493

 

494

        public bool DeleteDog(string name)

495

        {

496

            return DeleteDog("Name", name);

497

        }

498

 

499

 

500

        //WARNING: if pivotCol is NOT defined in the schema as UNIQUE, this method

501

        //          will delete all rows in which the pivotCol has the pivotVal!

502

        //This example is here to show how you can use string formatting on the command

503

        // value to do things that you need to tailor to your application for speed,

504

        // efficiency or clarity.

505

        public bool DeleteDog(string pivotCol, object pivotVal)

506

        {

507

            Log.Debug("DeleteDog"

508

                      "CommandText: DELETE FROM Dogs WHERE {0} = {1};"

509

                      pivotCol, pivotVal);

510

 

511

            string strCommand = 

512

                      string.Format(@"DELETE FROM Dogs WHERE {0} = @pivotValue;"

513

                                    pivotCol);

514

 

515

            try

516

            {

517

                using(var connection = _GetConnection(true))

518

                    using(var command = connection.CreateCommand())

519

                    {

520

                        connection.Open();

521

 

522

                        command.CommandText = strCommand;

523

                        command.Parameters.AddWithValue("@pivotValue",  pivotVal);

524

 

525

                        command.ExecuteNonQuery();

526

 

527

                        connection.Close();

528

                    }

529

 

530

            }

531

            catch(SqliteException e)

532

            {

533

                Log.Debug("DeleteDog""SqliteException #{0} caught: {1}"

534

                          e.ErrorCode.ToString(), e.Message);

535

 

536

                // TODO: check the ErrorCode: if it's not a constraint error, 

537

                //          there may be something more serious wrong!

538

 

539

                return false;

540

            }

541

 

542

            Log.Debug("DeleteDog""Success.");

543

            return true;

544

        }

545

 

546

        public bool AddSource(string newSource)

547

        {

548

            Log.Debug("AddSource"

549

                      "CommandText: INSERT OR IGNORE INTO Sources ( SourceName ) VALUES ( {0} );"

550

                      newSource);

551

 

552

            using(var connection = _GetConnection(true))

553

                using(var command = connection.CreateCommand())

554

                {

555

                    connection.Open();

556

                    command.CommandText = 

557

                        "INSERT OR IGNORE INTO Sources ( SourceName ) VALUES ( @newSrc );";

558

                    command.Parameters.AddWithValue("@newSrc",  newSource);

559

 

560

                    command.ExecuteNonQuery();

561

                    connection.Close();

562

                }

563

            return true;

564

        }

565

    }

566

}

 

 

 

The Processes:

Listing of dogs, and displaying individual data

Listing activities have the same screens in this example as the base example.

DBExample2.Main         DBExample2.ViewADog          DBExample2.ViewADog.withMenu

 

2. Creating new `Dog` records, or editing existing ones

These operations remain much the same as in the previous examples, with the added ability to set the dog’s date of birth.

DBExample2.EditTheDog         DBExample2.EditTheDog.withSource         DBExample2.EditTheDog.withDate 

Other than addition of the date control, the “Add a Dog” activity is the same in this example as in the previous two, in that it continues not to have a “Cancel” button, but relies on the ‘back’ button for cancellation.

 

3. Adding new `Source` records

The capacity for adding rows to the `Sources` table is added using a menu that is available on the “Add a Dog” or “Edit the Dog” screens:

DBExample2.EditTheDog.withMenu         DBExample2.EditTheDog.AddASource

After the information has been entered on the “Add a Source” and saved, the entry is available in the “Source” spinner.

PREV

 

 

Site design and contents (other than where otherwise noted)
Copyright © Eduard Qualls, 2013 - 2016.