示例#1
0
        private void copyTmpToKendaraan()
        {
            cKoneksi koneksi = new cKoneksi();

            using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
            {
                try
                {
                    conn.Open();//buka koneksi
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        using (var transaction = conn.BeginTransaction())
                        {
                            cQuery qr = new cQuery();
                            cmd.CommandText = qr.qCopyTmpToKendaraan();//"INSERT INTO kendaraan SELECT * FROM kendaraanTmp";//query copy table
                            cmd.ExecuteNonQuery();

                            transaction.Commit();
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close(); //tutup koneksi
                    conn.Dispose();
                }
            }
        }
示例#2
0
        //cek data sql lite
        //lastUpdate, true = pakai lastUpdate, false tidak pakai lastUpdate
        // return true jika ada data
        // return false jika tidak ada
        private bool cekDataSqlLIte(string query, bool lastUpdate)
        {
            if (lastUpdate == true)
            {
                this.LastUpdate = string.Empty;
            }                                                          //kosongkan dulu lastUpdate

            bool             ada     = false;
            cKoneksi         koneksi = new cKoneksi();
            SQLiteConnection sqlite  = null;

            sqlite = new SQLiteConnection(koneksi.LokasiSqlite());

            SQLiteDataReader rdrCek          = null;    //fikri 20150202
            SQLiteCommand    cek_dataSqlLite = null;    //fikri 20150202

            cek_dataSqlLite = new SQLiteCommand(query); // fikri 20150202

            try
            {
                sqlite.Open();
                cek_dataSqlLite.Connection = sqlite;
                rdrCek = cek_dataSqlLite.ExecuteReader();
                if (rdrCek.HasRows)
                {
                    if (lastUpdate == true)
                    {
                        while (rdrCek.Read())
                        {
                            this.LastUpdate = rdrCek.GetDateTime(0).ToString();
                        }                                                       //isi lastUpdate
                    }

                    ada = true;
                }
                else
                {
                    ada = false;
                }
                return(ada);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return(ada);
            }
            finally
            {
                rdrCek.Close();
                sqlite.Close();
                sqlite.Dispose();
            }
        }
示例#3
0
        private void InsertIP()
        {
            bool err = false;

            string query = "INSERT INTO IpHistory (IpAddress, LastModified, port) values (@IpAddress, @LastModified, @port)";

            cKoneksi koneksi = new cKoneksi();

            cControl cnt = new cControl();

            using (SQLiteConnection sqlCon = new SQLiteConnection(koneksi.LokasiSqlite()))//insert ke local Sqlite
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    DataTable         dt = new DataTable();

                    try
                    {
                        //---------------------------------
                        cmd.CommandText = query;
                        cmd.Parameters.AddWithValue("@IpAddress", getIpAdr);
                        cmd.Parameters.AddWithValue("@LastModified", cnt.getDateTimeNow());
                        cmd.Parameters.AddWithValue("@port", txtNewPort.Text.ToString().Trim());
                        //---------------------------------
                        cmd.Connection = sqlCon;
                        sqlCon.Open();

                        da.Fill(dt);
                        //---------------------
                    }
                    catch (Exception ex)
                    {
                        err = true;
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();

                        dt.Clear();
                        dt.Dispose();
                    }
                }
            }

            if (err == false)
            {
                refresh(); //jika tidak ada error refresh
            }
        }
        private void SearchAmbilKtm() //select dari simak
        {
            SqlConnection conn = null;

            try
            {
                string konekKe  = null;
                string queryUse = null;

                cQuery   qr      = new cQuery();
                cKoneksi koneksi = new cKoneksi();

                if (rbMahasiswa.Checked == true)
                {
                    konekKe  = "2";//2 = ke simak
                    queryUse = qr.qSearchMhsAmbilKtm(nik, nama);
                }
                else
                {
                    konekKe  = "4";//4 = ke payroll
                    queryUse = qr.qSearchAmbilKartuPeg(nik, nama);
                }

                conn = new SqlConnection(koneksi.konekMsSql(Convert.ToChar(konekKe)));
                SqlCommand cmd = null;
                //---------------------------------
                cmd = new SqlCommand(queryUse, conn);
                //---------------------------------
                conn.Open();
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet        ds = new DataSet();
                da.Fill(ds, "Authors_table");
                //---------------------
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Authors_table";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        private void UpdateRfidPegawai(string idPegawai, string lastHeksa)
        {
            Int32 sequence = Int32.Parse(lastHeksa, System.Globalization.NumberStyles.HexNumber);//dari heksa terakir convert ke int

            sequence++;
            string heksa = String.Format("{0:X7}", sequence); //heksa selanjutnya => utk pegawai heksa ada 7 digit

            DataTable     dt      = null;
            cKoneksi      koneksi = new cKoneksi();
            SqlConnection conn    = new SqlConnection(koneksi.konekMsSql('4'));

            conn.Open();

            try
            {
                string RFID = "2" + heksa + "";

                SqlCommand cmd = null;
                cQuery     qr  = new cQuery();

                cmd = new SqlCommand(qr.qSelectIdPegawaiByRfid(RFID), conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    MessageBox.Show("Proses Generate Gagal");
                    return;
                }

                cmd = new SqlCommand(qr.qUpdateRfidPegByID(), conn);
                cmd.Parameters.AddWithValue("@rfid", RFID);
                cmd.Parameters.AddWithValue("@id_pegawai", idPegawai);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                dt.Clear();
                dt.Dispose();
            }
        }
        private void UpdateRfidMhs(string NIM, string prodi, string lastHeksa)
        {
            Int32 sequence = Int32.Parse(lastHeksa, System.Globalization.NumberStyles.HexNumber);//dari heksa terakir convert ke int

            sequence++;
            string heksa = String.Format("{0:X4}", sequence); //heksa selanjutnya

            DataTable     dt      = null;
            cKoneksi      koneksi = new cKoneksi();
            SqlConnection conn    = new SqlConnection(koneksi.konekMsSql('2'));

            conn.Open();

            try
            {
                string RFID = "1" + prodi + "" + heksa + "";

                SqlCommand cmd = null;
                cQuery     qr  = new cQuery();

                cmd = new SqlCommand(qr.qSelectNimByRfid(RFID), conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    MessageBox.Show("Proses Generate Gagal");
                    return;
                }

                cmd = new SqlCommand(qr.qUpdateRfidMhsByNim(), conn);
                cmd.Parameters.AddWithValue("@RFID", RFID);
                cmd.Parameters.AddWithValue("@STUDENTID", NIM);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                dt.Clear();
                dt.Dispose();
            }
        }
示例#7
0
        private string loginMsSql()
        {
            cKoneksi koneksi = new cKoneksi();
            string   role    = string.Empty;

            var        con = new SqlConnection(koneksi.konekMsSql('1'));//// konek ke 10.0.1.64 db parkir
            cQuery     qr  = new cQuery();
            SqlCommand cmd = new SqlCommand(qr.qSelectUserLoginMsSql(), con);

            cmd.Parameters.AddWithValue("@username", this.textBox_username.Text);
            cmd.Parameters.AddWithValue("@password", this.textBox_pass.Text);

            try
            {
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    if (dr.HasRows == true)
                    {
                        role = dr.GetString(1);
                        cVarGlobal.isAplication = dr.GetString(2);//set hak akses aplikasi
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                role = string.Empty;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return(role);
        }
示例#8
0
        //select data ms sql
        public DataTable selectData(string query, char server)
        {
            cKoneksi koneksi = new cKoneksi();

            using (SqlConnection sqlCon = new SqlConnection(koneksi.konekMsSql(server)))// select data dari server
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //---------------------------------
                    cmd.CommandText = query;
                    //---------------------------------
                    cmd.Connection = sqlCon;
                    sqlCon.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable      dt = new DataTable();
                    da.Fill(dt);
                    //---------------------
                    sqlCon.Close();
                    sqlCon.Dispose();

                    return(dt);
                }
            }
        }
示例#9
0
        private void deleteUser()
        {
            string        errInsert = string.Empty;
            cKoneksi      koneksi   = new cKoneksi();
            SqlConnection conn      = new SqlConnection(koneksi.konekMsSql('1'));//koneksi ke 64 db parkir

            cQuery     qr  = new cQuery();
            SqlCommand cmd = null;

            try
            {
                conn.Open();

                cmd = new SqlCommand(qr.qDeleteUserWhereUsernameAndPass(), conn);
                cmd.Parameters.AddWithValue("@username", txt_username.Text);
                cmd.Parameters.AddWithValue("@password", txt_password.Text);

                cmd.ExecuteNonQuery();//insert ke sql
            }
            catch (Exception ex)
            {
                errInsert = ex.Message;
                MessageBox.Show(errInsert, "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            if (errInsert == string.Empty)
            {
                label_message.Text = "User Berahasil dihapus";
            }

            //string err = string.Empty;
            //cQuery qr = new cQuery();
            //cKoneksi koneksi = new cKoneksi();
            //using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
            //{
            //    try
            //    {
            //        conn.Open();//buka koneksi
            //        using (var cmd = new SQLiteCommand(conn))
            //        {
            //            using (var transaction = conn.BeginTransaction())
            //            {
            //                cmd.CommandText = qr.qDeleteUserWhereUsernameAndPass();//" Delete from user where username = @username and password = @password ";
            //                cmd.Parameters.Clear();
            //                cmd.Prepare();
            //                cmd.Parameters.AddWithValue("@username", txt_username.Text);
            //                cmd.Parameters.AddWithValue("@password", txt_password.Text);
            //                cmd.ExecuteNonQuery();

            //                transaction.Commit();
            //            }
            //        }
            //    }
            //    catch(Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //    }
            //    finally
            //    {
            //        conn.Close(); //tutup koneksi
            //        conn.Dispose();
            //    }
            //}
            //if (err == string.Empty)
            //{
            //    MessageBox.Show("Username berhasil dihapus");
            //}
        }
示例#10
0
        private void createUser()
        {
            string        errInsert = string.Empty;
            cKoneksi      koneksi   = new cKoneksi();
            SqlConnection conn      = new SqlConnection(koneksi.konekMsSql('1'));//koneksi ke 64 db parkir

            cQuery     qr  = new cQuery();
            SqlCommand cmd = null;

            string aplication = string.Empty;

            if (rbAkademik.Checked == true)
            {
                aplication = "Akademik";
            }
            else if (rbBiroUmum.Checked == true)
            {
                if (rbGate.Checked == true)
                {
                    aplication = "Gate";
                }
                else if (rbKantor.Checked == true)
                {
                    aplication = "Biro Umum";
                }
            }

            try
            {
                conn.Open();

                cmd = new SqlCommand(qr.qInsertUser(), conn);

                cmd.Parameters.AddWithValue("@username", txt_username.Text);
                cmd.Parameters.AddWithValue("@fullname", txt_fullname.Text);
                cmd.Parameters.AddWithValue("@password", txt_password.Text);
                cmd.Parameters.AddWithValue("@role", combo_role.Text);
                cmd.Parameters.AddWithValue("@aplication", aplication);

                cmd.ExecuteNonQuery();//insert ke sql
            }
            catch (Exception ex)
            {
                errInsert = ex.Message;
                MessageBox.Show(errInsert, "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            if (errInsert == string.Empty)
            {
                label_message.Text = "Registrasi User Berhasil Disimpan";
            }

            //cKoneksi koneksi = new cKoneksi();
            //cQuery qr = new cQuery();
            //using (var conn = new SqlConnection(koneksi.konekMsSql('1')))//koneksi ke 64 db parkir
            //{
            //    try
            //    {

            //        conn.Open();//buka koneksi
            //        using (var cmd = new SqlCommand(conn))
            //        {
            //            using (var transaction = conn.BeginTransaction())
            //            {
            //                cmd.CommandText = qr.qInsertUser(); // " INSERT INTO user (username ,fullname ,password ,role) VALUES (@username,@fullname,@password,@role)";
            //                    cmd.Parameters.Clear();
            //                    cmd.Prepare();
            //                    cmd.Parameters.AddWithValue("@username", txt_username.Text);
            //                    cmd.Parameters.AddWithValue("@fullname", txt_fullname.Text);
            //                    cmd.Parameters.AddWithValue("@password", txt_password.Text);
            //                    cmd.Parameters.AddWithValue("@role", combo_role.Text);
            //                    cmd.ExecuteNonQuery();

            //                transaction.Commit();
            //            }
            //        }
            //    }
            //    catch
            //    {
            //        MessageBox.Show("Username sudah dipakai, coba yang lain");
            //    }
            //    finally
            //    {
            //        conn.Close(); //tutup koneksi
            //        conn.Dispose();
            //    }

            //}
        }
示例#11
0
        private void blokir(string IdMhsOrPegawai, string rfid, string NamaMhs)
        {
            string        errInsert = string.Empty;
            cKoneksi      koneksi   = new cKoneksi();
            SqlConnection conn      = new SqlConnection(koneksi.konekMsSql('3'));//konek ke sql server historyBlokir

            cQuery     qr  = new cQuery();
            SqlCommand cmd = null;

            try
            {
                conn.Open();

                cmd = new SqlCommand(qr.qInsertHistory(), conn);

                cmd.Parameters.AddWithValue("@id_pengguna", IdMhsOrPegawai);
                cmd.Parameters.AddWithValue("@rfid", rfid);
                cmd.Parameters.AddWithValue("@nama", NamaMhs);

                cmd.ExecuteNonQuery();//insert ke sql
            }
            catch (Exception ex)
            {
                errInsert = ex.Message;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            //jika insert ke table history sukses, lanjut hapus rfid dari tblMhs
            if (errInsert == string.Empty)
            {
                string konekKe  = null;
                string queryUse = null;
                string paramUse = null;
                if (rbMahasiswa.Checked == true)//konek ke simak
                {
                    konekKe  = "2";
                    queryUse = qr.qUpdateRfidNullByNim();
                    paramUse = "@STUDENTID";
                }
                else if (rbPegawai.Checked == true)//konek ke payroll
                {
                    konekKe  = "4";
                    queryUse = qr.qUpdateRfidNullByIdPeg();
                    paramUse = "@id_pegawai";
                }

                SqlConnection connMhsOrPeg = new SqlConnection(koneksi.konekMsSql(Convert.ToChar(konekKe)));
                SqlCommand    cmdDelRfid   = null;
                try
                {
                    connMhsOrPeg.Open();

                    cmdDelRfid = new SqlCommand(queryUse, connMhsOrPeg);

                    cmdDelRfid.Parameters.AddWithValue(paramUse, IdMhsOrPegawai);
                    cmdDelRfid.ExecuteNonQuery();//insert ke sql
                }
                finally
                {
                    connMhsOrPeg.Close();
                    connMhsOrPeg.Dispose();
                }
            }
        }
示例#12
0
        private string downloadMahasiswaTmp()
        {
            string err = string.Empty;

            try
            {
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();
                DataTable dt = null;
                dt = db.selectData(qr.qSelectMhs(), '2'); // '2' = ambil data dari 10.0.1.61 db s1makumyny4
                if (dt.Rows.Count != 0)                   //jika ada data
                {
                    //===inisialisasi progres bar======
                    if (ProgressBarSinkron.InvokeRequired)
                    {
                        ProgressBarSinkron.BeginInvoke(
                            new Action(() =>
                        {
                            ProgressBarSinkron.Value   = 0;
                            ProgressBarSinkron.Minimum = 0;
                            ProgressBarSinkron.Maximum = dt.Rows.Count;
                        }
                                       ));
                    }
                    //=================================

                    cKoneksi koneksi = new cKoneksi();
                    using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
                    {
                        try
                        {
                            conn.Open();//buka koneksi
                            using (var cmd = new SQLiteCommand(conn))
                            {
                                using (var transaction = conn.BeginTransaction())
                                {
                                    //looping insert
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        cmd.CommandText = qr.qInsertPenggunaTmp();//"insert into penggunaTmp (id,nik,nama,rfid,kategori) values (@id,@nik,@nama,@rfid,@kategori)";
                                        cmd.Parameters.Clear();
                                        cmd.Prepare();
                                        cmd.Parameters.AddWithValue("@id", row["StudentID"]);  //kenapa ID diisi sama kayak nik??
                                        cmd.Parameters.AddWithValue("@nik", row["StudentID"]); //kenapa ID diisi sama kayak nik??
                                        cmd.Parameters.AddWithValue("@nama", row["fullname"]);
                                        cmd.Parameters.AddWithValue("@rfid", row["rfid"]);
                                        cmd.Parameters.AddWithValue("@kategori", 1); // 1 = kategori mahasiswa
                                        cmd.ExecuteNonQuery();

                                        //===proses progres bar======
                                        if (ProgressBarSinkron.InvokeRequired)
                                        {
                                            ProgressBarSinkron.BeginInvoke(
                                                new Action(() =>
                                            {
                                                ProgressBarSinkron.Increment(1);
                                            }
                                                           ));
                                        }
                                        //===========================
                                    }

                                    transaction.Commit();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close(); //tutup koneksi
                            conn.Dispose();
                        }
                    }

                    ////===tutup progres bar======
                    //if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                    //{ LblProgressBar.Text = "Download Complete"; }
                    //MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download");
                    //panelProgressBar.Visible = false;// hiden panel progressbar
                    ////==========================
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
                //ProgressBarSinkron.Visible = false;
                MessageBox.Show(ex.Message);
            }
            return(err);
        }
示例#13
0
        private string downloadKendaraanTmp()
        {
            string err = string.Empty;

            try
            {
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();
                DataTable dt = null;
                dt = db.selectData(qr.qSelectKendaraan(), '1'); // ambil data dari mssql
                if (dt.Rows.Count != 0)                         //jika ada data
                {
                    //===inisialisasi progres bar======
                    if (ProgressBarSinkron.InvokeRequired)
                    {
                        ProgressBarSinkron.BeginInvoke(
                            new Action(() =>
                        {
                            ProgressBarSinkron.Style   = ProgressBarStyle.Blocks;
                            ProgressBarSinkron.Value   = 0;
                            ProgressBarSinkron.Minimum = 0;
                            ProgressBarSinkron.Maximum = dt.Rows.Count;
                        }
                                       ));
                    }
                    //=================================

                    cKoneksi koneksi = new cKoneksi();

                    using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
                    {
                        try
                        {
                            conn.Open();//buka koneksi
                            using (var cmd = new SQLiteCommand(conn))
                            {
                                using (var transaction = conn.BeginTransaction())
                                {
                                    //looping insert
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        cmd.CommandText = qr.qInsertKendaraanTmp();
                                        cmd.Parameters.Clear();
                                        cmd.Prepare();
                                        cmd.Parameters.AddWithValue("@id", row["id_kendaraan"]);
                                        cmd.Parameters.AddWithValue("@id_pengguna", row["pengguna"]);
                                        cmd.Parameters.AddWithValue("@no_kendaraan", row["tnkb"]);
                                        cmd.Parameters.AddWithValue("@pemilik", row["pemilik"]);
                                        cmd.Parameters.AddWithValue("@merek", row["merek"]);
                                        cmd.Parameters.AddWithValue("@jenis", row["id_jenis_kendaraan"]); //jenis dilangsung aja
                                        cmd.Parameters.AddWithValue("@last_update", row["last_update"]);  //last Update
                                        cmd.ExecuteNonQuery();

                                        //===proses progres bar======
                                        if (ProgressBarSinkron.InvokeRequired)
                                        {
                                            ProgressBarSinkron.BeginInvoke(
                                                new Action(() =>
                                            {
                                                ProgressBarSinkron.Increment(1);
                                            }
                                                           ));
                                        }
                                        //===========================
                                    }

                                    transaction.Commit();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close(); //tutup koneksi
                            conn.Dispose();
                        }
                    }

                    ////===tutup progres bar======
                    //if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                    //{ LblProgressBar.Text = "Download Complete"; }
                    //MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download");
                    //panelProgressBar.Visible = false;// hiden panel progressbar
                    ////==========================
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
                //ProgressBarSinkron.Visible = false;
                MessageBox.Show(ex.Message);
            }
            return(err);
        }
示例#14
0
        private string downloadOperatorTmp()
        {
            string err = string.Empty;

            try
            {
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();
                DataTable dt = null;
                dt = db.selectData(qr.qSelectUserOpAkdk(), '1'); // ambil data dari mssql
                if (dt.Rows.Count != 0)                          //jika ada data
                {
                    //===inisialisasi progres bar======
                    if (ProgressBarSinkron.InvokeRequired)
                    {
                        ProgressBarSinkron.BeginInvoke(
                            new Action(() =>
                        {
                            ProgressBarSinkron.Style   = ProgressBarStyle.Blocks;
                            ProgressBarSinkron.Value   = 0;
                            ProgressBarSinkron.Minimum = 0;
                            ProgressBarSinkron.Maximum = dt.Rows.Count;
                        }
                                       ));
                    }

                    //=================================

                    cKoneksi koneksi = new cKoneksi();

                    using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
                    {
                        try
                        {
                            conn.Open();//buka koneksi
                            using (var cmd = new SQLiteCommand(conn))
                            {
                                using (var transaction = conn.BeginTransaction())
                                {
                                    //looping insert
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        cmd.CommandText = qr.qInsertUserTmp();//" INSERT INTO userTmp (username, fullname, password, role) values(@username, @fullname, @password, @role)";
                                        cmd.Parameters.Clear();
                                        cmd.Prepare();
                                        cmd.Parameters.AddWithValue("@username", row["username"]);
                                        cmd.Parameters.AddWithValue("@fullname", row["fullname"]);
                                        cmd.Parameters.AddWithValue("@password", row["password"]);
                                        cmd.Parameters.AddWithValue("@aplication", row["aplication"]);
                                        cmd.Parameters.AddWithValue("@role", row["role"]);
                                        cmd.ExecuteNonQuery();

                                        //===proses progres bar======
                                        if (ProgressBarSinkron.InvokeRequired)
                                        {
                                            ProgressBarSinkron.BeginInvoke(
                                                new Action(() =>
                                            {
                                                ProgressBarSinkron.Increment(1);
                                            }
                                                           ));
                                        }

                                        //ProgressBarSinkron.Increment(1);
                                        //===========================
                                    }

                                    transaction.Commit();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close(); //tutup koneksi
                            conn.Dispose();
                        }
                    }

                    ////===tutup progres bar======
                    //if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                    //{ LblProgressBar.Text = "Download Complete"; }
                    //MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download");
                    //panelProgressBar.Visible = false;// hiden panel progressbar
                    ////==========================
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
                //ProgressBarSinkron.Visible = false;
                MessageBox.Show(ex.Message);
            }
            return(err);
        }
示例#15
0
        private string uploadTransaksiKartuMhs()
        {
            cSyncRun.isSync = true;    //tandai proses sync

            string err = string.Empty; //tampungan error message

            try
            {
                DataTable dt = null;
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();

                dt = db.selectDataSqlite(qr.qSelectTransaksiMhsStatus0()); // ambil data dari sqlite
                if (dt.Rows.Count != 0)                                    //jika ada data
                {
                    cKoneksi koneksi = new cKoneksi();

                    SqlConnection    conn     = new SqlConnection(koneksi.konekMsSql('2'));   //konek ke sql server user ktm = 2
                    SQLiteConnection connLite = new SQLiteConnection(koneksi.LokasiSqlite()); //koneksi ke sqlite
                    try
                    {
                        conn.Open();     //open sql server
                        connLite.Open(); //open sqlite

                        //===inisialisasi progres bar======
                        if (PbUpload.InvokeRequired)
                        {
                            PbUpload.BeginInvoke(
                                new Action(() =>
                            {
                                PbUpload.Style   = ProgressBarStyle.Blocks;
                                PbUpload.Value   = 0;
                                PbUpload.Minimum = 0;
                                PbUpload.Maximum = dt.Rows.Count;
                            }
                                           ));
                        }
                        //=================================

                        SQLiteTransaction transactionSqlite = connLite.BeginTransaction();

                        DataTable dtTrMhs = null;
                        foreach (DataRow row in dt.Rows)
                        {
                            SqlCommand myCommand = null;

                            dtTrMhs = cekTransaksiMhs(row["nim"].ToString()); //cek data sebelumnya
                            if (dtTrMhs.Rows.Count == 0)                      //jika tidak ada data sebelumnya langung di insert
                            {
                                myCommand = new SqlCommand(qr.qInsertStatusMhs(), conn);

                                myCommand.Parameters.AddWithValue("@STUDENTID", row["nim"]);
                                myCommand.Parameters.AddWithValue("@TGL_AMBIL_KTM", row["lastModified"]);
                                myCommand.Parameters.AddWithValue("@PTGS_KTM_AMBIL", row["operator"]);
                            }
                            else //hanya di update
                            {
                                //==cek dulu status ktm diambil==
                                Int32  AMBIL_KTM_KE;
                                string tmpAMBIL_KTM_KE = "";
                                foreach (DataRow rowTrMhs in dtTrMhs.Rows)
                                {
                                    tmpAMBIL_KTM_KE = rowTrMhs["AMBIL_KTM_KE"].ToString().Trim();
                                }
                                //===============================

                                myCommand = new SqlCommand(qr.qUpdateStatusMhs(), conn);

                                if (Int32.TryParse(tmpAMBIL_KTM_KE, out AMBIL_KTM_KE))
                                {//jika sudah pernah diambil, urutan pengambilan ditambah 1
                                    myCommand.Parameters.AddWithValue("@AMBIL_KTM_KE", AMBIL_KTM_KE + 1);
                                }
                                else
                                {//jika urutan bukan int, set jadi 0
                                    myCommand.Parameters.AddWithValue("@AMBIL_KTM_KE", 1);
                                }
                                myCommand.Parameters.AddWithValue("@TGL_AMBIL_KTM", row["lastModified"]);
                                myCommand.Parameters.AddWithValue("@STUDENTID", row["nim"]);
                                myCommand.Parameters.AddWithValue("@PTGS_KTM_AMBIL", row["operator"]);
                            }

                            SQLiteCommand cmdSqlite = new SQLiteCommand(qr.qUpdateStatusUpload1(), connLite);
                            cmdSqlite.Parameters.AddWithValue("@idTransaksi", row["idTransaksi"]);

                            ////cek perintah thread close
                            if (cSyncRun.closingThread == true)
                            {
                                transactionSqlite.Commit(); //jika ada perintah thread close, maka di sqlite langsung di commit

                                conn.Close();               //tutup koneksi
                                conn.Dispose();
                                connLite.Close();
                                connLite.Dispose();

                                return("Thread Closed By User");
                            }

                            try
                            {
                                myCommand.ExecuteNonQuery();//insert ke sql
                            }
                            catch
                            {
                                transactionSqlite.Commit();//jika proses insert di sql gagal, di sqlite langsung di commit
                            }

                            cmdSqlite.ExecuteNonQuery();//update status ke sqlite

                            //===proses progres bar======
                            if (PbUpload.InvokeRequired)
                            {
                                PbUpload.BeginInvoke(
                                    new Action(() =>
                                {
                                    PbUpload.Increment(1);
                                }
                                               ));
                            }
                            //===========================

                            dtTrMhs.Clear();
                        }
                        transactionSqlite.Commit();
                    }
                    catch (Exception ex)
                    {
                        err = ex.Message;
                        //MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        conn.Close(); //tutup koneksi
                        conn.Dispose();
                        connLite.Close();
                        connLite.Dispose();
                    }
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
            }

            cSyncRun.isSync = false;

            return(err);
        }