public DataSet Get_Propietarios_Gestion(int GestionId)
 {
     try
     {
         if (ds.Tables["DATOS"] != null)
         {
             ds.Tables.Remove("DATOS");
         }
         cn.Open();
         OleDbCommand cmd = new OleDbCommand("SP_Get_Propietarios_Gestion", cn);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add("@GestionId", OleDbType.Integer).Value = GestionId;
         OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
         adp.Fill(ds, "DATOS");
         cn.Close();
         return(ds);
     }
     catch (Exception ex)
     {
         cn.Close();
         DataSet ds = new DataSet();
         return(ds);
     }
 }
Example #2
0
 static public DataTable importItemList()
 {
     try
     {
         using (OleDbConnection conn = new OleDbConnection())
         {
             string    path = dirExecutable + @"\Data\MasterItemList.xlsx";
             DataTable dt   = new DataTable();
             conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
             using (OleDbCommand comm = new OleDbCommand())
             {
                 comm.CommandText = "Select * from [MasterItemList$]";
                 comm.Connection  = conn;
                 using (OleDbDataAdapter da = new OleDbDataAdapter())
                 {
                     da.SelectCommand = comm;
                     da.Fill(dt);
                     conn.Close();
                     conn.Dispose();
                     return(dt);
                 }
             }
         }
     }
     catch (Exception e)
     {
         DialogResult result = MessageBox.Show(@"The item list for market prices was not cloned properly. It is in the Github under " +
                                               "/DOA Tracker v3/Data and should be put into the same folder in the executable directory; The " +
                                               "template DOA.accdb file should be located in there as well. Do you want to view the error log?", "File Missing!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
         if (result == DialogResult.Yes)
         {
             MessageBox.Show(e.ToString());
         }
         return(null);
     }
 }
Example #3
0
        public DataTable LoadDataTable(string connectionString, string sql)
        {
            if (MyLoadDataTable != null)
            {
                return(MyLoadDataTable(connectionString, sql));
            }

            DataTable     table      = new DataTable();
            DbDataAdapter adapter    = null;
            var           connection = Helper.DbConnectionFromConnectionString(connectionString);

            connection.Open();
            if (connection is OdbcConnection)
            {
                adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);
            }
            else
            {
                adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
            }
            adapter.SelectCommand.CommandTimeout = SelectTimeout;
            adapter.Fill(table);
            return(table);
        }
 public DataSet Persona_Juridica_Get(int UsuarioId)
 {
     try
     {
         if (ds.Tables["DATOS"] != null)
         {
             ds.Tables.Remove("DATOS");
         }
         cn.Open();
         OleDbCommand cmd = new OleDbCommand("SP_Persona_Juridica_GET", cn);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add("@UsuarioId", OleDbType.Integer).Value = UsuarioId;
         OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
         adp.Fill(ds, "DATOS");
         cn.Close();
         return(ds);
     }
     catch (Exception ex)
     {
         cn.Close();
         DataSet ds = new DataSet();
         return(ds);
     }
 }
Example #5
0
        public void loadround()
        {
            ivRound.Items.Clear();
            try
            {
                loaddb.checkdb();
                string           loadr = "SELECT * FROM DBRunRound ";
                OleDbCommand     cmd   = new OleDbCommand(loadr, loaddb.cnn);
                OleDbDataAdapter da    = new OleDbDataAdapter(cmd);
                DataSet          ds    = new DataSet();
                DataTable        dt    = new DataTable();
                da.Fill(ds, "DBRunRound");
                loaddb.cnn.Close();
                dt = ds.Tables["DBRunRound"];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ivRound.Items.Add(dt.Rows[i]["IDRR"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RTime"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["IDCard"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RName"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RLName"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RPhone"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RPST"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RNCar"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["Round"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RTicket"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RMoney"].ToString());
                    ivRound.Items[i].SubItems.Add(dt.Rows[i]["RCheck"].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "ตรวจพบข้อผิดพลาด");
            }
        }
Example #6
0
        private void search_box_Click(object sender, EventArgs e)
        {
            count = 0;
            con.Open();

            OleDbCommand cmd = con.CreateCommand();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = " select Name, Location, Weight, Bgroup, Gender, Phone from Table1 where Bgroup =  '" + bg.Text + "' and Location = '" + loc.Text + "' ";
            cmd.ExecuteNonQuery();
            DataTable        dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);

            da.Fill(dt);
            count = Convert.ToInt32(dt.Rows.Count.ToString());
            dataGridView1.DataSource = dt;

            con.Close();

            if (count == 0)
            {
                MessageBox.Show("Record is not found");
            }
        }
Example #7
0
        public void DataKelas()
        {
            OleDbConnection koneksi = new OleDbConnection(db);

            koneksi.Open();
            string       query = "SELECT KELAS FROM vw_DATA_JADWAL WHERE NIP = '" + labelNIP.Text + "' GROUP BY KELAS";
            OleDbCommand cmd   = new OleDbCommand();

            cmd.CommandText = query;
            cmd.Connection  = koneksi;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable        dt = new DataTable();

            da.Fill(dt);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                comboBox_LihatJadwalKelas.Items.Add(dt.Rows[i]["KELAS"].ToString());
                comboBox_InputNilaiKelas.Items.Add(dt.Rows[i]["KELAS"].ToString());
                comboBox_KelasDataNilaiSiswa.Items.Add(dt.Rows[i]["KELAS"].ToString());
            }
            koneksi.Close();
        }
Example #8
0
        private void BillProducts_Load(object sender, EventArgs e)
        {
            //MessageBox.Show(billno);
            OleDbDataAdapter ad4 = new OleDbDataAdapter("Select billno as Bill_No,bdate as Bill_Date,pname as Products,PSIZE AS P_SIZE,PCOLOR AS P_COLOR,QPC AS Q_P_CASE,PCASE AS P_CASE,pqty as T_QTY, pqtype as Unit,prate as Rate,pamount as Amount,bid from bill where billno=@billno and pamount>0 order by bid", con);

            ad4.SelectCommand.Parameters.Add("@billno", OleDbType.VarChar).Value = billno;
            DataSet ds4 = new DataSet();

            ad4.Fill(ds4);
            dataGridView1.DataSource       = ds4.Tables[0];
            dataGridView1.Columns[0].Width = 50;
            dataGridView1.Columns[1].Width = 80;
            dataGridView1.Columns[2].Width = 100;
            dataGridView1.Columns[3].Width = 50;
            dataGridView1.Columns[4].Width = 75;
            dataGridView1.Columns[5].Width = 65;
            dataGridView1.Columns[6].Width = 65;
            //dataGridView1.Columns[7].Width = 75;
            //dataGridView1.Columns[8].Width = 75;
            //dataGridView1.Columns[9].Width = 35;
            //dataGridView1.Columns[10].Width = 75;
            //dataGridView1.Columns[11].Width = 75;
            //dataGridView1.Columns[12].Width = 75;
        }
Example #9
0
        private void button3_Click(object sender, EventArgs e)
        {
            OleDbConnection DB1 = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = apteka.mdb");

            DB1.Open();

            OleDbCommand drop_command = new OleDbCommand("delete from " + comboBox1.SelectedItem + " where " + dataGridView1.Columns[0].HeaderText + "=" + dataGridView1.SelectedCells[0].Value.ToString(), DB1);

            if (drop_command.ExecuteNonQuery() == 0)
            {
                MessageBox.Show("Ошибка в запросе!");
            }

            DB1.Close();
            OleDbConnection DB = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = apteka.mdb");

            DB.Open();
            OleDbDataAdapter DA = new OleDbDataAdapter("select * from " + comboBox1.SelectedItem, DB);
            DataTable        DT = new DataTable();

            DA.Fill(DT);
            dataGridView1.DataSource = DT;
            DB.Close();
        }
Example #10
0
        private void comboBox_InputNilaiKelas_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox_InputNilaiMapel.Items.Clear();
            OleDbConnection koneksi = new OleDbConnection(db);

            koneksi.Open();

            string       query = "SELECT NAMA_PELAJARAN FROM vw_DATA_JADWAL WHERE NIP = '" + labelNIP.Text + "' AND KELAS = '" + comboBox_InputNilaiKelas.Text + "' GROUP BY NAMA_PELAJARAN";
            OleDbCommand cmd   = new OleDbCommand();

            cmd.CommandText = query;
            cmd.Connection  = koneksi;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable        dt = new DataTable();

            da.Fill(dt);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                comboBox_InputNilaiMapel.Items.Add(dt.Rows[i]["NAMA_PELAJARAN"].ToString());
            }

            string       query_2 = "SELECT NIS, NAMA_LENGKAP, KELAS FROM MASTER_SISWA WHERE KELAS='" + comboBox_InputNilaiKelas.Text + "' ORDER BY NAMA_LENGKAP ASC";
            OleDbCommand cmd_2   = new OleDbCommand();

            cmd_2.CommandText = query_2;
            cmd_2.Connection  = koneksi;

            OleDbDataAdapter da_2 = new OleDbDataAdapter(cmd_2);
            DataTable        dt_2 = new DataTable();

            da_2.Fill(dt_2);
            dataGridView_InputNilai_Jadwal.DataSource = dt_2;
            koneksi.Close();
        }
Example #11
0
        private void FillServiceList()
        {
            string           queryText = "SELECT ID,servicename FROM services ORDER BY servicename";
            OleDbDataAdapter adapter   = new OleDbDataAdapter(queryText, connectionString);
            DataSet          dataset   = new DataSet();

            adapter.Fill(dataset, "services");
            ServicesDataGrid.DataSource                   = dataset.Tables["services"].DefaultView;
            ServicesDataGrid.Columns["ID"].Visible        = false;
            ServicesDataGrid.Columns["servicename"].Width = 120;
            ServicesDataGrid.Columns["servicename"].DefaultCellStyle.BackColor = Color.LightGray;
            ServicesDataGrid.Columns["servicename"].ReadOnly = true;
            ServicesDataGrid.Columns.Add("quantity", "");
            ServicesDataGrid.Columns["quantity"].Width = 50;
            ServicesDataGrid.Columns.Add("unit", "");
            ServicesDataGrid.Columns["unit"].Width = 50;
            ServicesDataGrid.Columns["unit"].DefaultCellStyle.BackColor = Color.LightGray;
            ServicesDataGrid.Columns["unit"].ReadOnly = true;
            for (int i = 0; i < ServicesDataGrid.RowCount; i++)
            {
                ServicesDataGrid[2, i].Value = "0";
                ServicesDataGrid[3, i].Value = "шт.";
            }
        }
Example #12
0
        public bool OledbRead(string filePath)
        {
            //office 2007 or 2013
            string strCon = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0;'";

            //实例化一个Oledbconnection类(实现了IDisposable,要using)
            using (OleDbConnection ole_conn = new OleDbConnection(strCon))
            {
                ole_conn.Open();
                using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
                {
                    //类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表]
                    ole_cmd.CommandText = "select * from [Sheet1$]";
                    OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd);
                    DataSet          ds      = new DataSet();
                    adapter.Fill(ds, "Sheet1");
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        Console.WriteLine(ds.Tables[0].Rows[i]["Name"].ToString());//注意:列名不区分大小写
                    }
                }
            }
            return(true);
        }
Example #13
0
        private void Sorgula_Click(object sender, EventArgs e)
        {
            OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = vt.mdb");

            baglanti.Open();
            DataSet          dt1 = new DataSet();
            OleDbDataAdapter da  = new OleDbDataAdapter("Select tarih,urunadi,fiyat,miktar from fatura where tarih between @tarih1 and @tarih2 and alici='" + Program.kullaniciadi + "'", baglanti);

            da.SelectCommand.Parameters.AddWithValue("@tarih1", dateTimePicker1.Value.ToString("d.MM.yyyy"));
            da.SelectCommand.Parameters.AddWithValue("@tarih2", dateTimePicker2.Value.ToString("d.MM.yyyy"));

            da.Fill(dt1, "veri");
            dataGridView1.DataSource = dt1.Tables["veri"];
            baglanti.Close();

            //Tabloların isimlerini düzenle
            dataGridView1.Columns[0].HeaderText = "Tarih";

            dataGridView1.Columns[1].HeaderText = "Satılan Ürün";

            dataGridView1.Columns[2].HeaderText = "Birim Fiyatı";

            dataGridView1.Columns[3].HeaderText = "Ürün Miktarı";
        }
Example #14
0
 public void veriler()
 {
     if (secilenislem == 0)
     {
         baglan.Open();
         OleDbDataAdapter adp = new OleDbDataAdapter("select * from ADMIN where ad='" + txtad.Text + "' and soyad='" + txtsoyad.Text + "'", baglan);
         adp.Fill(tablo);
         baglan.Close();
         ad     = tablo.Rows[0][0].ToString();
         soyad  = tablo.Rows[0][1].ToString();
         kul_ad = tablo.Rows[0][2].ToString();
         posta  = tablo.Rows[0][4].ToString();
     }
     if (secilenislem == 1)
     {
         baglan.Open();
         OleDbDataAdapter adp = new OleDbDataAdapter("select * from ADMIN where kullanici_adi='" + txtkulad.Text + "' ", baglan);
         adp.Fill(tablo);
         baglan.Close();
         ad     = tablo.Rows[0][0].ToString();
         soyad  = tablo.Rows[0][1].ToString();
         kul_ad = tablo.Rows[0][2].ToString();
         posta  = tablo.Rows[0][4].ToString();
     }
     if (secilenislem == 2)
     {
         baglan.Open();
         OleDbDataAdapter adp = new OleDbDataAdapter("select * from ADMIN where eposta='" + txteposta.Text + "'", baglan);
         adp.Fill(tablo);
         baglan.Close();
         ad     = tablo.Rows[0][0].ToString();
         soyad  = tablo.Rows[0][1].ToString();
         kul_ad = tablo.Rows[0][2].ToString();
         posta  = tablo.Rows[0][4].ToString();
     }
 }
Example #15
0
    public static dsPersonnel GetPersonnel(string Database, string strSearch)
    {
        dsPersonnel      DS;
        OleDbConnection  sqlConn;
        OleDbDataAdapter sqlDA;

        // Instantiation of OleDbConnection 'sqlConn'
        sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database);
        // Instantiation of OledbDataAdapter 'sqlDA'
        if (strSearch == null || strSearch.Trim() == "")
        {
            sqlDA = new OleDbDataAdapter("select * from tblPersonnel", sqlConn);
        }
        else
        {
            sqlDA = new OleDbDataAdapter("select * from tblPersonnel where LastName = '" + strSearch + "'", sqlConn);
        }
        // Instantiation of dsUserActivity 'DS'
        DS = new dsPersonnel();
        //The 'Fill' method of 'sqlDA' class used with a parameter to access the table in the dataset
        sqlDA.Fill(DS.tblPersonnel);
        // The method will return information from the UserActivity Dataset
        return(DS);
    }
Example #16
0
    /// find user method
    // used to find the user in the database
    public dsProgramaholics FindUser(string LastName)
    {
        // sql statements to find user in datbase

        /*string sqlStmt = "SELECT * " +
         * "FROM tblUserDetails " +
         * "LEFT JOIN tblProgram ON (tblUserDetails.id = tblProgram.id) " +
         * "LEFT JOIN tblCompleted ON (tblUserDetails.id = tblCompleted.id)" +
         * " WHERE tblUserDetails.LastName like '" + LastName + "';";
         */
        string sqlStmt = "SELECT * FROM tblUserDetails, tblProgram, tblCompleted WHERE tblUseDetails.id = tblProgram.id and tblUseDetails.id = tblCompleted.id and tblUserDetails.LasName like ‘“ + LastName + “‘";

        OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter(sqlStmt, dbConnection);

        // new dataset to store
        dsProgramaholics myStoreData = new dsProgramaholics();

        sqlDataAdapter.Fill(myStoreData.tblUserDetails);
        sqlDataAdapter.Fill(myStoreData.tblPrograms);
        sqlDataAdapter.Fill(myStoreData.tblCompleted);

        // return dataset
        return(myStoreData);
    }
Example #17
0
    public static DataSet GetRandomSong()
    {
        OleDbConnection conn   = OpenConnection();
        Random          random = new Random((int)DateTime.Now.Ticks);

        String          getLastID = "SELECT TOP 1 TrackID FROM tblSong ORDER BY TrackID DESC";
        OleDbCommand    cmd       = new OleDbCommand(getLastID, conn);
        OleDbDataReader reader    = cmd.ExecuteReader();

        reader.Read();
        int lastID   = Convert.ToInt32(reader["TrackID"]);
        int randomID = random.Next(1, lastID);

        String strSQL = "SELECT TOP 1 TrackID, TrackTitle, TrackFilePath, tblAlbum.AlbumID, tblAlbum.AlbumTitle, tblAlbum.AlbumArtPath, "
                        + "tblArtist.ArtistID, tblArtist.ArtistName FROM (tblSONG INNER JOIN tblAlbum ON tblAlbum.AlbumID = tblSong.AlbumID) "
                        + "INNER JOIN tblArtist ON tblArtist.ArtistID = tblSong.ArtistID WHERE TrackID = " + randomID + ";";

        DataSet          randomSong    = new DataSet();
        OleDbDataAdapter randomAdapter = new OleDbDataAdapter(strSQL, conn);

        randomAdapter.Fill(randomSong, "randomSet");
        CloseConnection(conn);
        return(randomSong);
    }
Example #18
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            (Page.Master.FindControl("ProductsClassTreeView") as TreeView).ExpandDepth = 0;

            string          strConnection = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
            OleDbConnection objConnection = new OleDbConnection(strConnection);
            objConnection.Open();
            using (objConnection)
            {
                string           _sql = "SELECT * FROM XXTable";
                OleDbDataAdapter da   = new OleDbDataAdapter(_sql, objConnection);
                DataSet          ds   = new DataSet(); //填充DataSet
                da.Fill(ds, "nb");
                DataTable _dt = ds.Tables["nb"];
                strContactInf        = _dt.Rows[0]["LXFS"].ToString();
                AboutUsInfLabel.Text = _dt.Rows[0]["GSXJ"].ToString();
                JoinUsLabel.Text     = _dt.Rows[0]["ZPXX"].ToString();
                if (Request.QueryString["class"] == "joinus")
                {
                    stitle.Text             = "招聘信息";
                    JoinUsLabel.Visible     = true;
                    AboutUsInfLabel.Visible = false;
                    bannerImage.ImageUrl    = "~/images/b/jobs.jpg";
                }
                else
                {
                    stitle.Text             = "公司简介";
                    AboutUsInfLabel.Visible = true;
                    JoinUsLabel.Visible     = false;
                    bannerImage.ImageUrl    = "~/images/b/aboutus.jpg";
                }
            }
        }
    }
Example #19
0
        public DataTable Query(string sql, params object[] parameters)
        {
            DataTable table = new DataTable();
            //try {
            var cmd = new OleDbCommand(sql, connection);
            var adp = new OleDbDataAdapter(cmd);

            if (parameters != null)
            {
                foreach (var prm in parameters)
                {
                    cmd.Parameters.Add(new OleDbParameter()
                    {
                        Value = prm
                    });
                }
            }
            adp.Fill(table);
            try{} catch (Exception e) {
                table.Columns.Add("ERROR");
                table.Rows.Add(e.Message);
            }
            return(table);
        }
Example #20
0
    public DataSet GetDataTable(string strQuery)
    {
        DataSet tempDs   = null;
        string  filePath = Server.MapPath("Download\\" + excelFileUpload.FileName);

        {
            string          conPath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            OleDbConnection conn    = new OleDbConnection(conPath);
            try
            {
                conn.Open();

                OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
                tempDs = new DataSet();
                adapter.Fill(tempDs);
            }
            catch (Exception ex)
            {
                Response.Write("<h4>" + ex.Message);
            }
            conn.Close();
        }
        return(tempDs);
    }
Example #21
0
        private void updateLogDb()
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand     command = new OleDbCommand("SELECT * FROM LogTable", connection);
                OleDbDataAdapter Adapter = new OleDbDataAdapter(command);

                try
                {
                    connection.Open();
                    OleDbCommandBuilder cbClientOrder = new OleDbCommandBuilder(Adapter);
                    Adapter.Update(Local_LogTable);

                    connection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally {
                    connection.Close();
                }
            }
        }
Example #22
0
        private void FillComboBox()
        {
            OleDbConnection _connector = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\oksan\Desktop\AISforC#.mdb");

            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();

            try
            {
                _connector.Open();
                OleDbCommand     cmd1 = new OleDbCommand("select product_ID,product_name from Order_lines group by product_id, product_name", _connector);
                OleDbDataAdapter da1  = new OleDbDataAdapter();
                da1.SelectCommand = cmd1;
                da1.Fill(ds1);
                comboBox2.DisplayMember = "product_name";
                comboBox2.ValueMember   = "product_ID";
                comboBox2.DataSource    = ds1.Tables[0];

                OleDbCommand     cmd2 = new OleDbCommand("select order_id from Orders group by order_ID", _connector);
                OleDbDataAdapter da2  = new OleDbDataAdapter();
                da2.SelectCommand = cmd2;
                da2.Fill(ds2);
                comboBox1.DisplayMember = "order_ID";
                comboBox1.ValueMember   = "order_ID";
                comboBox1.DataSource    = ds2.Tables[0];
            }
            catch (Exception e)
            {
                MessageBox.Show("ERROR");
            }
            finally
            {
                _connector.Close();
                _connector.Dispose();
            }
        }
Example #23
0
        public void delete(Form3 f, int found)
        {
            OleDbConnection conn    = new OleDbConnection();
            String          s       = System.Environment.CurrentDirectory;
            string          connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

            connStr += @s + @"//StudentManager.mdb";
            // Console.WriteLine("当前连接字符串为:\n" + connStr + "\n");
            conn.ConnectionString = connStr;
            string              strSelectQuery = "select * from students";
            OleDbCommand        cmd            = new OleDbCommand(strSelectQuery, conn);
            OleDbDataAdapter    da             = new OleDbDataAdapter();
            OleDbCommandBuilder cb             = new OleDbCommandBuilder(da);

            da.SelectCommand = cmd;
            da.UpdateCommand = cb.GetUpdateCommand();

            DataSet result = new DataSet();

            da.Fill(result, "students");
            result.Tables[0].Rows[found].Delete();
            da.Update(result, "students");
            conn.Close();
        }
Example #24
0
 public static System.Data.DataTable ToDataTable(string DataSource, string version, OleDbCommand cmd)
 {
     try
     {
         //ClearCache();
         //Connection().Open();
         using (OleDbConnection con = Connection(DataSource, version))
         {
             using (cmd.Connection = con)
             {
                 using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                 {
                     using (System.Data.DataTable dt = new System.Data.DataTable())
                     {
                         da.Fill(dt);
                         return(dt);
                     }
                 }
             }
         }
     }
     catch (Exception) { throw; }
     finally { ConnectionClose(); }
 }
Example #25
0
        public static DataTable GetExcelData(string _FilePath, string ExcelSheetName = "", string Condition = "")
        {
            DataTable tbl             = new DataTable();
            string    excelConnString = @"provider=Microsoft.ACE.OLEDB.12.0;data source=" + _FilePath + ";extended properties=" + "\"excel 12.0;hdr=yes;IMEX=1\"";



            //Create Connection to Excel work book
            using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
            {
                ///----------------------------
                string sql = "Select * " + " from [" + ExcelSheetName + "$] ";
                if (Condition.Trim().Length > 0)
                {
                    sql += Condition;
                    //sql = sql + " where Format(" + DateColumn + ",'" + itm.MDataFormat + "') between '" + AttendanceFromDate.Value.ToString(itm.MDataFormat) + "' and '" + AttendanceToDate.Value.ToString(itm.MDataFormat) + "'";
                }
                OleDbDataAdapter da = new OleDbDataAdapter(sql, excelConnection);
                da.Fill(tbl);
            }


            return(tbl);
        }
Example #26
0
 void TryPreview()
 {
     try
     {
         lbName.Items.Clear();
         string s = "provider = Microsoft.Jet.OLEDB.4.0;" +
                    "data source = " + tbFile.Text + ";" +
                    "extended properties = Excel 8.0;";
         OleDbConnection conn  = new OleDbConnection(s);
         string          sheet = tbSheet.Text.Replace('.', '#');
         s = string.Format("SELECT * FROM [{0}${1}]",
                           sheet, tbRange.Text);
         OleDbDataAdapter da = new OleDbDataAdapter(s, conn);
         conn.Open();
         DataTable dtData = new DataTable();
         da.FillSchema(dtData, SchemaType.Source);
         conn.Close();
         foreach (DataColumn c in dtData.Columns)
         {
             lbName.Items.Add((string)c.ColumnName);
         }
     }
     catch { }
 }
Example #27
0
        void GetTables(OleDbConnection conn)
        {
            // add tables
            var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            foreach (DataRow dr in dt.Rows)
            {
                // get type (table/view)
                var type = (string)dr[TABLE_TYPE];
                if (type != TABLE && type != VIEW && type != LINK)
                {
                    continue;
                }

                // create table
                var name  = (string)dr[TABLE_NAME];
                var table = new DataTable(name);
                table.ExtendedProperties[TABLE_TYPE] = type;

                // save definition in extended properties
                foreach (DataColumn col in dt.Columns)
                {
                    table.ExtendedProperties[col.ColumnName] = dr[col];
                }

                // get table schema and add to collection
                try
                {
                    var select = GetSelectStatement(table);
                    var da     = new OleDbDataAdapter(select, conn);
                    da.FillSchema(table, SchemaType.Mapped);
                    Tables.Add(table);
                }
                catch { }
            }
        }
Example #28
0
        public bool charge(string phone, int money)
        {
            string sql = "select * from t_user WHERE 联系电话='" + phone + "'";
            //获取表1中昵称为LanQ的内容
            OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter(sql, oleDb); //创建适配对象
            DataTable        dt            = new DataTable();                  //新建表对象

            dbDataAdapter.Fill(dt);                                            //用适配对象填充表对象
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("电话不存在!");
                return(false);
            }
            foreach (DataRow item in dt.Rows)
            {
                money += Convert.ToInt32(item[6].ToString());
            }
            Form1Form2.mm.caculator = money;
            sql = "update t_user set 账户余额 ='" + money.ToString() + "'";
            OleDbCommand oleDbCommand = new OleDbCommand(sql, oleDb);
            int          i            = oleDbCommand.ExecuteNonQuery();

            return(i > 0);
        }
        private void btn_verdatos_Click(object sender, EventArgs e)
        {
            OleDbCommand com = new OleDbCommand();

            try
            {
                OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb");
                com.Connection = cnn;
                cnn.Open();
                string query = "select Producto, Descripcion, Cantidad, Precio from ProductoTB";
                com.CommandText = query;

                OleDbDataAdapter da = new OleDbDataAdapter(com);
                DataTable        dt = new DataTable();
                da.Fill(dt);
                dataverproductos.DataSource = dt;

                com.Clone();
            }
            catch (Exception ex)
            {
                MessageBox.Show("No se pudo llenar" + ex.ToString());
            }
        }
Example #30
0
    public DataTable JobDetails(string JobId)
    {
        OleDbConnection con = new OleDbConnection(constring);

        con.Open();
        try
        {
            OleDbCommand cmd = new OleDbCommand("Sp_Career_Sites_JobDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@JobId", JobId);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet          ds = new DataSet();
            da.Fill(ds);
            return(ds.Tables[0]);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }
Example #31
0
        public int Update(ViewDataSet.品號ViewDataTable table)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();

            adapter.UpdateCommand = UpdateCommand;
            adapter.InsertCommand = InsertCommand;
            adapter.DeleteCommand = DeleteCommand;

            this.Connection.Open();
            OleDbTransaction trans = this.Connection.BeginTransaction();
            adapter.InsertCommand.Transaction = trans;
            adapter.UpdateCommand.Transaction = trans;
            adapter.DeleteCommand.Transaction = trans;

            try
            {
                int count = 0;
                count = adapter.Update(table);
                trans.Commit();
                return count;
            }
            catch (System.Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                this.Connection.Close();
            }
        }
Example #32
0
        public int Update(DatabaseSet.假日ViewDataTable table)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();

            adapter.UpdateCommand = UpdateCommand;
            adapter.InsertCommand = InsertCommand;
            adapter.DeleteCommand = DeleteCommand;

            this.Connection.Open();
            OleDbTransaction trans = this.Connection.BeginTransaction();
            adapter.InsertCommand.Transaction = trans;
            adapter.UpdateCommand.Transaction = trans;
            adapter.DeleteCommand.Transaction = trans;

            try
            {
                int count = 0;
                foreach (DatabaseSet.假日ViewRow row in table)
                {
                    switch (row.RowState)
                    {
                        case DataRowState.Added:
                            //如果要加入週六日
                            if (row.週六日)
                            {
                                adapter.DeleteCommand.Parameters["日期"].Value = row.日期;
                                count += adapter.DeleteCommand.ExecuteNonQuery();
                            }
                            else
                            {
                                adapter.InsertCommand.Parameters["日期"].Value = row.日期;
                                adapter.InsertCommand.Parameters["增加"].Value = true;
                                count += adapter.InsertCommand.ExecuteNonQuery();
                            }
                            break;
                        case DataRowState.Deleted:
                            //如果要移除週六日
                            if ((bool)row["週六日", DataRowVersion.Original])
                            {
                                adapter.InsertCommand.Parameters["日期"].Value = row["日期", DataRowVersion.Original];
                                adapter.InsertCommand.Parameters["增加"].Value = false;
                                count += adapter.InsertCommand.ExecuteNonQuery();
                            }
                            else
                            {
                                adapter.DeleteCommand.Parameters["日期"].Value = row["日期", DataRowVersion.Original];
                                count += adapter.DeleteCommand.ExecuteNonQuery();
                            }
                            break;
                        case DataRowState.Modified:
                            //adapter.UpdateCommand.Parameters["日期"].Value = row.日期;
                            //adapter.UpdateCommand.Parameters["Original_日期"].Value = row["日期", DataRowVersion.Original];
                            //count += adapter.UpdateCommand.ExecuteNonQuery();
                            break;
                        default:
                            break;
                    }
                }

                trans.Commit();
                return count;
            }
            catch (System.Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                this.Connection.Close();
            }
        }
Example #33
0
            /// <summary>
            /// Initialize variables and sets the connectionstring and select query string.
            /// </summary>
            /// <param name="connString">Connection String.</param>
            /// <param name="sqlSelectString">Query String.</param>
            public OleDbRecordsetClass(String connString, String sqlSelectString)
            {
                this.dbConnection = new OleDbConnection(connString);
                this.SqlQuery = sqlSelectString;
                this.operationFinished = false;
                this.dbAdapter = CreateAdapter(SqlQuery, dbConnection);
                this.dbDataSet = new DataSet();
                this.dbTable = new DataTable();
                this.dbTable = dbDataSet.Tables.Add("Table");
                this.dbAdapter.Fill(this.dbTable);
                CheckUpdateCommandsFromMetaData();
                this.operationFinished = true;
                this.newRow = false;
                this.newRowChanged = false;
                this.foundRecordsCounter = 0;
                this.OnAfterQuery();
                classFields = new FieldsClass(this);

                SetInitialPosition();
            }
Example #34
0
        public DataSet fetch(string query)
        {
            OleDbTransaction trans = this.conn.BeginTransaction();

            OleDbCommand comm = new OleDbCommand();
            comm.CommandText = query;
            comm.Connection = this.conn;
            comm.Transaction = trans;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = comm;
            DataSet ds = new DataSet();
            adapter.Fill(ds, "tbl_0");

            trans.Commit();

            return ds;
        }
Example #35
0
            /// <summary>
            /// Initialize variables and sets the connection string.
            /// </summary>
            /// <param name="connString">string</param>
            public OleDbRecordsetClass(String connString)
            {
                dbConnection = new OleDbConnection(connString);
                dbAdapter = new OleDbDataAdapter();
                dbDataSet = new DataSet();
                newRow = false;
                newRowChanged = false;
                foundRecordsCounter = 0;
                classFields = new FieldsClass(this);

                SetInitialPosition();
            }