C# NpgsqlCommand.ExecuteScalar方法代码示例

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public string GetUserId()
		{
			NpgsqlMembershipProvider _provider = null;
			ProviderCollection _providers = null;

			// Get a reference to the <imageService> section
			MembershipSection section = (MembershipSection) WebConfigurationManager.GetSection("system.web/membership");

			// Load registered providers and point _provider
			// to the default provider
			_providers = new ProviderCollection();
			ProvidersHelper.InstantiateProviders(section.Providers, _providers, typeof (NpgsqlMembershipProvider));
			_provider = (NpgsqlMembershipProvider) _providers[section.DefaultProvider];

			HttpContext currentContext = HttpContext.Current;

			NpgsqlConnection conn = new NpgsqlConnection(_provider.connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"SELECT UserId FROM " + tableName + " WHERE user_name = @user_name AND application_name = @application_name", conn);

			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = currentContext.User.Identity.Name;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = _provider.ApplicationName;

			string UserId = "";
			try
			{
				conn.Open();
				UserId = cmd.ExecuteScalar().ToString();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetUserId()");

                    // use fully qualified name so as not to conflict with System.Data.ProviderException
                    // in System.Data.Entity assembly
					throw new System.Configuration.Provider.ProviderException(exceptionMessage);
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}

			return UserId;
		}

		//
		// MembershipProvider.GetAllUsers
		//

		public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
		{
			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(string.Format("SELECT Count(*) FROM {0} WHERE application_name = @application_name", tableName), conn);
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
			MembershipUserCollection users = new MembershipUserCollection();

			NpgsqlDataReader reader = null;
			totalRecords = 0;
			try
			{
				conn.Open();
				totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
				if (totalRecords <= 0)
				{
					return users;
				}

				cmd.CommandText = string.Format("SELECT UserId, user_name, Email, password_question, Comment, is_approved, is_locked_out, creation_date, last_login_date, last_activity_date, last_password_changed_date, last_locked_out_date  FROM {0}  WHERE application_name = @application_name  ORDER BY user_name Asc", tableName);

				using (reader = cmd.ExecuteReader())
				{
					int counter = 0;
					int startIndex = pageSize*pageIndex;
					int endIndex = startIndex + pageSize - 1;

					while (reader.Read())
					{
						if (counter >= startIndex)
						{
							MembershipUser u = GetUserFromReader(reader);
							users.Add(u);
						}

						if (counter >= endIndex)
						{
							cmd.Cancel();
						}

						counter++;
					}
					reader.Close();
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetAllUsers");

                    // use fully qualified name so as not to conflict with System.Data.ProviderException
                    // in System.Data.Entity assembly
					throw new System.Configuration.Provider.ProviderException(exceptionMessage);
				}
				else
				{
					throw;// e;
				}
			}
			finally
			{
				if (reader != null)
				{
					reader.Close();
				}
				cmd.Dispose();
				conn.Close();
			}

			return users;
		}

		//
		// MembershipProvider.GetNumberOfUsersOnline
		//

		public override int GetNumberOfUsersOnline()
		{
			TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
			DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					string.Format("SELECT Count(*) FROM {0} WHERE last_activity_date > @CompareDate AND application_name = @application_name", tableName), conn);

			cmd.Parameters.Add("@CompareDate", NpgsqlDbType.Timestamp).Value = compareTime;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;

			int numOnline = 0;

			try
			{
				conn.Open();

				numOnline = Convert.ToInt32(cmd.ExecuteScalar());
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetNumberOfUsersOnline");

                    // use fully qualified name so as not to conflict with System.Data.ProviderException
                    // in System.Data.Entity assembly
					throw new System.Configuration.Provider.ProviderException(exceptionMessage);
				}
				else
				{
					throw;// e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}

			return numOnline;
		}

 public void SetUp()
 {
     using (var conn = OpenConnection())
     using (var cmd = new NpgsqlCommand("SELECT postgis_version()", conn))
     {
         try
         {
             cmd.ExecuteNonQuery();
         }
         catch (PostgresException)
         {
             cmd.CommandText = "SELECT version()";
             var versionString = (string)cmd.ExecuteScalar();
             Debug.Assert(versionString != null);
             var m = Regex.Match(versionString, @"^PostgreSQL ([0-9.]+(\w*)?)");
             if (!m.Success)
                 throw new Exception("Couldn't parse PostgreSQL version string: " + versionString);
             var version = m.Groups[1].Value;
             var prerelease = m.Groups[2].Value;
             if (!string.IsNullOrWhiteSpace(prerelease))
                 Assert.Ignore($"PostGIS not installed, ignoring because we're on a prerelease version of PostgreSQL ({version})");
             TestUtil.IgnoreExceptOnBuildServer("PostGIS extension not installed.");
         }
     }
 }

		//
		// MembershipProvider.GetUserNameByEmail
		//

		public override string GetUserNameByEmail(string email)
		{
			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"SELECT user_name" + " FROM " + tableName + " WHERE Email = @Email AND application_name = @application_name", conn);

			cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = email;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;

			string username = "";

			try
			{
				conn.Open();

				username = (string) cmd.ExecuteScalar();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetUserNameByEmail");

                    // use fully qualified name so as not to conflict with System.Data.ProviderException
                    // in System.Data.Entity assembly
					throw new System.Configuration.Provider.ProviderException(exceptionMessage);
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}

			if (username == null)
			{
				username = "";
			}

			return username;
		}

		//
		// RoleProvider.RoleExists
		//

		public override bool RoleExists(string rolename)
		{
			bool exists = false;

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"SELECT COUNT(*) FROM " + rolesTable + "" +
					" WHERE role_name = @role_name AND application_name = @application_name", conn);

			cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;

			try
			{
				conn.Open();

				long numRecs = Convert.ToInt64(cmd.ExecuteScalar());

				if (numRecs > 0)
				{
					exists = true;
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "RoleExists");
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}

			return exists;
		}

		/// <summary>
		/// Gets number of inactive Profiles
		/// </summary>
		/// <param name="authenticationOption"></param>
		/// <param name="userInactiveSinceDate"></param>
		/// <returns></returns>
		public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption,
		                                                DateTime userInactiveSinceDate)
		{
			NpgsqlConnection conn = null;
			NpgsqlCommand cmd = null;
			try
			{
				conn = new NpgsqlConnection(_NpgsqlConnectionString);
				conn.Open();

				cmd = new NpgsqlCommand(GenerateQuery(false, authenticationOption), conn);
				cmd.CommandTimeout = CommandTimeout;
				cmd.Parameters.Add("@InactiveSinceDate", NpgsqlDbType.Timestamp).Value = userInactiveSinceDate.ToUniversalTime();

				object o = cmd.ExecuteScalar();
				if (o == null || !(o is int))
				{
					return 0;
				}
				return (int) o;
			}
			finally
			{
				if (cmd != null)
				{
					cmd.Dispose();
				}
				if (conn != null)
				{
					conn.Close();
					conn = null;
				}
			}
		}

        public void DeriveParametersInOnly()
        {
            using (var conn = OpenConnection())
            {
                // This function returns record because of the two Out (InOut & Out) parameters
                conn.ExecuteNonQuery(@"
                    CREATE OR REPLACE FUNCTION pg_temp.func(IN param1 INT, IN param2 INT) RETURNS int AS
                    '
                    BEGIN
                    RETURN param1 + param2;
                    END;
                    ' LANGUAGE 'plpgsql';
                ");

                var cmd = new NpgsqlCommand("pg_temp.func", conn) { CommandType = CommandType.StoredProcedure };
                NpgsqlCommandBuilder.DeriveParameters(cmd);
                Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
                Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
                Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Input));
                cmd.Parameters[0].Value = 5;
                cmd.Parameters[1].Value = 4;
                Assert.That(cmd.ExecuteScalar(), Is.EqualTo(9));
            }
        }

 /// <summary>
 /// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
 /// </summary>
 public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText,
     params DbParameter[] cmdParms)
 {
     NpgsqlCommand cmd = new NpgsqlCommand();
     PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
     object val = cmd.ExecuteScalar();
     cmd.Parameters.Clear();
     return val;
 }