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.

Property Generator Tab

Steps to create your 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

Miscellaneous Tab

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.

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.
  

•    Saves time writing code.
  

•    Remembers the last server name used
  

•    Supports Async calls
  

•    Supports nested  folders
  

Nested Folders

Examples of returning data using the DatabaseLibrarySDS

Returns a List using a Stored Procedure

    public List<Part> GetPartNumbers(int partId)
    {
        List<Part> partNumbers = new List<Part>();

        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.StoredProcedure, 30, true))
        {
            string query = "sp_GetParts"; 
            ss.AddParameter("PartId", partId);
            partNumbers = ss.ExecuteReader(new Part(), query);
        }
        return partNumbers;
    }

Returns a async Task<List> using a Stored Procedure

    public async Task<List<Part>> GetPartNumbers(int partId)
    {
        List<Part> partNumbers = new List<Part>();

        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.StoredProcedure, 30, true))
        {
            string query = "sp_GetParts"; 
            ss.AddParameter("PartId", partId);
            partNumbers = await ss.ExecuteReaderAsync(new Part(), query);
        }
        return partNumbers;
    }

Returns a List

    public List<Part> GetPartNumbers()
    {
        List<Part> partNumbers = new List<Part>();

        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.Text, 30, true))
        {
            string query = "SELECT FROM test_db1.dbo.PartNumbers"; 
            partNumbers = ss.ExecuteReader(new Part(), query);
        }
        return partNumbers;
    }

    public List<Part> GetPartNumbers()
    { 
        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.Text, 30, true))
        {
            string query = "SELECT * FROM test_db1.dbo.PartNumbers";
            return ss.ExecuteReader(new Part(), query);
        }              
    }
	

Return a single Part record

    public Part GetPartNumbers(int partId)
    { 
        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.Text, 30, true))
        {
            string query = "SELECT * FROM test_db1.dbo.PartNumbers WHERE PartId = partId";
            return ss.ExecuteReader(new Part(), query).FirstOrDefault();
        }              
    }

Returns a DataTable

    public DataTable GetPartNumbers()
    {
        DataTable partNumbers = new DataTable();

        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.Text, 30, true))
        {
            string query = "SELECT * FROM test_db1.dbo.PartNumbers";
            partNumbers = ss.ExecuteDataTable(new Part(), query);
        }

        return partNumbers;
    }

    public DataTable GetPartNumbers()
    { 
        string connectionString = "Server=(Local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.Text, 30, true))
        {
            string query = "SELECT * FROM test_db1.dbo.PartNumbers";
            return ss.ExecuteDataTable(new Part(), query);
        }
    }

Error Handling

  public List<Part> GetPartNumbers( )
    {
        List<Part> parts = new List<Part>();
        string connectionString = $"Server=(local);Database=Test_Db1;Trusted_Connection=True;";
        using (SqlServer ss = new SqlServer(connectionString, System.Data.CommandType.Text, 30))
        {
            try
            {
                if (ss.ErrorMessage != "") throw new Exception(ss.ErrorMessage);

                string query = "SELECT top 1 * FROM test_db1.dbo.PartNumbers";
                parts = ss.ExecuteReader(new Part(), query);

                if (ss.ErrorMessage != "") throw new Exception(ss.ErrorMessage);
            }
            catch (Exception e)
            {
               '' handle error here
            }
        }
        return parts;
    }

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, System.Data.CommandType.Text, 30, **_true_**))