Linq to sql Connection(stored Procedure) throws cast exception

Question

I need to access data by executing stored procedure through linq operations. please look at my code below. tell me where i went wrong.

public int ID { get; set; }
public string CategoryName { get; set; }

public static void GetCategory()
{
    string connectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
    var query = "EXEC SP_GET_ALL_CATEGORY"; // my stored procedure which is in SQL server
    using (DataContext dc = new DataContext(connectionString))
    {
        if (dc.DatabaseExists())
        {
            var _vGetCategory = dc.ExecuteQuery<category>(string.Format(query, 1, "null")).ToList(); // execution should only through Stored Procedures

            for (int i = 0; i < _vGetCategory.Count; i++)
            {
                string _strName = _vGetCategory[i].CategoryName;
            }
        }
    }

and my exception:

  System.InvalidCastException was unhandled by user code
  Message=Specified cast is not valid.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.SqlBuffer.get_Int32()
       at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
       at Read_category(ObjectMaterializer`1 )
       at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at category.GetCategory() in d:\Shankar\sample\LinqtoSql\App_Code\category.cs:line 28
       at _Default.Page_Load(Object sender, EventArgs e) in d:\Shankar\sample\LinqtoSql\Default.aspx.cs:line 8
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: 
    }

what does the stored proc look like? My guess is your code is looking for a category object but your proc is returning a result set and you need to cast that result set to a category. — Brian, Apr 24 '12 at 13:06
@Brian my Stored Proc contains select query.... how do i resolve this... — Sankar M, Apr 24 '12 at 13:18
instead of calling a stored proc can you do a linq query against the entity? — Brian, Apr 24 '12 at 13:38
because you're dealing with two different things then. you need to either go ado.net and a stored proc or linq. You can try casting the return as a Category object but more likely you'll have to iterate through your return and assign attributes. are you returning a dataset? — Brian, Apr 24 '12 at 15:22
@Brian Pls confirm me is it a right way to execute a stored procedure? — Sankar M, Apr 25 '12 at 05:31
Executing the stored procedure isn't the problem but you will need to cast it as a category object, or tie it to a datatable and loop through the data table and link up each row to a category object and then add each object to a list of category. — Brian, Apr 25 '12 at 13:04

score 1 · Answer 1 · answered Apr 24 '12 at 13:05

1

It would appear it can't convert the data coming back as Category into an Int32. Is it possible that a null value is coming back? Or that a value outside the Min/Max of Int32?

answered Apr 24 '12 at 13:05

taylonr

10,732
5
37
66

run the stored proc and see what data is coming back, verify that none are null and all are within the range of int.min and int.max – taylonr Apr 25 '12 at 12:21

score 0 · Accepted Answer · answered May 03 '12 at 12:32

0

Use Int64 for Bigint datatypes which is declared at data columns... it works for me...

answered May 03 '12 at 12:32

Sankar M

4,549
12
37
55

Linq to sql Connection(stored Procedure) throws cast exception

2 Answers2