public static int SaveRecord(string sql)
        {
            const int rv = 0;
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
                using (var conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
                    var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
                    comm.ExecuteNonQuery();

                    var ds = new DataSet();
                    var adapt = new OleDbDataAdapter(cmGetID);
                    adapt.Fill(ds);
                    adapt.Dispose();
                    cmGetID.Dispose();

                    return int.Parse(ds.Tables[0].Rows[0][0].ToString());

                }
            }
            catch (Exception)
            {
            }
            return rv;
        }

        //計算每個點的Frequency Table
        private void BFT_Click(object sender, EventArgs e)
        {
            DataTable FreqTable = new DataTable();
            GetDataSet.Open();
            OleDbDataAdapter dafretable = new OleDbDataAdapter("SELECT * FROM [01.csv]", GetDataSet);
            dafretable.Fill(FreqTable);

            foreach (DataRow row in FreqTable.Rows)
            {
                node[(int)row[0]].Visit[(int)row[1]] = node[(int)row[0]].Visit[(int)row[1]] + 1;  //計算每個點拜訪每個地點分別次數
                node[(int)row[0]].VTotal = node[(int)row[0]].VTotal + 1;                          //計算每個點總拜訪次數
            }

            for (int x = 1; x < 100; x++)
            {
                for (int y = 1; y < (locanum + 1); y++)
                {
                    if (node[x].VTotal != 0)
                    {
                        node[x].FTable[y] = (node[x].Visit[y] / node[x].VTotal) * 10;                  //計算頻率
                    }

                }
            }
            //label1.Text = node[121].FTable[363].ToString();
            GetDataSet.Close();
            state.Text = "BFT over.";
        }

        private void cbxclub_DropDownOpened(object sender, EventArgs e)
        {
            try
            {
                if (cbxseason.SelectedItem == null || cbxzone.SelectedItem == null || cbxdivision.SelectedItem == null)
                {
                    MessageBox.Show("Select Season, Zone, Division");
                }

                string strRetrieve = "";
                strRetrieve = "select * from clubdetails";
                //  cbxclub.Items.Clear();
                OleDbDataAdapter adpt = new OleDbDataAdapter(strRetrieve, Database.getConnection());
                DataTable dt = new DataTable();
                adpt.Fill(dt);

                foreach (DataRowView dr in dt.DefaultView)
                {
                    //   List<Team> lstFilter = Database.GetEntityList<Team>( false, false,false,Database.getConnection(),RecordStatus true);
                    //cbxclub.Items.Add(dr["ClubName"]);
                    //cbxteam.Items.Refresh();

                    if (!cbxclub.Items.Contains(dr["ClubName"]))// For remove list duplicacy
                    {
                        cbxclub.Items.Add(dr["ClubName"]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Create an Excel file with 2 columns: name and score:
        //Write a program that reads your MS Excel file through
        //the OLE DB data provider and displays the name and score row by row.

        static void Main()
        {
            OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=D:\Telerik\DataBases\HW\ADONET\06. ReadExcel\Table.xlsx;Extended Properties=""Excel 12.0 XML;HDR=Yes""");
            OleDbCommand myCommand = new OleDbCommand("select * from [Sheet1$]", dbConn);
            dbConn.Open();
            //First way
            //using (dbConn) - I think it is better to use dbConn in using clause, but for the demo issues i dont use using.
            //{
            OleDbDataReader reader = myCommand.ExecuteReader();

            while (reader.Read())
            {
                string name = (string)reader["Name"];
                double score = (double)reader["Score"];
                Console.WriteLine("{0} - score: {1}", name, score);
            }
            //}

            dbConn.Close();
            //Second way
            dbConn.Open();
            Console.WriteLine();
            Console.WriteLine("Second Way");
            Console.WriteLine("----------");
            DataTable dataSet = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", dbConn);
            adapter.Fill(dataSet);

            foreach (DataRow item in dataSet.Rows)
            {
                Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]);
            }
            dbConn.Close();
        }

        private static void PrintSpreadsheet(OleDbConnectionStringBuilder connestionString)
        {
            DataSet sheet1 = new DataSet();
            using (OleDbConnection connection = new OleDbConnection(connestionString.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet1$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.Fill(sheet1);
                }
                connection.Close();
            }
            
            var table = sheet1.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.Write("{0, -20} ", row[column]);
                }

                Console.WriteLine();
            }
        }

        private void FormFirma_Load(object sender, EventArgs e)
        {
            PutBaze = System.IO.File.ReadAllText(Application.StartupPath + "\\Ponuda\\BazaPonuda.txt");
            string PutKriterij =System.IO.File.ReadAllText(@"privPonuda.txt");

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+PutBaze;

            //----------SQL instrukcija-----------\\

            string sql = "SELECT * FROM sve WHERE Firma LIKE '" + PutKriterij + "%'";

            //klase za povezivanje na ACCESS bazu podataka//
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            //\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\

             ds = new DataSet();         //kreira noi objekt(kopiju) DataSet()-a
            conn.Open();                        //otvara spoj s bazom podataka

            adapter.Fill(ds, "sve");       //puni se DataSet s podacima tabele t_razred
            conn.Close();                       //zatvara se baza podataka

            //nakon zatvaanja BP imamo odgovarajuće podatke u ds objektu ( DataSet() )
            dataGridView2.DataSource = ds;      //upisivanje podataka id ds u dataGridView2
            dataGridView2.DataMember = "sve";
            ukupnaCijenaDataGridViewTextBoxColumn.DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("de-DE");

            this.dataGridView2.Sort(firmaDataGridViewTextBoxColumn, ListSortDirection.Ascending);
            System.IO.File.Delete(@"privPonuda.txt");
        }

 /// <summary>
 /// 执行查询语句，返回DataSet
 /// </summary>
 /// <param name="SQLString">查询语句</param>
 /// <returns>DataSet</returns>
 public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
 {
     using (OleDbConnection connection = new OleDbConnection(connectionString))
     {
         OleDbCommand cmd = new OleDbCommand();
         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
         using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             try
             {
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
             }
             catch (OleDbException e)
             {
                 //LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
                 throw;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
             return ds;
         }
     }
 }

        private void Autocomplete()
        {
            try
            {
                con = new OleDbConnection(cs);
                con.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT distinct ProductName FROM product", con);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds, "Product");
                AutoCompleteStringCollection col = new AutoCompleteStringCollection();
                int i = 0;
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    col.Add(ds.Tables[0].Rows[i]["productname"].ToString());

                }
                txtProductName.AutoCompleteSource = AutoCompleteSource.CustomSource;
                txtProductName.AutoCompleteCustomSource = col;
                txtProductName.AutoCompleteMode = AutoCompleteMode.Suggest;

                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /// <summary>
        /// Opens an Excel file and imports worksheet into a DataTable.  Worksheet Name is required.
        /// </summary>
        public static DataTable Build(string FilePath, string WorksheetName)
        {
            if (Path.GetExtension(FilePath) == ".xls")
            {
                // If anything goes wrong, "using" will force disposal of the connection to the file
                using (OleDbConnection conn = BuildExcelConnection(FilePath))
                {
                    // "Connect" to the XLS file
                    conn.Open();

                    // Get a DataAdapter to the query
                    string ExcelQuery = String.Format(SelectWorksheetQueryTemplate, WorksheetName);
                    OleDbDataAdapter Adapter = new OleDbDataAdapter(ExcelQuery, conn);

                    // Populate DataTable using DataAdapter
                    DataTable dataTable = new DataTable();
                    Adapter.Fill(dataTable);

                    // Close the connection
                    conn.Close();

                    // Finished
                    return dataTable;
                }
            }
            if (Path.GetExtension(FilePath) == ".xlsx")
            {
                return OpenXmlExcelToDataTableBuilder.Build(FilePath, WorksheetName);
            }

            throw new ArgumentException("Invalid file extension specified on Excel data file:" + FilePath);
        }

        /// <summary>
        /// 从EXCEL中获取数据(放入dataset中)
        /// </summary>
        /// <param name="postedFile"></param>
        /// <param name="context"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet GetDataFromUploadFile(this HttpPostedFile postedFile, HttpContext context, string tableName)
        {
            string directory = context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY);

            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            string filename = postedFile.FileName;
            //将文件上传至服务器
            postedFile.SaveAs(context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename);

            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

            //string sqlin = "SELECT * FROM [" + "ConstructPlanDevice" + "$]";
            //OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn));

            //OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand);
            //DataSet dsIn = new DataSet();
            //adapterIn.Fill(dsIn, tableName);

            OleDbConnection conn1 = new OleDbConnection(conn);
            conn1.Open();
            string name = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"]", conn1);
            DataSet dsIn1 = new DataSet();
            odda.Fill(dsIn1, tableName);
            conn1.Close();
            return dsIn1;
        }

 /// <summary>
 /// initialization casting for InitializeDataAccess()
 /// </summary>
 /// <param name="type"></param>
 /// <param name="ConnectionString"></param>
 /// <param name="Query"></param>
 private void castProvider(ProviderType type, string ConnectionString, string Query = null)
 {
     switch (type)
     {
         case ProviderType.Oledb:
             conn = new OleDbConnection(ConnectionString);
             cmd = new OleDbCommand(Query, (OleDbConnection)conn);
             da = new OleDbDataAdapter();
             break;
         case ProviderType.Odbc:
             conn = new OdbcConnection(ConnectionString);
             cmd = new OdbcCommand(Query, (OdbcConnection)conn);
             da = new OdbcDataAdapter();
             break;
         case ProviderType.SqlClient:
             conn = new SqlConnection(ConnectionString);
             cmd = new SqlCommand(Query, (SqlConnection)conn);
             da = new SqlDataAdapter();
             break;
         //case ProviderType.OracleClient:
         //    conn = new OracleConnection(ConnectionString);
         //    cmd = new OracleCommand(Query,(OracleConnection)conn);
         //    break;
     }
 }

        public Empresa ObtenerEmpresa(string RUC)
        {
            OleDbConnection obj_Conexion = new OleDbConnection();
            OleDbCommand obj_Comando = new OleDbCommand();
            OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
            DataSet obj_Data = new DataSet();
            obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
            obj_Conexion.Open();
            obj_Comando.Connection = obj_Conexion;
            obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc ='" + RUC + "'";
            obj_Comando.CommandType = CommandType.Text;
            obj_Adapter.SelectCommand = obj_Comando;
            obj_Adapter.Fill(obj_Data);
            obj_Conexion.Close();

            Empresa empresa = new Empresa(); ;
            foreach (var dr_Fila in obj_Data.Tables[0].Rows)
            {
                DataRow fila = (DataRow)dr_Fila;
                empresa.RUC = fila[0].ToString();
                empresa.nombrecomercial = fila[1].ToString();
                empresa.direccion = fila[5].ToString();
                empresa.telefono = fila[6].ToString();
                empresa.Estado = fila[3].ToString();
            }

            return empresa;
        }

        public ICollection<Empresa> ConsultarEmpresa(string RUC, string nombreComercial)
        {
            OleDbConnection obj_Conexion = new OleDbConnection();
            OleDbCommand obj_Comando = new OleDbCommand();
            OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
            DataSet obj_Data = new DataSet();
            obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
            obj_Conexion.Open();
            obj_Comando.Connection = obj_Conexion;
            if (RUC == null && nombreComercial == null)
                obj_Comando.CommandText = "Select * From Contribuyentes";
            else if (RUC != null && nombreComercial == null)
                obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc Like '%" + RUC + "%'";
            else
                obj_Comando.CommandText = "Select * From Contribuyentes Where RazonSocial Like '%" + nombreComercial + "%'";
            obj_Comando.CommandType = CommandType.Text;
            obj_Adapter.SelectCommand = obj_Comando;
            obj_Adapter.Fill(obj_Data);
            obj_Conexion.Close();

            List<Empresa> empresas = new List<Empresa>();
            Empresa empresa;
            foreach (var dr_Fila in obj_Data.Tables[0].Rows)
            {
                DataRow fila = (DataRow)dr_Fila;
                empresa = new Empresa();
                empresa.RUC = fila[0].ToString();
                empresa.nombrecomercial = fila[1].ToString();
                empresa.Estado = fila[3].ToString();
                empresas.Add(empresa);
            }

            return empresas;
        }

 public DataView ExceltoDataView(string strFilePath)
 {
     DataView dv;
     try
     {
         OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
         conn.Open();
         object[] CSs0s0001 = new object[4];
         CSs0s0001[3] = "TABLE";
         DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, CSs0s0001);
         string tableName = Convert.ToString(tblSchema.Rows[0]["TABLE_NAME"]);
         if (tblSchema.Rows.Count > 1)
         {
             tableName = "sheet1$";
         }
         string sql_F = "SELECT * FROM [{0}]";
         OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sql_F, tableName), conn);
         DataSet ds = new DataSet();
         adp.Fill(ds, "Excel");
         dv = ds.Tables[0].DefaultView;
         conn.Close();
     }
     catch (Exception)
     {
         Exception strEx = new Exception("請確認是否使用模板上傳(上傳的Excel中第一個工作表名稱是否為Sheet1)");
         throw strEx;
     }
     return dv;
 }

        public static DataTable GetExcelDataTable(string filePath, string sql)
        {
            try
            {
                //Office 2003
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;Readonly=0'");

                //Office 2007
                OleDbConnection conn =
                    new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                                        ";Extended Properties='Excel 12.0 Xml;HDR=YES'");

                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dt.TableName = "tmp";
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                MessageBox.Show("Please input correct SQL Command for your file!");
                return null;
            }
        }

        private void clears()
        {
            txtNameSearch.Text = "Enter all or part of a name here...";
            txtCCSearch.Text = "Scan a customer card here...";

            try
            {
                connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
                string query = "select * from customer order by last_name;";

                OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                DataTable dt = new DataTable();

                da.Fill(dt);

                BindingSource bs = new BindingSource();
                bs.DataSource = dt;

                dgvCustomers.DataSource = bs;

                da.Update(dt);

                databaseHandler d = new databaseHandler();
                d.closeDatabaseConnection();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }

        private void DisplayStudents_Load(object sender, EventArgs e)
        {
            connection = new OleDbConnection();
            command = new OleDbCommand();
            adapter = new OleDbDataAdapter();
            dataset = new DataSet();

            connection.ConnectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Students.accdb;" +
                "Persist Security Info=False";

            command.Connection = connection;
            command.CommandText = "SELECT * FROM Student";

            adapter.SelectCommand = command;

            try
            {
                adapter.Fill(dataset, "Student");
            }
            catch (OleDbException)
            {
                MessageBox.Show("Error occured while connecting to database.");
                Application.Exit();
            }

            PopulateFields(0);
        }

            public static void StateData()
            {
                var count = 0;

                var datafile = AppDomain.CurrentDomain.BaseDirectory + @"data\StateData.xlsx";

                var connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + datafile + @"; Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                var adapter = new OleDbDataAdapter("select * from [states$]", connStr);
                var ds = new DataSet();
                adapter.Fill(ds, "states");

                foreach (DataRow r in ds.Tables["states"].Rows)
                {
                    var code = r.ItemArray[0].ToString();
                    var value = r.ItemArray[1].ToString();
                    var capitol = r.ItemArray[2].ToString();
                    var population = r.ItemArray[3].ToString();
                    var squaremiles = r.ItemArray[4].ToString();

                    var entity = new Entity("States", code, value, SequenceType.ALPHA_ASC);
                    entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Capitol", capitol));
                    entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Population", population));
                    entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Square Miles", squaremiles));
                    entity.link = "US";

                    var json = ModelManager.ModelToJson<Entity>(entity);
                    var filename = AppDomain.CurrentDomain.BaseDirectory + @"data\state" + count.ToString() + ".json"; 
                    System.IO.File.WriteAllText(filename, json);

                    Console.WriteLine(count + " Entity: " + entity.code + " | " + entity.codevalue + " | " + capitol + " | " + population + " | " + squaremiles);

                    count++;
                }
            }

        public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow)
        {
            var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName);

            using (var excelConnection = new OleDbConnection(connectionString))
            {
                excelConnection.Open();

                if (sheetName == null)
                {
                    var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (excelSchema != null)
                    {
                        sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                    }
                }

                var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);

                using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
                {
                    var dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            actionForEachRow(reader);
                        }
                    }
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
                {
                // Mo hop thoai Dialog de tim den file Excel
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Title = "Chon file chua danh sach so dien thoai";
                ofd.Filter = "Các file Excel (*.xls) và *.txt|*.xls|*.txt|";
                ofd.ShowDialog();

                // Khai bao chuoi ket noi csdl
                string ConnectionString;

                ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; " + "data source='" + ofd.FileName + "'; ";


                // Tao doi tuong ket noi
                OleDbConnection cn = new OleDbConnection(ConnectionString);
                cn.Open();

                // Tao doi tuong thuc thi cau lenh
                OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$] ", cn);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds, "Danhsach");

                // gan du lieu vao dieu khien
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Danhsach";

                }
                catch (Exception ex) { MessageBox.Show("Khong ket noi duoc CSDL: " + ex.Message); }

                }