如何从SQL数据库中的列检索子字符串到列表框

Kindly help me with the following :

请帮助我以下几点:

I am using SQL database server 2008, in which I have a table with columns as shown in the image attached. 如何从SQL数据库中的列检索子字符串到列表框

我正在使用SQL数据库服务器2008，其中有一个包含列的表，如图所示。

I want to retrieve the ToolTip column and extract string after Controls: and separate type=value strings and list in a ListBox separately.

我想要检索工具提示列，并在控件之后提取字符串:和单独的type=value string和列表框中的列表。

Then I want to loop through each row get the list and compare this list with another set of similar list and check if they have same string lists. I haven't tried any code, since I don't know how to start in the first place. Any help will be appreciated. Thank you.

然后，我要循环遍历每一行，获取列表并将这个列表与另一组类似的列表进行比较，并检查它们是否有相同的字符串列表。我没有尝试过任何代码，因为我不知道如何开始。如有任何帮助，我们将不胜感激。谢谢你！

3 个解决方案

#1

It would be better if you had

如果你有的话会更好

Profile_Controls Table

Profile_Controls表

Profile_Name       Controls_Key     Controls_Value
-----------------------------------------------------
Lamp_Profile1      ON_OFF           ON
Lamp_Profile1      BRIGHTNESS       NONE
Fan_Profile1       ON_OFF           ON
Fan_Profile1       SPEED            NONE
.....

Then you can select

然后你可以选择

Select Controls_Key+'='+Controls_Value AS Settings From Profile_Controls Where Profile_Name = 'Fan_Profile1'

Results
-----------------
Settings
---------------------
ON_OFF=ON
SPEED=ON

This give you more flexibility as you can filter, JOIN, COMPARE, and use other built-in SQL functionality

这使您可以更灵活地过滤、连接、比较和使用其他内置的SQL功能

If you still want to maintain your table structure like you have it

如果您仍然想维护表结构，就像您拥有的那样

string[] mysplit = Tool_Tip.Split(s.Split(new string[]{"Controls:"}, StringSplitOptions.RemoveEmptyEntries);

string controls = mysplit[1].Substring(0); //ON_OFF=On;BRIGHTNESS=NONE

string[] eachSettings = controls.Split(';');

//eachSettings[0] = ON_OFF=ON
//eachSettings[1] = BRIGHTNESS=NONE

UPDATE

更新

public System.Data.DataTable GetProfileSettings(string profilename)
{
   string sql = "Select Controls_Key+'='+Controls_Value AS Settings From Profile_Controls Where Profile_Name = '"+profilename+"'";
   //write ADO.Net code here to get settings into DataTable
   //DataTable dt = blah blah blah;

   return dt;
}

in your page

在你的页面

protected void SomeEvent_Handler(object sender, EventArgs e)
{
   myListBox2.DataSource = GetProfileSettings("FanProfile1");
   myListBox2.DataTextField = "Settings";
   myListBox2.DataBind();
}

You can use this link as reference for adding item to your listBox How to retrieve commas delimited values in SQL to a ListBox separately

您可以使用此链接作为向您的列表框中添加项的引用，以分别检索SQL中的逗号分隔值到列表框

#2

String.Split and more reading on SqlConnection and SqlCommand

字符串。对SqlConnection和SqlCommand进行拆分和更多阅读

#3

How about we fire a query like this

我们这样做会怎么样?

select tooltip from FooTable where Profile_Type='Foo_Profile_Type' and Profile_Name = 'Foo_Profile_Name'

and get

并获得

"Type = LAMP;Profile = Lamp_Profile1;Controls:ON_OFF = ON;BRIGHTNESS = NONE;"

Then

然后

string str = returnedstring.Split(new string[]{"Controls:"})[2];

will give you

会给你

"Controls:ON_OFF = ON"

#1