private static string InitSnapshotStoreSql(string tableName, string schemaName = null)
{
if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName", "Akka.Persistence.SqlServer snapshot store table name is required");
schemaName = schemaName ?? "dbo";
var cb = new SqlCommandBuilder();
return string.Format(SqlSnapshotStoreFormat, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(tableName), cb.UnquoteIdentifier(schemaName), cb.UnquoteIdentifier(tableName));
}
public static string QuoteSchemaAndTable(this string sqlQuery, string schemaName, string tableName)
{
var cb = new SqlCommandBuilder();
return string.Format(sqlQuery, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(tableName));
}
public void DefaultProperties ()
{
SqlCommandBuilder cb = new SqlCommandBuilder ();
Assert.AreEqual ("[", cb.QuotePrefix, "#5");
Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
Assert.AreEqual (".", cb.CatalogSeparator, "#2");
//Assert.AreEqual ("", cb.DecimalSeparator, "#3");
Assert.AreEqual (".", cb.SchemaSeparator, "#4");
Assert.AreEqual (CatalogLocation.Start, cb.CatalogLocation, "#1");
Assert.AreEqual ("[monotest]", cb.QuoteIdentifier ("monotest"), "#7");
Assert.AreEqual ("\"monotest\"", cb.UnquoteIdentifier ("\"monotest\""), "#8");
//Assert.AreEqual (cb.ConflictOption.CompareAllSearchableValues, cb.ConflictDetection);
// FIXME: test SetAllValues
}
private SqlCommand BuildUpdateCommand(string tableName, DataTable attributes)
{
if (attributes == null || attributes.Rows.Count <= 0) {
return null;
}
DataRow row = attributes.Rows[0];
DataTable tableSchema = this.GetSchema(tableName);
/*
using(var writer = new System.IO.StringWriter()) {
tableSchema.WriteXml(writer);
Console.WriteLine(writer.ToString());
}
*/
attributes.Columns["Part_num"].ColumnName = "Part Number";
SqlCommand cmd = new SqlCommand();
SqlCommandBuilder bld = new SqlCommandBuilder();
int modifiedColumns = 0;
int whereColumns = 0;
string tableNameEscaped = bld.QuoteIdentifier(tableName);
string sqlBody = "";
string sqlWhere = "WHERE";
sqlBody = string.Format("UPDATE {0} SET", tableNameEscaped);
foreach (DataRow schemaColumn in tableSchema.Rows) {
string columnName = (string)schemaColumn["COLUMN_NAME"];
//TODO: Update DB schema to have proper primary keys, and figure out how to detect them ;-)
string columnKey = (columnName == "Part Number" ? "PRI" : "");
string dataType = (string)schemaColumn["DATA_TYPE"];
string columnNameEscaped = bld.QuoteIdentifier(columnName);
if (columnKey.Length > 0) {
if (columnKey == "PRI") {
// Add WHERE clause to satisfy PRImary key
if (attributes.Columns.Contains(columnName)) {
object originalValue = row[columnName, DataRowVersion.Original];
string paramName = "@w" + modifiedColumns.ToString();
sqlWhere += string.Format("{2}{0} = {1}", columnNameEscaped, paramName, (whereColumns++ > 0 ? " AND " : " "));
cmd.Parameters.AddWithValue(paramName, originalValue);
}
}
// Never update any UNIque, PRImary or MULty-key columns
continue;
}
if (Array.IndexOf(stringTypes, dataType.ToUpper()) < 0) {
// For now, ignore non-string parameters
continue;
}
if (attributes.Columns.Contains(columnName)) {
object orignalObject = row[columnName, DataRowVersion.Original];
object currentObject = row[columnName, DataRowVersion.Current];
string originalValue = Convert.IsDBNull(orignalObject) ? "" : (string)orignalObject;
string currentValue = Convert.IsDBNull(currentObject) ? "" : (string)currentObject;
if (originalValue != currentValue) {
// Add SET expression
string paramName = "@p" + modifiedColumns.ToString();
sqlBody += string.Format("{2}{0} = {1}", columnNameEscaped, paramName, (modifiedColumns++ > 0 ? ", " : " "));
cmd.Parameters.AddWithValue(paramName, currentValue);
}
}
}
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("{0} {1}", sqlBody, sqlWhere);
if (modifiedColumns > 0) {
return cmd;
} else {
return null;
}
}
public override void NewPart(PartType partType, ErrorHandler errorHandler)
{
//TODO: See note in PartsDb.NewPart()
SqlCommandBuilder bld = new SqlCommandBuilder();
SqlCommand cmd = new SqlCommand();
string tableNameEscaped = bld.QuoteIdentifier(partType.name);
string partNumColumnEscaped = bld.QuoteIdentifier("Part Number");
string partTypeColumnEscaped = bld.QuoteIdentifier("Part Type");
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("SELECT TOP 1 {0} FROM {1} ORDER BY {0} DESC", partNumColumnEscaped, tableNameEscaped);
Execute(cmd, delegate(DataTable partNumResult) {
if (partNumResult.Rows.Count > 0) {
DataRow row = partNumResult.Rows[0];
string Part_num = Util.PartNumberString(Util.PartNumberInteger((string)row["Part Number"]) + 1);
#if DEBUG
Console.WriteLine("New part num: {0}", Part_num);
#endif
SqlCommand insertCmd = new SqlCommand();
insertCmd.CommandType = System.Data.CommandType.Text;
insertCmd.CommandText = string.Format("INSERT INTO {0} ({1}, {2}) VALUES (@partNum, @partType);", tableNameEscaped, partNumColumnEscaped, partTypeColumnEscaped);
insertCmd.Parameters.AddWithValue("@partNum", Part_num);
insertCmd.Parameters.AddWithValue("@partType", partType.name);
Execute(insertCmd, null, errorHandler);
}
}, errorHandler);
}
public void RepairMissingAttributes()
{
this.GetPartTypes(delegate(List<PartType> partTypes) {
foreach (PartType partType in partTypes) {
SqlCommand cmd = new SqlCommand();
SqlCommandBuilder bld = new SqlCommandBuilder();
Connect();
cmd.Connection = _con;
string typeAttributesTable = string.Format("{0}_attributes", partType.name);
string partNumColumnEscaped = bld.QuoteIdentifier("Part_num");
string partsTableEscaped = bld.QuoteIdentifier("Parts");
string partTypesTableEscaped = bld.QuoteIdentifier("Part_types");
string partTypeIdColumnEscaped = bld.QuoteIdentifier("Part_type_id");
string typeAttributesTableEscaped = bld.QuoteIdentifier(typeAttributesTable);
cmd.CommandText = string.Format("SELECT {0} FROM {1} AS P NATURAL JOIN {2} WHERE P.{3} = @partType AND P.{0} NOT IN ( SELECT {0} FROM {4} )",
partNumColumnEscaped,
partsTableEscaped,
partTypesTableEscaped,
partTypeIdColumnEscaped,
typeAttributesTableEscaped);
cmd.Parameters.AddWithValue("@partType", partType.typeId);
#if DEBUG
Console.WriteLine(Util.SqlCommandToString(cmd));
#endif
SqlDataReader typeReader = cmd.ExecuteReader();
List<SqlCommand> insertCommands = new List<SqlCommand>();
try {
while (typeReader.Read()) {
string partNum = (string)typeReader["Part_num"];
SqlCommand insertCmd = new SqlCommand();
insertCmd.Connection = _con;
insertCmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES (@partNum)",
typeAttributesTableEscaped,
partNumColumnEscaped);
insertCmd.Parameters.AddWithValue("@partNum", partNum);
insertCommands.Add(insertCmd);
}
} finally {
typeReader.Close();
}
foreach (SqlCommand insertCmd in insertCommands) {
int rowsAffected = insertCmd.ExecuteNonQuery();
if (rowsAffected < 1) {
Console.WriteLine("Warning: Failed to add {0} to {1} table",
(string)insertCmd.Parameters["@partNum"].Value,
typeAttributesTable);
}
}
}
}, null);
}
public override void GetPart(string Part_num, PartType partType, PartHandler handler, ErrorHandler errorHandler)
{
SqlCommandBuilder bld = new SqlCommandBuilder();
SqlCommand cmd = new SqlCommand();
string tableNameEscaped = bld.QuoteIdentifier(partType.name);
string partNumColumnEscaped = bld.QuoteIdentifier("Part Number");
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE {1} = @partNum", tableNameEscaped, partNumColumnEscaped);
cmd.Parameters.AddWithValue("@partNum", Part_num);
Execute(cmd, delegate(DataTable partResult) {
Part part = null;
if (partResult.Rows.Count > 0) {
partResult.Columns["Part Number"].ColumnName = "Part_num";
part = new Part(Part_num, partType.typeId, partType.name, partResult);
}
if (handler != null) {
handler(part);
}
}, errorHandler);
}
public override void GetParts(PartType partType, PartsHandler partsHandler, ErrorHandler errorHandler)
{
SqlCommandBuilder bld = new SqlCommandBuilder();
SqlCommand cmd = new SqlCommand();
string tableNameEscaped = bld.QuoteIdentifier(partType.name);
cmd.CommandType = System.Data.CommandType.Text;
#if DEBUG
cmd.CommandText = string.Format("SELECT TOP 4 * FROM {0} ORDER BY [Part Number] ASC", tableNameEscaped);
#else
cmd.CommandText = string.Format("SELECT * FROM {0} ORDER BY [Part Number] ASC", tableNameEscaped);
#endif
Execute(cmd, delegate(DataTable result) {
result.Columns["Part Number"].ColumnName = "Part_num";
//result.Columns.Remove("Part Type");
PartCollection parts = new PartCollection(result);
if (partsHandler != null) {
partsHandler(parts);
}
}, errorHandler);
}
public SqlId(string value)
{
var sb = new StringBuilder();
var cmdBuilder = new SqlCommandBuilder();
value.Split('.').ToList().ForEach(segment =>
{
sb.Append(cmdBuilder.QuoteIdentifier(segment));
sb.Append('.');
});
if (sb[sb.Length - 1] == '.')
{
sb.Remove(sb.Length - 1, 1);
}
this.Value = sb.ToString();
}
[Test] // QuoteIdentifier (String)
public void QuoteIdentifier_UnquotedIdentifier_Null ()
{
SqlCommandBuilder cb = new SqlCommandBuilder ();
try {
cb.QuoteIdentifier ((string) null);
Assert.Fail ("#1");
} catch (ArgumentNullException ex) {
Assert.AreEqual (typeof (ArgumentNullException), ex.GetType (), "#2");
Assert.IsNull (ex.InnerException, "#3");
Assert.IsNotNull (ex.Message, "#4");
Assert.AreEqual ("unquotedIdentifier", ex.ParamName, "#5");
}
}
public void QuoteIdentifier_PrefixSuffix_NoMatch ()
{
SqlCommandBuilder cb;
cb = new SqlCommandBuilder ();
cb.QuoteSuffix = "\"";
try {
cb.QuoteIdentifier ("mono");
Assert.Fail ("#A1");
} catch (ArgumentException ex) {
// Specified QuotePrefix and QuoteSuffix values
// do not match
Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
Assert.IsTrue (ex.Message.IndexOf ("QuotePrefix") != -1, "#A5:" + ex.Message);
Assert.IsTrue (ex.Message.IndexOf ("QuoteSuffix") != -1, "#A6:" + ex.Message);
Assert.IsNull (ex.ParamName, "#A7");
}
cb = new SqlCommandBuilder ();
cb.QuotePrefix = "\"";
try {
cb.QuoteIdentifier ("mono");
Assert.Fail ("#B1");
} catch (ArgumentException ex) {
// Specified QuotePrefix and QuoteSuffix values
// do not match
Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#B2");
Assert.IsNull (ex.InnerException, "#B3");
Assert.IsNotNull (ex.Message, "#B4");
Assert.IsTrue (ex.Message.IndexOf ("QuotePrefix") != -1, "#B5:" + ex.Message);
Assert.IsTrue (ex.Message.IndexOf ("QuoteSuffix") != -1, "#B6:" + ex.Message);
Assert.IsNull (ex.ParamName, "#B7");
}
}
[Test] // QuoteIdentifier (String)
public void QuoteIdentifier ()
{
SqlCommandBuilder cb;
cb = new SqlCommandBuilder ();
Assert.AreEqual ("[mono]", cb.QuoteIdentifier ("mono"), "#A1");
Assert.AreEqual ("[]", cb.QuoteIdentifier (string.Empty), "#A2");
Assert.AreEqual ("[Z]", cb.QuoteIdentifier ("Z"), "#A3");
Assert.AreEqual ("[[]", cb.QuoteIdentifier ("["), "#A4");
Assert.AreEqual ("[A[C]", cb.QuoteIdentifier ("A[C"), "#A5");
Assert.AreEqual ("[]]]", cb.QuoteIdentifier ("]"), "#A6");
Assert.AreEqual ("[A]]C]", cb.QuoteIdentifier ("A]C"), "#A7");
Assert.AreEqual ("[[]]]", cb.QuoteIdentifier ("[]"), "#A8");
Assert.AreEqual ("[A[]]C]", cb.QuoteIdentifier ("A[]C"), "#A9");
cb = new SqlCommandBuilder ();
cb.QuotePrefix = "\"";
cb.QuoteSuffix = "\"";
Assert.AreEqual ("\"mono\"", cb.QuoteIdentifier ("mono"), "#B1");
Assert.AreEqual ("\"\"", cb.QuoteIdentifier (string.Empty), "#B2");
Assert.AreEqual ("\"Z\"", cb.QuoteIdentifier ("Z"), "#B3");
Assert.AreEqual ("\"\"\"\"", cb.QuoteIdentifier ("\""), "#B4");
Assert.AreEqual ("\"A\"\"C\"", cb.QuoteIdentifier ("A\"C"), "#B5");
}
private static string InitMetadataSql(string metadataTable, string schemaName)
{
if (string.IsNullOrEmpty(metadataTable)) throw new ArgumentNullException("metadataTable", "Akka.Persistence.SqlServer metadata table name is required");
schemaName = schemaName ?? "dbo";
var cb = new SqlCommandBuilder();
return string.Format(SqlMetadataFormat, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(metadataTable), cb.UnquoteIdentifier(schemaName), cb.UnquoteIdentifier(metadataTable));
}