需要一个函数来获取表中的值列表 [英] Need a function to get a list of values in a table

问题描述

大家好，

Iam具有这样的值列表

''CHOPL，NAKRL，NLGD2，DEVKD，HALYA''

如果我执行一个函数，我想要一个像这样的表

Hi All,

Iam having list of value like this

''CHOPL,NAKRL,NLGD2,DEVKD,HALYA''

If I execute a function i want a table like this

Number  IntegerFromList
1       CHOPL
2       NAKRL
3       NLGD2
4       DEVKD
5       HALYA

谁能帮助我为此情况编写函数

Can anyone help me to write a function for this scenario

推荐答案

// Your values as a string
string values = "CHOPL,NAKRL,NLGD2,DEVKD,HALYA";

// Split your string in to an array of values
string[] arrValues = values.Split(',');

// Connect to database using SqlConnection()
using (SqlConnection cn = new SqlConnection("connectionstring"))
{
    // SqlCommand to execute your sql-query
    SqlCommand cmd = new SqlCommand("INSERT INTO Table (Columne) VALUES (@Data)", cn);
    // Open connection and let it stay open until your work is done
    cn.Open();

    // Loop the values from your string array
    foreach (var value in arrValues)
    {
        // Clear parameters so the @data parameter is not added multiple times
        cmd.Parameters.Clear();
        // Add the next value in your array as a parameter
        cmd.Parameters.AddWithValue("@Data", value);
        cmd.ExecuteNonQuery();
    }
    // Work is done. Close connection
    cn.Close();
}

为什么不编写一个函数来返回此数据，而不是简单地将新表添加到数据库中，在其中添加这些值，然后在应用程序中使用该表.

我认为您要避免使用硬编码的值列表.

Instead of writing a function to return this data, why not simply add a new table to your database where you add these values and then use that table in your application.

In my opinion hardcoded list of values are something you want to avoid.

DECLARE @S varchar(max),
  @Split char(1),
  @X xml

SELECT @S = '1,2,3,4,5',
  @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

该逻辑将给您逗号分隔的值，只是@S输入到此处进行硬编码的函数.

This logic will give you comma seperated value just @S input to function which is hardcoded here.

这篇关于需要一个函数来获取表中的值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！