public override void Merge(bool display)
{
string testcommand = @"
merge Entities as target
using (select * from @upusers) as source (EntityId, Name, Phone, Email, Address, City, Zip, State, Country, BirthDate)
on (target.EntityId = source.EntityId)
when matched then
update
set Name = source.Name,
Phone = source.Phone,
Email = source.Email,
Address = source.Address,
City = source.City,
Zip = source.Zip,
State = source.State,
Country = source.Country,
BirthDate = source.Birthdate
when Not Matched Then
insert (Name, Phone, Email, Address, City, Zip, State, Country, Birthdate)
values (source.Name, source.Phone, source.Email, source.Address, source.City, source.Zip, source.State, source.Country, source.Birthdate)
;
";
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(testcommand, connection))
{
command.CommandTimeout = 5000;
command.CommandType = CommandType.Text;
var param = command.CreateParameter();
param.SqlDbType = SqlDbType.Structured;
param.ParameterName = "@upusers";
param.TypeName = "udt_entities";
param.Value = this.MergeRecords(this.BulkOperationsCount);
command.Parameters.Add(param);
var renderedQuery = RenderSQL.GenerateQuery(command);
using (var sqlcommand = connection.CreateCommand())
{
sqlcommand.CommandTimeout = 5000;
sqlcommand.CommandType = CommandType.Text;
sqlcommand.CommandText = renderedQuery;
using (var reader = sqlcommand.ExecuteReader())
{
DisplayReader(display, reader);
}
}
}
}
}
public override void Select(bool display)
{
string testcommand = @"
select * from Entities
join @intt
on [@intt].Id = Entities.EntityId
";
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(testcommand, connection))
{
command.CommandTimeout = 5000;
command.CommandType = CommandType.Text;
var param = command.CreateParameter();
param.SqlDbType = SqlDbType.Structured;
param.ParameterName = "@intt";
param.TypeName = "udt_inttable";
param.Value = this.SelectRecords;
command.Parameters.Add(param);
var renderedQuery = RenderSQL.GenerateQuery(command);
using (var sqlcommand = connection.CreateCommand())
{
command.CommandTimeout = 5000;
sqlcommand.CommandType = CommandType.Text;
sqlcommand.CommandText = renderedQuery;
using (var reader = sqlcommand.ExecuteReader())
{
DisplayReader(display, reader);
}
}
}
}
}
public override void Merge(bool display)
{
var dt = this.MergeRecords(this.BulkOperationsCount);
List <string> inserts = new List <string>();
StringBuilder sb = new StringBuilder();
foreach (DataRow row in dt.Rows)
{
if (row[0].ToString() == "0")
{
StringBuilder sbinsert = new StringBuilder();
sbinsert.Append("(");
for (var i = 1; i < dt.Columns.Count; i++)
{
sbinsert.Append(RenderSQL.GetDbValue(row[i]));
if (i < (dt.Columns.Count - 1))
{
sbinsert.Append(",");
}
}
sbinsert.Append(")");
inserts.Add(sbinsert.ToString());
}
else
{
//update
sb.Append("update Entities set ");
for (var i = 1; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i].ColumnName);
sb.Append(" = ");
sb.Append(RenderSQL.GetDbValue(row[i]));
if (i < (dt.Columns.Count - 1))
{
sb.Append(",");
}
}
sb.AppendFormat(" where EntityId = {0};", row[0]);
}
}
if (inserts.Count() > 0)
{
//insert
//iterations are for < 1000 rows, which is SQL's limit
int insertIterations = (int)Math.Ceiling(inserts.Count() / 1000f);
for (var ii = 0; ii < insertIterations; ii++)
{
var length = 1000;
if (length > (inserts.Count() % 1000))
{
length = inserts.Count() % 1000;
}
List <string> subset = inserts.GetRange(ii * 1000, length);
sb.Append("insert into Entities (");
for (var i = 1; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i].ColumnName);
if (i < (dt.Columns.Count - 1))
{
sb.Append(",");
}
}
sb.Append(") values ");
sb.Append(string.Join(",", subset.ToArray()));
sb.Append(";");
}
}
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sb.ToString(), connection))
{
command.CommandTimeout = 5000;
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
DisplayReader(display, reader);
}
}
}
}
public override void Merge(bool display)
{
var dt = this.MergeRecords(this.BulkOperationsCount);
List <string> inserts = new List <string>();
StringBuilder sb = new StringBuilder();
foreach (DataRow row in dt.Rows)
{
if (row[0].ToString() == "0")
{
StringBuilder sbinsert = new StringBuilder();
sbinsert.Append("(");
for (var i = 1; i < dt.Columns.Count; i++)
{
sbinsert.Append(RenderSQL.GetDbValue(row[i]));
if (i < (dt.Columns.Count - 1))
{
sbinsert.Append(",");
}
}
sbinsert.Append(")");
inserts.Add(sbinsert.ToString());
}
else
{
//update
sb.Append("update Entities set ");
for (var i = 1; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i].ColumnName);
sb.Append(" = ");
sb.Append(RenderSQL.GetDbValue(row[i]));
if (i < (dt.Columns.Count - 1))
{
sb.Append(",");
}
}
sb.AppendFormat(" where EntityId = {0};", row[0]);
}
}
if (inserts.Count() > 0)
{
for (var ii = 0; ii < inserts.Count; ii++)
{
sb.Append("insert into Entities (");
for (var i = 1; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i].ColumnName);
if (i < (dt.Columns.Count - 1))
{
sb.Append(",");
}
}
sb.Append(") values ");
sb.Append(inserts[ii]);
sb.Append(";");
}
}
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sb.ToString(), connection))
{
command.CommandTimeout = 5000;
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
DisplayReader(display, reader);
}
}
}
}