数据库测试与T-SQL代码复用的关键要点

### 数据库测试与T-SQL代码复用的关键要点 #### 1. 数据库对象测试 在数据库开发中，对各种数据库对象进行测试是确保数据准确性和系统稳定性的重要环节。 ##### 1.1 表、视图和UDF的测试 表、视图和用户定义函数（UDF）的测试方式基本相同，都是通过直接执行T - SQL查询来进行。这属于纯粹的黑盒测试，主要测试返回数据的有效性。测试时要确保对这些数据库对象所做的任何更改（如对视图添加或移除连接，或更改UDF中的业务逻辑）不会改变预期结果。 测试应在与应用程序在生产环境中使用的相同隔离级别下运行，并且需要对这些对象进行读写测试，因为它们既可以用于读取数据，也可以用于修改数据。不过，标量UDF只能进行读测试，因为无法通过它插入数据。 在对表进行写测试时，还需要考虑触发器的影响。触发器不能直接触发，它会在表修改过程中执行。因此，表的写测试必须考虑可能触发的触发器的影响，这些触发器可能会修改其他表，这些“二级”表也需要纳入测试范围。过度使用触发器会扩大测试范围，使测试变得庞大、繁琐且不切实际。 以下是一些具体的测试方法示例： - **表单行读取测试**：从`Person.Address`表中按`AddressId`列排序后返回第一行，并将其值与已知值进行比较。 ```csharp [Test] public void Table_single_line_read_from_Person_Address() { // Load test data before doing read tests! // for brevity's sake we'll use the // existing data in AdventureWorks database SqlCommand cmd = new SqlCommand("," conn); cmd.CommandText = @"SELECT TOP 1 AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address ORDER BY AddressID"; DataSet ds = new DataSet(); ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "ReadData"); DataRow row = ds.Tables["ReadData"].Rows[0]; Assert.AreEqual(row["AddressID"], 1, "Inserted value different from expected value."); Assert.AreEqual(row["AddressLine1"], "1970 Napa Ct.," "Inserted value different from expected value."); Assert.AreEqual(row["AddressLine2"], DBNull.Value, "Inserted value different from expected value."); Assert.AreEqual(row["City"], "Bothell," "Inserted value different from expected value."); Assert.AreEqual(row["StateProvinceID"], 79, "Inserted value different from expected value."); Assert.AreEqual(row["PostalCode"], "98011," "Inserted value different from expected value."); } ``` - **表单行插入测试**：向`Person.Address`表中插入一行新数据，并测试插入的值是否与已知值匹配。 ```csharp [Test] public void Table_single_line_insert_into_Person_Address() { // for simplicity assume we have a // valid connection already opened. SqlCommand cmd = new SqlCommand("," conn); string randomNumber = new Random(DateTime.Now .Millisecond) .Next(10, 1000).ToString(); DateTime date = DateTime.Parse(DateTime.Now .ToString("yyyy-MM-dd HH:mm:ss.ff")); // insert a random row and use output // so we don't have to do a read cmd.CommandText = @"INSERT INTO Person.Address( AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, ModifiedDate) OUTPUT inserted.* VALUES ('Test Address Line 1 " + randomNumber + @"', 'Test Address Line 2 " + randomNumber + @"', 'Test City " + randomNumber + "', 79, '98011', '" + date.ToString("yyyy-MM-dd HH:mm:ss.ff") + "')"; // insert and return inserted data DataSet ds = new DataSet(); ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "InsertedData"); DataRow row = ds.Tables["InsertedData"].Rows[0]; Assert.AreEqual(row["AddressLine1"].ToString(), "Test Address Line 1 " + randomNumber, "Inserted value different from expected value."); Assert.AreEqual(row["AddressLine2"].ToString(), "Test Address Line 2 " + randomNumber, "Inserted value different from expected value."); Assert.AreEqual(row["City"].ToString(), "Test City " + randomNumber, "Inserted value different from expected value."); Assert.AreEqual(row["StateProvinceID"], 79, "Inserted value different from expected value."); Assert.AreEqual(row["PostalCode"].ToString(), "98011," "Inserted value different from expected value."); Assert.AreEqual(row["ModifiedDate"], date, "Inserted value different from expected value."); } ``` - **视图单行插入测试**：向`Person.vStateProvinceCountryRegion`视图插入一行数据，该视图是父表和子表的连接，插入的数据是插入到子表中，最后将插入的行与已知值进行比较。 ```csharp [Test] public void View_single_line_insert_into_Person_vStateProvinceCountryRegion() { // for simplicity assume we have a // valid connection already opened. SqlCommand cmd = new SqlCommand("," conn); // CountryRegionCode is 3 chars long // so generate at maximum a 3 digit number string randomNumber = new Random(DateTime.Now .Millisecond) .Next(0, 999).ToString(); // we can insert into the view // as long as we insert into a single child table. // Look at the code for vStateProvinceCountryRegion // in AdventureWorks database for the view definition cmd.CommandText = @"insert into Person.vStateProvinceCountryRegion (CountryRegionCode, CountryRegionName) output inserted.CountryRegionCode, inserted.CountryRegionName values ('" + randomNumber + @"', '" + randomNumber + @" Country')"; // insert and return inserted data DataSet ds = new DataSet(); ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "InsertedViewData"); DataRow row = ds.Tables["InsertedViewData"].Rows[0]; Assert.AreEqual(row["CountryRegionCode"], randomNumber, "Inserted value different from expected value."); Assert.AreEqual(row["CountryRegionName"], randomNumber + " Country," "Inserted value different from expected value."); } ``` ##### 1.2 存储过程测试 存储过程的测试比其他数据库对象更具挑战性。它可以返回多个结果集，有多个可选的输入和输出参数，并且通常包含复杂的读写逻辑。 如果存储过程只是只读或只写的，那么可以像测试其他对象一样进行测试。但对于既执行读又执行写操作的存储过程，则需要特殊的测试过程。和表、视图、UDF一样，存储过程的测试也需要在与应用程序在生产环境中使用的相同类型的事务中进行。例如，如果应用程序使用`SERIALIZABLE`事务隔离级别，那么