結(jié)合使用 ADO.NET 和 AndroidUsing ADO.NET with Android

02/08/2018

本文內(nèi)容

Xamarin 內(nèi)置了對適用于 Android 的 SQLite 數(shù)據(jù)庫的支持，可以使用熟悉的類似 ADO.NET 的語法公開。Xamarin has built-in support for the SQLite database that is available on Android and can be exposed using familiar ADO.NET-like syntax. 使用這些 Api 需要編寫由 SQLite 處理的 SQL 語句，如 CREATE TABLE、INSERT 和 SELECT 語句。Using these APIs requires you to write SQL statements that are processed by SQLite, such as CREATE TABLE, INSERT and SELECT statements.

程序集引用Assembly References

若要通過 ADO.NET 使用訪問 SQLite，你必須將 System.Data 和 Mono.Data.Sqlite 引用添加到 Android 項(xiàng)目，如下所示：To use access SQLite via ADO.NET you must add System.Data and Mono.Data.Sqlite references to your Android project, as shown here:

右鍵單擊 "引用" > 編輯引用 ... "，然后單擊以選擇所需的程序集。Right-click References > Edit References... then click to select the required assemblies.

關(guān)于 Mono. SqliteAbout Mono.Data.Sqlite

我們將使用 Mono.Data.Sqlite.SqliteConnection 類創(chuàng)建一個(gè)空數(shù)據(jù)庫文件，然后實(shí)例化可用于對數(shù)據(jù)庫執(zhí)行 SQL 指令的 SqliteCommand 對象。We will use the Mono.Data.Sqlite.SqliteConnection class to create a blank database file and then to instantiate SqliteCommand objects that we can use to execute SQL instructions against the database.

創(chuàng)建空白數(shù)據(jù)庫– 調(diào)用具有有效(即可寫)文件路徑的 CreateFile 方法。Creating a Blank Database – Call the CreateFile method with a valid (i.e. writeable) file path. 在調(diào)用此方法之前，應(yīng)檢查文件是否已存在，否則將在舊文件的頂部創(chuàng)建新的(空白)數(shù)據(jù)庫，并將丟失舊文件中的數(shù)據(jù)。You should check whether the file already exists before calling this method, otherwise a new (blank) database will be created over the top of the old one, and the data in the old file will be lost.

Mono.Data.Sqlite.SqliteConnection.CreateFile (dbPath); dbPath 變量應(yīng)按照本文檔前面討論的規(guī)則確定。Mono.Data.Sqlite.SqliteConnection.CreateFile (dbPath); The dbPath variable should be determined according the rules discussed earlier in this document.

創(chuàng)建數(shù)據(jù)庫連接– 創(chuàng)建 SQLite 數(shù)據(jù)庫文件后，可以創(chuàng)建連接對象來訪問數(shù)據(jù)。Creating a Database Connection – After the SQLite database file has been created you can create a connection object to access the data. 連接是使用采用 Data Source=file_path形式的連接字符串構(gòu)造的，如下所示：The connection is constructed with a connection string which takes the form of Data Source=file_path, as shown here:

var connection = new SqliteConnection ("Data Source=" + dbPath);

connection.Open();

// do stuff

connection.Close();

如前所述，連接永遠(yuǎn)不能在不同的線程之間重復(fù)使用。 如果有疑問，請根據(jù)需要?jiǎng)?chuàng)建連接并在完成后關(guān)閉它;但請注意，此操作的執(zhí)行頻率要高于所需的頻率。If in doubt, create the connection as required and close it when you're done; but be mindful of doing this more often than required too.

創(chuàng)建和執(zhí)行數(shù)據(jù)庫命令– 一旦建立連接，就可以對其執(zhí)行任意 SQL 命令。Creating and Executing a Database Command – Once we have a connection we can execute arbitrary SQL commands against it. 下面的代碼顯示正在執(zhí)行的 CREATE TABLE 語句。The code below shows a CREATE TABLE statement being executed.

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

command.CommandText = "CREATE TABLE [Items] ([_id] int, [Symbol] ntext, [Name] ntext);";

var rowcount = command.ExecuteNonQuery ();

}

當(dāng)直接針對數(shù)據(jù)庫執(zhí)行 SQL 時(shí)，應(yīng)采取正常的預(yù)防措施來避免發(fā)出無效請求，例如，嘗試創(chuàng)建已存在的表。When executing SQL directly against the database you should take the normal precautions not to make invalid requests, such as attempting to create a table that already exists. 跟蹤數(shù)據(jù)庫的結(jié)構(gòu)，以便不會導(dǎo)致 SqliteException 如SQLite 錯(cuò)誤表 [Items] 已存在。Keep track of the structure of your database so that you don't cause a SqliteException such as SQLite error table [Items] already exists.

基本數(shù)據(jù)訪問Basic Data Access

在 Android 上運(yùn)行時(shí)，此文檔的DataAccess_Basic示例代碼如下所示：The DataAccess_Basic sample code for this document looks like this when running on Android:

下面的代碼演示了如何執(zhí)行簡單的 SQLite 操作并在應(yīng)用程序的主窗口中以文本形式顯示結(jié)果。The code below illustrates how to perform simple SQLite operations and shows the results in as text in the application's main window.

需要包含以下命名空間：You'll need to include these namespaces:

using System;

using System.IO;

using Mono.Data.Sqlite;

下面的代碼示例演示了整個(gè)數(shù)據(jù)庫交互：The following code sample shows an entire database interaction:

創(chuàng)建數(shù)據(jù)庫文件Creating the database file

插入一些數(shù)據(jù)Inserting some data

查詢數(shù)據(jù)Querying the data

這些操作通常出現(xiàn)在代碼中的多個(gè)位置，例如，你可以在應(yīng)用程序第一次啟動(dòng)時(shí)創(chuàng)建數(shù)據(jù)庫文件和表，并在應(yīng)用中的各個(gè)屏幕上執(zhí)行數(shù)據(jù)讀寫操作。These operations would typically appear in multiple places throughout your code, for example you may create the database file and tables when your application first starts and perform data reads and writes in individual screens in your app. 在下面的示例中，已將此示例中的一個(gè)方法分組為一個(gè)方法：In the example below have been grouped into a single method for this example:

public static SqliteConnection connection;

public static string DoSomeDataAccess ()

{

// determine the path for the database file

string dbPath = Path.Combine (

Environment.GetFolderPath (Environment.SpecialFolder.Personal),

"adodemo.db3");

bool exists = File.Exists (dbPath);

if (!exists) {

Console.WriteLine("Creating database");

// Need to create the database before seeding it with some data

Mono.Data.Sqlite.SqliteConnection.CreateFile (dbPath);

connection = new SqliteConnection ("Data Source=" + dbPath);

var commands = new[] {

"CREATE TABLE [Items] (_id ntext, Symbol ntext);",

"INSERT INTO [Items] ([_id], [Symbol]) VALUES ('1', 'AAPL')",

"INSERT INTO [Items] ([_id], [Symbol]) VALUES ('2', 'GOOG')",

"INSERT INTO [Items] ([_id], [Symbol]) VALUES ('3', 'MSFT')"

};

// Open the database connection and create table with data

connection.Open ();

foreach (var command in commands) {

using (var c = connection.CreateCommand ()) {

c.CommandText = command;

var rowcount = c.ExecuteNonQuery ();

Console.WriteLine("\tExecuted " + command);

}

}

} else {

Console.WriteLine("Database already exists");

// Open connection to existing database file

connection = new SqliteConnection ("Data Source=" + dbPath);

connection.Open ();

}

// query the database to prove data was inserted!

using (var contents = connection.CreateCommand ()) {

contents.CommandText = "SELECT [_id], [Symbol] from [Items]";

var r = contents.ExecuteReader ();

Console.WriteLine("Reading data");

while (r.Read ())

Console.WriteLine("\tKey={0}; Value={1}",

r ["_id"].ToString (),

r ["Symbol"].ToString ());

}

connection.Close ();

}

更復(fù)雜的查詢More Complex Queries

由于 SQLite 允許對數(shù)據(jù)運(yùn)行任意 SQL 命令，因此你可以執(zhí)行所需的任何 CREATE、INSERT、UPDATE、DELETE或 SELECT 語句。Because SQLite allows arbitrary SQL commands to be run against the data, you can perform whatever CREATE, INSERT, UPDATE, DELETE, or SELECT statements you like. 可以在 SQLite 網(wǎng)站上閱讀 SQLite 支持的 SQL 命令。You can read about the SQL commands supported by SQLite at the SQLite website. 使用 SqliteCommand 對象上三種方法之一來運(yùn)行 SQL 語句：The SQL statements are run using one of three methods on an SqliteCommand object:

ExecuteNonQuery – 通常用于表創(chuàng)建或數(shù)據(jù)插入。ExecuteNonQuery – Typically used for table creation or data insertion. 某些運(yùn)算的返回值為受影響的行數(shù)，否則為-1。The return value for some operations is the number of rows affected, otherwise it's -1.

當(dāng)行集合應(yīng)作為 SqlDataReader返回時(shí)，使用ExecuteReader –。ExecuteReader – Used when a collection of rows should be returned as a SqlDataReader.

ExecuteScalar – 檢索單個(gè)值(例如聚合)。ExecuteScalar – Retrieves a single value (for example an aggregate).

EXECUTENONQUERYEXECUTENONQUERY

INSERT、UPDATE和 DELETE 語句將返回受影響的行數(shù)。INSERT, UPDATE, and DELETE statements will return the number of rows affected. 所有其他 SQL 語句將返回-1。All other SQL statements will return -1.

using (var c = connection.CreateCommand ()) {

c.CommandText = "INSERT INTO [Items] ([_id], [Symbol]) VALUES ('1', 'APPL')";

var rowcount = c.ExecuteNonQuery (); // rowcount will be 1

}

EXECUTEREADEREXECUTEREADER

下面的方法演示了 SELECT 語句中的 WHERE 子句。The following method shows a WHERE clause in the SELECT statement.

由于代碼正在編寫完整的 SQL 語句，因此它必須小心地轉(zhuǎn)義保留字符，如引號(")，如字符串。Because the code is crafting a complete SQL statement it must take care to escape reserved characters such as the quote (') around strings.

public static string MoreComplexQuery ()

{

var output = "";

output += "\nComplex query example: ";

string dbPath = Path.Combine (

Environment.GetFolderPath (Environment.SpecialFolder.Personal), "ormdemo.db3");

connection = new SqliteConnection ("Data Source=" + dbPath);

connection.Open ();

using (var contents = connection.CreateCommand ()) {

contents.CommandText = "SELECT * FROM [Items] WHERE Symbol = 'MSFT'";

var r = contents.ExecuteReader ();

output += "\nReading data";

while (r.Read ())

output += String.Format ("\n\tKey={0}; Value={1}",

r ["_id"].ToString (),

r ["Symbol"].ToString ());

}

connection.Close ();

return output;

}

ExecuteReader 方法返回 SqliteDataReader 對象。The ExecuteReader method returns a SqliteDataReader object. 除了示例中所示的 Read 方法以外，其他有用的屬性包括：In addition to the Read method shown in the example, other useful properties include:

RowsAffected – 受查詢影響的行的計(jì)數(shù)。RowsAffected – Count of the rows affected by the query.

HasRows – 是否返回了任何行。HasRows – Whether any rows were returned.

EXECUTESCALAREXECUTESCALAR

對于返回單個(gè)值(例如聚合)的 SELECT 語句，請使用此函數(shù)。Use this for SELECT statements that return a single value (such as an aggregate).

using (var contents = connection.CreateCommand ()) {

contents.CommandText = "SELECT COUNT(*) FROM [Items] WHERE Symbol <> 'MSFT'";

var i = contents.ExecuteScalar ();

}

ExecuteScalar 方法的返回類型 object – 應(yīng)根據(jù)數(shù)據(jù)庫查詢強(qiáng)制轉(zhuǎn)換結(jié)果。The ExecuteScalar method's return type is object – you should cast the result depending on the database query. 結(jié)果可能是來自 COUNT 查詢的整數(shù)，或是 SELECT 查詢的單個(gè)列中的字符串。The result could be an integer from a COUNT query or a string from a single column SELECT query. 請注意，這不同于其他 Execute 方法，這些方法返回讀取器對象或受影響的行數(shù)的計(jì)數(shù)。Note that this is different to other Execute methods that return a reader object or a count of the number of rows affected.

相關(guān)鏈接Related Links

總結(jié)

以上是生活随笔為你收集整理的mono for android mysql_结合使用 ADO.NET 和 Android的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。