C# OleDbConnectionStringBuilder.Add方法代码示例

        private static void ReadFromExcel()
        {
            DataTable dt = new DataTable("table");
            OleDbConnectionStringBuilder csBuilder = new OleDbConnectionStringBuilder();
            csBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csBuilder.DataSource = @"..\..\Table.xlsx";
            csBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection  = new OleDbConnection(csBuilder.ConnectionString))
            {
                connection.Open();
                string query = @"SELECT * FROM Sample";

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }

        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            try
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
                builder.Add("Provider", Properties.Settings.Default.provider);
                builder.Add("Data Source", Properties.Settings.Default.path);

                string connString = builder.ConnectionString;
                OleDbConnection conn = new OleDbConnection(connString);

                conn.Open();
                conn.Close();

                LoadingScreen splashy = new LoadingScreen();
                splashy.Show();

                Application.Run(new Form1(splashy));

                /*Form1 f = new Form1();
                f.Show();*/

            }
            catch
            {
                Application.Run(new ConnectionDefinition());
                /*ConnectionDefinition c = new ConnectionDefinition();
                c.Show();*/
            }

            //Application.Run(/*new ConnectionDefinition()*/);
        }

        public static string GetConnectionString(string fileName)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

            string[] newExtensions = new string[]
            {
                ".xlsx", ".xlsb", ".xlsm"
            };
            string[] oldExtensions = new string[]
            {
                ".xls"
            };
            string extension = Path.GetExtension(fileName);
            if (newExtensions.Contains(extension))
            {
                builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                builder.Add("Extended Properties", "Excel 12.0 Xml; HDR=No; READONLY=true; IMEX=1");
            }
            else if (oldExtensions.Contains(extension))
            {
                builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                builder.Add("Extended Properties", "Excel 8.0; HDR=No; READONLY=true; IMEX=1");
            }
            else
            {
                throw new ArgumentException(Resources.UnknownExcelExtension, "fileName");
            }

            builder.DataSource = fileName;
            return builder.ConnectionString;
        }

        private void btPotvrdi_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(tbPutanja.Text) || (op.CheckPathExists == false || op.CheckFileExists == false))
            {
                MessageBox.Show("Proverite da li ste uneli sve podatke kako treba!", "Greška!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            string newConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbPutanja.Text;

            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

            builder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
            builder.Add("Data source", tbPutanja.Text);

            Program.ChangeConnectionString(builder.DataSource, builder.Provider);
            Properties.Settings.Default.Save();

            try
            {
                OleDbConnection dbConn = new OleDbConnection(builder.ConnectionString);
                dbConn.Open();
                dbConn.Close();
            }
            catch
            {
                MessageBox.Show("Lokacija baze ili naziv fajla nije ispravan! Moraćete ponovo da je definišete.", "Informacija", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            LoadingScreen s = new LoadingScreen();
            Form1 f = new Form1(s);
            this.Hide();
            f.Show();
        }

        public NoviPacijent()
        {
            InitializeComponent();

            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
            builder.Add("Provider", Properties.Settings.Default.provider);
            builder.Add("Data Source", Properties.Settings.Default.path);
            connString = builder.ConnectionString;
        }

        private static void InputDataFromExl(string directoryPath, string excelFileName)
        {
            DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = directoryPath + ReportsDirectory + excelFileName;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }

                connection.Close();
            }

            Console.WriteLine(dt.Rows[0].ItemArray[0]);
            int rowsCount = dt.Rows.Count - 1;
            for (int i = 2; i < rowsCount; i++)
            {
                foreach (var item in dt.Rows[i].ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }

        public static IEnumerable<string[]> GetReportsData(string zipPath, string extractPath)
        {
            if (Directory.Exists(extractPath))
            {
                Directory.Delete(extractPath, true);
            }

            using (ZipFile archive = ZipFile.Read(zipPath))
            {
                archive.ExtractAll(extractPath, ExtractExistingFileAction.OverwriteSilently);
            }

            IEnumerable<string> excelFiles = DirSearch(extractPath);

            var connectionString = new OleDbConnectionStringBuilder();
            connectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
            connectionString.Add("Extended Properties", "Excel 12.0");

            var sales = new List<String[]>();
            foreach (var file in excelFiles)
            {
                connectionString.DataSource = file;

                // Get the date from the name of the file.
                string pattern = "-Sales-Report-";
                string salesDate = file.Substring(file.LastIndexOf(pattern) + pattern.Length);
                string supermarket = Path.GetFileNameWithoutExtension(file.Substring(0, file.LastIndexOf(pattern)));
                salesDate = salesDate.Substring(0, salesDate.IndexOf(".xls"));

                sales.AddRange(ReadOneExcelFile(connectionString, supermarket, salesDate));
            }
            Directory.Delete(extractPath, true);
            return sales;
        }

        static void Main(string[] args)
        {
            var fileName = string.Format("{0}\\..\\..\\..\\file.xlsx", Directory.GetCurrentDirectory());

            DataSet sheet1 = new DataSet();
            OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder();
            conString.Provider = "Microsoft.ACE.OLEDB.12.0";
            conString.DataSource = fileName;
            conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(conString.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, 15} ", row[column]);
                }

                Console.WriteLine();
            }
        }

    static void Main(string[] args)
    {
        DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = @"..\..\Table.xlsx";
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet2$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
    }

        private static void Main()
        {
            Console.WriteLine("Name:");
            string name = Console.ReadLine();
            Console.WriteLine("Salary for {0}:", name);
            string salary = Console.ReadLine();

            var connectionBuilder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0;"
            };
            connectionBuilder.Add("Extended Properties", "Excel 12.0 XML");
            connectionBuilder.DataSource = File;

            var connectToExcel = new OleDbConnection(connectionBuilder.ConnectionString);

            var cmdAppendRow = new OleDbCommand("INSERT INTO [Sheet1$](FullName, Salary) VALUES('" + name + "','" + salary + "')", connectToExcel);

            try
            {
                connectToExcel.Open();

                using (connectToExcel)
                {
                    int rowsAffected = cmdAppendRow.ExecuteNonQuery();
                    Console.WriteLine("{0} rows affected", rowsAffected);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private static void Main()
        {
            var connectionBuilder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0;"
            };

            connectionBuilder.Add("Extended Properties", "Excel 12.0 XML");
            connectionBuilder.DataSource = File;

            var connectToExcel = new OleDbConnection(connectionBuilder.ConnectionString);

            var cmdToExcel = new OleDbCommand("SELECT * FROM [Sheet1$]", connectToExcel);

            connectToExcel.Open();
            using (connectToExcel)
            {
                var reader = cmdToExcel.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    var name = (string)reader["FullName"];
                    var salary = (double)reader["Salary"];
                    Console.WriteLine("{0}: {1}", name, salary);
                }
            }
        }

    private void ConvertDBFToCSV(string dbfPath)
    {
        if (File.Exists(dbfPath))
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder()
            {
                DataSource = Path.GetDirectoryName(dbfPath),
                Provider = "Microsoft.Jet.OLEDB.4.0"
            };

            builder.Add("Extended Properties", "dBase III");

            using (OleDbConnection connection = new OleDbConnection() { ConnectionString = "Provider=vfpoledb;Data Source=" + dbfPath + ";Collating Sequence=machine;" })
            {
                using (OleDbCommand cmd = new OleDbCommand() { Connection = connection })
                {
                    cmd.CommandText = "SELECT MR_BKNO,CONS_ACC,COL_DATE,COL_DATE,AMT_PAID,MR_RPNO FROM [" + Path.GetFileName(dbfPath) + "] where AMT_PAID<>0.00 and CONS_ACC<>" + (char)34 + " " + (char)34;
                    connection.Open();

                    DataTable dt = new DataTable();
                    dt.Load(cmd.ExecuteReader());

                    string csvData = string.Empty;
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            csvData +=
                                "NULL" + "}" + //SBM ID
                                "NULL" + "}" + //Collector Name
                                //"NULL" + "}" + //Collector ID
                                row[0].ToString().Trim() + "}" +
                                row[1].ToString().Trim() + "}" +
                                row[2].ToString().Trim().Replace(" 00.00.00", "") + "}" +
                                row[3].ToString().Trim().Replace(" 00.00.00", "") + "}" +
                                row[4].ToString().Trim() + "}" +
                                "NULL" + "}" + //Receipet No.
                                "NULL" + "}" + //Cheque No.
                                "01/01/2001" + "}" + //Cheque Date.
                                "NULL" + "}" + //BankNameCode
                                "NULL" + "}" + //M<anual Book No
                                row[5].ToString().Trim() +
                                "}Cash}0"
                                + Environment.NewLine;
                        }
                    }

                    connection.Close();

                    string csvFilePath = Server.MapPath("Uploads") + "//CSV//" + Path.GetFileNameWithoutExtension(dbfPath) + ".csv";

                    File.WriteAllText(csvFilePath, csvData);

                    UploadCSV(csvFilePath);
                }
            }
        }
    }

        /// <summary>
        /// Excel导入Access
        /// </summary>
        public void Excel2Access(string[] filenames)
        {
            try
            {
                for (int j = 0; j < filenames.Length; j++)
                {
                    OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
                    connectStringBuilder.DataSource = filenames[j];
                    connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
                    connectStringBuilder.Add("Extended Properties", "Excel 8.0");
                    using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
                    {
                        DataSet ds = new DataSet();
                        string sql = string.Format("select * from [{0}$]", sheetName);
                        OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
                        cn.Open();
                        using (OleDbDataReader dr = cmdLiming.ExecuteReader())
                        {
                            ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
                            DataTable dt = ds.Tables[sheetName];
                            if (dt.Rows.Count > 0)
                            {
                                for (int i = 4; i < dt.Rows.Count; i++)
                                {

                                    //写入数据库数据
                                    if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
                                    {
                                        string MySql = "insert into TB_Car (PlateNumber,Color,Brand,UseYear,MotorcyleType) values('" + dt.Rows[i][1].ToString() + "','" +
                                            dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" +
                                            dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "')";
                                        AccessHelper.SQLExecute(MySql);
                                    }

                                }
                                MessageBox.Show("数据导入成功！");

                                CarBLL carBLL = new CarBLL();
                                DataTable carList = carBLL.FindAll_infos(getblacklistsql);

                                dgvCarList.DataSource = carList;

                            }
                            else
                            {
                                MessageBox.Show("请检查你的Excel中是否存在数据");
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        /// <summary>
        /// Excel导入Access
        /// </summary>
        public void Excel2Access(string[] filenames)
        {
            try
            {
                for (int j = 0; j < filenames.Length; j++)
                {
                    OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
                    connectStringBuilder.DataSource = filenames[j];
                    connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
                    connectStringBuilder.Add("Extended Properties", "Excel 8.0");
                    using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
                    {
                        DataSet ds = new DataSet();
                        string sql = string.Format("select * from [{0}$]", sheetName);
                        OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
                        cn.Open();
                        using (OleDbDataReader dr = cmdLiming.ExecuteReader())
                        {
                            ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
                            DataTable dt = ds.Tables[sheetName];
                            if (dt.Rows.Count > 0)
                            {
                                for (int i = 4; i < dt.Rows.Count; i++)
                                {

                                    //写入数据库数据
                                    if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
                                    {
                                        string MySql = "insert into TB_BlacakList (User_ChineseName,User_EnglishName,Department_ChineseName,Department_EnglishName,Job,Sex,Birthdy,Identification_Type,Identification_Number,Employer,TELEPHONE) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
                                        AccessHelper.SQLExecute(MySql);
                                    }

                                }
                                MessageBox.Show("数据导入成功！");
                                string getblacklistsql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_BlacakList ";
                                DriverListBLL driverListBll = new DriverListBLL();
                                DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);

                                dgvBlackList.DataSource = dtDriverList;
                                dgvBlackList.Rows[0].Selected = false;

                            }
                            else
                            {
                                MessageBox.Show("请检查你的Excel中是否存在数据");
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        //----------------------------------
        public ExcelFile(string _filePath)
        {
            //FilePath = _filePath;

            csbuilder = new OleDbConnectionStringBuilder();

            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = _filePath;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml; HDR=YES");
        }