C# - dataCollector – Collecting and Saving Data Easily

• Download demo project and source - 23.71 KB

Introduction

The other day, I had to make an application that makes new tables in a Microsoft SQL database and save a lot of data into these tables. I couldn't find an easy way to do this in the .NET Framework 2.0. For these reasons, I made a class that collects and saves data in a Microsoft SQL database with SqlBulkCopy. It isn't a very complex solution, but it may save some coding time in similar situations. This project was implemented in C# with VS 2005. The database server was a Microsoft SQL Server 2000.

What Does dataCollector Do?

First, you have to define the schema of the table. You have to choose a .NET Framework type, and a SQL data type too. The dataCollector can create the table in the DB if you want. You could declare the primary key, and auto incremental identity column if needed. The dataCollector makes and runs the required SQL commands (create table; alter table). It also makes a DataTable with the same schema. It will be the temporary storage for your data.

In the second step, you can add new DataRow objects to the dataCollector. If the amount of data rows in the temporary storage reaches the given value, the dataCollector instance inserts these rows in the database table with a SqlBulkCopy, clears the temporary storage and continues receiving data.

When you finish, you have to order the dataCollector to save all unsaved data.

Defining the Schema of Table

Defining the schema of the table is a critical point. A small hidden mistake can cause a lot of anger.

I chose a simple but flexible way for defining the schema. An instance of dc_DataColumnDefinition class (included in the project) represents one column of the table. See the commented code of that class for details. These objects are stored in an ArrayList.

You have to choose the data type of column in SQL table and in DataTable too. Watch the correct and ideal pairing of data types. For example, a string type in C# can be char, nchar, varchar, nvarchar, text, ntext in the database depending on the usage of that column.

The order of dc_DataColumnDefinition instances in the ArrayList defines the order of columns in database table, and in the DataTable.

If you use an existing table in the DB, you have to define the same table for dataCollector! The SqlBulkCopy doesn't check the names, types and orders of the columns. It simply tries to insert the first value into the first field, the second value into the second field, and so on. If you try to insert a text into an integer field, the result will be an empty field, without an error message. The result is the same if you try to write an identity column, nothing will happen - you simply lose your data.

Using the Class

The following commented code shows how does the dataCollector class works. You can find it in the example project.

private void test01()
{
	/*
	 * This example shows the usage of dataCollector class.
	 * Steps:
	 * 1. Take care of database connection string and other basic data.
	 * 2. Make an ArrayList (System.Collections.ArrayList) for holding
	 *   of column definition objects.
	 * 3. Fill this ArrayList with dc.dc_DataColumnDefinition objects.
	 * 4. Make an instance of dc.dataCollector object.
	 * 5. Modify the parameters of dataCollector if you need.
	 * 6. Give datarows for dataCollector
	 * 7. After you finish, save the unsaved data. (it is important!)
	 */

	// 1. Connection string and table information.
	string connString = "Data Source=(local);
		Initial Catalog=Test;Integrated Security=SSPI;";
	string tableOwner = "dbo";
	string tableName = "test01";

	// 2. A standard ArrayList for column definition.
	// Add only dc.dc_DataColumnDefinition objects into this collection
	// The dc.dataCollector instance doesn't verify if there are any types of 
	// objects in the column definition collection or not.
	// Watch the order of dc.dc_DataColumnDefinition objects! (see article)
	ArrayList colList = new ArrayList();

	// 3. Adding column definitions.
	// See the overloads of constructor of dc.dc_DataColumnDefinition object.
	dc.dc_DataColumnDefinition col;
	// Defining the primary key, with identity. 
	col = new dc.dc_DataColumnDefinition(true, "id", 
		System.Type.GetType("System.Int32"), "[int]", true, 1, 1);
	colList.Add(col);
	// A string column. You have to pair the C# and the 
	// SQL data types and declare the
	// length of the SQL data if it is necessary.
	// Examples: [nvarchar](255); [varchar](50); [char](1); [ntext]
	// Watch the correct TSQL syntax...
	col = new dc.dc_DataColumnDefinition
		("str1", System.Type.GetType("System.String"), "[nvarchar](255)");
	colList.Add(col);
	// An integer...
	col = new dc.dc_DataColumnDefinition
		("int1", System.Type.GetType("System.Int32"), "[int]");
	colList.Add(col);

	/*
	 * 4. Creates an instance of dc.dataCollector object.
	 * In constructor you have to give every mandatory information:
	 *  1. Connection string
	 *  2. Owner of table
	 *  3. Name of table
	 *  4. Create the table or not
	 *  5. The filled column definition collection.
	 * You can't modify these values later.
	 */
	//-- don't create the table - try to use the existing one, 
	// and appending new rows.
	//dc.dataCollector dcTest = new dc.dataCollector
	//(connString, tableOwner, tableName, false, colList);
	//-- create the defined table - won't check if it already exists...
	dc.dataCollector dcTest = new dc.dataCollector
				(connString, tableOwner, tableName, true, colList);

	// 5. The dc.dataCollector will save data after every third row.
	// Default is 1000. 
	dcTest.insertPerRows = 3;
	// Shows the create table command text.
	gl.mb(dcTest.createTableCommandText, "Create table command");
	// Shows the primary key creator command text if you defined a primary key.
	if (dcTest.createPKCommandText != "")
	{
		gl.mb(dcTest.createPKCommandText, "Create primary key command");
	}

	// 6. Giving data to dataCollector
	DataRow row;
	row = dcTest.NewRow();
	row[1] = "test string";
	row[2] = 42;
	dcTest.AddDataRow(row);
	row = dcTest.NewRow();
	row[1] = "*** I like Role Playing Games :) ***";
	dcTest.AddDataRow(row);
	row = dcTest.NewRow();
	row[2] = 1;
	dcTest.AddDataRow(row);
	gl.mb("Before you click OK check the table 
		(named '" + dcTest.fullTableName + "'.)"
		+ Environment.NewLine +
		"The dataCollector already saved the first 
				three rows automatically.");
	row = dcTest.NewRow();
	row[1] = "'It is only with the heart that one can see rightly; ...'";
	row[2] = 564;
	dcTest.AddDataRow(row);
	row = dcTest.NewRow();
	row[1] = "'... what is essential is invisible to the eye.' - 
						Antoine de Saint-Exupery";
	dcTest.AddDataRow(row);

	// 7. If you don't call SaveUnsavedDataNow the last group of data
	// will be lost! (After the last running of automatic saving process)
	dcTest.SaveUnsavedDataNow();
	gl.mb("Check the table again. All five rows have been saved.");
	gl.mb("End of example");
}

Code

Commented code of the dataCollector and dc_DataColumnDefinition classes.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace dc
{
	/*
	 * The dataCollector class collets and saves the data rows. And it can
	 * create new table for the data.
	 */
	class dataCollector
	{
		// The class makes bulk copy when that amount of
		// rows gathered.
		public int insertPerRows = 1000;
		// It is the SqlBulkCopy.BatchSizeInBulkCopy property.
		// If this value is too high, it could cause timeout problems.
		public int BatchSizeInBulkCopy = 1000;
		private DataTable dt;
		private SqlConnection conn;
		// The constructor of class, the only way to create an instance
		// of dataCollector class. Using space or special characters in
		// table or owner name is not recommended.
		public dataCollector(string sqlConnString, string tableOwner, 
			string tableName, bool createThisTable, ArrayList dataColDef)
		{
			connString_i = sqlConnString;
			tableName_i = tableName;
			tableOwner_i = tableOwner;
			createPKCommandText_i = "";
			// This DataTable will store the data.
			dt = new DataTable(tableName);
			// Begriming of create table command text.
			createTableCommandText_i = 
			"CREATE TABLE " + fullTableName + " (" + Environment.NewLine;
			// Checking the column definition collection:
			//  1. Generating Column objects in dt.
			//  2. Adding column definition text to create command text.
			//  3. Making primary key generator command text, 
			//  if necessary.
			// Warning! This class can add only one 
			// primary key to a single column.
			// If you defined more, only the last one will added.
			foreach (dc_DataColumnDefinition colDef in dataColDef)
			{
				// adds the column to the DataTable
				dt.Columns.Add(colDef.columnName, 
				colDef.columnType);
				// adds the column definition 
				// to the Create Table command
				createTableCommandText_i += 
				"\t[" + colDef.columnName + "] " + colDef.sqlType;
				if (colDef.sqlIdentity)
				{
					//IDENTITY(1,1) NOT NULL
					createTableCommandText_i += " IDENTITY
					(" + colDef.sqlIdentitySeed + "," +
					colDef.sqlIdentityIncrement + ") NOT NULL";
				}
				else
				{
					if (colDef.sqlNullIsAllowed)
					{
						createTableCommandText_i += 
								" NULL";
					}
					else
					{
						createTableCommandText_i += 
								" NOT NULL";
					}
				}
				createTableCommandText_i += "," + 
							Environment.NewLine;
				// primary key producing command
				if (colDef.primaryKey)
				{
					createPKCommandText_i = "ALTER TABLE " + 
					fullTableName + Environment.NewLine +
					" ADD CONSTRAINT [PK_" + tableName + "] 
					PRIMARY KEY CLUSTERED" + 
					Environment.NewLine +
					"([" + colDef.columnName + "])";
				}
			}
			// end of create table command
			createTableCommandText_i = createTableCommandText_i.Remove
				(createTableCommandText_i.Length - 
				Environment.NewLine.Length - 1);
			createTableCommandText_i += ")";
			// Making the SQL connection
			conn = new SqlConnection(this.connString);
			// Running create table command if needed.
			if (createThisTable)
			{
				SqlCommand sqlComm = 
				new SqlCommand(createTableCommandText_i, conn);
				conn.Open();
				sqlComm.ExecuteNonQuery();
				// Adding primary key constraint if needed.
				if (createPKCommandText != "")
				{
					SqlCommand sqlCommPK = 
					new SqlCommand(createPKCommandText, conn);
					sqlCommPK.ExecuteNonQuery();
				}
				conn.Close();
			}
		}

		/*
		 * I tested this destructor, but it wasn't work perfectly in my tests.
		 * Sometimes it causes runtime error. Other times it worked correctly.
		 * I tried to save the last batch of data this way, 
		 * if the user doesn't save
		 * them manually, but I failed. So don't' forget 
		 * call SaveUnsavedDataNow in
		 * the end of data collecting...
		 */
		//~dataCollector()
		//{
		//    savingDataToDB();
		//}

		// Gives a new DataRow object with the given
		// column definition.
		// Warning! This method only gives an empty DataRow object.
		// You have to fill the data fields of its object, and finally
		// call the AddDataRow method (see bellow).
		public DataRow NewRow()
		{
			return dt.NewRow();
		}
		// This method put the filled DataRow in the DataTable.
		// (It collects data in memory before 
		// these are sent to the SQL server)
		// When the number of rows in this "temporary table" reaches the given
		// limit (insertPerRows) calls the savingDataToDB method, what will
		// save all rows to the SQL server.
		public void AddDataRow(DataRow row)
		{
			dt.Rows.Add(row);
			if (dt.Rows.Count >= insertPerRows)
			{
				savingDataToDB();
			}
		}

		// Save all unsaved data immediately.
		// WARNING! Always call this method when you finish the
		// work with this class!
		public void SaveUnsavedDataNow()
		{
			savingDataToDB();
		}

		// Save all unsaved data to the SQL server with SqlBulkCopy,
		// and clears the rows from DataTable.
		private void savingDataToDB()
		{
			if (dt.Rows.Count > 0)
			{
				// Copies all rows to the database 
				// from the DataTable.
				using (SqlBulkCopy bc = new SqlBulkCopy
							(this.connString))
				{
					// Number of rows in each batch.
					bc.BatchSize = BatchSizeInBulkCopy;
					// Destination table with owner.
					bc.DestinationTableName = fullTableName;
					// Starts the bulk copy.
					bc.WriteToServer(dt);
					// Closes the SqlBulkCopy instance
					bc.Close();
				}
				// Clears all rows from DataTable
				dt.Rows.Clear();
			}
		}

		// Returns the actual amount of unsaved rows
		public int ActualRowCount
		{
			get { return dt.Rows.Count; }
		}
		// Clears all stored data rows. All data will be lost.
		public void ClearRowsWithoutSaving()
		{
			dt.Rows.Clear();
		}

		// Returns the connection string.
		private string connString_i;
		public string connString
		{
			get { return connString_i; }
			//set { connString_i = value; }
		}

		// Returns the owner of the database table.
		private string tableOwner_i;
		public string tableOwner
		{
			get { return tableOwner_i; }
			//set { tableOwner_i = value; }
		}
		// Returns the name of the database table.
		private string tableName_i;
		public string tableName
		{
			get { return tableName_i; }
			//set { tableName_i = value; }
		}
		// Makes and returns the full name of table.
		// For example: [dbo].[table]
		public string fullTableName
		{
			get { return "[" + tableOwner + "].[" + tableName + "]"; }
		}

		// Stores and returns the text of create table command
		private string createTableCommandText_i;
		public string createTableCommandText
		{
			get { return createTableCommandText_i; }
			//set { createTableCommandText_i = value; }
		}
		// Stores and returns the text of the command what gives
		// primary key to the table.
		private string createPKCommandText_i;
		public string createPKCommandText
		{
			get { return createPKCommandText_i; }
			//set { createPKCommandText_i = value; }
		}
	}

	/*
	 * The dc_DataColumnDefinition class represents the definition of
	 * a data column. The dataCollector class needs a collection of this
	 * class. It describes them the columns of DataTable object, and the
	 * columns of the database table.
	 * 
	 * There are many constructors of this class. These help you to
	 * define column easily.
	 */
	class dc_DataColumnDefinition
	{
		// If this column is the primary key. Only one PK column
		// will be added, the last one.
		public bool primaryKey;
		// Name of the column in the database, 
		// and in the temporary table (DataTable).
		public string columnName;
		// .NET Framework type - type in DataTable
		public System.Type columnType;
		// MS SQL type
		public string sqlType;
		// If NULL value is accepted in SQL or not.
		public bool sqlNullIsAllowed;
		// If this column is an identity column in the SQL table.
		// If it is, the SQL server gives an incremental ID when
		// the row inserted.
		public bool sqlIdentity;
		// The seed of increment.
		public int sqlIdentitySeed;
		// The amount of increment.
		public int sqlIdentityIncrement;


		// Empty constructor: you have to fill all parameters
		public dc_DataColumnDefinition()
		{
		}
		// A typical and normal column: only the name, 
		// and the two types are significant.
		public dc_DataColumnDefinition(string columnName, 
			System.Type columnType,
			string sqlType)
		{
			this.primaryKey = false;
			this.columnName = columnName;
			this.columnType = columnType;
			this.sqlType = sqlType;
			this.sqlNullIsAllowed = true;
			this.sqlIdentity = false;
			this.sqlIdentitySeed = 0;
			this.sqlIdentityIncrement = 0;
		}

		// Previous + opportunity to give the NOT NULL restriction
		public dc_DataColumnDefinition(string columnName, 
			System.Type columnType,
			string sqlType, bool sqlNullIsAllowed)
		{
			this.primaryKey = false;
			this.columnName = columnName;
			this.columnType = columnType;
			this.sqlType = sqlType;
			this.sqlNullIsAllowed = sqlNullIsAllowed;
			this.sqlIdentity = false;
			this.sqlIdentitySeed = 0;
			this.sqlIdentityIncrement = 0;
		}
		// Primary key without identity
		public dc_DataColumnDefinition(bool primaryKey, string columnName,
			System.Type columnType, string sqlType)
		{
			this.primaryKey = primaryKey;
			this.columnName = columnName;
			this.columnType = columnType;
			this.sqlType = sqlType;
			this.sqlNullIsAllowed = false;
			this.sqlIdentity = false;
			this.sqlIdentitySeed = 0;
			this.sqlIdentityIncrement = 0;
		}
		// Primary key with identity
		public dc_DataColumnDefinition(bool primaryKey, 
			string columnName, System.Type columnType,
			string sqlType, bool sqlIdentity,
			int sqlIdentitySeed, int sqlIdentityIncrement)
		{
			this.primaryKey = primaryKey;
			this.columnName = columnName;
			this.columnType = columnType;
			this.sqlType = sqlType;
			this.sqlNullIsAllowed = false;
			this.sqlIdentity = sqlIdentity;
			this.sqlIdentitySeed = sqlIdentitySeed;
			this.sqlIdentityIncrement = sqlIdentityIncrement;
		}
	}
}

History

• 20^th April, 2009: Initial post