LINQ to SQL: using Stored Procedure

[DavidBS]

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.

[Gowtham Gopalsamy]

Hi David , 

 

Thanks for using Syncfusion controls. 

 

We are analyzing your reported issue. We will update more details on 04^th October,2019.  

 

We appreciate your patience until then.      

Regards, 

Gowtham 

[DavidBS]

Thank you Gowtham, I'll be in hold.

Kind regards,

David

[Gowtham Gopalsamy]

Hi David, 

 

Thanks for using Syncfusion controls. 

 

As we promised to provide the more details on 04^th October,2019.  

 

We appreciate your patience until then.      

Regards, 

Gowtham 

[Gowtham Gopalsamy]

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, 

 

Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/vb1304910493  

 

Please let us know if you require further other assistance from us.   

 

Regards,          

Gowtham          

 

[DavidBS]

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!

[Gowtham Gopalsamy]

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, 

 

Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/VB-18242607  

 

Please refer the below database link, 

 

Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/MYSP625875924  

 

Please let us know if you require further other assistance from us.       

 

Regards, 

Gowtham 

[DavidBS]

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

[Farjana Parveen Ayubb]

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