Over the course of my roughly 6 projects that used an SQLite DB, I wrote a number of classes that helped me and, in a sense, even automated my work with the database. So once I had learned the simple SQLite, I moved on to the more complex SQL Server.
Another reason is that at ASPone I have an MS SQL DB of 30MB available, but I never used it, and also because every time I had to ask them for FullTrust permissions, and it was only their good will that they always granted my request, for which I thank ASPone. Now to the class itself and how to write it. In SQLite everything could be handled with plain strings, even writing bytes was simple – X'{bytes in hex format'. Null was stored in the DB simply by writing NULL.
With SQL Server it is a bit different; here parameters play the leading role. For adding parameters I wrote this method:
private static void AddCommandParameter(SqlCommand command, int i, object value) {
if (value == null) {
//-If you want to store null in the DB, you have to set the parameter value to DBNull.Value
command.Parameters.AddWithValue("@p" + i.ToString(), DBNull.Value);
}
else if (value.GetType() == typeof(SqlParameter)) {
//-If you want to store null in an Image column, you have no choice but to pass a SqlParameter as the value, where you specify the SqlDbType and value[object]. Then just fill in the parameter name here (I use @pX - you can of course choose any starting number; I use 0, because VS has a for snippet that starts at zero, so when I want to add multiple parameters in a loop, I just use that snippet and this method. It is also just @pX because that makes SQL queries fastest - you save SQL Server the work of comparing long strings
SqlParameter param = (SqlParameter) value;
param.ParameterName = "@p" + i.ToString();
command.Parameters.Add(param);
}
else if (value.GetType() == typeof(byte[])) {
//-If you want to store a byte array, you first have to create a parameter with the DB type (I always use Image) and only then set the value
SqlParameter param = command.Parameters.Add("@p" + i.ToString(), SqlDbType.Image);
param.Value = value;
}
else {
//-For all other cases, I call the AddWithValue() method
command.Parameters.AddWithValue("@p" + i.ToString(), value);
}
}And finally, how parameters are added to an SQL query using my method:
/// <summary>
/// The method is called OneRow, but it updates any number of rows it finds via WHERE - it is my internal naming so that it reminds me someday; maybe later I will find a way to update just one row.
/// </summary>
public int UpdateOneRow(string table, string columnToUpdate, object newValue, string idColumn, object id) {
string sql = string.Format("UPDATE {0} SET {1}=@p1 WHERE {2} = @p2", table, columnToUpdate, idColumn);
SqlCommand command = new SqlCommand(sql, connection);
AddCommandParameter(command, 1, newValue);
AddCommandParameter(command, 2, id);
int affectedRows = command.ExecuteNonQuery();
return affectedRows;
}And now it is incredibly simple for you. Remember, whenever you want to pass any data to SQL Server, you have to do it through a parameter. That is the only way to ensure flawless functionality! Do not forget to write methods that will check for DBNull.Value and, when the returned value is that, return null instead. That way you will not confuse in your application DBNull.Value with a regular null value.