EntityJustWorks.SQL 1.0.0

dotnet add package EntityJustWorks.SQL --version 1.0.0                
NuGet\Install-Package EntityJustWorks.SQL -Version 1.0.0                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="EntityJustWorks.SQL" Version="1.0.0" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add EntityJustWorks.SQL --version 1.0.0                
#r "nuget: EntityJustWorks.SQL, 1.0.0"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install EntityJustWorks.SQL as a Cake Addin
#addin nuget:?package=EntityJustWorks.SQL&version=1.0.0

// Install EntityJustWorks.SQL as a Cake Tool
#tool nuget:?package=EntityJustWorks.SQL&version=1.0.0                

EntityJustworks

A compact but powerful data-first or code-first ORM. Specializes in dynamically generating SQL scripts, such as a CREATE TABLE script from the public properties of a C# class, or a script that creates a stored procedure that parameterizes an INSERT INTO, given a System.Data.DataTable class.

Security Warning:

This library generates dynamic SQL, and has functions that generate SQL and then immediately executes it. While it its true that all strings funnel through the function Helper.EscapeSingleQuotes, this can be defeated in various ways and only parameterized SQL should be considered SAFE. If you have no need for them, I recommend stripping semicolons ; and dashes --. Also there are some Unicode characters that can be interpreted as a single quote or may be converted to one when changing encodings. Additionally, there are Unicode characters that can crash .NET code, but mainly controls (think TextBox). You almost certainly should impose a white list: string clean = new string(dirty.Where(c => "abcdefghijklmnopqrstuvwxyz0123456789.,\"_ !@".Contains(c)).ToArray());
PLEASE USE the SQLScript.StoredProcedure and DatabaseQuery.StoredProcedure classes to generate SQL for you, as the scripts it produces is parameterized. All of the functions can be altered to generate parameterized instead of sanitized scripts. Ever since people have started using this, I have been maintaining backwards compatibility. However, I may break this in the future, as I do not wish to teach one who is learning dangerous/bad habits. This project is a few years old, and its already showing its age. What is probably needed here is a total re-write, deprecating this version while keep it available for legacy users after slapping big warnings all over the place. This project was designed to generate the SQL scripts for standing up a database for a project, using only MY input as data. This project was never designed to process a USER'S input.! Even if the data isn't coming from an adversary, client/user/manually entered data is notoriously inconsistent. Please do not use this code on any input that did not come from you, without first implementing parameterization. Again, please see the SQLScript.StoredProcedure class for inspiration on how to do that.

Usage examples:

DataTable from class type (using generics):

DataTable dataTable = Table.FromClass<PurchaseOrder>();
string createTableFromTable = SQLScript.CreateTable(dataTable);

Generate scripts to create stored procedures:

File.WriteAllText("sp_InsertRow.sql", StoredProcedure.Insert(dataTable);
File.WriteAllText("sp_ActivateAccount.sql", StoredProcedure.Update, "Active = 0");

Instance of class to an INSERT INTO script:

PurchaseOrder classInstance = new PurchaseOrder();
string insertIntoFromClass = SQLScript.InsertInto(classInstance);

Populate a List<foo> of your data class from a DataTable using reflection, populating public properties with names identical to the column names:

IList<PurchaseOrder> orderList = Table.ToClassInstanceCollection<PurchaseOrder>(tableWithManyRecords);

Write out SQL script to a file:

File.WriteAllText("create_table.sql",SQLScript.CreateTable(dataTable));

Or use it directly in a NonQueryCommand:

DatabaseQuery.NonQueryCommand(ConnectionString, insertIntoFromClass);

DataTable from a query:

DataTable queryTable = DatabaseQuery.ToDataTable(ConnectionString, "SELECT TOP 1 * FROM [{0}]", TableName);

Check that our DataTable is valid:

if (!Helper.IsValidDatatable(dataTable))
	return;
if (!Helper.IsValidDatatable(queryTable))
	return;

Emit a dynamic assembly, using Reflection.Emit, where the class's public properties' names match the DataTable's ColumnNames:

Type datatableAssembly = Table.ToAssembly(dataTable);

Generate the C# class as C# code using code DOM:

FileInfo csCodeFile = Table.ToCSharpCode(queryTable);

Class Hierarchy and Their Methods

namespace EntityJustWorks.SQL
{
	public static class Table
	{
		FromClass<T>();
		FromClassInstance<T>(System.Data.DataTable, T);
		FromClassInstanceCollection<T>(T[]);
		FromQuery(string, string, object[]);
		ToAssembly(System.Data.DataTable);
		ToClassInstanceCollection<T>(System.Data.DataTable);
		ToCSharpCode(System.Data.DataTable, string);
	}

	public static class ClassInstance
	{
		FromQuery<T>(string, string, object[]);
		FromDataTable<T>(System.Data.DataTable);
		ToDataTable<T>();
		ToDataTable<T>(T[]);
		ToCSharpCode<T>(string);
		ToAssembly<T>();	
	}

	public static class SQLScript
	{
		CreateTable<T>(T[]);
		CreateTable(System.Data.DataTable);
		InsertInto<T>(T[]);
		InsertInto(System.Data.DataRow);
	}

	public static class SQLScript.StoredProcedure
	{
		Insert(System.Data.DataTable);
		Update(System.Data.DataTable, string);
		GenerateParameterList(System.Data.DataTable);
	}
	
	public static class DatabaseQuery
	{
		ToClass<T>(string, string, object[]);
		ToDataTable(string, string, object[]);
		NonQueryCommand(string, string, object[]);
		ToScalarValue<T>(string, string, object[]);
	}

	public static class Code
	{
		CreateClass(string);
		CreateCode(System.Data.DataTable, string);
		CreateCodeNamespace(System.Data.DataTable);
		CreateConstructor(string);
		CreateGeneratorOptions();
		CreateProperty(string, System.Type);	
	}
	
	public static class Helper
	{
		EscapeSingleQuotes(string);
		ListToDelimitedString<T>(System.Collections.Generic.IEnumerable<T>, string, string, string);
		RowToValueString(System.Data.DataRow);
		TableToColumnsString(System.Data.DataTable);
		IsValidDatatable(System.Data.DataTable, bool);
		IsCollectionEmpty<T>(System.Collections.Generic.IEnumerable<T>);
		IsNullableType(System.Type);
	}
}
Product Compatible and additional computed target framework versions.
.NET Framework net45 is compatible.  net451 was computed.  net452 was computed.  net46 was computed.  net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

This package has no dependencies.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.0 789 11/24/2019