/** 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();
 }

        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 List<Drop> GetDrops(int mobId)
        {
            // create a new SQL command:
            sqlite_cmd = sqlite_conn.CreateCommand();

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

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

            List<Drop> droplist = new List<Drop>(10);

            // 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
            {
                int itemId = Convert.ToInt32(sqlite_datareader["itemId"]);
                int min = Convert.ToInt32(sqlite_datareader["min"]);
                int max = Convert.ToInt32(sqlite_datareader["max"]);
                int category = Convert.ToInt32(sqlite_datareader["category"]);
                int chance = Convert.ToInt32(sqlite_datareader["chance"]);

                droplist.Add(new Drop(mobId, itemId, min, max, category, chance));
            }
            return droplist;
        }

 public bool GetSchool()
 {
     bool bReturn = false;
     sqlite_cmd = new SQLiteCommand("Select * from ProgramConfig where id = 1", sqlite_conn);
     // 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
     {
         Console.WriteLine(sqlite_datareader["sLatitude"].ToString());
         school = new School(sqlite_datareader["sPassword"].ToString(), sqlite_datareader["sEmail"].ToString(), sqlite_datareader["sSchoolName"].ToString(),
             sqlite_datareader["sAddress"].ToString(), sqlite_datareader["sCity"].ToString(), sqlite_datareader["sState"].ToString(), sqlite_datareader["sZip"].ToString(),
             sqlite_datareader["sLatitude"].ToString(), sqlite_datareader["sLongitude"].ToString(), sqlite_datareader["sImageFile"].ToString());
         try
         {
             string sExistingDatabaseVersion = sqlite_datareader["sDatabaseVersion"].ToString();
             if(OldDBVersion(sExistingDatabaseVersion,DatabaseVersion))
             {
                 UpgradeDB(sExistingDatabaseVersion, DatabaseVersion);
             }
         }
         catch (Exception e)
         {
             Console.WriteLine(e.ToString());
             Console.WriteLine("No database version available.  Need to upgrade to DB Version 1.0 after backing things up.");
         }
         bReturn = true;
     }
     return bReturn;
 }

 internal SQLiteDataReader(SQLiteCommand pCmd, CommandBehavior cmdBehavior)
 {
     if (pCmd == null)
         throw new ArgumentNullException();
     if( pCmd.GetSQLStatementCount() <= 0 )
         throw new ArgumentException("CommandText doesn't contain any SQL statements in SQLiteCommand");
     mpCmd = pCmd;
     mCmdBehavior = cmdBehavior;
 }

        public OneSQLStatement( SQLiteCommand pCmd, String cmdText, ArrayList paramNames )
        {
            mCmd = pCmd;
            mCmdText = cmdText;
            mpParamNames = paramNames;
            if (mCmd == null)
                throw new ArgumentNullException("pCmd");

            if (mCmdText == null)
                throw new ArgumentNullException("cmdText");

            if (mCmdText.Length == 0)
                throw new ArgumentException("The command text must be non-empty");
        }

        void LoadUserSelect()
        {
            comboUser.Items.Clear();
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People", sqlite_conn);

            // 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
            {
                //Console.WriteLine(String.Format("{0}, {1}", sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
                comboUser.Items.Add(String.Format("{0}: {1}, {2}", sqlite_datareader["id"], sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
            }
        }

        /// <summary>
        /// Simplify the creation of a SQLite command object by allowing
        /// a CommandType and Command Text to be provided
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SQLiteCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
        /// </remarks>
        /// <param name="connection">A valid SQLiteConnection object</param>
        /// <param name="commandType">CommandType (TableDirect, Text)</param>
        /// <param name="commandText">CommandText</param>
        /// <returns>A valid SQLiteCommand object</returns>
        public static SQLiteCommand CreateCommand(SQLiteConnection connection, CommandType commandType, string commandText )
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");

            // If we receive parameter values, we need to figure out where they go
            if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );

            // Create a SQLiteCommand
            SQLiteCommand cmd = new SQLiteCommand(commandText, connection );
            cmd.CommandType = CommandType.Text ;

            return cmd;
        }

        public DataTable Insert(string queryString)
        {
            try
            {
                lock(DataBase._conn)
                {
                SQLiteCommand Cmd = new SQLiteCommand();
                Cmd = DataBase._conn.CreateCommand();
                Cmd.CommandText = queryString;
                Cmd.CommandType=CommandType.Text ;
                Cmd.ExecuteNonQuery();

                }
                return null;
            }catch(Exception e){
                Debug.WriteLine("DataBase Insert Problem: "+ e.Message);
                return null;
            }
        }

 private void btnAddGuardian_Click(object sender, EventArgs e)
 {
     SearchForUser GetContact = new SearchForUser(GUARDIAN_ASSOCIATION_REQUEST, this, sqlite_conn);
     GetContact.ShowDialog();
     Console.Write("{0}{1}", iUser, sRelationship);
     SQLiteCommand sqlite_cmd = new SQLiteCommand("INSERT INTO Guardians (id,iStudent,iPerson,sRelationship) VALUES (@id,@iStudent,@iPerson,@sRelationship)", sqlite_conn);
     sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = null;
     sqlite_cmd.Parameters.Add("@iStudent", SqlDbType.Int).Value = Int32.Parse(txtID.Text);
     sqlite_cmd.Parameters.Add("@iPerson", SqlDbType.Int).Value = iUser;
     sqlite_cmd.Parameters.Add("@sRelationship", SqlDbType.Text).Value = sRelationship;
     try
     {
         sqlite_cmd.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     LoadGuardians();
 }

        /** private void ButtonYes_Click(object sender, System.Windows.RoutedEventArgs e)
         * delete reminder
         */
		private void ButtonYes_Click(object sender, System.Windows.RoutedEventArgs e)
		{
            SQLiteConnection  new_con = new  SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
            new_con.Open();
            if  (delete_status == 1)
            {  
                SQLiteCommand  get = new  SQLiteCommand("DELETE FROM reminder WHERE rid ='"  + Convert.ToInt32(id) + "'", new_con);
                get.ExecuteNonQuery();
                MainWindowStart  mainWindow = MainWindowStart.Instance;
                mainWindow.mainWindowUpdate();
            }
            else
            {
                SQLiteCommand  get = new  SQLiteCommand("DELETE FROM contact_detail WHERE cid ='"  + Convert.ToInt32(id) + "'", new_con);
                 get.ExecuteNonQuery();
                MainWindowStart  mainWindow = MainWindowStart.Instance;
                mainWindow.mainWindowUpdateContact();
            }
            new_con.Close();
             this.Close();
		}

        static FmMain()
        {
            connection = new SQLiteConnection();
            command = new SQLiteCommand();

            String cs = "";
            cs = cs + String.Format("Data Source={0};", IDE.LibPath + "ASEExpertVS2005.CodeKeeper.db");
            cs = cs + String.Format("Version=3;");
            bool isNew = !File.Exists(IDE.LibPath + "ASEExpertVS2005.CodeKeeper.db");
            if (isNew)
                cs = cs + String.Format("New=True;");
            cs = cs + String.Format("UTF8Encoding=True;");
            cs = cs + String.Format(";");

            connection.ConnectionString = cs;
            try
            {
                connection.Open();

                command.Connection = connection;

                if (isNew)
                {
                    ExecuteNonQuery("CREATE TABLE groups (parentid INTEGER, name TEXT)");
                    ExecuteNonQuery("CREATE TABLE code (groupid INTEGER, name TEXT, code TEXT, langid INTEGER)");

                    ExecuteNonQuery("INSERT INTO groups (parentid , name) VALUES (0, 'General')");
                    ExecuteNonQuery("INSERT INTO groups (parentid , name) VALUES (0, 'Forms')");
                    ExecuteNonQuery("INSERT INTO groups (parentid , name) VALUES (0, 'IO')");
                    ExecuteNonQuery("INSERT INTO groups (parentid , name) VALUES (0, 'NET')");
                }
            }
            catch (Exception exc)
            {
                IDE.Debug("", exc);
            }
        }

        public DataTable Select(string queryString)
        {
            sw.Write("test"+queryString);
            try
            {
                lock(DataBase._conn)
                {
                SQLiteCommand Cmd = new SQLiteCommand();
                SQLiteDataAdapter da = new SQLiteDataAdapter(queryString, DataBase._conn);
                DataTable dt = new DataTable();
                if(da != null)
                {
                    da.Fill(dt);
                    return dt;
                }else{
                    return null;
                }
                }

            }catch(Exception e){
                Debug.WriteLine("DataBase Select Problem: "+ e.Message);
                return null;
            }
        }

 private void btnAddPerson_Click(object sender, EventArgs e)
 {
     SQLiteCommand insertSQL = new SQLiteCommand(@"INSERT INTO People (
         id,stxtFname,stxtLname,stxtPhone,
         stxtAddress,stxtCity,stxtState,stxtZip) VALUES
         (@id,@stxtFname,@stxtLname,@stxtPhone,@stxtAddress,@stxtCity,
         @stxtState, @stxtZip)", sqlite_conn);
     //SQLiteCommand test = new SQLiteCommand("")
     insertSQL.Parameters.Add("@id", SqlDbType.Int).Value = null;
     insertSQL.Parameters.Add("@stxtFname", SqlDbType.Text).Value = txtEditFName.Text;
     insertSQL.Parameters.Add("@stxtLname", SqlDbType.Text).Value = txtEditLName.Text;
     insertSQL.Parameters.Add("@stxtPhone", SqlDbType.Text).Value = txtEditPhone.Text;
     insertSQL.Parameters.Add("@stxtAddress", SqlDbType.Text).Value = txtEditAddress.Text;
     insertSQL.Parameters.Add("@stxtCity", SqlDbType.Text).Value = txtEditCity.Text;
     insertSQL.Parameters.Add("@stxtState", SqlDbType.Text).Value = txtEditState.Text;
     insertSQL.Parameters.Add("@stxtZip", SqlDbType.Text).Value = txtEditZip.Text;
     insertSQL.CommandType = CommandType.Text;
     try
     {
         insertSQL.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     LoadUserSelect();
 }

        void LoadGuardians()
        {
            comboGuardians.Items.Clear();
            SQLiteCommand sqlite_cmd3 = new SQLiteCommand("SELECT * FROM Guardians Where [email protected]", sqlite_conn);
            sqlite_cmd3.Parameters.Add("@iStudent", SqlDbType.Int).Value = Int32.Parse(txtID.Text);
            try
            {
                sqlite_cmd3.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            // Now the SQLiteCommand object can give us a DataReader-Object:
            SQLiteDataReader sqlite_datareader3 = sqlite_cmd3.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader3.Read()) // Read() returns true if there is still a result line to read
            {

                SQLiteCommand sqlite_cmd2 = new SQLiteCommand("SELECT * FROM People Where id = @id", sqlite_conn);
                string[] SelectedUser = comboUserSelect.SelectedItem.ToString().Split(':');
                int iUserID = Int32.Parse(SelectedUser[0]);

                sqlite_cmd2.Parameters.Add("@id", SqlDbType.Int).Value = sqlite_datareader3["iPerson"];
                try
                {
                    sqlite_cmd2.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                // Now the SQLiteCommand object can give us a DataReader-Object:
                SQLiteDataReader sqlite_datareader2 = sqlite_cmd2.ExecuteReader();

                // The SQLiteDataReader allows us to run through the result lines:
                while (sqlite_datareader2.Read()) // Read() returns true if there is still a result line to read
                {
                    //Console.WriteLine(String.Format("{0}, {1}", sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
                    comboGuardians.Items.Add(String.Format("{0}: {1} {2}, {3}", sqlite_datareader2["id"], sqlite_datareader2["stxtFname"], sqlite_datareader2["stxtLname"], sqlite_datareader3["sRelationship"]));
                }
            }
        }

        private void comboUserSelect_SelectedIndexChanged(object sender, EventArgs e)
        {
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People Where id = @id", sqlite_conn);
            string[] SelectedUser = comboUserSelect.SelectedItem.ToString().Split(':');
            int iUserID = Int32.Parse(SelectedUser[0]);

            sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
            // 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
            {
                txtID.Text = String.Format("{0}", sqlite_datareader["id"]);
                txtEditLName.Text = String.Format("{0}",sqlite_datareader["stxtLname"]);
                txtEditFName.Text = String.Format("{0}", sqlite_datareader["stxtFname"]);
                txtEditAddress.Text = String.Format("{0}", sqlite_datareader["stxtAddress"]);
                txtEditCity.Text = String.Format("{0}", sqlite_datareader["stxtCity"]);
                txtEditState.Text = String.Format("{0}", sqlite_datareader["stxtState"]);
                txtEditZip.Text = String.Format("{0}", sqlite_datareader["stxtZip"]);
            }

            LoadEContacts();
            LoadGuardians();
        }

        private void btnEditPerson_Click(object sender, EventArgs e)
        {
            SQLiteCommand updateSQL = new SQLiteCommand("UPDATE People set [email protected],stxtLname = @stxtLName, [email protected], [email protected],[email protected],[email protected],[email protected] Where [email protected]", sqlite_conn);
            //,stxtLname,stxtPhone,stxtAddress,stxtCity,stxtState,stxtZip Where id = $) VALUES (@id,@stxtFname,@stxtLname,@stxtPhone,@stxtAddress,@stxtCity,@stxtState, @stxtZip)", sqlite_conn);

            int iUserID = Int32.Parse(txtID.Text);

            updateSQL.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
            updateSQL.Parameters.Add("@stxtFname", SqlDbType.Text).Value = txtEditFName.Text;
            updateSQL.Parameters.Add("@stxtLname", SqlDbType.Text).Value = txtEditLName.Text;
            updateSQL.Parameters.Add("@stxtPhone", SqlDbType.Text).Value = txtEditPhone.Text;
            updateSQL.Parameters.Add("@stxtAddress", SqlDbType.Text).Value = txtEditAddress.Text;
            updateSQL.Parameters.Add("@stxtCity", SqlDbType.Text).Value = txtEditCity.Text;
            updateSQL.Parameters.Add("@stxtState", SqlDbType.Text).Value = txtEditState.Text;
            updateSQL.Parameters.Add("@stxtZip", SqlDbType.Text).Value = txtEditZip.Text;
            updateSQL.CommandType = CommandType.Text;
            try
            {
                updateSQL.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            LoadUserSelect();
        }

 private void btnDeletePerson_Click(object sender, EventArgs e)
 {
     int iUserID = Int32.Parse(txtID.Text);
     SQLiteCommand deleteSQL = new SQLiteCommand("Delete from People WHERE [email protected]", sqlite_conn);
     deleteSQL.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
     try
     {
         deleteSQL.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     LoadUserSelect();
 }

 private bool SaveConfigToFile(string id, string val)
 {
     try
     {
         //save the current configuration 
         SetConnection();
         sql_con.Open();
         sql_cmd = new SQLiteCommand();
         sql_cmd.Connection = sql_con;
         sql_cmd.CommandText = "replace into data(id,value,grp,tbl) values('"+id+"','" + val + "','"+this.sql_grp+"','" + this.sql_tbl + "')";
         sql_cmd.ExecuteNonQuery();
         sql_con.Close();
         return true;
     }
     catch (System.Exception e)
     {
         MsgDlg.Show(e.Message);
         return false;
     }
 }

        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();
        }