Skip to content

Commit bfc4cb4

Browse files
optimajetoptimajet
committed
Provider for MSSQL schema is added
1 parent 40dfb87 commit bfc4cb4

15 files changed

Lines changed: 242 additions & 191 deletions

‎Provider for MS SQL Server/DbObject.cs‎

Lines changed: 24 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -13,9 +13,27 @@ public class ColumnInfo
1313
public bool IsKey = false;
1414
public int Size = 256;
1515
}
16-
public class DbObject<T> where T : DbObject<T>, new()
16+
17+
public abstract class DbObject
1718
{
18-
public string DbTableName;
19+
public static string SchemaName { get; set; }
20+
}
21+
22+
public class DbObject<T> : DbObject where T : DbObject<T>, new()
23+
{
24+
// ReSharper disable once StaticMemberInGenericType
25+
public static string DbTableName;
26+
27+
public static string ObjectName
28+
{
29+
get
30+
{
31+
if (!string.IsNullOrWhiteSpace(SchemaName))
32+
return string.Format("[{0}].[{1}]", SchemaName, DbTableName);
33+
return string.Format("[{0}]", DbTableName);
34+
}
35+
}
36+
1937
public List<ColumnInfo> DbColumns = new List<ColumnInfo>();
2038

2139
public virtual object GetValue(string key)
@@ -65,10 +83,10 @@ public static T SelectByKey(SqlConnection connection, object id)
6583
var key = t.DbColumns.FirstOrDefault(c => c.IsKey);
6684
if(key == null)
6785
{
68-
throw new Exception(string.Format("Key for table {0} isn't defined.", t.DbTableName));
86+
throw new Exception(string.Format("Key for table {0} isn't defined.", ObjectName));
6987
}
7088

71-
string selectText = string.Format("SELECT * FROM {0} WHERE [{1}] = @p_id", t.DbTableName, key.Name);
89+
string selectText = string.Format("SELECT * FROM {0} WHERE [{1}] = @p_id", ObjectName, key.Name);
7290
var pId = new SqlParameter("p_id", key.Type) {Value = ConvertToDbCompatibilityType(id)};
7391

7492
return Select(connection, selectText, pId).FirstOrDefault();
@@ -79,12 +97,12 @@ public static int Delete(SqlConnection connection, object id, SqlTransaction tra
7997
var t = new T();
8098
var key = t.DbColumns.FirstOrDefault(c => c.IsKey);
8199
if (key == null)
82-
throw new Exception(string.Format("Key for table {0} isn't defined.", t.DbTableName));
100+
throw new Exception(string.Format("Key for table {0} isn't defined.", ObjectName));
83101

84102
var pId = new SqlParameter("p_id", key.Type) {Value = ConvertToDbCompatibilityType(id)};
85103

86104
return ExecuteCommand(connection,
87-
string.Format("DELETE FROM {0} WHERE [{1}] = @p_id", t.DbTableName, key.Name.ToUpper()), transaction, pId);
105+
string.Format("DELETE FROM {0} WHERE [{1}] = @p_id", ObjectName, key.Name.ToUpper()), transaction, pId);
88106
}
89107

90108
public static int ExecuteCommand(SqlConnection connection, string commandText,

‎Provider for MS SQL Server/MSSQLProvider.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,9 +22,10 @@ public void Init(WorkflowRuntime runtime)
2222
_runtime = runtime;
2323
}
2424

25-
public MSSQLProvider(string connectionString)
25+
public MSSQLProvider(string connectionString, string schema = "dbo" )
2626
{
2727
ConnectionString = connectionString;
28+
DbObject.SchemaName = schema;
2829
}
2930

3031
#region IPersistenceProvider

‎Provider for MS SQL Server/Models/WorkflowGlobalParameter.cs‎

Lines changed: 10 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -15,17 +15,19 @@ public class WorkflowGlobalParameter : DbObject<WorkflowGlobalParameter>
1515

1616
public string Value { get; set; }
1717

18-
private const string TableName = "WorkflowGlobalParameter";
18+
static WorkflowGlobalParameter()
19+
{
20+
DbTableName = "WorkflowGlobalParameter";
21+
}
1922

2023
public WorkflowGlobalParameter()
2124
{
22-
DbTableName = TableName;
2325
DbColumns.AddRange(new[]
2426
{
25-
new ColumnInfo() {Name = "Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
26-
new ColumnInfo() {Name = "Type"},
27-
new ColumnInfo() {Name = "Name"},
28-
new ColumnInfo() {Name = "Value"}
27+
new ColumnInfo {Name = "Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
28+
new ColumnInfo {Name = "Type"},
29+
new ColumnInfo {Name = "Name"},
30+
new ColumnInfo {Name = "Value"}
2931
});
3032
}
3133

@@ -69,7 +71,7 @@ public override void SetValue(string key, object value)
6971

7072
public static WorkflowGlobalParameter[] SelectByTypeAndName(SqlConnection connection, string type, string name = null)
7173
{
72-
string selectText = string.Format("SELECT * FROM [{0}] WHERE [Type] = @type", TableName);
74+
string selectText = string.Format("SELECT * FROM {0} WHERE [Type] = @type", ObjectName);
7375

7476
if (!string.IsNullOrEmpty(name))
7577
selectText = selectText + " AND [Name] = @name";
@@ -86,7 +88,7 @@ public static WorkflowGlobalParameter[] SelectByTypeAndName(SqlConnection connec
8688

8789
public static int DeleteByTypeAndName(SqlConnection connection, string type, string name = null)
8890
{
89-
string selectText = string.Format("DELETE FROM [{0}] WHERE [Type] = @type", TableName);
91+
string selectText = string.Format("DELETE FROM {0} WHERE [Type] = @type", ObjectName);
9092

9193
if (!string.IsNullOrEmpty(name))
9294
selectText = selectText + " AND [Name] = @name";

‎Provider for MS SQL Server/Models/WorkflowProcessInstance.cs‎

Lines changed: 29 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,33 @@
44
// ReSharper disable once CheckNamespace
55
namespace OptimaJet.Workflow.DbPersistence
66
{
7-
public class WorkflowProcessInstance: DbObject<WorkflowProcessInstance>
7+
public class WorkflowProcessInstance : DbObject<WorkflowProcessInstance>
88
{
9+
static WorkflowProcessInstance()
10+
{
11+
DbTableName = "WorkflowProcessInstance";
12+
}
13+
14+
public WorkflowProcessInstance()
15+
{
16+
DbColumns.AddRange(new[]
17+
{
18+
new ColumnInfo {Name = "Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
19+
new ColumnInfo {Name = "ActivityName"},
20+
new ColumnInfo {Name = "IsDeterminingParametersChanged", Type = SqlDbType.Bit},
21+
new ColumnInfo {Name = "PreviousActivity"},
22+
new ColumnInfo {Name = "PreviousActivityForDirect"},
23+
new ColumnInfo {Name = "PreviousActivityForReverse"},
24+
new ColumnInfo {Name = "PreviousState"},
25+
new ColumnInfo {Name = "PreviousStateForDirect"},
26+
new ColumnInfo {Name = "PreviousStateForReverse"},
27+
new ColumnInfo {Name = "SchemeId", Type = SqlDbType.UniqueIdentifier},
28+
new ColumnInfo {Name = "StateName"},
29+
new ColumnInfo {Name = "ParentProcessId", Type = SqlDbType.UniqueIdentifier},
30+
new ColumnInfo {Name = "RootProcessId", Type = SqlDbType.UniqueIdentifier}
31+
});
32+
}
33+
934
public string ActivityName { get; set; }
1035
public Guid Id { get; set; }
1136
public bool IsDeterminingParametersChanged { get; set; }
@@ -19,26 +44,6 @@ public class WorkflowProcessInstance: DbObject<WorkflowProcessInstance>
1944
public string StateName { get; set; }
2045
public Guid? ParentProcessId { get; set; }
2146
public Guid RootProcessId { get; set; }
22-
public WorkflowProcessInstance()
23-
{
24-
DbTableName = "WorkflowProcessInstance";
25-
DbColumns.AddRange(new[]
26-
{
27-
new ColumnInfo() {Name = "Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
28-
new ColumnInfo() {Name = "ActivityName"},
29-
new ColumnInfo() {Name = "IsDeterminingParametersChanged", Type = SqlDbType.Bit},
30-
new ColumnInfo() {Name = "PreviousActivity"},
31-
new ColumnInfo() {Name = "PreviousActivityForDirect"},
32-
new ColumnInfo() {Name = "PreviousActivityForReverse"},
33-
new ColumnInfo() {Name = "PreviousState"},
34-
new ColumnInfo() {Name = "PreviousStateForDirect"},
35-
new ColumnInfo() {Name = "PreviousStateForReverse"},
36-
new ColumnInfo() {Name = "SchemeId", Type = SqlDbType.UniqueIdentifier},
37-
new ColumnInfo() {Name = "StateName"},
38-
new ColumnInfo() {Name = "ParentProcessId", Type = SqlDbType.UniqueIdentifier},
39-
new ColumnInfo() {Name = "RootProcessId", Type = SqlDbType.UniqueIdentifier},
40-
});
41-
}
4247

4348
public override object GetValue(string key)
4449
{
@@ -80,7 +85,7 @@ public override void SetValue(string key, object value)
8085
switch (key)
8186
{
8287
case "Id":
83-
Id = (Guid)value;
88+
Id = (Guid) value;
8489
break;
8590
case "ActivityName":
8691
ActivityName = value as string;
@@ -116,11 +121,11 @@ public override void SetValue(string key, object value)
116121
ParentProcessId = value as Guid?;
117122
break;
118123
case "RootProcessId":
119-
RootProcessId = (Guid)value;
124+
RootProcessId = (Guid) value;
120125
break;
121126
default:
122127
throw new Exception(string.Format("Column {0} is not exists", key));
123128
}
124129
}
125130
}
126-
}
131+
}

‎Provider for MS SQL Server/Models/WorkflowProcessInstancePersistence.cs‎

Lines changed: 23 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,30 +1,34 @@
11
using System;
2-
using System.Data.SqlClient;
32
using System.Data;
3+
using System.Data.SqlClient;
44

55
// ReSharper disable once CheckNamespace
6+
67
namespace OptimaJet.Workflow.DbPersistence
78
{
89
public class WorkflowProcessInstancePersistence : DbObject<WorkflowProcessInstancePersistence>
910
{
10-
public Guid Id { get; set; }
11-
public Guid ProcessId { get; set; }
12-
public string ParameterName { get; set; }
13-
public string Value { get; set; }
14-
15-
private const string TableName = "WorkflowProcessInstancePersistence";
11+
static WorkflowProcessInstancePersistence()
12+
{
13+
DbTableName = "WorkflowProcessInstancePersistence";
14+
}
1615

1716
public WorkflowProcessInstancePersistence()
1817
{
19-
DbTableName = "WorkflowProcessInstancePersistence";
20-
DbColumns.AddRange(new[]{
21-
new ColumnInfo(){Name="Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
22-
new ColumnInfo(){Name="ProcessId", Type = SqlDbType.UniqueIdentifier},
23-
new ColumnInfo(){Name="ParameterName"},
24-
new ColumnInfo(){Name="Value", Type = SqlDbType.NVarChar, Size = -1}
18+
DbColumns.AddRange(new[]
19+
{
20+
new ColumnInfo {Name = "Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
21+
new ColumnInfo {Name = "ProcessId", Type = SqlDbType.UniqueIdentifier},
22+
new ColumnInfo {Name = "ParameterName"},
23+
new ColumnInfo {Name = "Value", Type = SqlDbType.NVarChar, Size = -1}
2524
});
2625
}
2726

27+
public Guid Id { get; set; }
28+
public Guid ProcessId { get; set; }
29+
public string ParameterName { get; set; }
30+
public string Value { get; set; }
31+
2832
public override object GetValue(string key)
2933
{
3034
switch (key)
@@ -47,10 +51,10 @@ public override void SetValue(string key, object value)
4751
switch (key)
4852
{
4953
case "Id":
50-
Id = (Guid)value;
54+
Id = (Guid) value;
5155
break;
5256
case "ProcessId":
53-
ProcessId = (Guid)value;
57+
ProcessId = (Guid) value;
5458
break;
5559
case "ParameterName":
5660
ParameterName = value as string;
@@ -65,17 +69,17 @@ public override void SetValue(string key, object value)
6569

6670
public static WorkflowProcessInstancePersistence[] SelectByProcessId(SqlConnection connection, Guid processId)
6771
{
68-
string selectText = string.Format("SELECT * FROM [{0}] WHERE [ProcessId] = @processid", TableName);
69-
var p = new SqlParameter("processid", SqlDbType.UniqueIdentifier) { Value = processId };
72+
var selectText = string.Format("SELECT * FROM {0} WHERE [ProcessId] = @processid", ObjectName);
73+
var p = new SqlParameter("processid", SqlDbType.UniqueIdentifier) {Value = processId};
7074
return Select(connection, selectText, p);
7175
}
7276

7377
public static int DeleteByProcessId(SqlConnection connection, Guid processId, SqlTransaction transaction = null)
7478
{
75-
var p = new SqlParameter("processid", SqlDbType.UniqueIdentifier) { Value = processId };
79+
var p = new SqlParameter("processid", SqlDbType.UniqueIdentifier) {Value = processId};
7680

7781
return ExecuteCommand(connection,
78-
string.Format("DELETE FROM [{0}] WHERE [ProcessId] = @processid", TableName), transaction, p);
82+
string.Format("DELETE FROM {0} WHERE [ProcessId] = @processid", ObjectName), transaction, p);
7983
}
8084
}
8185
}

‎Provider for MS SQL Server/Models/WorkflowProcessInstanceStatus.cs‎

Lines changed: 27 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,56 +1,60 @@
11
using System;
2-
using System.Data.SqlClient;
32
using System.Data;
3+
using System.Data.SqlClient;
44

55
// ReSharper disable once CheckNamespace
6+
67
namespace OptimaJet.Workflow.DbPersistence
78
{
89
public class WorkflowProcessInstanceStatus : DbObject<WorkflowProcessInstanceStatus>
910
{
10-
public Guid Id { get; set; }
11-
public Guid Lock { get; set; }
12-
public byte Status { get; set; }
11+
static WorkflowProcessInstanceStatus()
12+
{
13+
DbTableName = "WorkflowProcessInstanceStatus";
14+
}
1315

14-
private const string TableName = "WorkflowProcessInstanceStatus";
1516

1617
public WorkflowProcessInstanceStatus()
1718
{
18-
DbTableName = TableName;
19-
DbColumns.AddRange(new[]{
20-
new ColumnInfo(){Name="Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
21-
new ColumnInfo(){Name="Lock", Type = SqlDbType.UniqueIdentifier},
22-
new ColumnInfo(){Name="Status", Type = SqlDbType.TinyInt}
19+
DbColumns.AddRange(new[]
20+
{
21+
new ColumnInfo {Name = "Id", IsKey = true, Type = SqlDbType.UniqueIdentifier},
22+
new ColumnInfo {Name = "Lock", Type = SqlDbType.UniqueIdentifier},
23+
new ColumnInfo {Name = "Status", Type = SqlDbType.TinyInt}
2324
});
2425
}
2526

27+
public Guid Id { get; set; }
28+
public Guid Lock { get; set; }
29+
public byte Status { get; set; }
30+
2631
public override object GetValue(string key)
2732
{
2833
switch (key)
2934
{
3035
case "Id":
31-
return Id;
36+
return Id;
3237
case "Lock":
33-
return Lock;
38+
return Lock;
3439
case "Status":
3540
return Status;
3641
default:
3742
throw new Exception(string.Format("Column {0} is not exists", key));
3843
}
39-
4044
}
4145

4246
public override void SetValue(string key, object value)
4347
{
4448
switch (key)
4549
{
4650
case "Id":
47-
Id = (Guid)value;
51+
Id = (Guid) value;
4852
break;
4953
case "Lock":
50-
Lock = (Guid)value;
54+
Lock = (Guid) value;
5155
break;
5256
case "Status":
53-
Status = (byte)value;
57+
Status = (byte) value;
5458
break;
5559
default:
5660
throw new Exception(string.Format("Column {0} is not exists", key));
@@ -59,7 +63,7 @@ public override void SetValue(string key, object value)
5963

6064
public static int MassChangeStatus(SqlConnection connection, byte stateFrom, byte stateTo)
6165
{
62-
string command = string.Format("UPDATE [{0}] SET [Status] = @stateto WHERE [Status] = @statefrom", TableName);
66+
var command = string.Format("UPDATE {0} SET [Status] = @stateto WHERE [Status] = @statefrom", ObjectName);
6367
var p1 = new SqlParameter("statefrom", SqlDbType.TinyInt) {Value = stateFrom};
6468
var p2 = new SqlParameter("stateto", SqlDbType.TinyInt) {Value = stateTo};
6569

@@ -68,13 +72,13 @@ public static int MassChangeStatus(SqlConnection connection, byte stateFrom, byt
6872

6973
public static int ChangeStatus(SqlConnection connection, WorkflowProcessInstanceStatus status, Guid oldLock)
7074
{
71-
string command = string.Format("UPDATE [{0}] SET [Status] = @newstatus, [Lock] = @newlock WHERE [Id] = @id AND [Lock] = @oldlock", TableName);
72-
var p1 = new SqlParameter("newstatus", SqlDbType.TinyInt) { Value = status.Status };
73-
var p2 = new SqlParameter("newlock", SqlDbType.UniqueIdentifier) { Value = status.Lock };
74-
var p3 = new SqlParameter("id", SqlDbType.UniqueIdentifier) { Value = status.Id };
75-
var p4 = new SqlParameter("oldlock", SqlDbType.UniqueIdentifier) { Value = oldLock };
75+
var command = string.Format("UPDATE {0} SET [Status] = @newstatus, [Lock] = @newlock WHERE [Id] = @id AND [Lock] = @oldlock", ObjectName);
76+
var p1 = new SqlParameter("newstatus", SqlDbType.TinyInt) {Value = status.Status};
77+
var p2 = new SqlParameter("newlock", SqlDbType.UniqueIdentifier) {Value = status.Lock};
78+
var p3 = new SqlParameter("id", SqlDbType.UniqueIdentifier) {Value = status.Id};
79+
var p4 = new SqlParameter("oldlock", SqlDbType.UniqueIdentifier) {Value = oldLock};
7680

7781
return ExecuteCommand(connection, command, p1, p2, p3, p4);
7882
}
7983
}
80-
}
84+
}

0 commit comments

Comments
 (0)