Click here to Skip to main content
11,708,721 members (74,900 online)
Click here to Skip to main content
Articles » Database » Database » Databases » Downloads
Add your own
alternative version

Stop writing connection management code every time you access the database

, 6 Jan 2006 121.2K 2.1K 78
A simple class library for database access without the pervasive and often-fragile connection management details.
using System;
using System.Data;
using System.Data.SqlClient;
using NUnit.Framework;

namespace AMS.ADO.Test
{
	/// <summary>
	///   Base class for the Test classes. </summary>
	public class TestBase
	{
        // This is the name of the database used for the tests.  If it doesn't exist, the constructor creates it.
        private const string DB = "testAMSADO";
        
        // The connection strings below work with your local SQL Server database using Windows Authentication.
        private const string LOCAL_SQLCLIENT_CS = "Data Source=(local);Integrated Security=SSPI";

        internal const string SQLCLIENT_CS =    "Initial Catalog=" + DB + ";" + LOCAL_SQLCLIENT_CS;
        internal const string OLEDB_CS =        "Provider=SQLOLEDB.1;" + SQLCLIENT_CS;
        internal const string ODBC_CS =         "Driver={SQL Server};Server=(local);Database=" + DB;

        /// <summary>
        ///   Constructs the object and creats the test database if it's not present.  </summary>
        /// <remarks>
        ///   Note: This is called once at the beginning of every test batch (which could include multiple methods). </remarks>
        public TestBase()
        {
            using (SqlConnection conn = new SqlConnection(LOCAL_SQLCLIENT_CS))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(
                    @"IF NOT EXISTS (SELECT * FROM dbo.sysdatabases WHERE name = '" + DB + @"')
                        CREATE DATABASE " + DB, conn);
                cmd.ExecuteNonQuery();
            }
        }

		/// <summary>
		///  Initialize() is called once during test execution before test methods in this test class are executed. </summary>
		[SetUp]
		public void Initialize()
		{
			Cleanup();  // remove any existing tables and stored procedures.

            using (SqlConnection conn = new SqlConnection(SQLCLIENT_CS))
			{
				conn.Open();
                SqlCommand cmd = new SqlCommand(
					@"CREATE TABLE dbo.Test1 (ID INT IDENTITY, Description VARCHAR(100))

					INSERT INTO dbo.Test1 SELECT 'Testing' UNION SELECT 'This table may be deleted'", conn);
				cmd.ExecuteNonQuery();

				cmd = new SqlCommand(
					@"CREATE TABLE dbo.Test2 (ID INT IDENTITY, Description VARCHAR(100), Amount INT)

					INSERT INTO dbo.Test2 SELECT 'Testing 2', 10 UNION SELECT '2 This table may also be deleted', 20 UNION SELECT '3rd', 30 UNION SELECT NULL, NULL", conn);
				cmd.ExecuteNonQuery();

				cmd.CommandText =
					@"CREATE PROCEDURE dbo.spCreateTest1(@Description VARCHAR(100)) AS 
						INSERT INTO dbo.Test1 (Description) VALUES (@Description) 
						RETURN @@ROWCOUNT";
				cmd.ExecuteNonQuery();

				cmd.CommandText =
					@"CREATE PROCEDURE dbo.spGetTest1 AS SELECT * FROM dbo.Test1";
				cmd.ExecuteNonQuery();

				cmd.CommandText =
					@"CREATE PROCEDURE dbo.spGetTests1And2 AS 
                      SELECT * FROM dbo.Test1

                      SELECT * FROM dbo.Test2";
				cmd.ExecuteNonQuery();
			}
		}

		/// <summary>
		///  Cleanup() is called once during test execution after test methods in this class have executed unless
		///  this test class' Initialize() method throws an exception. 
        ///  Note: I've removed the [TearDown] attribute from here so that the tables are only dropped
        ///        at the beginning of the test. They're very small tables anyway. </summary>
		public void Cleanup()
		{
            using (SqlConnection conn = new SqlConnection(SQLCLIENT_CS))
			{
                conn.Open();
                SqlCommand cmd = new SqlCommand(
                    @"IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Test1]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
					    DROP TABLE dbo.Test1
					
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Test2]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
					    DROP TABLE dbo.Test2

					IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spCreateTest1]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
					    DROP PROCEDURE dbo.spCreateTest1
					
					IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spGetTest1]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
					    DROP PROCEDURE dbo.spGetTest1

					IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spGetTests1And2]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
					    DROP PROCEDURE dbo.spGetTests1And2", conn);

                cmd.ExecuteNonQuery();
            }
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Alvaro Mendez
Web Developer
United States United States
I've done extensive work with C++, MFC, COM, and ATL on the Windows side. On the Web side, I've worked with VB, ASP, JavaScript, and COM+. I've also been involved with server-side Java, which includes JSP, Servlets, and EJB, and more recently with ASP.NET/C#.

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150819.1 | Last Updated 6 Jan 2006
Article Copyright 2006 by Alvaro Mendez
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid