/// <summary>
/// Creates the length portion of the specified column.
/// </summary>
/// <param name="column">Object that stores the information for the column the parameter represents.</param>
/// <returns>String containing length information for the specific column.</returns>
public static string GetParameterLength(Column column)
{
if (column.Length == "-1")
{
return "max";
}
return column.Length;
}
/// <summary>
/// Creates a string for the default value of a column's data type.
/// </summary>
/// <param name="column">The column to get a default value for.</param>
/// <returns>The default value for the column.</returns>
public static string GetDefaultValue(Column column)
{
switch (column.Type.ToLower())
{
case "binary":
return "new byte[0]";
case "bigint":
return "0";
case "bit":
return "false";
case "char":
return "String.Empty";
case "datetime":
return "new DateTime(0)";
case "decimal":
return "Decimal.Zero";
case "float":
return "0.0F";
case "image":
return "new byte[0]";
case "int":
return "0";
case "money":
return "Decimal.Zero";
case "nchar":
return "String.Empty";
case "ntext":
return "String.Empty";
case "nvarchar":
return "null";
case "numeric":
return "Decimal.Zero";
case "real":
return "Decimal.Zero";
case "smalldatetime":
return "DateTime.Now";
case "smallint":
return "0";
case "smallmoney":
return "0.0F";
case "sql_variant":
return "new byte[0]";
case "sysname":
return "null";
case "text":
return "null";
case "timestamp":
return "DateTime.Now";
case "tinyint":
return "0x00";
case "varbinary":
return "new byte[0]";
case "varchar":
return "null";
case "uniqueidentifier":
return "Guid.Empty";
case "xml":
return "null";
default: // Unknow data type
throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
}
}
/// <summary>
/// Creates the GetXxx method to use for the specified column.
/// </summary>
/// <param name="column">The column to retrieve data for.</param>
/// <returns>The name of the method to call on a SqlDataReader for the specified column.</returns>
public static string GetGetMethod(Column column)
{
switch (column.Type.ToLower())
{
case "binary":
return "GetBytes";
case "bigint":
return "GetInt64";
case "bit":
return "GetBoolean";
case "char":
return "GetString";
case "datetime":
return "GetDateTime";
case "decimal":
return "GetDecimal";
case "float":
return "GetFloat";
case "image":
return "GetBytes";
case "int":
return "GetInt32";
case "money":
return "GetDecimal";
case "nchar":
return "GetString";
case "ntext":
return "GetString";
case "nvarchar":
return "GetString";
case "numeric":
return "GetDecimal";
case "real":
return "GetDecimal";
case "smalldatetime":
return "GetDateTime";
case "smallint":
return "GetIn16";
case "smallmoney":
return "GetFloat";
case "sql_variant":
return "GetBytes";
case "sysname":
return "GetString";
case "text":
return "GetString";
case "timestamp":
return "GetDateTime";
case "tinyint":
return "GetByte";
case "varbinary":
return "GetBytes";
case "varchar":
return "GetString";
case "uniqueidentifier":
return "GetGuid";
case "xml":
return "GetString";
default: // Unknow data type
throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
}
}
/// <summary>
/// Creates the name of the method to call on a SqlDataReader for the specified column.
/// </summary>
/// <param name="column">The column to retrieve data for.</param>
/// <returns>The name of the method to call on a SqlDataReader for the specified column.</returns>
public static string GetCsType(Column column)
{
switch (column.Type.ToLower())
{
case "binary":
return "byte[]";
case "bigint":
return "long";
case "bit":
return "bool";
case "char":
return "string";
case "datetime":
return "DateTime";
case "decimal":
return "decimal";
case "float":
return "float";
case "image":
return "byte[]";
case "int":
return "int";
case "money":
return "decimal";
case "nchar":
return "string";
case "ntext":
return "string";
case "nvarchar":
return "string";
case "numeric":
return "decimal";
case "real":
return "decimal";
case "smalldatetime":
return "DateTime";
case "smallint":
return "short";
case "smallmoney":
return "float";
case "sql_variant":
return "byte[]";
case "sysname":
return "string";
case "text":
return "string";
case "timestamp":
return "DateTime";
case "tinyint":
return "byte";
case "varbinary":
return "byte[]";
case "varchar":
return "string";
case "uniqueidentifier":
return "Guid";
case "xml":
return "string";
default: // Unknow data type
throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
}
}
/// <summary>
/// Creates a string containing the parameter declaration for a stored procedure based on the parameters passed in.
/// </summary>
/// <param name="column">Object that stores the information for the column the parameter represents.</param>
/// <param name="checkForOutputParameter">Indicates if the created parameter should be checked to see if it should be created as an output parameter.</param>
/// <returns>String containing parameter information of the specified column for a stored procedure.</returns>
public static string CreateParameterString(Column column, bool checkForOutputParameter)
{
string parameter;
switch (column.Type.ToLower())
{
case "binary":
parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
break;
case "bigint":
parameter = "@" + column.Name + " " + column.Type;
break;
case "bit":
parameter = "@" + column.Name + " " + column.Type;
break;
case "char":
parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
break;
case "datetime":
parameter = "@" + column.Name + " " + column.Type;
break;
case "decimal":
if (column.Scale.Length == 0)
parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";
else
parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ", " + column.Scale + ")";
break;
case "float":
parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";
break;
case "image":
parameter = "@" + column.Name + " " + column.Type;
break;
case "int":
parameter = "@" + column.Name + " " + column.Type;
break;
case "money":
parameter = "@" + column.Name + " " + column.Type;
break;
case "nchar":
parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
break;
case "ntext":
parameter = "@" + column.Name + " " + column.Type;
break;
case "nvarchar":
parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
break;
case "numeric":
if (column.Scale.Length == 0)
parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";
else
parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ", " + column.Scale + ")";
break;
case "real":
parameter = "@" + column.Name + " " + column.Type;
break;
case "smalldatetime":
parameter = "@" + column.Name + " " + column.Type;
break;
case "smallint":
parameter = "@" + column.Name + " " + column.Type;
break;
case "smallmoney":
parameter = "@" + column.Name + " " + column.Type;
break;
case "sql_variant":
parameter = "@" + column.Name + " " + column.Type;
break;
case "sysname":
parameter = "@" + column.Name + " " + column.Type;
break;
case "text":
parameter = "@" + column.Name + " " + column.Type;
break;
case "timestamp":
parameter = "@" + column.Name + " " + column.Type;
break;
case "tinyint":
parameter = "@" + column.Name + " " + column.Type;
break;
case "varbinary":
parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
break;
case "varchar":
parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
break;
case "uniqueidentifier":
parameter = "@" + column.Name + " " + column.Type;
break;
case "xml":
parameter = "@" + column.Name + " " + column.Type;
break;
default: // Unknow data type
throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
}
// Return the new parameter string
if (checkForOutputParameter && (column.IsIdentity || column.IsRowGuidCol))
{
return parameter + " OUTPUT";
}
else
{
return parameter;
}
}
/// <summary>
/// Creates a string for a method parameter representing the specified column.
/// </summary>
/// <param name="column">Object that stores the information for the column the parameter represents.</param>
/// <returns>String containing parameter information of the specified column for a method call.</returns>
public static string CreateMethodParameter(Column column)
{
return GetCsType(column) + " " + FormatCamel(column.Name);;
}
/// <summary>
/// Creates a string for a SqlParameter representing the specified column.
/// </summary>
/// <param name="column">Object that stores the information for the column the parameter represents.</param>
/// <returns>String containing SqlParameter information of the specified column for a method call.</returns>
public static string CreateSqlParameter(Table table, Column column)
{
string className = Utility.FormatClassName(table.Name);
string variableName = Utility.FormatVariableName(className);
if (column.Type == "xml")
{
return "new SqlParameter(\"@" + column.Name + "\", SqlDbType.Xml) { Value = " + variableName + "." + FormatPascal(column.Name) + " }";
}
else
{
return "new SqlParameter(\"@" + column.Name + "\", " + variableName + "." + FormatPascal(column.Name) + ")";
}
}
/// <summary>
/// Retrieves the column, primary key, and foreign key information for the specified table.
/// </summary>
/// <param name="connection">The SqlConnection to be used when querying for the table information.</param>
/// <param name="table">The table instance that information should be retrieved for.</param>
private static void QueryTable(SqlConnection connection, Table table)
{
// Get a list of the entities in the database
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(Utility.GetColumnQuery(table.Name), connection);
dataAdapter.Fill(dataTable);
foreach (DataRow columnRow in dataTable.Rows)
{
Column column = new Column();
column.Name = columnRow["COLUMN_NAME"].ToString();
column.Type = columnRow["DATA_TYPE"].ToString();
column.Precision = columnRow["NUMERIC_PRECISION"].ToString();
column.Scale = columnRow["NUMERIC_SCALE"].ToString();
// Determine the column's length
if (columnRow["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
column.Length = columnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();
}
else
{
column.Length = columnRow["COLUMN_LENGTH"].ToString();
}
// Is the column a RowGuidCol column?
if (columnRow["IS_ROWGUIDCOL"].ToString() == "1")
{
column.IsRowGuidCol = true;
}
// Is the column an Identity column?
if (columnRow["IS_IDENTITY"].ToString() == "1")
{
column.IsIdentity = true;
}
// Is columnRow column a computed column?
if (columnRow["IS_COMPUTED"].ToString() == "1")
{
column.IsComputed = true;
}
table.Columns.Add(column);
}
// Get the list of primary keys
DataTable primaryKeyTable = Utility.GetPrimaryKeyList(connection, table.Name);
foreach (DataRow primaryKeyRow in primaryKeyTable.Rows)
{
string primaryKeyName = primaryKeyRow["COLUMN_NAME"].ToString();
foreach (Column column in table.Columns)
{
if (column.Name == primaryKeyName)
{
table.PrimaryKeys.Add(column);
break;
}
}
}
// Get the list of foreign keys
DataTable foreignKeyTable = Utility.GetForeignKeyList(connection, table.Name);
foreach (DataRow foreignKeyRow in foreignKeyTable.Rows)
{
string name = foreignKeyRow["FK_NAME"].ToString();
string columnName = foreignKeyRow["FKCOLUMN_NAME"].ToString();
if (table.ForeignKeys.ContainsKey(name) == false)
{
table.ForeignKeys.Add(name, new List<Column>());
}
List<Column> foreignKeys = table.ForeignKeys[name];
foreach (Column column in table.Columns)
{
if (column.Name == columnName)
{
foreignKeys.Add(column);
break;
}
}
}
}
