Home

Overview

SqlWorker allows developer to most easily run database queries from .Net applications. By default, library automatically manages DbConnection, DbCommand and DbDataReader objects. Developer only writes queries.

It can be useful when implementing simple applications and utilities, that execute a few queries, or as workaround for ORM system issues when performing complicated stuff.

Parameters are implicity converted to the right IDbParamer[] array from Dictionary of String and Object or using provided helper classes. Also, developer transmits delegate to specify DataReader processing. Result type is IEnumerable, so delegates are executed while receiving the data and resources are released when reading is over.

Solution includes test project with usage examles

Supported API: OLEDB, PostgreSQL, MySql and MS SQL. Support of any other system can be added by implementing constructor and two functions!

Examples:

Initialisation

await using var SW = new SqlWorker.MsSqlWorker("<server>", "<database>"); // connecting using windows authentification
await using var sw2 = new SqlWorker.MsSqlWorker("<server>", "<database>", "login", "password"); // connecting using sql server authentification

Simple select and getting a collection of anonymous type

var dataIEnumerable = SW.Query(@"
    select id, name, comment
    from <table>",
    (System.Data.IDataReader dr) =>
        new {
        	id = (int)dr[0],
        	name = (String)dr[1],
        	comment = (String)dr[2]
        }
);
/* returns IEnumerable,
 * so processing can be started before all of the data has been completely loaded */
// if buffer with data is required, perform .ToArray(), or .ToList()
var dataArray = dataIEnumerable.ToArray();

Query with arguments

// query with a single parameter
var data1 = SW.Query(@"
    select id, name, comment
    from <table>
    where name like @name_mask",
    dr => new
    {
        id = (int)dr[0],
        name = (String)dr[1],
        comment = (String)dr[2]
    },
    new System.Data.SqlClient.SqlParameter("name_mask", "%foo%")
);

// query with multiple parameters
var data2 = SW.Query(@"
    select id, name, comment
    from <table>
    where name like @name_mask and comment like @comment_mask",
    dr => new
    {
        id = (int)dr[0],
        name = (String)dr[1],
        comment = (String)dr[2]
    },
    new SqlWorker.SWParameters
    {
        { "name_mask", "%foo%" },
        { "comment_mask", "%bar%" },
    }
);

parameters can be specified as:

• System.Data.IDataParameter,
• array of IDataParameter,
• Dictionary<String, Object> with param name and value,
• SWParameters structure - a collection of objects where name and value must be specified and also DbType and System.Data.ParameterDirection can be specified

DbParametersConstructor class is used to implicitly convert from those datatypes to array of DbParameter

Getting data from stored procedure

var data3 = SW.Query("<stored procedure>",
    dr => new
    {
        id = (int)dr[0],
        name = (String)dr[1],
        comment = (String)dr[2]
    },
    new SqlWorker.SWParameters
    {
        { "arg1", 42 },
        { "arg2", new DateTime(1970, 1, 1) },
    },
    commandType: CommandType.StoredProcedure // stored procedure
).ToArray;

Execute non-query request

sw.Exec(
    "delete from unit_hostel.place where id = @pid",
    new SWParameters { { "pid", id } }
);

Bulk insert

suppose, we have large JSON file: '[ [ <id>, <last_name>, <first_name>, <birthday> ], ... ]'

using (var jsonReader = new Newtonsoft.Json.JsonTextReader(streamReader))
{
    var ins_id = Guid.NewGuid();
    sw.BulkCopy(
        new Newtonsoft.Json.JsonSerializer()
            .Deserialize<IEnumerable<Object[]>>(jsonReader)
            .Select(pers =>
                new
                {
                    person_id = (long)pers[0],
                    name_last = (String)pers[1],
                    name_first = (String)pers[2],
                    birth_date = DateTime.Parse((String)pers[3]),
                }
    ), // first parameter - IEnumerable with values
    "dbo.persons" // table name to insert
  );
}

Getting output parameter from stored procedure

DbParametersConstructor parametersRaw = new SqlWorker.SWParameters
{
    { "foo", "bar" },
    { "backvalue", 0, DbType.Int32, System.Data.ParameterDirection.Output }
};

// DbParametersConstructor variable required for getting outputed value

var data2 = SW.Exec(
    "<stored procedure>",
    parametersRaw,
    commandType: CommandType.StoredProcedure);

var ouputValue = (int)parametersRaw.Parameters[1].Value;