public static bool IsBoolean(this TBColumn column)
{
CheckIsSupport(column);
var colEnum = Enum.Parse(typeof(MSSQLTypeEnum), column.TypeName, true);
return(colEnum.Equals(MSSQLTypeEnum.Bit));
}
public static bool IsDateTime(this TBColumn column)
{
CheckIsSupport(column);
var oraclecolumn = Common.Data.Common.GetOracleColumnType().FirstOrDefault(p => p.TypeName.Equals(column.TypeName, StringComparison.OrdinalIgnoreCase));
return(oraclecolumn.IsTime);
}
private static void CheckIsSupport(TBColumn column)
{
if (!IsSupport(column))
{
throw new Exception("不支持的类型" + column.TypeName);
}
}
public static bool IsDateTime(this TBColumn column)
{
CheckIsSupport(column);
var colEnum = Enum.Parse(typeof(MSSQLTypeEnum), column.TypeName, true);
return(colEnum.Equals(MSSQLTypeEnum.Datetime) ||
colEnum.Equals(MSSQLTypeEnum.Datetime2) ||
colEnum.Equals(MSSQLTypeEnum.Smalldatetime) ||
colEnum.Equals(MSSQLTypeEnum.Time));
}
private static bool IsSupport(TBColumn column)
{
MSSQLTypeEnum outEnum;
if (Enum.TryParse(column.TypeName, true, out outEnum))
{
return(true);
}
return(false);
}
public static bool IsString(this TBColumn column)
{
CheckIsSupport(column);
var colEnum = Enum.Parse(typeof(MSSQLTypeEnum), column.TypeName, true);
return(colEnum.Equals(MSSQLTypeEnum.Xml) ||
colEnum.Equals(MSSQLTypeEnum.Char) ||
colEnum.Equals(MSSQLTypeEnum.NChar) ||
colEnum.Equals(MSSQLTypeEnum.NText) ||
colEnum.Equals(MSSQLTypeEnum.NVarChar) ||
colEnum.Equals(MSSQLTypeEnum.Text) ||
colEnum.Equals(MSSQLTypeEnum.Varchar));
}
private static bool IsSupport(TBColumn column)
{
var oraclecolumn = Common.Data.Common.GetOracleColumnType().FirstOrDefault(p => p.TypeName.Equals(column.TypeName, StringComparison.OrdinalIgnoreCase));
//MSSQLTypeEnum outEnum;
//if (Enum.TryParse(column.TypeName,true, out outEnum))
//{
// return true;
//}
//return false;
return(oraclecolumn != null);
}
public static bool IsNumber(this TBColumn column)
{
CheckIsSupport(column);
var colEnum = Enum.Parse(typeof(MSSQLTypeEnum), column.TypeName, true);
return(colEnum.Equals(MSSQLTypeEnum.Bigint) ||
colEnum.Equals(MSSQLTypeEnum.Decimal) ||
colEnum.Equals(MSSQLTypeEnum.Float) ||
colEnum.Equals(MSSQLTypeEnum.Int) ||
colEnum.Equals(MSSQLTypeEnum.Money) ||
colEnum.Equals(MSSQLTypeEnum.Numeric) ||
colEnum.Equals(MSSQLTypeEnum.Real) ||
colEnum.Equals(MSSQLTypeEnum.Smallint) ||
colEnum.Equals(MSSQLTypeEnum.Smallmoney) ||
colEnum.Equals(MSSQLTypeEnum.Tinyint));
}
public static string TypeToString(this TBColumn column)
{
CheckIsSupport(column);
var oraclecolumn = Common.Data.Common.GetOracleColumnType().FirstOrDefault(p => p.TypeName.Equals(column.TypeName, StringComparison.OrdinalIgnoreCase));
if (oraclecolumn.LenAble)
{
return(string.Format("{0}({1})", oraclecolumn.TypeName, oraclecolumn.DefaultLen));
}
else if (oraclecolumn.ScaleAble)
{
return(string.Format("{0}({1},{2})", oraclecolumn.TypeName, oraclecolumn.DefaultPrecision, oraclecolumn.DefaultScale));
}
else
{
return(oraclecolumn.TypeName);
}
}
public static string GetDBType(TBColumn col)
{
switch (col.TypeName.ToLower())
{
case "varchar":
case "char":
case "nvarchar":
case "nchar":
return(string.Concat(col.TypeName, "(", col.Length, ")"));
case "numeric":
case "money":
case "decimal":
case "float":
return(string.Concat(col.TypeName, "(", col.prec, ",", col.scale, ")"));
default:
return(col.TypeName);
}
}
public static string ToDBType(this TBColumn col)
{
switch (col.TypeName.ToLower())
{
case "varchar":
case "char":
case "nchar":
return(string.Concat(col.TypeName, "(", col.Length == -1 ? "max" : col.Length.ToString(), ")"));
case "nvarchar":
return(string.Concat(col.TypeName, "(", col.Length == -1 ? "max" : (col.Length == 8000 ? "4000" : col.Length.ToString()), ")"));
case "numeric":
case "money":
case "decimal":
case "float":
return(string.Concat(col.TypeName, "(", col.prec, ",", col.scale, ")"));
default:
return(col.TypeName);
}
}
public static string TypeToString(this TBColumn column)
{
CheckIsSupport(column);
var colEnum = Enum.Parse(typeof(MSSQLTypeEnum), column.TypeName, true);
if (colEnum.Equals(MSSQLTypeEnum.Char) ||
colEnum.Equals(MSSQLTypeEnum.Datetime2) ||
colEnum.Equals(MSSQLTypeEnum.NChar) ||
colEnum.Equals(MSSQLTypeEnum.NVarChar) ||
colEnum.Equals(MSSQLTypeEnum.Time) ||
colEnum.Equals(MSSQLTypeEnum.Varchar))
{
return(string.Format("{0}({1})", column.TypeName, column.Length == -1?"MAX":column.Length.ToString()));
}
else if (colEnum.Equals(MSSQLTypeEnum.Decimal) ||
colEnum.Equals(MSSQLTypeEnum.Numeric))
{
return(string.Format("{0}({1},{2})", column.TypeName, column.prec, column.scale));
}
else
{
return(column.TypeName);
}
}
public static bool IsBoolean(this TBColumn column)
{
CheckIsSupport(column);
return(false);
}
private void BtnCheckDest_Click(object sender, EventArgs e)
{
try
{
var fields = GetDestFields;
StringBuilder sb = new StringBuilder($"if object_id('{GetDestTBName}') is null");
sb.AppendLine();
sb.AppendLine("begin");
sb.AppendLine($"CREATE TABLE [{GetDestTBName}](");
using (SqlConnection destconn = new SqlConnection(GetDestConnStr))
{
bool exist = false;
destconn.Open();
using (var cmd0 = destconn.CreateCommand())
{
cmd0.CommandType = CommandType.Text;
cmd0.CommandText = $@"if object_id('{GetDestTBName}') is not null
select 0
else
select 1";
exist = cmd0.ExecuteScalar().ToString() == "0";
}
using (SqlConnection conn = new SqlConnection(GetSourceConnStr))
{
using (var cmd = conn.CreateCommand())
{
if (!exist)
{
cmd.CommandText = GetTableColumnsMetaDataSQL; //$"select top 1 {string.Join(",",GetDestFields)} from {GetSourceTBName}";
cmd.Parameters.Add(new SqlParameter("@name", GetSourceTBName));
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable tb = new DataTable();
adp.Fill(tb);
for (int i = 0; i < tb.Rows.Count; i++)
{
var name = tb.Rows[i]["name"].ToString();
if (!fields.Contains(name))
{
continue;
}
var col = new TBColumn
{
IsKey = name == GetKeyField,
Length = int.Parse(tb.Rows[i]["length"].ToString()),
Name = tb.Rows[i]["name"].ToString(),
TypeName = tb.Rows[i]["type"].ToString(),
IsNullAble = tb.Rows[i]["isnullable"].ToString().Equals("1"),
prec = tb.Rows[i]["prec"].CovertToInt(),
scale = tb.Rows[i]["scale"].CovertToInt()
};
sb.AppendFormat("[{0}] {1} {2} ,", col.Name, col.ToDBType(), (col.IsID || col.IsKey) ? "NOT NULL" : (col.IsNullAble ? "NULL" : "NOT NULL"));
sb.AppendLine();
}
sb.AppendLine(")");
sb.AppendLine($"alter table {GetDestTBName} add constraint PK_{GetDestTBName}_1 primary key({GetKeyField})");
sb.AppendLine("end");
using (var cmd1 = destconn.CreateCommand())
{
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = sb.ToString();
var ret = cmd1.ExecuteNonQuery();
MessageBox.Show("创建成功");
}
}
else
{
var sql = $"select top 0 * from {GetDestTBName}";
using (var cmd2 = destconn.CreateCommand())
{
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = sql;
var table = new DataTable();
new SqlDataAdapter(cmd2).Fill(table);
for (int i = 0; i < CBFields.Items.Count; i++)
{
CBFields.SetItemChecked(i, table.Columns.Contains(CBFields.Items[i].ToString()));
}
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}