Skip to content

Commit 3f1eda4

Browse files
7k8m7k8m
committed
Query method implemented.
1 parent e4d8947 commit 3f1eda4

4 files changed

Lines changed: 245 additions & 0 deletions

File tree

‎DirectSQL/Database.cs‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,8 @@ public abstract class Database
1010
public delegate void SqlExecution(IDbConnection connection, IDbTransaction transaction);
1111
public delegate Task AsyncSqlExecution(IDbConnection connection, IDbTransaction transaction);
1212

13+
public delegate void ReadSqlResult(SqlResult result);
14+
1315
public async Task ProcessAsync(AsyncSqlExecution execute)
1416
{
1517

@@ -60,6 +62,68 @@ public void Process(SqlExecution execute)
6062
}
6163

6264
protected abstract IDbConnection CreateConnection();
65+
protected abstract IDbDataParameter CreateDbDataParameter(String name, Object value);
66+
67+
public static int ExecuteNonQuery(
68+
string sql,
69+
IDbDataParameter[] parameters,
70+
IDbConnection connection,
71+
IDbTransaction transaction)
72+
{
73+
using( var command = connection.CreateCommand())
74+
{
75+
command.Transaction = transaction;
76+
77+
command.CommandText = sql;
78+
SetParameters(command, parameters);
79+
80+
return command.ExecuteNonQuery();
81+
82+
}
83+
}
84+
85+
86+
public static int ExecuteNonQuery(
87+
string sql,
88+
IDbConnection connection,
89+
IDbTransaction transaction)
90+
{
91+
return ExecuteNonQuery(sql, new IDbDataParameter[0], connection, transaction);
92+
}
93+
94+
95+
public static void Query(
96+
string sql,
97+
IDbDataParameter[] parameters,
98+
IDbConnection connection,
99+
IDbTransaction transaction,
100+
ReadSqlResult readResult)
101+
{
102+
using (var result = new SqlResult(sql,parameters,connection,transaction))
103+
{
104+
result.Init();
105+
readResult(result);
106+
}
107+
}
108+
109+
110+
public static void Query(
111+
string sql,
112+
IDbConnection connection,
113+
IDbTransaction transaction,
114+
ReadSqlResult readResult)
115+
{
116+
Query(sql, new IDbDataParameter[0], connection, transaction, readResult);
117+
}
118+
119+
120+
internal static void SetParameters(IDbCommand command, IDbDataParameter[] parameters)
121+
{
122+
foreach (var parameter in parameters)
123+
{
124+
command.Parameters[parameter.ParameterName] = parameter;
125+
}
126+
}
63127

64128
}
65129
}

‎DirectSQL/SqlLite/SqlLiteDatabase.cs‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,5 +21,11 @@ protected override IDbConnection CreateConnection()
2121
{
2222
return new SQLiteConnection( _sqlLiteConnectionString );
2323
}
24+
25+
26+
protected override IDbDataParameter CreateDbDataParameter(string name, object value)
27+
{
28+
return new SQLiteParameter(name, value);
29+
}
2430
}
2531
}

‎DirectSQL/SqlResult.cs‎

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,109 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Collections.Immutable;
4+
using System.Data;
5+
using System.Dynamic;
6+
7+
namespace DirectSQL
8+
{
9+
public class SqlResult:IDisposable
10+
{
11+
private IDataReader _reader;
12+
private IDbCommand _command;
13+
14+
private ImmutableArray<String> _resultFields;
15+
16+
public IDataReader Reader
17+
{
18+
get
19+
{
20+
return _reader;
21+
}
22+
}
23+
24+
public IDbCommand Command
25+
{
26+
get
27+
{
28+
return _command;
29+
}
30+
}
31+
32+
public ImmutableArray<String> ResultFields
33+
{
34+
get
35+
{
36+
InitResultFields();
37+
return _resultFields;
38+
}
39+
}
40+
41+
internal SqlResult (
42+
String sql,
43+
IDbDataParameter[] parameters,
44+
IDbConnection connection,
45+
IDbTransaction transaction)
46+
{
47+
_command = connection.CreateCommand();
48+
49+
_command.CommandText = sql;
50+
Database.SetParameters(_command, parameters);
51+
52+
_command.Transaction = transaction;
53+
54+
}
55+
56+
57+
public bool Next()
58+
{
59+
return _reader.Read();
60+
}
61+
62+
public dynamic ResultValues()
63+
{
64+
var values = new ExpandoObject();
65+
66+
for (int i = 0; i < _reader.FieldCount; i++)
67+
{
68+
values.TryAdd(ResultFields[i],_reader.GetValue(i));
69+
}
70+
71+
return values;
72+
73+
}
74+
75+
internal void Init()
76+
{
77+
_reader = _command.ExecuteReader();
78+
}
79+
80+
private void InitResultFields()
81+
{
82+
if (_resultFields != null)
83+
return; //Already initialized. No need to init again.
84+
85+
List<string> list = new List<string>();
86+
for(int i = 0; i < _reader.FieldCount; i ++)
87+
{
88+
list.Add(_reader.GetName(i));
89+
}
90+
91+
_resultFields = ImmutableArray.ToImmutableArray<String>(list);
92+
93+
}
94+
95+
internal void Close()
96+
{
97+
if (_reader != null)
98+
_reader.Close();
99+
100+
if ( _command != null )
101+
_command.Dispose();
102+
}
103+
104+
public void Dispose()
105+
{
106+
Close();
107+
}
108+
}
109+
}

‎TestSqlLiteDatabase/QueryTest.cs‎

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
using Microsoft.VisualStudio.TestTools.UnitTesting;
2+
3+
using System;
4+
using System.Collections.Generic;
5+
using System.Text;
6+
7+
using System.Data;
8+
9+
using DirectSQL.SqlLite;
10+
11+
namespace TestSqlLiteDatabase
12+
{
13+
[TestClass]
14+
public class QueryTest
15+
{
16+
[TestMethod]
17+
public void TestQuery()
18+
{
19+
SqlLiteDatabase db = new SqlLiteDatabase("Data Source=:memory:");
20+
db.Process((connection, transaction) =>
21+
{
22+
CreateTableForTest(connection);
23+
24+
SqlLiteDatabase.ExecuteNonQuery(
25+
"insert into TEST_TABLE(TEST_VAL1,TEST_VAL2) values('abcdef',123)",
26+
connection,
27+
transaction);
28+
29+
SqlLiteDatabase.Query(
30+
"select TEST_VAL1,TEST_VAL2 from TEST_TABLE",
31+
connection,
32+
transaction,
33+
(result) => {
34+
if (result.Next())
35+
{
36+
var resultValues = result.ResultValues();
37+
Assert.AreEqual(resultValues.TEST_VAL1, "abcdef");
38+
Assert.AreEqual(resultValues.TEST_VAL2, 123);
39+
}
40+
else
41+
{
42+
Assert.Fail();
43+
}
44+
}
45+
);
46+
});
47+
}
48+
49+
private static void CreateTableForTest(IDbConnection connection)
50+
{
51+
using( var command = connection.CreateCommand())
52+
{
53+
command.CommandText =
54+
"create table " +
55+
"TEST_TABLE(" +
56+
"TEST_VAL1 text," +
57+
"TEST_VAL2 integer" +
58+
")";
59+
60+
command.ExecuteNonQuery();
61+
62+
}
63+
}
64+
65+
}
66+
}

0 commit comments

Comments
 (0)