dbcreatetableoperation.cs

一个通用的数据库访问层

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace YariSoft.DBUtil
{
	public class DBCreateTableOperation : DBSchemaOperation
	{
		#region Local variables
		private DataTable sourceTable = null;
		#endregion

		#region Constructor/Destructor
		public DBCreateTableOperation( OleDbConnection Connection, DataTable SourceTable )
		{
			this.connection = Connection;
			this.sourceTable = SourceTable;
		}
		#endregion

		#region Public functions
		public override bool PrepareSQL()
		{
			bool status = false;
			if( this.connection.Provider.ToUpper().Substring( 0, 8 ) == "SQLOLEDB" ){
				status = this.PrepareMSSQLString();
			} else {
				YariSoft.Utils.YMessages.Show( "Unsupported OLE DB data provider!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
			}
			return status;
		}
		#endregion

		#region Private functions
		private bool PrepareMSSQLString()
		{
			string tempSQL = "CREATE TABLE [" + this.sourceTable.TableName + "]( ";
			foreach( DataColumn column in this.sourceTable.Columns ){
				tempSQL	+= column.ColumnName + " ";
				if( column.DataType == typeof ( System.String ) ){
					if( column.MaxLength > 1000 ){
						tempSQL	+= "text ";
					} else {
						tempSQL	+= "varchar(" + column.MaxLength + ") ";
					}
				} else if ( column.DataType == typeof ( System.Int32 ) ) {
					tempSQL	+= "int ";
				} else if ( column.DataType == typeof ( System.DateTime ) ) {
					tempSQL	+= "datetime ";
				} else if ( column.DataType == typeof ( System.Byte ) || column.DataType == typeof ( System.Boolean ) ) {
					tempSQL	+= "bit ";
				} else if ( column.DataType == typeof ( System.Single ) ) {
					tempSQL	+= "real ";
				} else if ( column.DataType == typeof ( System.Int32 ) ) {
					tempSQL	+= "int ";
				} else if ( column.DataType == typeof ( System.Byte[] ) ) {
					tempSQL	+= "image ";
				} else if ( column.DataType == typeof ( System.Decimal ) ) {
					tempSQL	+= "money ";
				} else if ( column.DataType == typeof ( System.Int16 ) ) {
					tempSQL	+= "smallint ";
				}

				if( column.AllowDBNull ){
					tempSQL	+= "NULL ";
				} else {
					tempSQL	+= "NOT NULL ";
				}

				if( column.AutoIncrement ){
					long autoIncrementSeed = column.AutoIncrementSeed;
					if( autoIncrementSeed == 0 ){
						autoIncrementSeed ++;
					}
					tempSQL	+= "IDENTITY (" + autoIncrementSeed.ToString() +","+ column.AutoIncrementStep.ToString()+") ";
				}
				tempSQL	+= ", ";
			}
			tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ")";
			this.strSQL = tempSQL;

			bool status = this.PrepareMSSQLPrimaryKeys();
	
			return status;
		}

		private bool PrepareMSSQLPrimaryKeys()
		{
			string tempSQL ="";
			DataColumn[] primaryKeys = this.sourceTable.PrimaryKey;
			if( primaryKeys != null && primaryKeys.Length > 0 ){
				tempSQL  = "ALTER TABLE [" + this.sourceTable.TableName + "] ADD CONSTRAINT ";
				tempSQL += "PK_" + this.sourceTable.TableName.Replace( ' ', '_' ) + " PRIMARY KEY ( ";
				foreach ( DataColumn column in primaryKeys ){
					tempSQL	+= column.ColumnName + ", ";
				}
				tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ")";
				this.strSQL += "\r\n" + tempSQL;
			}
			return true; 
		}
		#endregion
	}
}