验证数据库中的移动号码，确保其在其他数据库中不存在 [英] Validate mobile number in a database making sure it does not exist in other database

问题描述

Suppose I have four databases in my project and I would like to validate a Mobile number but i want to check that that mobile number is also not present in the remaining three databases.

How to do that ?

我的尝试：



尝试Googline但没有得到太多信息。

What I have tried:

Tried Googline but didnt get much info.

推荐答案

创建一个带有2个参数的存储过程（输入PhoneNumber，输出存在）包含所有4个表的UNION查询。然后在C＃中，您可以使用这两个参数调用该存储过程并查明它是否存在。



SQL存储过程

Create a Stored Procedure with 2 parameters (input PhoneNumber, output Exists) that contains a UNION query for all 4 tables. Then in C# you can call that Stored Procedure with those 2 parameters and find out if it exists or not.

SQL Stored Procedure

CREATE PROCEDURE dbo.usp_PhoneNumberCheck (
	@PhoneNumber	NVARCHAR(100),
	@Exists		BIT	OUTPUT	= 1
) 
AS
BEGIN
	; WITH cte AS (
		SELECT PhoneNumber FROM DatabaseName1.SchemaName.TableName
		UNION
		SELECT PhoneNumber FROM DatabaseName2.SchemaName.TableName
		UNION
		SELECT PhoneNumber FROM DatabaseName3.SchemaName.TableName
		UNION
		SELECT PhoneNumber FROM DatabaseName4.SchemaName.TableName
	)

	IF NOT EXISTS ( SELECT 1 FROM cte WHERE PhoneNumber = @PhoneNumber) BEGIN
		SET @Exists = 0
	END
END
GO

C＃method guts

C# method guts

string PhoneNumber = "9115551212"; // placeholder
bool NumberExists = true;

using (SqlConnection conn = new SqlConnection("ConnectionString")) {
	SqlCommand cmd = new SqlCommand("dbo.usp_PhoneNumberCheck", conn);
	cmd.CommandType = CommandType.StoredProcedure;

	SqlParameter outputExists = new SqlParameter("@Exists", SqlDbType.Bit) {Direction = ParameterDirection.Output};

	cmd.Parameters.AddWithValue("@PhoneNumber", PhoneNumber);
	cmd.Parameters.Add(outputExists);

	conn.Open();
	cmd.ExecuteNonQuery();

	NumberExists = outputExists.value;

	conn.Close();
}

这篇关于验证数据库中的移动号码，确保其在其他数据库中不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！