C# SQLite.SQLiteConnection类代码示例

        public void TestCaseSensitiveKeyColumn()
        {
            var path = Path.GetTempFileName();
            try
            {
                var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path);
                sqlite.Open();
                var cmd = sqlite.CreateCommand();
                cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                sqlite.Close();
                sqlite.Dispose();
                using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID"))
                {
                    var ext = new Envelope();
                    var ds = new SharpMap.Data.FeatureDataSet();
                    sq.ExecuteIntersectionQuery(ext, ds);
                    NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count);
                }

            }
            catch (Exception ex)
            {
                Assert.Fail("Got exception, should not happen");

            }
            finally
            {
                File.Delete(path);
            }
        }

        public static void FindOpcodes(string query)
        {
            var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderByDescending(t => t);

            foreach (var file in files)
            {
                using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
                {
                    con.Open();
                    using (var sqlcommand = con.CreateCommand())
                    {
                        sqlcommand.CommandText = "select count(*) from packets where opcode in (" + query + ")";
                        var reader = sqlcommand.ExecuteReader();

                        while (reader.Read())
                        {
                            var found = reader.GetInt32(0);
                            if (found > 0)
                            {
                                System.Diagnostics.Debug.WriteLine(file + "\t" + found);
                            }
                            break;
                        }
                    }

                    con.Close();
                }
            }
        }

        public void UpdateDatabaseWithArteProps( string ConnectionString )
        {
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString );
            conn.Open();
            System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
            System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn );
            command.Transaction = transaction;
            foreach ( var a in ArteList ) {
                string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
                Console.WriteLine( UpdateNames );
                command.CommandText = UpdateNames;
                command.ExecuteNonQuery();
                string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\"";
                Console.WriteLine( UpdateDescs );
                command.CommandText = UpdateDescs;
                command.ExecuteNonQuery();

                if ( a.Type == Arte.ArteType.Generic ) {
                    string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
                    Console.WriteLine( UpdateStatus );
                    command.CommandText = UpdateStatus;
                    command.ExecuteNonQuery();
                }
            }
            command.Dispose();
            transaction.Commit();
            conn.Close();
            conn.Dispose();
        }

        public static IDbConnection CreateConnection()
        {
            var connection = new System.Data.SQLite.SQLiteConnection(GetConnectionString());
            connection.Open();

            return connection;
        }

        public List<String> getLastProjects()
        {
            List<String> list = new List<String>();
            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();

                    com.CommandText = "Select * FROM LASTPROJECTS";

                    using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["address"]);

                           list.Add(reader["address"].ToString());
                        }
                    }
                    con.Close();
                }
            }
            return list;
        }

        public static int Execute( List<string> args )
        {
            // 0xCB20

            if ( args.Count != 2 ) {
                Console.WriteLine( "Generates a scenario db for use in Tales.Vesperia.Website from a MAPLIST.DAT." );
                Console.WriteLine( "Usage: maplist.dat scenario.db" );
                return -1;
            }

            String maplistFilename = args[0];
            string connStr = "Data Source=" + args[1];

            using ( var conn = new System.Data.SQLite.SQLiteConnection( connStr ) ) {
                conn.Open();
                using ( var ta = conn.BeginTransaction() ) {
                    SqliteUtil.Update( ta, "CREATE TABLE descriptions( filename TEXT PRIMARY KEY, shortdesc TEXT, desc TEXT )" );
                    int i = 0;
                    foreach ( MapName m in new MapList( System.IO.File.ReadAllBytes( maplistFilename ) ).MapNames ) {
                        Console.WriteLine( i + " = " + m.ToString() );
                        List<string> p = new List<string>();
                        p.Add( "VScenario" + i );
                        p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
                        p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
                        SqliteUtil.Update( ta, "INSERT INTO descriptions ( filename, shortdesc, desc ) VALUES ( ?, ?, ? )", p );
                        ++i;
                    }
                    ta.Commit();
                }
                conn.Close();
            }

            return 0;
        }

        private void Rptpordonador_Load(object sender, EventArgs e)
        {
            CrystalReport2 objRpt = new CrystalReport2();
            string appPath = Path.GetDirectoryName(Application.ExecutablePath);
            String ConnStr = @"Data Source=" + appPath + @"\dbcar.s3db ;Version=3;";

            System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr);

            String Query1 = "SELECT * FROM Donaciones Where Nombre = '"+nombre1+"' AND Edad = '"+edad1+"'";

            System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr);

            DataSet Ds = new DataSet();

            // here my_dt is the name of the DataTable which we
            // created in the designer view.
            adapter.Fill(Ds, "my_dt");

            // Setting data source of our report object
            objRpt.SetDataSource(Ds);

            // Binding the crystalReportViewer with our report object.
            this.crystalReportViewer1.ReportSource = objRpt;

            objRpt.Refresh();
        }

        // "x:\util\android-sdk-windows\platform-tools\adb.exe"  tcpip 5555
        // restarting in TCP mode port: 5555

        // "x:\util\android-sdk-windows\platform-tools\adb.exe" connect 192.168.1.126:5555
        // connected to 192.168.1.126:5555

        static ApplicationWebService()
        {
            // will this work for android?

            Console.WriteLine("ApplicationWebService cctor " + new { Environment.CurrentDirectory });

            // ApplicationWebService cctor { CurrentDirectory = W:\staging.net.debug }

            #region setup:QueryExpressionBuilder.WithConnection
            //if (ScriptCoreLib.Query.Experimental.QueryExpressionBuilder.WithConnection == null)

            // override the default
            ScriptCoreLib.Query.Experimental.QueryExpressionBuilder.WithConnection =
                y =>
                {
                    // relative path?
                    var DataSource = "file:server1.sqlite";

                    // nuget xsqlite?
                    var cc = new System.Data.SQLite.SQLiteConnection(new System.Data.SQLite.SQLiteConnectionStringBuilder
                    {
                        DataSource = DataSource,
                        Version = 3
                    }.ConnectionString);
                    cc.Open();
                    y(cc);
                    cc.Dispose();
                };
            #endregion
        }

        public static IDbConnection GetDatabaseConnection(FileInfo database)
        {
            if (database == null) {
            throw new ArgumentNullException("database");
              }
              if (database.Exists && database.IsReadOnly) {
            throw new ArgumentException("Database file is read only", "database");
              }
              var cs = string.Format("Uri=file:{0}", database.FullName);
              if (Type.GetType("Mono.Runtime") == null) {
            var rv = new System.Data.SQLite.SQLiteConnection(cs);
            if (rv == null) {
              throw new ArgumentException("no connection");
            }
            rv.Open();
            return rv;
              }

              Assembly monoSqlite;
              try {
            monoSqlite = Assembly.Load("Mono.Data.Sqlite, Version=4.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756");
              }
              catch (Exception) {
            monoSqlite = Assembly.Load("Mono.Data.Sqlite, Version=2.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756");
              }
              var dbconn = monoSqlite.GetType("Mono.Data.Sqlite.SqliteConnection");
              var ctor = dbconn.GetConstructor(new[] { typeof(string) });
              var mrv = ctor.Invoke(new[] { cs }) as IDbConnection;
              if (mrv == null) {
            throw new ArgumentException("no connection");
              }
              mrv.Open();
              return mrv;
        }

    private static IDbConnection GetDatabaseConnectionSDS(string cs)
    {
      var rv = new System.Data.SQLite.SQLiteConnection(cs);
      if (rv == null) {
        throw new ArgumentException("no connection");
      }
      rv.Open();

      try {
        rv.SetChunkSize(GROW_SIZE);
      }
      catch (Exception ex) {
        log4net.LogManager.GetLogger(typeof(Sqlite)).Error(
          "Failed to sqlite control", ex);
      }

      if (clearPool == null) {
        clearPool = conn =>
        {
          System.Data.SQLite.SQLiteConnection.ClearPool(
            conn as System.Data.SQLite.SQLiteConnection);
        };
      }
      return rv;
    }

        public DatabaseManager()
        {
            string createTableQuery = @"CREATE TABLE IF NOT EXISTS LASTPROJECTS (
                          [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                          [address] VARCHAR(2048)  NULL
                          )";
            string path = Path.
                GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            path = path + @"\bin\Debug\databaseFile.db3";

            if(!File.Exists (path))
                System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");

            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();
                    com.CommandText = createTableQuery;
                    com.ExecuteNonQuery();
                    con.Close();
                }
            }
            Console.WriteLine("ALLO");
        }

 public DbConnection Db()
 {
     if (!System.IO.File.Exists(File)) return null;
     var cn = new System.Data.SQLite.SQLiteConnection(string.Format("Data Source={0};Version=3;", File));
     cn.Open();
     return cn;
 }

        static void Main1(string[] args)
        {
            string datasource = "test.db";

            System.Data.SQLite.SQLiteConnection.CreateFile(datasource);

            //连接数据库

            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();

            System.Data.SQLite.SQLiteConnectionStringBuilder connstr =

                new System.Data.SQLite.SQLiteConnectionStringBuilder();

            connstr.DataSource = datasource;

            connstr.Password = "admin";//设置密码，SQLite ADO.NET实现了数据库密码保护
            Console.WriteLine(connstr.ToString());
            conn.ConnectionString = connstr.ToString();

            conn.Open();

            //创建表

            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();

            string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";

            cmd.CommandText = sql;

            cmd.Connection = conn;

            cmd.ExecuteNonQuery();

            //插入数据

            sql = "INSERT INTO test VALUES('dotnetthink','mypassword')";

            cmd.CommandText = sql;

            cmd.ExecuteNonQuery();

            //取出数据

            sql = "SELECT * FROM test";

            cmd.CommandText = sql;

            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();

            StringBuilder sb = new StringBuilder();

            while (reader.Read())
            {
                sb.Append("username:").Append(reader.GetString(0)).Append("\n").Append("password:").Append(reader.GetString(1));

            }
            Console.WriteLine(sb.ToString());
        }

 internal static System.Data.IDbConnection SQLiteConn()
 {
     string strConn = System.Windows.Forms.Application.StartupPath + "//Database.db";
     System.Data.SQLite.SQLiteConnectionStringBuilder strBuild = new System.Data.SQLite.SQLiteConnectionStringBuilder();
     strBuild.DataSource = strConn;
     System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strBuild.ToString());
     return conn;
 }

        public void Command()
        {
            using (var conn = new System.Data.SQLite.SQLiteConnection("Data Source=:memory:"))
            {
                conn.Open();

                using (var command = new LoggingCommand(new System.Data.SQLite.SQLiteCommand()))
                {
                    command.CommandText = "select 1";
                    Assert.Throws<InvalidOperationException>(() => command.ExecuteReader());
                    Assert.Equal(LogLevel.Error, logger.Last.Level);
                    Assert.Equal(@"クエリ実行に失敗
            --- SQLiteCommand ---
            CommandTimeout   = 30
            CommandType      = Text
            UpdatedRowSource = None
            --- CommandText ---
            select 1
            --- Parameters ---
            ", logger.Last.Message);

                    command.Connection = conn;
                    using (var reader = command.ExecuteReader())
                    {
                        Assert.Equal(LogLevel.Trace, logger.Last.Level);
                        Assert.Matches(@"ExecuteReader. 実行時間:\d\d:\d\d:\d\d\.\d+
            --- CommandText ---
            select 1
            --- Parameters ---
            ", logger.Last.Message);

                        Assert.True(reader.Read());
                        Assert.Equal((long)1, reader[0]);
                    }

                    Assert.Equal((long)1, command.ExecuteScalar());
                    Assert.Matches(@"ExecuteScalar. result=1 実行時間:\d\d:\d\d:\d\d\.\d+
            --- CommandText ---
            select 1
            --- Parameters ---
            ", logger.Last.Message);

                    command.CommandText = "select @p";
                    command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("p", "abc"));
                    Assert.Equal("abc", command.ExecuteScalar());
                    Assert.Matches(@"ExecuteScalar. result=abc 実行時間:\d\d:\d\d:\d\d\.\d+
            --- CommandText ---
            select @p
            --- Parameters ---
            p\(String\)=abc
            ", logger.Last.Message);

                    Assert.IsType<System.Data.SQLite.SQLiteParameter>(command.CreateParameter());
                }
                Assert.Equal("コマンドが破棄(Dispose)されました。", logger.Last.Message);
            }
        }