running sql scripts inside visual studio 2012 express edition [Topic: C# interpreted queries]

Question

I am trying to run a script that contains the following code:

create table Customer
(
   ID int not null primary key,
   Name varchar(30)
)
insert Customer values (1, 'Tom')
insert Customer values (2, 'Dick')
insert Customer values (3, 'Harry')
insert Customer values (4, 'Mary')
insert Customer values (5, 'Jay')

And integrate it into this C# code

using System;
using System.Linq;
using System.Data.Linq; // in System.Data.Linq.dll   
using System.Data.Linq.Mapping;
[Table]
public class Customer
{
    [Column(IsPrimaryKey = true)]
    public int ID;
    [Column]
    public string Name;
}
class Test
{
    static void Main()
    {
        DataContext dataContext = new DataContext("connection string");
        Table<Customer> customers = dataContext.GetTable<Customer>();
        IQueryable<string> query = from c in customers
                                   where c.Name.Contains("a")
                                   orderby c.Name.Length
                                   select c.Name.ToUpper();
        foreach (string name in query) Console.WriteLine(name);
    }
}

So far I only managed to add reference to the System.Data.Linq dll.

I tried googling but i found no answers. This was found in the C# in a nutshell book, it provided no details about this topic in this book on how to run it on visual studio 2012 express. (sql file alongside the C# file).

score 0 · Answer 1 · edited May 23 '17 at 12:28

The first step is that you need a SQL server to connect to. If you don't have one provided for you by your workplace, or this is a hobby/school project, download SQL Server Express Edition if you haven't already done so.

Once you've gotten that installed, I have the following recommendations:

Add the Adventureworks test database. Quick article on how to do that here: http://technet.microsoft.com/en-us/library/ms144235%28v=sql.90%29.aspx. This database will take the guesswork out of setting things up for someone who doesn't know where to start.
Connect to SQL Server either using Visual Studio (SQL->Transact Sql Editor->New Query) or Sql Server Management Tools (click the New Query button in the toolbar). You'll want to connect to localhost.

Ok, now you've got a query editor window to work with. Type "USE AdventureWorks" and hit F5 or click the Execute button. Alternatively you can select AdventureWorks from the dropdown box on the lefthand side that lists all available databases.

Ok, now you can create and populate a table.

You can do that right there in the query editor if you want.

The code would look like this:

USE AdventureWorks
GO

CREATE TABLE dbo.Customer
(
    [ID] int NOT NULL Primary Key,
    [Name] varchar(30)
)

INSERT INTO dbo.Customer
(
    [ID],
    [Name] 
)
VALUES 
    1,
    'Tom'

INSERT INTO dbo.Customer
(
    [ID],
    [Name] 
)
VALUES 
    2,
    'Dick'

INSERT INTO dbo.Customer
(
    [ID],
    [Name] 
)
VALUES 
    3,
    'Harry'

INSERT INTO dbo.Customer
(
    [ID],
    [Name] 
)
VALUES 
    4,
    'Mary'

INSERT INTO dbo.Customer
(
    [ID],
    [Name] 
)
VALUES 
    5,
    'Jay'

Hit F5 or click the Execute button. Now you've got data in your database to work with.

You'll need to set up a connection to get the rest working (You need to replace the DataContext line with something that works).

You can either do this by making a connection directly to the .mdf database file as some of these MSDN examples do: http://msdn.microsoft.com/en-us/library/vstudio/bb399349%28v=vs.100%29.aspx

Or you can connect to the database as if it were on a server like this question asks about: Connect to SQL Server 2012 Database with C# (Visual Studio 2012)

running sql scripts inside visual studio 2012 express edition [Topic: C# interpreted queries]

1 Answers1