public void ExecuteAction(SqlAction action, CancellationTokenSource cts)
{
var sql = action.GetSql();
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
using (var cn = CreateAsyncConnection())
using (var cmd = cn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("USE {0}; {1}", action.DatabaseName, sql);
cmd.CommandTimeout = action.CommandTimeout;
var asyncResult = cmd.BeginExecuteNonQuery();
while (!asyncResult.AsyncWaitHandle.WaitOne(500))
{
if (cts.IsCancellationRequested)
{
cmd.Cancel();
return;
}
}
cmd.EndExecuteNonQuery(asyncResult);
}
}
public int AddRili(Rili rili)
{
int result = 0;
try
{
string sql = "INSERT INTO t_rili(Yangli,Nongli,Zhigan,Xingzuo,Xingqi,Yi,Ji)values(@Yangli,@Nongli,@Zhigan,@Xingzuo,@Xingqi,@Yi,@Ji)";
SQLiteParameter[] parameters = new SQLiteParameter[] {
new SQLiteParameter("@Yangli", rili.Yangli),
new SQLiteParameter("@Nongli", rili.Nongli),
new SQLiteParameter("@Zhigan", rili.Zhigan),
new SQLiteParameter("@Xingzuo", rili.Xingzuo),
new SQLiteParameter("@Xingqi", rili.Xingqi),
new SQLiteParameter("@Yi", rili.Yi),
new SQLiteParameter("@Ji", rili.Ji)
};
SqlAction action = new SqlAction();
result = action.IntQuery(sql, parameters);
}
catch (Exception ex)
{
Log.Error("插入日历数据出错：" + ex.ToString());
}
return(result);
}
private SqlAction InsertContacts(decimal rank)
{
string sql = $@"
UPDATE
[dtl_PatientContacts]
SET
[Ptn_Pk]=@ptnpk,
[VisitId]=@visitipk,
[LocationID]=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),
[UserID]='{Patient.UserId}',
[NextofKinName]='{Patient.Kin}',
[NextofKinRelationship]='{Patient.Kinrelationother}',
[NextofKinTelNo]='{Patient.Kinphone}',
[UpdateDate]=GETDATE()
WHERE
Ptn_pk=@ptnpk AND VisitId=@visitipk
IF @@ROWCOUNT=0
INSERT INTO
dtl_PatientContacts(
ptn_pk, VisitId, LocationID, UserID, CreateDate, NextofKinName, NextofKinRelationship, NextofKinTelNo)
VALUES(@ptnpk,@visitipk,
(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0), {Patient.UserId}, GETDATE(), '{Patient.Kin}', '{Patient.Kinrelationother}', '{Patient.Kinphone}');";
var action = new SqlAction(rank, sql);
return(action);
}
private SqlAction InsertVisit(decimal rank)
{
//TODO: ALTER TABLE [dbo].[ord_Visit] ADD [IQMobileVisitType] [int] NULL
string sql = $@"
UPDATE
[ord_Visit]
SET
[Ptn_Pk]=@ptnpk,
[LocationID]=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),
[VisitDate]='{Patient.EnrollmentdateString}',
[VisitType]= 12,
[UserID]='{Patient.UserId}',
[UpdateDate]=GETDATE(),
[IQMobileVisitType]=12
WHERE
Ptn_pk=@ptnpk AND LocationId=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0) AND IQMobileVisitType=12
IF @@ROWCOUNT=0
INSERT INTO
ord_Visit(
Ptn_Pk, LocationID, VisitDate, VisitType, UserID, CreateDate,IQMobileVisitType)
VALUES(
@ptnpk,(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0), '{Patient.EnrollmentdateString}', 12, '{Patient.UserId}', GETDATE(),12);
SET @visitipk=(SELECT TOP 1 [Visit_Id] FROM [ord_Visit] WHERE Ptn_Pk=@ptnpk AND IQMobileVisitType=12 ORDER BY CreateDate desc);";
var action = new SqlAction(rank, sql);
return(action);
}
private SqlAction InsertPatient(decimal rank)
{
//TODO: ALTER TABLE [dbo].[mst_Patient] ADD [SyncId] [uniqueidentifier] NULL
string sql = $@"
DECLARE @ptnpk int
DECLARE @visitipk int
UPDATE
[mst_Patient]
SET
[Status]='0',
[FirstName]=encryptbykey(key_guid('Key_CTC'), '{Patient.Firstname}'),
[MiddleName]=encryptbykey(key_guid('Key_CTC'), '{Patient.Middlename}'),
[LastName]=encryptbykey(key_guid('Key_CTC'), '{Patient.Lastname}'),
[LocationID]= (select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),
[RegistrationDate]= '{Patient.EnrollmentdateString}',
[Sex]= '{Patient.Sex}',
[DOB]= '{Patient.DobString}',
[DobPrecision]= '{Patient.GetDobEstimated()}',
[CountryId]=(select TOP 1 CountryID from mst_Facility WHERE DeleteFlag = 0),
[PosId]=(select TOP 1 PosID from mst_Facility WHERE DeleteFlag = 0),
[SatelliteId]=(select TOP 1 SatelliteID from mst_Facility WHERE DeleteFlag = 0),
[UserID]='{Patient.UserId}',
[UpdateDate]=GETDATE(),
[MaritalStatus]='{Patient.Kinrelation}',
[HTSOccupation]='{Patient.Kinphone}',
[HTSPhysicalAddress]='{Patient.Kin}',
[Phone]= encryptbykey(key_guid('Key_CTC'), '{Patient.Contactphone}'),
[Client_Code]= '{Patient.Clientcode}'
WHERE
SyncId='{Patient.UuId}'
IF @@ROWCOUNT=0
INSERT INTO
mst_Patient(
Status, FirstName, MiddleName, LastName,
LocationID, RegistrationDate, Sex, DOB, DobPrecision,
CountryId, PosId, SatelliteId, UserID, CreateDate,
Phone,Client_Code,SyncId,MaritalStatus, HTSOccupation, HTSPhysicalAddress)
VALUES(
'0', encryptbykey(key_guid('Key_CTC'), '{Patient.Firstname}'), encryptbykey(key_guid('Key_CTC'), '{Patient.Middlename}'), encryptbykey(key_guid('Key_CTC'), '{Patient.Lastname}'),
(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0), '{Patient.EnrollmentdateString}', '{Patient.Sex}', '{Patient.DobString}', '{Patient.GetDobEstimated()}',
(select TOP 1 CountryID from mst_Facility WHERE DeleteFlag = 0),
(select TOP 1 PosID from mst_Facility WHERE DeleteFlag = 0),
(select TOP 1 SatelliteID from mst_Facility WHERE DeleteFlag = 0), '{Patient.UserId}', GETDATE(),
encryptbykey(key_guid('Key_CTC'), '{Patient.Contactphone}'),'{Patient.Clientcode}','{Patient.UuId}','{Patient.Kinrelation}','{Patient.Kinphone}','{Patient.Kin}');
SET @ptnpk=(SELECT Ptn_Pk FROM mst_Patient WHERE SyncId ='{Patient.UuId}');";
var action = new SqlAction(rank, sql);
return(action);
}
private SqlAction InsertRegistration(decimal rank)
{
string sql = $@"
if exists(
select name from sysobjects where name = 'DTL_FBCUSTOMFIELD_Patient_Registration')
begin
UPDATE
[DTL_FBCUSTOMFIELD_Patient_Registration]
SET
[Ptn_Pk]=@ptnpk,
[Visit_Pk]=@visitipk,
[LocationID]=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),
[UserID]='{Patient.UserId}',
[UpdateDate]=GETDATE()
WHERE
Ptn_pk=@ptnpk AND Visit_Pk=@visitipk
IF @@ROWCOUNT=0
Insert into
[DTL_FBCUSTOMFIELD_Patient_Registration](
Ptn_pk,Visit_Pk,LocationId,UserID,CreateDate)
Values(
@ptnpk,@visitipk,(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),{Patient.UserId}, GetDate())
end ;";
var action = new SqlAction(rank, sql);
return(action);
}
public Database()
: base(null, Enums.ObjectType.Database)
{
AllObjects = new SearchSchemaBase();
_changesOptions = new List<DatabaseChangeStatus>();
Dependencies = new Dependencies();
TablesTypes = new SchemaList<TableType, Database>(this, AllObjects);
UserTypes = new SchemaList<UserDataType, Database>(this, AllObjects);
XmlSchemas = new SchemaList<XMLSchema, Database>(this, AllObjects);
Schemas = new SchemaList<Schema, Database>(this, AllObjects);
Procedures = new SchemaList<StoreProcedure, Database>(this, AllObjects);
CLRProcedures = new SchemaList<CLRStoreProcedure, Database>(this, AllObjects);
CLRFunctions = new SchemaList<CLRFunction, Database>(this, AllObjects);
FileGroups = new SchemaList<FileGroup, Database>(this);
Rules = new SchemaList<Rule, Database>(this, AllObjects);
DDLTriggers = new SchemaList<Trigger, Database>(this, AllObjects);
Synonyms = new SchemaList<Synonym, Database>(this, AllObjects);
Assemblies = new SchemaList<Assembly, Database>(this, AllObjects);
Views = new SchemaList<View, Database>(this, AllObjects);
Users = new SchemaList<User, Database>(this, AllObjects);
FullText = new SchemaList<FullText, Database>(this, AllObjects);
Functions = new SchemaList<Function, Database>(this, AllObjects);
PartitionFunctions = new SchemaList<PartitionFunction, Database>(this, AllObjects);
PartitionSchemes = new SchemaList<PartitionScheme, Database>(this, AllObjects);
Roles = new SchemaList<Role, Database>(this);
Tables = new SchemaList<Table, Database>(this, AllObjects);
Defaults = new SchemaList<Default, Database>(this, AllObjects);
ActionMessage = new SqlAction(this);
}
public FResume()
{
InitializeComponent();
cbAction.DataSource = SqlAction.List();
cbWhat.DataSource = TypeObject.List();
EnableButtons();
}
public static string ObjectToSql <T>(SqlAction action = SqlAction.SELECT, T conition = null, T newData = null) where T : class
{
if (conition is null && newData is null)
{
return(null);
}
switch (action)
{
case SqlAction.SELECT:
return(SelectSql(conition));
case SqlAction.INSERT:
return(InsertSql(newData));
case SqlAction.DELETE:
return(DeleteSql(conition));
case SqlAction.UPDATE:
return(UpdateSql(conition, newData));
default:
return(null);
}
return(null);
}
public int DeleteTimedEvent(int id)
{
int result = 0;
try
{
string sql = string.Empty;
SQLiteParameter[] parameters = null;
if (-1 == id)
{
sql = "delete from t_dingshi where Frequency='仅一次' and Time < @Time";
parameters = new SQLiteParameter[] {
new SQLiteParameter("@Time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
};
}
else
{
sql = "delete from t_dingshi where Id = @Id";
parameters = new SQLiteParameter[] {
new SQLiteParameter("@Id", id)
};
}
SqlAction action = new SqlAction();
result = action.IntQuery(sql, parameters);
}
catch
{
}
return(result);
}
public Database() : base(null, Enums.ObjectType.Database)
{
AllObjects = new SearchSchemaBase();
_changesOptions = new List <DatabaseChangeStatus>();
Dependencies = new Dependencies();
TablesTypes = new SchemaList <TableType, Database>(this, AllObjects);
UserTypes = new SchemaList <UserDataType, Database>(this, AllObjects);
XmlSchemas = new SchemaList <XMLSchema, Database>(this, AllObjects);
Schemas = new SchemaList <Schema, Database>(this, AllObjects);
Procedures = new SchemaList <StoreProcedure, Database>(this, AllObjects);
CLRProcedures = new SchemaList <CLRStoreProcedure, Database>(this, AllObjects);
CLRFunctions = new SchemaList <CLRFunction, Database>(this, AllObjects);
FileGroups = new SchemaList <FileGroup, Database>(this);
Rules = new SchemaList <Rule, Database>(this, AllObjects);
DDLTriggers = new SchemaList <Trigger, Database>(this, AllObjects);
Synonyms = new SchemaList <Synonym, Database>(this, AllObjects);
Assemblies = new SchemaList <Assembly, Database>(this, AllObjects);
Views = new SchemaList <View, Database>(this, AllObjects);
Users = new SchemaList <User, Database>(this, AllObjects);
FullText = new SchemaList <FullText, Database>(this, AllObjects);
Functions = new SchemaList <Function, Database>(this, AllObjects);
PartitionFunctions = new SchemaList <PartitionFunction, Database>(this, AllObjects);
PartitionSchemes = new SchemaList <PartitionScheme, Database>(this, AllObjects);
Roles = new SchemaList <Role, Database>(this);
Tables = new SchemaList <Table, Database>(this, AllObjects);
Defaults = new SchemaList <Default, Database>(this, AllObjects);
ActionMessage = new SqlAction(this);
}
public void Analyze(RegexFounding e, int scriptId, string text)
{
blocs.Clear();
MatchCollection cll = e.Expression.Matches(text);
if (cll != null && cll.Count > 0)
{
foreach (Match m in cll)
{
string db = m.Groups["database"]?.Value;
string sch = m.Groups["schema"]?.Value;
if (string.IsNullOrWhiteSpace(sch) && !string.IsNullOrWhiteSpace(db))
{
sch = db;
db = null;
}
string name = m.Groups["name"]?.Value;
string col = string.Empty;
if (SqlAction.IsForColumn(e.Action))
{
col = m.Groups["col"]?.Value;
}
else if (e.ApplyOn == TypeObject.Index && SqlAction.IsForIndex(e.Action))
{
col = m.Groups["col"]?.Value;
}
int?clientCode = null;
if (int.TryParse(m.Groups["codeClient"].Value, out int cc))
{
clientCode = cc;
}
var res = new Bloc()
{
ScriptId = scriptId,
SqlActionId = e.Action,
TypeObjectId = e.ApplyOn,
BlocIndex = m.Index,
BlocLength = m.Length,
BlocDatabase = RegexFounding.Filtre(db),
BlocSchema = RegexFounding.Filtre(sch),
BlocName = RegexFounding.Filtre(name),
BlocColumn = RegexFounding.Filtre(col),
ClientCodeId = clientCode
};
if (SqlAction.IsForColumn(e.Action))
{
res.BlocColumn = m.Groups["col"]?.Value;
}
blocs.Add(res);
}
}
}
/// <summary>
/// Plaies the action.
/// </summary>
/// <returns><c>true</c>, if action was played, <c>false</c> otherwise.</returns>
/// <param name="sqlAction">Sql action.</param>
public virtual bool PlayAction(SqlAction sqlAction)
{
if (!string.IsNullOrEmpty(sqlAction.Motion))
{
Self.Play(sqlAction.Motion, sqlAction.MotionTransition, false);
}
return true;
}
/// <summary>
///
/// </summary>
/// <param name="columns">Columns to choose</param>
/// <returns></returns>
public LinqSql <TModel> Select(string[] columns)
{
_sqlAction = SqlAction.Select;
foreach (var col in columns)
{
_tableCols.Add(col, col);
}
return(this);
}
public ScriptObject(Object obj, SqlAction act)
{
myobject = obj;
myAction = act;
if (myAction.SqlActionId != SqlAction.Delete && myAction.SqlActionId != SqlAction.Alter && myAction.SqlActionId != SqlAction.Create)
{
throw new ArgumentException("Action is only in CUD values");
}
}
/// <summary>
/// Plaies the state action.
/// </summary>
/// <returns><c>true</c>, if state action was played, <c>false</c> otherwise.</returns>
/// <param name="sqlAction">Sql action.</param>
public virtual bool PlayStateAction(SqlAction sqlAction)
{
if (sqlAction.AnimationID >= 0)
{
ActionStruct actionMeta = GetActionTypeID (sqlAction.AnimationID);
if (!string.IsNullOrEmpty(actionMeta.Name))
m_Entity.Play (actionMeta.Name, actionMeta.Transition, false);
}
return true;
}
private IList <TableIdentifier> ProcessFromClause(FromClause fromClause, SqlAction action)
{
var tableReferences = fromClause?.TableReferences;
if (tableReferences != null)
{
return(ProcessTableReferences(tableReferences, action));
}
return(null);
}
public ActionEditor_Sql PopulateForm(SqlAction model)
{
TxtBoxConnection.Text = model.ConectionString;
RichStatement.Text = model.Statement;
GridParameters.DataSource = model.Args;
TxtBoxName.Text = model.Name;
NumBoxPosition.Value = model.Position;
_model = model;
return(this);
}
public SqlAction InsertVisit(decimal rank)
{
//TODO: Remove hard codede ModuleID,VisitType,LocationID ,Signature values
//TODO: HTCTimeOut for Android
string sql = string.Empty;
try
{
sql = $@"
DECLARE @ptnpk int
DECLARE @visitipk int
SET @ptnpk=(SELECT Ptn_Pk FROM mst_Patient WHERE SyncId ='{Patient.UuId}');
UPDATE
[ord_Visit]
SET
[Ptn_Pk]=@ptnpk,
[LocationID]=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),
[VisitDate]='{Patient.EnrollmentdateString}',
[VisitType]= {VisitType.VisitTypeID},
[DataQuality]='1',
[UserID]='{Patient.UserId}',
[Signature]='0',
[UpdateDate]=GETDATE(),
[IQMobileVisitType]={VisitType.VisitTypeID}
WHERE
Ptn_pk=@ptnpk AND LocationId=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0) AND IQMobileVisitType={VisitType.VisitTypeID}
IF @@ROWCOUNT=0
INSERT INTO
ord_Visit(Ptn_Pk, LocationID, VisitDate, VisitType,DataQuality,UserID,Signature,CreateDate,IQMobileVisitType)
VALUES(
@ptnpk,(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0), '{Patient.EnrollmentdateString}', {VisitType.VisitTypeID},'1', '{Patient.UserId}','0', GETDATE(),{VisitType.VisitTypeID});
SET @visitipk=(SELECT TOP 1 [Visit_Id] FROM [ord_Visit] WHERE Ptn_Pk=@ptnpk AND IQMobileVisitType={VisitType.VisitTypeID} ORDER BY CreateDate desc);
";
}
catch (Exception ex)
{
Log.Debug("Error generating | Encounter Visit");
Log.Debug(ex);
Log.Debug(sql);
}
var action = new SqlAction(rank, sql);
return(action);
}
private void ProcessWhereClause(WhereClause whereClause, SqlAction action)
{
var searchCondition = whereClause?.SearchCondition;
ProcessBooleanExpression(searchCondition);
/*if (searchCondition is InPredicate)
* {
* var subquery = ((InPredicate) searchCondition).Subquery;
* var querySpecification = subquery.QueryExpression as QuerySpecification;
* ProcessFromClause(querySpecification?.FromClause, action);
* }*/
}
/// <summary>
/// Generate a SQL Server stored procedure for the specified SQL Action (Select, Insert, Update, Delete)
/// </summary>
/// <param name="sqlAction"></param>
/// <param name="objectSchema"></param>
/// <param name="objectName"></param>
/// <param name="getProcedureTemplate"></param>
private void GenerateSqlServerProcedure(SqlAction sqlAction, string objectSchema, string objectName, GetProcedureTemplate getProcedureTemplate)
{
string procedureName = string.Format("{0}{1}Auto", objectName, sqlAction.ToString());
List <sql.DBColumn> columns = new sql.DataAccess(_connectionString).GetColumns(
objectSchema, objectName, _columnLanguageTypeValues, _columnDataProviderTypeValues);
if (!HasPrimaryKey(columns))
{
throw new NoPrimaryKeyException(string.Format("{0}.{1} has no primary key. No Sql Server {2} procedure created.", objectSchema, objectName, sqlAction.ToString()));
}
string procText = getProcedureTemplate(objectSchema, objectName, procedureName, columns);
File.WriteAllText(string.Format("{0}\\{1}.{2}.sql", _outputFolder, objectSchema, procedureName), procText);
}
public SqlAction InsertObsData(decimal rank)
{
var singleBuilder = new StringBuilder();
foreach (var o in Encounter.Observations)
{
if (IsSingleObs(o.MConceptId))
{
singleBuilder.AppendLine(GetSingleStatement(o));
}
}
var action = new SqlAction(rank, singleBuilder.ToString());
return(action);
}
public string GetDefaultSearch()
{
string result = string.Empty;
try
{
string sqlString = @"select Value from t_config where Name = 'DefaultSearch'";
SqlAction action = new SqlAction();
result = action.StringQuery(sqlString, null);
}
catch
{
}
return(result);
}
public DataTable GetConfigWebsite()
{
DataTable data = new DataTable();
try
{
string sqlString = @"select * from t_website_config order by WebOrder";
SqlAction action = new SqlAction();
data = action.DataTableQuery(sqlString, null);
}
catch
{
}
return(data);
}
public int ClearConfigWebsite()
{
int result = 0;
try
{
string sqlString = @"delete from t_website_config";
SqlAction action = new SqlAction();
result = action.IntQuery(sqlString, null);
}
catch
{
}
return(result);
}
public DataTable GetRiliDataByMonth(int year, int month)
{
DataTable data = new DataTable();
try
{
string sqlString = @"select * from t_rili where Yangli like '" + string.Format("{0}年{1}月", year, month) + "%' order by Yangli";
SqlAction action = new SqlAction();
data = action.DataTableQuery(sqlString, null);
}
catch
{
}
return(data);
}
private SqlAction InsertEnrollment(decimal rank)
{
string sql = $@"
UPDATE
[ord_Visit]
SET
[Ptn_Pk]=@ptnpk,
[LocationID]=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),
[VisitDate]='{Patient.EnrollmentdateString}',
[VisitType]= {VisitType.VisitTypeID},
[DataQuality]=0,
[UserID]='{Patient.UserId}',
[UpdateDate]=GETDATE(),
[IQMobileVisitType]={VisitType.VisitTypeID}
WHERE
Ptn_pk=@ptnpk AND LocationId=(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0) AND IQMobileVisitType={VisitType.VisitTypeID}
IF @@ROWCOUNT=0
Insert into
ord_visit(
Ptn_Pk,LocationID,VisitDate,VisitType,DataQuality,DeleteFlag,UserID,CreateDate,IQMobileVisitType)
values (
@ptnpk,(select TOP 1 FacilityID from mst_Facility WHERE DeleteFlag = 0),'{Patient.EnrollmentdateString}',{VisitType.VisitTypeID},0,0,{Patient.UserId}, Getdate(),{VisitType.VisitTypeID});
UPDATE
[lnk_patientprogramstart]
SET
[Ptn_Pk]=@ptnpk,
[ModuleID]={Module.ModuleID},
[StartDate]='{Patient.EnrollmentdateString}',
[UserID]='{Patient.UserId}',
[UpdateDate]=GETDATE()
WHERE
Ptn_pk=@ptnpk AND ModuleID={Module.ModuleID}
IF @@ROWCOUNT=0
Insert into
lnk_patientprogramstart(
Ptn_Pk,ModuleID,StartDate,UserID,CreateDate)
values (
@ptnpk, {Module.ModuleID},'{Patient.EnrollmentdateString}',{Patient.UserId}, Getdate());" ;
var action = new SqlAction(rank, sql);
return(action);
}
public static SqlAlterTable Alter(Table table, SqlAction action)
{
ArgumentValidator.EnsureArgumentNotNull(table, "table");
ArgumentValidator.EnsureArgumentNotNull(action, "action");
if (action is SqlSetDefault && ((SqlSetDefault)action).Column == null ||
action is SqlDropDefault && ((SqlDropDefault)action).Column == null)
{
throw new ArgumentException(Strings.ExInvalidActionType, "action");
}
if (action is SqlAddColumn && ((SqlAddColumn)action).Column.DataTable != null &&
((SqlAddColumn)action).Column.DataTable != table ||
action is SqlSetDefault && ((SqlSetDefault)action).Column.DataTable != null &&
((SqlSetDefault)action).Column.DataTable != table ||
action is SqlDropDefault && ((SqlDropDefault)action).Column.DataTable != null &&
((SqlDropDefault)action).Column.DataTable != table ||
action is SqlDropColumn && ((SqlDropColumn)action).Column.DataTable != null &&
((SqlDropColumn)action).Column.DataTable != table ||
action is SqlAlterIdentityInfo && ((SqlAlterIdentityInfo)action).Column.DataTable != null &&
((SqlAlterIdentityInfo)action).Column.DataTable != table)
{
throw new ArgumentException(Strings.ExColumnBelongsToOtherTable, "action");
}
else if (action is SqlAddConstraint)
{
var constraint = ((SqlAddConstraint)action).Constraint as TableConstraint;
if (constraint == null)
{
throw new ArgumentException(Strings.ExInvalidConstraintType, "action");
}
else if (constraint.Table != null && constraint.Table != table)
{
throw new ArgumentException(Strings.ExConstraintBelongsToOtherTable, "action");
}
}
else if (action is SqlDropConstraint)
{
var constraint = ((SqlDropConstraint)action).Constraint as TableConstraint;
if (constraint == null)
{
throw new ArgumentException(Strings.ExInvalidConstraintType, "action");
}
else if (constraint.Table != null && constraint.Table != table)
{
throw new ArgumentException(Strings.ExConstraintBelongsToOtherTable, "action");
}
}
return(new SqlAlterTable(table, action));
}
public int ClearCityData()
{
int result = 0;
try
{
string sql = "delete from t_city";
SqlAction action = new SqlAction();
result = action.IntQuery(sql, null);
}
catch (Exception ex)
{
log.WriteLog(ex.ToString());
}
return(result);
}
public DataTable GetTimedEvents()
{
DataTable data = new DataTable();
try
{
string sqlString = @"select * from t_dingshi";
SqlAction action = new SqlAction();
data = action.DataTableQuery(sqlString, null);
}
catch
{
}
return(data);
}
public DataTable GetConfig()
{
DataTable data = new DataTable();
try
{
string sqlString = @"select * from t_config";
SqlAction action = new SqlAction();
data = action.DataTableQuery(sqlString, null);
}
catch (Exception ex)
{
log.WriteLog(ex.ToString());
}
return(data);
}
public Form1()
{
InitializeComponent();
fullFileName = Path.Combine(Application.StartupPath, "Jeux2Test.Json");
datas = RegexFoundingUnitTest.Load(fullFileName).ToList();
isChange = false;
lblNo.Visible = false;
cbAction.DataSource = SqlAction.List().Where(x => x.SqlActionId > 1).ToList();
cbWhat.DataSource = TypeObject.List().Where(x => x.TypeObjectId >= 0).ToList();
lblInfos.Text = string.Empty;
Choix_SelectedIndexChanged(null, null);
GereBoutton();
}
private SqlAction UpdateReference(decimal rank)
{
string sql = $@"
update
mst_patient set IQNumber = 'IQ-'+convert(varchar,Replicate('0',20-len(x.[ptnIdentifier]))) +x.[ptnIdentifier]
from (
select
UPPER(substring(convert(varchar(50),decryptbykey(firstname)),1,1))+UPPER(substring(convert(varchar(50),decryptbykey(lastname)),1,1))+convert(varchar,dob,112)+convert(varchar,locationid)+Convert(varchar(10),ptn_pk) [ptnIdentifier]
from
mst_patient where ptn_pk = @ptnpk)x
where ptn_pk= @ptnpk;" ;
var action = new SqlAction(rank, sql);
return(action);
}
/// <summary>
/// Generate a SQL Server stored procedure for the specified SQL Action (Select, Insert, Update, Delete)
/// </summary>
/// <param name="sqlAction"></param>
/// <param name="objectSchema"></param>
/// <param name="objectName"></param>
/// <param name="getProcedureTemplate"></param>
private void GenerateSqlServerProcedure(SqlAction sqlAction, string objectSchema, string objectName, GetProcedureTemplate getProcedureTemplate)
{
string procedureName = string.Format("{0}{1}Auto", objectName, sqlAction.ToString());
List<sql.DBColumn> columns = new sql.DataAccess(_connectionString).GetColumns(
objectSchema, objectName, _columnLanguageTypeValues, _columnDataProviderTypeValues);
if (!HasPrimaryKey(columns)) throw new NoPrimaryKeyException(string.Format("{0}.{1} has no primary key. No Sql Server {2} procedure created.", objectSchema, objectName, sqlAction.ToString()));
string procText = getProcedureTemplate(objectSchema, objectName, procedureName, columns);
File.WriteAllText(string.Format("{0}\\{1}.{2}.sql", _outputFolder, objectSchema, procedureName), procText);
}
