I was working on this function which was quite specific to my project, so thought I'd make it more generic and share it, since I could find very little on the subject in my searching.
Code also demonstartes how exceptions can work to your advantage when you know they are going to be thrown.
I'm really quite new to OOP, so maybe I have no business posting snippets, but I quite enjoyed writing this one so I'll post it anyway :-)
Any comments, criticisms or improvements are very welcome.
public string[,] SQLiteTableToArray(string tablename, SQLiteConnection sqlcon)
{
int cols = 0, rows = 0;
/*get number of columns in table*/
/*only need to read one row*/
string sqlstr = "SELECT * FROM " + tablename + " LIMIT 1";
SQLiteCommand cmd = new SQLiteCommand(sqlstr, sqlcon);
SQLiteDataReader reader = cmd.ExecuteReader();
reader.Read();
/*loop and increment until exception is thrown*/
while (true)
{
try
{
reader.GetValue(cols);
cols++;
}
catch (Exception)
{
break;
}
}
/*get number of columns in table*/
/*no need to read the whole table and loop*/
sqlstr = "SELECT COUNT(*) FROM " + tablename;
cmd = new SQLiteCommand(sqlstr, sqlcon);
object v = cmd.ExecuteScalar();
rows = Convert.ToInt32(v);
v = null;
/*create a new array with correct row and column count*/
string[,] array = new string[rows, cols];
/*read table into array*/
sqlstr = "SELECT * FROM " + tablename;
cmd = new SQLiteCommand(sqlstr, sqlcon);
reader = cmd.ExecuteReader();
for (int y = 0; y < rows; y++)
{
reader.Read();
for (int x = 0; x < cols; x++)
{
array[y, x] = reader.GetValue(x).ToString();
}
}
/*clean up*/
cmd.Dispose();
reader.Close();
return array;
}
Carry on Suzie! I find this a good snip. :o)
One little remark: normal C# comment style is to use // for one line comments and to use /* ....*/ for multiline comments. But hé, I'm nitpicking.
Looks good.
I have some suggestions which are sincerely intended as to be encouraging.
Firstly I would always use the using statement rather than manually calling Dispose eg:
using (SqlDataReader reader = cmd.ExecuteReader())
{
//Somde code here
}
For a few reasons - firstly you won't forget to call Dispose. Secondly it gives nicely structered indentation making it easier on the eye to see what is happening and thirdly because it is the way most developers do it so its a kind of unwritten standard.
I would also be mindful of the S in SOLID. That is single responsibility principle. This method does a lot. I would split it up a bit more. In Visual Studio a great thing to play with is highlighting a block of code that looks like it could be a seperate method or class and then right click and Refactor - try extracting to method - VS will figure out the parameters and does all the work for you.
Smaller, more cohesive methods are easier to maintain, test and debug.
And finanlly in C# we say methods not functions - sorry if that one is too picky!
Thanks guys, I appreciate your remarks.
Here is link to a reverse method to the one above.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.