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: 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

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
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**))