Last active
December 15, 2015 21:59
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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