只需存储过程中的一列

I have this stored procedure in my SQL Server;

我在SQL服务器中有这个存储过程;

CREATE PROC GetChild
    @Child_ID int
AS
    SELECT * FROM Children WHERE Child_ID = @Child_ID

I am calling this stored procedure from C#.

我从c#调用这个存储过程。

I would like to know, if it is possible to call just one column from this table instead of the whole record from C#.?

我想知道，是否可以只调用该表中的一列，而不调用c#中的整个记录。

5 个解决方案

#1

Assuming you mean return one column, if this is what your stored procedure looks like then no. It will always return all columns back to the client.

假设您的意思是返回一列，如果这是存储过程的样子，则不是。它总是将所有列返回给客户端。

You can simply ignore the returned columns that you do not need. Or you can change the stored procedure to only return one column. But as is, it always returns all of them.

您可以简单地忽略返回的不需要的列。或者可以将存储过程更改为只返回一列。但实际上，它总是返回所有这些。

#2

You have only have three choices.

你只有三个选择。

Rewrite the Stored procedure to just return the columns you want.

重写存储过程以返回所需的列。

e.g. SELECT foo from Children Where Child_id = @Child_ID

例如，从Child_id = @Child_ID的子代中选择foo
Use a DataReader and just get the columns you want from that

使用DataReader并从中获取所需的列

Using a reader directly

直接使用一个读者

while (reader.Read())

而(reader.Read())
```
  `Console.WriteLine("{0}", reader.GetInt32(0));`
```
Using the Linq extension methods which allows you to filter and sort the results as well as getting just the columns you want.

使用Linq扩展方法，允许您过滤和排序结果，以及获得您想要的列。

var List = rdr.Cast<IDataRecord>() .Select(s => s.GetInt32(0)).ToList();

var List = rdr.Cast () .Select(s => . getint32 (0)).ToList();
Abandon the stored procedure and write Select statements against the table. See Pranay's answer

放弃存储过程，对表写入Select语句。看到Pranay的回答

#3

just write below query

写出下面的查询

select columnname from Children where Child_ID = @Child_ID

columnname- is name of the column you want to retrive

columnname—是要检索的列的名称

Code for you

代码给你

SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText ="select columnname from Children where Child_ID = @Child_ID";

 mySqlCommand .Parameters.Add("@Child_ID", SqlDbType.Int);
 mySqlCommand .Parameters["@Child_ID"].Value = idvalue;
 mySqlConnection.Open();

 SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);


while (mySqlDataReader.Read()){
  Console.WriteLine("mySqlDataReader[\" columnname\"] = " +
    mySqlDataReader["columnname"]);
}

mySqlDataReader.Close();
mySqlConnection.Close();

#4

Use a SqlDataReader for this:

为此使用SqlDataReader:

SqlConnection DbConn = new SqlConnection(YourConnStringHere);
SqlCommand ExecStoredProc = new SqlCommand();
ExecStoredProc.CommandType = CommandType.StoredProcedure;
ExecStoredProc.CommandText = "GetChild";
ExecStoredProc.Connection = DbConn;
ExecStoredProc.Parameters.AddWithValue("@ChildID", YourChildId);

using (DbConn)
{
    DbConn.Open();
    using (SqlDataReader sdr = ExecStoredProc.ExecuteReader())
    {
        while(sdr.Read())
            // reference your column name like this:
            // sdr.GetString(sdr.GetOrdinal("YourColumnName"));
    }
}

You can reference any column returned by the SqlDataReader.Read() method. Likewise, if you are looking for an integer value:

您可以引用SqlDataReader.Read()方法返回的任何列。同样，如果您正在寻找一个整数值:

int someInt = sdr.GetInt32(sdr.GetOrdinal("YourColumnName"));

#5

From this thread ( Insert results of a stored procedure into a temporary table ), you might want to try OPENROWSET.

从这个线程(将存储过程的结果插入到临时表中)，您可能需要尝试OPENROWSET。

First, configure your DB,

首先,配置您的数据库,

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

then, depending on your connection :

然后，根据你的联系:

SELECT  yourcolumnname
  FROM    
OPENROWSET(
      'SQLNCLI', 
      'server=yourservername;database=yourdbname;uid=youruserid;pwd=youruserpwd',
      'EXEC [GetChild] yourchildid'
       )

或

SELECT  yourcolumnname
  FROM    
OPENROWSET(
      'SQLNCLI', 
      'server=yourservername;database=yourdbname;Trusted_Connection=yes',
      'EXEC [GetChild] yourchildid')

I wouldn't use this solution when retrieving only one line. Performance would be really bad. For retrieving a great number of lines, this should do the job.

当只检索一行时，我不会使用这个解决方案。性能真的很差。为了检索大量的行，应该这样做。

#1