SqlWorker provides simple API for writing queries and rids programmer from necessity of manually operating with DbConnection, DbTransaction, DbCommand and DbDataReader.

Minimalistic API allows developer to most easily do ordinary things with database; library automatically manages DbConnection, DbTransaction, DbCommand and DbDataReader objects. Developer only writes business-logic code.

Parameters can be transmitted as Dictionary of String and Object or using provided syntax sugar.
Also, developer can transmit delegate to specify DataReader processing. Result type is IEnumerable, so delegates are executed while receiving the data.

Solution includes test project with usage examles.

Supported DBMS: PostgreSQL and MS SQL.
Support of any other system can be implemented by realising constructor and two functions!



Examples:

Initialisation

var sw = new SqlWorker.MSSqlWorker("<server>", "<database>"); // connecting using windows authentification
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.Select(@"
    select top 100 id, name, comment
    from <table> order by id desc",
    delegate (System.Data.Common.DbDataReader dr)
    {
    	return new
    	{
    		id = (int)dr[0],
		name = (String)dr[1],
		comment = (String)dr[2]
	};
    }
);
/* returns IEnumerable,
 * so processing can be began 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.Select(@"
    select top 100 id, name, comment
    where name like @name_mask
    from <table> order by id desc",
    dr => new
    {
        id = (int)dr[0],
        name = (String)dr[1],
        comment = (String)dr[2]
    },
    new System.Data.SqlClient.SqlParameter("name_mask", "%foo%")
).ToArray();

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

// getting data from stored procedure
var data3 = SW.Select("<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
).ToArray;


parameters can be specified as:



Execute non-query request

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


In transaction

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


Insert

void RoomAdd(Room room)
{
    sw.InsertValues("unit_hostel.room", // table name (optionally with schema name)
    new SWParameters
    {
        { "flat_id", room.FlatId }, // attribute values
        { "room_index", room.RoomIndex },
        { "area", room.Area },
        { "room_category", room.room_category },
        { "room_category_description", room.room_category_description },
        { "is_reside", room.IsReside }
    });
}
// element's attributes names must be equal to table's columns


Update

public ActionResult FlatEdit(Flat flat)
{
    sw.UpdateValues("unit_hostel.flat", // table name
        new SWParameters
        { // values to update
            { "number", flat.Number },
            { "floor_n", flat.FloorN },
            { "subdepartment", flat.SubDepartment },
            { "flat_category", flat.flat_category },
            { "flat_category_description", flat.flat_category_description },
        },
        new SWParameters { { "id", flat.Id } } // condition
    );
}


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[3],
                    name_first = (String)pers[4],
                    birth_date = DateTime.Parse((String)pers[6]),
                }
    ), // first parameter - IEnumerable with values
    "home_mephi_ru.persons" // table name to insert
  );
}


Getting output parameter from stored procedure

MSSqlWorker.DbParametersConstructor parameters_raw = new SqlWorker.SWParameters
{
    { "foo", "bar" },
    { "backvalue", 0, DbType.Int32, System.Data.ParameterDirection.Output }
}; // implicit conversion to DbParametersConstructor

System.Data.Common.DbParameter[] parameters_sqlparamarray = parameters_raw;
// anoter implicit conversion to array on DbParameter
// this way it works when passing arguments in many different ways to *Select* function
// important! we need DbParameter[] variable to get outputed value

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

var ouput_value = (int)parameters_sqlparamarray[1].Value;


Upload file to SQL Server Filestream

sw.InsertFileNoStoredProcs(
    "register.files", // table name
    "id", // ROWGUID column
    "data", // filestream data column
    new Dictionary<string, object>
    { // other parameters to be inserted
        { "id", fileId },
        { "name", f.FileName },
        { "size", f.ContentLength },
        { "mime_type", f.ContentType }
    },
    f.InputStream // System.IO.Stream source
);