[MS SQL Server] Get all velues from a column in a string, then pass to Unity

Goog day, I know this is a SQL question, but I am working with MS SQL in pair with Unity, I got this script:

Code (CSharp):

1. Thread thread = new Thread(new ThreadStart(Test));
2. thread.Start();

Code (CSharp):

1. void Test()
2.     {
3.         string s = "";
4.         sqlQuery = sqlQuery + "SELECT something FROM Table";
5.  
6.         using (SqlConnection dbCon = new SqlConnection(_conString))
7.         {
8.             SqlCommand cmd = new SqlCommand(sqlQuery, dbCon);
9.             try
10.             {
11.                 connectionStatus = "Connecting...";
12.                 dbCon.Open();
13.                 SqlDataReader reader = cmd.ExecuteReader();
14.                 while (reader.Read())
15.                 {
16.                     connectionStatus = "Reading Data...";
17.                     if (dbCon != null && dbCon.State != ConnectionState.Closed)
18.                     {
19.                     s = s + "," + reader["Localidad"].ToString();
20.                     }
21.                 }
22.             }
23.             catch (Exception e)
24.             {
25.                 Debug.Log(e);
26.                 tableLoaded = false;
27.                 updating = false;
28.                 return;
29.             }
30.             if (dbCon.State != ConnectionState.Closed)
31.             {
32.                 dbCon.Close();
33.             }
34.             connectionStatus = "Finished";
35.             Debug.Log(s.Length);
36.             Debug.Log(s);
37.         }
38.     }

The process takes about 7 seconds to finish reading 24k rows.

I know that there a better and faster way to do this but I don't know how to.

I would like to know if there is a way to get the whole string from MS SQL instead of building it inside Unity, problem is that I do not have an ID column in the table, actually there is nothing like an identifier in the table.

I would like something like getting a string from the first item in the table to the last, if I could get all of the columns at once it would be better, just like exporting an CSV file, but instead of exporting and the importing into Unity, I want it to be directly.

 

To start - use a StringBuilder to make your string instead of just raw concatenation.

Otherwise, what in god's name are you doing where you need 24k rows from a DB table represented as a single comma separated string?

 

I am actually using a List of string arrays, I just used a string here the make the code look shorted. I create "n" game objects based on the values from the data base, the detaile here is that I have to update those object every certain time, so i need a better and faster way to fill my array with the data from the data base.

This is the actual code:

Code (CSharp):

1.  
2. void ReadDBTable()
3.     {
4.         if (testingConnection)
5.         {
6.             _warningMessage = _warningConnectionTestInProgress;
7.             warningWindowEnabled = true;
8.         }
9.         else if (!testingConnection)
10.         {
11.             updating = true;
12.             bool hasConnected = false;
13.  
14.             string sqlQuery = "SELECT ";
15.             for (int i = 0; i < itemsInTable.Length; i++)
16.             {
17.                 bool hasSpaces = false;
18.                 for (int j = 0; j < itemsInTable[i].Length; j++)
19.                 {
20.                     if (itemsInTable[i][j] == ' ')
21.                     {
22.                         hasSpaces = true;
23.                         break;
24.                     }
25.                 }
26.                 if (hasSpaces)
27.                 {
28.                     sqlQuery = sqlQuery + "[" + itemsInTable[i] + "]";
29.                 }
30.                 else if (!hasSpaces)
31.                 {
32.                     sqlQuery = sqlQuery + itemsInTable[i];
33.                 }
34.                 if (i < itemsInTable.Length - 1)
35.                 {
36.                     sqlQuery = sqlQuery + ", ";
37.                 }
38.             }
39.  
40.             sqlQuery = sqlQuery + " FROM SampleDataBase";
41.  
42.             List<string[]> data = new List<string[]>();
43.  
44.             using (SqlConnection dbCon = new SqlConnection(_conString))
45.             {
46.                 SqlCommand cmd = new SqlCommand(sqlQuery, dbCon);
47.                 try
48.                 {
49.                     connectionStatus = "Connecting...";
50.                     dbCon.Open();
51.                     hasConnected = true;
52.                     SqlDataReader reader = cmd.ExecuteReader();
53.                     while (reader.Read())
54.                     {
55.                         connectionStatus = "Reading Data...";
56.                         if (dbCon != null && dbCon.State != ConnectionState.Closed)
57.                         {
58.                             string[] rowData = new string[14];
59.                             if (FilterPackage(reader[itemsInTable[4]].ToString()))
60.                             {
61.                                 for (int j = 0; j < itemsInTable.Length; j++)
62.                                 {
63.                                     rowData[j] = reader[itemsInTable[j]].ToString();
64.                                 }
65.                                 data.Add(rowData);
66.                             }
67.                         }
68.                     }
69.                 }
70.                 catch (Exception e)
71.                 {
72.                     tableLoaded = false;
73.                     updating = false;
74.                     if (hasConnected)
75.                     {
76.                         _errorMessage = _errorDBConnectionLost;
77.                         errorWindowEnabled = true;
78.                         connectionStatus = "Connection Lost";
79.                     }
80.                     else if (!hasConnected)
81.                     {
82.                         _errorMessage = _errorNoConnectionWithDB;
83.                         errorWindowEnabled = true;
84.                         connectionStatus = "Connection Refused";
85.                     }
86.                     return;
87.                 }
88.                 if (dbCon.State != ConnectionState.Closed)
89.                 {
90.                     dbCon.Close();
91.                 }
92.             }
93.             tableLoaded = true;
94.             itemData = data;
95.             connectionStatus = "Data Received";
96.         }
97.         updating = false;
98.     }
99.  

 

What does your SQL query look like? To get all columns from a SQL table, you can certainly use "select * from table". What does a sample row from the db look like?

 

Again, you could use a StringBuilder to build your query. String concatenation is a garbage bomb.

Frankly, I can't even follow what that code is doing. What is itemsInTable? What are you even trying to accomplish?

 

I can not use "SELECT * FROM SampleDataBase", because every company's data base is different, the "itemsInTable" array contains strings typed by the user to match the name of the columns of the data base the user uses.

And the row as l said is different in each case, the order of the items may change, but generally all companies should have part number, client, warehouse, location, description, weight, type of material, date and so on, this particular user have 14 columns, but the data base does not have an ID column.

 

Ok, ignore the whole first loop from the line 14 to line 40, lets just say the query looks like "SELECT * FROM SampleDataBase", because what I am doing in the loops will be the same thing as writing " * ".

What I am doing in the second loop, is that I iterate trough every single colum in a row and then I add the item from each column to an array of strings, there are 14 columns, so the loopruns 14 times, once the loop finishes, I add the strings array to a List, so in the List I will have every single row available from the data base, then I just copy the List from the method to an external List for further use.

Ignore the line 59, it's just a filter.

 

So a user inputs "x, y, z" and you build a query that says "select x, y, z from sometable"....ok

And everything in the table is a string or?...how are you verifying data types?

You say you get back 24k rows. How many rows are in the table in total? Are any of the columns indexed?

And I'll ask again - I can see what you're doing but what are you trying to accomplish? You've set down this path because it solves a problem but there might be an easier way. Something like an object relational mapping solution that would get you out of the business of processing the resultset yourself. This is straying more into enterprise solutions which, in .NET land, I'm not as familiar with.

 

You could also do "select field1 + "," + field2 + "," + field3 etc to get each row into a single long string, separated by commas in this example.

 

I set all data types to strings once imported into Unity, and no the columns are not indexed. I do not write to the data base, I just read it, so I don't need to worry about data types in this case.

 

@Vitaly-Abilevich Then my suggestion of concatenating the fields into a single string in the SQL query would work.