如何从多个数据库列中以逗号分隔的字符串检索记录？

3 回答

• 0

  我是什么意思，但是 - 从广义上讲 - 我会执行select以从数据库获取相关数据并在阅读器上迭代我将使用Split拆分seat_select并将每个元素添加到列表中 . 然后我会使用Linq的Distinct删除重复项并显示它 .

  希望这是你的意思，它有所帮助

  回复于 2024-05-21T14:31:32+08:00
• 0

  您可以使用以下查询返回一个逗号单独的seat_select列表，然后使用linq对返回的数据执行不同的操作 .

  declare @seats nvarchar(max)
  select @seats = seat_select + ',' +  isnull(@seats,'') from tableName where [date] ='11/12/2010'
  return @seats
  

  回复于 2024-05-21T14:31:32+08:00
• 0

  标记：

  <asp:TextBox runat="server" ID="txtDate" />
  <asp:Button runat="server" ID="cmdGet" Text="Get" OnClick="cmdGet_Click" />
  <asp:TextBox runat="server" ID="txtSeat" />
  

  代码隐藏（C＃） - 表示层上的格式化：

  protected void cmdGet_Click (object sernder, EventArgs e)
  {
      DateTime d;
      if (!DateTime.TryParseExact(txtDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
          throw new InvalidOperationException("Input was in incorrect format");
  
      using (SqlConnection connection = new SqlConnection(connectionString))
      using (SqlCommand command = connection.CreateCommand())
      {
          command.CommandText = "SELECT seat_select FROM table_seats WHERE date <= @date";
          command.Parameters.AddWithValue("@date", d);
  
          connection.Open();
          using (SqlDataAdapter adapter = new SqlDataAdapter(command))
          using (DataTable table = new DataTable())
          {
              adapter.Fill(table);
              txtSeat.Text = String.Join(", ", table.AsEnumerable().Select(r => r.Field<int>("seat_select")));
          }
      }
  }
  

  不要忘记添加对 System.Data.DataSetExtensions.dll ， System.Core.dll 的引用 .

  ---

  代码隐藏（C＃） - 数据层格式化：

  protected void cmdGet_Click (object sernder, EventArgs e)
  {
      DateTime d;
      if (!DateTime.TryParseExact(txtDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
          throw new InvalidOperationException("Input was in incorrect format");
  
      using (SqlConnection connection = new SqlConnection(connectionString))
      using (SqlCommand command = connection.CreateCommand())
      {
          command.CommandText = "DECLARE @seats NVARCHAR(MAX); SELECT @seats = COALESCE(@seats + ', ', '') + seat_select FROM table_seats WHERE date <= @date";
          command.Parameters.AddWithValue("@date", d);
  
          connection.Open();
          txtSeat.Text = (string)command.ExecuteScalar();
      }
  }
  

  回复于 2024-05-21T14:31:32+08:00