Passing a table (DataTable) value as a parameter to stored procedure
-
Hi, I am trying to pass a datatable as a parameter to a stored procedure. my sql server is 2008. Also my database transactions happens with the class from enterprise library January 2006. Following is my code: DataTable dt = new DataTable(); DataColumn dcol = new DataColumn("Col1", typeof(int)); dt.Columns.Add(dcol); DataRow dr; dr = dt.NewRow(); dr["Col1"] = 1; dt.Rows.Add(dr); SqlParameter parameter = new SqlParameter("@MenuRights", SqlDbType.Structured); parameter.Value = dt; DataTable dtbl = ExecuteDatabaseQuery.Reference.ExecuteQuery_DataTable(ConAdmin, "teststorepro", "teststorepro", ProjectConstants.QueryType.StoredProc, null, true, null, parameter); When the sqlcommand is made and passed for executing, it throws an error "Failed to convert parameter value to IEnumerable". Following is the code from enterprise library: Database db = (Database)CreateConnection(ConnectionName); if (WriteInfoInLogFile == true && WriteToLogs == true) { CLogger.Reference.sLogInfo(ConnectionName, OptionName, oQuery, TypeOfQuery, CLogger.EStatus.StartProcess, LogMsg, CLogger.MsgType.Information, SQLParams); } &n
-
Hi, I am trying to pass a datatable as a parameter to a stored procedure. my sql server is 2008. Also my database transactions happens with the class from enterprise library January 2006. Following is my code: DataTable dt = new DataTable(); DataColumn dcol = new DataColumn("Col1", typeof(int)); dt.Columns.Add(dcol); DataRow dr; dr = dt.NewRow(); dr["Col1"] = 1; dt.Rows.Add(dr); SqlParameter parameter = new SqlParameter("@MenuRights", SqlDbType.Structured); parameter.Value = dt; DataTable dtbl = ExecuteDatabaseQuery.Reference.ExecuteQuery_DataTable(ConAdmin, "teststorepro", "teststorepro", ProjectConstants.QueryType.StoredProc, null, true, null, parameter); When the sqlcommand is made and passed for executing, it throws an error "Failed to convert parameter value to IEnumerable". Following is the code from enterprise library: Database db = (Database)CreateConnection(ConnectionName); if (WriteInfoInLogFile == true && WriteToLogs == true) { CLogger.Reference.sLogInfo(ConnectionName, OptionName, oQuery, TypeOfQuery, CLogger.EStatus.StartProcess, LogMsg, CLogger.MsgType.Information, SQLParams); } &n