Недопустимое имя объекта 'dbo.CategoryIdArray'
Мой мотив – передать длинный массив ID как параметр хранимой процедуре и выбрать данные на основе идентификатора. Итак, я создал Type в SQL Server
CREATE TYPE [dbo].[CategoryIdArray] AS TABLE( [CategoryId] [bigint] NULL ) GO и хранимая процедура
ALTER PROCEDURE [dbo].[GetNewestArticleByCatsPageWise] @dt as [dbo].[CategoryIdArray] READONLY, @PageIndex INT = 1 ,@PageSize INT = 10 ,@PageCount INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT ROW_NUMBER() OVER ( ORDER BY [dateadded] )AS RowNumber,[desid] INTO #Results FROM [DB_user1212].[dbo].[discussions] as d , [DB_user1212].[dbo].[CategoryMap] as c where d.desid=c.[Topic Id] and c.[Category Id] in (select CategoryId from [dbo].[CategoryIdArray]) and [TopicType]='1' order by [dateadded] DECLARE @RecordCount INT SELECT @RecordCount = COUNT(*) FROM #Results SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2))) PRINT @PageCount SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results END
Пытался использовать выше хранимую процедуру по коду ниже
public List<String> getNewestArticleByCategoryPageWise( long[] categoryId) { List<string> topicId= new List<string>(); try { DataTable dt_Categories = new DataTable(); dt_Categories.Columns.Add("Category", typeof(String)); DataRow workRow; foreach(long cat in categoryId) { workRow = dt_Categories.NewRow(); workRow["Category"] = cat; dt_Categories.Rows.Add(workRow); } int pageIndex = 1; SqlCommand cmd = new SqlCommand("dbo.GetNewestArticleByCatsPageWise", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@PageIndex", pageIndex); cmd.Parameters.AddWithValue("@PageSize", 10); cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output; SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories); tvparam.SqlDbType = SqlDbType.Structured; con.Open(); sdr= cmd.ExecuteReader(); while(sdr.Read()) { topicId.Add(sdr.GetString(0)); } con.Close(); } catch(Exception ex) { con.Close(); throw ex; } return topicId; }вpublic List<String> getNewestArticleByCategoryPageWise( long[] categoryId) { List<string> topicId= new List<string>(); try { DataTable dt_Categories = new DataTable(); dt_Categories.Columns.Add("Category", typeof(String)); DataRow workRow; foreach(long cat in categoryId) { workRow = dt_Categories.NewRow(); workRow["Category"] = cat; dt_Categories.Rows.Add(workRow); } int pageIndex = 1; SqlCommand cmd = new SqlCommand("dbo.GetNewestArticleByCatsPageWise", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@PageIndex", pageIndex); cmd.Parameters.AddWithValue("@PageSize", 10); cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output; SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories); tvparam.SqlDbType = SqlDbType.Structured; con.Open(); sdr= cmd.ExecuteReader(); while(sdr.Read()) { topicId.Add(sdr.GetString(0)); } con.Close(); } catch(Exception ex) { con.Close(); throw ex; } return topicId; }
Когда я запускаю выше, исключение функции Invalid object name 'dbo.CategoryIdArray'. Но я создал его как тип. Помогите мне, что я пропустил. Я повторил это .
Проблема в том, что эта строка в хранимой процедуре связана с этой строкой
select CategoryId from [dbo].[CategoryIdArray] .
Мы не можем выбирать из такого типа, мы должны использовать
select CategoryId from @dt
Первое, что я делаю, когда получаю эти вопросы, – создать образец базы данных. Следующий код создает следующее.
1 – база данных с именем [test]
2 – таблица с именем [Обсуждения]
3 – таблица с именем [CategoryMap]
4 – пользовательский тип таблицы с именем [CategoryIdArray]
5 – загрузить таблицы со 100 записями данных
-- -- Create a test db -- USE [master]; go CREATE DATABASE [Test]; GO -- -- Create the user defined type -- USE [Test]; go CREATE TYPE [CategoryIdArray] AS TABLE ( [CategoryId] [bigint] NULL ); -- -- Create skelton tables -- create table Discussions ( dis_id int identity (1,1), dis_name varchar(64), dis_added_dte datetime default getdate() ); go create table CategoryMap ( cat_id int identity(1,1), cat_topic_id int, cat_topic_type char(1) ); go -- clear tables truncate table Discussions; truncate table CategoryMap; go -- -- Create 100 rows of dummy data -- declare @cnt int = 0; while @cnt < 100 begin insert into Discussions (dis_name) values ('sample discussion record # ' + str(@cnt, 2, 0)); insert into CategoryMap (cat_topic_id, cat_topic_type) values (@cnt+1, '1') set @cnt = @cnt + 1; end; go -- -- Show the sample data -- select * from Discussions; go select * from CategoryMap; go
Второй шаг – перезаписать хранимую процедуру. Если вы используете ниже 2012 года, перейдите к функции window rownumber (). В 2012 году условия смещения и выборки заказа были включены для подкачки.
http://technet.microsoft.com/en-us/library/ms188385(v=sql.110).aspx
-- -- Create my procedure -- create procedure [GetArticlesByPage] @Tvp as [CategoryIdArray] READONLY, @PageIndex INT = 1, @PageSize INT = 10, @PageCount INT OUTPUT AS BEGIN -- Declare variables DECLARE @var_recs int = 0; DECLARE @var_offset int = 0; -- Do not count the records SET NOCOUNT ON; -- Start of paging SET @var_offset = @var_offset + ((@PageIndex - 1) * @PageSize); -- Set page count variable SELECT @var_recs = count(*) FROM [dbo].[Discussions] as d JOIN [dbo].[CategoryMap] as c ON d.dis_id = c.cat_topic_id JOIN @TVP a ON c.cat_id = a.CategoryId WHERE cat_topic_type = '1'; set @PageCount = ceiling(cast(@var_recs as real) / cast(@PageSize as real)); -- -- Return the record set -- SELECT dis_id FROM [dbo].[Discussions] as d JOIN [dbo].[CategoryMap] as c ON d.dis_id = c.cat_topic_id JOIN @TVP a ON c.cat_id = a.CategoryId WHERE cat_topic_type = '1' ORDER BY dis_added_dte OFFSET @var_offset ROWS FETCH NEXT @PageSize ROWS ONLY; END; GO
Я оставил счет страницы на месте; Тем не менее, я не думаю, что это необходимо, так как вы можете повторить вызов до тех пор, пока результирующий набор не будет пустым.
Пожалуйста, не выгружайте набор записей во временную таблицу, так как он может быть довольно большим, если вы возвращаете все столбцы для отображения. Я выбираю два отдельных вызова. Один для общего счета. Один для одной страницы.
Последняя часть TSQL предназначена для проверки хранимой процедуры из SSMS.
-- -- Call the stored procedure -- -- instantiate tvp DECLARE @my_tvp as [CategoryIdArray]; DECLARE @my_page_cnt as int; -- add 25 entries declare @cnt int = 25; while @cnt < 50 begin insert into @my_tvp (CategoryId) values (@cnt + 1); set @cnt = @cnt + 1; end; -- show the data in the tvp select * from @my_tvp -- call the function exec [GetArticlesByPage] @my_tvp, 1, 10, @PageCount = @my_page_cnt OUTPUT; -- show the data in the output select @my_page_cnt as 'my_pages'; go
В моем примере тестирования я хотел, чтобы строки от 26 до 50 постранично отображались в виде 10 строк. Результат 1 – это 25 строк, результат 2 – это 10 строк, которые были выгружены, а результат 3 – сколько страниц. Таким образом, часть TSQL решения является звуковой.
Оставайтесь с нами для сеанса отладки программы C # позже сегодня вечером.
http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/
Взгляните на этот пост. Это делает именно то, что вы пытаетесь сделать.
Вот некоторые идеи, которые нужно попробовать.
1 – Убедитесь, что для свойства соединения, база данных по умолчанию для входа – [Test] для моего примера.
2 – Является ли тип, определенный в базе данных [Test]? Пожалуйста, дважды проверьте это.
3 – Это правильно? Имя столбца – [CategoryId] в типе базы данных. У вас есть следующее – [Категория]. Попробуйте изменить имя в коде C #.
dt_Categories.Columns.Add ("Категория", typeof (String));
4 – Удалите [dbo]. от типа в SP. Это не в примере из советов MS SQL. Возможно, это путает проблему. SQL-сервер решит имя.
5 – Я заметил, что тип определяется как большой int, но идентификатор в таблицах – int? Убедитесь, что типы данных согласованы.
Пожалуйста, попробуйте эти предложения. Вернитесь ко мне, как вы разобрались.
Можете ли вы получить мне подробную трассировку стека вызовов и сообщение об ошибке, если это все еще проблема?
Итак, вот консольное приложение на C #, которое я обещал.
Он работает так, как ожидалось.
Вы смешивали некоторые идеи, которые являются основой ADO.NET и таблиц данных. Вы должны привыкнуть к непосредственному окну и локальным переменным. Это поможет вам выявить проблемы.
Вот мой образец вызова хранимой процедуры.
1 – Таблица данных установки (от 50 до 74)
2 – Страница данных по 5
3 – Посмотрите на вторую страницу
// // Good Ref. - http://msdn.microsoft.com/en-us/library/ms254937(v=vs.110).aspx // // Basic stuff from C# console app using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; // Required for data table using System.Data; using System.Data.SqlClient; // Standard stuff ... namespace ConsoleApplication1 { class Program { static void Main(string[] args) { // Debug info Console.WriteLine("Test - Start"); // Create the table with one column DataTable my_Table; my_Table = new DataTable("Category"); my_Table.Columns.Add("CategoryId", typeof(string)); // Add data to table for (int my_Cnt = 50; my_Cnt < 75; my_Cnt++) { DataRow my_Row = my_Table.NewRow(); my_Row["CategoryId"] = my_Cnt.ToString(); my_Table.Rows.Add(my_Row); } // Debug info Console.WriteLine("Test - created data set"); // Create a connection SqlConnection my_Conn; string str_Conn = "Server=localhost;Database=Test;Trusted_Connection=True;"; my_Conn = new SqlConnection(str_Conn); // Debug info Console.WriteLine("Test - create connection"); // Create the command and set its properties. SqlCommand my_Cmd = new SqlCommand(); my_Cmd.Connection = my_Conn; my_Cmd.CommandText = "dbo.GetArticlesByPage"; my_Cmd.CommandType = CommandType.StoredProcedure; // Add parameter 0 SqlParameter my_Parm0 = new SqlParameter(); my_Parm0.ParameterName = "@Tvp"; my_Parm0.SqlDbType = SqlDbType.Structured; my_Parm0.Direction = ParameterDirection.Input; my_Parm0.Value = my_Table; my_Cmd.Parameters.Add(my_Parm0); // Add parameter 1 SqlParameter my_Parm1 = new SqlParameter(); my_Parm1.ParameterName = "@PageIndex"; my_Parm1.SqlDbType = SqlDbType.Int; my_Parm1.Direction = ParameterDirection.Input; my_Parm1.Value = 2; my_Cmd.Parameters.Add(my_Parm1); // Add parameter 2 SqlParameter my_Parm2 = new SqlParameter(); my_Parm2.ParameterName = "@PageSize"; my_Parm2.SqlDbType = SqlDbType.Int; my_Parm2.Direction = ParameterDirection.Input; my_Parm2.Value = 5; my_Cmd.Parameters.Add(my_Parm2); // Add parameter 3 SqlParameter my_Parm3 = new SqlParameter(); my_Parm3.ParameterName = "@PageCount"; my_Parm3.SqlDbType = SqlDbType.Int; my_Parm3.Direction = ParameterDirection.Output; my_Parm3.Value = 5; my_Cmd.Parameters.Add(my_Parm3); // Open the connection my_Conn.Open(); // Debug info Console.WriteLine("Test - execute reader"); // Execute the reader SqlDataReader my_Reader = my_Cmd.ExecuteReader(); if (my_Reader.HasRows) { while (my_Reader.Read()) { Console.WriteLine("{0}", my_Reader[0].ToString()); } } else { Console.WriteLine("No rows found."); } // Close the reader my_Reader.Close(); // Number of pages (output after reader - order is important) Console.WriteLine("Pages = "); Console.WriteLine(my_Cmd.Parameters["@PageCount"].Value.ToString()); // Close the connection my_Conn.Close(); // Debug info Console.WriteLine("Test - close connection"); // Debug info Console.WriteLine("Test - End"); // Pause to view output Console.Read(); } } }
Вот снимок правильного вывода из консольного приложения C #.
Я должен поблагодарить вас за ваш вопрос!
Прошло некоторое время с тех пор, как я закодирован на C #. Но, как на велосипеде, не требуется много времени, чтобы вернуться на него. Примеры T-SQL были выполнены с помощью SSMS 2012, а программа C # была выполнена с VS 2013 года. Последняя и самая большая.
Спокойной ночи!
Я не претендую на эффективный или корректный, но читаемый современный синтаксис, ваш базовый запрос может быть написан следующим образом:
SELECT ROW_NUMBER() OVER (ORDER BY [dateadded]) AS RowNumber,[desid] INTO #Results FROM [DB_user1212].[dbo].[discussions] as d JOIN [DB_user1212].[dbo].[CategoryMap] as c ON d.desid=c.[Topic Id] JOIN [dbo].[CategoryIdArray] arr ON c.[Category Id] = arr.CategoryID WHERE [TopicType]='1'
Вот ваше решение:
В вашей хранимой процедуре в вашем операторе WHERE вы выбираете * из «ТИПА», а не в фактический объект объекта, который передается. Это похоже на «SELECT * FROM VARCHAR», что не имеет смысла. Попробуй это:
... and c.[Category Id] in ( select CategoryId from @dt -- select from the actual parameter, not its TYPE ) ...
Вместо:
workRow["Category"] = cat;
использование
workRow["CategoryId"] = cat;
Проверьте в студии управления сервером SQL, если пользователь имеет базу данных по умолчанию, установленную в базу данных, к которой вы пытаетесь получить доступ. У меня был такой же тип ошибок и застрял в течение нескольких дней. Наконец, выяснилось, что у пользователя был установлен Master как его «стандартная БД.