C# OleDb.OleDbCommand类代码示例

        private void buttonInfoMessage_Click(object sender, EventArgs e)
        {

            /**** Create this Store Procedure in the tempdb database first ****
                        Create Proc TestInfoMessage
                        as
                        Print 'Using a Print Statement'
                        RaisError('RaisError in Stored Procedures', 9, 1)
            ****************************************************/

            //1. Make a Connection
            System.Data.OleDb.OleDbConnection objOleCon = new System.Data.OleDb.OleDbConnection();
            objOleCon.ConnectionString = strConnection;
            objOleCon.Open();

            objOleCon.InfoMessage += new System.Data.OleDb.OleDbInfoMessageEventHandler(objOleCon_InfoMessage);

            //2. Issue a Command
            System.Data.OleDb.OleDbCommand objCmd;
            objCmd = new System.Data.OleDb.OleDbCommand("Raiserror('Typical Message Goes Here', 9, 1)", objOleCon);
            objCmd.ExecuteNonQuery();
            objCmd = new System.Data.OleDb.OleDbCommand("Exec TestInfoMessage", objOleCon); //This executes the stored procedure.
            objCmd.ExecuteNonQuery();

            //3. Process the Results
            /** No Results at this time **/

            //4. Clean up code
            objOleCon.Close();
        }

 public DataTable SelectToDataTable(string sql)
 {
     //set connection
     System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
     con.ConnectionString = connectString;
     //set commandtext
     System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
     command.CommandText = sql;
     command.Connection = con;
     //set adapter
     System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
     adapter.SelectCommand = command;
     //creat a datatable
     DataTable dt = new DataTable();
     try
     {
         //open this connection
         con.Open();
         adapter.Fill(dt);
     }
     catch (Exception ex)
     {
         //throw new Exception
         con.Close();
     }
     finally
     {
         //close this connection
         con.Close();
     }
     //return a datatable
     return dt;
 }

 public System.Collections.Generic.List<string> GetColumnNames(string file, string table)
 {
     System.Data.DataTable dataSet = new System.Data.DataTable();
     string connString = "";
     if (Global.filepassword != "")
     {
         if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
         else
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
     }
     else
     {
         if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file;
         else
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;";
     }
     using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString))
     {
         connection.Open();
         System.Data.OleDb.OleDbCommand Command = new System.Data.OleDb.OleDbCommand("SELECT * FROM " + table, connection);
         using (System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(Command))
         {
             dataAdapter.Fill(dataSet);
         }
     }
     System.Collections.Generic.List<string> columns = new System.Collections.Generic.List<string>();
     for (int i = 0; i < dataSet.Columns.Count; i++)
     {
         columns.Add(dataSet.Columns[i].ColumnName);
     }
     return columns;
 }

        private void button3_Click(object sender, EventArgs e)
        {   // Botão btnResultados
            try
            {
                string id1 = txtIdade1.Text;
                string id2 = txtIdade2.Text;
                string strSsexo = "";
                if (rbMasculino.Checked) strSsexo = " sexo = 'M' AND ";
                    else if (rbFeminino.Checked) strSsexo = " sexo = 'F' AND ";
                        else strSsexo = "";

                sql = "select * from alunos WHERE " + strSsexo 
                    + " idade>=" + id1 + " AND idade<=" + id2 
                    + " ORDER BY nome";
                stm = new System.Data.OleDb.OleDbCommand(sql, conexao);
                rs = stm.ExecuteReader();
                string strSaida = "";
                while (rs.Read())
                {
                    string dados = "RGM: " + rs.GetString(0) 
                        + ", " + rs.GetString(1) + ", idade: " + rs.GetInt32(3)
                        + ", sexo: " + rs.GetString(4)
                        + ", curso: " + rs.GetString(2);
                    strSaida += dados + "\r\n";
                }
                txtResultados.Text = strSaida;
            }
            catch (Exception exsql) { MessageBox.Show("Erro na consulta"); }
            if(stm!=null)stm.Dispose();
            if(rs!=null)rs.Close();
        }

 /// <summary>
 /// M�todo necesario para admitir el Dise�ador, no se puede modificar
 /// el contenido del m�todo con el editor de c�digo.
 /// </summary>
 private void InitializeComponent()
 {
     System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
     this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
     this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
     this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
     this.datosInforme1 = new informe_1.datosInforme();
     ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).BeginInit();
     //
     // oleDbDataAdapter1
     //
     this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
     this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                                 new System.Data.Common.DataTableMapping("Table", "Table", new System.Data.Common.DataColumnMapping[] {
                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("NOMBRE", "NOMBRE")})});
     //
     // oleDbSelectCommand1
     //
     this.oleDbSelectCommand1.CommandText = "SELECT \'\' AS NOMBRE FROM DUAL";
     this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
     //
     // oleDbConnection1
     //
     this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string))));
     //
     // datosInforme1
     //
     this.datosInforme1.DataSetName = "datosInforme";
     this.datosInforme1.Locale = new System.Globalization.CultureInfo("es-ES");
     this.datosInforme1.Namespace = "http://www.tempuri.org/datosInforme.xsd";
     this.Load += new System.EventHandler(this.Page_Load);
     ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).EndInit();
 }

 public void insertImage(string wkImg)
 {
     System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
     cmd.CommandText = "Insert into [" + TableName + "] (imageName) values('" + wkImg + "')  ";
        cmd.Connection = conn;
         cmd.ExecuteNonQuery();
 }

            public void DBSetup()
            {
                // +++++++++++++++++++++++++++  DBSetup function +++++++++++++++++++++++++++
                // This DBSetup() method instantiates all the DB objects needed to access a DB, 
                // including OleDbDataAdapter, which contains 4 other objects(OlsDbSelectCommand, 
                // oleDbInsertCommand, oleDbUpdateCommand, oleDbDeleteCommand.) And each
                // Command object contains a Connection object and an SQL string object.
                OleDbDataAdapter2 = new System.Data.OleDb.OleDbDataAdapter();
                OleDbSelectCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbInsertCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbUpdateCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbDeleteCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbConnection2 = new System.Data.OleDb.OleDbConnection();

                OleDbDataAdapter2.DeleteCommand = OleDbDeleteCommand2;
                OleDbDataAdapter2.InsertCommand = OleDbInsertCommand2;
                OleDbDataAdapter2.SelectCommand = OleDbSelectCommand2;
                OleDbDataAdapter2.UpdateCommand = OleDbUpdateCommand2;

                // The highlighted text below should be changed to the location of your own database

                OleDbConnection2.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Reg" + "istry Path =; Jet OLEDB:Database L" +
                "ocking Mode=1;Data Source= C:\\Users\\Trenton MCleod\\Desktop\\RegistrationDB.mdb;J" +
                "et OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System datab" +
                "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" +
                "hare Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet " +
                "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repai" +
                "r=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";

            }  //end DBSetup()

        /// <summary>
        /// Create access database file
        /// </summary>
        public static void create_mdb_file(DataSet ds, string destination, string template)
        {
            if(SqlConvert.ToString(template) == "")
                throw new Err("You must specify the location of a template mdb file inherit from.");
            if(!System.IO.File.Exists(template))
                throw new Err("The specified template file \"" + template + "\" could not be found.");
            if(SqlConvert.ToString(destination) == "")
                throw new Err("You must specify the destination location and filename of the mdb file to create.");

            //COPY THE TEMPLATE AND CREATE DESTINATION FILE
            System.IO.File.Copy(template,destination,true);
            //CONNECT TO THE DESTINATION FILE
            string sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destination + ";";
            System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection(sconn);
            oconn.Open();
            System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
            ocmd.Connection = oconn;
            //WRITE TO THE DESTINATION FILE
            try
            {
                oledb.insert_dataset(ds,ocmd);
            }
            catch(Exception ex)
            {
                //System.Web.HttpContext.Current.Response.Write(ex.ToString());
                General.Debugging.Report.SendError("Error exporting to MS Access", ex.ToString() + "\r\n\r\n\r\n" + ocmd.CommandText);
            }
            finally
            {
                // Close the connection.
                oconn.Close();
            }
        }

        public virtual int FillByWhereClause(AccountDataset.AccountsDataTable dataTable, string whereClause, System.Data.OleDb.OleDbParameter[] parameters)
        {
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
            command.Connection = this.Connection;
            command.CommandText = @"select * from
            (Select ""C-"" & CustomerID as ID, ""Customer"" AS CustomerSupplierFlag,
            CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, '' as HomePage, CreateID, CreateUser, ModifyID,  ModifyUser

             from Customers
            Union
            Select ""S-"" &  SupplierID as ID, ""Supplier"" AS CustomerSupplierFlag,

            CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage, CreateID, CreateUser, ModifyID,  ModifyUser
            FROM         Suppliers) as a "
            + whereClause + ";";
            command.CommandType = System.Data.CommandType.Text;

            if (null != parameters)
                command.Parameters.AddRange(parameters);

            this.Adapter.SelectCommand = command;
            if ((this.ClearBeforeFill == true))
            {
                dataTable.Clear();
            }
            int returnValue = this.Adapter.Fill(dataTable);
            return returnValue;
        }

 /// <summary>
 /// M�todo necesario para admitir el Dise�ador, no se puede modificar
 /// el contenido del m�todo con el editor de c�digo.
 /// </summary>
 private void InitializeComponent()
 {
     this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
     this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
     this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
     this.dsFicha1 = new ficha_antecedentes_personales.dsFicha();
     ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).BeginInit();
     //
     // oleDbConnection1
     //
     this.oleDbConnection1.ConnectionString = "Provider=SQLOLEDB;server=edoras;OLE DB Services = -2;uid=protic;pwd=,.protic;init" +
         "ial catalog=protic2";
     //
     // oleDbSelectCommand1
     //
     this.oleDbSelectCommand1.CommandText = @"select '' as nombre, '' as rut, '' as pasaporte, '' as fecha_nac, '' as fono, '' as nacionalidad, '' as Estado_civil, '' as Direccion, '' as comuna, '' as ciudad, '' as region, '' as colegio_egreso, '' as ano_egreso, '' as proced_educ, '' as inst_educ_sup, '' as Carrera, '' as ano_ingr, '' as FinanciaEst, '' as ultimo_post_ncorr, '' as nombre_sost_ec, '' as RUT_sost_ec, '' as fnac_sost_ec, '' as edad_sost, '' as fono_sost_ec, '' as pare_sost_ec, '' as dire_tdesc_sost_ec, '' as comu_sost_ec, '' as ciud_sost_ec, '' as regi_sost_ec";
     this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
     //
     // oleDbDataAdapter1
     //
     this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
     //
     // dsFicha1
     //
     this.dsFicha1.DataSetName = "dsFicha";
     this.dsFicha1.Locale = new System.Globalization.CultureInfo("es-CL");
     this.dsFicha1.Namespace = "http://www.tempuri.org/dsFicha.xsd";
     this.Load += new System.EventHandler(this.Page_Load);
     ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).EndInit();
 }

        protected void btnInsert_Click(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/products.mdb");
            Conn.Open();
            //Response.Write(Conn.State);

            string strSQL = "insert into [users] ([username], firstname, lastname, email, [password]) values (?,?,?,?,?)";
            object returnVal;

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

            Comm.CommandText = "select [username] from [users] where [username] = ?";
            Comm.Parameters.AddWithValue("[username]", txtUserName.Text);
            returnVal = Comm.ExecuteScalar();
            if (returnVal == null)
            {
                Comm.Parameters.Clear();
                Comm.CommandText = strSQL;
                Comm.Parameters.AddWithValue("username", txtUserName.Text);
                Comm.Parameters.AddWithValue("firstname", txtFName.Text);
                Comm.Parameters.AddWithValue("lastname", txtLName.Text);
                Comm.Parameters.AddWithValue("email", txtEmail.Text);
                Comm.Parameters.AddWithValue("password", txtPassword.Text);
                Comm.ExecuteNonQuery();
            }
            else {
                Response.Write("Username already exists.");
            }
            Conn.Close();
            Conn = null;
        }

        public int AddEvent(String eventName, String eventRoom,
			String eventAttendees, String eventDate)
        {
            System.Data.OleDb.OleDbConnection oleDbConnection1;
            System.Data.OleDb.OleDbDataAdapter daEvents;
            DataSet ds;

            oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
            oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\\Inetpub\\wwwroot\\PCSWebApp3\\PCSWebApp3.mdb";
            String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," +
                " EventDate) VALUES ('" + eventName + "', '" +
                eventRoom + "', '" + eventAttendees + "', '" +
                eventDate + "')";
            System.Data.OleDb.OleDbCommand insertCommand =
                new System.Data.OleDb.OleDbCommand(oleDbCommand,
                oleDbConnection1);
            oleDbConnection1.Open();

            int queryResult = insertCommand.ExecuteNonQuery();
            if (queryResult == 1)
            {
                daEvents = new System.Data.OleDb.OleDbDataAdapter(
                    "SELECT * FROM Events", oleDbConnection1);
                ds = (DataSet)Application["ds"];
                ds.Tables["Events"].Clear();
                daEvents.Fill(ds, "Events");
                Application.Lock();
                Application["ds"] = ds;
                Application.UnLock();
                oleDbConnection1.Close();
            }
            return queryResult;
        }

        public static IDbCommand CreateCommand(EnumProviders provider)
        {
            IDbCommand cmd;

            switch (provider)
            {
                case EnumProviders.SqlServer:
                    cmd = new System.Data.SqlClient.SqlCommand();
                    break;
                case EnumProviders.OleDb:
                    cmd = new System.Data.OleDb.OleDbCommand();
                    break;
                case EnumProviders.Odbc:
                    cmd = new System.Data.Odbc.OdbcCommand();
                    break;
                case EnumProviders.Oracle:
                    throw new NotImplementedException("Provider not implemented");
                    break;
                default:
                    cmd = new System.Data.SqlClient.SqlCommand();
                    break;
            }

            return cmd;
        }

        public static void makeAll(System.String baseDirectory, System.String version)
        {
            //make base directory
            if (!(baseDirectory.EndsWith("\\") || baseDirectory.EndsWith("/")))
            {
                baseDirectory = baseDirectory + "/";
            }
            System.IO.FileInfo targetDir = SourceGenerator.makeDirectory(baseDirectory + PackageManager.GetVersionPackagePath(version) + "EventMapping");

            //get list of data types
            System.Data.OleDb.OleDbConnection conn = NormativeDatabase.Instance.Connection;
            System.String sql = "SELECT * from HL7EventMessageTypes inner join HL7Versions on HL7EventMessageTypes.version_id = HL7Versions.version_id where HL7Versions.hl7_version = '" + version + "'";
            System.Data.OleDb.OleDbCommand temp_OleDbCommand = new System.Data.OleDb.OleDbCommand();
            temp_OleDbCommand.Connection = conn;
            temp_OleDbCommand.CommandText = sql;
            System.Data.OleDb.OleDbDataReader rs = temp_OleDbCommand.ExecuteReader();

            using (StreamWriter sw = new StreamWriter(targetDir.FullName + @"\EventMap.properties", false))
            {
                sw.WriteLine("#event -> structure map for " + version);
                while (rs.Read())
                {
                    string messageType = string.Format("{0}_{1}", rs["message_typ_snd"], rs["event_code"]);
                    string structure = (string) rs["message_structure_snd"];

                    sw.WriteLine(string.Format("{0} {1}", messageType, structure));
                }
            }
        }

        public static bool AnalyzeExcel(ExcelXMLLayout layout)
        {
            System.Data.OleDb.OleDbConnection conn = null;
            try
            {
                conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path));

                conn.Open();
                System.Data.DataTable table = conn.GetOleDbSchemaTable(
                    System.Data.OleDb.OleDbSchemaGuid.Columns,
                    new object[] { null, null, layout.sheet + "$", null });

                layout.Clear();
                System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]");

                foreach (System.Data.DataRow row in table.Rows)
                {
                    string name = row["Column_Name"].ToString();

                    System.Diagnostics.Debug.WriteLine(name);

                    // 测试数据类型
                    ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown;
                    {
                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
                            string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn
                            );
                        System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
                        while (r.Read())
                        {
                            System.Diagnostics.Debug.WriteLine(r[0].GetType());
                            if (r[0].GetType() == typeof(System.Double))
                            {
                                testType = ExcelXMLLayout.KeyType.Integer;
                                break;
                            }
                            if (testType == ExcelXMLLayout.KeyType.String)
                            {
                                break;
                            }
                            testType = ExcelXMLLayout.KeyType.String;
                        }
                        r.Close();
                        cmd.Dispose();
                    }

                    layout.Add(name, testType);
                }
                table.Dispose();
                conn.Close();

                return true;
            }
            catch (Exception outErr)
            {
                lastError = string.Format("无法分析，Excel 无法打开\r\n{0}", outErr.Message);
            }
            return false;
        }