Using C# to connect and insert to SQL Server 2012

Question

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);
    }
}

catch is for fixing errors. MessageBox.Show does not fix errors so your code is wrong. — Dour High Arch
– Dour High Arch, Commented Jan 24, 2014 at 17:09
I do not believe there is anything wrong with doing your own research and making your code SAFE. Plus your try statement is commented out. — websch01ar
– websch01ar, Commented Jan 24, 2014 at 17:09
You really should get the dev environment to validate the code you have to the point it can even compile-- anything else is wasting the resources of SO. Even the dump syntax highlighter here makes it clear most of the code has been commented out. — Hogan
– Hogan, Commented Jan 24, 2014 at 17:10
About those red lines. That's Visual Studio telling you there is a problem. A good habit to get into is to solve those problems as soon as you notice them. In other words, build early, build often, and when in doubt, build. — Dan Bracuk
– Dan Bracuk, Commented Jan 24, 2014 at 17:24

Mike Christensen · Accepted Answer · 2014-01-24 17:37:24Z

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.

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.
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.
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.
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);
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.
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.
You're calling varname1.Close(); but you've commented out the declaration of varname1, which will result in a compiler error.

Hope this helps!

Jake Rote · Accepted Answer · 2014-01-24 17:17:40Z

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);
    }
}

Collectives™ on Stack Overflow

Using C# to connect and insert to SQL Server 2012

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related