/// <summary>
/// Retrieve a collection of column properties for the Sql table.
/// </summary>
/// <returns>Returns a a collection of column properties.</returns>
protected override Hashtable GetColumnProperties()
{
Hashtable columnProperties = new Hashtable();
DataTable columnData = this.TableForDataTypes;
// Retrieve the column properties from the table
foreach (DataColumn dataColumn in columnData.Columns)
{
ColumnProperty columnProperty = new ColumnProperty();
columnProperty.Name = dataColumn.Caption;
columnProperty.DataType = GetSqlDataTypeString(dataColumn.DataType);
columnProperties.Add(columnProperty.Name.ToUpper(), columnProperty);
}
return columnProperties;
}
/// <summary>
/// Retrieve a set of Sql INSERT statements for the data.
/// </summary>
/// <returns>Returns a set of Sql INSERT statements.</returns>
protected override string GetSqlInsert()
{
#region Constants
const string SQL_DATA_TYPE_CHAR = "CHAR";
const string SQL_DATA_TYPE_DATETIME = "DATETIME";
#endregion Constants
StringBuilder sqlInsertTableStatement = new StringBuilder();
// Retrieve the Sql data type associated with each column
Hashtable columnProperties = new Hashtable();
// Build a list of the column names
StringBuilder columnListBuilder = new StringBuilder();
for (int i = 0; i < _data.Columns.Count; i++)
{
string columnName = _data.Columns[i].Caption;
ColumnProperty columnProperty = new ColumnProperty();
columnProperty.Name = columnName;
// Retrieve the appropriate column Xml node
XmlNode columnNode = ((XmlNode)_columnData).SelectSingleNode(string.Format("{0}[@{1}='{2}']", _XPATH_COLUMN, _ATTRIBUTE_NAME_COLUMNNAME, columnName));
if (columnNode != null)
{
// Retrieve the column data type attribute
if (columnNode.Attributes[_ATTRIBUTE_NAME_DATATYPE] != null)
{
string columnDataType = columnNode.Attributes[_ATTRIBUTE_NAME_DATATYPE].Value;
if (columnDataType != null && columnDataType != string.Empty)
{
columnProperty.DataType = columnDataType;
// Retrieve the column name override attribute value if it exists
if (columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAMEOVERRIDE] != null)
{
string columnNameOverride = columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAMEOVERRIDE].Value;
if (columnNameOverride != null && columnNameOverride != string.Empty)
{
columnProperty.NameOverride = columnNameOverride;
}
}
// Retrieve the column date time format attribute value if it exists
if (columnNode.Attributes[_ATTRIBUTE_NAME_DATETIMEFORMAT] != null)
{
string dateTimeFormat = columnNode.Attributes[_ATTRIBUTE_NAME_DATETIMEFORMAT].Value;
if (dateTimeFormat != null && dateTimeFormat != string.Empty)
{
columnProperty.DateTimeFormat = dateTimeFormat;
}
}
}
else
{
throw new Exception(string.Format("Column Xml data type attribute is empty: {0}.", columnName));
}
}
else
{
throw new Exception(string.Format("Column Xml data type attribute not present: {0}.", columnName));
}
}
else
{
throw new Exception(string.Format("Column Xml node not present: {0}.", columnName));
}
string nameToAppend = columnName;
if (columnProperty.NameOverride != null && columnProperty.NameOverride != string.Empty)
{
nameToAppend = columnProperty.NameOverride;
}
columnListBuilder.Append(string.Format("[{0}]", nameToAppend));
if (i < _data.Columns.Count - 1)
{
columnListBuilder.Append(",");
}
columnProperties.Add(columnProperty.Name, columnProperty);
}
string columnList = columnListBuilder.ToString();
// Build the Sql for data
// Generate Sql INSERT statements for each row
foreach (DataRow row in _data.Rows)
{
sqlInsertTableStatement.Append(string.Format("INSERT INTO [{0}] ({1}) VALUES (", _tableName, columnList));
for (int i = 0; i < _data.Columns.Count; i++)
{
string columnName = _data.Columns[i].Caption;
ColumnProperty columnProperty = (ColumnProperty)columnProperties[columnName];
// Ensure NULL's are entered correctly
if (row[i] == null || row[i] == System.DBNull.Value || row[i].ToString() == string.Empty)
{
sqlInsertTableStatement.Append(SQL_NULL);
}
// String data types need to be enclosed in single quotes and quotes in the string need to be escaped
else if (columnProperty.DataType.ToUpper().IndexOf(SQL_DATA_TYPE_CHAR) > -1)
{
sqlInsertTableStatement.Append("'" + row[i].ToString().Replace("'", "''") + "'");
}
// DateTime data types need to be enclosed in single quotes and formatted correctly to avoid localisation issues
else if (columnProperty.DataType.ToUpper() == SQL_DATA_TYPE_DATETIME)
{
string dateTimeValue = SQL_NULL;
try
{
string cellValue = row[i].ToString();
if (cellValue != null && cellValue != string.Empty && cellValue != "0")
{
DateTime dateTime = DateTime.MinValue;
if (columnProperty.DateTimeFormat != null && columnProperty.DateTimeFormat != string.Empty)
{
string[] formats = columnProperty.DateTimeFormat.Split(new char[] {';'});
dateTime = DateTime.ParseExact(cellValue, formats, System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat, System.Globalization.DateTimeStyles.None);
}
else
{
dateTime = Convert.ToDateTime(cellValue);
}
if (dateTime != DateTime.MinValue)
{
dateTimeValue = "'" + dateTime.ToLongDateString() + " " + dateTime.ToLongTimeString() + "'";
}
}
}
catch
{
// Do nothing
}
sqlInsertTableStatement.Append(dateTimeValue);
}
// Number data types do not need to be formatted
else
{
sqlInsertTableStatement.Append(row[i].ToString());
}
if (i < _data.Columns.Count - 1)
{
sqlInsertTableStatement.Append(",");
}
}
sqlInsertTableStatement.Append(")\r\n");
}
return sqlInsertTableStatement.ToString();
}
/// <summary>
/// Retrieve a collection of column properties for the Sql table.
/// </summary>
/// <returns>Returns a a collection of column properties.</returns>
protected override Hashtable GetColumnProperties()
{
Hashtable columnProperties = new Hashtable();
// Retrieve the column properties from the Xml
XmlNodeList columnNodeList = ((XmlNode)_columnData).SelectNodes(_XPATH_COLUMN);
foreach (XmlNode columnNode in columnNodeList)
{
string columnName = string.Empty;
// Determine if a column name override exists first
if (columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAMEOVERRIDE] != null && columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAMEOVERRIDE].Value != string.Empty)
{
columnName = columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAMEOVERRIDE].Value;
}
else if (columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAME] != null && columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAME].Value != string.Empty)
{
columnName = columnNode.Attributes[_ATTRIBUTE_NAME_COLUMNNAME].Value;
}
if (columnName != null && columnName != string.Empty && columnNode.Attributes[_ATTRIBUTE_NAME_DATATYPE] != null && columnNode.Attributes[_ATTRIBUTE_NAME_DATATYPE].Value != string.Empty)
{
ColumnProperty columnProperty = new ColumnProperty();
columnProperty.Name = columnName;
columnProperty.DataType = columnNode.Attributes[_ATTRIBUTE_NAME_DATATYPE].Value;
columnProperties.Add(columnProperty.Name.ToUpper(), columnProperty);
}
}
return columnProperties;
}
/// <summary>
/// Retrieve the properties of a Sql table column.
/// </summary>
/// <param name="rowDescribingTableColumn">Row of data describing Sql table column.</param>
/// <returns>Column property describing the Sql table column.</returns>
private ColumnProperty GetColumnProperty(DataRow rowDescribingTableColumn)
{
#region Constants
const string SQLCOLUMN_COLUMNNAME = "Column_name";
const string SQLCOLUMN_TYPE = "Type";
const string SQLCOLUMN_LENGTH = "Length";
const string SQLCOLUMN_PRECISION = "Prec";
const string SQLCOLUMN_SCALE = "Scale";
const string SQL_DATATYPE_DECIMAL = "DECIMAL";
const string SQL_DATATYPE_VAR = "VAR";
const string SQL_DATATYPE_CHAR = "CHAR";
#endregion Constants
ColumnProperty columnProperty = new ColumnProperty();
// Set the details of the column property
columnProperty.Name = rowDescribingTableColumn[SQLCOLUMN_COLUMNNAME].ToString();
columnProperty.DataType = rowDescribingTableColumn[SQLCOLUMN_TYPE].ToString();
string length = rowDescribingTableColumn[SQLCOLUMN_LENGTH].ToString();
string precision = rowDescribingTableColumn[SQLCOLUMN_PRECISION].ToString().Trim();
string scale = rowDescribingTableColumn[SQLCOLUMN_SCALE].ToString().Trim();
// Set the precision and scale of decimal types
if (columnProperty.DataType.ToUpper() == SQL_DATATYPE_DECIMAL)
{
columnProperty.DataType += "(" + precision + "," + scale + ")";
}
// Set the length of char and var* data types
else if (columnProperty.DataType.ToUpper().IndexOf(SQL_DATATYPE_VAR) >= 0 || columnProperty.DataType.ToUpper().IndexOf(SQL_DATATYPE_CHAR) >= 0)
{
columnProperty.DataType += "(" + length + ")";
}
return columnProperty;
}
