Delete All Tables in an MSSQL DB

Dave Mateer — Mon, 26 Jul 2010 00:46:41 GMT

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Be careful you are in the correct db! I had to manually drop some tables in the correct order due to FK’s

Test Driven Development in .NET - DAL (Ch5)

Dave Mateer — Tue, 20 Oct 2009 01:03:44 GMT

From the nbdn course, I had some interesting code I'd like to test. Just the business logic really, no DAL nor UI. As I wanted to make a full application, the first though was to make a DAL which talked to a text file, or just some dummy data. How to do this well? De-coupled. Tried to use poor mans dependency injection, and got confused. So I had a coffee, and bought this book. Because it had an end to end example of a testable application. It uses .net1.1 mostly (which is good as there are no language constructs to learn)

Some good introduction to refactorings, and unit testing. The main meat of the book is an example called MediaLibrary (music recordings). Chapter 5 started with testing the DAL.

Here are the first tests. Am showing nunit test runner.. mostly I use testdriven.net, and have setup ctrl alt shft t to run tests.

for source control I'm using git, and have coupled up with gvim as text editor. And linked in beyond compare for a merge tool.

The book uses Strongly Typed Datasets, which were the 'star of the show' in the .net1.0 release. By writing the sql, and having intellisense have autoproperties, this saves writing boiler plate code.

I learned about DataSets.. here is my edit (from wikipedia)..http://en.wikipedia.org/wiki/ADO.NET#DataSets

ADO.NET consists of two primary parts:

Data provider

These classes provide access to a data source, such as a Microsoft SQL Server. Each data source has its own set of provider objects, but they each have a common set of utility classes:

Connection: Provides a connection used to communicate with the data source.
Command: Used to perform some action on the data source, such as reading, updating, or deleting relational data.
Parameter: Describes a single parameter to a command. A common example is a parameter to a stored procedure.
DataAdapter: A bridge used to transfer data between a Data source and a DataSet object (see below).
DataReader: Used to efficiently process a large list of results one record at a time. It allows records to be accessed in a read-only, forward-only mode, i.e., records have to be accessed in sequential order; they can neither be randomly accessed nor can a record which has been processed previously be accessed again.

DataSets

DataSet objects, a group of classes describing a simple in-memory relational database,

A DataSet object represents a schema (either an entire database or a subset of one). It can contain tables and relationships between those tables.
- A DataTable object represents a single table in the database. It has a name, rows, and columns.
  - A DataView object overlays a DataTable and sorts the data (much like an SQL "order by" clause) and filters the records (much like an SQL "where" clause) if a filter is set. An in-memory index is used to facilitate these operations. All DataTables have a default filter, while any number of additional DataViews can be defined, reducing interaction with the underlying database and thus improving performance.
    - A DataColumn represents a column of the table, including its name and type.
    - A DataRow object represents a single row in the table; it allows reading and updating of values in that row, likewise retrieving any rows that are related to it through a primary-key foreign-key relationship.

VS has a nice editor:

Once I spiked up what datasets were (in simpleDatabaseTests.cs).. used this for reference: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.asp

 [Test] public void put_data_into_a_dataset()
{ DataSet dataset = new DataSet();
SqlDataAdapter data_adapter = new SqlDataAdapter("select
id, name from Artist", connection); SqlCommandBuilder sql_command_builder = new SqlCommandBuilder(data_adapter);
data_adapter.Fill(dataset, "Artist");
Assert.IsNotNull(dataset); DataTable data_table = dataset.Tables[0]; for (int i =< data_table.Rows.Count; i++)
            {
                DataRow data_row  0;
i = data_table.Rows[i]; Console.Out.Write(data_row["id"] + "
"); Console.Out.WriteLine(data_row["name"]);
} }>

The example I'm working through uses a Table Data Gateway Pattern for the DAL.

http://martinfowler.com/eaaCatalog/tableDataGateway.html
"A Table Data Gateway holds all the SQL for accessing a single table or view: selects, inserts, updates, and deletes. Other code calls its methods for all interaction with the database."

Lets take an example of ArtistFixture.cs which tests ArtistGateway.cs

[TestFixture] public class ArtistFixture
{ static readonly string artistName = "Artist";
SqlConnection connection; ArtistGateway gateway; RecordingDataSet recordingDataSet; long artistId;
[SetUp] public void setup_and_open_connection_pass_to_gateway_setup_data_set()
{ connection = new SqlConnection(ConfigurationSettings.AppSettings.Get("Catalog.Connection"));
connection.Open(); recordingDataSet = new RecordingDataSet();
gateway = new ArtistGateway(connection); //
insert a new artist getting its ID from the database artistId = gateway.Insert(recordingDataSet,
artistName); } [Test] public void RetrieveArtistFromDatabase()
{ //
create new RDS, use same gateway. RecordingDataSet loadedFromDB = new RecordingDataSet();
RecordingDataSet.Artist loadedArtist = gateway.FindById(artistId,
loadedFromDB); Assert.AreEqual(artistId, loadedArtist.Id); Assert.AreEqual(artistName,
loadedArtist.Name); }

Here is part of ArtistGateway.cs.. gateway is newed up with a connection passed in.. then only in Insert is it passed in a strongly typed RecordingDataSet, which has all the SQL in it which has been auto-genned.

public class ArtistGateway

{

SqlDataAdapter adapter;

SqlConnection connection;

SqlCommand command;

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 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< 1)  (rows.Length
return null;

return (RecordingDataSet.Artist)
rows[0];

}

>Class Diagram done in VS2008 with the Class Designer Powertoy http://www.codeplex.com/modeling

To get the blue lines go to Class Diagram, Filter Lines, Show All Associations.

Sequence diagram done in Visio: (hmm - this is a bit complex... I prefer the class diagram above with notes)

Genre was next, then the rest. Then relationships between objects eg ReviewReviewer.. just testing the test data we create comes back: Have abstracted out some of the setup code into ConnectionFixture.

 [TestFixture] public class ReviewReviewerFixture
: ConnectionFixture { [Test] public void ReviewerId()
{ RecordingDataSet recordingDataSet = new RecordingDataSet();
ReviewGateway reviewGateway = new ReviewGateway(Connection); long reviewId = reviewGateway.Insert(recordingDataSet,
1, "Review
Content"); ReviewerGateway reviewerGateway = new ReviewerGateway(Connection); long reviewerId = reviewerGateway.Insert(recordingDataSet, "Reviewer
Name"); RecordingDataSet.Review review = reviewGateway.FindById(reviewId,
recordingDataSet); review.ReviewerId = reviewerId;
reviewGateway.Update(recordingDataSet); Assert.AreEqual(reviewerId, review.Reviewer.Id);
reviewGateway.Delete(recordingDataSet, reviewId); reviewerGateway.Delete(recordingDataSet,
reviewerId); } }

The hardest test is RecordingGateway, as it depends on all the other 'tables'. To simplify, we abstract out the insert/del of test data to RecordingBuilder.cs.

The last class is Catalog, which is the only class that will be called from the Service layer. In Catalog we have FindByRecordingId, which returns a RecordingDataSet.Recording (so all the data associated with this recordingId).

In summary we have done this of the overall structure of the APP:

ProgramGood.Net - Database

Delete All Tables in an MSSQL DB

Test Driven Development in .NET - DAL (Ch5)

Data provider

DataSets