private static void AddValueListXml(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable values, DbType dbType) { string xml = null; using (var writer = new StringWriter()) { var xmlWriter = new XmlTextWriter(writer); xmlWriter.WriteStartElement("a"); foreach (var value in values) { xmlWriter.WriteStartElement("b"); xmlWriter.WriteValue(value); xmlWriter.WriteEndElement(); } xmlWriter.WriteEndElement(); xml = writer.ToString(); } var name = sql.Parameters.GetUniqueParameterName(); var alias = "x_" + name; var p = new SqlStatementParameter(name, DbType.Xml, xml); sql.Parameters.Add(p); //SELECT x.node.value('@value', 'int') // FROM @xml.nodes('/values/value') as x(node) builder.Append("select "); builder.Append(alias); builder.Append(".node.value('./node()[1]', '"); if (dbType == DbType.Int32 || dbType == DbType.Int16 || dbType == DbType.Boolean) { builder.Append("int"); } else if (dbType == DbType.Int64) { builder.Append("bigint"); } else if (dbType == DbType.String) { builder.Append("nvarchar(max)"); } else if (dbType == DbType.Guid) { builder.Append("uniqueidentider"); } else { throw new NotSupportedException(string.Format("Cannot handle '{0}'.", dbType)); } builder.Append("') id from "); builder.Append(sql.Dialect.FormatVariableNameForQueryText(p.Name)); builder.Append(".nodes('/a/b') as "); builder.Append(alias); builder.Append("(node)"); }
private static void AddValueListTableType(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable values, DbType dbType) { var name = sql.Parameters.GetUniqueParameterName(); var p = SqlStatementParameter.CreateArrayParameter(name, dbType, values); sql.Parameters.Add(p); builder.Append("select id from "); builder.Append(sql.Dialect.FormatVariableNameForQueryText(name)); }
private static void AddValueListLegacy(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable values, DbType dbType) { bool first = true; foreach (var value in values) { if (first) { first = false; } else { builder.Append(", "); } builder.Append(sql.Dialect.FormatVariableNameForQueryText(sql.Parameters.Add(dbType, value))); } }
public static void AddValueList(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable values, DbType dbType, ArrayParameterType type) { if (type == ArrayParameterType.Array) { sql.AddValueListTableType(builder, values, dbType); } else if (type == ArrayParameterType.BunchOfParameters) { sql.AddValueListLegacy(builder, values, dbType); } else if (type == ArrayParameterType.Xml) { sql.AddValueListXml(builder, values, dbType); } else { throw new NotSupportedException(string.Format("Cannot handle '{0}'.", sql.ArrayParameterType)); } }
public static void AddValueList(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable values, DbType dbType) { sql.AddValueList(builder, values, dbType, sql.ArrayParameterType); }
public static void AddValueList <T>(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable <T> values, ArrayParameterType type) { sql.AddValueList(builder, values, ConversionHelper.GetDBTypeForClrType(typeof(T)), type); }
public static void AddValueList <T>(this ISqlStatementArrayBuilder sql, StringBuilder builder, IEnumerable <T> values) { sql.AddValueList <T>(builder, values, sql.ArrayParameterType); }