本文整理汇总了C#中SQLiteConnection.CreateCommand方法的典型用法代码示例。如果您正苦于以下问题：C# SQLiteConnection.CreateCommand方法的具体用法？C# SQLiteConnection.CreateCommand怎么用？C# SQLiteConnection.CreateCommand使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类SQLiteConnection的用法示例。
在下文中一共展示了SQLiteConnection.CreateCommand方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: MultipleResultSets
public void MultipleResultSets()
{
using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
conn.ExecuteNonQuery(@"create table Test(Id integer not null primary key autoincrement, Value text not null);");
conn.ExecuteNonQuery(@"insert into Test(Id, Value) values(1, 'one'), (2, 'two');");
using (var cmd = (SQLiteCommand) conn.CreateCommand())
{
cmd.CommandText = @"select Value from Test where Id = @First; select Value from Test where Id = @Second;";
cmd.Parameters.Add("First", DbType.Int64).Value = 1L;
cmd.Parameters.Add("Second", DbType.Int64).Value = 2L;
using (var reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual("one", reader.GetString(0));
Assert.IsFalse(reader.Read());
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(reader.Read());
Assert.AreEqual("two", reader.GetString(0));
Assert.IsFalse(reader.Read());
Assert.IsFalse(reader.NextResult());
}
}
}
}示例2: TableExists
public static bool TableExists(String tableName, SQLiteConnection connection)
{
SQLite.Net.SQLiteCommand cmd = connection.CreateCommand("SELECT * FROM sqlite_master WHERE type = 'table' AND name = '"+tableName+"'");
//cmd.CommandText = ;
if(cmd.ExecuteScalar<int>()== 0)
{return false;}
else
{return true;}
}示例3: CreateTableBySql
public static void CreateTableBySql(SQLiteConnection conn, string tableName, string sql)
{
try
{
if (conn.GetTableInfo(tableName).Count == 0)
{
var cmd = conn.CreateCommand(sql);
var ret = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Logger.Log(LogType.Exception, sql, ex);
}
}示例4: SelectGuidTest
public void SelectGuidTest()
{
using(var con = new SQLiteConnection("Data Source=:memory:"))
using(var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = @"create table tbl1(one uniqueidentifier, two smallint);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values('111d60b1-583f-4278-b91d-8bb65d889730',10);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values('ec334526-8aa9-4441-abf5-e584de00f089',20);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values('cfc6e3d2-d3e9-4e0b-8098-2861220abcaa',310);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"select * from tbl1";
var reader = cmd.ExecuteReader();
while(reader.Read())
{
Assert.AreEqual(typeof(Guid), reader["one"].GetType());
}
}
}示例5: SelectDateTimeTest
public void SelectDateTimeTest()
{
using(var con = new SQLiteConnection("Data Source=:memory:"))
using(var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = @"create table tbl1(one datetime, two smallint);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values('2013-01-04',10);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values('2013-01-04 14:02:33.34',20);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values('2013-01-04 14:02:33.34 -0600',310);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"select * from tbl1";
var reader = cmd.ExecuteReader();
while(reader.Read())
{
Assert.AreEqual(typeof(DateTime), reader["one"].GetType());
Assert.AreNotEqual(default(DateTime), reader["one"]);
}
}
}示例6: SelectFloatingPoint2Test
public void SelectFloatingPoint2Test()
{
using(var con = new SQLiteConnection("Data Source=:memory:"))
using(var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = @"create table tbl1(one float, two smallint);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values(1,10);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values(-1.0,20);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into tbl1 values(53331231.0 ,310);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"select * from tbl1";
var reader = cmd.ExecuteReader();
while(reader.Read())
{
Assert.AreEqual(typeof(double), reader["one"].GetType());
Assert.AreNotEqual(default(double), reader["one"]);
}
}
}示例7: TestDropTableAsync
public void TestDropTableAsync ()
{
string path = null;
var conn = GetConnection (ref path);
conn.CreateTableAsync<Customer> ().Wait ();
// drop it...
conn.DropTableAsync<Customer> ().Wait ();
// check...
using (SQLiteConnection check = new SQLiteConnection (path)) {
// load it back and check - should be missing
var command = check.CreateCommand ("select name from sqlite_master where type='table' and name='customer'");
Assert.IsNull (command.ExecuteScalar<string> ());
}
}示例8: DataTableToTable
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
SQLiteConnectionFlags flags = cnn.Flags;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}示例9: using
/// <summary>
/// Creates temporary tables on the connection so schema information can be queried
/// </summary>
/// <remarks>
/// There's a lot of work involved in getting schema information out of SQLite, but LINQ expects to
/// be able to query on schema tables. Therefore we need to "fake" it by generating temporary tables
/// filled with the schema of the current connection. We get away with making this information static
/// because schema information seems to always be queried on a new connection object, so the schema is
/// always fresh.
/// </remarks>
/// <param name="cnn">The connection upon which to build the schema tables.</param>
void ISQLiteSchemaExtensions.BuildTempSchema(SQLiteConnection cnn)
{
string[] arr = new string[] { "TABLES", "COLUMNS", "VIEWS", "VIEWCOLUMNS", "INDEXES", "INDEXCOLUMNS", "FOREIGNKEYS", "CATALOGS" };
using (DataTable table = cnn.GetSchema("Tables", new string[] { "temp", null, String.Format("SCHEMA{0}", arr[0]) }))
{
if (table.Rows.Count > 0) return;
}
for (int n = 0; n < arr.Length; n++)
{
using (DataTable table = cnn.GetSchema(arr[n]))
{
DataTableToTable(cnn, table, String.Format("SCHEMA{0}", arr[n]));
}
}
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTS;
cmd.ExecuteNonQuery();
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTCOLUMNS;
cmd.ExecuteNonQuery();
}
}示例10: TestDropTableAsync
public async Task TestDropTableAsync()
{
string path = null;
SQLiteAsyncConnection conn = GetConnection(ref path);
await conn.CreateTableAsync<Customer>();
// drop it...
await conn.DropTableAsync<Customer>();
// check...
using (var check = new SQLiteConnection(_sqlite3Platform, path))
{
// load it back and check - should be missing
SQLiteCommand command =
check.CreateCommand("select name from sqlite_master where type='table' and name='customer'");
Assert.IsNull(command.ExecuteScalar<string>());
}
}示例11: PostDbParams
private void PostDbParams(string inName, SQLiteConnection updConn)
#endif
{
_dbParams["version"] = _dbParams["version.major"] + "." + _dbParams["version.minor"];
_dbParams["ot"] = inName.EndsWith(".ont") ? "1" : "0";
_dbParams["nt"] = "1";
_dbParams["strong"] = "0";
if (!_dbParams.ContainsKey("rtl"))
{
_dbParams["rtl"] = "0";
}
var detailTrx = updConn.BeginTransaction();
const string map = "Description=description,Abbreviation=short.title,Comments=about,Version=version,VersionDate=version.date,PublishDate=publish.date,RightToLeft=rtl,OT=ot,NT=nt,Strong=strong";
var mapPat = new Regex(@"([A-Za-z]+)=([\.a-z]+)");
foreach (Match match in mapPat.Matches(map))
{
var key = match.Groups[2].Value;
if (_dbParams.ContainsKey(key))
{
var detailCmd = updConn.CreateCommand();
var newValue = string.Format(@"""{0}""", _dbParams[key]);
newValue = ParseDateIfNecessary(key, newValue);
detailCmd.CommandText = string.Format("update Details SET {0} = {1}", match.Groups[1].Value, newValue);
int result = detailCmd.ExecuteNonQuery();
Debug.Assert(result == 1, match.Groups[0].Value);
}
}
detailTrx.Commit();
}示例12: UpdateScripture
private void UpdateScripture(SQLiteConnection updConn, bool ot)
#endif
{
var books = _vrs.SelectNodes("//bk");
Debug.Assert(books != null);
for (var bkn = ot ? 0 : 39; bkn < 66; )
{
var bk = books[bkn];
bkn += 1;
var matches = _vrsPat.Matches(bk.InnerText);
for (var chn = 1; chn <= matches.Count; chn++)
{
var trx = updConn.BeginTransaction();
var vrsCount = int.Parse(matches[chn - 1].Groups[1].Value);
for (var vrsn = 1; vrsn <= vrsCount; vrsn++)
{
var line = _sr.ReadLine();
Debug.Assert(line != null);
var cmd = updConn.CreateCommand();
line = line.Replace("\"", "\"\"");
cmd.CommandText = string.Format(@"update Bible SET Scripture = ""{0}"" WHERE Book = {1} AND Chapter = {2} AND Verse = {3};", line, bkn, chn, vrsn);
int result = cmd.ExecuteNonQuery();
Debug.Assert(result == 1);
}
trx.Commit();
}
}
}示例13: openFile
public BingoWordFile openFile(string filePath)
{
BingoWordFile file = new BingoWordFile(filePath);
int printTitle = 0;
int printFreeSpace = 0;
Cursor.Current = Cursors.WaitCursor;
try
{
using (DbConnection configdb = new SQLiteConnection("Data Source=" + file.filePath))
{
using (DbCommand cmd = configdb.CreateCommand())
{
configdb.Open();
cmd.CommandText = "SELECT * FROM 'info'";
try
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
try
{
file.cardTitleText = reader["cardtitletext"].ToString();
}
catch
{
file.cardTitleText = "";
}
try
{
file.cardFreeSpaceText = reader["cardfreespacetext"].ToString();
}
catch
{
file.cardFreeSpaceText = "";
}
try
{
file.cardRowSize = int.Parse(reader["cardrowsize"].ToString());
}
catch
{
file.cardRowSize = 5;
}
try
{
file.cardColSize = int.Parse(reader["cardcolsize"].ToString());
}
catch
{
file.cardColSize = 5;
}
try
{
file.numCardsToPrint = int.Parse(reader["numcardstoprint"].ToString());
}
catch
{
file.numCardsToPrint = 1;
}
try
{
file.numCardsPerPage = int.Parse(reader["numcardsperpage"].ToString());
}
catch
{
file.numCardsPerPage = 1;
}
try
{
printTitle = int.Parse(reader["printTitle"].ToString());
}
catch
{
printTitle = 0;
}
if (printTitle >= 1)
{
file.printTitle = true;
}
else
{
file.printTitle = false;
}
try
{
printFreeSpace = int.Parse(reader["printFreeSpace"].ToString());
}
catch
{
printFreeSpace = 0;
}
//.........这里部分代码省略.........
示例14: writeFile
public bool writeFile(BingoWordFile file)
{
if (file.filePath.EndsWith(".bwf") == false)
{
file.filePath += ".bwf";
} //Bingo word file
Cursor.Current = Cursors.WaitCursor;
try
{
using (DbConnection configdb = new SQLiteConnection("Data Source=" + file.filePath))
{
using (DbCommand cmd = configdb.CreateCommand())
{
//open the connection
configdb.Open();
try
{
cmd.CommandText = "DROP TABLE info";
cmd.ExecuteNonQuery();
}
catch
{
}
try
{
cmd.CommandText = "DROP TABLE wordlist";
cmd.ExecuteNonQuery();
}
catch
{
}
try
{
int freeSpacePrint = file.printFreeSpaceToInt();
int titlePrint = file.printTitleToInt();
cmd.CommandText = "CREATE TABLE info (cardtitletext TEXT, cardfreespacetext TEXT, cardrowsize TEXT, cardcolsize TEXT, numcardstoprint TEXT, numcardsperpage TEXT, printTitle TEXT, printFreeSpace TEXT)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT into info VALUES ('" + file.cardTitleText + "','" + file.cardFreeSpaceText + "','" + file.cardRowSize.ToString() + "', '" + file.cardColSize.ToString() + "', '" + file.numCardsToPrint.ToString() + "', '" + file.numCardsPerPage.ToString() + "', '" + titlePrint.ToString() + "', '" + freeSpacePrint.ToString() + "')";
cmd.ExecuteNonQuery();
}
catch
{
}
try
{
cmd.CommandText = "CREATE TABLE wordlist (word TEXT)";
cmd.ExecuteNonQuery();
foreach (string item in file.wordList)
{
cmd.CommandText = "INSERT into wordlist VALUES ('" + item + "')";
cmd.ExecuteNonQuery();
}
}
catch
{
}
try
{
configdb.Close();
}
catch
{
}
}
}
}
catch
{
}
finally
{
Cursor.Current = Cursors.Default;
}
return (true);
}示例15: ExcuteSql
public static bool ExcuteSql(SQLiteConnection conn, string sql)
{
bool ret = false;
try
{
var cmd = conn.CreateCommand(sql);
var result = cmd.ExecuteNonQuery();
ret = true;
}
catch (Exception ex)
{
Logger.Log(LogType.Exception, sql, ex);
}
return ret;
}