I am working on passing a recordset to a table parameter inside the stored proc using GenPro using C# .net4

I understand that its not possible using GenPro code generation. Here is the link to my previous question. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21449

Is it possible in GenPro without using the code generation?

I want to be able to do the following (in terms of psuedocode) in C# .net4 :

1. Instantiate GenPro object.
2. Declare some sort of command object in GenPro.
3. Specify my stored proc.
4. Specify my datatable object as collection of records.
5. Pass into the GenPro command object.
6. Call execute on GenPro object.

Please let me know. Thanks

Why do you want to use GenPro for this?

I could use ADO.net but since we are using GenPro for all the database calls, we would like to use GenPro.

The following call is not working for me. I do not get any error but on the other hand stored procecudure does not add any record.

int i = adapter.CallRetrievalStoredProcedure("uspStringResourceAdd", parameters);

When I call the stored proc within tsql environment, the records do get added, so I know the stored proc is working.

Please let me know if its possible in GenPro.

thanks

my bad.. the correct call that I am making to add records is as follows:

adapter.CallActionStoredProcedure("uspStringResourceAdd1", parameters);

Since this call does not work.. I wanted to get records back from stored proc call. Its not returning back any records either. I tested the proc and the proc is working.

var datatable = new DataTable(); adapter.CallRetrievalStoredProcedure("[Framework].uspStringResourceAdd1", parameters, datatable);

Please let me know why the 2 methods CallRetrievalStoredProcedure and CallActionStoredProcedure do not work in GenPro??

Did you map this SP in LLBLGenPro as SPCall? If yes, then is it mapped as an ActionSP, or a RetrievalSP?

I do not know what you mean by map this SP in LLBLGenPro as SPCall...

I will post all my code here to see if it further clarifies my question. Please help .. thanks.

Question: Following call appears to work successfully and does not error out. Yet records do not get added to the underline table.

adapter.CallActionStoredProcedure("[Framework].uspStringResourceAdd1", parameters);

My code is as follows:

    public void StringResourceValueSave(List<StringResourceTVP> resources)
    {
        var parameters = new DbParameter[1];

        var resourceTVP = resources.Select(p => p.ToTVPClass());

        parameters[0] = new SqlParameter("@StringResourcesAndValues", SqlDbType.Structured);
        parameters[0].Direction = ParameterDirection.Input;
        parameters[0].Value = resourceTVP;

        using (var adapter = new DataAccessAdapter(ConnectionStrings.VTX, false))
        {
            int i = adapter.CallActionStoredProcedure("[Framework].uspStringResourceAdd1", parameters);
            Assert.AreEqual(i, 2);
        }
    }

    [TestMethod]
    public void StringResourceAddProc()
    {
        var resourceCollection = new List<StringResourceTVP>();

        var resource = new StringResourceTVP(
            null,
            "Container1",
            "Key1",
            "CU1",
            null,
            "Value1");

        resourceCollection.Add(resource);

        resource = new StringResourceTVP(
            null,
            "Container2",
            "Key2",
            "CU2",
            null,
            "Value2");

        resourceCollection.Add(resource);

        StringResourceValueSave(resourceCollection);
    }

public class StringResourceTVP
{
    /// <summary>
    /// Initializes a new instance of the <see cref="StringResourceTVP"/> class.
    /// </summary>
    /// <param name="id">
    /// The id.
    /// </param>
    /// <param name="container">
    /// The container.
    /// </param>
    /// <param name="key">
    /// The key.
    /// </param>
    /// <param name="culture">
    /// The culture.
    /// </param>
    /// <param name="stringresourceid">
    /// The stringresourceid.
    /// </param>
    /// <param name="value">
    /// The value.
    /// </param>
    public StringResourceTVP(
        int? id,
        string container,
        string key,
        string culture,
        int? stringresourceid,
        string value)
    {
        ID = id;
        Container = container;
        Key = key;
        Culture = culture;
        StringResourceID = stringresourceid;
        Value = value;
    }

    /// <summary>
    /// Gets or sets the id.
    /// </summary>
    public int? ID { get; set; }

    /// <summary>
    /// Gets or sets the container.
    /// </summary>
    public string Container { get; set; }

    /// <summary>
    /// Gets or sets the key.
    /// </summary>
    public string Key { get; set; }

    /// <summary>
    /// Gets the value.
    /// </summary>
    public string Value { get; set; }

    /// <summary>
    /// Gets or sets the culture.
    /// </summary>
    public string Culture { get; set; }

    /// <summary>
    /// Gets or sets the string resource id.
    /// </summary>
    public int? StringResourceID { get; set; }

    /// <summary>
    /// The to tvp class.
    /// </summary>
    /// <returns>
    /// The Microsoft.SqlServer.Server.SqlDataRecord.
    /// </returns>
    public SqlDataRecord ToTVPClass()
    {
        var record = new SqlDataRecord(
            new[]
            {
                new SqlMetaData("ID", SqlDbType.Int), 
                new SqlMetaData("Container", SqlDbType.VarChar, 255), 
                new SqlMetaData("Key", SqlDbType.VarChar, 50), 
                new SqlMetaData("Culture", SqlDbType.VarChar, 10), 
                new SqlMetaData("StringResourceID", SqlDbType.Int), 
                new SqlMetaData("Value", SqlDbType.VarChar, 850)
            });
        return record;
    }
}

could the problem be in the following line of code in method StringResourceValueSave??

parameters[0].Value = resourceTVP;

What would be correct way of making a collection of records and passing in?

I tried to look for any sample code out there on how to pass in collection of records and I could not find any. Please provide sample code for this issue if possible.

there was issue in my code. I am posting it here to help out others in my situation.

thanks

    public SqlDataRecord ToTVPClass()
    {
        var record = new SqlDataRecord(
            new[]
            {
                new SqlMetaData("ID", SqlDbType.Int), 
                new SqlMetaData("Container", SqlDbType.VarChar, 255), 
                new SqlMetaData("Key", SqlDbType.VarChar, 50), 
                new SqlMetaData("Culture", SqlDbType.VarChar, 10), 
                new SqlMetaData("StringResourceID", SqlDbType.Int), 
                new SqlMetaData("Value", SqlDbType.VarChar, 850)
            });

        record.SetValues(ID, Container, Key, Culture, StringResourceID, Value);  //// missing line of code.
        return record;
    }