ADO/ADO.NET - How to use Robbie's code generator with sprocs that are in namespaces

Asked By Erik Little on 27-Feb-14 02:33 PM
I'm using the Code Generator built by Robbie and it is erroring out on me because my sproc have namespaces.

Here is the link to the generator: https://www.nullskull.com/articles/adonet_source_code_generator.asp 

How do I use this generator when my sproc look like so:

ALTER proc [Generic].[proc_UpdateGenericCatalog]
@UserID   UNIQUEIDENTIFIER,
@Name   VARCHAR(30),
@SupplierName   VARCHAR(30),
@SupplierEmail    VARCHAR(50),
@SupplierPhone    VARCHAR(12),
@GenericCatalogID INT
AS
SET NOCOUNT ON;
 
UPDATE [Generic].[GenericCatalog]
  SET [UserID] = @UserID
   ,[Name] = @Name
   ,[SupplierName] =  @SupplierName
   ,[SupplierEmail] = @SupplierEmail
   ,[SupplierPhone] = @SupplierPhone
 
 WHERE ID = @GenericCatalogID
 
 
SET NOCOUNT OFF; The namespace [Generic]. is causing the generator to error. How do I get the generator to work with the [Generic]. schema? This is the code block that is where the error is happening.
private static SqlParameter[] DiscoverParameters(string connectionString, string spName)
{
   SqlCommand cmd = null;
   SqlParameter[] discoveredParameters = null;
  try
  {
  using (SqlConnection conn = new SqlConnection())
  {

    conn.ConnectionString = connectionString;
    conn.Open();
 
    cmd = new SqlCommand(spName, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    
     
    SqlCommandBuilder.DeriveParameters(cmd);
    conn.Close();

    discoveredParameters = new SqlParameter[cmd.Parameters.Count];
 
    cmd.Parameters.CopyTo(discoveredParameters, 0);
 
    foreach (SqlParameter discoveredParameter in discoveredParameters)
    {
    discoveredParameter.Value = DBNull.Value;
    }
    cmd.Dispose();
  }
  }
  catch (Exception) { throw; }
  return discoveredParameters;
}
Robbe Morris replied to Erik Little on 28-Feb-14 08:36 AM
I never tested that old code for the use of namespaces.  Does the error occur here:

SqlCommandBuilder.DeriveParameters(cmd);
Erik Little replied to Robbe Morris on 28-Feb-14 02:00 PM
Yes that is where is occurs, but your code does work with namespaces, it was a rouge stored procedure that was causing the issue, and I'm not sure how it was working to begin with.

But, I like that data layer code generator. It is really light weight, much better than entity framework, which screws up all the time. For little projects those .net built-in data layer frameworks or ok, but for larger projects your code generator is perfect and easy to use.

The procedure that was causing the issue looked like this before I fixed it: 

[dbo].[Generic].[proc_UpdateGenericCatalog]

I removed the [dbo]and all is fine.
Robbe Morris replied to Erik Little on 28-Feb-14 05:44 PM
I got your other email but have been tied up.  I'll email you the most recent source code I have for the generator.  I've made a lot of improvements to it this past year.