I am getting following error when I call the Select function:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 3 ("@SearchTableVar"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid.
C# code
//DTO
public class SP_SearchEntity_Result
{
public string ID { get; set; }
public string NAME { get; set; }
}
//Businesslogic
public IQueryable Select(int PageIndex, int PageSize, List<KeyValuePair<string, string>> SearchBy, List<KeyValuePair<string, System.Data.SqlClient.SortOrder>> SortBy)
{
SqlDatabase obj = (SqlDatabase)DatabaseFactory.CreateDatabase();//System.Configuration.ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString
return obj.ExecuteSprocAccessor<SP_SearchEntity_Result>("SP_SearchEntity", PageIndex, PageSize, SearchBy.ToDataTable(), SortBy.ToDataTable()).AsQueryable<SP_SearchEntity_Result>();
}
//Extension methods
public static DataTable ToDataTable(this List<KeyValuePair<string, string>> source)
{
DataTable dataTable = new DataTable("Test");
dataTable.Columns.Add("KEY",typeof(System.String));
dataTable.Columns.Add("VALUE", typeof(System.String));
foreach (KeyValuePair<string, string> data in source)
{
var dr = dataTable.NewRow();
dr["KEY"] = data.Key;
dr["VALUE"] = data.Value;
dataTable.Rows.Add(dr);
}
return dataTable;
}
public static DataTable ToDataTable(this List<KeyValuePair<string, System.Data.SqlClient.SortOrder>> source)
{
DataTable dataTable = new DataTable("Test");
dataTable.Columns.Add("KEY", typeof(System.String));
dataTable.Columns.Add("VALUE", typeof(System.String));
foreach (KeyValuePair<string, System.Data.SqlClient.SortOrder> data in source)
{
var dr = dataTable.NewRow();
dr["KEY"] = data.Key;
dr["VALUE"] = data.Value == System.Data.SqlClient.SortOrder.Ascending ? "ASC" : "DESC";
dataTable.Rows.Add(dr);
}
return dataTable;
}
The stored procedure returns two tables in result
SQL proc definition
CREATE TYPE KeyValueTableVariable AS TABLE
(
[KEY] NVARCHAR(800),
[VALUE] NVARCHAR(800)
)
GO
CREATE PROCEDURE SP_SearchEntity
@PageIndex INT=NULL,
@PageSize INT=NULL,
@SearchTableVar dbo.KeyValueTableVariable READONLY,
@SortTableVar dbo.KeyValueTableVariable READONLY
AS
BEGIN
/*Bla bla bla*/
SELECT '1' as [ID], 'Nitin' as [NAME]
SELECT '1' as [COUNT]
END
I find that the xml datatype for stored proc parameters is easier to use. Rather than casting the parameters to DataTables, you would cast them to XML for the following example:
CREATE PROCEDURE SP_SearchEntity
@PageIndex INT=NULL,
@PageSize INT=NULL,
@SearchTableVar xml=NULL,
@SortTableVar xml=NULL
AS
BEGIN
/*Bla bla bla*/
SELECT '1' as [ID], 'Nitin' as [NAME]
SELECT '1' as [COUNT]
END
Here's a sample of the KeyValuePair and a query, after it is serialized as XML:
declare @sampleXml xml = '
<ArrayOfKeyValuePairOfstringstring xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<KeyValuePairOfstringstring>
<key>foo</key>
<value>bar</value>
</KeyValuePairOfstringstring>
<KeyValuePairOfstringstring>
<key>hello</key>
<value>world</value>
</KeyValuePairOfstringstring>
</ArrayOfKeyValuePairOfstringstring>'
select
Node.Elem.value('*:key[1]', 'nvarchar(800)') as [Key]
,Node.Elem.value('*:value[1]', 'nvarchar(800)') as Value
from @sampleXml.nodes(N'/*:ArrayOfKeyValuePairOfstringstring/*:KeyValuePairOfstringstring') Node(Elem)
go
and a XML Serializer:
// from Plinqo: http://www.codesmithtools.com/product/frameworks
public static string ToXml<T>(this T item)
{
var settings = new XmlWriterSettings();
settings.Indent = true;
settings.OmitXmlDeclaration = true;
var sb = new System.Text.StringBuilder();
using (var writer = XmlWriter.Create(sb, settings))
{
var serializer = new DataContractSerializer(typeof(T));
serializer.WriteObject(writer, item);
}
return sb.ToString();
}
I'll show you how to do that, but I'm not sure it's what you want to do, based on your mock SQL. If you are really just returning a count of the objects that were returned, you can count your results after they are IQueryable.
First you'll need a way of binding the objects, which you can get by extending MVC. These model binders expect your query to return column names that match your model properties.
using System;
using System.Collections.Generic;
using System.Web.Mvc;
public partial class ModelBinder
{
/// <summary>
/// Binds the values of an Dictionary to a POCO model
/// </summary>
public virtual T BindModel<T>(IDictionary<string, object> dictionary)
{
DictionaryValueProvider<object> _dictionaryValueProvider = new DictionaryValueProvider<object>(dictionary, null);
return BindModel<T>(_dictionaryValueProvider);
}
/// <summary>
/// Binds the values of an IValueProvider collection to a POCO model
/// </summary>
public virtual T BindModel<T>(IValueProvider dictionary)
{
Type _modelType = typeof(T);
var _modelConstructor = _modelType.GetConstructor(new Type[] { });
object[] _params = new object[] { };
string _modelName = _modelType.Name;
ModelMetadata _modelMetaData = ModelMetadataProviders.Current.GetMetadataForType(() => _modelConstructor.Invoke(_params), _modelType);
var _bindingContext = new ModelBindingContext() { ModelName = _modelName, ValueProvider = dictionary, ModelMetadata = _modelMetaData };
DefaultModelBinder _binder = new DefaultModelBinder();
ControllerContext _controllerContext = new ControllerContext();
T _object = (T)_binder.BindModel(_controllerContext, _bindingContext);
return _object;
}
}
Example conventions for model binding:
public partial class Person
{
public int Id { get; set; }
public string Name { get; set; }
public Project Project { get; set; }
public List<Person> Friends { get; set; }
}
public partial class Project
{
public int Id { get; set; }
public string Name { get; set; }
}
select
1 as [Id]
, 'NitinJs' as [Name]
, 5 as [Project.Id]
, 'Model Binding' as [Project.Name]
, 2 as [Friends[0]].Id]
, 'John' as [Friends[0]].Name]
, 3 as [Friends[1]].Id]
, 'Jane' as [Friends[1]].Name]
Now, you need a method that will read your Data results and bind them to a model:
/// <summary>
/// Reads a record from a SqlDataReader, binds it to a model, and adds the object to the results parameter
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <param name="modelName"></param>
/// <param name="results"></param>
private void ReadAs<T>(SqlDataReader reader, string modelName, List<T> results, string commandText)
{
Dictionary<string, object> _result = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
string _key = modelName + "." + reader.GetName(i);
object _value = reader.GetValue(i);
if (_result.Keys.Contains(_key)) // Dictionaries may not have more than one instance of a key, but a query can return the same column twice
{ // Since we are returning a strong type, we ignore columns that exist more than once.
throw new Exception("The following query is returning more than one field with the same key, " + _key + ": " + commandText); // command.CommandText
}
_result.Add(_key, _value);
}
T _object = new ModelBinder().BindModel<T>(_result);
if (_object != null)
{
results.Add((T)_object);
}
}
Next, you need a way of getting an open connection to your Database (note: you'll probaly want to grab _dbConnectionString from your config):
public SqlConnection GetOpenConnection()
{
_sqlConnection = new SqlConnection(_dbConnectionString);
_sqlConnection.Open();
return _sqlConnection;
}
Finally, you need to connect to your database to get your result sets:
/// <summary>
/// Executes a SqlCommand that expects four result sets and binds the results to the given models
/// </summary>
/// <typeparam name="T1">Type: the type of object for the first result set</typeparam>
/// <typeparam name="T2">Type: the type of object for the second result set</typeparam>
/// <returns>List of Type T: the results in a collection</returns>
public void ExecuteAs<T1, T2>(SqlCommand command, List<T1> output1, List<T2> output2)
{
string _modelName1 = typeof(T1).Name;
string _modelName2 = typeof(T2).Name;
string _commandText = command.CommandText;
using (SqlConnection connection = GetOpenConnection())
{
using (command)
{
command.Connection = connection;
command.CommandTimeout = _defaultCommandTimeout;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) // Call Read before accessing data.
{
ReadAs<T1>(reader, _modelName1, output1, _commandText);
}
reader.NextResult();
while (reader.Read()) // Call Read before accessing data.
{
ReadAs<T2>(reader, _modelName2, output2, _commandText);
}
} // end using reader
} // end using command
} // end using connection
}
Then your select method would look more like this:
public void SelectInto<SP_SearchEntity_Result, int>(int PageIndex, int PageSize, List<KeyValuePair<string, string>> SearchBy, List<KeyValuePair<string, System.Data.SqlClient.SortOrder>> SortBy, List<<SP_SearchEntity_Result> result1, List<int> result2)
{
SqlCommand command = new SqlCommand("SP_SearchEntity");
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("PageIndex", SqlDbType.Int).Value = PageIndex;
command.Parameters.Add("SearchTableVar", SqlDbType.Xml).Value = SearchBy.ToXml();
List<KeyValuePair<string, string>> SortByCastToString = // modify your ToDataTable method so you can pass a List<KeyValuePair<string, string>> for SortBy
command.Parameters.Add("SortTableVar", SqlDbType.Xml).Value = SortByCastToString.ToXml();
ExecuteAs<SP_SearchEntity_Result, int>(command, result1, result2);
}
public void SomeCallingMethod()
{
List<SP_SearchEntity_Result> _results = new List<SP_SearchEntity_Result>{};
List<int> _counts = new List<int>{};
// ...
// setup your SearchBy and SortBy
// ...
SelectInto<SP_SearchEntity_Result, int>(1, 20, SearchBy, SortBy, _results, _counts);
}