We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

LINQ to SQL: using Stored Procedure

Hi All!

I'm using VB.NET and SfDataGrid with "LINQ to SQL" pointing to a SQL Table, exactly like the Documentation advises.
Everything is OK.

But I would like to call a Stored Procedure in database, passing parameters and getting its results.
DBML has both TABLE and METHOD (my Stored Procedure was dragged into the Methods Panel) but I cannot call it at the Form Load due to the error:

"Results cannot be enumerated more than one time"  (or something like this, since it is a Portuguese translation)

How can I deal with it?

Thanks in advance.



8 Replies

GG Gowtham Gopalsamy Syncfusion Team October 1, 2019 01:31 PM UTC

Hi David , 
 
Thanks for using Syncfusion controls. 
 
We are analyzing your reported issue. We will update more details on 04th October,2019.  
 
We appreciate your patience until then.      
Regards, 
Gowtham 



DA DavidBS October 1, 2019 06:41 PM UTC

Thank you Gowtham, I'll be in hold.

Kind regards,
David


GG Gowtham Gopalsamy Syncfusion Team October 2, 2019 11:57 AM UTC

Hi David, 
 
Thanks for using Syncfusion controls. 
 
As we promised to provide the more details on 04th October,2019.  
 
We appreciate your patience until then.      
Regards, 
Gowtham 



GG Gowtham Gopalsamy Syncfusion Team October 4, 2019 01:30 PM UTC

Hi David,      
      
Thanks for your patience. 
 
We have analysed your query to call the stored procedure in database and we couldn’t able to reproduce the issue from our end.We have attached the tested sample. Please modified the sample based on your requirement . It will help us to investigate further details and provide the earlier prompt solution.     
 
Please refer the below attached sample link, 
 
 
Please let us know if you require further other assistance from us.   
 
Regards,          
Gowtham          
 



DA DavidBS October 5, 2019 11:10 AM UTC

Hi Gowtham,

You send me an OLEDB code and its not what I need or asked...
I can utilize my remote SQL database using OLEDB or even ADO.

My focus is using LINQ to SQL as recommended in Syncfusion documentation AND access an Stored Procedure there, to get data into my SFDataGrid control.

Can I create a manual access to SQL, fulfill a list with records and bind it for SFDataGrid?  SURE!

But if I could directly fulfill SFDataGrid using the DBML chart which allow us to point a Stores Procedure as a Method, it would be better and more efficient.
That's the problem!




GG Gowtham Gopalsamy Syncfusion Team October 7, 2019 04:42 PM UTC

Hi David, 
 
Thanks for your patience. 
 
We have analyzed your query to call the  stored procedure and bind the result set into SfDataGrid and we have prepared sample for calling stored procedure from SQL database. Please modified the sample as per your requirement and  It will help us to investigate further details and provide the earlier prompt solution.   

 
Please refer the below code snippet, 
 
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click 
                Dim constr As String = "//Input your database connection string here" 
                Using con As New MySqlConnection(constr) 
                                Using cmd As New MySqlCommand("GetStudentInfo", con) 
                                                cmd.CommandType = CommandType.StoredProcedure 
                                                cmd.Parameters.AddWithValue("@ids", 1) 
                                                Using sda As New MySqlDataAdapter(cmd) 
                                                                Dim dt As New DataTable() 
                                                                sda.Fill(dt) 
                                                                sfDataGrid1.DataSource = dt 
                                                End Using 
                                End Using 
                End Using 
End Sub 
 
 
Please refer the below sample link, 
 
 
Please refer the below database link, 
 
 
Please let us know if you require further other assistance from us.       
 
Regards, 
Gowtham 



DA DavidBS October 8, 2019 12:32 PM UTC

Hi Gowhan, 

Thank you very much for your response.

Anyway, I found another simpler way to perform this, once we have a StoredProcedure as a Method in DBML:

Set DB as new <MyDataContext>
SFGrid.Datasource = DB.<StoredProcedureName>.ToList

It's really effective in load exactly what the SP contains as code.

Kindest regards,
David





FP Farjana Parveen Ayubb Syncfusion Team October 9, 2019 06:50 AM UTC

Hi David, 
 
Thanks for the update. 
 
We are glad to know that the reported problem has been resolved at your end. Please let us know if you have any further queries on this. We are happy to help you. 
 
Regards, 
Farjana Parveen A 


Loader.
Up arrow icon