C# SqlCommand.ExecuteScalar方法代码示例

        private void submitButtonH_Click(object sender, EventArgs e)
        {
            //If this is valid then close the panel and show the info
               //Check if this barcode exists in the database
               //This code is the same in ever single station other than what data it should pull
               SqlConnection connection = new SqlConnection(Properties.Settings.Default.TigerCheckProductionConnectionString);
               SqlCommand ifExists = new SqlCommand("IF Exists(Select 1 From TigerCheckProduction.dbo.PatientRecords WHERE [ID_Num] = @barcodeNum) Select 1 Else Select 0", connection);
               ifExists.Parameters.AddWithValue("@barcodeNum", barcodeTextBox.Text);
               connection.Open();
               if (Convert.ToInt32(ifExists.ExecuteScalar()) == 1)
               {

                    barcodePanel.Visible = false;
                    //Since it exists, make a new patient object and load the data

                    patient.getPatientData(Convert.ToInt32(barcodeTextBox.Text));

                    Hearing.Text = patient.Hearing.ToString();
               }
               else
               {
                    MessageBox.Show("Invalid barcode number");
               }
               connection.Close();
        }

 //use for getting a single integer data
 public static int GetID(string SelectStatement)
 {
     SqlConnection con = new SqlConnection(ConnectionString);
     con.Open();
     SqlCommand com = new SqlCommand(SelectStatement, con);
     int id = Convert.ToInt32(com.ExecuteScalar());
     con.Close();
     return id;
 }

 public static object ExecuteScalar(string sql, params SqlParameter[] ps)
 {
     using (SqlConnection connection = new SqlConnection(connStr))
     {
         SqlCommand command = new SqlCommand(sql, connection);
         command.Parameters.AddRange(ps);
         connection.Open();
         return command.ExecuteScalar();
     }
 }

        public static void MARSSyncTimeoutTest()
        {
            using (SqlConnection connection = new SqlConnection(s_yukonConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("WAITFOR DELAY '01:00:00';SELECT 1", connection);
                command.CommandTimeout = 1;
                bool hitException = false;
                try
                {
                    object result = command.ExecuteScalar();
                }
                catch (Exception e)
                {
                    Assert.True(e is SqlException, "Expected SqlException but found " + e);
                    hitException = true;
                }
                Assert.True(hitException, "Expected a timeout exception but ExecutScalar succeeded");

                Assert.True(connection.State == ConnectionState.Open, string.Format("Expected connection to be open after soft timeout, but it was {0}", connection.State));

                Type type = typeof(SqlDataReader).GetTypeInfo().Assembly.GetType("System.Data.SqlClient.TdsParserStateObject");
                FieldInfo field = type.GetField("_skipSendAttention", BindingFlags.NonPublic | BindingFlags.Static);

                Assert.True(field != null, "Error: This test cannot succeed on retail builds because it uses the _skipSendAttention test hook");

                field.SetValue(null, true);
                hitException = false;
                try
                {
                    SqlCommand command2 = new SqlCommand("WAITFOR DELAY '01:00:00';SELECT 1", connection);
                    command2.CommandTimeout = 1;
                    try
                    {
                        object result = command2.ExecuteScalar();
                    }
                    catch (Exception e)
                    {
                        Assert.True(e is SqlException, "Expected SqlException but found " + e);
                        hitException = true;
                    }
                    Assert.True(hitException, "Expected a timeout exception but ExecutScalar succeeded");

                    Assert.True(connection.State == ConnectionState.Closed, string.Format("Expected connection to be closed after hard timeout, but it was {0}", connection.State));
                }
                finally
                {
                    field.SetValue(null, false);
                }
            }
        }

        /// <summary>
        /// Gets the internal connection associated with the given SqlConnection
        /// </summary>
        /// <param name="connection">Live outer connection to grab the inner connection from</param>
        /// <param name="supportKillByTSql">If true then we will query the server for this connection's SPID details (to be used in the KillConnectionByTSql method)</param>
        public InternalConnectionWrapper(SqlConnection connection, bool supportKillByTSql = false)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            _internalConnection = connection.GetInternalConnection();
            ConnectionString = connection.ConnectionString;

            if (supportKillByTSql)
            {
                // Save the SPID for later use
                using (SqlCommand command = new SqlCommand("SELECT @@SPID", connection))
                {
                    _spid = command.ExecuteScalar();
                }
            }
        }

            private Int32 CountSnapshots(Guid streamId)
            {
                using (var connection = SqlServerConnection.Create())
                using (var command = new SqlCommand("SELECT COUNT(*) FROM [Snapshot] WHERE [StreamId] = @StreamId;", connection))
                {
                    command.Parameters.AddWithValue("@StreamId", streamId);
                    connection.Open();

                    return (Int32)command.ExecuteScalar();
                }
            }

 private static bool IsValidParam(SqlDbType dbType, string col, object value, SqlConnection conn, string tempTable)
 {
     try
     {
         SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM " + tempTable + " WHERE " + col + " = @p", conn);
         cmd.Parameters.Add("@p", dbType).Value = value;
         cmd.ExecuteScalar();
     }
     catch (InvalidCastException)
     {
         return false;
     }
     return true;
 }

        private bool CreatePretaskBySMS(Message message)
        {
            string returnValueParameterName = "@ReturnValueParameter";
            try
                {
                using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
                    {
                    conn.Open();

                    using ( SqlCommand cmd = new SqlCommand("CreatePreTaskBySMS", conn) )
                        {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Text", message.MessageBody);
                        cmd.Parameters.AddWithValue("@PhoneNumber", message.Number);

                        SqlParameter param = new SqlParameter();
                        param.ParameterName = returnValueParameterName;
                        param.Direction = System.Data.ParameterDirection.ReturnValue;
                        cmd.Parameters.Add(param);

                        cmd.ExecuteScalar();
                        object returnValue = cmd.Parameters[returnValueParameterName].Value;
                        return true;
                        }
                    }
                }
            catch ( Exception exp )
                {
                NotifyOnError(exp);
                }
            return false;
        }

 public void Test_WithGuidValue_ShouldReturnGuid()
 {
     using (var conn = new SqlConnection(_connString))
     {
         conn.Open();
         var expectedGuid = Guid.NewGuid();
         var cmd = new SqlCommand("select @input", conn);
         cmd.Parameters.AddWithValue("@input", expectedGuid);
         var result = cmd.ExecuteScalar();
         Assert.Equal(expectedGuid, (Guid)result);
     }
 }

        private static void VerifyConnection(SqlCommand cmd)
        {
            Assert.True(cmd.Connection.State == ConnectionState.Open, "FAILURE: - unexpected non-open state after Execute!");

            cmd.CommandText = "select 'ABC'"; // Verify Connection
            string value = (string)cmd.ExecuteScalar();
            Assert.True(value == "ABC", "FAILURE: upon validation execute on connection: '" + value + "'");
        }

            public static object ExecuteSQL(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
            {
                SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));

                objCmd.CommandType = CommandType.Text;

                ProcessParams(objCmd, @Params); //Parse Parameters

                return objCmd.ExecuteScalar();
                //				objCmd.Dispose();
                //				objCmd = null;
            }

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

                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@userID", SqlDbType.NVarChar, 255).Value = userID;
                        sqlUpdateCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            return membershipUser;

        }

        /// <summary>
        /// Unlock a user.
        /// </summary>
        /// <param name="username">User name.</param>
        /// <returns>T/F if unlocked.</returns>
        public override bool UnlockUser(
         string username
        )
        {

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Unlock", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            int rowsAffected = 0;

            try
            {
                sqlConnection.Open();

                sqlCommand.ExecuteNonQuery();
                if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
                return false;
            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }*/
        public override string GetUserNameByEmail(string email)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("UserName_Sel_ByEmail", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = email;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            string username = String.Empty;

            try
            {
                sqlConnection.Open();
                username = Convert.ToString(sqlCommand.ExecuteScalar());
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                sqlConnection.Close();
            }

            if (username == null)
            {
                return String.Empty;
            }
            else
            {
                username.Trim();
            }

            return username;
        }

            public static object ExecuteSP(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
            {
                SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
                objCmd.CommandType = CommandType.StoredProcedure;

                objCmd.Connection.Open();
                ProcessParams(objCmd, @Params); //Parse Parameters

                using (objCmd)
                {
                    try
                    {
                        return objCmd.ExecuteScalar();
                    }
                    catch (Exception)
                    {
                        throw;
                    }

                }
            }

            public float GetFloatValue()
            {
                string cnnString;
                object value;
                float floatValue;

                cnnString = WebConfig.GetCnnString("CP_cnnString");

                using (SqlConnection cnn = new SqlConnection(cnnString)) {
                    using (SqlCommand cmd = new SqlCommand()) {
                        cmd.Connection = cnn;
                        cmd.CommandText = _name;
                        cmd.CommandType = CommandType.StoredProcedure;

                        foreach (SqlParameter parameter in _parameters) {
                            cmd.Parameters.Add(parameter);
                        }

                        cnn.Open();
                        value = cmd.ExecuteScalar();
                        cnn.Close();
                    }
                }

                floatValue = float.Parse(value.ToString(), CultureInfo.InvariantCulture);

                return floatValue;
            }

            public string GetStringValue()
            {
                string cnnString;
                object value;
                string stringValue;

                cnnString = WebConfig.GetCnnString("CP_cnnString");

                using (SqlConnection cnn = new SqlConnection(cnnString)) {
                    using (SqlCommand cmd = new SqlCommand()) {
                        cmd.Connection = cnn;
                        cmd.CommandText = _name;
                        cmd.CommandType = CommandType.StoredProcedure;

                        foreach (SqlParameter parameter in _parameters) {
                            cmd.Parameters.Add(parameter);
                        }

                        cnn.Open();
                        value = cmd.ExecuteScalar();
                        cnn.Close();
                    }
                }

                stringValue = value.ToString();

                return stringValue;
            }