Syntax of SQL Server database connection string in c#

Question

I am beginner at SQL and thank you for your attention. I've created a database (by using "Add new Item" from "Project" menu and adding a "Service Based Database") in Visual Studio 2015 and now I want to connect to it and read or write data on it.

But I don't know how to connect to it by code.

I use the string showed in the connection string when I click on the database in server explorer.

That is here:

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="c:\users\soroush\documents\visual studio 2015\Projects\databasetest2\databasetest2\Database1.mdf";Integrated Security=True    

But as you know, it cannot be used when I copy and paste it to a string thah can be used in new sqlConnection(connection string), because this string has '\' or ' " '

What's the right string for me to connect to this local database?

Now this is my code but it is not useful:

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = c:\users\soroush\documents\visual studio 2015\Projects\databasetest2\databasetest2\Database1.mdf; Integrated Security = True");
    con.Open();

    string t=@"INSERT INTO Table (Id,name) Values (34, 'John')";

    SqlCommand cmd = new SqlCommand(t, con);
    cmd.ExecuteNonQuery();

    con.Close();
}

private void button2_Click(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = c:\users\soroush\documents\visual studio 2015\Projects\databasetest2\databasetest2\Database1.mdf; Integrated Security = True"))
    {
        con.Open();
        string t = "SELECT * From Table";

        SqlCommand cmd = new SqlCommand(t, con);

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            MessageBox.Show(reader["Id"].ToString() + reader["name"].ToString());
        }

        con.Close();
    }
}

Thank you for your help

Update: I get another errors in writing and reading table

I think I've connected to my database after using your help. and now I have another error in reading the table. this error points to

SqlDataReader reader = cmd.ExecuteReader();

in my code and says:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Incorrect syntax near the keyword 'Table'.

and an error in writing on table points to

cmd.ExecuteNonQuery();

in my code:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Incorrect syntax near the keyword 'Table'.

My database has one table named Table that contains two columns: Id(int) and name(nchar10)

Answer 1

The code you're using to connect to your SQL database is really old school. We just don't do it like that any more.

So - what can we do instead? Let's use a nice library called Dapper which makes 'talking' to a SQL Server really easy, simple and safer.

First, install the package Dapper from Nuget:

Create a POCO which will represent the data that is returned from the DB.

public class Foo
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Now update the form code as follows:

private const string _connectionString = @"Data Source = (LocalDB) <snipped..>";



private void button1_Click(object sender, EventArgs e)
{
    string query = "INSERT INTO Table (Id,name) Values (34, 'John')";
    int rowsInserted;
    using (var db = new SqlConnection(_connectionString))
    {
        rowsInserted = db.Execute(query);
    }

    if (rowsInserted != 1)
    {
        // Log/Handle the fact that you failed to insert 1 record;
    }
}

private void button2_Click(object sender, EventArgs e)
{
    IList<Foo> foos;

    using (var db = new SqlConnection(_connectionString))
    {
        const string query = "SELECT * FROM Table";
         // This will always return a list. It's empty or has items in it.
        foos = db.Query<Foo>(query).ToList();
    }

    foreach(var foo in foos)
    {
        MessageBox.Show($"{foo.Id} - {foo.Name}");
    }
}

Is that much cleaner? Yep - I thought so.

Of course, I would never put database code behind a winform event but into a dedicated class, etc. But I guess you're just learning/playing around/experimenting :) :)

Also, I've not put error handling in there, to keep the example smallish.

Answer 2

Change:

string t = "SELECT * From Table";

to:

string t = "SELECT * From [Table]";

and:

string t=@"INSERT INTO Table (Id,name) Values (34, 'John')";

to:

string t=@"INSERT INTO [Table] (Id,name) Values (34, 'John')";

See https://stackoverflow.com/a/695590/34092 and https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql .

Answer 3

my problem has been solved at first for connection to database I typed an @ before connection string and deleted the quotes inside the string as @juergen d said in comments

at second for solving the error in writing and reading the table I typed [ and ] before and after the "Table" as @mjwills said

also @Pure.Krome explained a more professional way to improve the code

thank you every body