为什么我的ADO.NET查询不会在单个执行中运行，而是会在单独的执行中运行？ [英] Why my ADO.NET queries does not run in single execution but they will run in seperate executions?
问题描述
关于一个问题及其相关的答案，我正在尝试使用SQL查询创建新的表类型并运行
我将两个查询都放在同一脚本中，即创建类型和 select ，但它不起作用。我的查询如下（整个代码放在问题的末尾）：
IF TYPE_ID（N'BranchMappingType'） IS NULL
创建类型BranchMappingType如表
（COL_ServerID int，
COL_ServerSchema Common._SMALL_，
COL_TableName Common._SMALL_，
COL_BranchNo Common._SMALL_，
COL_BranchSchema Common._SMALL_）;
从[VIP]中选择[VMA_BranchID]。[VipMapping]
加入@TVP
VMA_ServerID = COL_ServerID和
VMA_ServerSchema = COL_ServerSchema和
VMA_TableName = COL_TableName AND
VMA_BranchNo = COL_BranchNo和
VMA_BranchSchema = COL_BranchSchema;
我将使用 SqlDataReader.ExecuteReader 但它将引发异常：
列，参数或变量@TVP。 ：找不到数据类型BranchMappingType。
但是当我运行查询的第一部分时，即 IF TYPE_ID（ ...）...创建类型，它将正确执行，然后然后运行第二部分，即选择...加入@TVP 可以正常工作。
当我用一次执行 SqlDataReader运行两个查询时，似乎无法识别新创建的表类型.ExecuteReader 。
我想知道原因是什么，是否在实施过程中犯了一些错误？我更喜欢一次调用 SqlDataReader.ExecuteReader 方法来运行两个查询。
下面是完整的代码，可以更好地检查：
List< int> ;键=新的List< int>（）;
if（foreignKeyInfoList == null || foreignKeyInfoList.Count == 0）
返回新的StatusResponseKeysList {ErrorCode = ErrorCodes.ERROR_CODE_MINUS_019_NULL_OR_EMPTY_INPUT_ARGUMENT};
const string ID_FIELD = VMA_BranchID;
const string STRUCTURED_DATA_TABLE_NAME = @TVP;
const string COL1_SERVER_ID = COL_ServerID;
const string COL2_SERVER_SCHEMA = COL_ServerSchema;
const string COL3_TABLE_NAME = COL_TableName;
const string COL4_BRANCH_NO = COL_BranchNo;
常量字符串COL5_BRANCH_SCHEMA = COL_BranchSchema;
//常量字符串COL6_TABLE_ID = COL_TableID;
const string MAP1_SERVER_ID = VMA_ServerID;
const string MAP2_SERVER_SCHEMA = VMA_ServerSchema;
const string MAP3_TABLE_NAME = VMA_TableName;
const string MAP4_BRANCH_NO = VMA_BranchNo;
const string MAP5_BRANCH_SCHEMA = VMA_BranchSchema;
const string TYPE_NAME = BranchMappingType;
//常量字符串MAP6_TABLE_ID = VMA_TableID;
字符串sqlQuery = $ IF TYPE_ID（N'{TYPE_NAME}'）IS NULL + +
$创建类型{TYPE_NAME}如表 + +
$ （{COL1_SERVER_ID} int， + +
$ {COL2_SERVER_SCHEMA} Common._SMALL_， + +
$ {COL3_TABLE_NAME} Common._SMALL_， + +
$ {COL4_BRANCH_NO} Common._SMALL_， + +
$ {COL5_BRANCH_SCHEMA} Common._SMALL_ + +
$）;;
sqlQuery + = $ SELECT [{ID_FIELD}] +
$ FROM {Settings.VIP_MAPPING_TABLE} +
$ JOIN {STRUCTURED_DATA_TABLE_NAME} ON +
$ {MAP1_SERVER_ID} = {COL1_SERVER_ID} +
$ AND {MAP2_SERVER_SCHEMA} = {COL2_SERVER_SCHEMA} +
$ AND {MAP3_TABLE_NAME} = {COL3_TABLE_NAME} +
$ AND AND {MAP4_BRANCH_NO} = {COL4_BRANCH_NO} +
$ AND {MAP5_BRANCH_SCHEMA} = {COL5_BRANCH_SCHEMA}; // +
// $ AND {MAP6_TABLE_ID} = {COL6_TABLE_ID};
DataTable dataTable = new DataTable（TYPE_NAME）;
dataTable.Columns.Add（COL1_SERVER_ID，typeof（int））;
dataTable.Columns.Add（COL2_SERVER_SCHEMA，typeof（string））;
dataTable.Columns.Add（COL3_TABLE_NAME，typeof（string））;
dataTable.Columns.Add（COL4_BRANCH_NO，typeof（string））;
dataTable.Columns.Add（COL5_BRANCH_SCHEMA，typeof（string））;
//dataTable.Columns.Add(COL6_TABLE_ID，typeof（int））;
for（int i = 0; i< ForeignKeyInfoList.Count; i ++）
{
ForeignKeyLookupModelInBranchSide oneKeySet = foreignKeyInfoList [i];
DataRow行= dataTable.NewRow（）;
row [COL1_SERVER_ID] = Int32.Parse（oneKeySet.ServerID）;
row [COL2_SERVER_SCHEMA] = oneKeySet.ServerSchema;
row [COL3_TABLE_NAME] = oneKeySet.TableName;
row [COL4_BRANCH_NO] = oneKeySet.BranchNo;
row [COL5_BRANCH_SCHEMA] = oneKeySet.BranchSchema;
dataTable.Rows.Add（row）;
}
尝试
{
使用（SqlConnection sqlConnection = new SqlConnection（Settings.connectionString））
{
SqlCommand命令= new SqlCommand （sqlQuery，sqlConnection）;
SqlParameter p = command.Parameters.Add（STRUCTURED_DATA_TABLE_NAME，SqlDbType.Structured）;
p.Value = dataTable;
p.TypeName = TYPE_NAME;
sqlConnection.Open（）;
SqlDataReader reader = command.ExecuteReader（）;
if（reader.HasRows）
{
而（reader.Read（））
{
for（int i = 0; i
keys.Add（reader.GetInt32（i））;
}
}
}
否则
{
}
sqlConnection.Close（）;
}
返回新的StatusResponseKeysList
{
keysList =键，
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_019_NULL_OR_EMPTY_INPUT_ARGUMENT
};
}
catch（异常e）
{
//此处捕获异常！!!!!!!：
//列，参数或变量@ TVP：找不到数据类型BranchMappingType。
if（e.GetType（）== typeof（SqlException））
{
//这是数据库错误
返回新的StatusResponseKeysList
{
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_014_DATABASE_EXCEPTION
};
}
else
{
//其他类型的错误（不是DB错误）
返回新的StatusResponseKeysList
{
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_001_COMMON_ERROR
};
}
}
表类型必须存在，然后才能执行使用该类型的参数化查询。原因是在批处理执行之前，数据库引擎首先在tempdb中创建与服务器上定义的表类型相匹配的内部表 。然后，它使用API提供的结构化参数值和元数据将TVP值批量插入tempdb中。
只有在表加载后，批处理才会执行，可以在其中使用TVP值。
含义是不能创建表类型并在同一表中使用它使用类型作为参数的批处理。
Regarding a question and its related answer, I'm trying to create a new table type using SQL queries and run a join query on data which are of this table type.
I put both queries in the same script, i.e. create type and select but it is not working. My query is as below (the whole code is brought in the end of question):
IF TYPE_ID(N'BranchMappingType') IS NULL
CREATE TYPE BranchMappingType As Table
(COL_ServerID int,
COL_ServerSchema Common._SMALL_,
COL_TableName Common._SMALL_,
COL_BranchNo Common._SMALL_,
COL_BranchSchema Common._SMALL_ );
SELECT [VMA_BranchID] FROM [VIP].[VipMapping]
JOIN @TVP ON
VMA_ServerID=COL_ServerID AND
VMA_ServerSchema=COL_ServerSchema AND
VMA_TableName=COL_TableName AND
VMA_BranchNo=COL_BranchNo AND
VMA_BranchSchema=COL_BranchSchema;
I will execute my query using SqlDataReader.ExecuteReader but It will throw exception:
Column, parameter, or variable @TVP. : Cannot find data type BranchMappingType.
But when I run 1st part of query, i.e., IF TYPE_ID(...) ... CREATE TYPE it will execute with no errors and then running the second part i.e. Select from ... join @TVP works flawlessly.
It seems newly created table type is not recognized when I run both queries with single execution of SqlDataReader.ExecuteReader.
I want to know what is the reason and If I've made some mistakes during my implementation or not? I prefer to run both queries with one-time calling of SqlDataReader.ExecuteReader method.
Here is full code for better inspection:
List<int> keys = new List<int>();
if (foreignKeyInfoList == null || foreignKeyInfoList.Count == 0)
return new StatusResponseKeysList { ErrorCode = ErrorCodes.ERROR_CODE_MINUS_019_NULL_OR_EMPTY_INPUT_ARGUMENT };
const string ID_FIELD = "VMA_BranchID";
const string STRUCTURED_DATA_TABLE_NAME = "@TVP";
const string COL1_SERVER_ID = "COL_ServerID";
const string COL2_SERVER_SCHEMA = "COL_ServerSchema";
const string COL3_TABLE_NAME = "COL_TableName";
const string COL4_BRANCH_NO = "COL_BranchNo";
const string COL5_BRANCH_SCHEMA = "COL_BranchSchema";
//const string COL6_TABLE_ID = "COL_TableID";
const string MAP1_SERVER_ID = "VMA_ServerID";
const string MAP2_SERVER_SCHEMA = "VMA_ServerSchema";
const string MAP3_TABLE_NAME = "VMA_TableName";
const string MAP4_BRANCH_NO = "VMA_BranchNo";
const string MAP5_BRANCH_SCHEMA = "VMA_BranchSchema";
const string TYPE_NAME = "BranchMappingType";
//const string MAP6_TABLE_ID = "VMA_TableID";
string sqlQuery = $"IF TYPE_ID(N'{TYPE_NAME}') IS NULL" + " " +
$"CREATE TYPE {TYPE_NAME} As Table" + " " +
$"({COL1_SERVER_ID} int," + " " +
$"{COL2_SERVER_SCHEMA} Common._SMALL_," + " " +
$"{COL3_TABLE_NAME} Common._SMALL_," + " " +
$"{COL4_BRANCH_NO} Common._SMALL_," + " " +
$"{COL5_BRANCH_SCHEMA} Common._SMALL_" + " " +
$"); ";
sqlQuery += $"SELECT [{ID_FIELD}] " +
$"FROM {Settings.VIP_MAPPING_TABLE} " +
$"JOIN {STRUCTURED_DATA_TABLE_NAME} ON " +
$"{MAP1_SERVER_ID}={COL1_SERVER_ID} " +
$" AND {MAP2_SERVER_SCHEMA}={COL2_SERVER_SCHEMA} " +
$" AND {MAP3_TABLE_NAME}={COL3_TABLE_NAME} " +
$" AND {MAP4_BRANCH_NO}={COL4_BRANCH_NO} " +
$" AND {MAP5_BRANCH_SCHEMA}={COL5_BRANCH_SCHEMA} ";// +
//$" AND {MAP6_TABLE_ID}={COL6_TABLE_ID}";
DataTable dataTable = new DataTable(TYPE_NAME);
dataTable.Columns.Add(COL1_SERVER_ID, typeof(int));
dataTable.Columns.Add(COL2_SERVER_SCHEMA, typeof(string));
dataTable.Columns.Add(COL3_TABLE_NAME, typeof(string));
dataTable.Columns.Add(COL4_BRANCH_NO, typeof(string));
dataTable.Columns.Add(COL5_BRANCH_SCHEMA, typeof(string));
//dataTable.Columns.Add(COL6_TABLE_ID, typeof(int));
for (int i = 0; i < foreignKeyInfoList.Count; i++)
{
ForeignKeyLookupModelInBranchSide oneKeySet = foreignKeyInfoList[i];
DataRow row = dataTable.NewRow();
row[COL1_SERVER_ID] = Int32.Parse(oneKeySet.ServerID);
row[COL2_SERVER_SCHEMA] = oneKeySet.ServerSchema;
row[COL3_TABLE_NAME] = oneKeySet.TableName;
row[COL4_BRANCH_NO] = oneKeySet.BranchNo;
row[COL5_BRANCH_SCHEMA] = oneKeySet.BranchSchema;
dataTable.Rows.Add(row);
}
try
{
using (SqlConnection sqlConnection = new SqlConnection(Settings.connectionString))
{
SqlCommand command = new SqlCommand(sqlQuery, sqlConnection);
SqlParameter p = command.Parameters.Add(STRUCTURED_DATA_TABLE_NAME, SqlDbType.Structured);
p.Value = dataTable;
p.TypeName = TYPE_NAME;
sqlConnection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
keys.Add(reader.GetInt32(i));
}
}
}
else
{
}
sqlConnection.Close();
}
return new StatusResponseKeysList
{
keysList = keys,
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_019_NULL_OR_EMPTY_INPUT_ARGUMENT
};
}
catch (Exception e)
{
//Exception caught here !!!!!!!:
//"Column, parameter, or variable @TVP. : Cannot find data type BranchMappingType."
if (e.GetType() == typeof(SqlException))
{
//this is a db error
return new StatusResponseKeysList
{
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_014_DATABASE_EXCEPTION
};
}
else
{
//other types of erros (not a DB-error)
return new StatusResponseKeysList
{
ErrorCode = ErrorCodes.ERROR_CODE_MINUS_001_COMMON_ERROR
};
}
}
The table type must exist before the parameterized query that uses the type is executed. The reason is the database engine first creates an internal table in tempdb matching the table type defined on the server before the batch executes. It then bulk inserts the TVP value into tempdb using the structured parameter value and meta-data provided by the API. Only after the table is loaded does the batch execute, where it can use the TVP value.
The implication is that one cannot create the table type and use it in the same batch that uses the type as a parameter.
这篇关于为什么我的ADO.NET查询不会在单个执行中运行，而是会在单独的执行中运行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
