使用C＃进行连接并插入到SQL Server 2012 [英] Using C# to connect and insert to SQL Server 2012

问题描述

我工作的一些code，试图让我的阵列是由用户输入连接和发送到SQL Server 2012，我被告知要使用所有这些命令来连接到数据库。

我的一个问题是，我一直在寻找通过堆栈溢出，每个人都建议使用而不是将避免SQL注入的参数，但是这是我的课，我们只有2个星期到C＃，所以我不认为他会喜欢它，如果我使用的参数。

我觉得我尝试捕捉是错误的，上半部分用红色填充线，你如何使用INSERT命令for循环？

 保护无效btnDisplay_Click（对象发件人，EventArgs的发送）
{
    //尝试
    // {
      // System.Data.SqlClient.SqlConnection varname1 =新System.Data.SqlClient.SqlConnection（）;
      // varname1 =服务器= localhost的;数据库= Lab1内; Trusted_connection = YES;
      // varname1.Open（）;
      // System.Data.SqlClient.SqlCommand CMD =新System.Data.SqlClient.SqlCommand（）;
      // cmd.Connection =康恩;
      // cmd.CommandText =删除从学生;
       // cmd.ExecuteNonQuery（）;
   //
    字符串SQL = NULL;
     的for（int i = 0; C1的＆GT; =我;我++）
     {
         SQL + =INSERT INTO学生VALUES（+ StudentId +姓名+地址）;
     }
    varname1.Close（）;
   //}
    赶上（SQLEXCEPTION EX）
    {
        MessageBox.Show（数据库失败+ ex.Message）;
    }
}
 

解决方案

那么，还有的不少的这个code问题。这可能是最花一个小时就可以左右，然后发布你不能找出任何具体问题。让我给你，尽管几个简单的指针。

1. 您有一个赶上（）块，但在匹配尝试块被注释掉。这将导致一个编译错误。它看起来像你只是调试一些东西，所以没什么大不了的。但是，它通常是明智的，发布的实际的code你想运行。




2. 您正在初始化字符串空，但你在串联到最后。这将导致运行时错误。你应该初始化字符串的String.Empty 来代替。此外，考虑在的StringBuilder 类，如果你正在做大量的字符串连接，因为它的速度更快。




3. 您是（理论上）建立一个SQL字符串，但从未实际运行它的任何地方。你也不值返回给任何可能运行它。




4. 您插入语句甚至不是有效的。您不必在INSERT语句匹配的结束），和你有一个无赖）你的变量之后，这将导致一个编译错误。你也刚醪所有的变量一起，他们之间没有引号或逗号。您可能希望喜欢的东西更多：

   SQL + =的String.Format（INSERT INTO学生VALUES（'{0}，{1}，{2}'）;，StudentId，姓名，地址）;




5. 使用参数化查询。总是。谁在乎你的老师说的话。如果不这样做，最起码，检查单引号字符串第一，因为这些将prematurely结束串搞砸了你的SQL语句。




6. 您循环似乎并没有太大的意义。什么是计数器1 ？它有什么价值？即使它被设置为正值，你正在做的是一遍又一遍地建立相同的SQL字符串再次自循环中的值不会更改。目前尚不清楚你想在这里做什么。




7. 您正在呼叫 varname1.Close（）; 但你已经注释掉 varname1 ，这将导致一个编译器错误。




希望这有助于！

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

解决方案

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!

这篇关于使用C＃进行连接并插入到SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！