在C#编码中使用来自command.Text方法的数据填充datagridview [英] filling datagridview with data from command.Text method in C# coding

问题描述

我试图用在我的编码中从command.Text方法检索的数据填充datagridview控件(dgvParameters).我从用户选择的数据库以及用户也选择的过程名称中获取数据.从数据库名称和过程名称中，我需要检索所有参数名称，参数类型和参数类型长度，并填充datagridview控件.这是我的代码，试图做到这一点...

I am trying to populate a datagridview control (dgvParameters) with data retrieved from the command.Text method in my coding. I get the data from a database that the user selects aswell as the procedure name that the user also selects. From the database name and procedure name, i need to retrieve the all the parameter names, parameter types and parameter type lengths and populate the datagridview control. Here is my code that tries to do just that...

using (SqlConnection sqlConx_2 = new SqlConnection(conxString_2))
            {
                sqlConx_2.Open();

                SqlCommand sqlcom = new SqlCommand();
                sqlcom.Connection = sqlConx_2;
                sqlcom.CommandText = "use " + dbName + " select " +
                                    "p.name As [ParameterName]," +
                                    "TYPE_NAME(P.user_type_id) As [ParameterDataType]," +
                                    "P.max_length As [ParameterMaxBytes]" +
                                    "from sys.objects AS SO" +
                                    "inner join sys.parameters As P" +
                                    "On SO.object_id = P.object_id" +
                                    "Where SO.object_id IN (Select OBJECT_ID " +
                                    "from sys.objects" +
                                    "where type IN ('P','FN')) AND SO.name = '" + storedproc + "'" +
                                    "Order by SO.name, P.parameter_id";
                DataTable lDataTable = null;
                lDataTable = new DataTable();
                sqlcom.CommandTimeout = 1200;
                SqlDataAdapter lSqlAdapter = new SqlDataAdapter(sqlcom);
                lSqlAdapter.Fill(lDataTable);
                dgvParameters.DataSource = lDataTable;
            }

通过"dbName"，我已经检索了数据库名称以及带有过程名称的"storedproc". 我说的错误

"SO"附近的语法不正确.
关键字"IN"附近的语法不正确."

请帮忙...我不确定我在做什么错...

在此先感谢

by ''dbName'' i have already retrieved the database name aswell as with ''storedproc'' with the procedure name.
The error i get says

"Incorrect syntax near ''SO''.
Incorrect syntax near the keyword ''IN''."

please help... I''m not sure what i am doing wrong...

Thanks in advance

推荐答案

OK，使用字符串连接来构建SQL语句会导致这样的错误.您正在做的事情很麻烦调试，因为您已经包含了各种字符串限定符和逗号，这使得阅读起来非常混乱.尽可能地将其最小化，并确保在引号内插入空格和逗号，并确保使用正确的引号，并在SQL关键字上使用大写形式以使内容更易于阅读.您错过了其中的几个.

我可以看到您的错误以及更多错误.例如，如果您完成的SQL语句正常工作，它将看起来像这样:

OK, using string concatentation to build SQL statements leads to errors like this. What you''re doing is a pain in the ass to debug because you''ve included all kinds of string qualifiers and commas that makes it very confusing to read. Minimize it as much as possible and make sure you get the spaces and commas INSIDE the quotes correct, as well as using capitalization on SQL key words to make things a little easier to read. You missed a couple of those.

I can see your error and a bunch more. For instance, your finished SQL statement, had it worked would have looked like this:

use <dbname> select p.name As [ParameterName],TYPE_NAME(P.user_type_id) As ... from sys.objectswhere</dbname>

如果连接字符串指定了要使用的数据库，则不需要USE语句.

我更喜欢将SQL字符串放在数据库的存储过程中或应用程序资源的字符串中，而不是将它们放在代码中.它使代码整洁.请注意，引号内每行的末尾都有一个空格，除非最后一个字符是括号字符.

但是，请改写如下代码:

You don''t need the USE statement if you connection string specifies the database you want to use.

I prefer to put my SQL strings in stored procedures in the database or in strings in the apps resources instead of laying them out in the code. It keeps the code uncluttered. Note, there''s a space at the end of every line inside the quotes, unless the last character is a parenthesis character.

But, rewrite the line like this:

sqlcom.CommandText = String.Format(
    "USE {0};" +
    "SELECT p.name AS [ParameterName], " + 
        "TYPE_NAME(P.user_type_id) AS [ParameterDataType], " +
        "P.max_length AS [ParameterMaxBytes], " +
    "FROM sys.objects AS SO " +
        "INNER JOIN sys.parameters AS P " +
        "ON SO.object_id = P.object_id " +
    "WHERE SO.object_id IN (" +
        "SELECT OBJECT_ID " +
        "FROM sys.objects " +
        "WHERE type IN (''P'',''FN'')" +
    ") AND SO.name = ''{1}'' " +
    "ORDER BY SO.name, P.parameter_id", dbName, storedProc);

这篇关于在C#编码中使用来自command.Text方法的数据填充datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！