Exemple #1
2
        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);
                }
            }
        }
        public AccessDatabase()
        {
            SyncRoot = new object();

            Name = "Access 2013";
            CollectionName = "table1";
            Category = "SQL";
            Description = "Access 2013";
            Website = "http://www.microsoft.com/en-us/download/details.aspx?id=13255";
            Color = Color.FromArgb(218, 66, 127);

            Requirements = new string[]
            {
                "Microsoft Access Database Engine 2010 Redistributable",
                "Microsoft.Office.Interop.Access.Dao.dll"
            };

            DataDirectory = Path.Combine(MainForm.DATABASES_DIRECTORY, Name);

            OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
            cb.Provider = "Microsoft.ACE.OLEDB.12.0";
            cb.DataSource = String.Format(@"{0}\{1}.accdb", DataDirectory, Name);
            ConnectionString = cb.ConnectionString;

            Logger = LogManager.GetLogger(Properties.Settings.Default.TestLogger);
        }
        /// <summary>
        /// Reads the contents of an excel report file into a list of sales arrays;
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="salesDate"></param>
        /// <returns>rows: Supermarket, Data, ProductID, Quantity, Unit Price</returns>
        public static List<string[]> ReadOneExcelFile(OleDbConnectionStringBuilder connectionString, string supermarket, string salesDate)
        {
            List<String[]> data = new List<String[]>();
            using (OleDbConnection connection = new OleDbConnection(connectionString.ToString()))
            {
                connection.Open();
                var command = new OleDbCommand("select * from [Sales$B3:E1000]", connection);
                using (OleDbDataReader oneRow = command.ExecuteReader())
                {
                    oneRow.Read();  // Skip row - ProductID, Quantity, Unit Price

                    while (oneRow.Read())
                    {
                        string productID = oneRow[0].ToString();
                        string quantity = oneRow[1].ToString();
                        string unitPrice = oneRow[2].ToString();
                        int result;  // only to check if the line containd a sale
                        if (int.TryParse(productID, out result))
                        // If not the Last rows
                        {
                            string[] line = { supermarket, salesDate, productID, quantity, unitPrice };
                            data.Add(line);
                        }
                    }
                }
            }
            return data;
        }
        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;
        }
        public static DbConnectionStringBuilder CreateConnectionStringBuilder(DataProvider dataProvider)
        {
            DbConnectionStringBuilder dbConnectionStringBuilder;

            switch (dataProvider)
            {
                case DataProvider.Odbc:
                    dbConnectionStringBuilder = new OdbcConnectionStringBuilder();
                    break;

                case DataProvider.OleDB:
                    dbConnectionStringBuilder = new OleDbConnectionStringBuilder();
                    break;

                case DataProvider.SqlServer:
                    dbConnectionStringBuilder = new SqlConnectionStringBuilder();
                    break;

                default:
                    dbConnectionStringBuilder = null;
                    break;
            }

            return dbConnectionStringBuilder;
        }
    public void LoadDelegates()
    {
        OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
        sb.Provider = "Microsoft.ACE.OLEDB.12.0";
        sb.DataSource = Server.MapPath("~/../uploads/db1.accdb");

        OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
        string sqlQuery = "SELECT * FROM Bag";

        OleDbCommand command = new OleDbCommand(sqlQuery, conn);
        OleDbDataReader myReader;

        //Open connection
        conn.Open();

        //Retrieve the data and display by using a DataList
        myReader = command.ExecuteReader();
        dlstDataList.DataSource = myReader;
        dlstDataList.DataBind();

        myReader.Close();

        //Close connection
        conn.Close();
    }
    protected void Page_Load(object sender, EventArgs e)
    {

        //Define connection
        OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
        sb.Provider = "Microsoft.ACE.OLEDB.12.0";
        sb.DataSource = Server.MapPath("~/../uploads/db1.accdb");

        OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
        string sqlQuery = "SELECT [BagID],[BagName], [Quantity],[UserName], [Color], [Cost], [Status]FROM [Orders]";
        _myAdapter = new OleDbDataAdapter(sqlQuery, conn);

        //Event handlers
        dlstDataList.ItemCommand += new DataListCommandEventHandler(dlstDataList_ItemCommand);
        Save.Click += new EventHandler(Save_Click);
        Clear.Click += new EventHandler(Clear_Click);

        if (!this.IsPostBack)
        {
            this.LoadDelegates();

            //Load table
            //No need to open and close connection. DataAdapter does it.
            _myAdapter.Fill(_myDs, _orderItemsName);
            _myDs.Clear();
            this.Session[_orderName] = _myDs;
        }

    }
Exemple #8
0
        //Ruft die einträge einer Person ab und gibt sie als Eintrag objekt zurück
        private Eintrag EintragAbrufen(Person p)
        {
            Eintrag eintrag = new Eintrag();

            OleDbConnectionStringBuilder bldr = new OleDbConnectionStringBuilder("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\\Eigene Dateien\\Visual Studio 2013\\Fi12\\Projekt\\Datenbank11.accdb");
            OleDbConnection con = new OleDbConnection();
            OleDbCommand com = new OleDbCommand();

            con.ConnectionString = bldr.ConnectionString;
            con.Open();

            com.Connection = con;

            com.CommandText = "SELECT Tweet FROM Tweets WHERE (AccID = pId)";
            com.Parameters.Add("pId", OleDbType.Numeric);
            com.CommandType = CommandType.Text;

            com.Parameters["pId"].Value = p.Id;
            com.ExecuteNonQuery();
            com.Connection = con;

            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                eintrag.addEintrag((string)reader["Tweet"]);
            }
            reader.Close();

            return eintrag;
        }
        public Connect(string dbhost = "localhost", string dbname = "test", string dbusername = "******", string dbpass = "", string dbdriver = "SQLOLEDB")
        {
            try
            {
                OleDbConnectionStringBuilder connection = new OleDbConnectionStringBuilder();

                connection["Provider"] = dbdriver;
                connection["Data Source"] = dbhost;
                connection["Database"] = dbname;
                connection["User ID"] = dbusername;
                connection["Password"] = dbpass;

                _conn = new OleDbConnection();
                _conn.ConnectionString = connection.ConnectionString;
                _conn.Open();
            }
            catch (InvalidOperationException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (OleDbException ex)
            {
                this.DisplayOleDbErrorCollection(ex);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            
        }
    protected void showOrders()
    {
        OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
        sb.Provider = "Microsoft.ACE.OLEDB.12.0";
        sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
        OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
        string str = "SELECT * FROM Orders";
        conn.Open();
        OleDbCommand com = new OleDbCommand("", conn);
        com.CommandText = str;
        OleDbDataReader data = com.ExecuteReader();

        string stre = "";

        stre += "<table cellpadding='0' cellspacing='0' border='1'>";
        stre += "<tr><td>OrderID</td><td>BagID</td><td>BagName</td><td>Quantity</td><td>UserName</td><td>Color</td><td>Cost</td><td>Status</td></tr>";
        while (data.Read())
        {
            stre += "<tr>";
            for (int i = 0; i < data.FieldCount; i++)
            {
                stre += "<td>" + data[i].ToString() + "</td>";
            }
            stre +="<tr/>";
        }
        stre += "</table>";
        ltlOrders.Text = stre;
    }
Exemple #11
0
        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 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);
                }
            }
        }
    protected void btnAddBag_Click(object sender, EventArgs e)
    {
        try
        {

            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
            OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
            conn.Open();
            string insertQuery = "insert into Bag ( [BagName], [Supplier], [Color], [Category], [Price], [Description]) values (@name ,@supplier ,@color ,@category ,@price ,@description)";
            OleDbCommand com = new OleDbCommand(insertQuery, conn);
            com.Parameters.AddWithValue("@name", txtBagName.Text);
            com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
            com.Parameters.AddWithValue("@color", txtColor.Text);
            com.Parameters.AddWithValue("@category", txtCategory.Text);
            com.Parameters.AddWithValue("@price", txtPrice.Text);
            com.Parameters.AddWithValue("@description", txtDescription.Text);

            com.ExecuteNonQuery();

            lblMessage.Text = "The bag was added successfully " + txtBagName.Text + " !";

            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write("Error: " + ex.ToString());
            lblMessage.Text = "Error" + txtBagName.Text + " !";
        }
    }
 /// <summary>
 /// Constructor for the class for Epi 2000 projects
 /// </summary>
 /// <param name="proj">Project the metadata belongs to</param>
 public MetadataDbProvider(Project proj)
 {
     IDbDriverFactory dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(Configuration.AccessDriver);
     OleDbConnectionStringBuilder cnnStrBuilder = new OleDbConnectionStringBuilder();
     cnnStrBuilder.DataSource = proj.FilePath;
     this.db = dbFactory.CreateDatabaseObject(cnnStrBuilder);
 }
        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);
            }
        }
Exemple #16
0
        public void ApplyEventDialogs(Architecture a)
        {
            this.Scenario = a.Scenario;
            if (this.OnApplyEvent != null)
            {
                this.OnApplyEvent(this, a);
            }
            if (nextScenario.Length > 0)
            {
                base.Scenario.EnableLoadAndSave = false;

                List<int> factionIds = new List<int>();
                foreach (Faction f in this.Scenario.PlayerFactions) 
                {
                    factionIds.Add(f.ID);
                }

                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
                {
                    DataSource = "GameData/Scenario/" + nextScenario,
                    Provider = "Microsoft.Jet.OLEDB.4.0"
                };
                this.Scenario.LoadGameScenarioFromDatabase(builder.ConnectionString, factionIds);

                this.Scenario.GameScreen.ReloadScreenData();

                base.Scenario.EnableLoadAndSave = true;
            }
        }
        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();
        }
        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);
                }
            }
        }
    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);
                }
            }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
            OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
            conn.Open();
            string updateQuery = "UPDATE Bag SET BagName=@name, Supplier=@supplier, Color=@color, Category=@category, Price=@price, Description=@description WHERE BagID= @ID";
            OleDbCommand com = new OleDbCommand(updateQuery, conn);

            com.Parameters.AddWithValue("@ID", txtBagId.Text);
            com.Parameters.AddWithValue("@name", txtBagName.Text);
            com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
            com.Parameters.AddWithValue("@color", txtColor.Text);
            com.Parameters.AddWithValue("@category", txtCategory.Text);
            com.Parameters.AddWithValue("@price", txtPrice.Text);
            com.Parameters.AddWithValue("@description", txtDescription.Text);
            

            com.ExecuteNonQuery();

            lblMessage.Text = "The bag No " + txtBagId.Text +" was updated successfully " + txtBagName.Text + " !";

            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write("Error: " + ex.ToString());
            lblMessage.Text = "Error !";
        }
    }
        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;
        }
    protected void Page_Load(object sender, EventArgs e)
    {

        if (IsPostBack)
        {
            
            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");


            OleDbConnection conn = new OleDbConnection(sb.ConnectionString);


            conn.Open();
            string checkUser = "******" + txtUserName.Text + "'";
            OleDbCommand com = new OleDbCommand(checkUser, conn);
            temp = Convert.ToInt32(com.ExecuteScalar().ToString());
            if (temp != 0)
            {
                Response.Write("User Already Exists !");
            }

            conn.Close();
        }

    }
        private static void InsertDataToExcel(OleDbConnectionStringBuilder csbuilder, Data data)
        {
            OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString);
            connection.Open();

            using (connection)
            {
                OleDbCommand command = new OleDbCommand("INSERT INTO [Sheet1$] (Id, Name, Score) " + 
                    "VALUES (@id, @name, @score)", connection);

                OleDbParameter id = new OleDbParameter("@id", data.Id);
                OleDbParameter name = new OleDbParameter("@name", data.Name);
                OleDbParameter score = new OleDbParameter("@score", data.Score);
                command.Parameters.AddRange(new OleDbParameter[] { id, name, score });

                try
                {
                    command.ExecuteNonQuery();

                    Console.WriteLine("Row inserted successfully.");
                }
                catch (OleDbException exception)
                {
                    Console.WriteLine("SQL Error occured: " + exception);
                }
            }
        }
        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()*/);
        }
    protected void btnCS_Click(object sender, EventArgs e)
    {
        try
        {

           
            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
            OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
            conn.Open();
            string updateQuery = "UPDATE Orders SET Status=@status WHERE ID= @ID";
            OleDbCommand com = new OleDbCommand(updateQuery, conn);
            
            com.Parameters.AddWithValue("@status", ddlOrderStatus.SelectedValue.ToString());
            com.Parameters.AddWithValue("@ID", txtOrderID.Text);




            com.ExecuteNonQuery();

            lblMessage.Text = "The Order No " + txtOrderID.Text + " was updated successfully !";

            conn.Close();
            Response.AppendHeader("Refresh", "2;url=Orders.aspx");
        }
        catch (Exception ex)
        {
            Response.Write("Error: " + ex.ToString());
            lblMessage.Text = "Error !";
        }
    }
        /// <summary>
        /// Creates a connection string to an Excel (version 8) file.
        /// </summary>
        /// <param name="filePath">Specifies the path to the Excel file.</param>
        /// <returns>A string that can be used as a connection string in an ADO.NET connection.</returns>
        public static string CreateExcel8FileConnectionString(string filePath)
        {
            var resultBuilder = new OleDbConnectionStringBuilder(ConnectionStringTemplates.Excel8){
                DataSource = filePath };

            return resultBuilder.ConnectionString;
        }
        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();
            }
        }
    protected void AddBug(string BugData)
    {
        System.Data.OleDb.OleDbConnectionStringBuilder objBuilder;
        System.Data.OleDb.OleDbConnection objCon;
        System.Data.OleDb.OleDbCommand    objComm;
        string strInsert = "";

        objBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
        objBuilder.ConnectionString = "Data Source= C:\\BugData.mdb";
        objBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
        objBuilder.Add("Jet OLEDB:Database Password", "");
        objBuilder.Add("Jet OLEDB:Database Locking Mode", 1);
        objCon = new System.Data.OleDb.OleDbConnection(objBuilder.ConnectionString);

        strInsert           = " Insert Into ReportedBugs ";
        strInsert          += " (Tester, AppName, Build, DateReported, Description) ";
        strInsert          += " Values( " + BugData + " ) ";
        objComm             = new System.Data.OleDb.OleDbCommand();
        objComm.Connection  = objCon;
        objComm.CommandType = System.Data.CommandType.Text;
        objComm.CommandText = strInsert;

        try
        {
            objCon.Open();
            objComm.ExecuteNonQuery();
            objCon.Close();
            Response.Redirect("Default.aspx");
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
 public ExcelAccess(string filePath)
 {
     this.filePath = filePath;
     this.oleDbConnectionSb = new OleDbConnectionStringBuilder();
     this.oleDbConnectionSb.Provider = "Microsoft.ACE.OLEDB.12.0";
     this.oleDbConnectionSb.DataSource = this.filePath;
     this.oleDbConnectionSb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=1");
 }
        public PracticalBusiness()
        {
            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = HttpContext.Current.Server.MapPath("~/../uploads/db1.accdb");

            _data = new PracticalData(sb.ConnectionString);
        }
Exemple #31
0
    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);
                }
            }
        }
    }
 public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
 {
     if (destinationType == null)
     {
         throw ADP.ArgumentNull("destinationType");
     }
     if (typeof(InstanceDescriptor) == destinationType)
     {
         OleDbConnectionStringBuilder options = value as OleDbConnectionStringBuilder;
         if (options != null)
         {
             return(this.ConvertToInstanceDescriptor(options));
         }
     }
     return(base.ConvertTo(context, culture, value, destinationType));
 }
Exemple #33
0
 public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
 {
     if (destinationType == null)
     {
         throw ADP.ArgumentNull("destinationType");
     }
     if (typeof(System.ComponentModel.Design.Serialization.InstanceDescriptor) == destinationType)
     {
         OleDbConnectionStringBuilder obj = (value as OleDbConnectionStringBuilder);
         if (null != obj)
         {
             return(ConvertToInstanceDescriptor(obj));
         }
     }
     return(base.ConvertTo(context, culture, value, destinationType));
 }
Exemple #34
0
        public static DbConnection DbConnectionFromConnectionString(string connectionString)
        {
            DbConnection connection = null;
            OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder(connectionString);
            string provider = builder["Provider"].ToString();

            if (provider.StartsWith("MSDASQL"))
            {
                //Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=mysql2;SERVER=localhost;UID=root;DATABASE=sakila;PORT=3306";Initial Catalog=sakila
                //Provider=MSDASQL.1;Persist Security Info=True;Data Source=mysql;Initial Catalog=sakila
                //Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=brCRM;DBQ=C:\tem\adb.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

                //Extract the real ODBC connection string...to be able to use the OdbcConnection
                string odbcConnectionString = "";
                if (builder.ContainsKey("Extended Properties"))
                {
                    odbcConnectionString = builder["Extended Properties"].ToString();
                }
                else if (builder.ContainsKey("Data Source") && !string.IsNullOrEmpty(builder["Data Source"].ToString()))
                {
                    odbcConnectionString = "DSN=" + builder["Data Source"].ToString();
                }
                if (odbcConnectionString != "" && builder.ContainsKey("Initial Catalog"))
                {
                    odbcConnectionString += ";DATABASE=" + builder["Initial Catalog"].ToString();
                }
                if (odbcConnectionString != "" && builder.ContainsKey("User ID"))
                {
                    odbcConnectionString += ";UID=" + builder["User ID"].ToString();
                }
                if (odbcConnectionString != "" && builder.ContainsKey("Password"))
                {
                    odbcConnectionString += ";PWD=" + builder["Password"].ToString();
                }

                connection = new OdbcConnection(odbcConnectionString);
            }
            else
            {
                connection = new OleDbConnection(connectionString);
            }
            return(connection);
        }
 private InstanceDescriptor ConvertToInstanceDescriptor(OleDbConnectionStringBuilder options)
 {
     Type[] types = new Type[] { typeof(string) };
     return(new InstanceDescriptor(typeof(OleDbConnectionStringBuilder).GetConstructor(types), new object[] { options.ConnectionString }));
 }
Exemple #36
0
 private System.ComponentModel.Design.Serialization.InstanceDescriptor ConvertToInstanceDescriptor(OleDbConnectionStringBuilder options)
 {
     Type[]   ctorParams = new Type[] { typeof(string) };
     object[] ctorValues = new object[] { options.ConnectionString };
     System.Reflection.ConstructorInfo ctor = typeof(OleDbConnectionStringBuilder).GetConstructor(ctorParams);
     return(new System.ComponentModel.Design.Serialization.InstanceDescriptor(ctor, ctorValues));
 }
        private DataTable ReadData()
        {
            string ConnectionString = "";

            if (this.Context.FileType == ImportFileType.excelx || this.Context.FileType == ImportFileType.excel)
            {
                System.Data.OleDb.OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder();
                builder.DataSource = this.Context.DataLocation + this.Context.DataFilePath;
                builder.Provider   = "Microsoft.ACE.OLEDB.12.0";
                if (this.Context.FileType == ImportFileType.excel)
                {
                    builder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1");
                }
                else
                {
                    builder.Add("Extended Properties", "Excel 12.0;HDR=Yes;IMEX=1");
                }

                ConnectionString = builder.ConnectionString;
                var con = new System.Data.OleDb.OleDbConnection(ConnectionString);
                System.Data.DataSet   ds = new System.Data.DataSet();
                System.Data.DataTable dt = new DataTable();
                con.Open();
                var dtSheets = new System.Data.DataTable();

                dtSheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dtSheets.Rows.Count == 0)
                {
                    throw new Exception("No Sheet exist in the excel file");
                }
                else if (dtSheets.Rows[0].IsNull(2))
                {
                    throw new Exception("No Data Avaiable");
                }
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("Select * from " + "[" + dtSheets.Rows[0][2] + "]", con);
                //    MyCommand.TableMappings.Add("Table", "TestTable");
                MyCommand.Fill(ds);
                dt = ds.Tables[0];
                List <DataColumn> dc = new List <DataColumn>();
                foreach (DataColumn c in dt.Columns)
                {
                    if (c.DataType == typeof(string))
                    {
                        dc.Add(c);
                    }
                }
                con.Close();
                con.Dispose();
                foreach (DataColumn c in dc)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr[c.ColumnName] != null)
                        {
                            dr[c.ColumnName] = dr[c.ColumnName].ToString().Trim();
                        }
                    }
                }
                return(dt);
            }
            else if (this.Context.FileType == ImportFileType.csv)
            {
                ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.Context.DataLocation + this.Context.DataFilePath + ";Extensions=asc,csv,tab,txt;";
            }
            return(new DataTable());
        }
Exemple #38
0
 public MyOledbHandler()
 {
     StringBuilder = new OleDbConnectionStringBuilder("provider=MSDAORA;USER ID=xxgs/xxgs; PERSIST SECURITY INFO=FALSE");
     StringBuilder["Data Source"] = DataSource;
     oDRDBConnectionString        = StringBuilder.ConnectionString;
 }
    protected void Page_Load(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnectionStringBuilder objBuilder;
        System.Data.OleDb.OleDbConnection objCon;
        System.Data.OleDb.OleDbCommand    objComm;
        string strSelect = "";

        //Create a connection to the database
        objBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
        objBuilder.ConnectionString = "Data Source= C:\\BugData.mdb";
        objBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
        objBuilder.Add("Jet OLEDB:Database Password", "");
        objBuilder.Add("Jet OLEDB:Database Locking Mode", 1);
        objCon = new System.Data.OleDb.OleDbConnection(objBuilder.ConnectionString);

        //Build the SQL Select command
        //Note the Spaces are important!
        strSelect  = " Select ";
        strSelect += " Tester, AppName, Build, DateReported, Description ";
        strSelect += " From ReportedBugs";
        Trace.Write(strSelect);

        //Build a Command object to send your command
        objComm             = new System.Data.OleDb.OleDbCommand();
        objComm.Connection  = objCon;
        objComm.CommandType = System.Data.CommandType.Text;
        objComm.CommandText = strSelect;
        //Response.Write(objComm.CommandText.ToString)

        //Create a DataReader variable to referance the results
        //we get back from the database.
        System.Data.OleDb.OleDbDataReader objDR;

        //Open the connection and run the command
        try
        {
            objCon.Open();
            objDR = objComm.ExecuteReader();

            //Print out the results
            Response.Write("<table border='1'>");
            Response.Write("<tr>");
            Response.Write("<th>Tester</th>");
            Response.Write("<th>App Name</th>");
            Response.Write("<th>Build</th>");
            Response.Write("<th>DateReported</th>");
            Response.Write("<th>Description</th>");
            Response.Write("</tr>");

            while (objDR.Read() == true)
            {
                Response.Write("<tr>");
                Response.Write("<td>" + objDR["Tester"] + "</td>");
                Response.Write("<td>" + objDR["AppName"] + "</td>");
                Response.Write("<td>" + objDR["Build"] + "</td>");
                Response.Write("<td>" + objDR["DateReported"] + "</td>");
                Response.Write("<td>" + objDR["Description"] + "</td>");
                Response.Write("</tr>");
            }

            Response.Write("</table>");
            objDR.Close();
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
        finally
        {
            if (objCon.State == System.Data.ConnectionState.Open)
            {
                objCon.Close();
            }
        }
    }