C# OleDb.OleDbCommand类代码示例

        private void lc(object sender, MouseEventArgs e)
        {
            c.Open();
            DataSet ds = new DataSet();
            string query = "select ID,pname,bill,pbill from pdetails where [email protected] ";
            OleDbCommand cmd = new OleDbCommand(query, c);
            cmd.Parameters.Add("@bc", OleDbType.Date).Value = dateTimePicker1.Value.Date;
            OleDbDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            /*ds.Tables.Add(dt);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.Fill(dt);*/
            dataGridView1.DataSource = dt.DefaultView;
            c.Close();
            try
            {
                c.Open();
                String str = @"SELECT SUM(pbill) FROM pdetails WHERE [email protected];";

                OleDbCommand comm2 = new OleDbCommand(str, c);
                comm2.Parameters.Add("@bb", OleDbType.Date).Value = dateTimePicker1.Value.Date;
                bill = Convert.ToDouble(comm2.ExecuteScalar());

                label3.Text = bill.ToString() + "/-";
            }
            catch(Exception ex)
            {
                MessageBox.Show("selected date miss match");
                c.Close();
            }
                c.Close();
        }

        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;
        }

        public void FillCombo()
        {
            try
            {

                con = new OleDbConnection(cs);
                con.Open();
                string ct = "select RTRIM(CategoryName) from Category order by CategoryName";
                cmd = new OleDbCommand(ct);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    cmbCategory.Items.Add(rdr[0]);
                }
                con.Close();
                con = new OleDbConnection(cs);
                con.Open();
                string ct1 = "select RTRIM(CompanyName) from Company order by CompanyName";
                cmd = new OleDbCommand(ct1);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    cmbCompany.Items.Add(rdr[0]);
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

    public int EnterToSite(UserDetails userDetails)//בודק על פי האימייל והסיסמא האם המשתשמש שמנסה להיכנס לאתר קיים או לא
    {
        OleDbCommand myCmd = new OleDbCommand("CheckIfUserExist", myConnection);
        myCmd.CommandType = CommandType.StoredProcedure;

        OleDbParameter objParam;

        objParam = myCmd.Parameters.Add("@Email", OleDbType.BSTR);
        objParam.Direction = ParameterDirection.Input;
        objParam.Value = userDetails.email;

        objParam = myCmd.Parameters.Add("@Password", OleDbType.BSTR);
        objParam.Direction = ParameterDirection.Input;
        objParam.Value = userDetails.password;

        int x = 0;
        try
        {
            myConnection.Open();
            x = (int)myCmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConnection.Close();
        }
        return x;

    }

        protected void datefilterPlanMeal_DayRender(object sender, DayRenderEventArgs e)
        {
            OleDbCommand command = new OleDbCommand("SELECT * FROM PlannedMeal WHERE UserDataID = " + userID + " ORDER BY CreatedDate DESC", myConnection);

            OleDbDataReader dr = command.ExecuteReader();
            // Read DataReader till it reaches the end
            while (dr.Read() == true)
            {
                // Assign the Calendar control dates
                // already contained in the database
                //datefilterPlanMeal.SelectedDates.Add((DateTime)dr["CreatedDate"]);
                if (e.Day.Date == (DateTime)dr["CreatedDate"])
                {
                    e.Cell.BackColor = System.Drawing.Color.Silver;
                }
            }
            if (e.Day.IsSelected)
            {
                e.Cell.BackColor = System.Drawing.ColorTranslator.FromHtml("#4db6ac");
                e.Cell.ForeColor = System.Drawing.Color.White;
            }

            // Close DataReader
            dr.Close();
        }

 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();
 }

    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);
    }

    protected void btnAddBag_Click(object sender, EventArgs e)
    {
        try
        {

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

            com.ExecuteNonQuery();

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

            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write("Error: " + ex.ToString());
            lblMessage.Text = "Error" + txtBagName.Text + " !";
        }
    }

 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param>		
 public static void ExecuteSqlTran(ArrayList SQLStringList)
 {
     using (OleDbConnection conn = new OleDbConnection(connectionString))
     {
         conn.Open();
         OleDbCommand cmd = new OleDbCommand();
         cmd.Connection = conn;
         OleDbTransaction tx = conn.BeginTransaction();
         cmd.Transaction = tx;
         try
         {
             for (int n = 0; n < SQLStringList.Count; n++)
             {
                 string strsql = SQLStringList[n].ToString();
                 if (strsql.Trim().Length > 1)
                 {
                     cmd.CommandText = strsql;
                     cmd.ExecuteNonQuery();
                 }
             }
             tx.Commit();
         }
         catch (System.Data.OleDb.OleDbException E)
         {
             tx.Rollback();
             throw new Exception(E.Message);
         }
     }
 }

        protected void Page_Load(object sender, System.EventArgs e)
        {
            // resolve the address to the Access database
            string fileNameString = this.MapPath(".");
            fileNameString += "..\\..\\..\\..\\data\\chartdata.mdb";

            // initialize a connection string
            string myConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameString;

            // define the database query
            string mySelectQuery="SELECT GrossSales FROM SALES WHERE QuarterEnding < #01/01/2002#;";

            // create a database connection object using the connection string
            OleDbConnection myConnection = new OleDbConnection(myConnectionString);

            // create a database command on the connection using query
            OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);

            // open the connection
            myCommand.Connection.Open();

            // create a database reader
            OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // since the reader implements and IEnumerable, pass the reader directly into
            // the DataBind method with the name of the Column selected in the query
            Chart1.Series["Default"].Points.DataBindY(myReader, "GrossSales");

            // close the reader and the connection
            myReader.Close();
            myConnection.Close();
        }

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

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

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

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

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

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

    protected void Button1_Click(object sender, EventArgs e)
    {
        int lgflg = 0;

        OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["classDB"]);
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM student WHERE studentpassword = '" + password.Text + "' AND email = '" + userName.Text + "'", conn);

        conn.Open();
        OleDbDataReader myReader = cmd.ExecuteReader();

        while (myReader.Read())
        {
            lgflg = 1;
            Session.Add("fname", myReader["Contact_name"]);
            Session.Add("address", myReader["address"]);
            Session.Add("city", myReader["city"]);
            Session.Add("state", myReader["state"]);
            Session.Add("zipcode", myReader["zipcode"]);
        }
        myReader.Close();
        conn.Close();

        if (lgflg == 1)
        {
            Response.Write(Session["fname"]);
        }
        else
        {
            error.Visible = true;
        }
    }

 public IDataReader ExecuteDataReader(string connectionString, string query)
 {
     _connection = new OleDbConnection(connectionString);
     _connection.Open();
     var command = new OleDbCommand(query, _connection);
     return command.ExecuteReader();
 }

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

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

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

        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();
                }
            }
        }