Sql Columns To CSharp Properties

Sql Columns to C# Properties toolbox extension for Visual Studio. This extension creates base property classes based on columns from tables in a selected database. For use with SQL Server 2008 and above.

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

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

Method Builder Tab

Steps to create your method class

1. Select the method type
2. Select the return type
3. Select the command type
4. check/uncheck Use class for AddParameters - this is automatic when command type is StoredProcedure
5. Enter the property class name if it's not filled in from creating a property class
6. Enter the folder name
7. Enter the method name
8. Enter the class name
9. 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**))