* DataTable 칼럼 추가시 길이 명확하게 주는 방법
DataColumn cid = new DataColumn("tid", typeof(string));
cid.MaxLength = 32;
dt.Columns.Add(cid);
* DataTable Column 명 변경
dt.Columns["tid"].ColumnName = "id";
* DataTable Column Order 변경하는 방법
dt.Columns["id"].SetOrdinal(0);
private void DBupdate(string connectionstring, DataTable dt, string destinationTableName, bool truncateTable, bool isNewTable)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction(); // 트랜잭션시작
try
{
if (truncateTable)
{
SqlCommand cmd = new SqlCommand();
cmd.Transaction = tran; // 현재사용할트랜잭션객체지정
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("truncate table {0};", destinationTableName);
cmd.ExecuteNonQuery();
Console.WriteLine("[{0}] Destination Table truncate [{1}]", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), destinationTableName);
}
if (isNewTable)
{
SqlCommand cmd = new SqlCommand();
cmd.Transaction = tran; // 현재사용할트랜잭션객체지정
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = CreateTableScript(destinationTableName, dt);
cmd.ExecuteNonQuery();
Console.WriteLine(string.Format("Destination Table Create [{0}]", destinationTableName));
}
DataSet ds = new DataSet();
DataTable newDT = new DataTable();
newDT = dt.Copy();
ds.Tables.Add(newDT);
DataSet newDs = SplitDataTable(ds, 2000);
for (int i = 0; i < newDs.Tables.Count; i++)
{
Console.WriteLine("[{0}] BulkCopy[{1}] {2} / {3}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), destinationTableName, i + 1, newDs.Tables.Count);
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, tran);
bulkCopy.BulkCopyTimeout = 3600;
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.WriteToServer(newDs.Tables[i]);
}
tran.Commit(); // 트랜잭션commit
}
catch (Exception ex)
{
tran.Rollback(); // 에러발생시rollback
}
}
}
private static string CreateTableScript(string tableName, DataTable table)
{
string sqlsc;
sqlsc = String.Format("IF OBJECT_ID('{0}', 'U') IS NOT NULL\n DROP TABLE {0}\n", tableName);
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.Byte[]":
sqlsc += " nvarchar(32)";//" binary(max)";
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)";
}
private DataSet SplitDataTable(DataSet ds, int max)
{
int i = 0;
int j = 1;
int endpoint = ds.Tables[0].Rows.Count;
DataSet newDs = new DataSet();
DataTable newDt = ds.Tables[0].Clone();
newDt.TableName = "Table_" + j;
newDt.Clear();
foreach (DataRow row in ds.Tables[0].Rows)
{
DataRow newRow = newDt.NewRow();
newRow.ItemArray = row.ItemArray;
newDt.Rows.Add(newRow);
i++;
if (i == max)
{
newDs.Tables.Add(newDt);
j++;
newDt = ds.Tables[0].Clone();
newDt.TableName = "Table_" + j;
newDt.Clear();
i = 0;
endpoint = endpoint - max;
}
else if (i == endpoint)
{
newDs.Tables.Add(newDt);
j++;
newDt = ds.Tables[0].Clone();
newDt.TableName = "Table_" + j;
newDt.Clear();
i = 0;
}
}
return newDs;
}
