Skip to content

Commit cb3d7a9

Browse files
ThatRendleThatRendle
committed
Improving SQL CE 4.0 support
1 parent dbdd1fe commit cb3d7a9

19 files changed

Lines changed: 142 additions & 77 deletions

‎Simple.Data.Ado/AdoAdapter.cs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -215,5 +215,15 @@ public int Delete(string tableName, SimpleExpression criteria, IAdapterTransacti
215215
var commandBuilder = new DeleteHelper(_schema).GetDeleteCommand(tableName, criteria);
216216
return Execute(commandBuilder, transaction);
217217
}
218+
219+
public string GetIdentityFunction()
220+
{
221+
return _connectionProvider.GetIdentityFunction();
222+
}
223+
224+
public bool ProviderSupportsCompoundStatements
225+
{
226+
get { return _connectionProvider.SupportsCompoundStatements; }
227+
}
218228
}
219229
}

‎Simple.Data.Ado/AdoAdapterInserter.cs‎

Lines changed: 43 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -32,13 +32,25 @@ public IDictionary<string, object> Insert(string tableName, IDictionary<string,
3232

3333
string insertSql = "insert into " + table.QualifiedName + " (" + columnList + ") values (" + valueList + ")";
3434

35-
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
36-
37-
if (identityColumn != null)
35+
var identityFunction = _adapter.GetIdentityFunction();
36+
if (!string.IsNullOrWhiteSpace(identityFunction))
3837
{
39-
insertSql += "; select * from " + table.QualifiedName + " where " + identityColumn.QuotedName +
40-
" = scope_identity()";
41-
return ExecuteSingletonQuery(insertSql, data.Values.ToArray());
38+
var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
39+
40+
if (identityColumn != null)
41+
{
42+
var selectSql = "select * from " + table.QualifiedName + " where " + identityColumn.QuotedName +
43+
" = " + identityFunction;
44+
if (_adapter.ProviderSupportsCompoundStatements)
45+
{
46+
insertSql += "; " + selectSql;
47+
return ExecuteSingletonQuery(insertSql, data.Values.ToArray());
48+
}
49+
else
50+
{
51+
return ExecuteSingletonQuery(insertSql, selectSql, data.Values.ToArray());
52+
}
53+
}
4254
}
4355

4456
Execute(insertSql, data.Values.ToArray());
@@ -64,6 +76,31 @@ internal IDictionary<string, object> ExecuteSingletonQuery(string sql, params ob
6476
}
6577
}
6678

79+
internal IDictionary<string, object> ExecuteSingletonQuery(string insertSql, string selectSql, params object[] values)
80+
{
81+
if (_transaction != null)
82+
{
83+
var command = CommandHelper.Create(_transaction.Connection, insertSql, values.ToArray());
84+
command.Transaction = _transaction;
85+
TryExecute(command);
86+
command.CommandText = selectSql;
87+
command.Parameters.Clear();
88+
return TryExecuteSingletonQuery(command);
89+
}
90+
91+
using (var connection = _adapter.CreateConnection())
92+
{
93+
using (var command = CommandHelper.Create(connection, insertSql, values.ToArray()))
94+
{
95+
connection.Open();
96+
TryExecute(command);
97+
command.CommandText = selectSql;
98+
command.Parameters.Clear();
99+
return TryExecuteSingletonQuery(command);
100+
}
101+
}
102+
}
103+
67104
private static IDictionary<string, object> TryExecuteSingletonQuery(IDbCommand command)
68105
{
69106
try

‎Simple.Data.Ado/IConnectionProvider.cs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,5 +9,7 @@ public interface IConnectionProvider
99
DbConnection CreateConnection();
1010
ISchemaProvider GetSchemaProvider();
1111
string ConnectionString { get; }
12+
bool SupportsCompoundStatements { get; }
13+
string GetIdentityFunction();
1214
}
1315
}

‎Simple.Data.Ado/ObjectName.cs‎

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,9 +10,15 @@ public class ObjectName : IEquatable<ObjectName>
1010
private readonly string _schema;
1111
private readonly string _name;
1212

13+
public ObjectName(object schema, object name)
14+
{
15+
if (name == null) throw new ArgumentNullException("name");
16+
_schema = schema != null && schema != DBNull.Value ? (string)schema : null;
17+
_name = (string)name;
18+
}
19+
1320
public ObjectName(string schema, string name)
1421
{
15-
if (schema == null) throw new ArgumentNullException("schema");
1622
if (name == null) throw new ArgumentNullException("name");
1723
_schema = schema;
1824
_name = name;
@@ -77,7 +83,7 @@ public override int GetHashCode()
7783
{
7884
unchecked
7985
{
80-
return (_schema.GetHashCode()*397) ^ _name.GetHashCode();
86+
return ((_schema??string.Empty).GetHashCode()*397) ^ _name.GetHashCode();
8187
}
8288
}
8389

‎Simple.Data.Ado/Properties/AssemblyInfo.cs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,5 +36,6 @@
3636
[assembly: AssemblyFileVersion("1.0.0.0")]
3737
[assembly: InternalsVisibleTo("Simple.Data.IntegrationTest")]
3838
[assembly: InternalsVisibleTo("Simple.Data.SqlCeTest")]
39+
[assembly: InternalsVisibleTo("Simple.Data.SqlCe40Test")]
3940
[assembly: InternalsVisibleTo("Simple.Data.SqlTest")]
4041
[assembly: InternalsVisibleTo("Simple.Data.Ado.Test")]

‎Simple.Data.Mocking/Ado/MockConnectionProvider.cs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,5 +36,15 @@ public string ConnectionString
3636
{
3737
get { return _connection.ConnectionString; }
3838
}
39+
40+
public string GetIdentityFunction()
41+
{
42+
return null;
43+
}
44+
45+
public bool SupportsCompoundStatements
46+
{
47+
get { return false; }
48+
}
3949
}
4050
}

‎Simple.Data.SqlCe35/SqlCe35ConnectionProvider.cs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,5 +32,15 @@ public string ConnectionString
3232
{
3333
get { return _connectionString; }
3434
}
35+
36+
public string GetIdentityFunction()
37+
{
38+
return null;
39+
}
40+
41+
public bool SupportsCompoundStatements
42+
{
43+
get { return false; }
44+
}
3545
}
3646
}

‎Simple.Data.SqlCe40/SqlCe40ConnectionProvider.cs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,5 +49,15 @@ public string ConnectionString
4949
{
5050
get { return _connectionString; }
5151
}
52+
53+
public string GetIdentityFunction()
54+
{
55+
return "@@IDENTITY";
56+
}
57+
58+
public bool SupportsCompoundStatements
59+
{
60+
get { return false; }
61+
}
5262
}
5363
}

‎Simple.Data.SqlCe40/SqlCe40SchemaProvider.cs‎

Lines changed: 18 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,6 @@
11
using System;
22
using System.Collections.Generic;
33
using System.Data;
4-
using System.Data.SqlClient;
54
using System.Data.SqlServerCe;
65
using System.Linq;
76
using Simple.Data.Ado;
@@ -37,20 +36,12 @@ private static Table SchemaRowToTable(DataRow row)
3736

3837
public IEnumerable<Column> GetColumns(Table table)
3938
{
40-
foreach (var row in GetColumnsDataTable(table).AsEnumerable())
41-
{
42-
yield return new Column(row["COLUMN_NAME"].ToString(), table);
43-
}
44-
}
45-
46-
private static Column SchemaRowToColumn(Table table, DataRow row)
47-
{
48-
return new Column(row["name"].ToString(), table, (bool)row["is_identity"]);
39+
return Enumerable.Select(GetColumnsDataTable(table).AsEnumerable(), row => new Column(row["COLUMN_NAME"].ToString(), table, row["AUTOINC_SEED"] != null));
4940
}
5041

5142
public IEnumerable<Procedure> GetStoredProcedures()
5243
{
53-
return GetSchema("Procedures").Select(SchemaRowToStoredProcedure);
44+
return Enumerable.Empty<Procedure>();
5445
}
5546

5647
private IEnumerable<DataRow> GetSchema(string collectionName, params string[] constraints)
@@ -63,34 +54,9 @@ private IEnumerable<DataRow> GetSchema(string collectionName, params string[] co
6354
}
6455
}
6556

66-
private static Procedure SchemaRowToStoredProcedure(DataRow row)
67-
{
68-
return new Procedure(row["ROUTINE_NAME"].ToString(), row["SPECIFIC_NAME"].ToString(), row["ROUTINE_SCHEMA"].ToString());
69-
}
70-
7157
public IEnumerable<Parameter> GetParameters(Procedure storedProcedure)
7258
{
73-
return GetSchema("ProcedureParameters", null, storedProcedure.Schema, storedProcedure.SpecificName)
74-
.Select(SchemaRowToProcedureParameter);
75-
}
76-
77-
private static Parameter SchemaRowToProcedureParameter(DataRow row)
78-
{
79-
return new Parameter(row["parameter_name"].ToString(), SqlTypeResolver.GetClrType(row["data_type"].ToString()),
80-
DirectionFromString(row["parameter_mode"].ToString()));
81-
}
82-
83-
private static ParameterDirection DirectionFromString(string mode)
84-
{
85-
switch (mode)
86-
{
87-
case "IN":
88-
return ParameterDirection.Input;
89-
case "OUT":
90-
return ParameterDirection.Output;
91-
default:
92-
return ParameterDirection.InputOutput;
93-
}
59+
return Enumerable.Empty<Parameter>();
9460
}
9561

9662
public Key GetPrimaryKey(Table table)
@@ -99,7 +65,7 @@ public Key GetPrimaryKey(Table table)
9965
return new Key(GetPrimaryKeys(table.ActualName).AsEnumerable()
10066
.Where(
10167
row =>
102-
row["TABLE_SCHEMA"].ToString() == table.Schema && row["TABLE_NAME"].ToString() == table.ActualName)
68+
AreEqual(row["TABLE_SCHEMA"], table.Schema) && row["TABLE_NAME"].ToString() == table.ActualName)
10369
.OrderBy(row => (int)row["ORDINAL_POSITION"])
10470
.Select(row => row["COLUMN_NAME"].ToString()));
10571
}
@@ -108,20 +74,29 @@ public IEnumerable<ForeignKey> GetForeignKeys(Table table)
10874
{
10975
if (table == null) throw new ArgumentNullException("table");
11076
var groups = GetForeignKeys(table.ActualName)
111-
.Where(row =>
112-
row["TABLE_SCHEMA"].ToString() == table.Schema && row["TABLE_NAME"].ToString() == table.ActualName)
77+
.Where(row => AreEqual(row["TABLE_SCHEMA"], table.Schema)
78+
&& row["TABLE_NAME"].ToString() == table.ActualName)
11379
.GroupBy(row => row["CONSTRAINT_NAME"].ToString())
11480
.ToList();
11581

11682
foreach (var group in groups)
11783
{
118-
yield return new ForeignKey(new ObjectName(group.First()["TABLE_SCHEMA"].ToString(), group.First()["TABLE_NAME"].ToString()),
84+
yield return new ForeignKey(new ObjectName(group.First()["TABLE_SCHEMA"], group.First()["TABLE_NAME"]),
11985
group.Select(row => row["COLUMN_NAME"].ToString()),
120-
new ObjectName(group.First()["UNIQUE_TABLE_SCHEMA"].ToString(), group.First()["UNIQUE_TABLE_NAME"].ToString()),
86+
new ObjectName(group.First()["UNIQUE_TABLE_SCHEMA"], group.First()["UNIQUE_TABLE_NAME"]),
12187
group.Select(row => row["UNIQUE_COLUMN_NAME"].ToString()));
12288
}
12389
}
12490

91+
private static bool AreEqual(object string1, object string2)
92+
{
93+
if (string1 == DBNull.Value) string1 = null;
94+
if (string2 == DBNull.Value) string2 = null;
95+
if (string1 == null && string2 == null) return true;
96+
if (string1 == null || string2 == null) return false;
97+
return string1.ToString().Trim().Equals(string2.ToString().Trim());
98+
}
99+
125100
public string QuoteObjectName(string unquotedName)
126101
{
127102
if (unquotedName == null) throw new ArgumentNullException("unquotedName");
@@ -136,7 +111,7 @@ public Type DataTypeToClrType(string dataType)
136111

137112
private DataTable GetColumnsDataTable(Table table)
138113
{
139-
return SelectToDataTable("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + table.ActualName + "'");
114+
return SelectToDataTable("SELECT COLUMN_NAME, AUTOINC_SEED FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + table.ActualName + "'");
140115
}
141116

142117
private DataTable GetPrimaryKeys()

‎Simple.Data.SqlCe40Test/FindTests.cs‎

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,16 +1,12 @@
1-
using System;
2-
using System.Collections;
3-
using System.Diagnostics;
4-
using System.Reflection;
5-
using System.Text;
1+
using System.Reflection;
62
using System.Collections.Generic;
7-
using System.Linq;
83
using NUnit.Framework;
94
using System.IO;
105
using Simple.Data.Ado;
116
using Simple.Data.SqlCe40;
7+
using Simple.Data.SqlCeTest;
128

13-
namespace Simple.Data.SqlCeTest
9+
namespace Simple.Data.SqlCe40Test
1410
{
1511
/// <summary>
1612
/// Summary description for FindTests
@@ -41,7 +37,7 @@ public void TestFindById()
4137
public void TestAll()
4238
{
4339
var db = Database.OpenFile(DatabasePath);
44-
var all = new List<dynamic>(db.Users.All().Cast<dynamic>());
40+
var all = new List<object>(db.Users.All().Cast<dynamic>());
4541
Assert.IsNotEmpty(all);
4642
}
4743

@@ -68,10 +64,11 @@ public void TestInsert()
6864
{
6965
var db = Database.OpenFile(DatabasePath);
7066

71-
db.Users.Insert(Name: "Alice", Password: "foo", Age: 29);
72-
User user = db.Users.FindByNameAndPassword("Alice", "foo");
67+
var user = db.Users.Insert(Name: "Alice", Password: "foo", Age: 29);
7368

7469
Assert.IsNotNull(user);
70+
Assert.AreEqual("Alice", user.Name);
71+
Assert.AreEqual("foo", user.Password);
7572
Assert.AreEqual(29, user.Age);
7673
}
7774
}

0 commit comments

Comments
 (0)