/// <summary> /// Gets a record by its primary key. /// </summary> /// <typeparam name="TKey"></typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="key">The key.</param> /// <returns>MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>.</returns> public SingleRowDbCommandBuilder <SqlCommand, SqlParameter> GetByKey <TKey>(SqlServerObjectName tableName, TKey key) where TKey : struct { return(GetByKeyList(tableName, new List <TKey> { key })); }
MultipleRowDbCommandBuilder <OleDbCommand, OleDbParameter> GetByKeyList <T>(SqlServerObjectName tableName, ColumnMetadata <OleDbType> columnMetadata, IEnumerable <T> keys) { var keyList = keys.AsList(); string where; if (keys.Count() > 1) { where = columnMetadata.SqlName + " IN (" + string.Join(", ", keyList.Select((s, i) => "?")) + ")"; } else { where = columnMetadata.SqlName + " = ?"; } var parameters = new List <OleDbParameter>(); for (var i = 0; i < keyList.Count; i++) { var param = new OleDbParameter("@Param" + i, keyList[i]); if (columnMetadata.DbType.HasValue) { param.OleDbType = columnMetadata.DbType.Value; } parameters.Add(param); } return(new OleDbSqlServerTableOrView(this, tableName, where, parameters)); }
/// <summary> /// Delete a record by its primary key. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="key">The key.</param> /// <param name="options">The options.</param> /// <returns>MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>.</returns> public SingleRowDbCommandBuilder <SqlCommand, SqlParameter> DeleteByKey <T>(SqlServerObjectName tableName, T key, DeleteOptions options = DeleteOptions.None) where T : struct { return(DeleteByKeyList(tableName, new List <T> { key }, options)); }
internal SqlServerTableOrViewMetadata <OleDbType> GetTableOrViewInternal(SqlServerObjectName tableName) { const string TableSql = @"SELECT s.name AS SchemaName, t.name AS Name, t.object_id AS ObjectId, CONVERT(BIT, 1) AS IsTable, (SELECT COUNT(*) FROM sys.triggers t2 WHERE t2.parent_id = t.object_id) AS Triggers FROM SYS.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = ? AND t.Name = ? UNION ALL SELECT s.name AS SchemaName, t.name AS Name, t.object_id AS ObjectId, CONVERT(BIT, 0) AS IsTable, (SELECT COUNT(*) FROM sys.triggers t2 WHERE t2.parent_id = t.object_id) AS Triggers FROM SYS.views t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = ? AND t.Name = ?" ; string actualSchema; string actualName; int objectId; bool isTable; bool hasTriggers; using (var con = new OleDbConnection(m_ConnectionBuilder.ConnectionString)) { con.Open(); using (var cmd = new OleDbCommand(TableSql, con)) { cmd.Parameters.AddWithValue("@Schema1", tableName.Schema ?? DefaultSchema); cmd.Parameters.AddWithValue("@Name1", tableName.Name); cmd.Parameters.AddWithValue("@Schema2", tableName.Schema ?? DefaultSchema); cmd.Parameters.AddWithValue("@Name2", tableName.Name); using (var reader = cmd.ExecuteReader()) { if (!reader.Read()) { throw new MissingObjectException($"Could not find table or view {tableName}"); } actualSchema = reader.GetString(reader.GetOrdinal("SchemaName")); actualName = reader.GetString(reader.GetOrdinal("Name")); objectId = reader.GetInt32(reader.GetOrdinal("ObjectId")); isTable = reader.GetBoolean(reader.GetOrdinal("IsTable")); hasTriggers = reader.GetInt32(reader.GetOrdinal("Triggers")) > 0; } } } var columns = GetColumns(objectId); return(new SqlServerTableOrViewMetadata <OleDbType>(new SqlServerObjectName(actualSchema, actualName), isTable, columns, hasTriggers)); }
internal StoredProcedureMetadata <SqlServerObjectName, SqlDbType> GetStoredProcedureInternal(SqlServerObjectName procedureName) { const string StoredProcedureSql = @"SELECT s.name AS SchemaName, sp.name AS Name, sp.object_id AS ObjectId FROM SYS.procedures sp INNER JOIN sys.schemas s ON sp.schema_id = s.schema_id WHERE s.name = @Schema AND sp.Name = @Name" ; string actualSchema; string actualName; int objectId; using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString)) { con.Open(); using (var cmd = new SqlCommand(StoredProcedureSql, con)) { cmd.Parameters.AddWithValue("@Schema", procedureName.Schema ?? DefaultSchema); cmd.Parameters.AddWithValue("@Name", procedureName.Name); using (var reader = cmd.ExecuteReader()) { if (!reader.Read()) { var sysStoredProcedure = GetSystemStoredProcedure(procedureName); if (sysStoredProcedure != null) { return(sysStoredProcedure); } var masterStoredProcedure = GetMasterStoredProcedure(procedureName); if (masterStoredProcedure != null) { return(masterStoredProcedure); } throw new MissingObjectException($"Could not find stored procedure {procedureName}"); } actualSchema = reader.GetString("SchemaName"); actualName = reader.GetString("Name"); objectId = reader.GetInt32("ObjectId"); } } var objectName = new SqlServerObjectName(actualSchema, actualName); var parameters = GetParameters(objectName.ToString(), objectId, con); return(new StoredProcedureMetadata <SqlServerObjectName, SqlDbType>(objectName, parameters)); } }
internal override TableFunctionMetadata <SqlServerObjectName, OleDbType> GetTableFunctionInternal(SqlServerObjectName tableFunctionName) { const string TvfSql = @"SELECT s.name AS SchemaName, o.name AS Name, o.object_id AS ObjectId FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.type in ('TF', 'IF', 'FT') AND s.name = ? AND o.Name = ?" ; /* * TF = SQL table-valued-function * IF = SQL inline table-valued function * FT = Assembly (CLR) table-valued function */ string actualSchema; string actualName; int objectId; using (var con = new OleDbConnection(m_ConnectionBuilder.ConnectionString)) { con.Open(); using (var cmd = new OleDbCommand(TvfSql, con)) { cmd.Parameters.AddWithValue("@Schema", tableFunctionName.Schema ?? DefaultSchema); cmd.Parameters.AddWithValue("@Name", tableFunctionName.Name); using (var reader = cmd.ExecuteReader()) { if (!reader.Read()) { throw new MissingObjectException($"Could not find table valued function {tableFunctionName}"); } actualSchema = reader.GetString(reader.GetOrdinal("SchemaName")); actualName = reader.GetString(reader.GetOrdinal("Name")); objectId = reader.GetInt32(reader.GetOrdinal("ObjectId")); } } } var objectName = new SqlServerObjectName(actualSchema, actualName); var columns = GetColumns(objectId); var parameters = GetParameters(objectName.ToString(), objectId); return(new TableFunctionMetadata <SqlServerObjectName, OleDbType>(objectName, parameters, columns)); }
List <SqlServerIndexColumnMetadata> GetColumnsForIndex(SqlServerObjectName tableName) { const string columnSql = @"SELECT c.name, ic.is_descending_key, ic.is_included_column, ic.index_id FROM sys.index_columns ic INNER JOIN sys.objects o ON ic.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE o.name = @Name AND s.name = @Schema ORDER BY ic.key_ordinal;"; var tableColumns = GetTableOrView(tableName).Columns; using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString)) { con.Open(); using (var cmd = new SqlCommand(columnSql, con)) { cmd.Parameters.AddWithValue("@Schema", tableName.Schema); cmd.Parameters.AddWithValue("@Name", tableName.Name); using (var reader = cmd.ExecuteReader()) { var results = new List <SqlServerIndexColumnMetadata>(); while (reader.Read()) { var is_included_column = reader.GetBoolean("is_included_column"); var is_descending_key = reader.GetBooleanOrNull("is_descending_key"); var name = reader.GetString("Name"); var index_id = reader.GetInt32("index_id"); var column = tableColumns[name]; results.Add(new SqlServerIndexColumnMetadata(column, is_descending_key, is_included_column, index_id)); } return(results); } } } }
SqlServerTableOrView <TObject> OnGetByKey <TObject, TKey>(SqlServerObjectName tableName, ColumnMetadata <SqlDbType> columnMetadata, TKey key) where TObject : class { var where = columnMetadata.SqlName + " = @Param0"; var parameters = new List <SqlParameter>(); var param = new SqlParameter("@Param0", key); if (columnMetadata.DbType.HasValue) { param.SqlDbType = columnMetadata.DbType.Value; } parameters.Add(param); return(new SqlServerTableOrView <TObject>(this, tableName, where, parameters)); }
internal StoredProcedureMetadata <SqlServerObjectName, SqlDbType>?GetSystemStoredProcedure(SqlServerObjectName procedureName) { try { const string StoredProcedureSql = @"SELECT s.name AS SchemaName, sp.name AS Name, sp.object_id AS ObjectId FROM SYS.all_objects sp INNER JOIN sys.schemas s ON sp.schema_id = s.schema_id WHERE s.name = @Schema AND sp.Name = @Name" ; string actualSchema; string actualName; int objectId; using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString)) { con.Open(); using (var cmd = new SqlCommand(StoredProcedureSql, con)) { cmd.Parameters.AddWithValue("@Schema", procedureName.Schema ?? "sys"); cmd.Parameters.AddWithValue("@Name", procedureName.Name); using (var reader = cmd.ExecuteReader()) { if (!reader.Read()) { return(null); } actualSchema = reader.GetString("SchemaName"); actualName = reader.GetString("Name"); objectId = reader.GetInt32("ObjectId"); } } var objectName = new SqlServerObjectName(actualSchema, actualName); var parameters = GetParameters(objectName.ToString(), objectId, con, useAllParameters: true); return(new StoredProcedureMetadata <SqlServerObjectName, SqlDbType>(objectName, parameters)); } } catch { return(null); //this will silently fail } }
GetUserDefinedTableTypeInternal(SqlServerObjectName typeName) { const string sql = @"SELECT s.name AS SchemaName, t.name AS Name, tt.type_table_object_id AS ObjectId FROM sys.types t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id LEFT JOIN sys.table_types tt ON tt.user_type_id = t.user_type_id LEFT JOIN sys.types t2 ON t.system_type_id = t2.user_type_id WHERE s.name = ? AND t.name = ? AND t.is_table_type = 1;"; string actualSchema; string actualName; int objectId; using (var con = new OleDbConnection(m_ConnectionBuilder.ConnectionString)) { con.Open(); using (var cmd = new OleDbCommand(sql, con)) { cmd.Parameters.AddWithValue("@Schema", typeName.Schema ?? DefaultSchema); cmd.Parameters.AddWithValue("@Name", typeName.Name); using (var reader = cmd.ExecuteReader()) { if (!reader.Read()) { throw new MissingObjectException($"Could not find user defined type {typeName}"); } actualSchema = reader.GetString("SchemaName"); actualName = reader.GetString("Name"); objectId = reader.GetInt32("ObjectId"); } } } var columns = GetColumns(typeName.ToString(), objectId); return(new UserDefinedTableTypeMetadata <SqlServerObjectName, OleDbType>(new SqlServerObjectName(actualSchema, actualName), columns)); }
/// <summary> /// This is used to query a table valued function. /// </summary> /// <param name="tableFunctionName">Name of the table function.</param> /// <returns></returns> public TableDbCommandBuilder <SqlCommand, SqlParameter, SqlServerLimitOption> TableFunction(SqlServerObjectName tableFunctionName) { return(new SqlServerTableFunction(this, tableFunctionName, null)); }
/// <summary> /// Loads a procedure definition and populates it using the parameter object. /// </summary> /// <param name="procedureName">Name of the procedure.</param> /// <param name="argumentValue">The argument value.</param> /// <returns></returns> /// <remarks> /// The procedure's definition is loaded from the database and used to determine which properties on the parameter object to use. /// </remarks> public MultipleTableDbCommandBuilder <SqlCommand, SqlParameter> Procedure(SqlServerObjectName procedureName, object argumentValue) { return(new SqlServerProcedureCall(this, procedureName, argumentValue)); }
public SqlServerInsertBulk InsertBulk <TObject>(SqlServerObjectName tableName, IEnumerable <TObject> objects, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default) where TObject : class { var tableType = DatabaseMetadata.GetTableOrView(tableName); return(new SqlServerInsertBulk(this, tableName, new ObjectDataReader <TObject>(tableType, objects, OperationTypes.Insert), options)); }
/// <summary> /// Returns true if the two objects are equal. /// </summary> /// <param name="other"></param> /// <returns></returns> /// <remarks>This is a case-insensitive comparison.</remarks> public bool Equals(SqlServerObjectName other) { return this == other; }
/// <summary> /// Performs an insert or update operation as appropriate. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="argumentValue">The argument value.</param> /// <param name="options">The options for how the insert/update occurs.</param> /// <returns>SqlServerUpdate.</returns> public ObjectDbCommandBuilder <SqlCommand, SqlParameter, TArgument> Upsert <TArgument>(SqlServerObjectName tableName, TArgument argumentValue, UpsertOptions options = UpsertOptions.None) where TArgument : class { return(new SqlServerInsertOrUpdateObject <TArgument>(this, tableName, argumentValue, options)); }
/// <summary> /// Delete multiple rows by key. /// </summary> /// <typeparam name="TArgument">The type of the t argument.</typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="newValues">The new values to use.</param> /// <param name="keys">The keys.</param> /// <returns></returns> /// <remarks>This only works on tables that have a scalar primary key.</remarks> public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> UpdateByKey <TArgument>(SqlServerObjectName tableName, TArgument newValues, params string[] keys) { return(UpdateByKeyList(tableName, newValues, keys)); }
/// <summary> /// Delete a record by its primary key. /// </summary> /// <typeparam name="TArgument">The type of the t argument.</typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="newValues">The new values to use.</param> /// <param name="key">The key.</param> /// <param name="options">The options.</param> /// <returns>MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>.</returns> public SingleRowDbCommandBuilder <SqlCommand, SqlParameter> UpdateByKey <TArgument>(SqlServerObjectName tableName, TArgument newValues, string key, UpdateOptions options = UpdateOptions.None) { return(UpdateByKeyList(tableName, newValues, new List <string> { key }, options)); }
/// <summary> /// Gets a set of records by their primary key. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="keys">The keys.</param> /// <returns></returns> /// <remarks>This only works on tables that have a scalar primary key.</remarks> public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> GetByKey(SqlServerObjectName tableName, params string[] keys) { return(GetByKeyList(tableName, keys)); }
/// <summary> /// Gets a set of records by their primary key. /// </summary> /// <typeparam name="TKey"></typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="keys">The keys.</param> /// <returns></returns> /// <remarks>This only works on tables that have a scalar primary key.</remarks> public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> GetByKey <TKey>(SqlServerObjectName tableName, params TKey[] keys) where TKey : struct { return(GetByKeyList(tableName, keys)); }
/// <summary> /// Gets a record by its primary key. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="key">The key.</param> /// <returns>MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>.</returns> public SingleRowDbCommandBuilder <SqlCommand, SqlParameter> GetByKey(SqlServerObjectName tableName, string key) { return(GetByKeyList(tableName, new List <string> { key })); }
/// <summary> /// This is used to directly query a table or view. /// </summary> /// <param name="tableOrViewName">Name of the table or view.</param> /// <param name="filterValue">The filter value is used to generate a simple AND style WHERE clause.</param> /// <param name="filterOptions">The filter options.</param> /// <returns>SqlServerTableOrView.</returns> public TableDbCommandBuilder <SqlCommand, SqlParameter, SqlServerLimitOption> From(SqlServerObjectName tableOrViewName, object filterValue, FilterOptions filterOptions = FilterOptions.None) { return(new SqlServerTableOrView(this, tableOrViewName, filterValue, filterOptions)); }
/// <summary> /// This is used to directly query a table or view. /// </summary> /// <param name="tableOrViewName">Name of the table or view.</param> /// <param name="whereClause">The where clause. Do not prefix this clause with "WHERE".</param> /// <param name="argumentValue">Optional argument value. Every property in the argument value must have a matching parameter in the WHERE clause</param> /// <returns>SqlServerTableOrView.</returns> public TableDbCommandBuilder <SqlCommand, SqlParameter, SqlServerLimitOption> From(SqlServerObjectName tableOrViewName, string whereClause, object argumentValue) { return(new SqlServerTableOrView(this, tableOrViewName, whereClause, argumentValue)); }
/// <summary> /// This is used to query a table valued function. /// </summary> /// <param name="tableFunctionName">Name of the table function.</param> /// <param name="functionArgumentValue">The function argument.</param> /// <returns></returns> public TableDbCommandBuilder <SqlCommand, SqlParameter, SqlServerLimitOption> TableFunction(SqlServerObjectName tableFunctionName, object functionArgumentValue) { return(new SqlServerTableFunction(this, tableFunctionName, functionArgumentValue)); }
/// <summary> /// Delete a record by its primary key. /// </summary> /// <typeparam name="TArgument">The type of the t argument.</typeparam> /// <typeparam name="TKey"></typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="newValues">The new values to use.</param> /// <param name="key">The key.</param> /// <param name="options">The options.</param> /// <returns>MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>.</returns> public SingleRowDbCommandBuilder <SqlCommand, SqlParameter> UpdateByKey <TArgument, TKey>(SqlServerObjectName tableName, TArgument newValues, TKey key, UpdateOptions options = UpdateOptions.None) where TKey : struct { return(UpdateByKeyList(tableName, newValues, new List <TKey> { key }, options)); }
/// <summary> /// Inserts the batch of records as one operation. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="tableTypeName">Name of the table type.</param> /// <param name="dataReader">The data reader.</param> /// <param name="options">The options.</param> /// <returns>MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>.</returns> public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> InsertBatch(SqlServerObjectName tableName, SqlServerObjectName tableTypeName, DbDataReader dataReader, InsertOptions options = InsertOptions.None) { return(new SqlServerInsertBatch(this, tableName, tableTypeName, dataReader, options)); }
/// <summary> /// Delete multiple rows by key. /// </summary> /// <typeparam name="TArgument">The type of the t argument.</typeparam> /// <typeparam name="TKey"></typeparam> /// <param name="tableName">Name of the table.</param> /// <param name="newValues">The new values to use.</param> /// <param name="keys">The keys.</param> /// <returns></returns> /// <remarks>This only works on tables that have a scalar primary key.</remarks> public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> UpdateByKey <TArgument, TKey>(SqlServerObjectName tableName, TArgument newValues, params TKey[] keys) where TKey : struct { return(UpdateByKeyList(tableName, newValues, keys)); }
public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> InsertBatch <TObject>(SqlServerObjectName tableName, SqlServerObjectName tableTypeName, IEnumerable <TObject> objects, InsertOptions options = InsertOptions.None) { var tableType = DatabaseMetadata.GetUserDefinedType(tableTypeName); return(new SqlServerInsertBatch(this, tableName, tableTypeName, new ObjectDataReader <TObject>(tableType, objects), options)); }
public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> UpdateByKeyList <TArgument, TKey>(SqlServerObjectName tableName, TArgument newValues, IEnumerable <TKey> keys, UpdateOptions options = UpdateOptions.None) { var primaryKeys = DatabaseMetadata.GetTableOrView(tableName).Columns.Where(c => c.IsPrimaryKey).ToList(); if (primaryKeys.Count != 1) { throw new MappingException($"UpdateByKey operation isn't allowed on {tableName} because it doesn't have a single primary key."); } var keyList = keys.AsList(); var columnMetadata = primaryKeys.Single(); string where; if (keys.Count() > 1) { where = columnMetadata.SqlName + " IN (" + string.Join(", ", keyList.Select((s, i) => "@Param" + i)) + ")"; } else { where = columnMetadata.SqlName + " = @Param0"; } var parameters = new List <SqlParameter>(); for (var i = 0; i < keyList.Count; i++) { var param = new SqlParameter("@Param" + i, keyList[i]); if (columnMetadata.DbType.HasValue) { param.SqlDbType = columnMetadata.DbType.Value; } parameters.Add(param); } return(new SqlServerUpdateMany(this, tableName, newValues, where, parameters, parameters.Count, options)); }
/// <summary> /// Inserts the batch of records as one operation.. /// </summary> /// <typeparam name="TObject">The type of the object.</typeparam> /// <param name="tableTypeName">Name of the table type.</param> /// <param name="objects">The objects.</param> /// <param name="options">The options.</param> /// <returns> /// MultipleRowDbCommandBuilder<SqlCommand, SqlParameter>. /// </returns> public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> InsertBatch <TObject>(SqlServerObjectName tableTypeName, IEnumerable <TObject> objects, InsertOptions options = InsertOptions.None) where TObject : class { return(InsertBatch(DatabaseMetadata.GetTableOrViewFromClass <TObject>().Name, tableTypeName, objects, options)); }
/// <summary> /// Inserts an object model from the specified table. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="argumentValue">The argument value.</param> /// <param name="options">The delete options.</param> /// <returns>SqlServerInsert.</returns> public ObjectDbCommandBuilder <SqlCommand, SqlParameter, TArgument> Delete <TArgument>(SqlServerObjectName tableName, TArgument argumentValue, DeleteOptions options = DeleteOptions.None) where TArgument : class { var table = DatabaseMetadata.GetTableOrView(tableName); if (!AuditRules.UseSoftDelete(table)) { return(new SqlServerDeleteObject <TArgument>(this, tableName, argumentValue, options)); } UpdateOptions effectiveOptions = UpdateOptions.SoftDelete | UpdateOptions.IgnoreRowsAffected; if (options.HasFlag(DeleteOptions.UseKeyAttribute)) { effectiveOptions = effectiveOptions | UpdateOptions.UseKeyAttribute; } return(new SqlServerUpdateObject <TArgument>(this, tableName, argumentValue, effectiveOptions)); }
/// <summary> /// Inserts the batch of records using bulk insert. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="dataReader">The data reader.</param> /// <param name="options">The options.</param> /// <returns>SqlServerInsertBulk.</returns> public SqlServerInsertBulk InsertBulk(SqlServerObjectName tableName, IDataReader dataReader, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default) { return(new SqlServerInsertBulk(this, tableName, dataReader, options)); }