public static SqlCommand GetMergeCommand(SqlConnection connection, IList <SqlColumnInfo> sourceColumnInfos, string destinationTableName)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = SqlScriptHelper.GenerateMergeScript(null, sourceColumnInfos, destinationTableName);
foreach (SqlColumnInfo sourceColumnInfo in sourceColumnInfos)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = @"{sourceColumnInfo.Name}";
parameter.SqlDbType = SqlDatabaseHelper.GetDbType(sourceColumnInfo.Type);
command.Parameters.Add(parameter);
}
return(command);
}
public static SqlColumnInfo[] GetColumnInfos(SqlConnection connection, string tableName)
{
bool isTempTable = tableName.StartsWith("#");
// Get Primary key
List <string> primaryKeys = new List <string>();
if (!isTempTable)
{
using (SqlCommand loadPrimaryKeysCommand = connection.CreateCommand())
{
loadPrimaryKeysCommand.CommandText = SqlScriptHelper.GenerateLoadTablePrimaryKeyScript(tableName);
using (SqlDataReader reader = loadPrimaryKeysCommand.ExecuteReader())
{
while (reader.Read())
{
primaryKeys.Add(Convert.ToString(reader[0]));
}
}
}
}
// Get Column Info
List <SqlColumnInfo> columnInfos = new List <SqlColumnInfo>();
using (SqlCommand loadColumnInfosCommand = connection.CreateCommand())
{
if (!isTempTable)
{
loadColumnInfosCommand.CommandText = SqlScriptHelper.GenerateLoadTableColumnsScript(tableName);
}
else
{
loadColumnInfosCommand.CommandText = SqlScriptHelper.GenerateLoadTempTableColumnsScript(tableName);
}
using (SqlDataReader reader = loadColumnInfosCommand.ExecuteReader())
{
while (reader.Read())
{
SqlColumnInfo columnInfo = new SqlColumnInfo();
columnInfo.Name = Convert.ToString(reader["Name"]);
columnInfo.Type = (SqlColumnType)Enum.Parse(typeof(SqlColumnType), Convert.ToString(reader["Type"]));
switch (columnInfo.Type)
{
// None
case SqlColumnType.@image:
case SqlColumnType.@text:
case SqlColumnType.@uniqueidentifier:
case SqlColumnType.@date:
case SqlColumnType.@tinyint:
case SqlColumnType.@smallint:
case SqlColumnType.@int:
case SqlColumnType.@smalldatetime:
case SqlColumnType.@real:
case SqlColumnType.@money:
case SqlColumnType.@datetime:
case SqlColumnType.@float:
case SqlColumnType.@sql_variant:
case SqlColumnType.@ntext:
case SqlColumnType.@bit:
case SqlColumnType.@smallmoney:
case SqlColumnType.@bigint:
case SqlColumnType.@hierarchyid:
case SqlColumnType.@geometry:
case SqlColumnType.@geography:
case SqlColumnType.@timestamp:
case SqlColumnType.@xml:
break;
// percious & scale
case SqlColumnType.@decimal:
case SqlColumnType.@numeric:
{
columnInfo.Precision = Convert.ToInt32(reader["Precision"]);
columnInfo.Scale = Convert.ToInt32(reader["Scale"]);
}
break;
// Scale
case SqlColumnType.@time:
case SqlColumnType.@datetime2:
case SqlColumnType.@datetimeoffset:
{
columnInfo.Scale = Convert.ToInt32(reader["Scale"]);
}
break;
// Unicode char
case SqlColumnType.@nvarchar:
case SqlColumnType.@nchar:
{
columnInfo.Length = Convert.ToInt32(reader["Length"]);
if (columnInfo.Length > 0)
{
columnInfo.Length /= 2;
}
}
break;
// Ansi char
case SqlColumnType.@varchar:
case SqlColumnType.@char:
{
columnInfo.Length = Convert.ToInt32(reader["Length"]);
}
break;
// Binary
case SqlColumnType.@varbinary:
case SqlColumnType.@binary:
{
columnInfo.Length = Convert.ToInt32(reader["Length"]);
}
break;
// Other
case SqlColumnType.@sysname:
break;
default:
break;
}
columnInfo.IsPrimaryKey = primaryKeys.Any(p => p.Equals(Convert.ToString(reader["Name"]), StringComparison.OrdinalIgnoreCase));
columnInfo.Nullable = Convert.ToBoolean(reader["Nullable"]);
columnInfo.Collation = !Convert.IsDBNull(reader["Collation"]) ? Convert.ToString(reader["Collation"]) : null;
columnInfos.Add(columnInfo);
}
}
}
return(columnInfos.ToArray());
}
