NarsilWorks.Query 1.0.3

How to use NarsilWorks Query

To use Query, you need to define a record or a class that represents the table in the database that you plan to manipulate.

For example:

A table we call tcoUsers

+-----------+--------+----+-----+
|Column Name|Type    |Size|Null |
+-----------+--------+----+-----+
|UserID     |varchar |  40|false|
|Fullname   |varchar | 100|false|
|Age        |smallint|   8|false|
|BirthDate  |datetime|    |false|
+-----------+--------+----+-----+

Which is represented by a C# record:

public record Users {
  public string UserID {get;set;}
  public string Name {get;set;} // We deliberately did not put the matching name here
  public short Age {get;set;}
  public DateTime BirthDate {get;set;}
}

The record Users needs to be initialized and filled with values in order to persist in the database. The problem with this is that it wont find any table in the database, because there's no hint provided. To provide hint to Query, we need to put an attribute to the record, including its fields. Putting an attribute to the fields, especially for strings, will allow Query to validate the input fields.

[QueryObjectAttribute(DataName = "tcoUsers")]
public record Users {

	[QueryColumnAttribute(Type=System.Data.SqlDbType.VarChar, Size = 40)]
	public string UserID {get;set;}
	
	[QueryColumnAttribute(Name = "FullName" Type=System.Data.SqlDbType.VarChar, Size = 40)]
	public string Name {get;set;}
	
	public short Age {get;set;}
	public DateTime BirthDate {get;set;}
}

The record Users now has an attribute of QueryObjectAttribute set with DataName to "tcoUsers", which is the table to persist to. The field UserID was added with a QueryColumnAttribute "Type" to indicate that this is a varchar column, with a size of 40. The Name field also gets a QueryColumnAttribute as UserID, but with a Name attribute set to "FullName", which is the actual column name reserved to it.

To use in query generation:

// Create user with the following data
User data = new User{
	UserID = "admin",
	Name = "Administrator",
	Age = 100,
	BirthDate = new DateTime(1923,07,19)
};

// Create a Query object with a User type
// If the generation is successful, the output
// sql will contain an SQL query string, the args will 
// contain parameter values
string sql; object[] args; Exception err;
Query<User> q = new();

(sql, args, err) = q.Insert(data);
if (err != null) {
	throw err;
}

// Output:
// sql: INSERT INTO [tcoUsers] (UserID, FullName, Age, BirthDate) VALUES (@p1, @p2, @p3, @p4);
// args: Array of objects that contains: ["admin", "Administrator", 100, "1923/07/19 00:00:00"]

You can then put this in an SqlCommand class, with the parameters added with AddWithValue via a for i loop.

using (SqlConnection conn = new SqlConnection(<ConnectionString>)) {
try {
	conn.Open();
	
	SqlCommand cmd = new SqlCommand () {
	  Connection = conn,
	};
	
	User data = new User{
		UserID = "admin",
		Name = "Administrator",
		Age = 100,
		BirthDate = new DateTime(1923,07,19)
	};
	
	string sql; object[] args; Exception err;
	Query<User> q = new();
	
	(sql, args, err) = q.Insert(data);
	if (err != null) {
		throw err;
	}
	
	cmd.CommandText = sql;
	cmd.Parameters.Clear();
	for (int i = 0; i < args.Length; i++){
		cmd.Parameters.AddWithValue("@p" + (i + 1).ToString(), args[i]);
	}
	
catch (Exception ex) {
    throw ex;
} finally {
     if (conn.State != System.Data.ConnectionState.Closed) {
         conn.Close();
     }
 }

Note: The sample code is not tested in an actual program

Using Query gives you the control of streamlining your query, without strenuous filling all the table columns with the value defined by fields. The full documentation will be written next.

Contact me at narsilworks@gmail.com