我在这做错了什么？ C＃SQL读取功能 [英] What am I doing wrong here? C# SQL Read function

问题描述

嘿伙计们，我正在尝试从数据库中读取数据，给出查询。查询有效，我已插入断点以确保它正在搜索正确的数据，它是。它应该提供结果。这有什么不对？谢谢!!

Hey guys, I am trying to read data from a database, given a query. The query works, and I have inserted breakpoints to make sure that it is searching the correct data, and it is. It should be providing results. What is wrong here? Thanks!!

SqlConnection cntwo = new SqlConnection(global::_MyApp.Properties.Settings.Default.StallHoldersConnectionString);
            cntwo.open();
                SqlCommand comm = new SqlCommand("SELECT * FROM StallHolders WHERE stallID = '"+ textBox1.Text +"';", cntwo);
               

                SqlDataReader reader = comm.ExecuteReader();
                List<string> str = new List<string>();
                
                string stallholdername = "";
                string stallholderlastname = "";

                while (reader.Read())
                {
                    stallholdername = reader.GetValue(1).ToString();
                    stallholderlastname =reader.GetValue(2).ToString();
                }
                reader.Close();

推荐答案

两件事。首先，不要使用字符串连接来构建SQL查询，尤其是使用直接输入的TextBox值。你只是在乞求SQL注入攻击。改为使用参数化查询。



其次，你的主键是整数还是其他数字？你的SQL查询说它是一个字符串。如果它是一个数值，则必须将TextBox值解析为整数，然后将其传递给SQL查询中的参数。



您也不需要查询结尾处的分号，除非您计划在一个批处理中执行多个语句。

Two things. First, do NOT use string concatenation to build your SQL query, especially with a directly entered TextBox value. You're just begging for an SQL Injection attack. Use parameterized queries instead.

Second, is your primary key an Integer or other numeric? You SQL query is saying that it's a string. If it's a numeric value, you have to parse the TextBox value into an integer and then pass that to the parameter in your SQL query.

You also don't need the semi-colon on the end of the query, unless you plan on executing multiple statements in one batch.

使用SqlDataReader，您应该使用方法来获取正确的SQL数据类型，即GetSqlString而不是GetValue 。同样在注释中，您获取值但不添加到列表中，因此将只获得一行结果。

您还应该将文本框中的值解析为Int32并将其作为参数添加到查询中避免错误和注入攻击。

With SqlDataReader you should use methods to get correct SQL data types i.e. GetSqlString instead of GetValue. Also as in comment, you are getting the values but not adding to a list so will get only one row of results.
You should also parse value from textbox as Int32 and add as a parameters to the query to avoid errors and injection attacks.

// I agree with above solutions that you should not use inline query, see below code for proper use of DataReader, if your query is resulting then this code block should work
    public void SimpleRead()
    {
        // declare the SqlDataReader, which is used in
        // both the try block and the finally block
        SqlDataReader rdr = null;

        // create a connection object
        SqlConnection conn = new SqlConnection("Data Source=(your server);Initial Catalog=yourdatabse;Integrated Security=SSPI");

        // create a command object
        SqlCommand cmd = new SqlCommand("select * from StallHolders WHERE stallID='" + txtbox1.Text + "'", conn);

        try
        {
            // open the connection
            conn.Open();

            // 1. get an instance of the SqlDataReader
            rdr = cmd.ExecuteReader();

            // 2. print necessary columns of each   record
            while (rdr.Read())
            {
                // get the results of each column
                string stallholdername = (string)rdr["stallholdername"];
                string stallholderlastname = (string)rdr["stallholderlastname"];
            }
        }
        finally
        {
            // 3. close the reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

这篇关于我在这做错了什么？ C＃SQL读取功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！