Skip to content

Commit eb1b29b

Browse files
muratcakirmuratcakir
committed
Implemented a legacy stored procedure execution switch for SqlServer 2008 and lower (has still problems with output params)
1 parent f9cada8 commit eb1b29b

3 files changed

Lines changed: 133 additions & 42 deletions

File tree

‎src/Libraries/SmartStore.Data/Extensions/DbContextExtensions.cs‎

Lines changed: 28 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,13 +2,24 @@
22
using System.Data.Entity;
33
using System.Data.Entity.Infrastructure;
44

5-
65
namespace SmartStore
76
{
87

8+
public class SqlServerInfo
9+
{
10+
public string ProductVersion { get; set; }
11+
public string PatchLevel { get; set; }
12+
public string ProductEdition { get; set; }
13+
public string ClrVersion { get; set; }
14+
public string DefaultCollation { get; set; }
15+
public string Instance { get; set; }
16+
public int Lcid { get; set; }
17+
public string ServerName { get; set; }
18+
}
19+
920
public static class DbContextExtensions
1021
{
11-
22+
1223
public static bool ColumnExists(this DbContext context, string tableName, string columnName)
1324
{
1425
if (context != null && tableName.HasValue() && columnName.HasValue())
@@ -70,5 +81,20 @@ public static int Execute(this DbContext context, string sql, params object[] pa
7081
return context.Database.ExecuteSqlCommand(sql, parameters);
7182
}
7283

84+
internal static SqlServerInfo GetSqlServerInfo(this DbContext context)
85+
{
86+
string sql = @"SELECT
87+
SERVERPROPERTY('productversion') as 'ProductVersion',
88+
SERVERPROPERTY('productlevel') as 'PatchLevel',
89+
SERVERPROPERTY('edition') as 'ProductEdition',
90+
SERVERPROPERTY('buildclrversion') as 'ClrVersion',
91+
SERVERPROPERTY('collation') as 'DefaultCollation',
92+
SERVERPROPERTY('instancename') as 'Instance',
93+
SERVERPROPERTY('lcid') as 'Lcid',
94+
SERVERPROPERTY('servername') as 'ServerName'";
95+
96+
return context.Database.SqlQuery<SqlServerInfo>(sql).FirstOrDefault();
97+
}
98+
7399
}
74100
}

‎src/Libraries/SmartStore.Data/ObjectContextBase.cs‎

Lines changed: 103 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,8 @@ namespace SmartStore.Data
2828
[DbConfigurationType(typeof(SmartDbConfiguration))]
2929
public abstract class ObjectContextBase : DbContext, IDbContext
3030
{
31+
private static bool? s_isSqlServer2012OrHigher = null;
32+
3133

3234
#region Ctor
3335

@@ -147,47 +149,85 @@ public virtual string CreateDatabaseScript()
147149
return base.Set<TEntity>();
148150
}
149151

152+
private IEnumerable<DbParameter> ToParameters(params object[] parameters)
153+
{
154+
if (parameters == null || parameters.Length == 0)
155+
return Enumerable.Empty<DbParameter>();
156+
157+
return parameters.Cast<DbParameter>();
158+
}
159+
150160
public virtual IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : BaseEntity, new()
151161
{
152162
// Add parameters to command
153-
if (parameters != null && parameters.Length > 0)
163+
var commandText2 = commandText;
164+
var dbParams = ToParameters(parameters);
165+
bool firstParam = true;
166+
bool hasOutputParams = false;
167+
foreach (var p in dbParams)
154168
{
155-
for (int i = 0; i <= parameters.Length - 1; i++)
169+
commandText += firstParam ? " " : ", ";
170+
firstParam = false;
171+
172+
commandText += "@" + p.ParameterName;
173+
if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
156174
{
157-
var p = parameters[i] as DbParameter;
158-
if (p == null)
159-
throw new Exception("Unsupported parameter type");
175+
// output parameter
176+
hasOutputParams = true;
177+
commandText += " output";
178+
}
179+
}
160180

161-
commandText += i == 0 ? " " : ", ";
162181

163-
commandText += "@" + p.ParameterName;
164-
if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
165-
{
166-
//output parameter
167-
commandText += " output";
168-
}
169-
}
182+
var isLegacyDb = !this.IsSqlServer2012OrHigher();
183+
if (isLegacyDb && hasOutputParams)
184+
{
185+
// SQL Server 2008 or lower is not capable of handling
186+
// stored procedures with output parameters
187+
return ExecuteStoredProcedureListLegacy<TEntity>(commandText2, dbParams);
170188
}
171189

172190
var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList();
191+
using (var scope = new DbContextScope(this, autoDetectChanges: false))
192+
{
193+
for (int i = 0; i < result.Count; i++)
194+
{
195+
result[i] = AttachEntityToContext(result[i]);
196+
}
197+
}
198+
return result;
199+
}
173200

174-
var autoDetect = this.AutoDetectChangesEnabled;
201+
private IList<TEntity> ExecuteStoredProcedureListLegacy<TEntity>(string commandText, IEnumerable<DbParameter> parameters) where TEntity : BaseEntity, new()
202+
{
203+
var connection = this.Database.Connection;
204+
// Don't close the connection after command execution
175205

176-
try
206+
// open the connection for use
207+
if (connection.State == ConnectionState.Closed)
208+
connection.Open();
209+
210+
// create a command object
211+
using (var cmd = connection.CreateCommand())
177212
{
178-
this.AutoDetectChangesEnabled = false;
213+
// command to execute
214+
cmd.CommandText = commandText;
215+
cmd.CommandType = CommandType.StoredProcedure;
179216

217+
// move parameters to command object
218+
cmd.Parameters.AddRange(parameters.ToArray());
219+
220+
// database call
221+
var reader = cmd.ExecuteReader();
222+
var result = ((IObjectContextAdapter)(this)).ObjectContext.Translate<TEntity>(reader).ToList();
180223
for (int i = 0; i < result.Count; i++)
181224
{
182225
result[i] = AttachEntityToContext(result[i]);
183226
}
227+
// close up the reader, we're done saving results
228+
reader.Close();
229+
return result;
184230
}
185-
finally
186-
{
187-
this.AutoDetectChangesEnabled = autoDetect;
188-
}
189-
190-
return result;
191231
}
192232

193233
/// <summary>
@@ -398,6 +438,28 @@ public static string GetConnectionString()
398438
throw Error.Application("A connection string could not be resolved for the parameterless constructor of the derived DbContext. Either the database is not installed, or the file 'Settings.txt' does not exist or contains invalid content.");
399439
}
400440

441+
protected internal bool IsSqlServer2012OrHigher()
442+
{
443+
if (!s_isSqlServer2012OrHigher.HasValue)
444+
{
445+
try
446+
{
447+
// TODO: actually we should cache this value by connection (string).
448+
// But fact is: it's quite unlikely that multiple DB versions are used within a single application scope.
449+
var info = this.GetSqlServerInfo();
450+
string productVersion = info.ProductVersion;
451+
int version = productVersion.Split(new char[] { '.' })[0].ToInt();
452+
s_isSqlServer2012OrHigher = version >= 11;
453+
}
454+
catch
455+
{
456+
s_isSqlServer2012OrHigher = false;
457+
}
458+
}
459+
460+
return s_isSqlServer2012OrHigher.Value;
461+
}
462+
401463
/// <summary>
402464
/// Attach an entity to the context or return an already attached entity (if it was already attached)
403465
/// </summary>
@@ -406,20 +468,20 @@ public static string GetConnectionString()
406468
/// <returns>Attached entity</returns>
407469
protected virtual TEntity AttachEntityToContext<TEntity>(TEntity entity) where TEntity : BaseEntity, new()
408470
{
409-
//little hack here until Entity Framework really supports stored procedures
410-
//otherwise, navigation properties of loaded entities are not loaded until an entity is attached to the context
411-
var alreadyAttached = Set<TEntity>().Local.Where(x => x.Id == entity.Id).FirstOrDefault();
412-
if (alreadyAttached == null)
413-
{
414-
//attach new entity
415-
Set<TEntity>().Attach(entity);
416-
return entity;
417-
}
418-
else
419-
{
420-
//entity is already loaded.
421-
return alreadyAttached;
422-
}
471+
// little hack here until Entity Framework really supports stored procedures
472+
// otherwise, navigation properties of loaded entities are not loaded until an entity is attached to the context
473+
var alreadyAttached = Set<TEntity>().Local.Where(x => x.Id == entity.Id).FirstOrDefault();
474+
if (alreadyAttached == null)
475+
{
476+
// attach new entity
477+
Set<TEntity>().Attach(entity);
478+
return entity;
479+
}
480+
else
481+
{
482+
// entity is already loaded.
483+
return alreadyAttached;
484+
}
423485
}
424486

425487
public bool IsAttached<TEntity>(TEntity entity) where TEntity : BaseEntity, new()
@@ -431,11 +493,12 @@ public static string GetConnectionString()
431493
public void DetachEntity<TEntity>(TEntity entity) where TEntity : BaseEntity, new()
432494
{
433495
Guard.ArgumentNotNull(() => entity);
434-
if (this.IsAttached(entity))
435-
{
436-
((IObjectContextAdapter)this).ObjectContext.Detach(entity);
437-
}
496+
if (this.IsAttached(entity))
497+
{
498+
((IObjectContextAdapter)this).ObjectContext.Detach(entity);
499+
}
438500
}
501+
439502
public void Detach(object entity)
440503
{
441504
((IObjectContextAdapter)this).ObjectContext.Detach(entity);

‎src/Presentation/SmartStore.Web/SmartStore.Web.csproj‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -565,6 +565,8 @@
565565
<Content Include="Content\editors\tinymce4\skins\smartstore\img\trans.gif" />
566566
<Content Include="Content\editors\tinymce4\skins\smartstore\img\wline.gif" />
567567
<Content Include="Content\editors\tinymce4\skins\smartstore\skin.ie7.min.css" />
568+
<Content Include="Content\editors\tinymce4\skins\smartstore\content.smartstore.css" />
569+
<Content Include="Content\editors\tinymce4\skins\smartstore\skin.smartstore.css" />
568570
<Content Include="Content\editors\tinymce4\skins\smartstore\skin.min.css" />
569571
<Content Include="Content\editors\tinymce4\themes\modern\theme.min.js" />
570572
<Content Include="Content\editors\tinymce4\tinymce.gzip.js" />

0 commit comments

Comments
 (0)