Exemplo n.º 1
0
        private void ReadExcel(string i_ExcelPath, string i_SheetName)
        {
            DataTable dataTable = new DataTable();
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
            {
                Provider   = "Microsoft.ACE.OLEDB.12.0;",
                DataSource = i_ExcelPath
            };

            if (i_ExcelPath.CompareTo(".xls") == 0)
            {
                builder.Add("Extended Properties", "Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';");
            }
            else
            {
                builder.Add("Extended Properties", "Extended Properties='Excel 12.0;HDR=NO';");
            }
            using (OleDbConnection connection = new OleDbConnection(builder.ConnectionString))
            {
                connection.Open();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + i_SheetName + "]", connection))
                {
                    adapter.Fill(dataTable);
                }
                connection.Close();
            }
        }
Exemplo n.º 2
0
        public static string GenerateConnectionString(ExcelFile excel)
        {
            if (excel == null)
            {
                throw new ExcelFileException($"Ссылна на объект {nameof(excel)} равна NULL ");
            }
            string ext = excel.Extension.ToLower();
            OleDbConnectionStringBuilder strcon = new OleDbConnectionStringBuilder();

            switch (ext)
            {
            case ".xlsx":
            {
                strcon.Add("Provider", $"{OleDbProvider}");
                strcon.Add("Extended Properties", "Excel 12.0;HDR=YES;IMEX=1");
                break;
            }

            case ".xls":
            {
                strcon.Add("Provider", $"{OleDbProvider}");
                strcon.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=1");
                break;
            }

            default:
                throw new Exception("Неверный формат файла.");
            }
            strcon.Add("Data Source", excel.FullPath);
            ConnectionStringGenerated?.Invoke(null, strcon.ToString());
            return(strcon.ToString());
        }
        bool CargarHojasCombo()
        {
            try
            {
                OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
                cb.DataSource = ruta;

                if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLS")
                {
                    cb.Provider = "Microsoft.Jet.OLEDB.4.0";
                    cb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=0;");
                }
                else if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLSX")
                {
                    cb.Provider = "Microsoft.ACE.OLEDB.12.0";
                    cb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=0;");
                }

                dt.Clear();
                bool flag = false;
                //   String strconn2 = "";
                //  strconn2 = "Provider=Microsoft.ACE.OLEDB.8.0;Data Source=" + ruta + ";Extended Properties=Excel 8.0;";
                // OleDbConnection mconn2 = new OleDbConnection(strconn2);
                OleDbConnection mconn2 = new OleDbConnection(cb.ConnectionString);
                //abre una conexion de tipo oledb
                mconn2.Open();
                dt = mconn2.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //Lo agrega a mi datatable
                if (dt != null)
                {
                    String[] excelSheets = new String[dt.Rows.Count];
                    int      i           = 0;

                    // Add the sheet name to the string array.
                    cmbHoja.Items.Clear();
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString();
                        cmbHoja.Items.Add(excelSheets[i].Substring(0, excelSheets[i].Length - 1)); //$
                        i++;
                    }
                    cmbHoja.SelectedIndex = 0;
                    //cierra una conexion de tipo oledb
                    flag = true;
                }
                else
                {
                    flag = false;
                }
                mconn2.Close();
                return(flag);
            }
            catch (Exception ex)
            {
                Log_Error_bus.Log_Error(ex.ToString());
                MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                dt = new DataTable();
                return(false);
            }
        }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
0
    static void Main()
    {
        OleDbConnectionStringBuilder builder =
            new OleDbConnectionStringBuilder();

        builder.ConnectionString = @"Data Source=c:\Sample.mdb";

        // Call the Add method to explicitly add key/value
        // pairs to the internal collection.
        builder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
        builder.Add("Jet OLEDB:Database Password", "MyPassword!");
        builder.Add("Jet OLEDB:System Database", @"C:\Workgroup.mdb");

        // set up row-level locking.
        builder.Add("Jet OLEDB:Database Locking Mode", 1);

        // Dump the contents of the "filled-in"
        // OleDbConnectionStringBuilder
        // to the console window.
        DumpBuilderContents(builder);

        // Clear current values and reset known keys to their
        // default values.
        builder.Clear();

        // Dump the contents of the newly emptied
        // OleDbConnectionStringBuilder
        // to the console window.
        DumpBuilderContents(builder);

        Console.WriteLine("Press Enter to continue.");
        Console.ReadLine();
    }
Exemplo n.º 6
0
        protected override string GetConnectionString()
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(FConnectionString);

            builder.DataSource = tbDatabase.Text;

            if (!String.IsNullOrEmpty(tbUserName.Text))
            {
                builder.Add(MsAccessDataConnection.strUserID, tbUserName.Text);
            }
            else
            {
                builder.Remove(MsAccessDataConnection.strUserID);
            }

            if (!String.IsNullOrEmpty(tbPassword.Text))
            {
                builder.Add(MsAccessDataConnection.strPassword, tbPassword.Text);
            }
            else
            {
                builder.Remove(MsAccessDataConnection.strPassword);
            }

            return(builder.ToString());
        }
Exemplo n.º 7
0
        public GpsHistory()
        {
            //todo 后期需统一将数据库操作封装到一层中
            gpsHistoryDBConnectBuilder = new OleDbConnectionStringBuilder();

            gpsHistoryDBConnectBuilder.Add("Provider", "MSDAORA");
            gpsHistoryDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "gpsDB"));
            gpsHistoryDBConnectBuilder.Add("Persist Security Info", true);
            gpsHistoryDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "gpsDBUser"));
            gpsHistoryDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "gpsDBPasswd"));

            //gpsHistoryDBConnectBuilder.Add("Provider", "MSDAORA");
            //gpsHistoryDBConnectBuilder.Add("Data Source", "10.178.1.218/gspgis");
            //gpsHistoryDBConnectBuilder.Add("Persist Security Info", true);
            //gpsHistoryDBConnectBuilder.Add("User ID", "zcdz");
            //gpsHistoryDBConnectBuilder.Add("Password", "zcdz");

            gpsDeviceDBConnectBuilder = new OleDbConnectionStringBuilder();

            gpsDeviceDBConnectBuilder.Add("Provider", "MSDAORA");
            gpsDeviceDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "gpsDeviceDB"));
            gpsDeviceDBConnectBuilder.Add("Persist Security Info", true);
            gpsDeviceDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "gpsDeviceDBUser"));
            gpsDeviceDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "gpsDeviceDBPasswd"));

            //gpsDeviceDBConnectBuilder.Add("Provider", "MSDAORA");
            //gpsDeviceDBConnectBuilder.Add("Data Source", "10.178.1.108/pgis");
            //gpsDeviceDBConnectBuilder.Add("Persist Security Info", true);
            //gpsDeviceDBConnectBuilder.Add("User ID", "zcdz");
            //gpsDeviceDBConnectBuilder.Add("Password", "zcdz");
        }
Exemplo n.º 8
0
        //Returns the connection object
        public OleDbConnection OpenExcelFile(string ExcelFilePath, string SheetName, ExcelFileType FileType, bool HasHeaderRow, bool ForceMixedDataAsText, int TypeGuessRows, string CSVDelimiter)
        {
            string           ConnectionStringOptions = "";
            DataTable        sheet1 = new DataTable();
            OleDbDataAdapter adapter;
            DataSet          ds = new DataSet();

            adapter = new OleDbDataAdapter();

            string ParentDirectory = ExcelFilePath.Substring(0, ExcelFilePath.LastIndexOf('\\'));
            string FileName        = ExcelFilePath.Split('\\').Last();

            if (HasHeaderRow)
            {
                ConnectionStringOptions += "HDR=YES;";
            }
            else
            {
                ConnectionStringOptions += "HDR=NO;";
            }
            if (ForceMixedDataAsText)
            {
                ConnectionStringOptions += "IMEX=1;";
            }
            ConnectionStringOptions += "TypeGuessRows=" + TypeGuessRows + ";";
            ConnectionStringOptions  = ConnectionStringOptions.Substring(0, ConnectionStringOptions.Length - 1);    //remove trailing ; character

            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();

            if (FileType == ExcelFileType.xlsx)
            {
                csbuilder.Provider   = "Microsoft.ACE.OLEDB.12.0";
                csbuilder.DataSource = ExcelFilePath;
                csbuilder.Add("Extended Properties", "Excel 12.0 Xml;" + ConnectionStringOptions);
            }
            else if (FileType == ExcelFileType.xls)
            {
                csbuilder.Provider   = "Microsoft.Jet.OLEDB.4.0";
                csbuilder.DataSource = ExcelFilePath;
                csbuilder.Add("Extended Properties", "Excel 8.0;" + ConnectionStringOptions);
            }
            else if (FileType == ExcelFileType.csv)
            {
                csbuilder.Provider   = "Microsoft.Jet.OLEDB.4.0";
                csbuilder.DataSource = ParentDirectory;
                csbuilder.Add("Extended Properties", "text;FMT=Delimited(" + CSVDelimiter + ");" + ConnectionStringOptions);
            }
            else
            {
                throw new System.ArgumentException("Invalid file type specified.  File must end with .xls, .xlsx or .csv");
            }


            OleDbConnection con = new OleDbConnection(csbuilder.ConnectionString);

            con.Open();
            return(con);
        }
Exemplo n.º 9
0
        // kai paspaudziamas mygtukas "Pradėti"
        private async void MygtukasPradeti_Click(object sender, EventArgs e)
        {
            toRun = true;

            int atsakuSkaicius = int.Parse(atsakuSarasas.SelectedItem.ToString());

            mygtukasPradeti.Enabled  = false;
            atsakuSarasas.Enabled    = false;
            mygtukasStabdyti.Enabled = true;
            CloseButton.EnableDisable(this, false);

            Task[] ts = new Task[atsakuSkaicius];

            while (toRun)
            {
                ConcurrentBag <DatabaseTable> generatedSequences = new ConcurrentBag <DatabaseTable>();

                await Task.Run(() =>
                {
                    for (int i = 0; i < atsakuSkaicius; i++)
                    {
                        ts[i] = Task.Factory.StartNew(() => generatedSequences.Add(obj.GenerateSequence()));
                    }
                    Task.WaitAll(ts);
                }
                               );

                try
                {
                    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
                    string path     = Directory.GetParent(System.Reflection.Assembly.GetExecutingAssembly().Location).FullName;
                    string fileName = Path.Combine(path, "Database.mdb");
                    builder.ConnectionString = @"Data Source=" + fileName;

                    builder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
                    builder.Add("Jet OLEDB:Database Password", "12345");

                    foreach (DatabaseTable element in generatedSequences)
                    {
                        string[]     data         = { element.threadId.ToString(), element.data };
                        ListViewItem listViewItem = new ListViewItem(data);
                        atsakymuLentele.Items.Insert(0, listViewItem);

                        if (atsakymuLentele.Items.Count > 20)
                        {
                            atsakymuLentele.Items.RemoveAt(atsakymuLentele.Items.Count - 1);
                        }

                        InsertData(builder.ConnectionString, element.threadId, element.generatedTime, element.data);
                    }
                }
                catch (Exception ex)
                {
                    LogWriter logger = new LogWriter(ex.ToString());
                    logger.LogWrite(ex.ToString());
                }
            }
        }
Exemplo n.º 10
0
        /// <summary>
        /// 圈选获取监所详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public JSDetail GetJSDetailByPoly(string id)
        {
            OleDbConnectionStringBuilder zzjgDBConnectBuilder = new OleDbConnectionStringBuilder();

            zzjgDBConnectBuilder.Add("Provider", "MSDAORA");
            zzjgDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "zzjgDB"));
            zzjgDBConnectBuilder.Add("Persist Security Info", true);
            zzjgDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBUser"));
            zzjgDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBPasswd"));

            JSDetail info = new JSDetail();

            try
            {
                using (OleDbConnection conn = new OleDbConnection(zzjgDBConnectBuilder.ConnectionString))
                {
                    //缺少照片数据
                    String sql = String.Format("select JSMC,LD,DH,DZ,BZRS from B_ZTK_SP_JSJBXX where JSBH='{0}'", id);
                    conn.Open();
                    OleDbCommand    cmd    = new OleDbCommand(sql, conn);
                    OleDbDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0))
                        {
                            info.JS_MC = reader[0].ToString();
                        }
                        if (!reader.IsDBNull(1))
                        {
                            info.DWLD_XM = reader[1].ToString();
                        }
                        if (!reader.IsDBNull(2))
                        {
                            info.DWLD_LXDH = reader[2].ToString();
                        }
                        if (!reader.IsDBNull(3))
                        {
                            info.GAJGXZ = reader[3].ToString();
                        }
                        if (!reader.IsDBNull(4))
                        {
                            info.RS = reader[4].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(info);

            //string json = ServiceUtil.GetRemoteXmlStream(ConfigHelper.GetValueByKey("webservice.config", "圈选监所详细信息") + id, null);
            //return JsonConvert.DeserializeObject<List<JSDetail>>(json)[0];
        }
Exemplo n.º 11
0
    static void Main(string[] args)
    {
        OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

        builder.ConnectionString = @"Data Source=C:\Sample.mdb";

        // Call the Add method to explicitly add key/value
        // pairs to the internal collection.
        builder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
        builder.Add("Jet OLEDB:Database Password", "MyPassword!");
        builder.Add("Jet OLEDB:System Database", @"C:\Workgroup.mdb");

        // Set up row-level locking.
        builder.Add("Jet OLEDB:Database Locking Mode", 1);

        Console.WriteLine(builder.ConnectionString);
        Console.WriteLine();

        // Clear current values and reset known keys to their
        // default values.
        builder.Clear();

        // Pass the OleDbConnectionStringBuilder an existing
        // connection string, and you can retrieve and
        // modify any of the elements.
        builder.ConnectionString =
            "Provider=DB2OLEDB;Network Transport Library=TCPIP;" +
            "Network Address=192.168.0.12;Initial Catalog=DbAdventures;" +
            "Package Collection=SamplePackage;Default Schema=SampleSchema;";

        Console.WriteLine("Network Address = " + builder["Network Address"].ToString());
        Console.WriteLine();

        // Modify existing items.
        builder["Package Collection"] = "NewPackage";
        builder["Default Schema"]     = "NewSchema";

        // Call the Remove method to remove items from
        // the collection of key/value pairs.
        builder.Remove("User ID");

        // Note that calling Remove on a nonexistent item does not
        // throw an exception.
        builder.Remove("BadItem");
        Console.WriteLine(builder.ConnectionString);
        Console.WriteLine();

        // Setting the indexer adds the value, if
        // necessary.
        builder["User ID"]  = "SampleUser";
        builder["Password"] = "******";
        Console.WriteLine(builder.ConnectionString);

        Console.WriteLine("Press Enter to finish.");
        Console.ReadLine();
    }
Exemplo n.º 12
0
        public TempleManager()
        {
            this.zzjgDBConnectBuilder = new OleDbConnectionStringBuilder();

            zzjgDBConnectBuilder.Add("Provider", "MSDAORA");
            zzjgDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "zzjgDB"));
            zzjgDBConnectBuilder.Add("Persist Security Info", true);
            zzjgDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBUser"));
            zzjgDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBPasswd"));
        }
Exemplo n.º 13
0
        /// <inheritdoc/>
        protected override string GetConnectionStringWithLoginInfo(string userName, string password)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(ConnectionString);

            builder.Remove("User ID");
            builder.Add("User ID", userName);

            builder.Remove("Password");
            builder.Add("Password", password);

            return(builder.ToString());
        }
Exemplo n.º 14
0
        public static string GetOleDbConnectionString(string dbfDir)
        {
            var builder = new OleDbConnectionStringBuilder {
                Provider = "Microsoft.Jet.OLEDB.4.0", DataSource = dbfDir
            };

            builder.Add("Extended Properties", "dBASE IV");
            builder.Add("User ID", "Admin");
            builder.Add("Password", string.Empty);

            return(builder.ConnectionString);
        }
        bool CargarArchivoExcelADataTable()
        {
            try
            {
                ds.Clear();
                String strconn = "";
                bool   flag    = false;

                OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
                cb.DataSource = ruta;

                if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLS")
                {
                    cb.Provider = "Microsoft.Jet.OLEDB.4.0";
                    cb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=0;");
                }
                else if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLSX")
                {
                    cb.Provider = "Microsoft.ACE.OLEDB.12.0";
                    cb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=0;");
                }


                // strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ruta + ";Extended Properties=Excel 12.0;";
                //  OleDbConnection mconn = new OleDbConnection(strconn);
                OleDbConnection mconn = new OleDbConnection(cb.ConnectionString);
                //El nombre de la hoja del archivo de marcaciones tiene que llamarse Marcaciones
                OleDbDataAdapter ad = new OleDbDataAdapter("Select * from [" + plantilla + "$]", mconn);
                //abre una conexion de tipo oledb
                mconn.Open();
                //Lo agrega a mi datatable
                ad.Fill(ds);
                if (ds != null)
                {
                    flag = true;
                }
                else
                {
                    flag = false;
                }
                //cierra una conexion de tipo oledb
                mconn.Close();
                return(flag);
            }
            catch (Exception ex)
            {
                Log_Error_bus.Log_Error(ex.ToString());
                MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                ds = new DataTable();
                return(false);
            }
        }
Exemplo n.º 16
0
        /// <summary>
        /// Databases the connection string.
        /// </summary>
        /// <param name="databasePath">The database path.</param>
        /// <param name="usePassword">if set to <c>true</c> [use password].</param>
        /// <returns>System.String.</returns>
        internal static string DatabaseConnectionString(string databasePath, bool usePassword = false)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

            builder.ConnectionString = $"Data Source={databasePath}";
            builder.Add("Provider", "Microsoft.JET.Oledb.4.0");
            if (usePassword)
            {
                builder.Add("Jet OLEDB:Database Password", Database.DbPassword);
            }
            builder.Add("Mode", 12);
            return(builder.ToString());
        }
Exemplo n.º 17
0
        protected override OleDbConnection GetNativeConnection(string connectionString)
        {
            OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(ConnectionString);

            oleDBCnnStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
            oleDBCnnStrBuilder.Add("Mode", "ReadWrite");

            if (false == connectionString.Contains("Extended Properties"))
            {
                oleDBCnnStrBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes");
            }

            return(new OleDbConnection(oleDBCnnStrBuilder.ToString()));
        }
Exemplo n.º 18
0
        protected override OleDbConnection GetNativeConnection(string connectionString)
        {
            OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(connectionString);

            oleDBCnnStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
            if (connectionString.ToUpperInvariant().Contains("UNICODE"))
            {
                oleDBCnnStrBuilder.Add("Extended Properties", "text;CharacterSet=UNICODE;HDR=Yes;FMT=Delimited");
            }
            else
            {
                oleDBCnnStrBuilder.Add("Extended Properties", "text;HDR=Yes;FMT=Delimited");
            }
            return(new OleDbConnection(oleDBCnnStrBuilder.ToString()));
        }
Exemplo n.º 19
0
        public ChildDBConnect(int spec_id, String tableName, String punktName)
        {
            tabName = tableName;
            RegistryKey regKey = Registry.CurrentUser;

            regKey = regKey.OpenSubKey("Software\\UFSIN\\ivrJournal");

            OleDbConnectionStringBuilder bldr = new OleDbConnectionStringBuilder();

            bldr.DataSource = regKey.GetValue("dbPath", "").ToString(); // Указываем путь
            bldr.Provider   = DataProvider;                             // Указываем провайдера

            bldr.Add("Jet OLEDB:Database Password", "ivr32a");

            OleDbCon = new OleDbConnection(bldr.ConnectionString);

            sprSet = new DataSet();
            if (tableName == "spec_psycho")
            {
                InitDataTableSpecPsyho(spec_id, tableName, punktName);
            }
            else
            {
                InitDataTableIVR(spec_id, tableName, punktName);
            }
        }
Exemplo n.º 20
0
        private void Form1_Load(object sender, EventArgs e)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder()
            {
                Provider   = "Microsoft.Jet.OLEDB.4.0",
                DataSource = System.IO.Directory.GetCurrentDirectory() + "\\database.accdb",
            };

            builder.Add("Jet OLEDB:Engine Type", 5);
            var catalog = new ADOX.Catalog();
            var table   = new ADOX.Table();

            table.Name = "DataTableSample1";
            table.Columns.Append("Column");
            table.Columns.Append("Column1");
            table.Columns.Append("Column2");
            if (!System.IO.File.Exists(builder.DataSource))
            {
                catalog.Create(builder.ConnectionString);
            }
            try {
                catalog.Tables.Append(table);
            } catch (Exception ex)
            {
                MessageBox.Show("Exists");
            }
            var connection = catalog.ActiveConnection as ADODB.Connection;

            if (connection != null)
            {
                connection.Close();
            }
        }
Exemplo n.º 21
0
    private static void InsertIntoExcel()
    {
        OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder();

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

        using (var dbCon = new OleDbConnection(conString.ConnectionString))
        {
            dbCon.Open();

            var command = new OleDbCommand("INSERT INTO [Sheet1$] VALUES (@vendor, @incomes, @expences, @taxes, @financialResults)");
            command.Parameters.AddWithValue("@vendor", "Vendor");
            command.Parameters.AddWithValue("@incomes", "Incomes");
            command.Parameters.AddWithValue("@expences", "Expences");
            command.Parameters.AddWithValue("@taxes", "Taxes");
            command.Parameters.AddWithValue("@finanvialResults", "Financial Results");


            //string command = "SELECT * FROM [Sales$]";
            //using (var adapter = new OleDbDataAdapter(command, dbCon))
            //{
            //    adapter.Fill(sheet);
            //}
        }
    }
Exemplo n.º 22
0
 static void Main(string[] args)
 {
     if (System.IO.File.Exists(FileName))
     {
         var Builder = new OleDbConnectionStringBuilder()
         {
             DataSource = Path.GetDirectoryName(FileName),
             Provider   = "Microsoft.Jet.OLEDB.4.0"
         };
         Builder.Add("Extended Properties", "dBase III");
         using (var cn = new System.Data.OleDb.OleDbConnection()
         {
             ConnectionString = Builder.ConnectionString
         })
         {
             using (var cmd = new OleDbCommand()
             {
                 Connection = cn
             })
             {
                 cmd.CommandText = "SELECT * FROM " + Path.GetFileName(FileName);
                 cn.Open();
                 var dt = new DataTable("Data");
                 dt.Load(cmd.ExecuteReader());
                 dt.WriteXml("Data.xml");
             }
         }
     }
 }
Exemplo n.º 23
0
        private List <String> GetExcelSheetNames(string filePath)
        {
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            String strExtendedProperties = String.Empty;

            sbConnection.DataSource = filePath;

            sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
            strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";

            sbConnection.Add("Extended Properties", strExtendedProperties);
            List <string> listSheet = new List <string>();

            using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
            {
                conn.Open();
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    {
                        listSheet.Add(drSheet["TABLE_NAME"].ToString());
                    }
                }
            }
            return(listSheet);
        }
Exemplo n.º 24
0
        private void button3_Click(object sender, EventArgs e)
        {
            if (dataGridView1.RowCount > 0)
            {
                try
                {
                    using (SaveFileDialog saveFileDialog1 = new SaveFileDialog())
                    {
                        saveFileDialog1.AddExtension     = true;
                        saveFileDialog1.DefaultExt       = "xls";
                        saveFileDialog1.Filter           = "Excel文件(*.xls)|*.xls";
                        saveFileDialog1.RestoreDirectory = true;
                        saveFileDialog1.FileName         = DateTime.Now.ToString("药品结余 yyyy-MM-dd");
                        if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                        {
                            OleDbConnectionStringBuilder osb = new OleDbConnectionStringBuilder();
                            osb.Provider   = "Microsoft.Jet.OLEDB.4.0";
                            osb.DataSource = saveFileDialog1.FileName;
                            osb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=2");

                            StringBuilder sb        = new StringBuilder();
                            string        TableName = DateTime.Now.ToString("hh_mm_ss");
                            sb.Append(string.Format("CREATE TABLE [{0}](", TableName));
                            using (DataTable DatDB = dataGridView1.DataSource as DataTable)
                            {
                                foreach (DataColumn dc in DatDB.Columns)
                                {
                                    sb.Append(string.Format("[{0}]{1},", dc.ColumnName, OleDbType.VarChar));
                                }
                                using (OleDbConnection cn = new OleDbConnection(osb.ConnectionString))
                                {
                                    using (OleDbCommand cmd = new OleDbCommand(sb.ToString().TrimEnd(',') + ")", cn))
                                    {
                                        cn.Open();
                                        cmd.ExecuteNonQuery();
                                        cn.Close();
                                    }
                                    using (OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}]", TableName), cn))
                                    {
                                        using (OleDbCommandBuilder cb = new OleDbCommandBuilder(oda))
                                        {
                                            cb.DataAdapter.Update(DatDB);
                                        }
                                    }
                                }
                            }
                            MessageBox.Show("保存成功");
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show("没有结余数据,勿需保存!!!");
            }
        }
Exemplo n.º 25
0
    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);
            }
        }
    }
Exemplo n.º 26
0
        public List <string> ListSheetInExcel(string filePath)
        {
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            String strExtendedProperties = String.Empty;

            sbConnection.DataSource = filePath;
            if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
            {
                sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; //HDR=ColumnHeader,IMEX=InterMixed
            }
            else if (Path.GetExtension(filePath).Equals(".xlsx"))   //for 2007 Excel file
            {
                sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
            }
            sbConnection.Add("Extended Properties", strExtendedProperties);
            List <string> listSheet = new List <string>();

            using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
            {
                conn.Open();
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    {
                        listSheet.Add(drSheet["TABLE_NAME"].ToString());
                    }
                }
            }
            return(listSheet);
        }
Exemplo n.º 27
0
        /// <summary>
        /// Creates a <see cref="OleDbConnection"/> based on the specified Microsoft Excel workbook.
        /// </summary>
        /// <param name="fileName">Full path and file name of the Microsoft Excel workbook to use as the data source.</param>
        /// <returns>Returns a <see cref="OleDbConnection"/>.</returns>
        private OleDbConnection GetWorkbookConnection(string fileName)
        {
            // http://stackoverflow.com/a/9274455/2386774
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException("fileName", "The filename must not be empty or null.");
            }

            OleDbConnectionStringBuilder connectionString = new OleDbConnectionStringBuilder();

            connectionString.DataSource = fileName;
            string extendedProperties = String.Empty;

            if (Path.GetExtension(fileName).Equals(".xls")) // for 97-03 Excel file
            {
                connectionString.Provider = "Microsoft.Jet.OLEDB.4.0";
                extendedProperties        = "Excel 8.0";
            }
            else if (Path.GetExtension(fileName).Equals(".xlsx"))  // for 2007 Excel file
            {
                // Install 2007 Office System Driver: Data Connectivity Components
                // https://www.microsoft.com/en-us/download/details.aspx?id=23734
                connectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
                extendedProperties        = "Excel 12.0";
            }
            else
            {
                throw new ArgumentException("The file must be of type .xls or .xlsx.");
            }
            connectionString.Add("Extended Properties", extendedProperties + ";HDR=Yes;IMEX=1"); // HDR=ColumnHeader,IMEX=InterMixed

            return(new OleDbConnection(connectionString.ToString()));
        }
Exemplo n.º 28
0
    static void Main()
    {
        string  fileName = "../../scoreboard.xlsx";
        DataSet sheet    = 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 (var dbCon = new OleDbConnection(conString.ConnectionString))
        {
            dbCon.Open();
            string command = "SELECT * FROM [Sheet1$]";
            using (var adapter = new OleDbDataAdapter(command, dbCon))
            {
                adapter.Fill(sheet);
            }

            string name  = "Joro the rabbit";
            double score = 434.34;
            AddToExcel(name, score, dbCon);
        }

        DataTable table = sheet.Tables[0];

        PrintTable(table);
    }
Exemplo n.º 29
0
        private static string ExcelConnectionString(string filePath)
        {
            try
            {
                OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
                String strExtendedProperties = String.Empty;
                sbConnection.DataSource = filePath;

                if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
                {
                    sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                    strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; //HDR=ColumnHeader,IMEX=InterMixed
                }
                else if (Path.GetExtension(filePath).Equals(".xlsx"))   //for 2007 Excel file
                {
                    sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                    strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                }

                sbConnection.Add("Extended Properties", strExtendedProperties);

                return(sbConnection.ToString());
            }
            catch (Exception)
            {
                throw;
            }
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder {
                Provider = "Microsoft.ACE.OLEDB.12.0", DataSource = System.IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            };

            //
            // Our highly secure password :-)
            //
            Builder.Add("Jet OLEDB:Database Password", "password");

            using (OleDbConnection cn = new OleDbConnection {
                ConnectionString = Builder.ConnectionString
            })
            {
                using (OleDbCommand cmd = new OleDbCommand {
                    Connection = cn, CommandText = "SELECT TOP 6 CompanyName FROM Customers;"
                })
                {
                    cn.Open();

                    dt.Load(cmd.ExecuteReader());
                    ListBox1.DisplayMember = "CompanyName";
                    ListBox1.DataSource    = dt;
                }
            }
        }