Кто-нибудь знает, как лучше всего создать таблицу SQL Server CE (Compact 3.5) на основе схемы DataTable во время выполнения? Я не хочу формулировать оператор CREATE TABLE на основе всех возможных типов данных и т. д.
В качестве бонуса — знаете ли вы, как заполнить его прямо из таблицы данных?
Я использовал и обновил код Бена Брина:
GetSqlDBTypeFromType
/// <summary>
/// Gets the correct SqlDBType for a given .NET type. Useful for working with SQL CE.
/// </summary>
/// <param name="type">The .Net Type used to find the SqlDBType.</param>
/// <returns>The correct SqlDbType for the .Net type passed in.</returns>
public static SqlDbType GetSqlDBTypeFromType(Type type)
{
TypeConverter tc = TypeDescriptor.GetConverter(typeof(DbType));
if (/*tc.CanConvertFrom(type)*/ true)
{
DbType dbType = (DbType)tc.ConvertFrom(type.Name);
// A cheat, but the parameter class knows how to map between DbType and SqlDBType.
SqlCeParameter param = new SqlCeParameter();
param.DbType = dbType;
return param.SqlDbType; // The parameter class did the conversion for us!!
}
else
{
throw new Exception("Cannot get SqlDbType from: " + type.Name);
}
}
GetSqlServerCETypeName
/// <summary>
/// The method gets the SQL CE type name for use in SQL Statements such as CREATE TABLE
/// </summary>
/// <param name="dbType">The SqlDbType to get the type name for</param>
/// <param name="size">The size where applicable e.g. to create a nchar(n) type where n is the size passed in.</param>
/// <returns>The SQL CE compatible type for use in SQL Statements</returns>
public static string GetSqlServerCETypeName(SqlDbType dbType, int size)
{
// Conversions according to: http://msdn.microsoft.com/en-us/library/ms173018.aspx
bool max = (size == int.MaxValue) ? true : false;
bool over4k = (size > 4000) ? true : false;
if (size>0)
{
return string.Format(Enum.GetName(typeof(SqlDbType), dbType)+" ({0})", size);
}
else
{
return Enum.GetName(typeof(SqlDbType), dbType);
}
}
GetCreateTableStatement
/// <summary>
/// Genenerates a SQL CE compatible CREATE TABLE statement based on a schema obtained from
/// a SqlDataReader or a SqlCeDataReader.
/// </summary>
/// <param name="tableName">The name of the table to be created.</param>
/// <param name="schema">The schema returned from reader.GetSchemaTable().</param>
/// <returns>The CREATE TABLE... Statement for the given schema.</returns>
public static string GetCreateTableStatement(DataTable table)
{
StringBuilder builder = new StringBuilder();
builder.Append(string.Format("CREATE TABLE [{0}] (", table.TableName));
foreach (DataColumn col in table.Columns)
{
SqlDbType dbType = GetSqlDBTypeFromType(col.DataType);
builder.Append("[");
builder.Append(col.ColumnName);
builder.Append("]");
builder.Append(" ");
builder.Append(GetSqlServerCETypeName(dbType, col.MaxLength));
builder.Append(", ");
}
if (table.Columns.Count > 0) builder.Length = builder.Length - 2;
builder.Append(")");
return builder.ToString();
}
Создать из набора данных
public static void CreateFromDataset(DataSet set, SqlCeConnection conn)
{
conn.Open();
SqlCeCommand cmd;
foreach (DataTable table in set.Tables)
{
string createSql = copyDB.GetCreateTableStatement(table);
Console.WriteLine(createSql);
cmd = new SqlCeCommand(createSql, conn);
Console.WriteLine(cmd.ExecuteNonQuery());
}
conn.Close();
}
}
Я закодировал разумное решение, но надеялся избежать операторов case для типов SQL:
Во-первых, изящный трюк для преобразования типа .NET в SqlDBType:
/// <summary>
/// Gets the correct SqlDBType for a given .NET type. Useful for working with SQL CE.
/// </summary>
/// <param name="type">The .Net Type used to find the SqlDBType.</param>
/// <returns>The correct SqlDbType for the .Net type passed in.</returns>
public static SqlDbType GetSqlDBTypeFromType(Type type)
{
TypeConverter tc = TypeDescriptor.GetConverter(typeof(DbType));
if (/*tc.CanConvertFrom(type)*/ true)
{
DbType dbType = (DbType)tc.ConvertFrom(type.Name);
// A cheat, but the parameter class knows how to map between DbType and SqlDBType.
SqlParameter param = new SqlParameter();
param.DbType = dbType;
return param.SqlDbType; // The parameter class did the conversion for us!!
}
else
{
throw new Exception("Cannot get SqlDbType from: " + type.Name);
}
}
Оператор case для типов, используемых в операторах SQL:
/// <summary>
/// The method gets the SQL CE type name for use in SQL Statements such as CREATE TABLE
/// </summary>
/// <param name="dbType">The SqlDbType to get the type name for</param>
/// <param name="size">The size where applicable e.g. to create a nchar(n) type where n is the size passed in.</param>
/// <returns>The SQL CE compatible type for use in SQL Statements</returns>
public static string GetSqlServerCETypeName(SqlDbType dbType, int size)
{
// Conversions according to: http://msdn.microsoft.com/en-us/library/ms173018.aspx
bool max = (size == int.MaxValue) ? true : false;
bool over4k = (size > 4000) ? true : false;
switch (dbType)
{
case SqlDbType.BigInt:
return "bigint";
case SqlDbType.Binary:
return string.Format("binary ({0})", size);
case SqlDbType.Bit:
return "bit";
case SqlDbType.Char:
if (over4k) return "ntext";
else return string.Format("nchar({0})", size);
ETC...
Затем, наконец, оператор CREATE TABLE:
/// <summary>
/// Genenerates a SQL CE compatible CREATE TABLE statement based on a schema obtained from
/// a SqlDataReader or a SqlCeDataReader.
/// </summary>
/// <param name="tableName">The name of the table to be created.</param>
/// <param name="schema">The schema returned from reader.GetSchemaTable().</param>
/// <returns>The CREATE TABLE... Statement for the given schema.</returns>
public static string GetCreateTableStatement(string tableName, DataTable schema)
{
StringBuilder builder = new StringBuilder();
builder.Append(string.Format("CREATE TABLE [{0}] (\n", tableName));
foreach (DataRow row in schema.Rows)
{
string typeName = row["DataType"].ToString();
Type type = Type.GetType(typeName);
string name = (string)row["ColumnName"];
int size = (int)row["ColumnSize"];
SqlDbType dbType = GetSqlDBTypeFromType(type);
builder.Append(name);
builder.Append(" ");
builder.Append(GetSqlServerCETypeName(dbType, size));
builder.Append(", ");
}
if (schema.Rows.Count > 0) builder.Length = builder.Length - 2;
builder.Append("\n)");
return builder.ToString();
}
Для тех, кто хочет сделать это в обычных настройках SQL Server (не CE), мне удалось изменить это для работы с установкой SQL Server 2016, когда мне нужно было программно создавать таблицы базы данных на основе гигантских файлов csv (используя 4.7.1 .NET Framework). Обратите внимание, что здесь проверяется максимальное количество столбцов, но не максимальное количество строк, поэтому вы можете столкнуться с ошибкой, если не будете учитывать это при работе с большими CSV-файлами.
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
//
//
//
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.TextFieldType = FieldType.Delimited;
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = false;
string[] colFields = csvReader.ReadFields();
int columnCounter = 0;
foreach (string column in colFields)
{
if (columnCounter > 1023)
{
break; // the table has reached the maximum column size, either ignore the extra columns, or create additional linked tables (sounds like awful table design though).
}
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
columnCounter++;
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
Array.Resize(ref fieldData, 1024); //max number of columns is 1024 in SQL table, and we're not going through the trouble of making a Sparse table.
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return csvData;
}
/** <summary>
* Gets the correct SqlDBType for a given .NET type. Useful for working with SQL.
* </summary>
* <param name="type">The .Net Type used to find the SqlDBType.</param>
* <returns>The correct SqlDbType for the .Net type passed in.</returns>
*/
public static SqlDbType GetSqlDBTypeFromType(Type type)
{
TypeConverter tc = TypeDescriptor.GetConverter(typeof(DbType));
DbType dbType = (DbType)tc.ConvertFrom(type.Name);
// A cheat, but the parameter class knows how to map between DbType and SqlDBType.
SqlParameter param = new SqlParameter();
param.DbType = dbType;
return param.SqlDbType; // The parameter class did the conversion for us!!
}
/**
* <summary>
* The method gets the SQL type name for use in SQL Statements such as CREATE TABLE
* </summary>
* <param name="dbType">The SqlDbType to get the type name for</param>
* <param name="size">The size where applicable e.g. to create a nchar(n) type where n is the size passed in.</param>
* <returns>A string of the SQL compatible type for use in SQL Statements</returns>
*/
public static string GetSqlServerTypeName(SqlDbType dbType, int size)
{
// Conversions according to: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings
bool max = (size == int.MaxValue || size == -1) ? true : false;
string returnVal = "";
if (max)
{
returnVal = Enum.GetName(typeof(SqlDbType), dbType) + " (max)";
}
else if (size > 0)
{
returnVal = string.Format(Enum.GetName(typeof(SqlDbType), dbType) + " ({0})", size);
}
else
{
returnVal = Enum.GetName(typeof(SqlDbType), dbType);
}
return returnVal;
}
/**
* <summary>
* Genenerates a SQL compatible CREATE TABLE statement based on a schema obtained from
* a SqlDataTable.
* </summary>
* <param name="table">The name of the table to be created.</param>
* <returns>The CREATE TABLE... Statement for the given data table.</returns>
*/
public static string GetCreateTableStatement(DataTable table)
{
StringBuilder builder = new StringBuilder();
builder.Append(string.Format("CREATE TABLE [{0}] (", table.TableName));
int primaryCol = 0;
foreach (DataColumn col in table.Columns)
{
SqlDbType dbType = GetSqlDBTypeFromType(col.DataType);
builder.Append("[");
builder.Append(col.ColumnName);
builder.Append("]");
builder.Append(" ");
builder.Append(GetSqlServerTypeName(dbType, col.MaxLength));
//if on first column, assume it's a "PRIMARY KEY" (for now)
if(primaryCol == 0)
{
builder.Append(" PRIMARY KEY");
}
builder.Append(", ");
primaryCol++;
}
if (table.Columns.Count > 0) builder.Length = builder.Length - 2;
builder.Append(")");
return builder.ToString();
}
/**
* <summary>
* Genenerates a SQL compatible CREATE TABLE statement based on a schema obtained from
* a SqlDataTable.
* </summary>
* <param name="dtable">The name of the table to be created.</param>
* <param name="conn">The SQL Connection to the database that the table will be created in.</param>
*/
public static void CreateFromDataTable(DataTable dTable, SqlConnection conn)
{
bool openedHere = false;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
openedHere = true;
}
SqlCommand cmd;
string createSql = GetCreateTableStatement(dTable);
Console.WriteLine(createSql);
cmd = new SqlCommand(createSql, conn);
Console.WriteLine(cmd.ExecuteNonQuery());
if (openedHere)
{
conn.Close();
}
}
