CODEWITHSUNDEEP
c#asp.netsqlsql-servervisual-studio-2010
Tim
Tim

Reputation: 331

Using C# to connect and insert to SQL Server 2012

I'm working on some code to try and get my array that's entered by the user to connect and send to SQL Server 2012. I've been told to use all of these commands to connect to the database.

One of my issues is that I've been looking through Stack Overflow and everyone suggests using parameters instead of concatenating to avoid SQL injection, but this is for my class and we are only 2 weeks into C# so I don't think he's going to like it if I use parameters.

I think my try catch is wrong, the top half is filled with red lines and how do you use the INSERT command with a for loop?

protected void btnDisplay_Click(object sender, EventArgs e)
{
    //try
    //{
      //  System.Data.SqlClient.SqlConnection varname1 = new System.Data.SqlClient.SqlConnection();
      //  varname1 = "server = LOCALHOST"; Database = Lab1; Trusted_connection = yes;
      //  varname1.Open();
      //  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
      //  cmd.Connection = conn;
      //  cmd.CommandText = "Delete From Student";
       // cmd.ExecuteNonQuery();
   //    
    string sql = null;
     for(int i=0; counter1 >= i; i++)
     {
         sql += "INSERT into Student VALUES(" + StudentId + Name + Address);
     }
    varname1.Close();
   //}
    catch (SqlException ex)
    {
        MessageBox.Show("Database failed" + ex.Message);
    }
}

Upvotes: 0

Views: 1201

Answers (2)

Mike Christensen
Mike Christensen

Reputation: 91638

So, there are quite a few problems with this code. It might be best to spend another hour on it or so, then post any specific questions you can't figure out. Let me give you a few quick pointers though.

  1. You have a catch() block, but the matching try block is commented out. This will result in a compiler error. It looks like you were just debugging some stuff, so no big deal. However, it's usually wise to post the actual code you're trying to run.

  2. You're initializing a string to null, but you're concatenating on to the end. This will result in a runtime error. You should initialize your string to String.Empty instead. Also, look into the StringBuilder class if you're doing large amounts of string concatenation, as it's much faster.

  3. You're (in theory) building a SQL string, but never actually running it anywhere. Nor do you return the value to anything that could run it.

  4. Your INSERT statement isn't even valid. You don't have a matching end ) in the INSERT statement, and you have a rogue ) after your variables, which will result in a compiler error. You also just mash all the variables together, without quotes or commas between them. You probably want something more like:

    sql += String.Format("INSERT into Student VALUES('{0}', '{1}', '{2}');", StudentId, Name, Address);

  5. Use parameterized queries. Always. Who cares what your teacher says. If you don't, at the very least, check the strings for apostrophes first, as these will screw up your SQL statement by prematurely ending the string.

  6. Your loop doesn't seem to make much sense. What is counter1? What value does it have? Even if it's set to a positive value, all you're doing is building the same SQL string over and over again since the values within the loop don't change. It's not clear what you're trying to do here.

  7. You're calling varname1.Close(); but you've commented out the declaration of varname1, which will result in a compiler error.

Hope this helps!

Upvotes: 6

Jake Rote
Jake Rote

Reputation: 2247

Is this what you are after. You may have to adapt some of it. Sorry if it doesent fully work dont have a debugger at the moment.

class Data {
    public int StudentId {get;set;}
    public string Name {get;set;}
    public string Address {get;set;}
}

protected void btnDisplay_Click(object sender, EventArgs e)
{
    var datas = new List<Data>();
    try
    {
        StringBuilder sql = new StringBuilder();
        foreach(data in datas)
        {
            sql.Append(String.Format("INSERT into Student VALUES({0},'{1}','{2}') ",data.UserId,data.Name,data.Address));
        }

        var sqlConnection = new SqlConnection(@"Data Source=LOCALHOST;Initial Catalog=Lab1;Trusted_Connection=True;");
        sqlConnection.Open();
        var command = new SqlCommand(sql.ToString(),sqlConnection);
        command..ExecuteNonQuery();
        sqlConnection.Close();

    }
    catch(SqlException ex){
        MessageBox.Show("Database failed" + ex.Message);
    }
}

Upvotes: 0

Related Questions