我们可以将表作为参数传递给企业库吗？

I'm working on a sql project using enterprise library

我正在使用企业库开发一个sql项目

        public void InsertTotalOrdersIntoYearlyTable(int val, double emSal)
        {
           DbCommand cmd = db.GetSqlStringCommand("INSERT INTO Table1 (TotalValue,TotalSalary) VALUES (@val, @emSal)");
           db.AddInParameter(cmd, "val", DbType.Int32, val);
           db.AddInParameter(cmd, "emSal", DbType.Double, emSal);
        }

Now I have to insert into another table with some manipulations and I'm creating another method, so I want to send the table as a parameter to a method so I have not repeat the method.Is this can be done?

现在我必须使用一些操作插入到另一个表中，并且我正在创建另一个方法，所以我想将表作为参数发送到方法，所以我没有重复该方法。这可以做到吗？

1 个解决方案

#1

-1

Sounds like you're trying to do one method call to insert data from c# into SQL. You can use User-defined table type in SQL to achieve this.

听起来你正试图进行一次方法调用，将数据从c＃插入到SQL中。您可以在SQL中使用用户定义的表类型来实现此目的。

Here's the SQL User-Defined Table

这是SQL用户定义表

CREATE TYPE [dbo].[udt_Table] AS TABLE(
       [TotalValue] [int] NOT NULL,
       [TotalSalary] [Double] NOT NULL
       PRIMARY KEY CLUSTERED 
(
       [TotalValue] ASC,
       [TotalSalary] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Now a stored procedure that uses the User-defined table type.

现在是一个使用用户定义的表类型的存储过程。

CREATE PROCEDURE [dbo].[usp_table_insert]
       @UdtTableIn dbo.udt_Table ReadOnly
AS
BEGIN
       SET NOCOUNT ON;

       insert into table Table1 ( TotalValue, TotalSalary)
       values ( source.TotalValue, source.TotalSalary);

END

Now the C# (ado) code to make a single call.

现在C＃（ado）代码进行一次调用。

/// <summary>
        /// Processes the counts from the float file, and adds the values to the database.
        /// </summary>
        /// <param name="valuesToBeInserted">
        /// The count of the accounts in the Float file that was parsed.
        /// </param>            
    public void ProcessCountResults(Dictionary<long, int> valuesToBeInserted)
    {
        // Creating a data table and adding the columns with their names and types.
        var resultsDataTable = new DataTable();
        resultsDataTable.Columns.Add("TotalValue", typeof(int));
        resultsDataTable.Columns.Add("TotalSalary", typeof(long));

        foreach (var values in valuesToBeInserted)
        {
            // Creating and then adding data to the data row.
            var dataRow = resultsDataTable.NewRow();

            // Adding the primary key values first.
            dataRow["TotalValue"] = values.Key;
            dataRow["TotalSalary"] = values.Value;

            // Adding the row to the data table.
            resultsDataTable.Rows.Add(dataRow);
        }

        // Calling the method to insert the data table into the SQL database.
        this.InsertDataTableIntoSqlDatabase(resultsDataTable, "dbo.usp_table_insert", "@CorpUpld_UDT");
    }

    /// <summary>
    /// Inserts a data table into a SQL database.
    /// </summary>
    /// <param name="dataTable"> 
    /// The data table to be inserted 
    /// </param>
    /// <param name="spCommand"> 
    /// The string name of the stored procedure command to be executed. 
    /// </param>
    /// <param name="parameterName"> 
    /// The name of the parameter to be used. 
    /// </param>
    private void InsertDataTableIntoSqlDatabase(DataTable dataTable, string spCommand, string parameterName)
    {
        using (var connection = (SqlConnection)this.connectionManager.AcquireConnection(this.scriptObjectModelTransaction))
        {
            using (var command = new SqlCommand(spCommand, connection))
            {
                // Specify the command as a stored procedure.
                command.CommandType = CommandType.StoredProcedure;

                // Adding the parameter with values to the stored procedure call.           
       command.Parameters.AddWithValue(parameterName, dataTable);

                    // Executing the stored procedure.
                    command.ExecuteNonQuery();
                }
            }
        }
    }

Keep in mind here I'm using a dictionary for the ProcessCountResults method. You can change it to whatever you want. The point is you need to fill a c# dataTable that will be passed as a stored procedure parameter. Just make sure your c# dataTable matches the User-Defined Table Type.

请记住，我正在使用ProcessCountResults方法的字典。您可以将其更改为您想要的任何内容。关键是你需要填充一个c＃dataTable，它将作为存储过程参数传递。只需确保您的c＃dataTable与用户定义的表类型匹配。

#1

-1