How to connect to SQL Server

Question

I am trying to connect to my database, I am beginner to the repository, dependency injection. I couldn't connect to the database.

How can I resolve this issue?

This is my code:

Controller:

public ActionResult Create(FormCollection collection)
{
    try
    {
        return RedirectToAction("Index");
    }
    catch
    {
        return View();
    }
}

Repository:

public UserMaster Add(UserMaster item)
{
    using (SqlConnection sqlCon = new SqlConnection(connectionstring))
    {
        sqlCon.Open();
        string query = "INSERT INTO Employee 
                        VALUES (@ID, @Name, @City, @Address)";

        for (int i = 0; i <= 100; i++)
        {
            SqlCommand sqlcmd = new SqlCommand(query, sqlCon);

            sqlcmd.Parameters.AddWithValue(ID = i, Name = "newride", City = "newride", Address = "USA");
        }
    }

    return item;
}

Answer 1

The connection is made using the connection string and the SqlConnection class - that seems to be fine in your code.

BUT: the way you're trying to insert values is all wrong - you need to use something like this:

using (SqlConnection sqlCon = new SqlConnection(connectionstring))
{
    sqlCon.Open();
    // SPECIFY the column you insert into!
    // Without the @ "query" is not recognized as a multiline string... that's why the PO is getting that VALUES does not exists in the current context...
    string query = @"INSERT INTO Employee (ID, Name, City, Address)
                    VALUES (@ID, @Name, @City, @Address)";

    for (int i = 0; i <= 100; i++)
    {
        SqlCommand sqlcmd = new SqlCommand(query, sqlCon);

        // set the individual parameters, and AVOID "AddWithValue"
        sqlcmd.Parameters.Add("@ID", SqlDbType.Int).Value = i;
        sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = "newride";
        sqlcmd.Parameters.Add("@City", SqlDbType.VarChar, 100).Value = "newride";
        sqlcmd.Parameters.Add("@Address", SqlDbType.VarChar, 100).Value = "USA";

        // and then *EXECUTE* the SqlCommand to actually RUN the INSERT
        sqlcmd.ExecuteNonQuery();
    }
}