mcs/class/Mono.Data.Sqlite/Test/SqliteTest.cs

   1 //\r
   2 // SqliteTest.cs - Test for the Sqlite ADO.NET Provider in Mono.Data.Sqlite\r
   3 //                 This provider works on Linux and Windows and uses the native\r
   4 //                 sqlite.dll or sqlite.so library.\r
   5 //\r
   6 // Modify or add to this test as needed...\r
   7 //\r
   8 // SQL Lite can be downloaded from\r
   9 // http://www.hwaci.com/sw/sqlite/download.html\r
  10 //\r
  11 // There are binaries for Windows and Linux.\r
  12 //\r
  13 // To compile:\r
  14 //  mcs SqliteTest.cs -r System.Data.dll -r Mono.Data.Sqlite.dll\r
  15 //\r
  16 // Author:\r
  17 //     Daniel Morgan <danmorg@sc.rr.com>\r
  18 //\r
  19 \r
  20 using System;\r
  21 using System.Data;\r
  22 using Mono.Data.Sqlite;\r
  23 \r
  24 namespace Test.Mono.Data.Sqlite\r
  25 {\r
  26         class SqliteTest\r
  27         {\r
  28                 [STAThread]\r
  29                 static void Main(string[] args)\r
  30                 {\r
  31                         Test(false, null);\r
  32                         Console.WriteLine();\r
  33                         Test(false, "ISO-8859-1");\r
  34                         Console.WriteLine();\r
  35                         Test(true, null);\r
  36                 }\r
  37                 \r
  38                 static void Test(bool v3, string encoding) {\r
  39                         if (!v3)\r
  40                                 Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));\r
  41                         else\r
  42                                 Console.WriteLine("Testing Version 3");\r
  43                                 \r
  44                         System.IO.File.Delete("SqliteTest.db");\r
  45                 \r
  46                         SqliteConnection dbcon = new SqliteConnection();\r
  47                         \r
  48                         // the connection string is a URL that points\r
  49                         // to a file.  If the file does not exist, a \r
  50                         // file is created.\r
  51 \r
  52                         // "URI=file:some/path"\r
  53                         string connectionString =\r
  54                                 "URI=file:SqliteTest.db";\r
  55                         if (v3)\r
  56                                 connectionString += ",Version=3";\r
  57                         if (encoding != null)\r
  58                                 connectionString += ",encoding=" + encoding;\r
  59                         dbcon.ConnectionString = connectionString;\r
  60                                 \r
  61                         dbcon.Open();\r
  62 \r
  63                         SqliteCommand dbcmd = new SqliteCommand();\r
  64                         dbcmd.Connection = dbcon;\r
  65                         \r
  66                         dbcmd.CommandText = \r
  67                                 "CREATE TABLE MONO_TEST ( " +\r
  68                                 "NID INT, " +\r
  69                                 "NDESC TEXT, " +\r
  70                                 "NTIME DATETIME); " +\r
  71                                 "INSERT INTO MONO_TEST  " +\r
  72                                 "(NID, NDESC, NTIME) " +\r
  73                                 "VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";\r
  74                         Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());\r
  75 \r
  76                         dbcmd.CommandText =\r
  77                                 "INSERT INTO MONO_TEST  " +\r
  78                                 "(NID, NDESC, NTIME) " +\r
  79                                 "VALUES(:NID,:NDESC,:NTIME)";\r
  80                         dbcmd.Parameters.Add( new SqliteParameter("NID", 2) );\r
  81                         dbcmd.Parameters.Add( new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)") );\r
  82                         dbcmd.Parameters.Add( new SqliteParameter(":NTIME", DateTime.Now) );\r
  83                         Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());\r
  84 \r
  85                         dbcmd.CommandText =\r
  86                                 "INSERT INTO MONO_TEST  " +\r
  87                                 "(NID, NDESC, NTIME) " +\r
  88                                 "VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";\r
  89                         Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());\r
  90 \r
  91                         dbcmd.CommandText =\r
  92                                 "INSERT INTO MONO_TEST  " +\r
  93                                 "(NID, NDESC, NTIME) " +\r
  94                                 "VALUES(4,'Four with ANSI char: ü', NULL)";\r
  95                         Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());\r
  96 \r
  97                         dbcmd.CommandText =\r
  98                                 "INSERT INTO MONO_TEST  " +\r
  99                                 "(NID, NDESC, NTIME) " +\r
 100                                 "VALUES(?,?,?)";\r
 101                         dbcmd.Parameters.Clear();\r
 102                         IDbDataParameter param1 = dbcmd.CreateParameter();\r
 103                         param1.DbType = DbType.DateTime;\r
 104                         param1.Value = 5;\r
 105                         dbcmd.Parameters.Add(param1);                   \r
 106                         IDbDataParameter param2 = dbcmd.CreateParameter();\r
 107                         param2.Value = "Using unnamed parameters";\r
 108                         dbcmd.Parameters.Add(param2);\r
 109                         IDbDataParameter param3 = dbcmd.CreateParameter();\r
 110                         param3.DbType = DbType.DateTime;\r
 111                         param3.Value = DateTime.Parse("2006-05-11 11:45:00");\r
 112                         dbcmd.Parameters.Add(param3);\r
 113                         Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());\r
 114 \r
 115                         dbcmd.CommandText =\r
 116                                 "SELECT * FROM MONO_TEST";\r
 117                         SqliteDataReader reader;\r
 118                         reader = dbcmd.ExecuteReader();\r
 119 \r
 120                         Console.WriteLine("read and display data...");\r
 121                         while(reader.Read())\r
 122                                 for (int i = 0; i < reader.FieldCount; i++)\r
 123                                         Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",\r
 124                                                 i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));\r
 125 \r
 126                         dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";\r
 127                         Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());\r
 128 \r
 129                         dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";\r
 130                         Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());\r
 131 \r
 132                         Console.WriteLine("read and display data using DataAdapter/DataSet...");\r
 133                         SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);\r
 134                         DataSet dataset = new DataSet();\r
 135                         adapter.Fill(dataset);\r
 136                         foreach(DataTable myTable in dataset.Tables){\r
 137                                 foreach(DataRow myRow in myTable.Rows){\r
 138                                         foreach (DataColumn myColumn in myTable.Columns){\r
 139                                                 Console.WriteLine(" " + myRow[myColumn]);\r
 140                                         }\r
 141                                 }\r
 142                         }\r
 143 \r
 144                         /*Console.WriteLine("read and display data using DataAdapter/DataTable...");\r
 145                         DataTable dt = new DataTable();\r
 146                         adapter.Fill(dt);\r
 147                         DataView dv = new DataView(dt);\r
 148                         foreach (DataRowView myRow in dv) {\r
 149                                 foreach (DataColumn myColumn in myRow.Row.Table.Columns) {\r
 150                                         Console.WriteLine(" " + myRow[myColumn.ColumnName]);\r
 151                                 }\r
 152                         }*/\r
 153                                             \r
 154                         try {\r
 155                                 dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";\r
 156                                 dbcmd.ExecuteNonQuery();\r
 157                                 Console.WriteLine("Should not reach here.");\r
 158                         } catch (Exception e) {\r
 159                                 Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);\r
 160                         }\r
 161 \r
 162                         /*try {\r
 163                                 dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";\r
 164                                 Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());\r
 165                         } catch (Exception e) {\r
 166                                 Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);\r
 167                         }*/\r
 168 \r
 169                         dataset.Dispose();\r
 170                         adapter.Dispose();\r
 171                         reader.Close();\r
 172                         dbcmd.Dispose();\r
 173                         dbcon.Close();\r
 174                 }\r
 175         }\r
 176 }\r