将C#类转换为SQL表 [英] Convert C# Class to SQL table
问题描述
我可以找到的唯一参考文献是建议使用实体框架来执行此操作，但是我没有或没有使用实体框架.关于该主题的另一件事是从SQL表转到C#类，这与我想要的相反.
The only references I can find is someone suggesting to use Entity Framework to do this, but I don't have or use Entity Framework. The only other thing on this subject is going from SQL table to C# class, which is the opposite of what I want.
这不必与TSQL/MSSQL(2014)兼容，但这就是我的DBMS.
This doesn't have to be TSQL/MSSQL (2014) Compatible but that's my DBMS.
C#类只是POCO.我想我听说您可以选择该类，然后以某种方式将其转换为DataTable，然后使用SqlBulkCopy使其从DataTable创建一个表.
The C# class is just a POCO. I think I heard you could take the class and somehow convert it a DataTable and then using SqlBulkCopy have it create a table from the DataTable.
这是我当前使用的，它使用SELECT INTO并将null强制转换为sqlType以使可为空的列.如您所见，它虽然很原始，但却可以完成大部分工作-我正在寻找不太容易出错的方法.
This is what I am currently using it uses a SELECT INTO and casting a null to a sqlType to make nullable columns. As you can see it's quite raw but gets the job done mostly - I am looking for less error prone methods.
var columnNames = GetColumnsToBeUpdated<T>().ToList();
var columnTypes = GetColumnsTypesToBeUpdated<T>().ToList();
var selects = columnNames.Select((t, i) => $"CAST(NULL as {columnTypes[i]}) AS [{t}]");
var createsql = $@"
SELECT {string.Join(", ", selects)}
INTO SDE.[{tableName}]";
using (var connection = new SqlConnection(_sdeConnectionString))
{
EsriServer.ExecuteNonQuery(connection, $"IF OBJECT_ID(N'SDE.[{tableName}]', N'U') IS NOT NULL " +
$"DROP TABLE SDE.[{tableName}]", null);
EsriServer.ExecuteNonQuery(connection, createsql, null);
EsriServer.ExecuteNonQuery(connection, $"TRUNCATE TABLE SDE.[{tableName}]", null);
}
private static string GetSqlDataType(Type type)
{
var name = type.Name;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
{
name = type.GetGenericArguments()[0].Name;
}
switch (name)
{
case "Guid":
return "uniqueidentifier";
case "Boolean":
return "bit";
case "Byte":
return "tinyint";
case "Int16":
return "smallint";
case "Int32":
return "int";
case "Int64":
return "bigint";
case "Decimal":
return "decimal(38,25)";
case "Single":
return "real";
case "Double":
return "float";
case "DateTime":
return "datetime";
case "String":
case "Char[]":
return "nvarchar(max)";
case "Char":
return "nvarchar(1)";
case "Byte[]":
return "varbinary";
case "Object":
return "sql_variant";
default:
throw new ArgumentOutOfRangeException();
}
}
private static IEnumerable<string> GetColumnsToBeUpdated<T>() where T : class, new()
{
var typeT = new T();
var ps = typeT.GetType().GetProperties();
var propertyNames = ps.Select(p => p.Name);
return propertyNames.Except(GetColumnsToBeIgnored<T>());
}
private static IEnumerable<string> GetColumnsTypesToBeUpdated<T>() where T : class, new()
{
var typeT = new T();
var ps = typeT.GetType().GetProperties();
return ps.Select(p => p.PropertyType).Select(GetSqlDataType).ToList();
}
如果这不能准确地完成或需要主要代码，那么我对应用程序或在线服务器非常满意，可以让我粘贴到简单的C#类中并返回create table语句.
If this can't be done accurately or requires major code then I am pretty fine with an application or online server that lets me paste in a simple C# class and get back a create table statement.
推荐答案
您可以将 C#类转换为Json ，然后将 Json转换为Sql .
- 使用 Json.NET 将C#类序列化为JSON.
- 通过 csharp2json.io 进行在线转换.
- Serialize the C# class to JSON using Json.NET.
- Convert online at csharp2json.io.
这篇关于将C#类转换为SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
