Database Reference
In-Depth Information
20. DateTime dateUpdated = DateTime.Now;
21.
22. sqlCmd.Connection = sqlConn;
23. sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
24. sqlCmd.CommandText = "proc_SaveProperty";
25. sqlCmd.Parameters.Add("name", NVarChar, 255);
26. sqlCmd.Parameters.Add("value", VarBinary, int.MaxValue);
27. sqlCmd.Parameters.Add("vector", VarBinary, 16);
28. sqlCmd.Parameters.Add("lastUpdated", DateTime);
29. sqlCmd.Parameters.Add("hash", VarBinary, 32);
30. sqlCmd.Parameters[0].Value = propertyName;
31. sqlCmd.Parameters[1].Value = ct.cipher;
32. sqlCmd.Parameters[2].Value = ct.vector;
33. sqlCmd.Parameters[3].Value = dateUpdated;
34.
35. // Calculate the hash of this record...
36. // We pass the list of values that should be hashed
37. // If any of these values changes in the database,
38. // recalculating the hash would yield a different result
39. byte[] hash = Util.ComputeHash(
40. propertyName.GetBytes(),
41. ct.cipher,
42. ct.vector,
43. dateUpdated.GetBytes());
44.
45. sqlCmd.Parameters[4].Value = hash;
46.
47. int res = sqlCmd.ExecuteNonQuery();
48.
49. }
50.
51. sqlConn.Close();
52.
53. }
54. }
55.
56. }
As promised, following is the code for the stored procedure. You create a stored procedure because
it allows you to provide additional security from an access-control standpoint. As you see later, you
create a schema that contains the tables and a separate schema for the stored procedures that access the
tables. This provides greater control over your database security. You review schemas later in this
chapter:
IF (Exists(SELECT * FROM sys.sysobjects WHERE Name = 'proc_SaveProperty' AND Type = 'P'))
DROP PROC proc_SaveProperty
GO
-- SELECT * FROM UserProperties
CREATE PROC proc_SaveProperty
@name nvarchar(255),
@value varbinary(max),
@vector binary(16),
