C# OleDbConnection.CreateCommand方法代码示例

        //public string ExcelFile {private get; set; }
        public static DataTable ReadData(string excelFile)
        {
            if (!System.IO.File.Exists(excelFile))
                return null;

            OleDbConnection excelConnection = new OleDbConnection();
            excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile);
            excelConnection.Open();
            DataTable dtSchema = excelConnection.GetSchema("Tables");
            if (dtSchema.Rows.Count == 0)
                return null;

            string strTableName = dtSchema.Rows[0]["Table_Name"] as string;
            string strSQL = string.Format("select * from [{0}]", strTableName);
            OleDbCommand cmdSelect = excelConnection.CreateCommand();
            cmdSelect.CommandText = strSQL;
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect);
            DataTable dtResult=new DataTable();
            dbAdapter.Fill(dtResult);

            dbAdapter.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();

            return dtResult;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            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 = "select cin from assiste where id_module=" + label1.Text + " group by cin";
          
            try
            {
                con.Open();
                OleDbDataReader dr = cmd.ExecuteReader();
        
                while (dr.Read())
                {
                    //dataGridView1.Rows.Add(dr.GetValue(0), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4), dr.GetValue(0));
                    cmd1.CommandText="select nom,prenom from etudiant where cin="+dr.GetValue(0);
                    
                    try { OleDbDataReader dr1 = cmd1.ExecuteReader(); dr1.Read(); dataGridView1.Rows.Add(dr.GetValue(0), dr1.GetValue(0), dr1.GetValue(1),0); }
                    catch (Exception ex) { }
                    
                }
                dr.Close();
                con.Close();

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

    public void check_privillage()
    {
        string Select_SQL, Select_SQL2, Select_SQL3, Update_SQL1;
        OleDbConnection SqlConnection1 = new OleDbConnection();
        SqlConnection1.ConnectionString = AccessDataSource1.ConnectionString;
        Select_SQL = "select module_name,page_name,user_name from module_user_mng,user_mng,module_mng where module_mng.module_id = module_user_mng.module_id and user_mng.user_id = module_user_mng.user_id and page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "' and user_name='" + Session["xsctintranet"] + "'";

        SqlConnection1.Open();
        OleDbCommand SqlCommand = SqlConnection1.CreateCommand();
        SqlCommand.CommandText = Select_SQL;
        OleDbDataReader SqlDataReader = SqlCommand.ExecuteReader();
        if (SqlDataReader.HasRows)
        {
            while (SqlDataReader.Read())
            {
                OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand();
                Update_SQL1 = "insert into visit_mng (visit_module_name,visit_user_name,visit_date,visit_ip,visit_module_page) VALUES ('" + SqlDataReader.GetString(0) + "','" + SqlDataReader.GetString(2) + "','" + DateTime.Now.ToString() + "','" + Page.Request.UserHostAddress + "','" + SqlDataReader.GetString(1) + "')";
                SqlCommand2.CommandText = Update_SQL1;
                SqlCommand2.ExecuteNonQuery();

            }
        }
        else
        {
            OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand();
            Select_SQL2 = "select * from module_mng where page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "'";
            SqlCommand2.CommandText = Select_SQL2;
            OleDbDataReader SqlDataReader2 = SqlCommand2.ExecuteReader();
            if (SqlDataReader2.HasRows)
            {
                Response.Write("您没有访问该页面的权限！");
                SqlDataReader2.Close();
                SqlDataReader.Close();
                SqlConnection1.Close();
                Response.End();
            }
            else
            {
                OleDbCommand SqlCommand3 = SqlConnection1.CreateCommand();
                Select_SQL3 = "select * from user_mng where user_name='" + Session["xsctintranet"] + "'";
                SqlCommand3.CommandText = Select_SQL3;
                OleDbDataReader SqlDataReader3 = SqlCommand3.ExecuteReader();
                if (SqlDataReader3.HasRows)
                { }
                else
                {
                    Response.Write("您没有访问该页面的权限！");
                    SqlDataReader3.Close();
                    SqlDataReader2.Close();
                    SqlDataReader.Close();
                    SqlConnection1.Close();
                    Response.End();
                }
                SqlDataReader3.Close();
            }
            SqlDataReader2.Close();
        }
        SqlDataReader.Close();
        SqlConnection1.Close();
    }

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


        }

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





        }

        public static void CreateSyncTimestamp()
        {
            string DPDBLocation = Properties.Settings.Default.DPDBLocation;
            try
            {
                OleDbConnection DPDBconnection = null;

                DPDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + DPDBLocation);
                DPDBconnection.Open();

                OleDbCommand syncCmd = DPDBconnection.CreateCommand();

                string commandText = @"INSERT INTO Syncs (sync_datetime)
                                 VALUES (#$latestSync#)";

                commandText = commandText.Replace("$latestSync", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //Save latest sync.
                syncCmd.CommandText = commandText;
                int numRowsAffected = syncCmd.ExecuteNonQuery();
                DPDBconnection.Close();
            }
            catch
            {
                throw;
            }
        }

        public List<InventoryPurchaseInfo> getAllInventoryPurchaseInfo()
        {
            List<InventoryPurchaseInfo> allInventoryPurchaseInfo = new List<InventoryPurchaseInfo>();
            using(OleDbConnection sqlconn = new OleDbConnection(database))
            {
                try
                {
                    sqlconn.Open();
                    OleDbCommand cmd = sqlconn.CreateCommand();

                    String select = "SELECT [inventory_item_id] FROM [INVENTORY_PURCHASE_INFO]";
                    cmd.CommandText = select;
                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        InventoryPurchaseInfo purchaseInfo = getSingleInventoryPurchaseInfo((int)reader["inventory_item_id"]);

                        allInventoryPurchaseInfo.Add(purchaseInfo);
                    }
                    return allInventoryPurchaseInfo;
                }
                catch (OleDbException ex)
                {
                    return allInventoryPurchaseInfo;
                }
                finally
                {
                    sqlconn.Close();
                }
            }
        }

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

        private void button2_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();
            cmd.CommandText = "update enseignant set mdp='" + txt_g_password.Text + "',nom=' "+txt_g_family.Text+"',prenom='"+txt_g_name.Text+"', grade='"+txt_g_grade.Text+"' where login='"+login+"'";
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Modification Réussi");
                txt_g_password.Text = "";
                txt_g_family.Text = "";
                txt_g_name.Text = "";
                txt_g_grade.Text = "";
                con.Close();
                new acceuil(login).Show();
                this.Hide();

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

		internal override void LoadAll()
		{
			try
			{
				ISeriesDatabase database = (ISeriesDatabase)this.dbRoot.ClassFactory.CreateDatabase();
				//database._name = cn.DataSource;

				OleDbConnection cn = new OleDbConnection(this.dbRoot.ConnectionString); 
				cn.Open();
				OleDbCommand cmd = cn.CreateCommand();
				cmd.CommandText = "SELECT c.CATALOG_NAME, t.TABLE_SCHEMA FROM SYSTABLES t, QSYS2.SYSCATALOGS c";
				OleDbDataReader reader = cmd.ExecuteReader();
				while(reader.Read()) 
				{
					//database. = reader["CATALOG_NAME"].ToString();
					database._name = reader["TABLE_SCHEMA"].ToString();
					break;
				}
				reader.Close();
				cn.Close();
	
				database.dbRoot = this.dbRoot;
				database.Databases = this;
				this._array.Add(database);
			}
			catch (Exception ex)
			{
				string message = ex.Message;
			}
		}

 public static void Main()
 {
     String connect = "Provider=Microsoft.JET.OLEDB.4.0;"
       + @"data source=c:\booksharp\gittleman\ch15\Sales.mdb";
     OleDbConnection con = new OleDbConnection(connect);
     con.Open();
     Console.WriteLine
       ("Made the connection to the Sales database");
     OleDbCommand cmd = con.CreateCommand();
     cmd.CommandText = "SELECT * FROM Customer";
     OleDbDataReader reader = cmd.ExecuteReader();
     XmlDocument document = new XmlDocument();
     XmlElement customers = document.CreateElement("customers");
     document.AppendChild(customers);
     while (reader.Read()) {
       XmlElement customer = document.CreateElement("customer");
       customers.AppendChild(customer);
       XmlElement name = document.CreateElement("name");
       customer.AppendChild(name);
       name.AppendChild
        (document.CreateTextNode(reader.GetString(1)));
       XmlElement address = document.CreateElement("address");
       customer.AppendChild(address);
       address.AppendChild
        (document.CreateTextNode(reader.GetString(2)));
       XmlElement balance = document.CreateElement("balance");
       customer.AppendChild(balance);
       Decimal b = reader.GetDecimal(3);
       balance.AppendChild
                   (document.CreateTextNode(b.ToString()));
     }
     document.Save(Console.Out);
     reader.Close();
 }

        public byte[] GetRommList()
        {
            try
            {
                string strConnection = ConfigurationManager.ConnectionStrings["DigiLock"].ConnectionString;
                OleDbConnection objConnection = new OleDbConnection(strConnection);
                objConnection.Open();//打开连接
                OleDbCommand odCommand = objConnection.CreateCommand();
                odCommand.CommandText = "SELECT ROOMNO,STATE,CARDQTY FROM ROOMLIST";
                OleDbDataReader reader = odCommand.ExecuteReader();
                List<RoomListModel> Roomlistmodel = new List<RoomListModel>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomListModel mRoom = new RoomListModel();
                        mRoom.State = dbh.ToString(reader["STATE"]);
                        mRoom.RoomNo = reader["RoomNo"].ToString().Substring(1, 3);
                        mRoom.CardQTY = dbh.ToInt32(reader["CARDQTY"]);
                        Roomlistmodel.Add(mRoom);
                    }
                    reader.Close();
                }
                objConnection.Close();
                return cmn.SerializeObject(Roomlistmodel);
            }
            catch (Exception err)
            {

                throw err;
            }
        }

        public JsonResult changeProduct(string productName)
        {
            string allInfo = "";
            OleDbConnection conn1 = null;
            conn1 = new OleDbConnection(
            "Provider=Microsoft.Jet.OLEDB.4.0; " +
            "Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~") + ("App_Data\\Products.mdb"));
            conn1.Open();
            OleDbDataReader dbReader1 = null;
            OleDbCommand cmd1 = conn1.CreateCommand();
            string allProductInfo = "SELECT * from Products where Products.product_name='" + productName + "';";
            cmd1.CommandText = allProductInfo;
            dbReader1 = cmd1.ExecuteReader();
            List<string> list = new List<string>();
            while (dbReader1.Read())
            {
                allInfo = "@" + (string)dbReader1.GetValue(1) + "@" + (string)dbReader1.GetValue(2) + "@" +
                    (string)dbReader1.GetValue(3) + "@" + (string)dbReader1.GetValue(4) + "@" +
                    (string)dbReader1.GetValue(5) + "@" + (string)dbReader1.GetValue(6);
                list.Add(allInfo);
                allInfo = "";
            }
            dbReader1.Close();
            list.Reverse();
            list.Insert(0, list.Count.ToString());
            conn1.Close();

            return Json(list, JsonRequestBehavior.AllowGet);
        }

 /// <summary>
 /// 获取inpno
 /// </summary>
 /// <param name="p_strPatientId"></param>
 /// <param name="p_strVisitId"></param>
 /// <returns></returns>
 public string GetInpNo(string p_strPatientId, string p_strVisitId)
 {
     DataSet _dsR = new DataSet();
     string _strSQL = string.Format("select inp_no from pat_master_index where patient_id = '{0}'", p_strPatientId);
     ConnectionStringSettings sEmr = ConfigurationManager.ConnectionStrings["EMRConnectionString"];
     using (OleDbConnection connEMR = new OleDbConnection(sEmr.ConnectionString))
     {
         try
         {
             connEMR.Open();
             OleDbCommand cmdEMR = connEMR.CreateCommand();
             OleDbDataAdapter daEMR = new OleDbDataAdapter();
             cmdEMR.CommandText = _strSQL;
             daEMR.SelectCommand = cmdEMR;
             daEMR.Fill(_dsR);
             if (_dsR.Tables[0].Rows.Count == 1)
             {
                 return _dsR.Tables[0].Rows[0]["inp_no"].ToString();
             }
         }
         catch (Exception ex)
         {
             throw ex;
         }
     }
     return "";
 }

        /// <summary>
        /// Read the specified Excel file and returns the content
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        public DataTable Read(string excelFile)
        {
            string connectionString = string.Empty;

            string fileExtension = Path.GetExtension(excelFile);
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            }

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

                using (OleDbCommand command = conn.CreateCommand())
                {
                    DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString();

                    command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);

                    using (OleDbDataAdapter da = new OleDbDataAdapter(command))
                    {
                        var dt = new DataTable();

                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }