用C#做过项目,不过开发的不很爽
.很想美美的驱动和重构,一直没有机会.弄点代码来学习吧 .
1.连接字符串的测试驱动范例：
DataBase -SQL Server 2000:
1.Connecting to the Database
2.XML应用程序的配置文件:
<?xml version="1.0" encoding="utf-8"
?>
<configuration>
<appSettings>
<add key="Catalog.Connection"
value="server=(local);database=catalog;Trusted_Connection=true"
/>
</appSettings>
</configuration>
using System;
using System.Data;
using System.Data.SqlClient;
using NUnit.Framework;
[TestFixture]
public class SqlConnectionFixture
{
[Test]
public void ConnectionIsOpen()
{
//使用配置文件定义连接字符串
//string connectionString =
// ConfigurationSettings.AppSettings.Get("Catalog.Connection");
// Assert.IsNotNull(connectionString);
SqlConnection connection
=
new
SqlConnection(
@"server=(local);database=catalog;Trusted_Connection=true");
connection.Open();
Assert.AreEqual(ConnectionState.Open,
connection.State);
connection.Close();
}
}
2.连接字符串的测试驱动优化范例：
以上代码可以替换为:
[TestFixture]
public class SqlConnectionFixture
{
private string connectionString;
[SetUp]
public void RetrieveConnectionString()
{
connectionString =
ConfigurationSettings.AppSettings.Get("Catalog.Connection");
}
[Test]
public void CanRetrieveConnectionString()
{
Assert.IsNotNull(connectionString);
}
[Test]
public void ConnectionIsOpen()
{
SqlConnection connection
=
new
SqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open,
connection.State);
connection.Close();
}
}
3.ArtistGateway实现数据访问层的一个范例
对数据表artist的通用操作
using System; using System.Data;
using System.Data.SqlClient; namespace DataAccessLayer
{ public class ArtistGateway
{ private SqlDataAdapter adapter;
private SqlConnection connection;
private SqlCommand command;
private SqlCommandBuilder builder;
public ArtistGateway(SqlConnection connection)
{
this.connection = connection;
command = new SqlCommand(
"select id, name from artist where id = @id",
connection);
command.Parameters.Add("@id",SqlDbType.BigInt);
adapter = new SqlDataAdapter(command);
builder = new SqlCommandBuilder(adapter);
}
public long Insert(
RecordingDataSet recordingDataSet, string artistName) {
long artistId =
GetNextId(recordingDataSet.Artists.TableName);
RecordingDataSet.Artist artistRow =
recordingDataSet.Artists.NewArtist();
artistRow.Id = artistId;
artistRow.Name = artistName;
recordingDataSet.Artists.AddArtist(artistRow);
adapter.Update(recordingDataSet,
recordingDataSet.Artists.TableName);
return artistId;
}
public RecordingDataSet.Artist
FindById(long artistId, RecordingDataSet recordingDataSet)
{
command.Parameters["@id"].Value = artistId;
adapter.Fill(recordingDataSet,
recordingDataSet.Artists.TableName);
DataRow[] rows = recordingDataSet.Artists.Select(
String.Format("id={0}",artistId));
if(rows.Length < 1) return null;
return (RecordingDataSet.Artist)rows[0];
}
public long GetNextId(string tableName)
{
SqlTransaction transaction =
connection.BeginTransaction(
IsolationLevel.Serializable, "GenerateId");
SqlCommand selectCommand = new SqlCommand(
"select nextId from PKSequence where tableName = @tableName",
connection, transaction);
selectCommand.Parameters.Add("@tableName",
SqlDbType.VarChar).Value=tableName;
long nextId = (long)selectCommand.ExecuteScalar();
SqlCommand updateCommand = new SqlCommand(
"update PKSequence set nextId = @nextId where tableName=@tableName",
connection, transaction);
updateCommand.Parameters.Add("@tableName",
SqlDbType.VarChar).Value=tableName;
updateCommand.Parameters.Add("@nextId",
SqlDbType.BigInt).Value=nextId+1;
updateCommand.ExecuteNonQuery();
transaction.Commit();
return nextId;
}
4.ArtistFixture.cs 数据访问层代理类的测试用例
using System; using System.Configuration;
using System.Data; using System.Data.SqlClient;
using DataAccessLayer; using NUnit.Framework;
[TestFixture] public class ArtistFixture
{ [Test]
public void RetrieveArtistFromDatabase() {
string artistName = "Artist";
SqlConnection connection =
new SqlConnection(
ConfigurationSettings.AppSettings.Get(
"Catalog.Connection")); connection.Open();
ArtistGateway gateway = new ArtistGateway(connection);
long artistId =
gateway.Insert(new RecordingDataSet(),artistName);
RecordingDataSet loadedFromDB = new RecordingDataSet();
RecordingDataSet.Artist loadedArtist =
gateway.FindById(artistId, loadedFromDB);
Assert.AreEqual(artistId,loadedArtist.Id);
Assert.AreEqual(artistName, loadedArtist.Name);
gateway.Delete(loadedFromDB, artistId); //删除功能的驱动
connection.Close();
} }
5.删除驱动所形成的实现代码：
public void Delete(RecordingDataSet recordingDataSet,
long artistId)
{
RecordingDataSet.Artist
loadedArtist =
FindById(artistId,
recordingDataSet);
loadedArtist.Delete();
adapter.Update(recordingDataSet,
recordingDataSet.Artists.TableName);
}
6.有了完整的测试用例，以及实现的代码，可以进行重构：
//首先优化测试的代码：
[TestFixture] public class ArtistFixture
{ private static readonly string artistName = "Artist";
private SqlConnection connection; private ArtistGateway gateway;
private RecordingDataSet recordingDataSet; private long artistId;
[SetUp] public void SetUp()
{ connection = new SqlConnection(
ConfigurationSettings.AppSettings.Get(
"Catalog.Connection"));
connection.Open();
recordingDataSet = new RecordingDataSet();
gateway = new ArtistGateway(connection);
artistId = gateway.Insert(recordingDataSet,artistName);
} [TearDown]
public void TearDown() {
gateway.Delete(recordingDataSet, artistId);
connection.Close();
} [Test]
public void RetrieveArtistFromDatabase() {
RecordingDataSet loadedFromDB = new RecordingDataSet();
RecordingDataSet.Artist loadedArtist =
gateway.FindById(artistId, loadedFromDB);
Assert.AreEqual(artistId,loadedArtist.Id);
Assert.AreEqual(artistName, loadedArtist.Name);
} [Test]
public void DeleteArtistFromDatabase() {
RecordingDataSet emptyDataSet = new RecordingDataSet();
long deletedArtistId = gateway.Insert(emptyDataSet,"Deleted Artist");
gateway.Delete(emptyDataSet,deletedArtistId);
RecordingDataSet.Artist deleletedArtist =
gateway.FindById(deletedArtistId, emptyDataSet);
Assert.IsNull(deleletedArtist);
} }
7.在重构的基础上加入更新方法：.
[Test] public void UpdateArtistInDatabase()
{ RecordingDataSet.Artist artist = recordingDataSet.Artists[0];
artist.Name = "Modified Name"; gateway.Update(recordingDataSet);
RecordingDataSet updatedDataSet = new RecordingDataSet(); RecordingDataSet.Artist updatedArtist =
gateway.FindById(artistId, updatedDataSet); Assert.AreEqual("Modified Name", updatedArtist.Name);
}
//The following is the implementation of Update:
public void Update(RecordingDataSet recordingDataSet) {
adapter.Update(recordingDataSet,
recordingDataSet.Artists.TableName);
}
8.Genre Gateway 另一个测试驱动的例子（类似于artists）
[TestFixture] public class GenreFixture
{ private static readonly string genreName = "Rock";
private SqlConnection connection; private GenreGateway gateway;
private RecordingDataSet recordingDataSet; private long genreId;
[SetUp] public void SetUp()
{ connection = new SqlConnection(
ConfigurationSettings.AppSettings.Get(
"Catalog.Connection"));
connection.Open();
recordingDataSet = new RecordingDataSet();
gateway = new GenreGateway(connection);
genreId = gateway.Insert(recordingDataSet, genreName);
} [TearDown]
public void TearDown() {
gateway.Delete(recordingDataSet, genreId);
connection.Close();
} [Test]
public void RetrieveGenreFromDatabase() {
RecordingDataSet loadedFromDB = new RecordingDataSet();
RecordingDataSet.Genre loadedGenre =
gateway.FindById(genreId, loadedFromDB);
Assert.AreEqual(genreId, loadedGenre.Id);
Assert.AreEqual(genreName, loadedGenre.Name);
} }
9.实现部分Genre Gateway：
public class GenreGateway {
private SqlDataAdapter adapter;
private SqlConnection connection;
private SqlCommand command;
private SqlCommandBuilder builder;
public GenreGateway(SqlConnection connection)
{
this.connection = connection;
command = new SqlCommand(
"select id, name from Genre where id = @id",
connection);
command.Parameters.Add("@id",SqlDbType.BigInt);
adapter = new SqlDataAdapter(command);
builder = new SqlCommandBuilder(adapter);
}
public long Insert(RecordingDataSet recordingDataSet,
string genreName) {
long genreId = GetNextId(recordingDataSet.Genres.TableName);
RecordingDataSet.Genre genreRow =
recordingDataSet.Genres.NewGenre();
genreRow.Id = genreId;
genreRow.Name = genreName;
recordingDataSet.Genres.AddGenre(genreRow);
adapter.Update(recordingDataSet,
recordingDataSet.Genres.TableName); return genreId;
}
public RecordingDataSet.Genre
FindById(long genreId, RecordingDataSet recordingDataSet)
{
command.Parameters["@id"].Value = genreId;
adapter.Fill(recordingDataSet,
recordingDataSet.Genres.TableName); DataRow[] rows = recordingDataSet.Genres.Select(
String.Format("id={0}",genreId));
if(rows.Length < 1) return null;
return (RecordingDataSet.Genre)rows[0];
}
public void Delete(RecordingDataSet recordingDataSet,
long genreId) {
RecordingDataSet.Genre loadedGenre =
FindById(genreId, recordingDataSet);
loadedGenre.Delete();
adapter.Update(recordingDataSet,
recordingDataSet.Genres.TableName); }
public long GetNextId(string tableName)
{ /* same as in ArtistGateway */ }
}
10.获取下一个ID
[TestFixture] public class IdGeneratorFixture
{ private SqlConnection connection;
[SetUp] public void OpenConnection()
{ connection = new SqlConnection(
ConfigurationSettings.AppSettings.Get(
"Catalog.Connection"));
connection.Open();
} [Test]
public void GetNextIdIncrement() {
SqlCommand sqlCommand = new SqlCommand(
"select nextId from PKSequence where tableName=@tableName",
connection);
sqlCommand.Parameters.Add(
"@tableName",SqlDbType.VarChar).Value="Artist";
long nextId = (long)sqlCommand.ExecuteScalar();
long nextIdFromGenerator =
IdGenerator.GetNextId("Artist", connection);
Assert.AreEqual(nextId, nextIdFromGenerator);
nextId = (long)sqlCommand.ExecuteScalar();
Assert.AreEqual(nextId, nextIdFromGenerator + 1);
} [TearDown]
public void CloseConnection() {
connection.Close();
} }
//实现部分
public class IdGenerator {
public static long GetNextId(string tableName, SqlConnection connection)
{ SqlTransaction transaction = connection.BeginTransaction(
IsolationLevel.Serializable, "GenerateId"); SqlCommand selectCommand = new SqlCommand(
"select nextId from PKSequence where tableName = @tableName",
connection, transaction);
selectCommand.Parameters.Add("@tableName",
SqlDbType.VarChar).Value=tableName;
long nextId = (long)selectCommand.ExecuteScalar();
SqlCommand updateCommand = new SqlCommand(
"update PKSequence set nextId = @nextId where tableName=@tableName",
connection, transaction);
updateCommand.Parameters.Add("@tableName", SqlDbType.VarChar).Value=tableName;
updateCommand.Parameters.Add("@nextId", SqlDbType.BigInt).Value=nextId+1;
updateCommand.ExecuteNonQuery();
transaction.Commit();
return nextId;
} }
11.IdGeneratorFixture.cs优化过得实现：
using System; using System.Configuration;
using System.Data; using System.Data.SqlClient;
using NUnit.Framework; using DataAccessLayer;
[TestFixture] public class IdGeneratorFixture : ConnectionFixture
{ [Test]
public void GetNextIdIncrement() {
SqlCommand sqlCommand =
new SqlCommand(
"select nextId from PKSequence where tableName=@tableName",
Connection);
sqlCommand.Parameters.Add("@tableName",SqlDbType.VarChar).Value="Artist";
long nextId = (long)sqlCommand.ExecuteScalar();
long nextIdFromGenerator = IdGenerator.GetNextId("Artist", Connection);
Assert.AreEqual(nextId, nextIdFromGenerator);
nextId = (long)sqlCommand.ExecuteScalar();
Assert.AreEqual(nextId, nextIdFromGenerator + 1);
} } | 
