ProgramGood.Net

Use SQL Server Management Studio to generate scripts.

System.OutOfMemoryException – when you get this trying to run a query in SQL Server Management Studio

sqlcmd -S YOURSQLSERVER\INSTANCENAME -i "C:\Your Script.sql”

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

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.

 [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 = 0; i < data_table.Rows.Count; i++)
            {
                DataRow data_row = data_table.Rows[i];
                Console.Out.Write(data_row["id"] + " ");
                Console.Out.WriteLine(data_row["name"]);
            }
        }

[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);
        }

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

 [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);
     }
 }