C# SQLite.SQLiteConnection类代码示例

        /// <summary>
        /// OnDelDir delete dir from DB if its there
        /// </summary>
        /// <param name="args">input arguments</param>
        /// <returns><c>true</c> if dir was deleted</returns>
        public static bool CheckDupeDelDir(string[] args)
        {
            Log.Info("DelDir.CheckDupeDelDir");

            if (!SkipDupe.CheckSkipDupe(args))
            {
                Log.Info("DelDir.CheckDupeDelDir");
                return true;
            }

            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;

            string dbName = ConfigReader.GetConfig("dbDupeDir");
            string db = String.Format(CultureInfo.InvariantCulture,
                @"Data Source={0};Version=3;New=False;Compress=True;", dbName);
            sqlite_conn = new SQLiteConnection(db);
            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();

            string dbCommand = String.Format(CultureInfo.InvariantCulture,
                @"DELETE FROM dirDupe WHERE ReleaseName = '{0}'", Global.dupe_name);

            Log.InfoFormat("{0}", dbCommand);

            sqlite_cmd.CommandText = dbCommand;
            sqlite_cmd.ExecuteNonQuery();

            sqlite_conn.Close();

            Log.Info("DelDir.CheckDupeDelDir");

            return true;
        }

        public DataRow[] drExecute(string FileData, string sSql)
        {
            DataRow[] datarows = null;
            SQLiteDataAdapter dataadapter = null;
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            try
            {
                using (SQLiteConnection con = new SQLiteConnection())
                {
                    con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=False;";
                    con.Open();
                    using (SQLiteCommand sqlCommand = con.CreateCommand())
                    {
                        dataadapter = new SQLiteDataAdapter(sSql, con);
                        dataset.Reset();
                        dataadapter.Fill(dataset);
                        datatable = dataset.Tables[0];
                        datarows = datatable.Select();
                        k = datarows.Count();
                    }
                    con.Close();
                }
            }
            catch(Exception ex)
            {
            //    throw ex;
                datarows = null;
            }
            return datarows;

        }

 /** public void createUc()  - create user controls (display reminders)         * 
  */
 public void createUc() 
 {
     int i = 0;
     UserControl1[] uc = new UserControl1[200];
     SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
     new_con.Open();
     SQLiteCommand get = new SQLiteCommand("SELECT * FROM  reminder LIMIT 0 , 30", new_con);
     String header;
     SQLiteDataReader reader;
     reader = get.ExecuteReader();
     while (reader.Read())
     {
         //data[i,0] = new ArrayList();
         string[] str = new string[3];
         str[0]=reader[0].ToString();
         str[1]=DateTime.Parse(reader[1].ToString()).ToShortDateString();
         str[2]=DateTime.Parse(reader[5].ToString()).ToShortTimeString();
         reminderList.Add(str);
         header = String.Format("{1,-20}   {0,5}", reader[2].ToString(), DateTime.Parse(reader[1].ToString()).ToShortDateString());
         uc[i] = new UserControl1();
         uc[i].setContent(Convert.ToInt16(reader[0].ToString()), reader[3].ToString(), reader[4].ToString(), DateTime.Parse(reader[5].ToString()).ToShortTimeString() , header);
         WrapPanel1.Children.Add(uc[i]);
         i++;   
     }
     reminderlistarray = reminderList.ToArray();
 }

 // Functie pentru a sterge un capitol din baza de date
 public static void delete_capitole(string decizie, string title)
 {
     SQLiteConnection sqlite_conn;
     SQLiteCommand sqlite_cmd;
     string database_title = get_databaseName_tableName.get_databaseName(decizie, "capitole");
     string table_title = get_databaseName_tableName.get_tableName(decizie, "capitole");
        /* if (decizie == "info") { database_title = "Capitole.db"; table_title = "capitole";}
     if (decizie == "mate") { database_title = "Capitole_mate.db"; table_title = "capitole_mate"; }
     if (decizie == "bio") { database_title = "Capitole_bio.db"; table_title = "capitole_bio"; }*/
     try
     {
         sqlite_conn = new SQLiteConnection("Data Source=" + database_title + ";Version=3;New=False;Compress=True;");
         sqlite_conn.Open();
         sqlite_cmd = sqlite_conn.CreateCommand();
         sqlite_cmd.CommandText = "delete from " + table_title + " where titlu = @titlu_capitol";
         SQLiteParameter parameter = new SQLiteParameter("@titlu_capitol", DbType.String);
         parameter.Value = title;
         sqlite_cmd.Parameters.Add(parameter);
         sqlite_cmd.ExecuteNonQuery();
         MessageBox.Show("Lectia a fost stearsa cu succes!");
         sqlite_conn.Close();
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
 }

        public MainWindow()
        {
            InitializeComponent();
            AddDinValues();
            AddPCBValues();
            AddRKValues();

            // create a new database connection:
            sqlite_conn = new SQLiteConnection("Data Source=ICTSupportInventory.db;Version=3;New=False;Compress=True;");

            // open the table for connection
            sqlite_conn.Open();

            // create a new SQL command:
            sqlite_cmd = sqlite_conn.CreateCommand();

            // create a new table to work with
            sqlite_cmd.CommandText = "CREATE TABLE Items (ProductType varchar(100), SerialNumber varchar(100), Location varchar(100), Availability varchar(50), Owner varchar(100), ETR varchar(100), Comments varchar(100));";

            // close the connection
            sqlite_conn.Close();

            // get values to populate all items 
            PopulateAllLists();
        }

 public SQLiteCommand(String pCmdText, SQLiteConnection pConn)
 {
     if (pConn == null)
         throw new ArgumentNullException();
     CommandText = pCmdText;
     Connection = pConn;
 }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            // Let the SQLiteCommand object know our SQL-Query:
            sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;Compress=True;");
            sqlite_conn.Open();
            //UPDATE People set [email protected],stxtLname = @stxtLName,
            SQLiteCommand sqlite_cmd = new SQLiteCommand(@"Update ProgramConfig Set sPassword = @password, sEmail = @email, sSchoolName = @name,
                            sAddress = @address, sCity = @city, sState = @state, sZip = @zip, sLatitude = @latitude,
                            sLongitude = @longitude, sImageFile = @ImageFile Where id = 1", sqlite_conn);

            sqlite_cmd.Parameters.Add("@password", SqlDbType.Text).Value = parentForm.school.password;
            sqlite_cmd.Parameters.Add("@email", SqlDbType.Text).Value = parentForm.school.email;
            sqlite_cmd.Parameters.Add("@name", SqlDbType.Text).Value = parentForm.school.name;
            sqlite_cmd.Parameters.Add("@address", SqlDbType.Text).Value = parentForm.school.address;
            sqlite_cmd.Parameters.Add("@city", SqlDbType.Text).Value = parentForm.school.city;
            sqlite_cmd.Parameters.Add("@state", SqlDbType.Text).Value = parentForm.school.state;
            sqlite_cmd.Parameters.Add("@zip", SqlDbType.Text).Value = parentForm.school.zip;
            sqlite_cmd.Parameters.Add("@latitude", SqlDbType.Text).Value = parentForm.school.latitude;
            sqlite_cmd.Parameters.Add("@longitude", SqlDbType.Text).Value = parentForm.school.longitude;
            sqlite_cmd.Parameters.Add("@ImageFile", SqlDbType.Text).Value = parentForm.school.ImageFile;
            sqlite_cmd.CommandType = CommandType.Text;

            try
            {
                int i = sqlite_cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            parentForm.RefreshLogo(parentForm.school.ImageFile);
            this.Close();
        }

 public SearchForUser(int iRequestType, PeopleManagement parent, SQLiteConnection sql_conn)
 {
     InitializeComponent();
     sqlite_conn = sql_conn;
     LoadUserSelect();
     frmParent = parent;
 }

 internal SQLiteTransaction(SQLiteConnection pConn)
 {
     mpConn = pConn;
     if (pConn == null)
         throw new ArgumentNullException();
     mpConn.sqlite.exec("begin transaction");
 }

        public int iExecuteNonQuery(string FileData, string sSql, int where)
        {
            int n = 0;
            //try
            //{
                using (SQLiteConnection con = new SQLiteConnection())
                {
                    if (where == 0)
                    {
                        con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=True;";
                    }
                    else
                    {
                        con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=False;";
                    }
                    con.Open();
                    using (SQLiteCommand sqlCommand = con.CreateCommand())
                    {
                        sqlCommand.CommandText = sSql;
                        n = sqlCommand.ExecuteNonQuery();
                    }
                    con.Close();
                }
            //}
            //catch
            //{
            //    n = 0;
            //}
            return n;

        }

        /// <summary>
        /// metodo cria ou acessa banco de dados sqlite carregar configs
        /// </summary>
        public void CreateOrAccessDataBase()
        {
            try
            {
                string file = AppDomain.CurrentDomain.BaseDirectory +
                              "Database\\Shamia.db";
                if (!ExistDataBase())
                {
                    Connection = new SQLiteConnection("Data Source=" + file + ";Version=3;New=True;Compress=True");
                    Connection.Open();
                    string[] config = new string[] {string.Empty, string.Empty, string.Empty};
                    // create table(s)
                    // config   => contais configurations
                    config[0] = @"create table config (port interger(4)" +
                                @",server varchar(30) primary key" +
                                @",language varchar(20))";
                    // channels => contais channels
                    config[1] = @"create table channels (channel varchar(30) primary key)";
                    // user(s)  => my login accont user(s) used login auth SSL
                    config[2] = @"create table users (nick varchar(20) primary key" +
                                @",password varchar(50))";

                    foreach (string i in config)
                    {
                        SQLiteCommand c = new SQLiteCommand(i, Connection);
                        c.ExecuteNonQuery();
                    }
                    IsDatabase = true;
                }
                else
                {
                    Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
                                                      ";New=False;Compress=True");
                    Connection.Open();
                    IsDatabase = true;

                    // select database to list all channels and servers
                    C = Connection.CreateCommand();
                    C.CommandText = @"SELECT channel FROM channels";
                    DataReader = C.ExecuteReader();
                    while (DataReader.Read())
                    {
                        MainWindow.Configuration.Channels.Add(new TemplateChannels
                        {
                            Channels = DataReader["channel"].ToString()
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                MyDelegates.OnDebugMessageCallBack(ex.ToString());
                IsDatabase = false;
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            }
        }

        public static void UnDupeDir(string[] args)
        {
            Log.Info("UnDupe.UnDupeDir");

            Global.undupe_dir = args[1];
            Log.InfoFormat("Un Dupe String = '{0}'", Global.undupe_dir);

            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;
            SQLiteDataReader sqlite_datareader;

            string dbName = ConfigReader.GetConfig("dbDupeDir");
            string db = String.Format(CultureInfo.InvariantCulture,
                @"Data Source={0};Version=3;New=False;Compress=True;", dbName);

            sqlite_conn = new SQLiteConnection(db);
            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();

            string cmdText =
                String.Format(CultureInfo.InvariantCulture,
                "SELECT COUNT(*) FROM dirDupe WHERE ReleaseName = '{0}'", Global.undupe_dir);
            sqlite_cmd.CommandText = cmdText;
            sqlite_datareader=sqlite_cmd.ExecuteReader();

            int count = 0;

            while (sqlite_datareader.Read())
            {
                count = Int32.Parse(sqlite_datareader.GetValue(0).ToString());
            }

            sqlite_datareader.Close();

            if ( count > 0 )
            {
                string deleteCommand =
                    String.Format(CultureInfo.InvariantCulture,
                    @"DELETE FROM dirDupe WHERE ReleaseName = '{0}'", Global.undupe_dir);

                Log.InfoFormat("{0}", deleteCommand);

                sqlite_cmd.CommandText = deleteCommand;
                sqlite_cmd.ExecuteNonQuery();

                Console.WriteLine(
                    Format.FormatStr1ng(ConfigReader.GetConfig("msgUnDupes_ok"), 0, null));

            }
            else
            {
                Console.WriteLine(
                    Format.FormatStr1ng(ConfigReader.GetConfig("msgUnDupes_fail"), 0, null));
            }

            sqlite_conn.Close();

            Log.Info("UnDupe.UnDupeDir");
        }

        private void Adaugare_Intrebare_Load(object sender, EventArgs e)
        {
            btn_home = home.Size;
            btn_adauga_intrebarea = adauga_intrebarea.Size;
            if (decizie == "info")
            {
                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;
                sqlite_conn = new SQLiteConnection("Data Source=Capitole.db;Version=3;New=False;Compress=True;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM capitole";
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    String myr = sqlite_datareader.GetString(0);
                    checkedListBox1.Items.Add(myr);
                }
                sqlite_conn.Close();
            }
            if (decizie == "mate")
            {
                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;
                sqlite_conn = new SQLiteConnection("Data Source=Capitole_mate.db;Version=3;New=False;Compress=True;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM capitole_mate";
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    String myr = sqlite_datareader.GetString(1);
                    checkedListBox1.Items.Add(myr);
                }
                sqlite_conn.Close();
            }
            if (decizie == "bio")
            {
                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;
                sqlite_conn = new SQLiteConnection("Data Source=Capitole_bio.db;Version=3;New=False;Compress=True;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM capitole_bio";
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    String myr = sqlite_datareader.GetString(1);
                    checkedListBox1.Items.Add(myr);
                }
                sqlite_conn.Close();

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // [snip] - As C# is purely object-oriented the following lines must be put into a class:

            // We use these three SQLite objects:
            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;
            SQLiteDataReader sqlite_datareader;

            // create a new database connection:
            sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

            // open the connection:
            sqlite_conn.Open();

            // create a new SQL command:
            sqlite_cmd = sqlite_conn.CreateCommand();

            // Let the SQLiteCommand object know our SQL-Query:
            sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";

            // Now lets execute the SQL ;D
            sqlite_cmd.ExecuteNonQuery();

            // Lets insert something into our new table:
            sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Test Text 1');";

            // And execute this again ;D
            sqlite_cmd.ExecuteNonQuery();

            // ...and inserting another line:
            sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (2, 'Test Text 2');";

            // And execute this again ;D
            sqlite_cmd.ExecuteNonQuery();

            // But how do we read something out of our table ?
            // First lets build a SQL-Query again:
            sqlite_cmd.CommandText = "SELECT * FROM test";

            // Now the SQLiteCommand object can give us a DataReader-Object:
            sqlite_datareader = sqlite_cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                // Print out the content of the text field:
                // System.Console.WriteLine(sqlite_datareader["text"]);
                string a = sqlite_datareader.GetString(0);
                MessageBox.Show(a);
            }

            // We are ready, now lets cleanup and close our connection:
            sqlite_conn.Close();
        }

        public override IDbConnection CreateConn(string connString)
        {
            SQLiteConnection conn= new SQLiteConnection(connString);
               // SQLiteConnection conn = new SQLiteConnection();
               // SQLiteConnectionStringBuilder connsb = new SQLiteConnectionStringBuilder();
               // connsb.DataSource = dbName;
               // connsb.Password = "";
               // conn.ConnectionString = connsb.ToString();

            return conn;
        }