private static void UpdateRecord(string tableName, string sourceCondition, string targetCondition, ArrayList skip, DBHelper source, DBHelper target)
{
string colName;
DataTable dt;
bool isIdentity;
ArrayList columns = new ArrayList();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
using (IDataReader reader = source.RunTextDataReader(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}] {1}", tableName, sourceCondition)))
{
dt = reader.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
colName = (string)row["ColumnName"];
if (!skip.Contains(colName) && CheckColumn(target, cmd, tableName, colName, out isIdentity))
columns.Add(colName);
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb.Append("SELECT ");
sb2.Append("UPDATE [");
sb2.Append(tableName);
sb2.Append("] SET ");
for (int i = 0; i < columns.Count; i++)
{
colName = (string)columns[i];
if (i > 0)
{
sb.Append(", ");
sb2.Append(", ");
}
sb.Append("[");
sb.Append(colName);
sb.Append("]");
sb2.Append("[");
sb2.Append(colName);
sb2.Append("] = @p");
sb2.Append(i);
}
sb.Append(" FROM [");
sb.Append(tableName);
sb.Append("] ");
sb.Append(sourceCondition);
sb2.Append(" ");
sb2.Append(targetCondition);
string selectCommand = sb.ToString();
string updateCommand = sb2.ToString();
// Read data from source and update target
using (IDataReader reader = source.RunTextDataReader(selectCommand))
{
dt = reader.GetSchemaTable();
cmd.CommandText = updateCommand;
cmd.Parameters.Clear();
if (reader.Read())
{
// Create parameters
for (int i = 0; i < columns.Count; i++)
{
cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@p{0}", i), GetColumnValue((string)columns[i], reader[i], null, null));
if ((Type)dt.Rows[i]["DataType"] == typeof(byte[]))
cmd.Parameters[i].SqlDbType = SqlDbType.Binary;
}
target.RunCmd(cmd);
}
}
}
protected void CopyBinaryData(DBHelper source, string sourceTableName, string sourceBinColumn, string sourceIdentityColumn, object sourceId,
DBHelper target, string targetTableName, string targetBinColumn, string targetIdentityColumn,
SqlCommand cmd, SqlParameter paramTextPtr, SqlParameter paramData, SqlParameter paramId)
{
if (source == null)
throw new ArgumentNullException("source");
if (cmd == null)
throw new ArgumentNullException("cmd");
if (paramTextPtr == null)
throw new ArgumentNullException("paramTextPtr");
if (target == null)
throw new ArgumentNullException("target");
if (paramData == null)
throw new ArgumentNullException("paramData");
object size = source.RunTextScalar(string.Format(CultureInfo.InvariantCulture, "SELECT DATALENGTH([{0}]) FROM [{1}] WHERE [{2}] = @id", sourceBinColumn, sourceTableName, sourceIdentityColumn), new SqlParameter("@id", sourceId));
if (size != null && size != DBNull.Value && (int)size > 0)
{
cmd.CommandText = string.Format(CultureInfo.InvariantCulture, "SET NOCOUNT ON UPDATE [{1}] SET [{0}] = 0x0 WHERE [{2}] = @id SELECT @ptr = TEXTPTR([{0}]) FROM [{1}] WHERE [{2}] = @id", targetBinColumn, targetTableName, targetIdentityColumn);
cmd.Parameters.Clear();
cmd.Parameters.Add(paramId);
cmd.Parameters.Add(paramTextPtr);
paramTextPtr.Direction = ParameterDirection.Output;
target.RunCmd(cmd);
cmd.CommandText = string.Format(CultureInfo.InvariantCulture, "UPDATETEXT [{0}].[{1}] @ptr 0 NULL @data", targetTableName, targetBinColumn);
cmd.Parameters.Clear();
cmd.Parameters.Add(paramData);
cmd.Parameters.Add(paramTextPtr);
paramTextPtr.Direction = ParameterDirection.Input;
using (IDataReader reader = source.RunTextDataReaderBlob(string.Format(CultureInfo.InvariantCulture, "SELECT [{0}] FROM [{1}] WHERE [{2}] = @id", sourceBinColumn, sourceTableName, sourceIdentityColumn), new SqlParameter("@id", sourceId)))
{
if (reader.Read())
{
long read, startIndex = 0;
do
{
read = reader.GetBytes(0, startIndex, _buffer, 0, _binaryBufferSize);
paramData.Size = (int)read;
target.RunCmd(cmd);
startIndex += read;
cmd.CommandText = string.Format(CultureInfo.InvariantCulture, "UPDATETEXT [{0}].[{1}] @ptr NULL NULL @data", targetTableName, targetBinColumn);
} while (read == _binaryBufferSize);
}
}
}
}
private void ClearTables(XmlNodeList tables, DBHelper target)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
for (int i = tables.Count - 1; i >= 0; i--)
{
string tableName = tables[i].Attributes["name"].Value;
XmlAttribute del = tables[i].Attributes["delete"];
if (del == null || del.Value == "1")
{
SendProgress("Clearing table: {0}", tableName);
StringBuilder sb = new StringBuilder();
sb.Append("DELETE [");
sb.Append(tableName);
sb.Append("]");
XmlAttribute where = tables[i].Attributes["where"];
if (where != null)
{
sb.Append(" WHERE ");
sb.Append(where.Value);
}
cmd.CommandText = sb.ToString();
target.RunCmd(cmd);
SendProgress("Done");
}
}
}
}
private static void WriteTables(XmlNodeList dictionaries, DBHelper target, int langId, int defaultLangId, string defaultLocale, bool addAllLanguages, Hashtable languages, NameValueCollection replace)
{
string name, lang, colName;
XmlAttribute a;
StringBuilder builder = new StringBuilder();
bool isIdentity, hasIdentity;
ArrayList columns = new ArrayList();
Type type;
Hashtable colTypes = new Hashtable();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
foreach (XmlNode dict in dictionaries)
{
builder.Length = 0;
columns.Clear();
colTypes.Clear();
hasIdentity = false;
name = dict.Attributes["name"].Value;
//Console.WriteLine("Writing table: {0}", name);
a = dict.Attributes["lang"];
lang = (a != null) ? a.Value : null;
if (langId == defaultLangId || lang != "single")
{
foreach (XmlNode item in dict.SelectNodes("item"))
{
if (name == "LANGUAGES")
{
languages[item.Attributes["Locale"].Value] = int.Parse(item.Attributes["LanguageId"].Value, CultureInfo.InvariantCulture);
if (!addAllLanguages && item.Attributes["Locale"].Value != defaultLocale)
continue;
}
builder.Append(string.Format(CultureInfo.InvariantCulture, "\r\nINSERT [{0}] (", name));
int n = 0;
if (lang == "multi")
{
builder.Append("[LanguageId]");
n++;
}
foreach (XmlAttribute attr in item.Attributes)
{
colName = attr.Name;
if (!columns.Contains(colName))
{
columns.Add(colName);
if (CheckColumn(target, cmd, name, colName, out isIdentity))
{
if (isIdentity)
hasIdentity = true;
}
}
if (n > 0)
builder.Append(", ");
builder.Append("[");
builder.Append(colName);
builder.Append("]");
n++;
}
if (name == "LANGUAGES")
{
builder.Append(", [IsDefault]");
n++;
}
builder.Append(") VALUES(");
n = 0;
if (lang == "multi")
{
builder.Append(langId.ToString(CultureInfo.InvariantCulture));
n++;
}
foreach (XmlAttribute attr in item.Attributes)
{
colName = attr.Name;
if (n > 0)
builder.Append(", ");
string val = attr.Value;
if (replace != null)
{
foreach (string key in replace.Keys)
val = val.Replace(key, replace[key]);
}
if (val == "[=NULL=]")
builder.Append("NULL");
else
{
type = (Type)colTypes[colName];
if (type == null)
{
type = GetColumnType(target, cmd, name, colName);
colTypes[colName] = type;
}
if (type == typeof(string))
{
builder.Append("N'");
builder.Append(val.Replace("'", "''"));
builder.Append("'");
}
else if (type == typeof(Guid))
{
builder.Append("'");
builder.Append(val);
builder.Append("'");
}
else
builder.Append(val);
}
n++;
}
if (name == "LANGUAGES")
{
builder.Append(", ");
builder.Append(item.Attributes["Locale"].Value == defaultLocale ? "1" : "0");
n++;
}
builder.Append(")");
}
using (DBTransaction tran = target.BeginTransaction())
{
if (hasIdentity)
{
cmd.CommandText = string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT [{0}] ON", name);
target.RunCmd(cmd);
}
try
{
cmd.CommandText = builder.ToString();
target.RunCmd(cmd);
}
finally
{
if (hasIdentity)
{
cmd.CommandText = string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT [{0}] OFF", name);
target.RunCmd(cmd);
}
}
tran.Commit();
}
}
}
}
