GTranslate

 

DBExample1

 

 

DBExample1 The Baseline App Plus Table With Foreign Key & Spinner

DBExample2Example1 Plus DateTime Handling
A DateTime field is added to the local C# class, with code added to manipulate its ‘tick’ value as an integer within the DBMS table.

DBExample3Example2 Plus Ability To Add To, Or Delete Rows From,
The Foreign Key Table

Source files: DBExample1.zip

This example takes the starting example and edits it to add a table associated with the original one through a foreign key relationship. Then, the data contained by that table is used to populate a spinner object whenever you add a dog or edit an existing dog’s data.

We retain the set of capabilities which access the database of pet-dog information, so we continue to work with a known set of activities that make up the app:

  • 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

 

DBExample1.filetreeHowever, 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.

 

Database

Data stored about the source of each dog is contained within the `Sources` table, which at present consists solely of the name of the source, with the row’s index. To store data about specific dogs, we have continue to four fields in the “master” table, with changes to the final column definition:

  • each record’s unique database-row id
  • the dog’s Name
  • any info we want to add about this pup, and
  • the ID of the row in the `Sources` table that details where we got the dog from.

 

The database schema for this example now 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

     );

 

Then, the creation string for the `Dogs` table changes to this, which includes the constraint definition establishing the foreign-key relationship between the two tables:

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

         CONSTRAINT `IDSourceDogs_FK`

8

             FOREIGNKEY (`IDSource`)

9

             REFERENCES `Sources` (`_id`)

10

             ON DELETE NO ACTION

11

             ON UPDATE CASCADE

12

     );

 

[SQLite docs:  http://www.sqlite.org/keyword_index.html.]

 

Contrary to the way the original app behaves, because our backing store and our display fields are no longer the same, starting with this example we will use instances of a class setup specifically for display,  DFDog (DataFormatDog class), that combines information from the Dog and Source classes for ease in manipulating display fields, retaining classes that mirror the two data tables to facilitate easier CRUD cycles.

This split is done so that the classes set up for DBMS operations can be kept separate from those specifically for the complexities of multiple-table display formatting procedures; with those classes in place you can create constructors or other methods for transferring data between classes to facilitate timely update.

The original Dog class’s fourth field has been changed from a string a long to hold the foreign key value. Methods have been added to provide additional constructors (particularly for calls from the constructors in DFDog and DFSource), as well as an override for ToString(), and a ToDebugString() method for utility.

 

1

// Dog.cs

2

namespace DBExample1

3

{

4

    public class Dog

5

    {

6

        public long   DB_id      { getset; }

7

        public string Name       { getset; }

8

        public string Info       { getset; }

9

        public long   DBIDSource { getset; }

10

 

11

        public Dog()

12

        {

13

            DB_id      = -1;

14

            Name       = string.Empty;

15

            Info       = string.Empty;

16

            DBIDSource = -1;

17

        }

18

 

19

        public Dog(Dog d)

20

        {

21

            DB_id      = d.DB_id;

22

            Name       = d.Name;

23

            Info       = d.Info;

24

            DBIDSource = d.DBIDSource;

25

        }

26

 

27

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

28

        {

29

            DB_id      = id;

30

            Name       = name;

31

            Info       = info;

32

            DBIDSource = idSrc;

33

        }

34

 

35

        public override string ToString()

36

        {

37

            return Name;

38

        }

39

 

40

        public string ToDebugString()

41

        {

42

            return string.Format(

43

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

44

                                 DB_id, Name, Info, DBIDSource);

45

        }

46

    }

47

 

48

    public class Source

49

    {

50

        public long   DB_id      { getset; }

51

        public string SourceName { getset; }

52

 

53

        public Source()

54

        {

55

            DB_id      = -1;

56

            SourceName = string.Empty;

57

        }

58

 

59

        public Source(long id, string name)

60

        {

61

            DB_id      = id;

62

            SourceName = name;

63

        }

64

 

65

        public Source(Source s)

66

        {

67

            DB_id      = s.DB_id;

68

            SourceName = s.SourceName;

69

        }

70

 

71

        public override string ToString()

72

        {

73

            return SourceName;

74

        }

75

        

76

        public string ToDebugString()

77

        {

78

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

79

                                 DB_id, SourceName);

80

        }

81

 

82

        // object equality depends on the paired field values

83

        //

84

        public override bool Equals(object o)

85

        {

86

            if(o == null)

87

                return false;

88

 

89

            Source s = o as Source;

90

            if(s == null)

91

                return false;

92

 

93

            return (DB_id == s.DB_id) 

94

                && (SourceName == s.SourceName);

95

        }

96

 

97

        public bool Equals(Source s)

98

        {

99

            if(s == null)

100

                return false;

101

 

102

            return (DB_id      == s.DB_id) 

103

                && (SourceName == s.SourceName);

104

        }

105

 

106

        public bool Equals(string s)

107

        {

108

            return (s == null

109

                 || s != SourceName)

110

                   ? false

111

                   : true;

112

        }

113

 

114

        public override int GetHashCode()

115

        {

116

            return SourceName.GetHashCode();

117

        }

118

    }

119

}

 

The data-formatting classes in DFDog.cs extend the base classes in Dog.cs with data needed, for the spinner control, in particular. It also contains a class and methods used to facilitate orderly transfer of data between activities using the Intent. (In larger applications, one would consider implementing IParcelable as the way to pass data between activities, but with the very small nature of the data flow here, it’s not necessary.)

 

 

1

// DFDog.cs

2

using System;

3

using System.Collections;

4

using System.Collections.Generic;

5

using Android.Content;

6

 

7

 

8

namespace DBExample1

9

{

10

    public class DFDog : Dog

11

    {

12

        public static string[] dataIdxData0 = { 

13

            "dogDBID0",    "dogName0",    "dogInfo0"

14

            "dogSrcDBID0""dogSrcName0""dogSrcSpID0"

15

        };

16

        public static string[] dataIdxData1 = { 

17

            "dogDBID1",    "dogName1",    "dogInfo1"

18

            "dogSrcDBID1""dogSrcName1""dogSrcSpID1"

19

        };

20

 

21

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

22

        public string SrcName { getset; }

23

 

24

        public DFDog() : base()

25

        {

26

            SrcName = string.Empty;

27

            DogPos  = 0;

28

        }

29

 

30

        public DFDog(Dog d) : base(d)

31

        {

32

            SrcName = string.Empty;

33

            DogPos  = 0;

34

        }

35

 

36

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

37

        {

38

            SrcName = src;

39

            DogPos  = 0;

40

        }

41

 

42

        public DFDog(long   dogID, 

43

                     string dogName, 

44

                     string dogInfo, 

45

                     string srcName, 

46

                     long   srcDBID)

47

            : base(dogID, dogName, dogInfo, srcDBID)

48

        {

49

            SrcName = srcName;

50

            DogPos  = 0;

51

        }

52

 

53

        public DFDog(Intent data, string[] dataIdx)

54

        {

55

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

56

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

57

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

58

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

59

 

60

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

61

        }

62

 

63

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

64

        {

65

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

66

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

67

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

68

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

69

 

70

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

71

        }

72

 

73

        public new string ToDebugString()

74

        {

75

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

76

                                 base.ToDebugString(), SrcName);

77

        }

78

 

79

        public bool Equals(DFDog dfd)

80

        {

81

            if(dfd == null)

82

                return false;

83

 

84

            Dog d = dfd as DFDog;

85

 

86

            if(d == null

87

            || dfd.SrcName != SrcName)

88

                return false;

89

 

90

            return base.Equals(d);

91

        }

92

 

93

        public override bool Equals(object o)

94

        {

95

            DFDog dfd = o as DFDog;

96

            if(dfd == null)

97

                return false;

98

 

99

            if(dfd.SrcName != SrcName)

100

                return false;

101

 

102

            return base.Equals(o);

103

        }

104

 

105

        public override int GetHashCode()

106

        {

107

            return base.GetHashCode();

108

        }

109

    }

100

 

111

    public class DFSource : Source

112

    {

113

        public long DFSPItemID { getset; }

114

 

115

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

116

 

117

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

118

            : base(dbRowID, srcName)

119

        {

120

            DFSPItemID = dfID;

121

        }

122

 

123

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

124

        {

125

            DFSPItemID = dfID;

126

        }

127

 

128

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

129

        {

130

            DFSPItemID = dfs.DFSPItemID;

131

        }

132

 

133

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

134

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

135

        public override string ToString()

136

        {

137

            return SourceName;

138

        }

139

 

140

        public new string ToDebugString()

141

        {

142

            return base.ToDebugString()

143

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

144

        }

145

 

146

 

147

        public override bool Equals(object o)

148

        {

149

            return base.Equals(o);

150

        }

151

 

152

        public override int GetHashCode()

153

        {

154

            return base.GetHashCode();

155

        }

156

    }

157

 

158

    public static class DFDogIntentData

159

    {

160

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

161

        {

162

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

163

        }

164

 

165

        public static void SetOriginalData(Intent data, DFDog d)

166

        {

167

            d.ToData(data, DFDog.dataIdxData0);

168

        }

169

 

170

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

171

        {

172

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

173

        }

174

 

175

        public static void SetNewData(Intent data, DFDog d)

176

        {

177

            d.ToData(data, DFDog.dataIdxData1);

178

        }

179

    }

180

}

 

 

Example1’s definition of the DBMS interface adds a method to get all the Sources rows into a generic List, and changes the return types for the delete methods from void to bool:

1

// IDogRepository.cs

2

using System;

3

 

4

 

5

namespace DBExample1

6

{

7

    public interface IDogRepository

8

    {

9

        List<DFDog>  GetAllDogs();

10

        List<Source> GetAllSources();

11

 

12

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

13

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

14

 

15

        long GetRowCount(string tableName);

16

 

17

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

18

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

19

 

20

        bool DeleteDog(long rowID);

21

        bool DeleteDog(string dogName);

22

        bool DeleteDog(string pivotCol, object pivotVal);

23

    }

24

}

 

The two methods that include parameters starting with “pivot” use string formatting within the methods to provide generalized ways of calling into the DBMS.

Thus the three DeleteDog methods support, from top to bottom, deletion by row ID, deletion by the dog’s name, and deletion of all rows in which the pivot column has the specified string value.

Main.cs

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

 

TheDogRepository.cs: Expanded Implementation of IDogRepository:

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 DBExample1

 

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

                                CONSTRAINT `IDSourceDogs_FK`

 

153

                                    FOREIGN KEY (`IDSource`)

 

154

                                    REFERENCES `Sources` (`_id`)

 

155

                                    ON DELETE NO ACTION 

 

156

                                    ON UPDATE CASCADE

 

157

                            )";

 

158

 

 

159

                    command.ExecuteNonQuery();

 

160

                    connection.Close();

 

161

                }

 

162

 

 

163

            #if DEBUG

 

164

            {

 

165

                if(dbgInitializeData == 1)

 

166

                {

 

167

                    string[,] testDog = {

 

168

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

 

169

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

 

170

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

 

171

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

 

172

                    };

 

173

 

 

174

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

 

175

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

 

176

                }

 

177

            }

 

178

            #endif

 

179

 

 

180

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

 

181

            return true;

 

182

        }

 

183

 

 

184

        public List<DFDog> GetAllDogs()

 

185

        {

 

186

            var dogList = new List<DFDog>();

 

187

 

 

188

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

 

189

 

 

190

            try

 

191

            {

 

192

                using(var connection = _GetConnection(true))

 

193

                    using(var command = connection.CreateCommand())

 

194

                    {

 

195

                        connection.Open();

 

196

                        command.CommandText = 

 

197

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

198

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

199

                          + "ORDER BY Name;";

200

 

 

201

                        using(var reader = command.ExecuteReader())

 

202

                            while(reader.Read())

 

203

                                dogList.Add(_ReadDog(reader));

 

204

 

 

205

                        connection.Close();

 

206

                    }

 

207

            }

 

208

            catch(SqliteException e)

 

209

            {

 

210

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

 

211

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

 

212

 

 

213

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

 

214

                //          there may be something more serious wrong!

 

215

 

 

216

                dogList.Clear();

 

217

                return dogList;

 

218

            }

 

219

 

 

220

            Log.Debug("GetAllDogs"

 

221

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

 

222

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

 

223

 

 

224

            return dogList;

 

225

        }

 

226

 

 

227

        private DFDog _ReadDog(SqliteDataReader rdr)

 

228

        {

 

229

            //retrieve values using column name as index

 

230

                                                              // use 'AS' alias as index

 

231

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


232

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

 

233

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

 

234

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

235

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

236

 

 

237

            return new DFDog {

 

238

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

 

239

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

 

240

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

 

241

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

242

                SrcName    = (string)rdr["SourceName"]

243

            };

 

244

        }

 

245

 

 

246

 

 

247

        public long GetRowCount(string tableName)

 

248

        {

 

249

            long rows = 0;

 

250

 

 

251

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

 

252

                                       tableName);

 

253

 

 

254

            try

 

255

            {

 

256

                using(var connection = _GetConnection(false))

 

257

                    using(var command = connection.CreateCommand())

 

258

                    {

 

259

                        connection.Open();

 

260

 

 

261

                        command.CommandText = cmd;

 

262

                        var reader = command.ExecuteReader();

 

263

 

 

264

                        if(reader.Read())

 

265

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

 

266

 

 

267

                        connection.Close();

 

268

                    }

 

269

            }

 

270

            catch(SqliteException e)

 

271

            {

 

272

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

 

273

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

 

274

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

 

275

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

 

276

                //   during development

 

277

            }

 

278

 

 

279

            return rows;

 

280

        }

 

281

 

 

282

        public List<Source> GetAllSources()

 

283

        {

 

284

            var sources = new List<Source>();

 

285

 

 

286

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

 

287

 

 

288

            using(var connection = _GetConnection(true))

 

289

                using(var command = connection.CreateCommand())

 

290

                {

 

291

                    connection.Open();

 

292

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

 

293

 

 

294

                    using(var reader = command.ExecuteReader())

 

295

                        while(reader.Read())

 

296

                            sources.Add(new Source {

 

297

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

 

298

                                SourceName = (string)reader["SourceName"]

 

299

                            });

 

300

 

 

301

                    connection.Close();

 

302

                }

 

303

 

 

304

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

 

305

            return sources;

 

306

        }

 

307

 

 

308

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

 

309

        {

 

310

            Log.Debug("AddDog[0]"

 

311

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

 

312

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

 

313

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

 

314

                      name, info, source);

 

315

 

 

316

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

 

317

                            + "VALUES ( @name, @info, "

 

318

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

319

 

 

320

            try

 

321

            {

 

322

                using(var connection = _GetConnection(true))

 

323

                    using(var command = connection.CreateCommand())

 

324

                    {

 

325

                        connection.Open();

 

326

 

 

327

                        command.CommandText = strCommand;

 

328

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

 

329

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

 

330

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

 

331

 

 

332

                        command.ExecuteNonQuery();

 

333

 

 

334

                        connection.Close();

 

335

                    }

 

336

            }

 

337

            catch(SqliteException e)

 

338

            {

 

339

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

 

340

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

 

341

 

 

342

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

 

343

                //          there may be something more serious wrong!

 

344

 

 

345

                return false;

 

346

            }

 

347

 

 

348

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

 

349

            return true;

 

350

        }

 

351

 

 

352

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

 

353

        {

 

354

            Log.Debug("AddDog[1]"

 

355

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

 

356

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

 

357

                      name, info, sourceID);

 

358

 

 

359

            string strCommand = 

 

360

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

 

361

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

 

362

 

 

363

            try

 

364

            {

 

365

                using(var connection = _GetConnection(true))

 

366

                    using(var command = connection.CreateCommand())

 

367

                    {

 

368

                        connection.Open();

 

369

 

 

370

                        command.CommandText = strCommand;

 

371

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

 

372

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

 

373

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

 

374

 

 

375

                        command.ExecuteNonQuery();

 

376

 

 

377

                        connection.Close();

 

378

                    }

 

379

            }

 

380

            catch(SqliteException e)

 

381

            {

 

382

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

 

383

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

 

384

 

 

385

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

 

386

                //          there may be something more serious wrong!

 

387

 

 

388

                return false;

 

389

            }

 

390

 

 

391

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

 

392

            return true;

 

393

        }

 

394

 

 

395

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

 

396

        {

 

397

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

 

398

        }

 

399

 

 

400

        public bool UpdateDog(string pivotCol, object pivotVal, 

 

401

                              string updCol,   object updVal)

 

402

        {

 

403

            Log.Debug("UpdateDog"

 

404

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

 

405

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

 

406

 

 

407

            string strCommand = 

 

408

                    string.Format("UPDATE Dogs "

 

409

                                + "SET   {0} = @updValue "

 

410

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

 

411

                                  updCol, pivotCol);

 

412

 

 

413

            try

 

414

            {

 

415

                using(var connection = _GetConnection(true))

 

416

                    using(var command = connection.CreateCommand())

 

417

                    {

 

418

                        connection.Open();

 

419

 

 

420

                        command.CommandText = strCommand;

 

421

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

 

422

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

 

423

 

 

424

                        command.ExecuteNonQuery();

 

425

 

 

426

                        connection.Close();

 

427

                    }

 

428

            }

 

429

            catch(SqliteException e)

 

430

            {

 

431

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

 

432

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

 

433

 

 

434

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

 

435

                //          there may be something more serious wrong!

 

436

 

 

437

                return false;

 

438

            }

 

439

 

 

440

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

 

441

            return true;

 

442

        }

 

443

 

 

444

        public bool DeleteDog(long id)

 

445

        {

 

446

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

 

447

        }

 

448

 

 

449

        public bool DeleteDog(string name)

 

450

        {

 

451

            return DeleteDog("Name", name);

 

452

        }

 

453

 

 

454

 

 

455

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

 

456

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

 

457

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

 

458

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

 

459

        // efficiency or clarity.

 

460

        public bool DeleteDog(string pivotCol, object pivotVal)

 

461

        {

 

462

            Log.Debug("DeleteDog"

 

463

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

 

464

                      pivotCol, pivotVal);

 

465

 

 

466

            string strCommand = 

 

467

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

 

468

                                    pivotCol);

 

469

 

 

470

            try

 

471

            {

 

472

                using(var connection = _GetConnection(true))

 

473

                    using(var command = connection.CreateCommand())

 

474

                    {

 

475

                        connection.Open();

 

476

 

 

477

                        command.CommandText = strCommand;

 

478

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

 

479

 

 

480

                        command.ExecuteNonQuery();

 

481

 

 

482

                        connection.Close();

 

483

                    }

 

484

            }

 

485

            catch(SqliteException e)

 

486

            {

 

487

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

 

488

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

 

489

 

 

490

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

 

491

                //          there may be something more serious wrong!

 

492

 

 

493

                return false;

 

494

            }

 

495

 

 

496

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

 

497

            return true;

 

498

        }

 

499

    }

 

500

}

 

 

 

The Processes:

Listing of dogs, and displaying individual data

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

DBExample1.main         DBExample1.ViewADog         DBExample1.ViewADog.Menu

 

2. Creating new records, or editing existing ones

The single largest change to either of these activities is the use of a common class to create and manage the spinner that restricts the user’s choice of Source. This and the rest of the example use a static data set held by the Sources table, but it should be easy enough for the user to create an activity that would allow adding or deleting of Source rows. When you do this, though, be sure to be aware of the FOREIGN KEY CONSTRAINT, and have your code prepared to handle any refusals by the DBMS to delete a Source if it is used by a row within the Dogs table.


1

 // SourceSpinner.cs

2

using System;

3

using System.Collections.Generic;

4

using Android.Content;

5

using Android.OS;

6

using Android.Widget;

7

using Android.Util;

8

 

9

 

10

namespace DBExample1

11

{

12

    public static class SourceSpinner

13

    {

14

        public static DFSource dfsSelected = new DFSource();

15

 

16

        public static List<Source> InitDFSourceList(    List<Source>   srcList, 

17

                                                    ref List<DFSource> dfSourceList)

18

        {

19

            if(srcList != null)

20

                srcList.Clear();

21

            srcList = MainApp.TheDogRepository.GetAllSources();

22

 

23

            if(dfSourceList == null)

24

            {

25

                dfSourceList = new List<DFSource>();

26

 

27

                for(int idx = 0; idx < srcList.Count; idx++)

28

                {

29

                    dfSourceList.Add(new DFSource(srcList[idx], (long)idx));

30

                    dfSourceList[idx].DFSPItemID = (long)idx;

31

 

32

                    Log.Debug("SourceSpinner"

33

                              "InitDFSourceList(): dfSourceList[idx] = {0}"

34

                              dfSourceList[idx].ToDebugString());

35

                }

36

            }

37

 

38

            return srcList;

39

        }

40

 

41

        public static void InitSourceSpinner(Context ctxt, Spinner sp, DFSource newDFSource)

42

        {

43

            ArrayAdapter<DFSource> aaSrc = 

44

                new ArrayAdapter<DFSource>(ctxt, Android.Resource.Layout.SimpleSpinnerItem);

45

 

46

            aaSrc.SetDropDownViewResource(Android.Resource.Layout.SimpleSpinnerDropDownItem);

47

 

48

            List<DFSource> lDFSrc = MainActivity.DFSourceList;

49

 

50

            for(int i = 0; i < lDFSrc.Count; i++)

51

            {

52

                aaSrc.Add(lDFSrc[i]);

53

 

54

                Log.Debug("SourceSpinner.InitSourceSpinner"

55

                          "added to aaSrc: lSrc[{0}] = {1}", i, lDFSrc[i].ToDebugString());

56

            }

57

 

58

            sp.Adapter = aaSrc;

59

 

60

            //Note that this call to SetSelection MUST come AFTER the spinner's Adapter

61

            //  value has been set (line 56), else it (has no target obj and) fails silently!

62

            sp.SetSelection((int)newDFSource.DFSPItemID);

63

            sp.Prompt = "Select source:";

64

 

65

            Log.Debug("SourceSpinner.InitSourceSpinner"

66

                      "SetSelection({0})", ((int)newDFSource.DFSPItemID).ToString());

67

 

68

            sp.ItemSelected += 

69

                delegate(object sender, AdapterView.ItemSelectedEventArgs e)

70

                {

71

                    dfsSelected = aaSrc.GetItem(e.Position) as DFSource;

72

                    dfsSelected.DFSPItemID = e.Position;

73

 

74

                    Log.Debug("SourceSpinner.InitSourceSpinner.ItemSelected"

75

                              "dfsSelected = {0}; e = {1}", dfsSelected.ToDebugString(), 

76

                                                            e.ToString());

77

                };

78

        }

79

    }

80

}

 

DBExample1.EditTheDog         DBExample1.EditTheDogWithSpinner

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

 

PREV    —    NEXT

 

 

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