/// <summary>
/// Initializes a new instance of the <see cref="SqliteCommandBuilder"/> class.
/// </summary>
/// <param name="adapter">The adapter.</param>
public MySqliteCommandBuilder(SqliteDataAdapter adapter)
: base()
{
DataAdapter = adapter;
this.QuotePrefix = "\"";
this.QuoteSuffix = "\"";
}
DataTable GetSchemaDataTable(SqliteCommand cmd, string[] restrictionValues)
{
if (restrictionValues != null && cmd.Parameters.Count > 0)
{
for (int i = 0; i < restrictionValues.Length; i++)
{
cmd.Parameters[i].Value = restrictionValues[i];
}
}
SqliteDataAdapter adapter = new SqliteDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
DataTable GetSchemaDataTable( SqliteCommand cmd, string[] restrictionValues )
{
if ( restrictionValues != null && cmd.Parameters.Count > 0 )
{
for ( int i = 0; i < restrictionValues.Length; i++ )
cmd.Parameters[i].Value = restrictionValues[i];
}
SqliteDataAdapter adapter = new SqliteDataAdapter( cmd );
DataTable dt = new DataTable();
adapter.Fill( dt );
return dt;
}
public static DataTable ExecuteTable(string commandText, object[] paramList)
{
SqliteConnection cn = new SqliteConnection(connectString);
string sqlQuery = commandText;
if (paramList != null)
{
sqlQuery = string.Format(commandText, paramList);
}
SqliteCommand command = new SqliteCommand(sqlQuery, cn);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
return dataTable;
}
public void Test2()
{
Console.WriteLine("Test2 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("NAME"));
Console.WriteLine(" NAME: {0} = {1}", s, s == "ä¸æ–‡");
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
public void Test1()
{
Console.WriteLine("Test1 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s = reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}