Skip to content

Instantly share code, notes, and snippets.

@t3hc13h
Last active December 15, 2015 21:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save t3hc13h/5329815 to your computer and use it in GitHub Desktop.
Save t3hc13h/5329815 to your computer and use it in GitHub Desktop.
This is a first try at a simple SqlServer client module for Edge.js.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Edge.Sql
{
public class Startup
{
public async Task<object> Invoke(object i)
{
var connectionArgs = i as Dictionary<string, object>;
string connectionString = null;
if (connectionArgs != null)
connectionString = connectionArgs.TryGetObjectValueAs<string>("connectionString");
return new
{
query = (Func<object, Task<object>>)(async (input) =>
{
var info = GetExecutionInfo(input as Dictionary<string, object>, connectionString);
return await this.ExecuteQuery(info);
}),
exec = (Func<object, Task<object>>)(async (input) =>
{
var info = GetExecutionInfo(input as Dictionary<string, object>, connectionString);
return await this.ExecuteNonQuery(info);
})
};
}
private ExecutionInfo GetExecutionInfo(Dictionary<string, object> args, string connection = null)
{
var info = new ExecutionInfo();
info.CommandType = GetCommandType(args);
info.Parameters = args.TryGetObjectValueAs<Dictionary<string, object>>("commandParameters");
info.CommandText = args.TryGetObjectValueAs<string>("commandText");
info.ConnectionString = string.IsNullOrEmpty(connection) ? args.TryGetObjectValueAs<string>("connectionString") : connection;
return info;
}
private class ExecutionInfo
{
public string ConnectionString { get; set; }
public string CommandText { get; set; }
public IDictionary<string, object> Parameters { get; set; }
public CommandType CommandType { get; set; }
}
private CommandType GetCommandType(Dictionary<string, object> args)
{
object commandTypeString = null;
args.TryGetValue("commandType", out commandTypeString);
CommandType type;
if (Enum.TryParse(commandTypeString as string, true, out type))
return type;
return CommandType.Text;
}
async Task<object> ExecuteQuery(ExecutionInfo info)
{
List<object> rows = new List<object>();
using (var connection = new SqlConnection(info.ConnectionString))
{
using (var command = GetCommand(info, connection))
{
await connection.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
{
string[] fieldNames = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldNames[i] = reader.GetName(i);
}
IDataRecord record = (IDataRecord)reader;
while (await reader.ReadAsync())
{
object[] values = new object[fieldNames.Length];
reader.GetValues(values);
var resultRecord = new Dictionary<string, object>();
for (int i = 0; i < fieldNames.Length; i++)
{
resultRecord[fieldNames[i]] = values[i];
}
rows.Add(resultRecord);
}
}
}
}
return rows;
}
private SqlCommand GetCommand(ExecutionInfo info, SqlConnection connection)
{
var cmd = new SqlCommand(info.CommandText, connection);
cmd.CommandType = info.CommandType;
foreach (var parameter in info.Parameters)
cmd.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
return cmd;
}
async Task<object> ExecuteNonQuery(ExecutionInfo info)
{
using (SqlConnection connection = new SqlConnection(info.ConnectionString))
{
using (SqlCommand command = GetCommand(info,connection))
{
await connection.OpenAsync();
return await command.ExecuteNonQueryAsync();
}
}
}
}
public static class Extensions
{
public static T TryGetObjectValueAs<T>(this IDictionary<string, object> collection, string key) where T : class
{
object obj;
collection.TryGetValue(key, out obj);
return obj as T;
}
}
}
var edge = require('edge');
var sql = edge.func('Edge.Sql.dll')({
connectionString: 'Server=.\\SQLEXPRESS;Database=musicstore;Integrated Security=SSPI',
}, true);
sql.query({
commandText: 'select * from Albums where AlbumId = @Id',
commandParameters: {
Id: 1
}
}, function(error, result) {
if (error) throw error;
console.log(result);
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment