Represents an object in SQL Server (e.g. table, view, procedure)
예제 #1
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.</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));
        }
예제 #3
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.</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));
 }
예제 #4
0
        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));
        }
예제 #5
0
        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));
            }
        }
예제 #6
0
        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));
        }
예제 #7
0
        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));
        }
예제 #9
0
        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
            }
        }
예제 #10
0
        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));
        }
예제 #11
0
 /// <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));
 }
예제 #12
0
 /// <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));
 }
예제 #13
0
        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));
        }
예제 #14
0
 /// <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;
 }
예제 #15
0
 /// <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));
 }
예제 #16
0
 /// <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));
 }
예제 #17
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.</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));
 }
예제 #18
0
 /// <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));
 }
예제 #19
0
 /// <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));
 }
예제 #20
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.</returns>
 public SingleRowDbCommandBuilder <SqlCommand, SqlParameter> GetByKey(SqlServerObjectName tableName, string key)
 {
     return(GetByKeyList(tableName, new List <string> {
         key
     }));
 }
예제 #21
0
 /// <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));
 }
예제 #22
0
 /// <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));
 }
예제 #23
0
 /// <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));
 }
예제 #24
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.</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));
 }
예제 #25
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.</returns>
 public MultipleRowDbCommandBuilder <SqlCommand, SqlParameter> InsertBatch(SqlServerObjectName tableName, SqlServerObjectName tableTypeName, DbDataReader dataReader, InsertOptions options = InsertOptions.None)
 {
     return(new SqlServerInsertBatch(this, tableName, tableTypeName, dataReader, options));
 }
예제 #26
0
 /// <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));
 }
예제 #27
0
        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));
        }
예제 #28
0
        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));
        }
예제 #29
0
 /// <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&lt;SqlCommand, SqlParameter&gt;.
 /// </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));
 }
예제 #30
0
        /// <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));
        }
예제 #31
0
 /// <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));
 }