C# LINQ How to get a data source from a db?

Question

I am trying use LINQ in a C# program to get information from a database. I have found a lot of examples showing basic to advanced queries, but I can't find anything to get a basic database connection. See basic LINQ example below:

var adultNames = from person in people
                 where person.Age >= 18
                 select person.Name; 

I just can't figure out how to get to a specific database table and return information out of it. I want to be able to connect to a db table and return some information out of it. Thanks!

Solution 1

Theres a very simple way to connect to a database although I suggest you read the app config afterwards and other parts of your project to see how it works.

In the menu strip go to Data -> Add New Data Source. From here pick database then entity data model. Follow the rest of the steps to create a datasource for your project.

In your program you can create an object of the datasource by using:

WhatYouCalledTheDataSource datasourceObject = new WhatYouCalledTheDataSource();

You can then make queries on this object by changing your first line of the query to:

var adultNames = from person in datasourceObject.people 

to get the data you want, once you have the query you can place the results into a list like:

List<string> queryResults = new List<string>();

queryResults.AddRange(adultNames);

bearing in mind that I basing on the thought that name would be a string. You can also create lists based on tables like

var adultNames = from person ...
                 ...
                 select person;
List<people> queryResults = new List<people>();
queryResults.AddRange(adultNames);

Solution 2

It sounds like you have been looking at examples of LINQ, which allows you to query from a data set. What you need in addition is called an Object Relational Mapper (ORM). Look at LINQ to SQL or The Entity Framework (newer) to get starts with this. This layer will introspect a database and create a lot of infrastructure to pull data out for you based on the data model.

Solution 3

If you're using Linq to Sql, then this may help: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
entity framework: http://www.codeguru.com/csharp/csharp/net30/article.php/c15489

Solution 4

There are a number of ways to retrieve data from a database. ADO.NET is the general technology. Your specific options are:

System.Data/SqlClient/ODP

You can use the standard query system in ADO.NET to get data from any database that uses ODBC, or use native data providers such as SqlClient or ODP. The SqlClient namespace may be used to get data from SQL Server. The Oracle Data Provider (ODP) is used with Oracle.

You have to write the queries/stored procedures yourself using "raw" ADO.NET.

Here is an excellent listing of ADO.NET example code: http://msdn.microsoft.com/en-us/library/dw70f090.aspx

Here is a link to Oracle's ODP:
http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

LINQ to SQL

LINQ to SQL is a LINQ provider specifically for the SQL Server database. It is still supported, but is superseded by Entity Framework.

More detail here: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Entity Framework

Entity Framework works natively with SQL Server and can work with Oracle using third-party or open-source data providers.

Entity Framework 4/4.1 will allow you to use an Entity Data Model (EDM) as an abstraction of your database. The EDM will surface a set of objects with which you can interact, and a Context object is used to retrieve/commit data to the database. The CRUD queries are dynamically generated from your LINQ syntax, so you don't have to write T-SQL or PL-SQL queries yourself in most cases.

Here is a basic example of Entity Framework:
http://www.codeproject.com/KB/database/sample_entity_framework.aspx

Solution 5

if you have a database table people then you can use LINQ-to-SQL to return information from it, first you need to reference the following two assemblies.

using System.Data.Linq;
using System.Data.Linq.Mapping;

define the following entity like this:

[table]
class people
{
    [column]
    public int Age { get; set; };
    [column] 
    public string Name { get; set; };
}

then you can write tour query against database

Datacontext db = new DataContext("database Connection String");
var adultNames = from person in db.GetTable<people>()
             where person.Age >= 18
             select person.Name; 

View more solutions