P & M Builder

The P & M Builder is a C# toolbox extension for Visual Studio 2022 and replaces the Sql Columns to C# Properties version. When it was originally created it only consisted of the Property Builder and It wasn't long before the Method Builder was added. The old name didn't describe the tool very well any more once the method builder was added, hence the new name of P & M Builder was created.

This extension creates Property and Method classes based on columns from tables, views and stored procedures from selected database. Can also create Property classes from the parameters of stored procedures. For use with SQL Server 2008 and above.

Website www.MyToolSource.com

Feature: v.1.0.0.11

DatabaseLibrarySDS - Added IsGuid() extension.
Changed AddDataTableParameter method name to AddParameter.

Bug Fix: v1.0.0.10

DatabaseLibrarySDS - Added new AddDataTableParameter method for passing a DataTable as a parameter to a stored procedure.
Also modified the Execute methods passing the class type as a without passing the T as a parameter.
e.g. ss.ExecuteReader(queryString) instead of ss.ExecuteReader(new Customer(), queryString);
P & M Builder - Updated ExecuteReader template with changes made to ExecuteReader methods in the DatabaseLibrarySDS

Bug Fix: v1.0.0.9

Added conversion from a list to a DataTable.
e.g. return ss.ExecuteReader(queryString).ToDataTable() or DataTable dt = customer.ToDataTable();.

Bug Fix: v1.0.0.8

Added better error handling and data type management to the DataTableToList().

Bug Fix: v1.0.0.7

Issue deploying the latest change. Corrected issue and redeployed.

Feature: v1.0.0.6

DatabaseLibrarySDS - Added new method to convert a DataTable to a list - DataTableToList()

Feature: v1.0.0.5

DatabaseLibrarySDS - Added new Dataset override methods - GetDataSet()

Bug Fix: v1.0.0.4

When creating a method if the command type was text the AddParameter was being added when it shouldn't have been

Feature: v1.0.0.3

Feature: Added link to www.MyToolSource.com.

Feature: v1.0.0.2

Changed the location of the DatabaseLibrarySDS when using the dll. It will now create the MyToolSource directory under the project directory and copy the dll there. This makes it more convenient when committing your project to your repository.

Bug Fix: v1.0.0.1

Missed changing all references from Sql Columns To CSharp Properties to P & M Builder.

Features: v1.0.0.0

Created this new version of the Sql Columns To CSharp Properties tool and changed the to P & M Builder. Any future work will be to the new version (P & M Builder).

###Below is the history of Sql Columns To CSharp Properties before the name change and other modifications and additions

Features: v1.0.0.34

Added tooltips to the Return Class & Param Class dropdown boxes on the Methods tab that show the namespace each items belongs to. This is helpful if you duplicate class names in different folders.
e.g. If there is a property class in the Models\Tables named Customer and it's based on a table and you have a Customer class in the Models\Views based on a view. Hovering over the class name will display a tooltip that displays the namespace for that class.
Double-clicking on the Method Name textbox on the Method Builder tab will populate the textbox with the selected file in the Solution Explorer. Useful if adding new methods to an existing method class.

Bug Fix: v1.0.033

Corrected: If you have 2 projects, A & B, A being the main project. If project B is selected and the user changes tabs from Property Builder to Method Builder the selected project would change back to project A.

Features: v1.0.033

Moved Sever and Database controls to the main portion of the tool window for easier access for both the Property & Method Builder tabs.
Added Stored Procedure dropdown box to the Method Builder Tab. This way the user doesn't have to go back to the Property Builder tab to select a stored procedure and then back to the Method Bulder to finish building a method.
Can now save Property Folder names according to the Table, View, SP Params & SP Cols selections.
e.g. You can segregate you property classes into multiple namespaces like Models\Tables, Models\Views, Models\SPParameters and Models\SPColumns or set them all to Models.
Added option to include or exclude the database name to the query string. The Option is on the Miscellaneous tab
e.g. string queryString = "MyToolSource.dbo.p_Activations_s"; OR string queryString = "dbo.p_Activations_s";

Bug Fix: v1.0.032

Corrected bugs with method builder selections introduced in v1.0.0.31

Feature: v1.0.0.31

Added ability to pass a properties class to the execute methods. This alleviates the need to use the AddParameter for each property which results in one line of code to insert or update records.

   return ss.ExecuteForGuid(queryString, customer); for inserting and returning a guid
   return ss.ExecuteNonQuery(queryString, customer); for updating: expects returning an int - usually the number of records affected

Added ExecuteForGuid method for returning the new id (Guid) of a newly inserted record. Note: The stored Procedure must return the new guid.

return ss.ExecuteForGuid(queryString, customer);

Changes this Note: This functionality still exists by unchecking the UseClassForAddParameters checkbox

To This

Example Stored Procedure For returning the Guid of the newly created record

Feature: v1.0.0.30

Added Return Type of Guid for methods. Useful for returning the Guid for a newly inserted record using a stored procedure

Bug Fix: v.1.0.0.29

When trying to add a reference to the DatabaLibrarySDS to a project for the first time if the directory and file didn't exist an error was thrown stating Could not find file 'C:\Program Files\MyToolSource\DatabaseLibrarySDS.dll'.
Corrected spacing/location for the 'Add Using DatabaLibrarySDS' button on the miscellaneous tab.

Added Data Annotation Options for Properties v1.0.0.28

Added DataAnnotaion options for properties - accessible on the Miscellaneous tab
Added ErrorMessage to [Required] e.g. [Required(ErrorMessage = "{0} is required!")]
Made change to StringLength - When the datatype is varchar(MAX) or Text no annotation will be added.

Bug fix: v1.0.0.27

Fixed duplicating created by header when adding methods to the property class
Added DataAnnotaion options for properties - accessible on the Miscellaneous tab e.g. [Required] [StringLength(50)] OR [StringLength(50, ErrorMessage = "The {0} value cannot exceed {1} characters.")] public string? FirstName { get; set; }

Added Stored Procedure Functionality

Property classes can be created from Stored Procedures for both parameters and columns returned.

There are two limitations known limitations

If a stored procedure returns more than one result set only the first result set will be returned
If a stored procedure contains dynamic sql creation will fail

SqlColumnsToCSharpPropertiesTabSPParamtersExample__1.PNG

Property Builder Tab

Steps to create your pproperties class

Enter the server name (Uses Windows Authentication)
Select the database
Select the table
Select one, more or all of the columns in the list
Enter the folder to save the class to
Click the Create Class button to create the class and add the properties

Method Builder Tab

Steps to create your method class

Select the method type
Select the return type
Select the command type
check/uncheck Use class for AddParameters - this is automatic when command type is StoredProcedure
Enter the property class name if it's not filled in from creating a property class
Enter the folder name
Enter the method name
Enter the class name
Click the Create Class button to create the class

Example Generated by the Method Builder

/*************************************************************************************
* Code Generated using Sql Columns To C# Properties (A Visual Studio Extention)
* Located here: https://marketplace.visualstudio.com/items?itemName=JoeMSims.jms2331
* ------------------------------------------------------------------------------------
*   Created By: simsj
* Created Date: 2/24/2023 @ 6:26 PM
***************************************************************************************/

using DatabaseLibrarySDS;
using MyToolSource.Models;
using System.Data;

namespace MyToolSource.Services
{
    public class ActivationService
    {
        // add paramter(s), connectionstring & query string 
        public int InsertActivation(Activation activation)
        {
		try
		{
			using (var ss = new SqlServer("conString", CommandType.StoredProcedure))
			{
				string queryString = "?";
				ss.AddParameter("ActivationId", activation.ActivationId );
				ss.AddParameter("CustomerId", activation.CustomerId );
				ss.AddParameter("MachineId", activation.MachineId );
				ss.AddParameter("LicenseKey", activation.LicenseKey );
				ss.AddParameter("Activated", activation.Activated );
				ss.AddParameter("DateActivated", activation.DateActivated );
				ss.AddParameter("DateDeactivated", activation.DateDeactivated );
				ss.AddParameter("TrialCreatedDate", activation.TrialCreatedDate );
				ss.AddParameter("TrialExpirationDate", activation.TrialExpirationDate );
				ss.AddParameter("IsOnRightMachine", activation.IsOnRightMachine );
				ss.AddParameter("UseMachineCode", activation.UseMachineCode );
				ss.AddParameter("SetTime", activation.SetTime );
				ss.AddParameter("IsRegistered", activation.IsRegistered );
				ss.AddParameter("IsTrial", activation.IsTrial );
				ss.AddParameter("ExistingKey", activation.ExistingKey );
				ss.AddParameter("SubscriptionPaid", activation.SubscriptionPaid );
				return ss.ExecuteNonQuery(queryString);
			}
		}
		catch (Exception e)
		{
			throw new Exception(e.Message, e);
		}
        }
    }
}

Miscellaneous Tab

This is no longer required to use the DatabaseLibrarySDS.

Clicking the Add a reference to DatabaseLibrarySDS.dll will add the reference to the solution. Clicking the Add using DatabaseLibrarySDS will add the using statement to the top of your class.

Sql Columns to C# Properties Highlights

```
   Saves time writing code.
```
```
   Remembers the last server name used
```
```
   Supports Async calls
```
```
   Supports nested  folders
```

DatabaseLibrarySDS Highlights

   Using the dll makes retrieving data from the database easier with less coding.

   It automatically maps the columns in the results from the table to the corresponding properties in the class.

```
   Supports Transactions
```

   Supports logging errors to the Windows Event Log

Examples of returning data using the DatabaseLibrarySDS

Returns a List using a Stored Procedure

    public List<Part> GetPartNumbers(int partId)
    { 
        try
        {
             using (SqlServer ss = new SqlServer(connectionString, CommandType.StoredProcedure, 30))
            {
                string query = "sp_GetParts";
                ss.AddParameter("PartId", partId);
                return ss.ExecuteReader(new Part(), query);
            }
        }
        catch (Exception ex)
        {
            //handle error here...
        }
    }

Returns a async Task<List> using a Stored Procedure

    public async Task<List<Part>> GetPartNumbers(int partId)
    { 
        try
        {
             using (SqlServer ss = new SqlServer(connectionString, CommandType.StoredProcedure, 30))
            {
                string query = "sp_GetParts";
                ss.AddParameter("PartId", partId);
                return await ss.ExecuteReaderAsync(new Part(), query);
            }
        }
        catch (Exception ex)
        {
            //handle error here...
        } 
    }

Returns a List

    public List<Part> GetPartNumbers()
    { 
         try
        {
             using (SqlServer ss = new SqlServer(connectionString, CommandType.Text, 30))
            {
                string query = "SELECT * FROM test_db1.dbo.PartNumbers "; 
                return ss.ExecuteReader(new Part(), query);
            }
        }
        catch (Exception ex)
        {
            //handle error here...
        }
    }

Return a single Part record

    public Part GetPartNumbers(int partId)
    {
        try
        {
            using (SqlServer ss = new SqlServer(connectionString, CommandType.Text, 30, true))
            {
                string query = "SELECT * FROM test_db1.dbo.PartNumbers WHERE PartId = partId";
                return ss.ExecuteForSingle(new Part(), query);
            }
        }
        catch (Exception ex)
        {//handle errors here...
        }
    }

Returns a DataTable

    public DataTable GetPartNumbers()
    { 
        try
        {
            using (SqlServer ss = new SqlServer(connectionString, CommandType.Text, 30))
            {
                string query = "SELECT * FROM test_db1.dbo.PartNumbers";
                return ss.ExecuteDataTable(new Part(), query);
            }
        }
        catch (Exception ex)
        {
            // handle error here...
        } 
    }

Using a Transaction with a Stored Procedure

    public static int InsertCutomers(List<Customer> records)
    {
        int recordsAffected;
        try
        {
            using (SqlServer ss = new SqlServer(connectionString, CommandType.StoredProcedure))
            {
                bool isTrans = ss.BeginTrans();
                try
                {
                    foreach (var record in records)
                    {
                        ss.ClearParameters();
                        ss.AddParameter("@id", record.ID);
                        ss.AddParameter("@FirstName", record.First_Name);
                        ss.AddParameter("@LastName", record.Last_Name);
                        ///...

                        ss.QueryString = "[dbo].[p_InsertCutomers]";
                        recordsAffected += ss.ExecuteNonQuery();
                    }

                    if (isTrans) ss.CommitTrans();
                }

                catch (Exception ex)
                {
                    if (isTrans) ss.RollbackTrans();
                    // handle error here...
                }
            } 
        }
        catch (Exception ex)
        {
           // handle error here...
        }
        return recordsAffected;
    }

Optional Error Handling

    The DatabaseLibrarySDS can also handle errors by sending errors to the Windows Event Log by setting the parameter useEventLog = true in the SqlServer constructor.

    i.e. using (SqlServer ss = new SqlServer(connectionString, CommandType.Text, 30, **true**))

Joe M. Sims