public void InsertTest()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
RegionDao target = new RegionDao();
IList<RegionInfo> regions = target.QueryAll();
if (regions.Count > 0)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("delete [GENERALS_REGION]");
int lastId = 1000;
foreach (RegionInfo info in regions)
{
info.Id = lastId++;
}
foreach (RegionInfo r in regions)
{
foreach (RegionInfo c in regions.Where(o => o.ParentId == r.OldId))
{
c.ParentId = r.Id;
}
}
foreach (RegionInfo info in regions)
{
string insert = @"insert into [GENERALS_REGION] (Id,Name,Code,ParentId) values ({0},'{1}','{2}',{3})";
Console.WriteLine(insert, info.Id, info.Name, info.Code.Replace("'", "''"), info.ParentId);
}
}
else
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "select RegionID,NameStr,ParentRID,NameSpell from dbo.E_Region order by RegionID asc";
connection.Open();
try
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
RegionInfo info = new RegionInfo() { Id = reader.GetInt32(0), Name = reader.GetString(1), ParentId = reader.GetInt32(2), Code = reader.GetString(3) }; // TODO: 初始化为适当的值
string insert = @"insert into [GENERALS_REGION] (OldId,Name,Code,ParentId) values ({0},'{1}','{2}',{3})";
Console.WriteLine(insert, info.Id, info.Name, info.Code, info.ParentId);
object actual = target.Insert(info);
}
reader.Close();
regions = target.QueryAll();
foreach (RegionInfo info in regions)
{
RegionInfo parent = target.QueryOneByOldId(info.ParentId);
if (parent == null)
{
continue;
}
if (parent.Name == info.Name && parent.Code == info.Code)
{
target.Delete(info);
continue;
}
info.ParentId = parent.Id;
string update = @"update [GENERALS_REGION] set Name={0},Code={1},ParentId={2} where Id={3}";
Console.WriteLine(update, info.Id, info.Name, info.Code, info.ParentId);
target.Update(info);
}
foreach (RegionInfo info in regions)
{
info.OldId = info.Id;
target.Update(info);
}
}
finally
{
connection.Close();
}
}
}
}
public int Update(RegionInfo info)
{
return SqlMapper.Update("RegionMap.Update", info);
}
public int Delete(RegionInfo info)
{
return SqlMapper.Delete("RegionMap.Delete", info);
}
public object Insert(RegionInfo info)
{
return SqlMapper.Insert("RegionMap.Insert", info);
}
