У меня есть DataTable, который я вручную создал и загрузил данными с помощью С#.
Каким будет наиболее эффективный способ создания таблицы в SQL Server 2005, которая использует столбцы и данные в DataTable?
Создание таблицы SQL Server из С# datatable
Ответ 1
Это немного необычно в SQL для создания таблиц из клиентского определения объекта Datatable. Таблицы представляют собой тщательно обработанные сущности в SQL, с учетом времени размещения места выбора правильного диска с учетом индексации во время разработки и со всеми проблемами, связанными с правильным моделированием базы данных.
Лучше объясните, что вы пытаетесь достичь, поэтому мы понимаем, какой совет дать.
Как замечание, в SQL 2008 существует очень простой способ создать таблицу из определенного пользователем клиента Datatable: передать значение DataTable в качестве параметра значения таблицы, а затем выдать SELECT * INTO <tablename> FROM @tvp, это эффективно передаст определение данных Datatable и его содержимого в реальную таблицу в SQL.
Ответ 2
public static string CreateTABLE(string tableName, DataTable table)
{
string sqlsc;
sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
string columnType = table.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint";
break;
case "System.Byte":
sqlsc += " tinyint";
break;
case "System.Decimal":
sqlsc += " decimal ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.String":
default:
sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
break;
}
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
return sqlsc.Substring(0,sqlsc.Length-1) + "\n)";
}
Ответ 3
Я знаю, что этот вопрос довольно старый, но у меня просто было что-то очень похожее, что мне нужно было написать. Я взял то, что сделал, и изменил примеры, представленные как Amin, так и rasputino, и создал пример, который выводит только SQL. Я добавил несколько функций и избегал конкатенации, чтобы помочь улучшить процесс, который по своей сути является плохим исполнителем.
/// <summary>
/// Inspects a DataTable and return a SQL string that can be used to CREATE a TABLE in SQL Server.
/// </summary>
/// <param name="table">System.Data.DataTable object to be inspected for building the SQL CREATE TABLE statement.</param>
/// <returns>String of SQL</returns>
public static string GetCreateTableSql(DataTable table)
{
StringBuilder sql = new StringBuilder();
StringBuilder alterSql = new StringBuilder();
sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);
for (int i = 0; i < table.Columns.Count; i++)
{
bool isNumeric = false;
bool usesColumnDefault = true;
sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);
switch (table.Columns[i].DataType.ToString().ToUpper())
{
case "SYSTEM.INT16":
sql.Append(" smallint");
isNumeric = true;
break;
case "SYSTEM.INT32":
sql.Append(" int");
isNumeric = true;
break;
case "SYSTEM.INT64":
sql.Append(" bigint");
isNumeric = true;
break;
case "SYSTEM.DATETIME":
sql.Append(" datetime");
usesColumnDefault = false;
break;
case "SYSTEM.STRING":
sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
break;
case "SYSTEM.SINGLE":
sql.Append(" single");
isNumeric = true;
break;
case "SYSTEM.DOUBLE":
sql.Append(" double");
isNumeric = true;
break;
case "SYSTEM.DECIMAL":
sql.AppendFormat(" decimal(18, 6)");
isNumeric = true;
break;
default:
sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
break;
}
if (table.Columns[i].AutoIncrement)
{
sql.AppendFormat(" IDENTITY({0},{1})",
table.Columns[i].AutoIncrementSeed,
table.Columns[i].AutoIncrementStep);
}
else
{
// DataColumns will add a blank DefaultValue for any AutoIncrement column.
// We only want to create an ALTER statement for those columns that are not set to AutoIncrement.
if (table.Columns[i].DefaultValue != null)
{
if (usesColumnDefault)
{
if (isNumeric)
{
alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ({2}) FOR [{1}];",
table.TableName,
table.Columns[i].ColumnName,
table.Columns[i].DefaultValue);
}
else
{
alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ('{2}') FOR [{1}];",
table.TableName,
table.Columns[i].ColumnName,
table.Columns[i].DefaultValue);
}
}
else
{
// Default values on Date columns, e.g., "DateTime.Now" will not translate to SQL.
// This inspects the caption for a simple XML string to see if there is a SQL compliant default value, e.g., "GETDATE()".
try
{
System.Xml.XmlDocument xml = new System.Xml.XmlDocument();
xml.LoadXml(table.Columns[i].Caption);
alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ({2}) FOR [{1}];",
table.TableName,
table.Columns[i].ColumnName,
xml.GetElementsByTagName("defaultValue")[0].InnerText);
}
catch
{
// Handle
}
}
}
}
if (!table.Columns[i].AllowDBNull)
{
sql.Append(" NOT NULL");
}
sql.Append(",");
}
if (table.PrimaryKey.Length > 0)
{
StringBuilder primaryKeySql = new StringBuilder();
primaryKeySql.AppendFormat("\n\tCONSTRAINT PK_{0} PRIMARY KEY (", table.TableName);
for (int i = 0; i < table.PrimaryKey.Length; i++)
{
primaryKeySql.AppendFormat("{0},", table.PrimaryKey[i].ColumnName);
}
primaryKeySql.Remove(primaryKeySql.Length - 1, 1);
primaryKeySql.Append(")");
sql.Append(primaryKeySql);
}
else
{
sql.Remove(sql.Length - 1, 1);
}
sql.AppendFormat("\n);\n{0}", alterSql.ToString());
return sql.ToString();
}
Вот простой тест для использования этого метода и получения SQL:
DataTable table = new DataTable("Users");
table.Columns.Add(new DataColumn()
{
ColumnName = "UserId",
DataType = System.Type.GetType("System.Int32"),
AutoIncrement = true,
AllowDBNull = false,
AutoIncrementSeed = 1,
AutoIncrementStep = 1
});
table.Columns.Add(new DataColumn()
{
ColumnName = "UserName",
DataType = System.Type.GetType("System.String"),
AllowDBNull = true,
DefaultValue = String.Empty,
MaxLength = 50
});
table.Columns.Add(new DataColumn()
{
ColumnName = "LastUpdate",
DataType = System.Type.GetType("System.DateTime"),
AllowDBNull = false,
DefaultValue = DateTime.Now,
Caption = "<defaultValue>GETDATE()</defaultValue>"
});
table.PrimaryKey = new DataColumn[] { table.Columns[0] };
string sql = DataHelper.GetCreateTableSql(table);
Console.WriteLine(sql);
И, наконец, вывод:
CREATE TABLE [Users] (
[UserId] int IDENTITY(0,1) NOT NULL,
[UserName] nvarchar(50),
[LastUpdate] datetime NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY (UserId)
);
ALTER TABLE Users ADD CONSTRAINT [DF_Users_UserName] DEFAULT ('') FOR [UserName];
ALTER TABLE Users ADD CONSTRAINT [DF_Users_LastUpdate] DEFAULT (GETDATE()) FOR[LastUpdate];
Я согласен с оригинальным ответом, в котором говорится, что управление данными не является чем-то, что нужно делать беспорядочно. Это действительно требует большой мысли, чтобы БД работала бесперебойно и позволяла поддерживать работоспособность в будущем. Но есть моменты, когда требуется решение для кодирования, и я надеюсь, что это может помочь кому-то.
Ответ 4
Что касается ответа Амина, я добавил первичные ключи к его коду.
public static string CreateTABLEPablo(string connectionString, string tableName, System.Data.DataTable table)
{
string sqlsc;
//using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
{
connection.Open();
sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n" + table.Columns[i].ColumnName;
if (table.Columns[i].DataType.ToString().Contains("System.Int32"))
sqlsc += " int ";
else if (table.Columns[i].DataType.ToString().Contains("System.DateTime"))
sqlsc += " datetime ";
else if (table.Columns[i].DataType.ToString().Contains("System.String"))
sqlsc += " nvarchar(" + table.Columns[i].MaxLength.ToString() + ") ";
else if (table.Columns[i].DataType.ToString().Contains("System.Single"))
sqlsc += " single ";
else if (table.Columns[i].DataType.ToString().Contains("System.Double"))
sqlsc += " double ";
else
sqlsc += " nvarchar(" + table.Columns[i].MaxLength.ToString() + ") ";
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
string pks = "\nCONSTRAINT PK_" + tableName + " PRIMARY KEY (";
for (int i = 0; i < table.PrimaryKey.Length; i++)
{
pks += table.PrimaryKey[i].ColumnName + ",";
}
pks = pks.Substring(0, pks.Length - 1) + ")";
sqlsc += pks;
connection.Close();
}
return sqlsc + ")";
}
Ответ 5
Если вы имеете в виду какой-либо произвольный ADO.Net DataTable, я думаю, вам придется закодировать это как средство генерации кода DDL, итерации через коллекцию колонок DataTables при создании "Создать таблицу..." Заявление DDL.
Затем подключитесь к требуемой базе данных и выполните построенный оператор создания таблицы DDL.
Ответ 6
Я бы просто создал инструкцию Create Table на основе DataTable и отправил ее в базу данных. Вы также можете использовать SMO (объекты управления SQL Server). Не уверен, что было бы самым быстрым.
Это определенно то, что может перейти в класс уровня фрейма для повторного использования.
Следующая ссылка содержит информацию (и пример кода SqlTableCreator) о том, как это сделать: Создание новой таблицы в SQL Server из ADO.NET DataTable. Вы можете найти forks SqlTableCreator здесь, здесь, и здесь.
Надеюсь, что это поможет.
Ответ 7
Вот какой код, который я написал, чтобы сделать это для работы. Он был протестирован и использован в производственной среде для генерации script. Он обрабатывает DBNull и первичные ключи правильно и не прерывается, если их нет или только один. Он также более эффективен, чем другие предложения, потому что он использует StringBuilder, Linq Aggregate и не вызывает ToString() несколько раз.
Примечание. Если ваши данные поступают из внешнего источника, убедитесь, что ваш код всегда дезинфицирует входные данные этого метода или проверяет вывод этого метода, прежде чем вслепую выполнение сгенерированного script в отношении вашей базы данных.
/// <summary>
/// Creates a SQL script that creates a table where the columns matches that of the specified DataTable.
/// </summary>
public static string BuildCreateTableScript(DataTable Table)
{
if (!Helper.IsValidDatatable(Table, IgnoreZeroRows: true))
return string.Empty;
StringBuilder result = new StringBuilder();
result.AppendFormat("CREATE TABLE [{1}] ({0} ", Environment.NewLine, Table.TableName);
bool FirstTime = true;
foreach (DataColumn column in Table.Columns.OfType<DataColumn>())
{
if (FirstTime) FirstTime = false;
else
result.Append(" ,");
result.AppendFormat("[{0}] {1} {2} {3}",
column.ColumnName, // 0
GetSQLTypeAsString(column.DataType), // 1
column.AllowDBNull ? "NULL" : "NOT NULL", // 2
Environment.NewLine // 3
);
}
result.AppendFormat(") ON [PRIMARY]{0}GO{0}{0}", Environment.NewLine);
// Build an ALTER TABLE script that adds keys to a table that already exists.
if (Table.PrimaryKey.Length > 0)
result.Append(BuildKeysScript(Table));
return result.ToString();
}
/// <summary>
/// Builds an ALTER TABLE script that adds a primary or composite key to a table that already exists.
/// </summary>
private static string BuildKeysScript(DataTable Table)
{
// Already checked by public method CreateTable. Un-comment if making the method public
// if (Helper.IsValidDatatable(Table, IgnoreZeroRows: true)) return string.Empty;
if (Table.PrimaryKey.Length < 1) return string.Empty;
StringBuilder result = new StringBuilder();
if (Table.PrimaryKey.Length == 1)
result.AppendFormat("ALTER TABLE {1}{0} ADD PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, Table.PrimaryKey[0].ColumnName);
else
{
List<string> compositeKeys = Table.PrimaryKey.OfType<DataColumn>().Select(dc => dc.ColumnName).ToList();
string keyName = compositeKeys.Aggregate((a,b) => a + b);
string keys = compositeKeys.Aggregate((a, b) => string.Format("{0}, {1}", a, b));
result.AppendFormat("ALTER TABLE {1}{0}ADD CONSTRAINT pk_{3} PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, keys, keyName);
}
return result.ToString();
}
/// <summary>
/// Returns the SQL data type equivalent, as a string for use in SQL script generation methods.
/// </summary>
private static string GetSQLTypeAsString(Type DataType)
{
switch (DataType.Name)
{
case "Boolean": return "[bit]";
case "Char": return "[char]";
case "SByte": return "[tinyint]";
case "Int16": return "[smallint]";
case "Int32": return "[int]";
case "Int64": return "[bigint]";
case "Byte": return "[tinyint] UNSIGNED";
case "UInt16": return "[smallint] UNSIGNED";
case "UInt32": return "[int] UNSIGNED";
case "UInt64": return "[bigint] UNSIGNED";
case "Single": return "[float]";
case "Double": return "[double]";
case "Decimal": return "[decimal]";
case "DateTime": return "[datetime]";
case "Guid": return "[uniqueidentifier]";
case "Object": return "[variant]";
case "String": return "[nvarchar](250)";
default: return "[nvarchar](MAX)";
}
}
Пример сгенерированного вывода:
CREATE TABLE [Order] (
[OrderID] [bigint] UNSIGNED NOT NULL
,[Description] [nvarchar](250) NULL
,[Flag] [bit] NULL
,[Quantity] [int] NULL
,[Price] [decimal] NULL
,[Customer] [nvarchar](MAX) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Order
ADD CONSTRAINT pk_OrderIDCustomer PRIMARY KEY (OrderID, Customer)
GO
Все включено, кроме Helper.IsValidDatatable(), но вы получаете идею. Это, по крайней мере, должно быть заменено нулевой проверкой и, вероятно, проверяться на отсутствие DataColumns. На самом деле, если ваш любопытный, этот код исходит из более крупной (но еще менее 1000 строк) открытой библиотеки классов С# с открытым исходным кодом, которая облегчает перенос данных из объекта класса С# в DataTable, затем в SQL-скрипты и обратно. Он также содержит несколько вспомогательных методов доступа к данным для более сжатого кода. Я называю это EntityJustworks, и там также находится тело метода IsValidDatatable() (в файле класса Helper.cs). Вы можете получить доступ к коду через CodePlex (https://entityjustworks.codeplex.com) или просмотреть полный список всех других мест (GitHub, Code.MSDN, Pastebin, ect), что EntityJustworks можно получить, посетив сообщение в блоге (http://www.csharpprogramming.tips/2015/01/entity-justworks-class-to-sql.html).
Ответ 8
Насколько вы эффективны? Я бы, вероятно, написал свой собственный TSQL (на основе столбцов DataTable), чтобы создать столбец + столбцы, но для его заполнения у вас есть выбор; если у вас умеренное количество строк, SqlDataAdapter должно быть в порядке. Если у вас много данных, тогда SqlBulkCopy принимает DataTable и имя таблицы...