StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat is best aproach to get sql data from C#
text
Body
copied!<p>I'm trying to find optimal (fast vs easiest) way to access SQL Server code thru code in c#. </p> <p>As i was learning from books I've encountered multiple suggestions usually telling me to do it via drag and drop. However since i wanted to do it in code first aproach was to get data by column numbers, but any reordering in SQL Query (like adding/removing columns) was pain for me to fix.</p> <p>For example (don't laugh, some code is like 2 years old), i even coded special function to pass sqlQueryResult and check if it's null or not):</p> <pre><code>public static void exampleByColumnNumber(string varValue) { string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2] FROM [Database].[dbo].[Table] WHERE [SomeOtherColumn] = @varValue"; SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection); sqlQuery.Prepare(); sqlQuery.Parameters.AddWithValue("@varValue) ", varValue); SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader(); if (sqlQueryResult != null) { while (sqlQueryResult.Read()) { string var1 = Locale.checkForNullReturnString(sqlQueryResult, 0); string var2 = Locale.checkForNullReturnString(sqlQueryResult, 1); } sqlQueryResult.Close(); } } </code></pre> <p>Later on i found out it's possible thru column names (which seems easier to read with multiple columns and a lot of changing order etc):</p> <pre><code> public static void exampleByColumnNames(string varValue) { string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2] FROM [Database].[dbo].[Table] WHERE [SomeOtherColumn] = @varValue"; SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection); sqlQuery.Prepare(); sqlQuery.Parameters.AddWithValue("@varValue) ", varValue); SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader(); if (sqlQueryResult != null) { while (sqlQueryResult.Read()) { string var1 = (string) sqlQueryResult["SomeColumn"]; string var2 = (string) sqlQueryResult["SomeColumn2"]; } sqlQueryResult.Close(); } } </code></pre> <p>And 3rd example is by doing it by column names but using .ToString() to make sure it's not null value, or by doing If/else on the null check. </p> <pre><code> public static void exampleByColumnNamesAgain(string varValue) { string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2], [SomeColumn3] FROM [Database].[dbo].[Table] WHERE [SomeOtherColumn] = @varValue"; SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection); sqlQuery.Prepare(); sqlQuery.Parameters.AddWithValue("@varValue) ", varValue); SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader(); if (sqlQueryResult != null) { while (sqlQueryResult.Read()) { string var1 = (string) sqlQueryResult["SomeColumn"].ToString(); DateTime var2; DateTime.TryParse(sqlQueryResult["SomeColumn2"].ToString()); int varInt = ((int) sqlQueryResult["SomeColumn3"] == null ? 0 : (int) sqlQueryResult["SomeColumn3"]; } sqlQueryResult.Close(); } } </code></pre> <p>Please bare in mind that I've just created this for sake of this example and there might be some typos or some slight syntax error, but the main question is which approach is best, which is the worst (i know first one is the one that i dislike the most). </p> <p>I will soon have to start / rewrtiting some portion of my little 90k lines app which has at least those 3 examples used widely, so i would like to get best method for speed and preferably easiest to maintain (hopefully it will be same aproach).</p> <p>Probably there are some better options out there so please share?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload