C# System.Data.OleDb.OleDbCommand.ExecuteScalar方法代码示例

        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 isHomologyNote(string Table, string term1, string term2, string Value1, string Value2)
 {
     string tem_sql = "";//定义字符串变量
     System.Data.OleDb.OleDbConnection tem_conn =
         new System.Data.OleDb.OleDbConnection(connstr);//连接数据库
     System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
     tem_conn.Open();//打开数据库的连接
     //设置SQL语句，查找要添加的记录
     tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" + 
         Value1 + "' and " + term2 + " = '" + Value2 + "'";
     tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//执行SQL语句
     int RecordCount = 0;//定义变量
     if (tem_comm.ExecuteScalar() == null)//如果查询为空
         RecordCount = 0;
     else
         RecordCount = (int)tem_comm.ExecuteScalar();//返回查找结果的个数
     tem_conn.Close();//关闭连接
     tem_comm.Dispose();//释放资源
     tem_conn.Dispose();//释放资源
     return RecordCount;//返回查询记录数量
 }

        /// <summary>
        /// イベントで呼ばれる実際の処理：@@IDENTITYでオートナンバー型の列を取得する
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Adapter_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
        {
            if (e.StatementType == System.Data.StatementType.Insert
                && e.Status == System.Data.UpdateStatus.Continue)
            {
                var cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", null);
                cmd.Connection = e.Command.Connection;
                cmd.Transaction = e.Command.Transaction;

                var result = cmd.ExecuteScalar();

                if (result != null
                    && result.GetType() != typeof(System.DBNull))
                {
                    e.Row["ID"] = (int)result;
                    e.Row.AcceptChanges();
                }
            }
        }

        public override void RestoreParameters()
        {
            //testsetup was left for compatibility only; is not used otherwise
            System.Data.OleDb.OleDbDataReader rsParameters = default(System.Data.OleDb.OleDbDataReader);

            try
            {
                if (DBPtr == null)
                {
                    return ;
                }
                DBPtr.Open();
                System.Data.OleDb.OleDbCommand sqlCOM = new System.Data.OleDb.OleDbCommand("SELECT * FROM QuasiAddIn2_NetSample2 WHERE Setup=" + System.Convert.ToString(Setup)) {Connection = DBPtr};

                sqlCOM.ExecuteScalar();
                rsParameters = sqlCOM.ExecuteReader();
                if (!rsParameters.Read())
                {
                    //no records
                    rsParameters.Close();
                    System.Data.OleDb.OleDbCommand sqlWRCom = new System.Data.OleDb.OleDbCommand("INSERT INTO QuasiAddIn2_NetSample2 (Setup) VALUES (" + System.Convert.ToString(Setup) + ")", DBPtr);
                    sqlWRCom.ExecuteNonQuery();
                    sqlCOM.ExecuteNonQuery();
                    rsParameters = sqlCOM.ExecuteReader();
                    rsParameters.Read();
                }

                if (!(rsParameters == null))
                {
                    mvarAverages = System.Convert.ToInt16(rsParameters["Averages"]);
                }
                base.RaiseRestoreParams();

            }
            catch (Exception ex)
            {
                MessageBox.Show("RestoreParameters " + ex.Message);
            }
            finally
            {
                if (DBPtr.State == ConnectionState.Open)
                {
                    DBPtr.Close();
                }
            }
        }

        private void PrepareDatabase()
        {
            string sConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + TempBurnPath + @"\Data\RODB.DAT;Mode=Read|Write|Share Deny None;";
            System.Data.OleDb.OleDbConnection AC = new System.Data.OleDb.OleDbConnection(sConnection);
            try
            {
                AC.Open();
            }
            catch(System.Data.OleDb.OleDbException Err)
            {
                MessageBox.Show(Err.Message);
                string peek = Err.Message;
                return;
            }
            catch(Exception Err)
            {
                MessageBox.Show(Err.Message);
                string sPeek = Err.Message;
                return;
            }
            #region TheWork
            System.Data.OleDb.OleDbCommand CM = new System.Data.OleDb.OleDbCommand();
            CM.Connection = AC;
            System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter();
            System.Data.DataTable DT = new System.Data.DataTable();
            foreach(OCL.RecordingSession RS in SelectedRecordingSessions)
            {

                string sSQL = @"INSERT INTO tblSession(Description,IsPresentation,OwnerId,CreatedDate,IsOysterSession) VALUES('" +
                    RS.Description + "'" +
                    "," +Convert.ToInt32(RS.IsPresentation) +
                    "," + 23 +
                    ",'" + RS.CreatedDate +"'" +
                    "," + Convert.ToInt32(true) + ")";
                int SessionID = 0;
                try
                {

                    CM.CommandText = sSQL;
                    CM.ExecuteNonQuery();
                    CM.CommandText = "SELECT @@IDENTITY";
                    SessionID = (int)CM.ExecuteScalar();

                }
                catch(System.Data.OleDb.OleDbException Err)
                {
                    MessageBox.Show(Err.Message);
                    string peek = Err.Message;
                    return;
                }
                catch(Exception Err)
                {
                    MessageBox.Show(Err.Message);
                    string sPeek = Err.Message;
                    return;
                }
                foreach(OCL.Recording R in RS.CurrentRecordings(frmParent.LUser))
                {
                    string FileName = "";
                    string FileExt = "";

                    string[] sFileName = R.Description.Split(".".ToCharArray());
                    if(sFileName.Length == 1)
                    {
                        FileExt = "";
                    }
                    else if(sFileName.Length == 2)
                    {
                        FileExt = "." + sFileName[1];
                    }
                    FileName = RS.Description + "_" + R.DisplayName;

                    string NewDescription = RenameFile(FileName,FileExt);
                    sSQL = @"INSERT INTO tblRecording(Name,Created,VideoStorageServerId," +
                        "DisplayName, StreamingEncoderId,SessionId," +
                        "IsDesktopCapture,IsPrimaryCamera,DiskSize,Playlength," +
                        "HasAudio,HasVideo) " +
                        "VALUES ('" + NewDescription +
                        "', '" + R.CreatedDate +
                        "', 9" +
                        ", '" + R.DisplayName +
                        "', 15" +
                        "," + SessionID +
                        "," + Convert.ToInt32(R.IsDesktopCapture) +
                        "," + Convert.ToInt32(R.IsPrimaryCamera) +
                        "," + R.SizeOnDisk +
                        "," + R.PlayLength +
                        "," + Convert.ToInt32(R.HasAudio) +
                        "," + Convert.ToInt32(R.HasVideo) +
             						")";

                    try
                    {
                        CM.CommandText = sSQL;
                        CM.ExecuteNonQuery();
                    }
                    catch(System.Data.OleDb.OleDbException Err)
                    {
                        MessageBox.Show(Err.Message);
                        string peek = Err.Message;
//.........这里部分代码省略.........

        public void CheckBatch(ref string StrSQl_Count, ref string StrSQl_1,
                               ref string StrSQl_2, ref string StrSQl_3,
                                   string FieldsPagina, string FieldsSequence,
                                   string FoxFile, string FieldsCurstage,
                                   string SetCurstage, string FieldsBatchnum,
                               ref ComboBox cmbBatch, string FieldsExprunid,
                                   string FieldsProvNum, string FieldsDateFrom,
                                   string FieldsInsured, string FieldsFilePath, string FieldsIfName, string FieldsCpt, string FieldsPin)
        {
            System.Data.OleDb.OleDbConnection _lcFoxCnn = null;
            System.Data.OleDb.OleDbCommand _lcFoxCmd = null;
            System.Data.OleDb.OleDbDataReader _lcFoxDataReader = null;
            int _lcIntTotalRecFox = 0;
            int _lcIntTotalRecPendienteFox = 0;
            int _lcIntTotalRecErrorPagina = 0;
            int _lcIntTotalRecErrorProv = 0; // campo 25
            int _lcIntTotalRecErrorInsurade = 0;
            int _lcIntTotalRecErrorCpt = 0;
            int _lcIntTotalRecErrorPin = 0; // campo 33
            myData[] _lcDataErrorPagina = null;
            myData[] _lcDataErrorProvNum = null;
            myData[] _lcDataErrorInsured = null;
            myData[] _lcDataErrorCpt = null;
            myData[] _lcDataErrorPin = null;

            bool _lcIsPaginaError = false;
            bool _lcIsProvError = false;
            bool _lcIsSuscError = false;
            bool _lcIsCptError = false;
            bool _lcIsPinError = false;
            /////////
            try
            {
                _lcFoxCnn = new System.Data.OleDb.OleDbConnection();
                _lcFoxCmd = new System.Data.OleDb.OleDbCommand();
                _lcFoxCnn.ConnectionString = this._lgbFoxConnectionString;

                if (_lcFoxCnn.State == System.Data.ConnectionState.Closed)
                {
                    _lcFoxCnn.Open();
                }
                ////////////////////
                StrSQl_Count = "Select count(*) as Total_record from " + FoxFile; // Para el total de imagenes.
                StrSQl_1 = "Select count(*) as Total from " + FoxFile + " where not " + FieldsCurstage + "='" + SetCurstage + "' and " + FieldsBatchnum + "='" + cmbBatch.SelectedItem.ToString() + "'"; //Cuental los Batch Que Hay Diferente de V0 --
                StrSQl_2 = "Select * from " + FoxFile + " where " + FieldsBatchnum + "='" + cmbBatch.SelectedItem.ToString() + "' and " + FieldsPagina + "='99' or " + FieldsPagina + "=' ' order by " + FieldsSequence;
                StrSQl_3 = "Select * from " + FoxFile + " where " + FieldsBatchnum + "='" + cmbBatch.SelectedItem.ToString() + "' and not " + FieldsExprunid + "='VR' and not " + FieldsPagina + "='99' order by " + FieldsSequence;
                //////////////////Set _lcFoxCmd //////////////////////////
                _lcFoxCmd.Connection = _lcFoxCnn;
                _lcFoxCmd.CommandType = System.Data.CommandType.Text;
                //////////////////Get Total de Imagenes //////////////////
                _lcFoxCmd.CommandText = StrSQl_Count;
                _lcIntTotalRecFox = Convert.ToInt32(_lcFoxCmd.ExecuteScalar());
                if (_lcIntTotalRecFox <= 0)
                {
                    throw new Exception("No Hay datos para el Quality");
                }
                //////////////////Get Total de Record Pendiente//////////
                _lcFoxCmd.CommandText = StrSQl_1;
                _lcIntTotalRecPendienteFox = Convert.ToInt32(_lcFoxCmd.ExecuteScalar());
                if (_lcIntTotalRecPendienteFox > 0)
                {
                    throw new Exception("Hay " + _lcIntTotalRecPendienteFox + " Record Pendientes...");
                }
                ///////////////////////Cuando hay un error con el campo de la pagina /////
                if (this._gblIsPagina)
                {
                    _lcFoxCmd.CommandText = StrSQl_2;
                    _lcFoxDataReader = _lcFoxCmd.ExecuteReader();

                    while (_lcFoxDataReader.Read())
                    {
                        if ((_lcFoxDataReader[FieldsProvNum].ToString().Trim().Length > 0) || (_lcFoxDataReader[FieldsDateFrom].ToString().Trim().Length > 0)
                            || (_lcFoxDataReader[FieldsPagina].ToString().Trim().Length == 0) || (_lcFoxDataReader[FieldsPagina].ToString().Trim() == "99"))
                        {
                            _lcIntTotalRecErrorPagina++;
                            _lcIsPaginaError = true;
                        }
                    }
                    if (_lcIsPaginaError)
                    {
                        _lcFoxDataReader.Close();
                        _lcFoxDataReader = _lcFoxCmd.ExecuteReader();
                        int intIndex = 0;
                        _lcDataErrorPagina = new myData[_lcIntTotalRecErrorPagina];

                        while (_lcFoxDataReader.Read())
                        {
                            if ((_lcFoxDataReader[FieldsProvNum].ToString().Trim().Length > 0) || (_lcFoxDataReader[FieldsDateFrom].ToString().Trim().Length > 0)
                                || (_lcFoxDataReader[FieldsPagina].ToString().Trim().Length == 0) || (_lcFoxDataReader[FieldsPagina].ToString().Trim() == "99"))
                            {
                                _lcDataErrorPagina[intIndex] = new myData();
                                _lcDataErrorPagina[intIndex].Provnum = _lcFoxDataReader[FieldsProvNum].ToString().Trim();
                                _lcDataErrorPagina[intIndex].Insured = _lcFoxDataReader[FieldsInsured].ToString().Trim();
                                _lcDataErrorPagina[intIndex].FilePath = _lcFoxDataReader[FieldsFilePath].ToString().Trim();
                                _lcDataErrorPagina[intIndex].IfName = _lcFoxDataReader[FieldsIfName].ToString().Trim();
                                _lcDataErrorPagina[intIndex].Pagina = _lcFoxDataReader[FieldsPagina].ToString().Trim();
                                _lcDataErrorPagina[intIndex].BatchNum = _lcFoxDataReader[FieldsBatchnum].ToString().Trim();
                                _lcDataErrorPagina[intIndex].FieldsName = FieldsPagina;
                                _lcDataErrorPagina[intIndex].Label = _gblLabelPagina;

//.........这里部分代码省略.........

        private void buttonCommand_Click(object sender, EventArgs e)
        {

            //1. Make a Connection
            System.Data.OleDb.OleDbConnection objOleCon;
            objOleCon = new System.Data.OleDb.OleDbConnection();
            objOleCon.ConnectionString = strConnection;
            objOleCon.Open();
            objOleCon.ChangeDatabase("Northwind"); //We will use the Northwind database for this demo...

            //2. Issue a Command
            System.Data.OleDb.OleDbCommand objCmd;
            objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objOleCon;
            objCmd.CommandType = CommandType.Text;//Not normally typed out since Text is the default anyway

            //3. Process the Results
            //When you don't need results back, like these two examples, use ExecuteNonQuery()
            objCmd.CommandText = "Insert Into MyTable Values(1, 'SomeData')";
            objCmd.CommandText = "Raiserror('Error Level 11 to 25 are Caught by Try-Catch', 15, 1)";
            try
            {
                objCmd.ExecuteNonQuery(); // Ex. Inserts, Updates, Deletes SQL commands
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            //When you want a single value back use ExecuteScalar()
            objCmd.CommandText = "Select Count(*) From Products";//Your SQL Code
            int intResult = (int)objCmd.ExecuteScalar(); //Ex. Capture returning Integer data
            // string strResult = (string)objCmd.ExecuteScalar(); //Ex. Capture returning Varchar(50) data
            MessageBox.Show("Number of Products: " + intResult.ToString());

            //When you want multiple results back
            objCmd.CommandText = "Select ProductName From Products"; //Your SQL Code
            System.Data.OleDb.OleDbDataReader objDR; //Class used to process multiple results
            objDR = objCmd.ExecuteReader();

            MessageBox.Show(string.Format("FieldCount: {0}, RecordsAffected: {1} ", objDR.FieldCount, objDR.RecordsAffected));
            objDR.Close();

            //You can also submit multiple queries and get multiple result SETS back
            objCmd.CommandText = @"Select ProductName From Products  ; 
                                                 Select OrderId, ProductId From [Order Details]"; //Your SQL Code
            objDR = objCmd.ExecuteReader();

            MessageBox.Show(string.Format("FieldCount: {0}", objDR.FieldCount));
            objDR.Read();
            objDR.NextResult();
            objDR.Read();
            MessageBox.Show(string.Format("FieldCount: {0}", objDR.FieldCount));
            objDR.Close();

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

        private void buttonParameters_Click(object sender, EventArgs e)
        {

            /****************************************************
             * NOTE: You must create a stored procedure using the code 
             * found in the PrameterDemoCode.sql file before you can run this!
             ****************************************************/

            //1. Make a Connection
            System.Data.OleDb.OleDbConnection objOleCon;
            objOleCon = new System.Data.OleDb.OleDbConnection();
            objOleCon.ConnectionString = strConnection;
            objOleCon.Open();
            objOleCon.ChangeDatabase("TempDB"); //Since the previous demos used Northwind we need this command.

            //2. Issue a Command
            System.Data.OleDb.OleDbCommand objCmd;
            objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objOleCon;
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "DivideDemo";

            //2.1 Create and Add Parameters before you execute the Sproc.

            #region Input Parameters
            System.Data.OleDb.OleDbParameter objP1 = new System.Data.OleDb.OleDbParameter();
            objP1.Direction = ParameterDirection.Input;
            objP1.DbType = DbType.Double;
            objP1.ParameterName = "@p1";
            objP1.Value = Convert.ToDouble(textBox1.Text);

            System.Data.OleDb.OleDbParameter objP2 = new System.Data.OleDb.OleDbParameter();
            objP2.Direction = ParameterDirection.Input;
            objP2.DbType = DbType.Double;
            objP2.ParameterName = "@p2";
            objP2.Value = Convert.ToDouble(textBox2.Text); ;
            #endregion

            #region Output Parameter
            System.Data.OleDb.OleDbParameter objAnswer = new System.Data.OleDb.OleDbParameter();
            objAnswer.Direction = ParameterDirection.Output;
            objAnswer.DbType = DbType.Double;
            objAnswer.ParameterName = "@answer";
            #endregion

            #region Return Code
            System.Data.OleDb.OleDbParameter objRC = new System.Data.OleDb.OleDbParameter();
            objRC.Direction = ParameterDirection.ReturnValue;
            objRC.DbType = DbType.Int32;
            objRC.ParameterName = "@RC";
            #endregion

            //Now, add all to the Parameter Collection
            /*
             * NOTE: The order of the parameters used to call the Stored Procedure is Positional! 
             * So, if the Store Procedure's parameter list looks like this... 
             * 
             *       Create Proc DivideDemo( @p1 float, @p2 float, @answer float out ) 
             *       
             * And, you execute the Store Procedure in SQL with the following code...
             * 
             *        Exec @RC = DivideDemo 4, 3, @Value out;
             *   
             *Then the Return Code parameter *MUST* be added first, followed by @p1 parameter, 
             * @p2, and @answer, to the Parameters Collection like this....
             *
             */

            objCmd.Parameters.Add(objRC); //1st
            objCmd.Parameters.Add(objP2); //2nd
            objCmd.Parameters.Add(objP1);  //3rd
            objCmd.Parameters.Add(objAnswer);//4th

            /*NOTE: Start a SQL Profiler Trace and show students how the command is submitted */

            //3. Process the Results            
            try
            {
                object objSelectData = objCmd.ExecuteScalar(); //Use this when there is a Select that selects a single value in the sproc.
                MessageBox.Show("Select data: " + Convert.ToString(objSelectData));
                MessageBox.Show("Output data: " + objCmd.Parameters["@answer"].Value.ToString());
                MessageBox.Show("Return Value data: " + objCmd.Parameters["@RC"].Value.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

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

        }

//.........这里部分代码省略.........
         this.Adapter.InsertCommand.Parameters[1].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[1].Value = ((string)(firstname));
     }
     if ((street == null))
     {
         this.Adapter.InsertCommand.Parameters[2].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[2].Value = ((string)(street));
     }
     if ((zipcode == null))
     {
         this.Adapter.InsertCommand.Parameters[3].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[3].Value = ((string)(zipcode));
     }
     if ((city == null))
     {
         this.Adapter.InsertCommand.Parameters[4].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[4].Value = ((string)(city));
     }
     if ((homephone == null))
     {
         this.Adapter.InsertCommand.Parameters[5].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[5].Value = ((string)(homephone));
     }
     if ((workphone == null))
     {
         this.Adapter.InsertCommand.Parameters[6].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[6].Value = ((string)(workphone));
     }
     if ((mobilephone == null))
     {
         this.Adapter.InsertCommand.Parameters[7].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[7].Value = ((string)(mobilephone));
     }
     if ((personnumber == null))
     {
         this.Adapter.InsertCommand.Parameters[8].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[8].Value = ((string)(personnumber));
     }
     if ((info == null))
     {
         this.Adapter.InsertCommand.Parameters[9].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[9].Value = ((string)(info));
     }
     if ((freecarddate == null))
     {
         this.Adapter.InsertCommand.Parameters[10].Value = System.DBNull.Value;
     }
     else
     {
         this.Adapter.InsertCommand.Parameters[10].Value = ((string)(freecarddate));
     }
     System.Data.ConnectionState previousConnectionState = this.Adapter.InsertCommand.Connection.State;
     if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
                 != System.Data.ConnectionState.Open))
     {
         this.Adapter.InsertCommand.Connection.Open();
     }
     try
     {
         int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
         System.Data.OleDb.OleDbConnection conn = this.Adapter.InsertCommand.Connection;
         System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY FROM Patients", conn );
         returnValue = Convert.ToInt32(cmd.ExecuteScalar());
         return returnValue;
     }
     finally
     {
         if ((previousConnectionState == System.Data.ConnectionState.Closed))
         {
             this.Adapter.InsertCommand.Connection.Close();
         }
     }
 }