C# SqlConnection.ExecuteScalar方法代码示例

 public void expect_to_execute_sql()
 {
     var executeSqlFile = new ExecuteSqlFile {
         BuildEngine = _buildEngineMock,
         DbLogin = "BPMOnlineBuild",
         DbPassword = "Terrasoft+",
         DbServer = "tscore-dev-06",
         DbName = "TEST_DB",
         SqlFile = "test.sql"
     };
     var cs = new SqlConnectionStringBuilder {
         DataSource = "tscore-dev-06",
         UserID = "BPMOnlineBuild",
         Password = "Terrasoft+"
     };
     using (var connection = new SqlConnection(cs.ConnectionString)) {
         try {
             connection.Open();
             try {
                 connection.Execute("CREATE DATABASE TEST_DB");
             }
             catch (SqlException ex) {
                 if (ex.Number != 1801) {
                     throw;
                 }
             }
             executeSqlFile.Execute();
             var tableExist = connection.ExecuteScalar<int>("SELECT COUNT (*)  FROM sys.databases d WHERE d.name = @Name ", new { Name = "TestTable" }) == 1;
             Assert.IsTrue(tableExist);
         } finally  {
             connection.ExecuteScalar<int>(@"
                 ALTER DATABASE [TEST_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE [TEST_DB]");
         }
     }
 }

        public void Start() {
            _stopWatch.Start();

            // load input version
            using (var cn = new SqlConnection(_context.Process.Connections.First(c => c.Name == _context.Entity.Connection).GetConnectionString())) {
                cn.Open();
                _context.Entity.MaxVersion = _context.Entity.Version == string.Empty ? null : cn.ExecuteScalar(_context.SqlGetInputMaxVersion());
            }

            // load control batch
            using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) {
                cn.Open();
                _context.Entity.BatchId = GetBatchId(cn);
                cn.Execute(_context.SqlControlStartBatch(), new { _context.Entity.BatchId, Entity = _context.Entity.Alias });

                // load output version
                if (_context.Entity.Version == string.Empty)
                    return;

                var field = _context.Entity.GetVersionField();

                if (field == null)
                    return;

                _context.Entity.MinVersion = cn.ExecuteScalar(_context.SqlGetOutputMaxVersion(field));
            }

        }

 public void expect_to_backup_db()
 {
     var backup = new BackupDb {
         BuildEngine = _buildEngineMock,
         BackupFolder = @"\\V-ARTEMCHUK\tmp\",
         DbLogin = "BPMOnlineBuild",
         DbPassword = "Terrasoft+",
         DbServer = "tscore-dev-06",
         DatabaseName = "TEST_DB"
     };
     var cs = new SqlConnectionStringBuilder {
         DataSource = "tscore-dev-06",
         UserID = "BPMOnlineBuild",
         Password = "Terrasoft+"
     };
     using (var connection = new SqlConnection(cs.ConnectionString)) {
         try {
             connection.Open();
             connection.Execute("CREATE DATABASE TEST_DB");
             backup.Execute();
             Assert.IsTrue(File.Exists(backup.BackupPath.ToString()));
             Assert.IsTrue((new FileInfo(backup.BackupPath.ToString())).Length > 10);
         } finally  {
             connection.ExecuteScalar<int>(string.Format("DROP DATABASE [{0}]", backup.DatabaseName));
         }
     }
 }

        public int CreateIndividualConsignee(IndividualConsigneeBindingModel individualConsigneeModel)
        {
            int? createdConsigneeId = null;
            var individualConsignee = new IndividualConsignee();

            individualConsignee.FirstName = individualConsigneeModel.FirstName;
            individualConsignee.LastName = individualConsigneeModel.LastName;
            individualConsignee.TRN = individualConsigneeModel.TRN;
            individualConsignee.CustomerCode = individualConsigneeModel.CustomerCode;
            individualConsignee.Address = individualConsigneeModel.Address;

            using (var sqlConnection = new SqlConnection(_DBConnectionString))
            {
                try
                {
                    sqlConnection.Open();
                    createdConsigneeId = sqlConnection.ExecuteScalar<int>(
                       @"INSERT INTO IndividualConsignee (FirstName,LastName,TRN,Address,CustomerCode) 
                        OUTPUT Inserted.Id Values (@indivdualConsignee)", individualConsignee,
                      commandType: CommandType.Text, commandTimeout: 60);

                }
                catch (Exception ex)
                {
                    ex.ToString();
                    //log exceptions here

                }
            }
            return createdConsigneeId.Value;
        }

        public T_Property Save(SqlConnection db)
        {
            // Does not exist, inserting
            if (this.Property_Id == 0)
                this.Property_Id = db.ExecuteScalar<int>("INSERT INTO Property(Property_Term_Id, Property_Key, Property_Value) VALUES (@Property_Term_Id, @Property_Key, @Property_Value); SELECT CAST(SCOPE_IDENTITY() as int);", this);
            else
                // Exist, Updating
                db.Execute("UPDATE Property SET Property_Term_Id = @Property_Term_Id, Property_Key = @Property_Key, Property_Value = @Property_Value WHERE Property_Id = @Property_Id", this);

            // Method Chain
            return this;
        }

        public void ExecuteScalar()
        {
            const string sql = @"SELECT 1 As IntColumn WHERE @Fizz = 1";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                object result = conn.ExecuteScalar(sql, dict.ToSqlParameters());

                // UnitTest
                Assert.AreEqual(1, result);
            }
        }

        public int AddBrand(Brand brand)
        {
            var insertStmt = @"INSERT Products.Brands (BrandName, BrandCode, BrandDescription, BrandDisplayName, InCommBrandIdentifier, BrandImageUrl,
                IsActive, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy) VALUES (@BrandName, @BrandCode, @BrandDescription, @BrandDisplayName,
                @InCommBrandIdentifier, @BrandImageUrl, @IsActive, @CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy); SELECT scope_identity();";
            object id = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                
                id = conn.ExecuteScalar(insertStmt, brand);

                conn.Close();
            }
            return id != null ? Convert.ToInt32(id) : 0;
        }

        public ActionResult Breadcrumb(string id)
        {
            using (_db = new SqlConnection(_connString))
            {
                _db.Open();

                var trailData = _db.ExecuteScalar<string>("EXEC mw_Get_Breadcrumb_Trail @ID", new { ID = id });

                var links = trailData.Split('|')
                                     .Select(x => x.Split('^'))
                                     .Select(x => new Tuple<string, string>(x[0], x[1]))
                                     .ToList();

                return View(new BreadcrumbViewModel { Links = links });
            }
        }

 public static User Login(string userName, string password)
 {
     using (var context = new PrincipalContext(ContextType.Domain, "erccollections.com"))
     {
         var usr = UserPrincipal.FindByIdentity(context, userName);
         if (usr != null)
         {
             var verified = context.ValidateCredentials(userName, password);
             if (verified)
             {
                 using (var conn = new SqlConnection(ConnectionString))
                 {
                     conn.Open();
                     var user =
                         conn.Query<User>(
                             "select * from [collect2000].[ERCTasks].[Users] WHERE UserName = @userName",
                             new { userName = userName }).FirstOrDefault();
                     if (user == null)
                     {
                         var userId = conn.ExecuteScalar<int>(
                             "INSERT INTO [collect2000].[ERCTasks].[Users] (UserName, DisplayName, Email) VALUES (@userName, @dispName, @email); SELECT SCOPE_IDENTITY();",
                             new { userName = userName, dispName = usr.DisplayName, email = usr.EmailAddress });
                         user = new User()
                         {
                             DisplayName = usr.DisplayName,
                             UserName = userName,
                             UserRole = "user",
                             UserId = userId,
                             Email = usr.EmailAddress
                         };
                     }
                     return user;
                 }
             }
         }
     }
     return null;
 }

        public void Example1_Dapper()
        {
            IEnumerable<Dog> dog;
            var guid = Guid.NewGuid();
            using (var connection = new SqlConnection(s_ConnectionString))
            {
                dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });
            }

            Assert.AreEqual(1, dog.Count());
            Assert.IsNull(dog.First().Age);
            Assert.AreEqual(guid, dog.First().Id);


            //Make it more realistic by actually inserting a record
            var originalDog = new Dog() { Age = 2, Name = "Fido", Weight = 2.5f };

            Guid key;
            using (var connection = new SqlConnection(s_ConnectionString))
            {
                const string insertSql = "INSERT INTO Dog (Age, Name, Weight) OUTPUT Inserted.Id VALUES (@Age, @Name, @Weight);";
                key = connection.ExecuteScalar<Guid>(insertSql, originalDog);
            }

            //And then re-read it back
            Dog fetchedDog;
            using (var connection = new SqlConnection(s_ConnectionString))
            {
                const string selectSql = "SELECT Age, Name, Weight FROM Dog WHERE Id = @Id;";
                fetchedDog = connection.Query<Dog>(selectSql, new { Id = key }).Single();
            }

            Assert.AreEqual(originalDog.Age, fetchedDog.Age);
            Assert.AreEqual(originalDog.Name, fetchedDog.Name);
            Assert.AreEqual(originalDog.Weight, fetchedDog.Weight);
        }

        public int Insert(Employee employee)
        {
            const string sql = @"INSERT	INTO HR.Employee
		(FirstName,
		 MiddleName,
		 LastName,
		 Title,
		 ManagerKey,
		 OfficePhone,
		 CellPhone
		)
VALUES	(@FirstName,
		 @MiddleName,
		 @LastName,
		 @Title,
		 @ManagerKey,
		 @OfficePhone,
		 @CellPhone
		);

SELECT SCOPE_IDENTITY()
";
            using (var con = new SqlConnection(m_ConnectionString))
            {
                con.Open();
                return con.ExecuteScalar<int>(sql, employee);
            }
        }

        public void GetAllPlayersOnAllTeamsTest()
        {
            int count = 0;

            using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
            {
                count = (int)cn.ExecuteScalar("select count(*) from players");
            }

            Assert.AreEqual(count, _ops.GetAllPlayerOnAllTeams().Players.Count());
        }

        public void RemovePlayerByIDTest()
        {
            int playerID = 0;

            using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
            {
                playerID = (int)cn.ExecuteScalar("select count(*) from Players");
            }

            Assert.AreEqual(true, _ops.RemovePlayerByID(playerID).Success);
        }

        public Stream GetConfiguration(string nodeName)
        {
            // Initialize service if uninitialized.
            Initialize();

            // Check for the required parameters.
            if (string.IsNullOrEmpty(nodeName))
                throw new ArgumentNullException("nodeName");

            if (string.IsNullOrEmpty(m_connectionString))
                throw new ArgumentNullException("ConnectionString");

            SqlConnection database = null;
            DataSet configuration = new DataSet("Iaon"); ;
            MemoryStream output = new MemoryStream();
            try
            {
                // Open connection to the database.
                database = new SqlConnection(m_connectionString);
                database.Open();

                // Lookup ID of the specified node.
                object nodeID = database.ExecuteScalar(string.Format("SELECT ID FROM Node WHERE [Name] = '{0}'", nodeName));
                if (nodeID == null)
                    throw new ArgumentException("nodeName is not valid.");

                // Add configuration entities table to the output.
                DataTable entities, entity;
                entities = database.RetrieveData("SELECT * FROM ConfigurationEntity WHERE Enabled <> 0 ORDER BY LoadOrder");
                entities.TableName = "ConfigurationEntity";
                configuration.Tables.Add(entities.Copy());

                // Add each configuration entity to the output.
                foreach (DataRow row in entities.Rows)
                {
                    // Load configuration entity data filtered by node ID.
                    entity = database.RetrieveData(string.Format("SELECT * FROM {0} WHERE NodeID = '{1}'", row["SourceName"].ToString(), nodeID.ToString()));
                    entity.TableName = row["RuntimeName"].ToString();

                    // Remove redundant node ID column from the data.
                    entity.Columns.Remove("NodeID");

                    // Add entity configuration data to the output.
                    configuration.Tables.Add(entity.Copy());
                }

                // Extract prepared data in XML format.
                configuration.WriteXml(output);

                // Return formatted output to requestor.
                output.Position = 0;
                WebOperationContext.Current.OutgoingResponse.ContentType = "application/xml";
                WebOperationContext.Current.OutgoingResponse.ContentLength = output.Length;
                return output;
            }
            finally
            {
                // Release used memory.
                if (database != null)
                    database.Dispose();

                if (configuration != null)
                    configuration.Dispose();
            }
        }

 public static decimal? PingDb(string connectionString)
 {
     try {
         var csb = new SqlConnectionStringBuilder(connectionString);
         var db = new {DbName = csb.InitialCatalog};
         csb.InitialCatalog = null;
         using (var connection = new SqlConnection(connectionString)) {
             var query = "select 1";
             var command = new SqlCommand(query, connection);
             connection.Open();
             command.ExecuteScalar();
         }
         using (var c2 = new SqlConnection(csb.ConnectionString)) {
             return c2.ExecuteScalar<decimal>(@"SELECT CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID(@DbName)", db);
         }
     }
     catch {
         return null;
     }
 }