从SQL数据库中的表中选择字段

I have created the data source within Visual Studio, I am trying to access a field called Reference No_ by using the Brief Number which is stored as No_.

我在Visual Studio中创建了数据源,我试图通过使用存储为No_的Brief Number来访问名为Reference No_的字段。

argclean="AW02464";            
string connectionString = "Data Source=ERP-SERVER; Initial Catalog=RMS2015; Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
var query = "SELECT [Reference No_] FROM [RMS2015].[dbo].[RMS Live$Artwork Brief] WHERE [No_] = " + argclean + " ";

Above is my code that I have tried but I cant seem to get it to work

以上是我尝试过的代码,但似乎无法让它工作

1 个解决方案

#1

It's better to get in the habit of using params in your queries, such as:

养成在查询中使用params的习惯更好,例如:

var query = "SELECT [Reference No_] FROM [RMS2015].[dbo].[RMS Live$Artwork Brief] WHERE [No_] = @ArtworkNumber;

...and then:

new SqlParameter()
{
    ParameterName = "@ArtworkNumber",
    SqlDbType = SqlDbType.VarChar,
    Value = argclean
}

UPDATE

Here is a general purpose method I use to retreive a DataTable from a query:

这是我用来从查询中检索DataTable的通用方法:

public static DataTable ExecuteSQLReturnDataTable(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(YourConnStr))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        cmd.CommandTimeout = EXTENDED_TIMEOUT;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        cmd.Connection.Open();
        new SqlDataAdapter(cmd).Fill(ds);
        return ds.Tables[0];
    }
}

It can be called like so:

它可以像这样调用:

DataTable dtDeliveryPerformanceResults = 
    SQLDBHelper.ExecuteSQLReturnDataTable(
        PLATYPUS_STOREDPROC,
        CommandType.StoredProcedure,
        new SqlParameter()
        {
            ParameterName = "@Unit",
            SqlDbType = SqlDbType.VarChar,
            Value = unit
        },
        new SqlParameter()
        {
            ParameterName = "@BeginDate",
            SqlDbType = SqlDbType.DateTime,
            Value = _begDate
        },
        new SqlParameter()
        {
            ParameterName = "@EndDate",
            SqlDbType = SqlDbType.DateTime,
            Value = _endDate
        },
        new SqlParameter()
        {
            ParameterName = "@PoisonToeLength",
            SqlDbType = Convert.ToInt32(SqlDbType.Int),
            Value = 42
        }
    );

The example shows calling a Stored Procedure (a recommended practice), but you can use it with "regular" or "plain vanilla" SQL queries, too.

该示例显示调用存储过程(建议的做法),但您也可以将其与“常规”或“普通的”SQL查询一起使用。