/// <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."); }