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

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

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

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

            foreach (DataRow item in dataSet.Rows)
            {
                Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]);
            }
            dbConn.Close();
        }
Exemplo n.º 2
0
    protected void onClick_login(object sender, EventArgs e)
    {
        connection = new OleDbConnection(path);
        connection.Open();

        cmd = new OleDbCommand("SELECT COUNT(*) FROM tblProfiles WHERE user_name = '" + inputUsername.Text + "';", connection);
        cmd.ExecuteNonQuery();

        int compareUsername = Convert.ToInt32(cmd.ExecuteScalar().ToString());

        connection.Close();

        if (compareUsername == 1)
        {
            connection.Open();

            cmd = new OleDbCommand("SELECT user_password FROM tblProfiles WHERE user_name = '" + inputUsername.Text + "';", connection);
            cmd.ExecuteNonQuery();

            string comparePassword = cmd.ExecuteScalar().ToString();

            connection.Close();

            if (comparePassword == inputPassword.Text)
            {
                Session["username"] = inputUsername.Text;
                Server.Transfer("main.aspx");
            }
            else
                errorPassword.Visible = true;
        }
        else
            errorUsername.Visible = true;
    }
Exemplo n.º 3
0
 protected void LinkButton5_Click(object sender, EventArgs e)
 {
     OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath(@"App_Data\db.mdb")+";Persist Security Info=True");
     OleDbCommand cmd = new OleDbCommand("insert into siparis (UYEID,ADRES,TEL,DURUMU) values (@UYEID,@ADRES,@TEL,@DURUMU)",baglanti);
     cmd.Parameters.AddWithValue("@UYEID",((DataTable)(Session["UYE"])).Rows[0][0].ToString());
      cmd.Parameters.AddWithValue("@ADRES",TextBox1.Text);
      cmd.Parameters.AddWithValue("@TEL",TextBox2.Text);
      cmd.Parameters.AddWithValue("@DURUMU","Beklemede");
      baglanti.Open();
      cmd.ExecuteNonQuery();
      baglanti.Close();
      OleDbDataAdapter da = new OleDbDataAdapter("select ID from siparis where UYEID=@UYEID order by ID DESC",baglanti);
      da.SelectCommand.Parameters.AddWithValue("@UYEID",((DataTable)(Session["UYE"])).Rows[0][0].ToString());
      DataTable dt = new DataTable();
      da.Fill(dt);
     DataTable sdt=((DataTable)(Session["SEPET"]));
     for (int i = 0; i < sdt.Rows.Count; i++)
         {
             OleDbCommand scmd = new OleDbCommand("insert into sepet (SIPID,URUNID,URUN,FIYAT,ADET,TOPLAM) VALUES (@SIPID,@URUNID,@URUN,@FIYAT,@ADET,@TOPLAM)",baglanti);
             scmd.Parameters.Clear();
             scmd.Parameters.AddWithValue("@SIPID",dt.Rows[0][0].ToString());
         scmd.Parameters.AddWithValue("@URUNID",sdt.Rows[i]["ID"].ToString());
         scmd.Parameters.AddWithValue("@URUN",sdt.Rows[i]["URUN"].ToString());
         scmd.Parameters.AddWithValue("@FIYAT",sdt.Rows[i]["FIYAT"].ToString());
         scmd.Parameters.AddWithValue("@ADET",sdt.Rows[i]["ADET"].ToString());
         scmd.Parameters.AddWithValue("@TOPLAM", sdt.Rows[i]["TOPLAM"].ToString());
         baglanti.Open();
         scmd.ExecuteNonQuery();
         baglanti.Close();
         }
     Label2.Text = "Sipariş Takip Numarasınız : "+dt.Rows[0][0].ToString();
 }
Exemplo n.º 4
0
        private void AddUser_Click(object sender, EventArgs e)
        {
            comboBox1.SelectedItem = "caissier";
            string newuser = textBox1.Text;
            string newmdp = textBox2.Text;
            string test = "select username from Users where username='******'";
            OleDbConnection userhandlingconn = new OleDbConnection(connexionstring);
            OleDbCommand cmduserhandling = new OleDbCommand();

            try
            {

                if (textBox1.Text.Length ==0 || textBox2.Text.Length ==0|| comboBox1.SelectedItem.ToString().Length ==0)
                {
                    MessageBox.Show("Un ou plusieur champs sont vides !!!");
                }

                else
                {
                    try
                    {
                        cmduserhandling = new OleDbCommand(test, userhandlingconn);
                        userhandlingconn.Open();
                        string verif = cmduserhandling.ExecuteScalar().ToString();
                        userhandlingconn.Close();
                        if (verif.Length > 0)
                        {
                            MessageBox.Show("Cet utilisateur existe deja dans la base de données");
                        }

                    }
                    catch (NullReferenceException)
                    {
                        userhandlingconn.Close();
                        string adduser = "******" + newuser + "','" + newmdp + "',Date(),'" + comboBox1.SelectedItem.ToString() + "');";
                        userhandlingconn.Open();
                        cmduserhandling = new OleDbCommand(adduser, userhandlingconn);
                        cmduserhandling.ExecuteNonQuery();
                        MessageBox.Show("Ajout effectué");
                        userhandlingconn.Close();
                    }

                }
            }
            catch(Exception ex)
            {
                //MessageBox.Show("Erreur !!!");
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                textBox1.Text = "";
                textBox2.Text = "";
                comboBox1.SelectedItem = "";
                DataReload();
            }
        }
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        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);
        
        
        int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
        conn.Close();
        if (temp == 1)
        {
            conn.Open();
            string checkPassword = "******" + txtUserName.Text + "'";
            
            OleDbCommand passcom = new OleDbCommand(checkPassword, conn);
            
            string password = passcom.ExecuteScalar().ToString().Replace(" ", "");


            string checkAdmin = "select usergroup from UserData where UserName='******'";
            OleDbCommand usercon = new OleDbCommand(checkAdmin, conn);
            int veriAdmin =Convert.ToInt32( usercon.ExecuteScalar().ToString());

           // conn.Close();
            if (password == txtPass.Text && veriAdmin == 1)
            {
                
                
                //if (veriAdmin == "admin")
                //{
                    Session["UserName"] = txtUserName.Text;
                    Response.Write("Login Successful! Welcome " + Session["UserName"] + "!");
              
                    
                    
                  //  Response.Redirect("~/Admin/Intro.aspx");
                    Response.AppendHeader("Refresh", "3;url=Intro.aspx");
            }
            else
            {
                Response.Write("Password is incorrect");
            }
        }
        else
        {
            Response.Write("User Name is incorrect");
        }


    }
Exemplo n.º 6
0
        // Completely Remove an Area from the database.
        public static void DeleteArea(int iAreaID)
        {
            // Configure database connection elements.
            OleDbDataAdapter da = new OleDbDataAdapter();

            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = database.getConnectionString();

            // Delete All Rooms in the Area.
            try
            {
                connection.Open();

                // Create query.
                string strSQL = string.Empty;
                strSQL += " delete ";
                strSQL += " from   [Room] ";
                strSQL += " where  [RoomAreaID] = " + iAreaID.ToString() + " ";

                da.InsertCommand = new OleDbCommand(strSQL);
                da.InsertCommand.Connection = connection;

                da.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string strError = ex.Message;
            }
            connection.Close();

            // Delete the Area.
            try
            {
                connection.Open();

                // Create query.
                string strSQL = string.Empty;
                strSQL += " delete ";
                strSQL += " from   [Area] ";
                strSQL += " where  [AreaID] = " + iAreaID.ToString() + " ";

                da.InsertCommand = new OleDbCommand(strSQL);
                da.InsertCommand.Connection = connection;

                da.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string strError = ex.Message;
            }

            connection.Close();
            connection.Dispose();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            
            string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
            OleDbConnection con = new OleDbConnection(chaine);
            OleDbCommand cmd = con.CreateCommand();
            OleDbCommand cmd1 = con.CreateCommand();

            cmd.CommandText = "Insert into etudiant values (" + Convert.ToInt32(txt_cin.Text) + ",'" + txt_nom.Text + "','" + txt_prenom.Text + "','" + comboBox1.Text + "')";
            

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Ajout Module Réussi");
                con.Close();

                new acceuil(login).Show();
                this.Hide();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Erreur" + ex.Message);
                con.Close();
            }



            //ckecked lisT 
             foreach(object itemChecked in checkedListBox1.CheckedItems) {
                 cmd1.CommandText = "Insert into assiste(cin,id_module,date_present) values(" + Convert.ToInt32(txt_cin.Text) + "," + hash[(string)itemChecked] + ",'" + DateTime.Now.ToString("dd-MM-yyyy") + "')";
                 try
                 {
                     con.Open();
                     cmd1.ExecuteNonQuery();
                     con.Close();


                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show("Erreur" + ex.Message);
                     con.Close();
                 }
             }





        }
    protected void LoadData()
    {
        string strsql = "select * from customers where custnumber = " + Session["custid"];
        OleDbConnection myConn = new OleDbConnection();
        myConn.ConnectionString = System.Web.Configuration.WebConfigurationManager.
        ConnectionStrings["AcmeShoppeConnectionString"].ConnectionString;
        OleDbCommand myCmd = new OleDbCommand(strsql, myConn);
        OleDbDataReader myReader;
        if (myConn.State == ConnectionState.Closed) myConn.Open();
            myReader = myCmd.ExecuteReader();

            if (myReader.HasRows)
            {
                myReader.Read();
                this.lblFName.Text = myReader["FirstName"].ToString();
                this.lblLName.Text = myReader["LastName"].ToString();
                this.txtAddress.Text = myReader["Address"].ToString();
                this.txtCity.Text = myReader["City"].ToString();
                this.txtZip.Text = myReader["ZipCode"].ToString();
                this.DropDownList1.SelectedValue = myReader["State"].ToString();
            }
            else
                this.lblerr.Text = "error";
         myReader.Close();
         myCmd.CommandText = "select sum(extension) from cart_view01 " +
                                    "where cartnumber = " + Session["cartnumber"];
         decimal decSubtotal = 0;
         double decSalesTax = 0;
         double decTotal = 0;
         double decShip = 3.25;
         if (myConn.State == ConnectionState.Closed) myConn.Open();
         myReader = myCmd.ExecuteReader();
         if (myReader.HasRows)
         {
             myReader.Read();
             if (myReader[0] != DBNull.Value)
             {
                 decSubtotal = Convert.ToDecimal(myReader[0].ToString());
                 this.lblSubtotal.Text = decSubtotal.ToString("c");
             }
             decSalesTax = Convert.ToDouble(decSubtotal) * .05;
             Math.Round(decSalesTax, 2);
             decTotal = Convert.ToDouble(decSubtotal) + decSalesTax + decShip;
             Math.Round(decTotal, 2);
             this.lblTax.Text = decSalesTax.ToString("c");
             this.lblTotal.Text = decTotal.ToString("c");
             this.lblShipping.Text = decShip.ToString("c");
         }
         else
             this.lblerr.Text = "error";
         myConn.Close();
    }
Exemplo n.º 9
0
        public static System.Data.DataSet Excel2Dataset(string fileName, string tableName)
        {
            bool isOpen = true;
            try
            {
                return DataSet2CSV.ConverCSV2DataSet(fileName, tableName);
            }
            catch (System.Exception) { }

            OleDbConnection connStr = new OleDbConnection();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
            connStr.ConnectionString = strConn;
            try
            {
                connStr.Open();
            }
            catch (System.Exception)
            {
                isOpen = false;
            }

            if (!isOpen)
            {
                isOpen = true;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
                connStr.ConnectionString = strConn;
                try
                {
                    connStr.Open();
                }
                catch (System.Exception)
                {
                    isOpen = false;
                }
            }

            if (!isOpen)
                return null;

            //动态表名
            DataTable tblSchema = connStr.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string sheetName = tblSchema.Rows[0]["TABLE_NAME"].ToString();
            OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [" + sheetName + "]", connStr);

            DataSet ds = new DataSet();
            cmd.Fill(ds, tableName);

            connStr.Close();
            connStr = null;
            return ds;
        }
Exemplo n.º 10
0
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        if (MainMenu.SelectedValue.ToString() == "")
        {
            LblError.Text = "من فضلك قم بأختيار خيار من القائمه";
            LblError.ForeColor = System.Drawing.Color.Red;
            LblError.Visible = true;
        }
        string Path = string.Empty;
        OleDbConnection Con = new OleDbConnection(constr);
        OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM MenuItem Where ItemID = " + MainMenu.SelectedValue.ToString(), Con);
        try
        {
            Con.Open();
            Path = CMD.ExecuteScalar().ToString();

        }
        catch { }
        Con.Close();
        if (Path == string.Empty)
        {
            //Path = MapPath("~/QAData/QAData" + QAMainMenu.SelectedValue.ToString() + ".QA");
            Path = String.Format("../ItemData/Data{0}{1}{2}{3}.Dat", MainMenu.SelectedValue, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second);
        }
        try
        {
            CMD.CommandText = String.Format("Update MenuItem Set Data_Path = '{0}' Where ItemID = {1}", Path, MainMenu.SelectedValue);
            Con.Open();
            CMD.ExecuteNonQuery();
            Path = MapPath(Path);
            //if (File.Exists(Path))
            //{
            //    File.Delete(Path);
            //}
            File.Create(Path).Close();
            TextWriter TW = new StreamWriter(Path, false);
            TW.Write(Request.Form["editor1"]);
            TW.Flush();
            TW.Close();
            LblError.Text = "تم الحفــــظ";
            LblError.ForeColor = System.Drawing.Color.Green;
            LblError.Visible = true;
        }
        catch (Exception ex)
        {
            LblError.Text = ex.Message.ToString();
            LblError.ForeColor = System.Drawing.Color.Red;
            LblError.Visible = true;
        }
        Con.Close();
    }
Exemplo n.º 11
0
    public int GetExcelDistictBTNCOunt_Sales(string sFileName)
    {
        Int32 NORec;
        DataSet objDataset1 = new DataSet();
        int count = 0;
        OleDbConnection objConn = new OleDbConnection();
        try
        {
             string FileExt = System.IO.Path.GetExtension(sFileName);
             if (FileExt == ".xls")
             {
                 //Excell connection
                 string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\"";
                 objConn.ConnectionString = Xls_Con;
                 //Dim objConn As New OleDbConnection(Xls_Con)
                 objConn.Open();
                 DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                 string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

                 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn);
                 objAdapter1.Fill(objDataset1, "XLData");

                 count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
             }
             else if (FileExt == ".xlsx")
             {
                 //Excell connection
                 string Xls_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                 objConn.ConnectionString = Xls_Con;
                 //Dim objConn As New OleDbConnection(Xls_Con)
                 objConn.Open();
                 DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                 string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

                 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn);
                 objAdapter1.Fill(objDataset1, "XLData");

                 count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
             }
            objConn.Close();
        }
        catch (Exception ex)
        {
            throw ex;
            //Redirecting to error message page

            // Redirect(ConstantClass.StrErrorPageURL);
        }
        return count;
    }
Exemplo n.º 12
0
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        string Path = string.Empty;
        OleDbConnection Con = new OleDbConnection(constr);
        OleDbCommand CMD = new OleDbCommand("SELECT Contain FROM NewsData Where ID = " + Request.QueryString["ID"].ToString(), Con);
        try
        {
            Con.Open();
            Path = CMD.ExecuteScalar().ToString();

        }
        catch { }
        Con.Close();
        if (Path == string.Empty)
        {
            //Path = MapPath("~/QAData/QAData" + QAMainMenu.SelectedValue.ToString() + ".QA");
            if (!Directory.Exists(MapPath("../ItemData/")))
            {
                Directory.CreateDirectory(MapPath("../ItemData/"));
            }
            Path = String.Format("../ItemData/Data{0}{1}{2}{3}.Dat", Request.QueryString["ID"], DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second);
        }
        try
        {
            CMD.CommandText = String.Format("Update NewsData Set Contain = '{0}' Where ID = {1}", Path, Request.QueryString["ID"]);
            Con.Open();
            CMD.ExecuteNonQuery();
            Path = MapPath(Path);
            //if (File.Exists(Path))
            //{
            //    File.Delete(Path);
            //}
            File.Create(Path).Close();
            TextWriter TW = new StreamWriter(Path, false);
            TW.Write(txt1.Html);
            TW.Flush();
            TW.Close();
            LblError.Text = "تم الحفــــظ";
            LblError.ForeColor = System.Drawing.Color.Green;
            LblError.Visible = true;
        }
        catch (Exception ex)
        {
            LblError.Text = ex.Message.ToString();
            LblError.ForeColor = System.Drawing.Color.Red;
            LblError.Visible = true;
        }
        Con.Close();
    }
Exemplo n.º 13
0
        public static List<Dictionary<string, string>> GetDataFromExcel(string FilePath, string Extension, string isHDR)
        {
            var list = new List<Dictionary<string, string>>();

            string conStr = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07
                    conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }
            conStr = String.Format(conStr, FilePath, isHDR);
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            cmdExcel.Connection = connExcel;

            //Get the name of First Sheet
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
            connExcel.Close();

            //Read Data from First Sheet
            connExcel.Open();
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
            oda.SelectCommand = cmdExcel;
            oda.Fill(dt);
            connExcel.Close();

            foreach (DataRow row in dt.Rows)
            {
                var obj = new Dictionary<string, string>();
                foreach (var col in dt.Columns)
                {
                    var key = Regex.Replace(col.ToString(), @"\s+", "");
                    obj.Add(key, row[col.ToString()].ToString());
                }
                list.Add(obj);
            }

            return list;
        }
Exemplo n.º 14
0
    public void count()
    {
        OleDbConnection conn = new OleDbConnection(connstr);
        OleDbCommand cmdread = new OleDbCommand("SELECT count FROM main WHERE id=1", conn);
        try
        {
            conn.Open();
            p = (int)cmdread.ExecuteScalar();
            conn.Close();
            label1.Text = p.ToString();
            if (p < 10)
                label1.Text = "000" + label1.Text;
            else if (p < 100)
                label1.Text = "00" + label1.Text;
            else if (p < 1000)
                label1.Text = "0" + label1.Text;

            if ((int)Application["numVisitors"] != 0)
            {
                c = (int)Application["numVisitors"];
                int r = p + c;

                OleDbCommand cmd = new OleDbCommand("UPDATE main SET [count]=@count", conn);  /*"UPDATE main SET [count]=@count WHERE [id]=1"*/

                cmd.Parameters.AddWithValue("count", r);

                /*p = (int)cmd.Parameters.AddWithValue("@count", 4).Value;*/

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                label1.Text = r.ToString();
                if (r < 10)
                    label1.Text = "000" + label1.Text;
                else if (r < 100)
                    label1.Text = "00" + label1.Text;
                else if (r < 1000)
                    label1.Text = "0" + label1.Text;

                Application["numVisitors"] = 0;
            }
        }
        finally
        {
            conn.Close();
        }
    }
    // Gets all the countries that start with the typed text, taking paging into account
    protected DataTable GetCountries(string text, int startOffset, int numberOfItems)
    {
        OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../App_Data/continent.mdb"));
        myConn.Open();

        string whereClause = " WHERE CountryName LIKE @CountryName";
        string sortExpression = " ORDER BY CountryName";

        string commandText = "SELECT TOP " + numberOfItems + " CountryID, CountryName FROM Country";
        commandText += whereClause;
        if (startOffset != 0)
        {
            commandText += " AND CountryID NOT IN (SELECT TOP " + startOffset + " CountryID FROM Country";
            commandText += whereClause + sortExpression + ")";
        }

        commandText += sortExpression;

        OleDbCommand myComm = new OleDbCommand(commandText, myConn);
        myComm.Parameters.Add("@CountryName", OleDbType.VarChar).Value = text + '%';

        OleDbDataAdapter da = new OleDbDataAdapter();
        DataSet ds = new DataSet();
        da.SelectCommand = myComm;
        da.Fill(ds, "Country");

        myConn.Close();

        return ds.Tables[0];
    }
Exemplo n.º 16
0
    public void updateProductPrice(ProductInBag Product)
    {
        OleDbConnection myConn = new OleDbConnection(Connstring.getConnectionString());
        OleDbCommand myCmd;
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
        try
        {
            myCmd = new OleDbCommand("UpdateUnitPrice", myConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            OleDbParameter objParam;

            objParam = myCmd.Parameters.Add("@UnitPrice", OleDbType.Decimal);
            objParam.Direction = ParameterDirection.Input;
            objParam.Value = Product.Price;

            objParam = myCmd.Parameters.Add("@ProductID", OleDbType.Integer);
            objParam.Direction = ParameterDirection.Input;
            objParam.Value = Product.ProdID;

            myConn.Open();
            myCmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConn.Close();
        }
    }
Exemplo n.º 17
0
        public override List<DomainAlias> GetDomainAliases(string domainName)
        {
            var _tmp = new List<DomainAlias>();

            using (OleDbConnection _conn = new OleDbConnection(Settings.Default.connectionString))
            {
                _conn.Open();
                using (OleDbCommand _cmd = new OleDbCommand(@"SELECT domain_aliases.name AS alias, domains.name AS [domain], domain_aliases.status
                                                                FROM (domain_aliases INNER JOIN
                                                            domains ON domain_aliases.dom_id = domains.id)
                                                                WHERE (domain_aliases.status = 0) AND (domains.name = ?)", _conn))
                {
                    _cmd.CommandType = CommandType.Text;
                    _cmd.Parameters.AddWithValue("NAME", domainName);

                    using (OleDbDataReader _read = _cmd.ExecuteReader())
                    {
                        while (_read.Read())
                        {
                            var _d = new DomainAlias();
                            _d.Domain = _read["domain"].ToString();
                            _d.Alias = _read["alias"].ToString();

                            _tmp.Add(_d);
                        }
                    }
                }
                _conn.Close();
            }

            return _tmp;
        }
Exemplo n.º 18
0
        public void update(string query)
        {
            OleDbConnection connection = new OleDbConnection();
            string executable = System.Reflection.Assembly.GetExecutingAssembly().Location;
            string path = (System.IO.Path.GetDirectoryName(executable));
            AppDomain.CurrentDomain.SetData("DataDirectory", path);
            connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|/LMS.accdb";

            OleDbCommand command;
            command = connection.CreateCommand();
            try
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                connection.Open();
                //SqlCommand comm = new SqlCommand(query, connection);

                command.ExecuteNonQuery();

            }
            catch (Exception)
            {
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }
Exemplo n.º 19
0
    protected void Page_Load(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/productsdb.mdb");
        Conn.Open();
        //Response.Write(Conn.State);

        System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand();
        System.Data.OleDb.OleDbDataReader dr;
        Comm.Connection = Conn;

        Comm.CommandText = "select productid, productname, productiondescription, price, qoh, imagelocation from products";

        if (Request.Params["categoryid"] != null && Request.Params["categoryid"].Length >0)
        {
            Comm.CommandText += " where categoryid = ?" ;
            Comm.Parameters.AddWithValue("anything", Request.Params["categoryid"].ToString());
        }

        dr = Comm.ExecuteReader();
        bool firstone = true;
        Response.Write("{\"product\":[");
        while (dr.Read())
        {
             if (!firstone)
            {
                Response.Write(",");
            }
            Response.Write(dr[0].ToString());

            firstone = false;

        }
        Response.Write("]}");
    }
Exemplo n.º 20
0
        public bool InsertFtpRecord(FileDetail fileDetail)
        {
            var lcsql =
                "insert into MasterFtp(FileName, CreateTime, Folder, Records, DlTime) values ( ?, ?,?,?,?)";


            var connectionString = ConfigurationManager.ConnectionStrings["vfpConnectionString"].ConnectionString;
            using (var connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SET NULL OFF";

                    command.ExecuteNonQuery();
                }
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = lcsql;
                    command.Parameters.AddWithValue("FileName", fileDetail.FileName);
                    command.Parameters.AddWithValue("CreateTime", fileDetail.FileDate);
                    command.Parameters.AddWithValue("Folder", fileDetail.Folder);
                    command.Parameters.AddWithValue("Records", fileDetail.Records);
                    command.Parameters.AddWithValue("DlTime", fileDetail.DownloadTime);

                    //connection.Open();
                    var retval = command.ExecuteNonQuery();
                    var success = (retval == 1);
                    return success;
                }
            }


        }
Exemplo n.º 21
0
        public DataTable sepetcevir(string uyeID, string map)
        {

            OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + map);
            cnn.Open();
            OleDbCommand cmd = new OleDbCommand("select sepet from uyeler where uyeid=" + uyeID, cnn);
            OleDbDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            string[] urunler = rdr[0].ToString().Split(',');

            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[3] { new DataColumn("fid"), new DataColumn("aciklama"), new DataColumn("fiyat") });

            if (rdr[0].ToString() != "")
            {
                foreach (string item in urunler)
                {
                    cmd.Dispose();
                    string fid = item;

                    cmd = new OleDbCommand("select * from fotolar where fot_id=" + fid, cnn);
                    OleDbDataReader rdr2 = cmd.ExecuteReader();
                    rdr2.Read();
                    string ack = rdr2[5].ToString();
                    string fiyat = rdr2[6].ToString();

                    dt.Rows.Add(fid, ack, fiyat);
                }
            }

            cnn.Close();

            return dt;
        }
Exemplo n.º 22
0
        private void FormFirma_Load(object sender, EventArgs e)
        {
            PutBaze = System.IO.File.ReadAllText(Application.StartupPath + "\\Ponuda\\BazaPonuda.txt");
            string PutKriterij =System.IO.File.ReadAllText(@"privPonuda.txt");

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

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

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

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

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

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

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

            this.dataGridView2.Sort(firmaDataGridViewTextBoxColumn, ListSortDirection.Ascending);
            System.IO.File.Delete(@"privPonuda.txt");
        }
Exemplo n.º 23
0
 private void fill_form(string p_id)
 {
     String strQuery = String.Empty;
     OleDbConnection sqlConn = new OleDbConnection();
     OleDbCommand sqlComm = new OleDbCommand();
     OleDbDataReader sqlRead;// = new OleDbDataReader();
     DateTime strReturn = DateTime.Now;
     //
     sqlConn.ConnectionString = PCPUB.m_oledb_connection.ToString();
     sqlConn.Open();
     //
     strQuery = String.Empty;
     strQuery += " SELECT";
     strQuery += " [yarn_count_id],";
     strQuery += " [yarn_count_name]";
     strQuery += " FROM [tis_yarn_count]";
     strQuery += " WHERE [yarn_count_id] = '" + p_id + "'";
     //
     sqlComm.Connection = sqlConn;
     sqlComm.CommandText = strQuery.ToString();
     sqlRead = sqlComm.ExecuteReader();
     //
     if (sqlRead.Read())
     {
         txt_yarn_count_id.Text = sqlRead["yarn_count_id"].ToString();
         txt_yarn_count_name.Text = sqlRead["yarn_count_name"].ToString();
     }
     //
     sqlRead.Close();
     sqlConn.Close();
     sqlRead.Dispose();
     sqlComm.Dispose();
     sqlConn.Dispose();
 }
Exemplo n.º 24
0
        private void addToDatabase(string sql)
        {
            OleDbConnection db = null;

            try
            {
                db = new OleDbConnection();
                db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mFileName;
                db.Open();

                OleDbCommand command = new OleDbCommand(sql, db);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                showErrorMessage(ex.Message);
            }
            finally
            {
                if (db != null)
                {
                    db.Close();
                }
            }
        }
    public string LoadContain()
    {
        if (Request.QueryString["CourseId"] == null)
        {
            return string.Empty;
        }

        string ThePath = string.Empty;
        string RetData = string.Empty;
        using (OleDbConnection Con = new OleDbConnection(constr))
        {
            OleDbCommand cmd = new OleDbCommand(String.Format("SELECT TOP 1 DataPath FROM CoursenotimeDataPath WHERE CourseId = {0}", Request.QueryString["CourseId"]), Con);
            try
            {
                Con.Open();
                ThePath = cmd.ExecuteScalar().ToString();
                //if (ThePath != string.Empty)
                //    ThePath = MapPath(DB.CourseNoTimeFileDir + ThePath);
                ThePath = DB.CourseNoTimeFileDir + ThePath;

                TextReader TR = new StreamReader(ThePath);
                RetData = TR.ReadToEnd();
                TR.Close();
                TR.Dispose();

            }
            catch (Exception ex)
            {
                RetData = ex.Message;
            }
            Con.Close();
        }

        return HttpUtility.HtmlDecode(RetData);
    }
Exemplo n.º 26
0
    protected void btTaiLen_Click1(object sender, EventArgs e)
    {
        if (Request.Cookies["ADMIN"] == null) return;
        if (FileUpload1.HasFile)
            try
            {
                string path = Server.MapPath(@"EXCEL\") + "Question_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx";
                FileUpload1.SaveAs(path);
                string txt = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;", path);
                OleDbConnection con = new OleDbConnection(txt);
                con.Open();
                txt = "Select * FROM [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(txt, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();

                gvCauHoi.DataSource = ds;
                gvCauHoi.DataBind();
                string temp;
                foreach (GridViewRow dr in gvCauHoi.Rows)
                {
                    temp = Server.HtmlDecode(dr.Cells[0].Text);
                    if (temp.StartsWith("#"))
                        dr.CssClass = "Q1";
                    else if (temp.StartsWith("$")) // Không đảo phương án trả lời
                        dr.CssClass = "Q2";
                    else if (temp.StartsWith("*")) // Phương án trả lời đúng
                        dr.CssClass = "A1";
                }
            }
            catch (Exception ex) { lbError.Text = ex.Message; }
    }
Exemplo n.º 27
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();
            }
        }
Exemplo n.º 28
0
    public DataSet GetDataSet(string filepath, string excelFileExtension)
    {
        try {
            System.Data.OleDb.OleDbConnection oledbcon = null;
            string strConn = string.Empty;
            switch (excelFileExtension.Trim()) {
                case "xls":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\"");
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
                    break;
                case "xlsx":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties=Excel 12.0;";
                    break;
            }

            //excel
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
            System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon);
            //select
            DataSet ds = new DataSet();
            oledbAdaptor.Fill(ds);
            oledbcon.Close();
            return ds;
        } catch (Exception ex) {
            throw ex;
        }
    }
Exemplo n.º 29
0
 private void btn_Browser_Click(object sender, EventArgs e)
 {
     OpenFileDialog P_open = new OpenFileDialog();//创建打开文件对话框对象
     P_open.Filter = "文本文件|*.txt|所有文件|*.*";//设置筛选字符串
     if (P_open.ShowDialog() == DialogResult.OK)
     {
         txt_Path.Text = P_open.FileName;//显示文件路径
         string conn = string.Format(//创建连接字符串
              @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=text",
              P_open.FileName.Substring(0,P_open.FileName.LastIndexOf(@"\")));
         OleDbConnection P_OleDbConnection = new OleDbConnection(conn);//创建连接对象
         try
         {
             P_OleDbConnection.Open();//打开连接
             OleDbCommand cmd = new OleDbCommand(//创建命令对象
                 string.Format("select * from {0}",
                 P_open.FileName.Substring(P_open.FileName.LastIndexOf(@"\"),
                 P_open.FileName.Length - P_open.FileName.LastIndexOf(@"\"))),
                 P_OleDbConnection);
             OleDbDataReader oda = cmd.ExecuteReader();//得到数据读取器对象
             while (oda.Read())
             {
                txt_Message.Text  += oda[0].ToString();//得到文本文件中的字符串
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message);//弹出消息对话框
         }
         finally
         {
             P_OleDbConnection.Close();//关闭连接
         }
     }
 }
Exemplo n.º 30
0
        private void button1_Click(object sender, EventArgs e)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                    @"Data source= C:\Users\OK\Documents\Library.accdb";

            try
            {
                conn.Open();
                String username = textBox1.Text.ToString();
                String Paswrd   = textBox2.Text.ToString();

                String my_querry = "select * from tblMembers where UserName = '******' and Pasword = '" + Paswrd + "'";


                OleDbCommand cmd = new OleDbCommand(my_querry, conn);
                //OleDbDataReader dr = cmd.ExecuteReader();
                var dr = cmd.ExecuteReader();

                if (dr.Read() == true)
                {
                    MessageBox.Show("Login Successful");

                    // MessageBox.Show(dr[0].ToString());
                    this.Hide();
                    //Form3 f3 = new Form3();
                    //f3.Show();
                    //this.Hide();
                    Form4 f4 = new Form4();
                    f4.Show();
                }
                else
                {
                    MessageBox.Show("Invalid Credentials, Please Re-Enter");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
Exemplo n.º 31
0
        /// <summary>
        ///  using the OleDb to Delete DataTable
        /// </summary>
        /// <param name="Path">路径</param>
        /// <param name="dt">DataTable</param>
        /// <param name="HDR">HDR true:读取column false:column行作为内容读取</param>
        public void DropTable(string sheetName, string Path, string HDR = "true")
        {
            string HDRStr = "YES";

            if (!HDR.Equals("true"))
            {
                HDRStr = "NO";
            }
            string   strCon    = string.Empty;
            FileInfo file      = new FileInfo(Path);
            string   extension = file.Extension;

            switch (extension)
            {
            case ".xls":
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;HDR=" + HDRStr + ";";
                break;

            case ".xlsx":
                strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=" + HDRStr + ";IMEX=0;'";
                break;

            default:
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=" + HDRStr + ";IMEX=0;'";
                break;
            }
            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon))
            {
                con.Open();

                System.Data.OleDb.OleDbCommand cmd;
                try
                {
                    cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", sheetName), con);    //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    con.Close();
                }
            }
        }
Exemplo n.º 32
0
        public int OutExcel()
        {
            DataTable dt = GetDataTable();

            string FileName = Guid.NewGuid().ToString().Substring(8) + ".xlsx";

            string sNewFullFile = "C:\\" + FileName;

            string strConn = GetConnectionString(sNewFullFile);

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            OleDbCommand cmd = null;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [Sheet1]( [位置编码] Text,[关联位置] Text,[公司编号] Text,[计划工厂] Text,[维护工厂] Text,[工厂区域] Text,[计划员组] Text,[成本中心] Text,[位置类型] Text,[位置状态] Text,[开始日期] Text,[修改日期] Text,[业务范围] Text,[是否安装] Text,[包含删除] Text,[维护班组] Text)", conn);
                cmd.ExecuteNonQuery();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var sRows = "INSERT INTO [Sheet1$] ([位置编码], [关联位置],[公司编号],[计划工厂],[维护工厂],[工厂区域],[计划员组],[成本中心],[位置类型],[位置状态],[开始日期],[修改日期],[业务范围],[是否安装],[包含删除],[维护班组]) VALUES (";
                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        sRows += "'" + dt.Rows[i][j] + "',";
                    }
                    sRows = sRows.TrimEnd(',');
                    string strSQL = sRows + ")";
                    cmd = new OleDbCommand(strSQL, conn);
                    cmd.ExecuteNonQuery();
                }
                return(1);
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                conn.Dispose();
            }
        }
        //打开恢复数据库专用的数据连接,用master数据库
        public string DBCreate(string str_Sql)
        {
            string myConnectionStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID="
                                     + ConfigurationSettings.AppSettings["WorkstationSet"]
                                     + ";Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master"
                                     + ";Data Source=" + SystemInformation.ComputerName + "\\MSDEDH"
                                     + ";User ID=" + ConfigurationSettings.AppSettings["DatabaseUser"]
                                     + ";Password="******"DatabasePassword"];

            myConnection = new System.Data.OleDb.OleDbConnection(myConnectionStr);

            try
            {
                myConnection.Open();
            }
            catch (SqlException e)
            {
                string errorMessage = e.Message;
                return(errorMessage);
            }

            if (HaveDB() == false)
            {
                myCommand = new System.Data.OleDb.OleDbCommand("CREATE DATABASE CityEduManage", myConnection);
                myCommand.ExecuteNonQuery();
                myCommand.Dispose();
            }

            //断开CityEduManage的一切连接
            //myCommand = new SqlCommand(str_Sql,myConnection);
            string str_Sql_DisConnect = "declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('"
                                        + ConfigurationSettings.AppSettings["Database"]
                                        + "') exec sp_msforeach_worker '?'";

            myCommand = new System.Data.OleDb.OleDbCommand(str_Sql_DisConnect, myConnection);
            myCommand.ExecuteNonQuery();
            myCommand.Dispose();

            myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection);
            myCommand.ExecuteNonQuery();
            myCommand.Dispose();

            myConnection.Close();
            return("OK");
        }
        public void XLSX2Grid(GridControl gc)
        {
            OpenFileDialog choofdlog = new OpenFileDialog();

            choofdlog.Filter = "Excel file (*.xlsx)|*.xlsx";
            choofdlog.ShowDialog();
            //string filePath = System.IO.Path.GetDirectoryName(choofdlog.FileName);
            string filePath = choofdlog.FileName;

            //XtraMessageBox.Show(filePath);
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.DataSet DtSet;
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            string conStr;

            if (Path.GetExtension(filePath).ToLower().Trim() == ".xls" && Environment.Is64BitOperatingSystem == false)
            {
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
                         ";Extended Properties=\"Excel 8.0;HDR= Yes" +
                         ";IMEX=0\"";
            }
            else
            {
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                         ";Extended Properties=\"Excel 12.0;HDR= Yes" +
                         ";IMEX=0\"";
            }
            MyConnection = new System.Data.OleDb.OleDbConnection(conStr);
            //XtraMessageBox.Show(MyConnection.State.ToString());
            if (choofdlog.ShowDialog() == DialogResult.OK)
            {
                MyConnection.Open();
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet$]", MyConnection);
                //MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1]", MyConnection);
                MyCommand.TableMappings.Add("Table", "Net-informations.com");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                gc.DataSource = DtSet.Tables[0];
                MyConnection.Close();
            }
            else
            {
                choofdlog.Dispose();
            }
        }
Exemplo n.º 35
0
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcel(string filename, bool isHdR)
        {
            string hdr = "N0";

            if (isHdR)
            {
                hdr = "YEX";
            }
            System.Data.DataSet itemDS = new DataSet();
            if (filename.Trim().ToUpper().EndsWith("XLS") || filename.Trim().ToUpper().EndsWith("XLSX"))
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HdR=" + hdr + ";\"";
                System.Data.OleDb.OleDbConnection conn       = null;
                System.Data.OleDb.OleDbCommand    oledbCommd = null;
                try
                {
                    conn = new System.Data.OleDb.OleDbConnection();
                    conn.ConnectionString = connStr;
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    //判断连接Excel sheet名
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr      = dt.Rows[i];
                        string  sqlText = "select * from [" + dr["TABLE_NAME"] + "]";
                        oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn);
                        oledbCommd.CommandTimeout = 100000;
                        //执行
                        System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd);
                        oledbDA.Fill(itemDS);
                    }
                }
                catch
                { }
                finally
                {
                    //释放
                    oledbCommd.Dispose();
                    conn.Close();
                }
                //创建连接
            }
            return(itemDS.Tables[0]);
        }
Exemplo n.º 36
0
    public void addItemToDatabase(string asin)
    {
        string   description     = getItemDescription(asin);
        string   price           = getCurrentPrice(asin);
        string   lastStoredPrice = "0";
        string   url             = getImageURL(asin);
        DateTime date            = DateTime.Now;

        OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString);

        conn.Open();
        OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();

        cmd.Connection  = conn;
        cmd.CommandText = "Insert Into User_Item (UserID, ItemID) values ('testuser', '" + asin + "')";
        cmd.ExecuteNonQuery();

        //only insert into price if price is different from current price
        try
        {
            cmd.CommandText = "Select Price From Price Where ItemID = '" + asin + "' Order By PriceDate DESC";
            lastStoredPrice = (cmd.ExecuteScalar()).ToString();
        }
        catch (Exception)
        {
            //do nothing if no record found in price
        }

        if (lastStoredPrice != price)
        {
            cmd.CommandText = "Insert Into Price(ItemID, Price, PriceDate) values ('" + asin + "', '" + price + "', '" + date + "')";
            cmd.ExecuteNonQuery();
        }

        try
        {
            cmd.CommandText = "Insert Into Item (ItemID, Description, ImageURL, CurPrice, CurDate) values ('" + asin + "', '" + description + "', '" + url + "', '" + price + "', '" + date + "')";
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            //do nothing if duplicate
        }
        conn.Close();
    }
Exemplo n.º 37
0
        protected void ListBox5_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListBox1.ClearSelection();
            ListBox2.ClearSelection();
            ListBox4.ClearSelection();
            ListBox3.ClearSelection();
            ListBox6.Items.Clear();
            // ListBox7.Items.Clear();
            String selection = ListBox5.SelectedItem.Text;

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                    @"Data source= C:\Users\OK\Documents\Ecafe.accdb";

            conn.Open();

            String       my_querry1 = "select Item from Items where Type = '" + selection + "'";
            OleDbCommand cmd1       = new OleDbCommand(my_querry1, conn);

            var dr1 = cmd1.ExecuteReader();

            // MessageBox.Show("Query executed");
            while (dr1.Read())
            {
                ListBox6.Items.Add(dr1[0].ToString());
                //MessageBox.Show(dr1[1].ToString());
            }
            Label2.Text      = selection;
            ListBox6.Visible = true;
            ListBox7.Visible = true;
            ListBox8.Visible = true;
            ListBox9.Visible = true;
            Label1.Visible   = true;
            Label2.Visible   = true;
            Label3.Visible   = true;
            Label5.Visible   = true;
            Label6.Visible   = true;
            Label7.Visible   = true;
            Label8.Visible   = true;
            Label9.Visible   = true;
            Label10.Visible  = true;
            TextBox2.Visible = true;
            Button1.Visible  = true;
            Button2.Visible  = true;
        }
        public Boolean DaliPostoiFirma()
        {
            String komanda = "EXECUTE spProveriFirma";

            OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija);
            OleDbCommand    OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn);
            OleDbDataReader dt;

            if (!daliPromenaImeCheckBox.Checked)
            {
                OleCm.Parameters.Add(new OleDbParameter("@Firma", this.companyNameTextBox.Text.Trim()));
            }
            else
            {
                OleCm.Parameters.Add(new OleDbParameter("@Firma", this.novoImeTextBox.Text.Trim()));
            }
            int rezultat = -1;

            try
            {
                OleCn.Open();
                dt = OleCm.ExecuteReader();
                if (dt.Read())
                {
                    rezultat = Convert.ToInt32(dt["Rezultat"].ToString());
                }
                dt.Close();
                OleCn.Close();
                if (rezultat >= 1)
                {
                    OleCn.Close();
                    return(true);
                }
                else if (rezultat == 0)
                {
                    OleCn.Close();
                    return(false);
                }
            }
            catch (Exception ex)
            {
                return(false);
            }
            return(false);
        }
Exemplo n.º 39
0
        public void enterTopic(TUser tUser, TTopic tTopic)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            System.Data.OleDb.OleDbDataReader  dbDataReader;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //Begin Transaction
                //Verify if the user is already on-line
                dbCommand.CommandText = "SELECT * FROM User_Topic WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL";
                dbDataReader          = dbCommand.ExecuteReader();
                if (dbDataReader.HasRows)
                {
                    dbDataReader.Close();
                    dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL";
                    dbCommand.ExecuteNonQuery();
                }
                dbDataReader.Close();
                dbCommand.CommandText = "INSERT INTO User_Topic (user_id, topic_id, startDateTime) VALUES ('" + tUser.id + "', '" + tTopic.id + "', '" + DateTime.Now.ToString() + "')";
                dbCommand.ExecuteNonQuery();
                tUser.topic = tTopic;
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex == -1)
            {
                MessageBox.Show("Please Select Series for Item");
            }
            if (textBox1.Text == "" || textBox1.Text == " ")
            {
                MessageBox.Show("Please Enter code for Item");
            }
            if (textBox2.Text == "" || textBox2.Text == " ")
            {
                MessageBox.Show("Please Enter Rate for Item");
            }

            if (comboBox1.SelectedIndex != -1 && textBox1.Text != "" && textBox2.Text != "")
            {
                String series, code;
                float  price;


                series = comboBox1.SelectedItem.ToString();
                code   = textBox1.Text;
                price  = float.Parse(textBox2.Text);

                OleDbConnection cnon = new System.Data.OleDb.OleDbConnection();
                //    cnon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database.accdb";
                cnon.ConnectionString = Utility.con;
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
                command.CommandText = "INSERT INTO Item(Series,Code,Rate)VALUES(@series,@code,@rate)";
                command.Parameters.AddWithValue("@series", series);
                command.Parameters.AddWithValue("@code", code);
                command.Parameters.AddWithValue("@rate", price);
                cnon.Open();
                command.Connection = cnon;
                command.ExecuteNonQuery();

                MessageBox.Show("Item Added");
                cnon.Close();



                clear_data();
            }
        }
Exemplo n.º 41
0
        //加载Excel
        public static DataTable getExcel(string filePos)
        {
            string  connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", filePos);
            DataSet ds = new DataSet();
            string  tableName;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tableName = table.Rows[0]["Table_Name"].ToString();
                string           strExcel = "select * from " + "[" + tableName + "]";
                OleDbDataAdapter adapter  = new OleDbDataAdapter(strExcel, connectionString);
                adapter.Fill(ds, tableName);
                connection.Close();
            }
            return(ds.Tables[tableName]);
        }
Exemplo n.º 42
0
        //確定更新按鈕
        private void Update_btn_Click(object sender, EventArgs e)
        {
            // === 對 Access 資料庫下SQL語法 ===
            //// Transact-SQL 陳述式
            String strSQL = "UPDATE [SystemSetting] SET Timer = " + Timer_num.Value + " ,MaxThread = " + Thread_num.Value + " ,MaxIOThread = " + ThreadIO_num.Value + " ,LimitNonPage = " + LimitNonPage_Num.Value + " ,LimitTimeRange = " + LimitTimeRange_Num.Value + " WHERE id=1";

            System.Data.OleDb.OleDbConnection oleConn =
                new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=FreeSpaceEyesDB.mdb");
            //// 開啟資料庫連接。
            oleConn.Open();
            //// OleDbCommand (String, OleDbConnection) : 使用查詢的文字和 OleDbConnection,初始化 OleDbCommand 類別的新執行個體。
            System.Data.OleDb.OleDbCommand oleCmd =
                new System.Data.OleDb.OleDbCommand(strSQL, oleConn);
            oleCmd.ExecuteNonQuery();
            //// 關閉資料庫連接。
            oleConn.Close();
            this.Close();
        }
Exemplo n.º 43
0
        public object GetPrimaryKey(SqlHelper sqlHelper, string dataBaseName, string tableName)
        {
            var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString);

            con.Open();
            var schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys,
                                                      new Object[] { null, null, tableName });

            con.Close();
            if (schemaTable == null || schemaTable.Rows.Count == 0)
            {
                return("");
            }
            object pk = null;

            pk = schemaTable.Rows[0].ItemArray[3].ToString().FirstLetterToUpper();
            return(pk);
        }
Exemplo n.º 44
0
        public DataTable CSVToDataGridViews(string filePath, DataGridView dgv)
        {
            char[] chr = new char[1] {
                '\\'
            };
            string[] strs   = filePath.Split(chr, StringSplitOptions.RemoveEmptyEntries);
            string   constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath.Substring(0, filePath.Length - strs[strs.Length - 1].Length) + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\"";

            System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr);
            con.Open();
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter("select * from [" + strs[strs.Length - 1] + "]", con);
            DataTable dt = new DataTable();

            adapter.Fill(dt);
            con.Close();
            return(dt);
            //dgv.DataSource = dt;
        }
Exemplo n.º 45
0
 public bool removeItemFromDatabase(string imageURL)
 {
     try
     {
         OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString);
         conn.Open();
         OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
         cmd.Connection  = conn;
         cmd.CommandText = "Delete From User_Item Where ItemID = (Select ItemID From Item Where ImageURL = '" + imageURL + "')";
         cmd.ExecuteNonQuery();
         conn.Close();
         return(true);
     }
     catch
     {
         return(false);
     }
 }
Exemplo n.º 46
0
        public static DataTable ReadFromExcal(string excalPath, string sheetName)
        {
            string connectionString = @"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + excalPath +
                                      ";Extended Properties=Excel 12.0 Xml";        // Connection string
            OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            string          excelQuery      = "Select * from [" + sheetName + "$]"; // SQl statement
            OleDbCommand    excelCommand    = new System.Data.OleDb.OleDbCommand(excelQuery, excelConnection);

            excelConnection.Open(); // open Connection
            OleDbDataReader excelReader;

            excelReader = excelCommand.ExecuteReader(); // excute quary
            DataTable excelTable = new DataTable();

            excelTable.Load(excelReader); // put the excal data into data table
            excelConnection.Close();
            return(excelTable);
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            System.Data.OleDb.OleDbConnection conn1 = new
                                                      System.Data.OleDb.OleDbConnection();
            // TODO: Modify the connection string and include any
            // additional required properties for your database.
            conn1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                     @"Data source= C:\Users\user\" +
                                     @"My Documents\Inventory.mdb";

            string       queryString1 = "UPDATE Inv Set product_cost = '" + TextBox1.Text + "', product_stock = '" + TextBox2.Text + "' WHERE product_name = '" + TextBox3.Text + "'";
            OleDbCommand command      = new OleDbCommand(queryString1, conn1);

            conn1.Open();
            OleDbDataReader reader = command.ExecuteReader();

            reader.Close(); conn1.Close();
        }
Exemplo n.º 48
0
        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="path">文件名称</param>
        /// <returns>返回一个数据集</returns>
        public DataSet ExcelToDs(string path)
        {
            var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            var conn    = new System.Data.OleDb.OleDbConnection(strConn);

            conn.Open();
            var strExcel = "";

            System.Data.OleDb.OleDbDataAdapter myCommand = null;
            DataSet ds = null;

            strExcel  = "select * from [Sheet1$]";
            myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
            ds        = new DataSet();
            myCommand.Fill(ds, "table1");
            conn.Close();
            return(ds);
        }
Exemplo n.º 49
0
        private void Getbuaan()
        {
            if (comboBox1.Text == "Online")
            {
                try
                {
                    cbBuaAn.Items.Clear();
                    Task <string> callTask = Task.Run(() => GetAllbuaan());
                    callTask.Wait();
                    string       astr  = callTask.Result;
                    List <BuaAn> buaan = JsonConvert.DeserializeObject <List <BuaAn> >(astr);

                    foreach (BuaAn ba in buaan)
                    {
                        cbBuaAn.Items.Add(ba.ten);
                    }
                }
                catch (AggregateException e)
                {
                    MessageBox.Show("Lỗi đường truyền!");
                }
            }
            else
            {
                string    filePath = Application.StartupPath + @"\Buaan\BuaAn.xls";
                DataTable table    = new DataTable();
                System.Data.OleDb.OleDbConnection MyConnection;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
                MyConnection.Open();
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                oada.Fill(table);
                MyConnection.Close();

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow drow = table.Rows[i];

                    if (drow.RowState != DataRowState.Deleted)
                    {
                        cbBuaAn.Items.Add(drow["ten"].ToString());
                    }
                }
            }
        }
Exemplo n.º 50
0
        //检查该原理图中的元件在库里是否存在对应型号
        private string CheckIndexName(string checkIndexnumber, string name, ref string indexname, ref string checkFootprint, ref string cavityType)
        {
            string checkindexname = null;

            System.Data.OleDb.OleDbConnection conncheck = new System.Data.OleDb.OleDbConnection();
            conncheck.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data source=F:\CavityLibrary\CavityLibrary.mdb";
            try
            {
                string indexcheck = null;
                string Footprint  = null;
                string CavityType = null;
                conncheck.Open();
                string          sqlcheck1    = @"select * from ComponentsDb where ComponentsDb.Name='" + name + @"'";
                OleDbCommand    sqlcmdcheck1 = new OleDbCommand(sqlcheck1, conncheck);
                OleDbDataReader dbreader1    = sqlcmdcheck1.ExecuteReader();
                while (dbreader1.Read())
                {
                    indexcheck = dbreader1["IndexName"].ToString();
                    Footprint  = dbreader1["Footprint"].ToString();
                    CavityType = dbreader1["CavityType"].ToString();
                }
                string          sqlcheck2    = @"select 索引编号 from " + indexcheck + " where " + indexcheck + ".编码='" + checkIndexnumber + @"'";
                OleDbCommand    sqlcmdcheck2 = new OleDbCommand(sqlcheck2, conncheck);
                OleDbDataReader dbreader2    = sqlcmdcheck2.ExecuteReader();
                while (dbreader2.Read())
                {
                    checkindexname = dbreader2["索引编号"].ToString();
                }
                indexname      = indexcheck;
                checkFootprint = Footprint;
                cavityType     = CavityType;
            }

            catch (OleDbException ex)
            {
                string s = ex.ToString();
                MessageBox.Show(s, "Failed to connect to data source", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conncheck.Close();
            }
            return(checkindexname);
        }
Exemplo n.º 51
0
        // Sing: Function to retrieve car details details from the database based on the care hire name.
        //This function returns a structure of car rental details
        public static cars getCarDetails(string carHire)
        {
            //Sing: Database connection string declarations
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
            DataTable       dtable  = new DataTable();
            OleDbCommand    command = new OleDbCommand();
            OleDbDataReader reader;

            //Sing : Login database connection
            connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=PrimaryDB.mdb;Jet OLEDB:Database Password=;";
            command.Connection          = connection;

            connection.Open();
            //Sing: SQL database query string
            string query = "SELECT * FROM Cars WHERE CarRentalCompany ='" + carHire + "'";

            command.CommandText = query;
            reader = command.ExecuteReader();

            //Sing: Instantiating a new car structure
            cars carDetails = new cars();

            //Sing: Get all details of car rental
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    if (reader["CarRentalCompany"].ToString() == carHire)
                    {
                        carDetails.numPlate    = reader["NumberPlate"].ToString();
                        carDetails.carMake     = reader["Make"].ToString();
                        carDetails.carModel    = reader["Model"].ToString();
                        carDetails.carType     = reader["CarType"].ToString();
                        carDetails.gearBox     = reader["GearBox"].ToString();
                        carDetails.seats       = reader["Seats"].ToString();
                        carDetails.pricePerDay = reader["pricePerDay"].ToString();
                    }
                }
                reader.Close();
            }
            connection.Close();
            //return car details
            return(carDetails);
        }
Exemplo n.º 52
0
        public string Upload(HttpPostedFileBase uploadFile)
        {
            StringBuilder strValidations = new StringBuilder(string.Empty);

            try
            {
                if (uploadFile.ContentLength > 0)
                {
                    string filePath = Path.Combine(HttpContext.Server.MapPath("../Uploads"),
                                                   Path.GetFileName(uploadFile.FileName));
                    uploadFile.SaveAs(filePath);
                    DataSet ds = new DataSet();

                    //A 32-bit provider which enables the use of

                    string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";

                    using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
                    {
                        conn.Open();
                        using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
                        {
                            string           sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            string           query     = "SELECT * FROM [" + sheetName + "]";
                            OleDbDataAdapter adapter   = new OleDbDataAdapter(query, conn);
                            //DataSet ds = new DataSet();
                            adapter.Fill(ds, "Items");
                            if (ds.Tables.Count > 0)
                            {
                                if (ds.Tables[0].Rows.Count > 0)
                                {
                                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                                    {
                                        //Now we can insert this data to database...
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex) { }
            return("");
        }
Exemplo n.º 53
0
        protected void Button1_Click(object sender, EventArgs e)
        {
            //Int32 p = Convert.ToInt32(ListBox7.Text) * Convert.ToInt32(txt_price.Text);

            String selection = ListBox6.SelectedItem.Text;

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                    @"Data source= C:\Users\OK\Documents\Ecafe.accdb";

            conn.Open();

            String       my_querry1 = "select Price from Items where Item = '" + selection + "'";
            OleDbCommand cmd1       = new OleDbCommand(@"select Price from Items where Item = @x1 ", conn);

            cmd1.Parameters.AddWithValue("@x1", selection);

            var dr1 = cmd1.ExecuteReader();
            var p   = "a";

            // MessageBox.Show(dr1.GetString(0));
            while (dr1.Read())
            {
                // MessageBox.Show(dr1[0].ToString());
                p = dr1[0].ToString();
                //ListBox7.Items.Add(dr1[0].ToString());
            }

            var ip = Convert.ToInt32(p);
            var q  = 1;

            q = Convert.ToInt32(TextBox2.Text);

            var qpr = ip * q;

            x += qpr;
            ListBox7.Items.Add(ListBox6.SelectedItem.Text);
            ListBox8.Items.Add(TextBox2.Text);
            ListBox9.Items.Add(qpr.ToString());

            Label1.Text      = x.ToString();
            Label11.Visible  = true;
            TextBox3.Visible = true;
        }
Exemplo n.º 54
0
        private void LoadExcelFile()
        {
            if (String.IsNullOrEmpty(FileName))
            {
                throw new Exception("Select file");
            }

            OleDbConnection  connExcel = new OleDbConnection();
            OleDbCommand     cmdExcel  = new OleDbCommand();
            OleDbDataAdapter oda       = new OleDbDataAdapter();

            connExcel           = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FileName + ";Extended Properties=Excel 12.0;");
            cmdExcel.Connection = connExcel;
            connExcel.Open();
            System.Data.DataTable dt = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                throw new Exception("File read error");
            }

            foreach (DataRow row in dt.Rows)
            {
                string t = row["TABLE_NAME"].ToString();
                EI.Add(new ExcelInfo {
                    ID = t, Sheet = CleanUpSheetName(t)
                });
            }

            CleanUpSheetName(EI[0].ID);

            connExcel.Close();

            GridControl1.BeginUpdate();
            GridControl1.DataSource = null;
            GridView1.Columns.Clear();

            GridControl1.DataSource = Ext.ToDataTable(EI);

            GridView1.Columns["ID"].Visible = false;

            GridView1.ClearSelection();
            GridControl1.EndUpdate();
        }
Exemplo n.º 55
0
        public void setPositionOfNewContentItem(string contentItemGroup)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            System.Data.OleDb.OleDbDataReader  dbDataReader;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //Begin Transaction
                //Verify if the user is already on-line
                dbCommand.CommandText = "SELECT MAX(id) FROM ContentItems WHERE item_group = '" + contentItemGroup + "'";
                dbDataReader          = dbCommand.ExecuteReader();
                if (dbDataReader.HasRows)
                {
                    dbDataReader.Read();
                    Int64 maxId = dbDataReader.GetInt32(0); //MAX id is the new item
                    dbDataReader.Close();
                    dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + maxId.ToString() + " WHERE (id = " + maxId.ToString() + ") AND (item_group = 'A')";
                    dbCommand.ExecuteNonQuery();
                }
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
Exemplo n.º 56
0
        public void GetTable(string Apath, ComboBox ComBox)
        {
            string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Apath + ";Persist Security Info=True";

            System.Data.OleDb.OleDbConnection tem_OleConn = new System.Data.OleDb.OleDbConnection(connstr);
            tem_OleConn.Open();
            DataTable tem_DataTable = tem_OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            tem_OleConn.Close();
            ComBox.Items.Clear();
            for (int i = 0; i < tem_DataTable.Rows.Count; i++)
            {
                ComBox.Items.Add(tem_DataTable.Rows[i][2]);
            }
            if (ComBox.Items.Count > 0)
            {
                ComBox.SelectedIndex = 0;
            }
        }
Exemplo n.º 57
0
        //------------------------------------------------------------------------------------------------------------------//
        //---------------------------------- Write To The Down Time Database -----------------------------------------------//
        //------------------------------------------------------------------------------------------------------------------//

        public short WriteToDownTime(List <List <string> > buffer)
        {
            string[] tempArray = new string[buffer.Count];
            List <List <List <string> > > finalBuffer = new List <List <List <string> > >();
            short ret = 0;

            char[] delim = { ',' };

            for (int i = 0; i < buffer.Count; i++)
            {
                List <List <string> > splitBuffer = new List <List <string> >();
                for (int j = 0; j < buffer[i].Count; j++)
                {
                    tempArray = buffer[i][j].Split(delim);

                    // tempArray.ToList();
                    List <string> tempSplit = new List <string>();

                    tempSplit = tempArray.ToList();
                    splitBuffer.Add(tempSplit);
                }

                try
                {
                    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB\downtime.accdb;Persist Security Info=False;");
                    conn.Open();
                    System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(@"INSERT into downtime(start, end, duration, reason) values(?,?,?,?)", conn);
                    cmd.Parameters.Add("startime", splitBuffer[0][1]);
                    cmd.Parameters.Add("endtime", splitBuffer[1][1]);
                    cmd.Parameters.Add("duration", splitBuffer[2][1]);
                    cmd.Parameters.Add("reason", splitBuffer[3][1]);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception e)
                {
                    conn.Close();
                    MessageBox.Show(e.ToString());
                }
            }

            return(ret);
        }
Exemplo n.º 58
0
        public static void Main()
        {
            Console.Title = "Add row to excel file";

            try
            {
                ReadExcelFile();

                Console.WriteLine("\n To add row fill data.");
                Console.Write("Enter name: ");
                string name = Console.ReadLine();

                Console.Write("Enter score: ");
                string scoreStr = Console.ReadLine();

                int score = int.Parse(scoreStr);

                OleDbConnection conn = new System.Data.OleDb
                                       .OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=scores.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";");

                conn.Open();

                using (conn)
                {
                    var command = new OleDbCommand(
                        "INSERT INTO [Sheet1$] VALUES(@name, @score);", conn);

                    command.Parameters.AddWithValue("@name", name);
                    command.Parameters.AddWithValue("@score", score);

                    var afectedRows = command.ExecuteNonQuery();

                    Console.WriteLine("afected rows: {0}\n", afectedRows);

                    ReadExcelFile();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Invalid Input!");
                Console.WriteLine(ex.Message);
            }
        }
Exemplo n.º 59
0
        private void ComprobarTipoImpresion(string p_ingr_nfolio_referencia, string p_ting_ccod, string p_pers_ncorr)
        {
            OleDbCommand comando = new OleDbCommand();
            int          v_eing_ccod;


            oleDbConnection1.Open();

            comando.Connection  = oleDbConnection1;
            comando.CommandText = "select distinct a.eing_ccod from ingresos a where cast(a.ingr_nfolio_referencia as varchar) = '" + p_ingr_nfolio_referencia + "' and cast(a.ting_ccod as varchar)= '" + p_ting_ccod + "' and cast(a.pers_ncorr as varchar)= '" + p_pers_ncorr + "'";
            OleDbDataReader dr = comando.ExecuteReader();

            dr.Read();
            v_eing_ccod = (int)dr.GetDecimal(0);

            b_intereses_repactacion = ((v_eing_ccod == 7) && (!dr.Read())) ? true : false;

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

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

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

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