SQL Server doesn't use the LIMIT keyword, instead use TOP() to select the maximum number of rows to return: SqlCommand1->CommandText = "SELECT TOP(1) SomeRowName \ FROM SomeTableName \ WHERE SomeRowName2 == 0";
Category: SELECT
DateTime in SELECT queries
Older than # days SqlCommand1->CommandText = "DELETE FROM tblMyTable WHERE DATEDIFF(day, getdate(), MyColumnName) < -10"; //Delete all records older than # days ago SqlCommand1->ExecuteNonQuery();
Parameters In Queries
A C++ .Net example using parameters SqlCommand1->CommandType = CommandType::Text; SqlCommand1->Parameters->AddWithValue(“@Type”, Convert::ToString(Type)); SqlCommand1->Parameters->AddWithValue(“@LocationId”, Convert::ToString(LocationId)); SqlCommand1->Parameters->AddWithValue(“@EventDateTime”, EventDateTime->ToString(“s”)); SqlCommand1->Parameters->AddWithValue(“@TagId”, Convert::ToString(TagId)); SqlCommand1->Parameters->AddWithValue(“@TagScore”, Convert::ToString(TagScore)); SqlCommand1->Parameters->AddWithValue(“@SourceId”, SourceId); SqlCommand1->CommandText = “DELETE FROM tblMyTable WHERE Something = @LocationId”; SqlCommand1->ExecuteNonQuery(); SqlCommand1->CommandText = “INSERT INTO tblMyTable ( \ Added, \ Type, \ LocationId, \ EventDateTime, \ TagId, \ TagScore, \ SourceId \ ) VALUES ( \ […]
Sanitizing Queries
Use parameter based queries instead of trying to sanitize strings – its much safer and easier. Good resources: http://stackoverflow.com/questions/249567/algorithm-to-avoid-sql-injection-on-mssql-server-from-c-sharp-code C++ .Net example of a parameter based query SqlConnection1->Open(); SqlClient::SqlDataAdapter ^SqlDataAdaptor1 = gcnew SqlClient::SqlDataAdapter(); SqlDataAdaptor1->MissingSchemaAction = MissingSchemaAction::AddWithKey; DataSet ^DataSet1 = gcnew DataSet(); SqlClient::SqlCommand ^SqlCommand1 = gcnew SqlClient::SqlCommand(); SqlCommand1->Connection = SqlConnection1; SqlClient::SqlCommandBuilder ^CmdBuilder1 = gcnew SqlClient::SqlCommandBuilder(); CmdBuilder1->DataAdapter […]
Count Results
Total Number Of Some Column SELECT COUNT(SomeColumnName) FROM SomeTable WHERE … SELECT SomeColumnName, COUNT(SomeColumnName) FROM SomeTable WHERE … GROUP BY SomeColumnName //You can read it like this TotalVideosInSection = Convert::ToInt32(Row[0]); Total Number Of Rows SELECT COUNT(*) FROM SomeTable WHERE … Sum Of Some Column SELECT SUM(SomeColumnName) FROM SomeTable WHERE … Total Number Of True SELECT […]
GROUP BY
Group By Allows You To Return Results Grouped By A Certain Value Which Rows Share. Returns Total Number Of Rows For Each Value Present array ^Value = gcnew array(0); array ^MatchingRows= gcnew array(0); SqlClient::SqlConnection ^SqlConnection1 = gcnew SqlClient::SqlConnection(); SqlConnection1->ConnectionString = DataSourceString + DatabaseConnectionString; SqlConnection1->Open(); SqlClient::SqlCommand ^SqlCommand1; SqlCommand1 = gcnew SqlClient::SqlCommand(); SqlCommand1->Connection = SqlConnection1; SqlCommand1->CommandType = […]
Sub Queries
Matches A Value Returned From A Sub Query For Instance On Another Table “WHERE MyColumnName IN (SELECT SomeColumnNameToReturn FROM tblSomeTableName WHERE SomeColumnName=2)”
ORDER BY
ORDER BY UserName ASC"; ORDER BY UserName DESC"; ORDER BY Country, UserName"; //For multiple fields DateTime most recent first ORDER BY MyDateColumn DESC";
General Select Statements
“SELECT * FROM tblTest” “SELECT SupplierID, CompanyName FROM dbo.Suppliers;” “SELECT AuthorID, LastName, FirstName FROM Authors” “SELECT * FROM tblUsers WHERE UserId=” + Convert::ToString(UserId) WHERE Multiple SELECT’s “SELECT SomeColumnName, SomeOtherColumnName FROM SomeTableName WHERE … SELECT AS “SELECT SomeColumnName AS ‘The Name I Want To Use’ FROM SomeTableName WHERE … Multiple WHERE’s “SELECT * FROM SomeTableName WHERE […]