* 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;

}

DataTable Split 데이터테이블 분할 Bulk Copy 벌크카피