linq to sql

razza_b
razza_b used Ask the Experts™
on
Hi

I have a SP that our system uses for when a user logs in it retrieves their roles funtionality from many tables(user, roles, screens,security,temp) and populates a tree view.

But im wanting to now use LinqToSql to acheive the exact same result to populate the treeview, does anyone know by looking at this sp how to do this especially if a temp table is involved.

I know how to get data onto a treeview and i dont want to follow a basic example website that shows how to drop tables onto a designer, its more the linq part to replicate the sp.

See attached.

Thanks
user-login-sp.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
how about dropping the sp itself in the Linq orm and use it?

Author

Commented:
Hi, do you know how i would do that?

Author

Commented:
So could i still acheive doing what u said like what my sp does already? How would linq query the dropped on sp to orm?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

once sp is there, you can use the Context to call it:
ie:
http://msdn.microsoft.com/en-us/library/bb918119.aspx
or, if you prefer a more detailed an graphical tutorial with vb and c# sintax:
http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
Retired
Distinguished Expert 2017
Commented:
Hi razza_b;

You need to add the ORM to your project which maps the database tables and Stored Procedures to the code. Follow the following steps.

In Windows Explorer right click on the project name and select Add => New Item... when the New Item dialog box opens find and select "Linq to SQL Classes", then click on OK

When the ORM designer opens go to Server Explorer window and Find the database and open its tree node to expose tables and Stored Procedures. Open the Stored Procedure node and find the stored procedure you want to run, w4sp_getUserAccess and drag and drop onto the right panel of the designer.

Now for the left side panel. In the Toolbox window drag and drop a class object Double click Class1 in the class object and change its name to something meaningful.

Now right click anywhere in the class object and select Add => Property and give it a name of one of the columns that will be returned from the SP. Do this for all the columns that the SP will return.

Now open the Properties window and then click on each property in the new class and change the data type to the type being returned by selecting the Type property, do this for each property.

Now right click on the Stored Procedure that is in the right panel and select Properties.

In the Properties window select Return Type and from the combo box select the class you just finished creating in the designer. When the warning dialog box opens just click OK.

Now in the code first create and instance of the DataContext. If you left the default name when you added the Linq to SQL Classes the name of the DataContext is DataClasses1DataContext. Then execute the SP as Follows

// Create the DataContext
DataClasses1DataContext ctx = new DataClasses1DataContext();

// Execute the SP as follows: ClassName in the below line is the name of the class you created 
// in the ORM designer and the parameter passed in to w4sp_getUserAccess is the string value
// expected by the SP.
List<ClassName> queryResults = ctx.w4sp_getUserAccess("User As Expecting by SP").ToList();

Open in new window


The ToList() converts the result set to a List of objects.

Author

Commented:
Thanks for feedback guys.

Fernando, this looks great. So if my SP that i have worked with in existing system and always returned the values....@ScreenID, @ScreenName, @Description, @ScreenPath, @Category, @ScreenOrder, @ParentID, @ReadOnly

and populated my tree, how can i get the ctx query to to populate in same way? do i have to declare them in query?

I now have the code in my SL enabled WCF like...

[ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class GetRoles
    {
        [OperationContract]
        public List<UserRoles> getRoles(string username)
        {
            UserRolesForTreeDataContext context = new UserRolesForTreeDataContext();
            List<UserRoles> queryResults = context.w4sp_getUserAccess_AA(username).ToList();
            var result = from ? in queryResults
                         select ?;

            return result.ToList();
        }
    }


Thanks

Author

Commented:
or would i just do this...

[OperationContract]
        public List<UserRoles> getRoles(string username)
        {
            UserRolesForTreeDataContext context = new UserRolesForTreeDataContext();
            List<UserRoles> queryResults = context.w4sp_getUserAccess_AA(username).ToList();
            var result = queryResults;
           
            return result.ToList();
        }

since the class used with sp in orm knows what the properties are?

Author

Commented:
I get the error "The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type" when debugging at the line..

List<UserRoles> queryResults = context.w4sp_getUserAccess_AA(username).ToList();

username has a value.

Author

Commented:
i just set the nullable properties to true for the fields that can be true.

so when debugging the ctx query has all the data but then thorws a wobbly and i get this..

{System.Runtime.Serialization.SerializationException: ValueType 'System.Int32' cannot be null.
   at ReadUserRolesFromXml(XmlReaderDelegator , XmlObjectSerializerReadContext , XmlDictionaryString[] , XmlDictionaryString[] )
   at System.Runtime.Serialization.ClassDataContract.ReadXmlValue(XmlReaderDelegator xmlReader, XmlObjectSerializerReadContext context)
   at System.Runtime.Serialization.XmlObjectSerializerReadContext.ReadDataContractValue(DataContract dataContract, XmlReaderDelegator reader)
   at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator reader, String name, String ns, DataContract& dataContract)
   at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator xmlReader, Int32 id, RuntimeTypeHandle declaredTypeHandle, String name, String ns)
   at ReadArrayOfUserRolesFromXml(XmlReaderDelegator , XmlObjectSerializerReadContext , XmlDictionaryString , XmlDictionaryString , CollectionDataContract )
   at System.Runtime.Serialization.CollectionDataContract.ReadXmlValue(XmlReaderDelegator xmlReader, XmlObjectSerializerReadContext context)
   at System.Runtime.Serialization.XmlObjectSerializerReadContext.ReadDataContractValue(DataContract dataContract, XmlReaderDelegator reader)
   at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator reader, String name, String ns, DataContract& dataContract)
   at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator xmlReader, Type declaredType, DataContract dataContract, String name, String ns)
   at System.Runtime.Serialization.DataContractSerializer.InternalReadObject(XmlReaderDelegator xmlReader, Boolean verifyObjectName)
   at System.Runtime.Serialization.XmlObjectSerializer.ReadObjectHandleExceptions(XmlReaderDelegator reader, Boolean verifyObjectName)
   at System.Runtime.Serialization.DataContractSerializer.ReadObject(XmlDictionaryReader reader, Boolean verifyObjectName)
   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameterPart(XmlDictionaryReader reader, PartInfo part, Boolean isRequest)}
'System.Int32' is an integer, it is diffcult to believe it is username. Check if you forgot a parameter or an initialization value in table. Try also call the sp from sql server passing same parameters you're using. Does it work?
By the way: values are serialized. That is : you must provide a default value that can be converted to Int from string and vice versa for that numeric field

Author

Commented:
back in the mainpage.cs i changed the e.result to e.result.ToList()

and i no longer get the error, but now my tree has all parent nodes(no children), plus each node says..

SLLOB.GetUserRolesForTree.UserRoles x 130

Author

Commented:
In the old system that populated the asp.net treeview i used same sp but done this..
DataSet ds;
            menu mnu = new menu();
            ds = mnu.getDalUserAccess(user);

            ds.DataSetName = "Menus";
            ds.Tables[0].TableName = "Menu";
            DataRelation relation = new DataRelation("ParentChild",ds.Tables["Menu"].Columns["ScreenID"],ds.Tables["Menu"].Columns["ParentID"], true);

            relation.Nested = true;
            ds.Relations.Add(relation);

            xmlDataSource1.Data = ds.GetXml();
            xmlDataSource1.Data = xmlDataSource1.Data.Replace("\r\n", "");

then bound the xmldatasource to the treeview.

so i somehow need to manage to get the data into my treeview...
Category
   screename
   screename
Category
   screename
   screename

rather than..
SLLOB.GetUserRolesForTree.UserRoles x 130

Author

Commented:
any idea how i can get the data to display properly if its managing to bring back the all the content to the treeview item source?

Author

Commented:
I'm now getting back every(130) screennames onto treeview but should be categorized underneath their category, but they all appear as parent node and no child node.

There should be 11 categories and 119 screens as children.

i have this on treeview xaml just now but just cant get the proper display dont see what im missing...

<controls:TreeView x:Name="treeView1" DisplayMemberPath="ParentID" SelectedValuePath="ParentID" Height="598" VerticalAlignment="Top" Margin="0" Background="#FFF6E9BB" HorizontalAlignment="Left" Width="190">
                <controls:TreeView.ItemTemplate>
                    <common:HierarchicalDataTemplate
                        ItemsSource="{Binding}">
                        <StackPanel>
                            <TextBlock Text="{Binding Path=Screenname}" />
                        </StackPanel>
                    </common:HierarchicalDataTemplate>
                </controls:TreeView.ItemTemplate>
            </controls:TreeView>

see attached snippet of db output..
db-output-snippet.docx
seems as if you should - once you got the result from sp, query it again via Linq to obtain the proper infos, getting out the relations you do not have as first result.

Author

Commented:
Nope im totally lost on this Linq and how to display the way i want it:(

Author

Commented:
I have done the relationship of the data i need before sending back to client, but data still seems to appear the same...all as parent nodes, so i still dont know what the issue is. I have tried with the hierarchical data of xaml but still no luck.  

Author

Commented:
It was used to get me half way to what i had to acheive the rest i had to use linq queryable to get data out of context before sending back to client.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial