problem with system stored procedure in Linq

[vineetbindal]

Hi all,

I have to use some of the system stored procedures in link but when i click on system stored procedure in my database explorer. it does not display me the list of system stored procedure?? cant we use system stored procedure in link?

can someone suggest me a solution

Regards,
Vineet

[Frinavale]

Have you seen this blog on using stored procedures in linq?

[vineetbindal]

The problem is not that "i can not use stored procedures". I can use userdefined stored procedure. the problem is when i go to Database Explorer in my Visual Studio and click on Stored Procedure. It does'nt shows me the list of system stored proc. It just shows me the list of User Defined Stored Proc.

I am not able to understant why? so for example if i want to use sp_helpserver(a system defined stored procedure which gives you a list of Linked remote Objects(Remote serveres connected to your server.)) it does not shows me this stored procedure in the list anywhere?

Why?

[nirosoful]

You can call system stored procedures by using _dataContext.Ex ecuteQuery<Type >(string query, params object[] parameters) function. Just set the query to be the system stored proc you want. Type is the type returned by the query.
The problem is that you have to create the return type yourself, and the names of the properties of this object must be the same as the names of the columns in the result of the query in order for linq to map the result to the return type.

ex:

class ServerDetails
{  public string name;
   public string network_name;
   public string status;
}

using (DataContext dc = new DataContext(cs))
{
   List<ServerDetails> list = dc.ExecuteQuery<ServerDetails>("sp_helpserver").ToList();
}

[vineetbindal]

Tnx a lot, it was really helpful

[pavarotti86]

Hi

Im also trying to use system stored procedures but i get the error "The data source for GridView with id 'GridView1' did not have any properties or attributes from which to generate columns. Ensure that your data source has content." i am trying to use the "sp_databas es" to retrieve the databases on a server. im i supposed to specify the server and if so where/how??

Thanks!

[Frinavale]

What have you done to get that error?

You have to specify a data source for the GridView to display. Once you've specified the data source, you need to call the databind method to bind the GridView to the data in order to display the data and work with it. I would recommend specifying the data source and binding it in the PreRender event in order to avoid problems with editing etc.

-Frinny

[pavarotti86]

this is the code i used:

 Using dc As New northwindDataContext
            GridView1.DataSource = dc.ExecuteQuery(Of ServerDetails)("sp_databases").ToList()
            GridView1.DataBind()
  End Using

the error comes up when i try to bind the data to my grid. looks like it is because my datasource is empty. What is it that i am doing wrong? My code is heavily based on the reply by nirosoful but in VB.net

[Frinavale]

I'm not entirely sure what you're trying to do in that code.

Check out this tutorial on Link to SQL: Retrieving Data Using Stored Procedures. I think it will clarify a few things...


-Frinny

[pavarotti86]

that was my first stop when i started using stored procedures. the thing is you can't access system stored procs like that. if you execute the procedure "sp_databas es" it will give you all the databases on a server, the size of each and the remarks if any.