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 );