public override void Down()
{
long userId = SqlExecute.ExecuteScalar <long>("SELECT TOP 1 [Id] FROM [WebUsers] WHERE [LoginName] LIKE 'admin';");
Sql(string.Format("DELETE FROM [WebUsers] WHERE [Id] = '{0}';", userId));
Sql(string.Format("DELETE FROM [Users] WHERE [Id] = '{0}';", userId));
}
public void SqlExecuteFilesExceptionTest()
{
// Create a temp file and write some content to it which will error
FileInfo f = new FileInfo(System.IO.Path.GetTempFileName());
File.WriteAllLines(f.FullName, new[] { "SELECT CONVERT(CHdAR(10), GETDATE(), 103)" });
// Initialise Instance
var target = new SqlExecute {
Action = SqlExecuteAction.Execute
};
// Declare additional parameters
var parameters = new Dictionary <string, object>
{
{ "Files", new[] { f.FullName } },
{ "ConnectionString", "Data Source=.;Initial Catalog=;Integrated Security=True" },
{ "UseTransaction", true },
{ "CommandTimeout", 30 },
};
// Create a WorkflowInvoker and add the IBuildDetail Extension
WorkflowInvoker invoker = new WorkflowInvoker(target);
invoker.Invoke(parameters);
}
public override void Up()
{
SqlExecute.ExecuteNonQuery("DELETE FROM dbo.Roles");
SqlExecute.ExecuteNonQuery("INSERT INTO [Roles](Id, Description) VALUES(1, 'Administrator')");
SqlExecute.ExecuteNonQuery("INSERT INTO [Roles](Id, Description) VALUES(2, 'User')");
}
public override void Up()
{
int parentDirId = SqlExecute.ExecuteScalar <int>("SELECT TOP 1 [Id] FROM [ConfigDirectories] WHERE [Name] = @name;", new[]
{
new SqlParameter("@name", MarketDataConfiguration.MarketDataDirectoryName)
});
ConfigValueInsert defaultDividendYield = new ConfigValueInsert
{
Name = MarketDataConfiguration.DefaultDividendYieldValueName,
ParentDirectory_Id = parentDirId,
};
defaultDividendYield.SetValue(.0);
SqlExecute.InsertAndGetInt32Identity("ConfigValues", defaultDividendYield);
ConfigValueInsert daysOfDefaultExpiry = new ConfigValueInsert
{
Name = MarketDataConfiguration.DaysOfDefaultExpiryValueName,
ParentDirectory_Id = parentDirId,
};
daysOfDefaultExpiry.SetValue(45.0);
SqlExecute.InsertAndGetInt32Identity("ConfigValues", daysOfDefaultExpiry);
}
void markImported(string pId, string pTab)
{
string tab_ = null;
switch (pTab)
{
case TableINVOICE.TABLE:
tab_ = TableINVOICE.TABLE_FULL_NAME;
break;
case TableORFICHE.TABLE:
tab_ = TableORFICHE.TABLE_FULL_NAME;
break;
case TableKSLINES.TABLE:
tab_ = TableKSLINES.TABLE_FULL_NAME;
break;
}
if (tab_ == null)
{
return;
}
string sql_ = "UPDATE " + tab_ + " SET RECVERS = @P1, READONLY = 1 WHERE LOGICALREF = @P2";
SqlExecute.executeNonQuery(environment, sql_, new object[] { short.MaxValue, pId });
}
protected virtual void update()
{
if (needUpdate())
{
SqlExecute.executeBatch(SqlExecute.translate(getReplaceList(), environment.prepareSqlText(sqlText)), environment);
setVersion();
}
}
public static IDictionary getCardToGLRef(IEnvironment pEnv, int card, ConstCardGlRelationTrcode trcode, ConstCardGlRelationType type)
{
SqliteParameter[] par = new SqliteParameter[] {
SqlPF.get(ConstSqlParametersName.parCon1, SqlDbType.SmallInt, trcode),
SqlPF.get(ConstSqlParametersName.parCon2, SqlDbType.SmallInt, type),
SqlPF.get(ConstSqlParametersName.parCon3, SqlDbType.Int, card)
};
return(SqlExecute.executeGetLine(pEnv, Resource.SqlText.SqlTextCardGlAccRef, par));
}
public static string GreateOrderNmber(string sign)
{
SqlExecute sqlExecute = new SqlExecute();
string orderNmber = string.Empty;
string dateNmber = DateTime.Now.ToString("yyyyMMdd");
string selectCurrentOrderNmb = @"select count(1) rows from instockorder t where t.ordercode like '%" + dateNmber + "%' ";
int rowsNmb = 1 + sqlExecute.GetRows(selectCurrentOrderNmb);
return(orderNmber = sign + dateNmber + rowsNmb.ToString().PadLeft(3, '0'));
}
protected virtual bool needUpdate()
{
Object res = SqlExecute.executeScalar(environment, SqlExecute.translate(getReplaceList(), environment.prepareSqlText(SQLTextCollection.NeedUpdate)));
if (ToolCell.isNull(res, string.Empty).ToString() == "1")
{
return(true);
}
return(false);
}
public override void Up()
{
SqlExecute.ExecuteNonQuery("DELETE FROM [dbo].[__MigrationHistory] WHERE [MigrationId] = N'201411270918013_AddCacheStatus'");
CacheStatus securitiesInfoCacheEntry = new CacheStatus(CacheEntity.SecurityInformation);
CacheStatus optionBasicInfoCacheEntry = new CacheStatus(CacheEntity.OptionBasicInformation);
SqlExecute.InsertAndGetInt32Identity("CacheStatuses", securitiesInfoCacheEntry);
SqlExecute.InsertAndGetInt32Identity("CacheStatuses", optionBasicInfoCacheEntry);
}
/// <summary>
/// SetBackParamValue
/// </summary>
/// <param name="sqlExecute"></param>
/// <param name="dbparams"></param>
private void SetBackParamValue(SqlExecute sqlExecute, IDbDataParameter[] dbparams)
{
for (var i = 0; i <= dbparams.Length - 1; i++)
{
if (dbparams[i].Direction != ParameterDirection.Input)
{
sqlExecute.SetValueAt(i, dbparams[i].Value);
}
}
}
void makeDbReadonly()
{
string sql_ =
@"
UPDATE LG_$FIRM$_$PERIOD$_INVOICE SET READONLY = 1 WHERE READONLY <> 1 ;
UPDATE LG_$FIRM$_$PERIOD$_ORFICHE SET READONLY = 1 WHERE READONLY <> 1 ;
UPDATE LG_$FIRM$_$PERIOD$_KSLINES SET READONLY = 1 WHERE READONLY <> 1 ;
";
SqlExecute.executeNonQuery(environment, sql_);
}
void deleteTables(string[] arrTables, string[] arrDesc)
{
for (int i = 0; i < arrTables.Length; ++i)
{
ToolMobile.log("Delete Data:" + arrDesc[i]);
//
log.set(MessageCollection.T_MSG_DATA_DELETING, new object[] { arrDesc[i] });
//
SqlExecute.executeNonQuery(environment, string.Format("DELETE FROM {0}", arrTables[i]));
}
}
public static DataRow getItemData(IEnvironment pEnv, object lref)
{
DataTable tab_ = SqlExecute.execute(pEnv, "SELECT * FROM LG_$FIRM$_ITEMS WHERE LOGICALREF = @P1", new object[] { lref });
tab_.TableName = TableITEMS.TABLE;
return(ToolRow.getFirstRealRow(tab_));
//IPagedSource ps = new PagedSourceMaterial(pEnv);
//ps.getBuilder().addParameterValue(TableITEMS.LOGICALREF, lref);
//DataTable table = ps.getAll();
//return ToolRow.getFirstRealRow(table);
}
public void rollbackBatch()
{
if (transaction != null)
{
var x = transaction;
transaction = null;
x.Rollback();
x.Dispose();
//
SqlExecute.closeConn();
}
}
void deleteDocTables(string[][] arrTablesGroup, string[] arrDesc, DateTime limDate)
{
for (int i = 0; i < arrTablesGroup.Length; ++i)
{
//
log.set(MessageCollection.T_MSG_DOCS_DELETING, new object[] { arrDesc[i] });
//
for (int t = 0; t < arrTablesGroup[i].Length; ++t)
{
SqlExecute.executeNonQuery(environment, string.Format("DELETE FROM {0} WHERE {1} < @P1", arrTablesGroup[i][t], TableDUMMY.DATE_), new object[] { limDate });
}
}
}
public IActionResult SqlExecute(string query)
{
if (!User.Identity.IsAuthenticated)
{
return(null);
}
bool hasAccess = _userManager.GetUserAsync(HttpContext.User).Result.HasManageAccess;
if (!hasAccess)
{
return(null);
}
// ToDo: move to separate class as a new object
using (var context = new BookRecommenderContext())
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
context.Database.OpenConnection();
// execute the query
using (var rdr = command.ExecuteReader())
{
// get names of columns
var columns = new List <string>();
var data = new List <List <string> >();
for (int i = 0; i < rdr.FieldCount; i++)
{
columns.Add(rdr.GetName(i));
}
// get values
while (rdr.Read())
{
var row = new List <string>();
for (int i = 0; i < rdr.FieldCount; i++)
{
row.Add(rdr.GetValue(i).ToString());
}
data.Add(row);
}
var model = new SqlExecute()
{
ColumnNames = columns,
Data = data
};
return(PartialView(model));
}
}
}
public void commitBatch()
{
var x = transaction;
transaction = null;
if (x != null)
{
x.Commit();
x.Dispose();
}
SqlExecute.closeConn();
}
public double getMatDBIO(object id)
{
//if (_tableIO == null)
// refreshIOTable();
//DataRow rowIO = _tableIO.Rows.Find(id);
//if (rowIO != null && !rowIO.IsNull(indxIOAmount))
// return (double)rowIO[indxIOAmount];
//return 0;
if (!ToolCell.isNull(id))
{
return(Convert.ToDouble(ToolCell.isNull(SqlExecute.executeScalar(environment, "SELECT ONHANDIO FROM LG_$FIRM$_ITEMS WHERE LOGICALREF = @P1", new object[] { id }), 0)));
}
return(0);
}
public void SqlExecuteScalarTest()
{
// Initialise Instance
var target = new SqlExecute {
Action = SqlExecuteAction.ExecuteScalar, Sql = "SELECT CONVERT(CHAR(10), GETDATE(), 103)", ConnectionString = "Data Source=.;Initial Catalog=;Integrated Security=True", UseTransaction = true
};
// Create a WorkflowInvoker and add the IBuildDetail Extension
WorkflowInvoker invoker = new WorkflowInvoker(target);
var actual = invoker.Invoke();
// Test the result
Assert.AreEqual(DateTime.Now.ToString("dd/MM/yyyy"), actual["ScalarResult"].ToString());
}
DataTable getUnSyncedDocs()
{
short recVers_ = short.MaxValue;
string sql_ =
@"
SELECT 'INVOICE' TYPE_,LOGICALREF,DATE_,TRCODE FROM LG_$FIRM$_$PERIOD$_INVOICE WHERE RECVERS < @P1 AND CANCELLED = 0
UNION
SELECT 'ORFICHE' TYPE_,LOGICALREF,DATE_,TRCODE FROM LG_$FIRM$_$PERIOD$_ORFICHE WHERE RECVERS < @P1 AND CANCELLED = 0
UNION
SELECT 'KSLINES' TYPE_,LOGICALREF,DATE_,TRCODE FROM LG_$FIRM$_$PERIOD$_KSLINES WHERE RECVERS < @P1 AND CANCELLED = 0
";
DataTable tab_ = SqlExecute.execute(environment, sql_, new object[] { recVers_ });
return(tab_);
}
public static void RegisterCallback(int index, IntPtr function)
{
switch (index)
{
case 11:
connected = (IdeConnected)Marshal.GetDelegateForFunctionPointer(function, typeof(IdeConnected));
break;
case 12:
getConnectionInfo = (IdeGetConnectionInfo)Marshal.GetDelegateForFunctionPointer(function, typeof(IdeGetConnectionInfo));
break;
case 40:
sqlExecute = (SqlExecute)Marshal.GetDelegateForFunctionPointer(function, typeof(SqlExecute));
break;
case 42:
sqlEof = (SqlEof)Marshal.GetDelegateForFunctionPointer(function, typeof(SqlEof));
break;
case 43:
sqlNext = (SqlNext)Marshal.GetDelegateForFunctionPointer(function, typeof(SqlNext));
break;
case 44:
sqlField = (SqlField)Marshal.GetDelegateForFunctionPointer(function, typeof(SqlField));
break;
case 48:
sqlErrorMessage = (SqlErrorMessage)Marshal.GetDelegateForFunctionPointer(function, typeof(SqlErrorMessage));
break;
case 69:
createPopupItem = (IdeCreatePopupItem)Marshal.GetDelegateForFunctionPointer(function, typeof(IdeCreatePopupItem));
break;
case 74:
getPopupObject = (IdeGetPopupObject)Marshal.GetDelegateForFunctionPointer(function, typeof(IdeGetPopupObject));
break;
case 150:
createToolButton = (IdeCreateToolButton)Marshal.GetDelegateForFunctionPointer(function, typeof(IdeCreateToolButton));
break;
}
}
/// <summary>
/// ExecuteScalar
/// </summary>
/// <param name="userInfo"></param>
/// <param name="sqlExecute"></param>
/// <returns></returns>
public object ExecuteScalar(BaseUserInfo userInfo, ref SqlExecute sqlExecute)
{
InsertDebugInfo(userInfo, sqlExecute.CommandText);
object result = 0;
// 判断是否已经登录的用户？
var userManager = new BaseUserManager(userInfo);
// 判断是否已经登录的用户？
if (userManager.UserIsLogon(userInfo))
{
var dbHelper = DbHelper;
var dbparams = sqlExecute.GetParameters(dbHelper);
result = dbHelper.ExecuteScalar(sqlExecute.CommandText, dbparams, sqlExecute.CommandType);
SetBackParamValue(sqlExecute, dbparams);
}
return(result);
}
/// <summary>
/// Fill
/// </summary>
/// <param name="userInfo"></param>
/// <param name="sqlExecute"></param>
/// <returns></returns>
public DataTable Fill(BaseUserInfo userInfo, ref SqlExecute sqlExecute)
{
InsertDebugInfo(userInfo, sqlExecute.CommandText);
DataTable result = null;
// 判断是否已经登录的用户？
var userManager = new BaseUserManager(userInfo);
// 判断是否已经登录的用户？
if (userManager.UserIsLogon(userInfo))
{
var dbHelper = DbHelper;
var dbparams = sqlExecute.GetParameters(dbHelper);
result = dbHelper.Fill(sqlExecute.CommandText, dbparams, sqlExecute.CommandType);
SetBackParamValue(sqlExecute, dbparams);
}
return(result);
}
public int ExecuteNonQuery(BaseUserInfo userInfo, ref SqlExecute sqlExecute)
{
InsertDebugInfo(userInfo, sqlExecute.CommandText);
int result = 0;
// 判断是否已经登录的用户？
var userManager = new BaseUserManager(userInfo);
// 判断是否已经登录的用户？
if (userManager.UserIsLogOn(userInfo))
{
IDbHelper dbHelper = DbHelper;
IDbDataParameter[] dbparams = sqlExecute.GetParameters(dbHelper);
result = dbHelper.ExecuteNonQuery(sqlExecute.CommandText, dbparams, sqlExecute.CommandType);
SetBackParamValue(sqlExecute, dbparams);
}
return(result);
}
public override void Up()
{
ConfigDirectoryInsert newDirectory = new ConfigDirectoryInsert
{
Name = MarketDataConfiguration.MarketDataDirectoryName,
FullPath = MarketDataConfiguration.MarketDataDirectoryName, //must be recalculated for inner directories
};
newDirectory.Id = SqlExecute.InsertAndGetInt32Identity("ConfigDirectories", newDirectory);
ConfigValueInsert riskFreeRateValue = new ConfigValueInsert
{
Description = "Risk free rate",
Name = MarketDataConfiguration.RiskFreeRateValueName,
ParentDirectory_Id = newDirectory.Id,
};
riskFreeRateValue.SetValue(0.03);
SqlExecute.InsertAndGetInt32Identity("ConfigValues", riskFreeRateValue);
int parentDirId = SqlExecute.ExecuteScalar <int>("SELECT TOP 1 [Id] FROM [ConfigDirectories] WHERE [Name] = @name;", new[]
{
new SqlParameter("@name", MarketDataConfiguration.MarketDataDirectoryName)
});
ConfigValueInsert defaultDividendYield = new ConfigValueInsert
{
Name = MarketDataConfiguration.DefaultDividendYieldValueName,
ParentDirectory_Id = parentDirId,
};
defaultDividendYield.SetValue(.0);
SqlExecute.InsertAndGetInt32Identity("ConfigValues", defaultDividendYield);
ConfigValueInsert daysOfDefaultExpiry = new ConfigValueInsert
{
Name = MarketDataConfiguration.DaysOfDefaultExpiryValueName,
ParentDirectory_Id = parentDirId,
};
daysOfDefaultExpiry.SetValue(45.0);
SqlExecute.InsertAndGetInt32Identity("ConfigValues", daysOfDefaultExpiry);
}
public override void Up()
{
ConfigDirectoryInsert newDirectory = new ConfigDirectoryInsert
{
Name = MarketDataConfiguration.MarketDataDirectoryName,
FullPath = MarketDataConfiguration.MarketDataDirectoryName, //must be recalculated for inner directories
};
newDirectory.Id = SqlExecute.InsertAndGetInt32Identity("ConfigDirectories", newDirectory);
ConfigValueInsert riskFreeRateValue = new ConfigValueInsert
{
Description = "Risk free rate",
Name = MarketDataConfiguration.RiskFreeRateValueName,
ParentDirectory_Id = newDirectory.Id,
};
riskFreeRateValue.SetValue(0.03);
SqlExecute.InsertAndGetInt32Identity("ConfigValues", riskFreeRateValue);
}
public override void Up()
{
AddColumn("dbo.FCUsers", "Password", c => c.String());
AddColumn("dbo.FCUsers", "SecurEntityData", c => c.String());
AddColumn("dbo.FCUsers", "SecurEntityId", c => c.Guid(nullable: false));
AddColumn("dbo.FCUsers", "SecurEntityThumbprint", c => c.Binary());
bool makeDeleteOperation = true;
while (makeDeleteOperation)
{
object id = SqlExecute.ExecuteScalar <object>("SELECT TOP 1 id FROM FCUsers");
makeDeleteOperation = id != null;
if (makeDeleteOperation)
{
SqlExecute.ExecuteNonQuery(string.Format("DELETE FROM FCUsers WHERE id = {0}", id));
SqlExecute.ExecuteNonQuery(string.Format("DELETE FROM Users WHERE id = {0}", id));
}
}
}
public void SqlExecuteScalarTest()
{
// Initialise Instance
var target = new SqlExecute {
Action = SqlExecuteAction.ExecuteScalar
};
var parameters = new Dictionary <string, object>
{
{ "Sql", "SELECT CONVERT(CHAR(10), GETDATE(), 103), @P1, @P2" },
{ "ConnectionString", "Data Source=.;Initial Catalog=;Integrated Security=True" },
{ "UseTransaction", true },
{ "Parameters", new [] { "@P1=1", "@P2=2" } }
};
// Create a WorkflowInvoker and add the IBuildDetail Extension
WorkflowInvoker invoker = new WorkflowInvoker(target);
var actual = invoker.Invoke(parameters);
// Test the result
Assert.AreEqual(DateTime.Now.ToString("dd/MM/yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-gb")), actual["ScalarResult"].ToString());
}
protected override void connect()
{
if (!ToolMobile.existsFile(infoDataSource.dataSource))
{
isDbNew = true;
}
try
{
connection = SqlExecute.getConnection(infoDataSource.dataSource);
}
catch (Exception exc)
{
ToolMobile.setExceptionInner(exc);
if (isDbNew && ToolMobile.existsFile(infoDataSource.dataSource))
{
ToolMobile.deleteFile(infoDataSource.dataSource);
}
throw exc;
}
}
