Add Datatable to input parameter of Stored Procedure with Microsoft infrastructure library in c#

Question

I have written a method in c# in which I want to insert some data in sql database table. This method is accepting a Datatable parameter which I want to pass as parameter to stored procedure but there is some syntax issue. Please have a look at the following code-

public int SaveProcessingRecords(long policyDownloadId,string comment, string commentBy,string commentStatus, string questionType, DataTable policyResponseMappingTable)
{
    Database db = DatabaseFactory.CreateDatabase(DLConnection.GetConnection());
    DbCommand dbCmd = db.GetStoredProcCommand("ProcessingAreaINSERT");
    db.AddInParameter(dbCmd, "@policyDownloadId", DbType.Int64, policyDownloadId);
    db.AddInParameter(dbCmd, "@comment", DbType.String, comment);
    db.AddInParameter(dbCmd, "@commentBy", DbType.String, commentBy);
    db.AddInParameter(dbCmd, "@commentStatus", DbType.String, commentStatus);
    db.AddInParameter(dbCmd, "@questionType", DbType.String, questionType);
    db.AddInParameter(dbCmd, "@policyResponseMappingTable", SqlDbType.Structured, policyResponseMappingTable);

    return db.ExecuteNonQuery(dbCmd);
}

does your `GetStoredProcCommand` method set the `.CommandType` to `CommandType.StoredProcedure` ? What **exactly** does the message say? it isn't clear what `db` is here. That isn't vanilla ADO.NET; does `db.AddInParameter` set the `SqlParameter.SqlDbType` from the `SqlDbType.Structured`? (pretty please tell me that isn't ent-lib; how is ent-lib still a thing? - edit: actually, I checked: it isn't - "This content is outdated and is no longer being maintained." - if that is ent-lib, please consider not doing that... it isn't your friend) — Marc Gravell, Dec 20 '17 at 19:50

score 2 · Answer 1 · answered Dec 20 '17 at 20:29

2

I found the solution(Pass (Data Table) to SQL Server From ASP.NET using Enterprise Library

SqlParameter param = new SqlParameter("@policyResponseMappingTable", policyResponseMappingTable);
            param.SqlDbType = SqlDbType.Structured;
            dbCmd.Parameters.Add(param);

answered Dec 20 '17 at 20:29

LogicalDesk

1,237
4
16
46

1

I don't mean to labor the point, but: ent-lib has been long-since deprecated. You're probably making your life very hard by choosing that over the other options available. There would have to be a stunningly good reason to actively choose that when it is days away from 2018. – Marc Gravell Dec 20 '17 at 20:34
I agree with you but as it very old project so I have to use this. – LogicalDesk Dec 21 '17 at 06:39

score 1 · Answer 2 · answered Dec 20 '17 at 20:29

Change your declaration to

SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase(DLConnection.GetConnection());

Database is more generalized, so it doesn't know anything about parameters that are specific to SQL Server.

Enterprise Library is trying to "hide" implementation-specific details, like which database you're using. But in this case that won't work. Presumably you're using SQL Server, and you need to add a parameter type that's specific to SQL server.

Add Datatable to input parameter of Stored Procedure with Microsoft infrastructure library in c#

2 Answers2