public bool GenerateMonthlyPayrollReport(string procname, Object[] parameters)
{
var q = _rmsctx.ExecuteQuery <MonthlyPayrollReportModel>("Exec " + procname, parameters);
var result = q.ToList();
return(FinanceReportService.GenerateMonthlyPayrollReport(result));
}
public INVStockLoading checklocation(int chamber, int floor, int pocket)
{
//try
//{
INVStockLoading invsl = new INVStockLoading();
string whereClause = string.Empty;
string queryTable = "INVStockLoading";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if ((chamber != 0))
{
whereClause += " WHERE ChamberNo = '" + chamber + "' and Floor='" + floor + "' and Pocket='" + pocket + "';";
}
strSQL += whereClause;
int cnt = dc.ExecuteQuery <INVStockLoading>(@strSQL).Count();
invsl = (cnt > 0) ? dc.ExecuteQuery <INVStockLoading>(@strSQL).First() : null;
return(invsl);
//}
//catch (Exception ex)
//{
// //_Logger.Error(ex);
// return null;
//}
}
public static int getNextIdentifier(Type T, DataContext Connection)
{
try
{
string tableName = GetTableName(T);
if (tableName != string.Empty)
{
var results = Connection.ExecuteQuery("SELECT [NextIDInt] FROM [NextID] WHERE [Table] = '" + tableName + "'");
if (results.Count( ) == 0)
{
return(0);
}
int nextID = (int)results.First()[0];
Connection.ExecuteQuery(string.Format("UPDATE [NextID] SET [NextIDInt] = {0} WHERE [Table] = '{1}'", nextID + 1, tableName)).Count();
return(nextID);
}
else
{
return(0);
}
}
catch
{
return(0);
}
}
public Linq2BafSql(string sqlFilePath)
{
SQLiteConnection cn = new SQLiteConnection("Data Source=" + sqlFilePath + ";Version=3");
core = new DataContext(cn, mapping)
{
DeferredLoadingEnabled = false,
ObjectTrackingEnabled = false
};
// opening a connection at this point leads to a 7 fold speed increase when looking up mass spectra.
core.Connection.Open();
// increases speed of massspectra look up but is not compatible with a
// function in baf2sql_c.dll (probably when creating the sqlite cache) because they are blocking the db access of each another
// TODO: Examine which baf2sql_c method is causing this.
//System.Data.Common.DbTransaction tn = core.Connection.BeginTransaction();
try
{
core.ExecuteQuery <int>("CREATE INDEX StepsID ON Steps (TargetSpectrum)");
}
catch
{
Console.Out.WriteLine("INDEX On TargedSpectrum in Steps table already exists, creation is skipped");
}
try
{
core.ExecuteQuery <int>("CREATE INDEX SpectrumID ON PerSpectrumVariables (Spectrum)");
}
catch
{
Console.Out.WriteLine("INDEX On SpectrumID in PerSpectrumVariables table already exists, creation is skipped");
}
}
public void Initialize()
{
using (var db = new DataContext(_config.ConnectionString))
{
_queueID = db.ExecuteQuery<int>("exec [dbo].[GetQueueID] {0}", _queueName).Single();
_poisonQueueID = db.ExecuteQuery<int>("exec [dbo].[GetQueueID] {0}", _queueName + "_Poison").Single();
}
}
public RTN_SAVE_DATA CompanySave_Exec(T_COMPANY Param, DataContext db1 = null)
{
if (db1 == null)
{
db1 = db;
}
RTN_SAVE_DATA rtnData = new RTN_SAVE_DATA()
{
};
if (!(string.IsNullOrEmpty(Param.PASSWORD) || Param.PASSWORD == "******"))
{
Param.PASSWORD = GlobalMvc.Util.Encrypt_PW(Param.PASSWORD);
}
if (Param.SAVE_TYPE != "D")
{
string sql = Global.DBAgent.LoadSQL(sqlBasePath + "BizService\\Basic\\T_COMPANY.xml", "CompanySave"
, Param.COMPANY_CODE.ToString("-1")
, Param.COMPANY_ID.ToString("")
, Param.PASSWORD.ToString("")
, Param.COMPANY_NAME.ToString("")
, Param.COMPANY_TYPE.ToString("")
, Param.COMPANY_TYPE2.ToString("")
, Param.PHONE.ToString("")
, Param.MOBILE.ToString("")
, Param.EMAIL.ToString("")
, Param.ADDRESS1.ToString("")
, Param.ADDRESS2.ToString("")
, Param.ZIP_CODE.ToString("")
, Param.LATITUDE.ToString("")
, Param.LONGITUDE.ToString("")
, Param.OWNER_NAME.ToString("")
, Param.OWNER_PHONE.ToString("")
, Param.OWNER_MOBILE.ToString("")
, Param.OWNER_EMAIL.ToString("")
, (Param.STORE_SYNC == null || Param.STORE_SYNC == false) ? "0" : "1"
, Param.STATUS.ToString("9")
, Param.CULTURE_NAME.ToString("ko-KR")
, Param.THEME_NAME.ToString("SPICYX")
, Param.REMARK.ToString("")
, Param.INSERT_CODE.ToString("0")
);
rtnData = db1.ExecuteQuery <RTN_SAVE_DATA>(sql).First();
}
else
{
string sql = Global.DBAgent.LoadSQL(sqlBasePath + "BizService\\Basic\\T_COMPANY.xml", "CompanyDelete"
, Param.COMPANY_CODE.ToString("-1")
, Param.INSERT_CODE
);
rtnData = db1.ExecuteQuery <RTN_SAVE_DATA>(sql).First();
}
return(rtnData);
}
public void Initialize()
{
foreach (var map in SimpleORM.Current.Mappings.Values)
{
var result = connection.ExecuteQuery <int?>("SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + map.Name + "'").FirstOrDefault();
if (result.HasValue && result == 0)
{
StringBuilder createTableCommand = new StringBuilder();
createTableCommand.Append("CREATE TABLE [" + map.Name + "](");
foreach (var column in map.Data)
{
var fieldReferences = false;
foreach (var reference in map.References)
{
if (reference.Value.Value == column.Key.Name)
{
fieldReferences = true;
break;
}
}
var valueToAppend = column.Value;
if (fieldReferences)
{
valueToAppend = valueToAppend.Replace("MAX", "500");
}
if (map.SecondaryKeyNames.Contains(column.Key.Name))
{
valueToAppend = valueToAppend.Replace("MAX", "500");
valueToAppend += " UNIQUE NOT NULL";
}
if (map.PrimaryKeyName == column.Key.Name && !map.SecondaryKeyNames.Contains(column.Key.Name))
{
valueToAppend += " NOT NULL";
}
valueToAppend += ",";
createTableCommand.Append(valueToAppend);
}
createTableCommand.Append(")");
var request = new SqlRequest(createTableCommand.ToString());
Execute(request);
}
}
BuildReferences();
}
public static int Run(DataContext ctx, string city)
{
string queryString = "SELECT * FROM dbo.Users WHERE City = '" + city + "'";
var users = ctx.ExecuteQuery <UserEntity>(queryString);
var users1 = ctx.ExecuteQuery(typeof(UserEntity), queryString);
var users2 = ctx.ExecuteQuery(elementType: typeof(UserEntity), parameters: city, query: queryString);
var users3 = ctx.ExecuteQuery(typeof(string), queryString);
var users4 = ctx.ExecuteQuery(typeof(string), queryString, city);
var users5 = ctx.ExecuteCommand(queryString, city);
return(0);
}
/// <summary>
/// Queries the target database specified by the SqlConnection for schema information.
/// </summary>
private static SchemaDiscoveryResult GetSchemaInformation(SqlConnection connection)
{
using (DataContext context = new DataContext(connection))
{
return(new SchemaDiscoveryResult
{
DatabaseName = connection.Database,
Objects = context.ExecuteQuery <DbObject>(SqlObjectsQuery).ToList(),
Columns = context.ExecuteQuery <DbColumn>(SqlColumnsQuery).ToList(),
Indexes = context.ExecuteQuery <DbIndex>(SqlIndexesQuery).ToList()
});
}
}
/// <summary>
/// Queries the target database specified by the SqlConnection for schema information.
/// </summary>
private static SchemaDiscoveryResult GetSchemaInformation(SqlConnection connection)
{
using (DataContext context = new DataContext(connection))
{
return new SchemaDiscoveryResult
{
DatabaseName = connection.Database,
Objects = context.ExecuteQuery<DbObject>(SqlObjectsQuery).ToList(),
Columns = context.ExecuteQuery<DbColumn>(SqlColumnsQuery).ToList(),
Indexes = context.ExecuteQuery<DbIndex>(SqlIndexesQuery).ToList()
};
}
}
/// <summary>
/// Calls STATS_DATE function for the current object
/// </summary>
/// <param name="dataContext">Database context used to execute the database call</param>
/// <returns>The last date at which statistics have been calculated for this object</returns>
public DateTime?GetStatsDate(DataContext dataContext)
{
#if Mono
return(dataContext.ExecuteQuery <object>(
"select STATS_DATE({0}, {1})", this.object_id, this.stats_id
)
.Select(o => o as DateTime?)
.FirstOrDefault());
#else
return(dataContext.ExecuteQuery <DateTime?>(
"select STATS_DATE({0}, {1})", this.object_id, this.stats_id
).FirstOrDefault());
#endif
}
public T ExecuteSelectOneStatement <T>(string query, params object[] parameters)
{
if (DatabaseExists())
{
try
{
return(_context.ExecuteQuery <T>(query, parameters).FirstOrDefault <T>());
}
catch (Exception)
{
return(default(T));
}
}
return(default(T));
}
public INVStockLoading GetLoadBySerial(string serialNo)
{
try
{
string whereClause = string.Empty;
string queryTable = "INVStockLoading";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if (!string.IsNullOrEmpty(serialNo))
{
whereClause += " WHERE SerialID = '" + serialNo + "' ";
}
strSQL += whereClause;
return(dc.ExecuteQuery <INVStockLoading>(@strSQL).First());
}
catch (Exception ex)
{
_Logger.Error(ex);
return(null);
}
}
private static async Task <Dictionary <string, string> > GetScripts(string connectionString, IEnumerable <string> scriptsList)
{
return(await Task <Dictionary <string, string> > .Run(() =>
{
Dictionary <string, string> scriptsContentDictionary = new Dictionary <string, string>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataContext dataContext = new DataContext(connection);
string query = "SELECT st.ScriptName, st.ScriptContent FROM Scripts.ScriptsTable st WHERE st.ScriptName IN (";
for (int i = 0; i < scriptsList.Count(); i++)
{
if (i == 0)
{
query += $"'{scriptsList.ElementAt(i)}'";
}
else
{
query += $",'{scriptsList.ElementAt(i)}'";
}
}
query += ")";
var scriptsContemtList = dataContext.ExecuteQuery <nestedScriptsContent>(query);
foreach (var i in scriptsContemtList)
{
scriptsContentDictionary.Add(i.ScriptName, i.ScriptContent);
}
}
return scriptsContentDictionary;
}));
}
public IEnumerable <TaskPriorityInfo> GetWaitingTasks(Slave slave)
{
//Originally we checked here if there are parent tasks which should be calculated (with GetParentTasks(resourceIds, count, false);).
//Because there is at the moment no case where this makes sense (there don't exist parent tasks which need to be calculated),
//we skip this step because it's wasted runtime
return(DataContext.ExecuteQuery <TaskPriorityInfo>(GetWaitingTasksQueryString, slave.ResourceId, Enum.GetName(typeof(TaskState), TaskState.Waiting), slave.FreeCores, slave.FreeMemory).ToList());
}
public ActionResult FromBase()
{
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
excelApp.Workbooks.Open("D:/some.xlsx");
int row = 1;
List <List <string> > maping = new List <List <string> >();
Excel.Worksheet currentSheet = (Excel.Worksheet)excelApp.Workbooks[1].Worksheets[1];
while (currentSheet.get_Range("A" + row).Value2 != null)
{
List <string> tempList = new List <string>();
for (char column = 'A'; column < 'J'; column++)
{
Excel.Range cell = currentSheet.get_Range(column.ToString() + row);
tempList.Add(cell.Value2 != null ? cell.Value2.ToString() : "");
}
maping.Add(tempList);
row++;
}
excelApp.Quit();
var dc = new DataContext("Data Source=TRITII;Initial Catalog=123;Integrated Security=True");
var list = dc.ExecuteQuery <Information>("select Id,Title,Description,PublicationDate from Informations").ToList();
//new SqlConnection("Data Source=TRITII;Initial Catalog=123;Integrated Security=True")
return(View(list));
}
public List <T> GetGenericWithSQLQuery <T>(string sqlStr, object[] parametres) where T : class
{
_cntxt = new DataContext(_connStr);
try
{
_cntxt.CommandTimeout = 600;
if (_cntxt.Connection.State == System.Data.ConnectionState.Closed)
{
_cntxt.Connection.Open();
}
List <T> list = (from s in _cntxt.ExecuteQuery <T>(sqlStr, parametres) select s).ToList <T>();
return(list);
}
catch (Exception exp)
{
_cntxt.Connection.Close();
try
{
StackTrace stackTrace = new StackTrace();
MethodBase methodBase = stackTrace.GetFrame(1).GetMethod();
LogException(exp, "GetGenericWithSQLQuery in " + methodBase.ReflectedType.Name + "." + methodBase.Name, 0);
}
catch { }
return(null);
}
}
public bool DoesEmailAlreadyExist(string Email)
{
var connection = new SqliteConnection(string.Format(@"Data Source={0};Version=3;Read Only=True",
Application.streamingAssetsPath + "\\" + "Players.db"));
var context = new DataContext(connection);
if (context.DatabaseExists())
{
var query = @"SELECT ID FROM PlayerData where Email={0}";
List <PlayerData> table = context.ExecuteQuery <PlayerData>(query, Email).ToList <PlayerData>();
if (table.Count != 0)
{
context.Dispose();
connection.Close();
connection.Dispose();
return(true);
}
}
context.Dispose();
connection.Close();
connection.Dispose();
return(false);
}
private void SavePayerDetails()
{
var connection = new SqliteConnection(string.Format(@"Data Source={0};Version=3",
Application.streamingAssetsPath + "\\" + "Players.db"));
var context = new DataContext(connection);
if (context.DatabaseExists())
{
var Name = viewModel.Name.text;
var Email = viewModel.Email.text;
var Country = viewModel.Countries.Text;
var OptOut = viewModel.OptOut.isOn;
var query = @"INSERT INTO PlayerData(Name,Email,Country,OptOut,StartTime,EndTime)
values({0}, {1}, {2}, {3}, {4}, {5})";
DateTime gameStartTime = DateTime.UtcNow;
string timeToSave = gameStartTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
context.ExecuteCommand(query, Name, Email, Country, OptOut, timeToSave, timeToSave);
query = @"SELECT ID FROM PlayerData where Email={0} ORDER BY ID";
List <PlayerData> table = context.ExecuteQuery <PlayerData>(query, Email).ToList <PlayerData>();
gameController.Init(gameStartTime, table.Last <PlayerData>().ID);
}
context.Dispose();
connection.Close();
connection.Dispose();
}
/// <summary>
/// DA method to search object by params
/// </summary>
/// <param name="partyCode"></param>
/// <param name="partyName"></param>
/// <param name="culture"></param>
/// <returns></returns>
public IList <INVParty> SearchPartyByCode(string partyCode)
{
try
{
string whereClause = string.Empty;
string queryTable = "t_INVParty";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if (!string.IsNullOrEmpty(partyCode))
{
whereClause += " WHERE PartyCode = '" + partyCode + "'";
}
strSQL += whereClause;
return(dc.ExecuteQuery <INVParty>(@strSQL).ToList());
}
catch (Exception ex)
{
_Logger.Error(ex);
return(null);
}
}
private void CopyDb(StandardKernel kernel, out FileInfo sandboxFile, out string connectionString)
{
var config = kernel.Get<IConfig>();
var db = new DataContext(config.ConnectionStrings("ConnectionString"));
TestDbName = string.Format("{0}_{1}", NameDb, DateTime.Now.ToString("yyyyMMdd_HHmmss"));
sandboxFile = new FileInfo(string.Format("{0}\\{1}.bak", Sandbox, TestDbName));
var sandboxDir = new DirectoryInfo(Sandbox);
//backupFile
var textBackUp = string.Format(@"-- Backup the database
BACKUP DATABASE [{0}]
TO DISK = '{1}'
WITH COPY_ONLY",
NameDb, sandboxFile.FullName);
db.ExecuteCommand(textBackUp);
var restoreFileList = string.Format("RESTORE FILELISTONLY FROM DISK = '{0}'", sandboxFile.FullName);
var fileListRestores = db.ExecuteQuery<FileListRestore>(restoreFileList).ToList();
var logicalDbName = fileListRestores.FirstOrDefault(p => p.Type == "D");
var logicalLogDbName = fileListRestores.FirstOrDefault(p => p.Type == "L");
var restoreDb = string.Format("RESTORE DATABASE [{0}] FROM DISK = '{1}' WITH FILE = 1, MOVE N'{2}' TO N'{4}\\{0}.mdf', MOVE N'{3}' TO N'{4}\\{0}.ldf', NOUNLOAD, STATS = 10", TestDbName, sandboxFile.FullName, logicalDbName.LogicalName, logicalLogDbName.LogicalName, sandboxDir.FullName);
db.ExecuteCommand(restoreDb);
connectionString = config.ConnectionStrings("ConnectionString").Replace(NameDb, TestDbName);
}
// extracted from ts-app\WebApp\App_Code\TicketPageService.cs UpdateAction() AND
// also extracted from ts-app\webapp\app_code\ticketservice.cs
public static void Create(DataContext db, int organizationID, int userID, int ticketID, ref ActionProxy actionProxy)
{
Action action = new Actions(new LoginUser(userID, organizationID)).AddNewAction();
action.TicketID = ticketID;
action.CreatorID = userID;
action.Description = actionProxy.Description;
action.SystemActionTypeID = actionProxy.SystemActionTypeID;
if (!String.IsNullOrEmpty(actionProxy.Name))
{
action.Name = actionProxy.Name;
}
// add signature?
string signature = db.ExecuteQuery <string>($"SELECT [Signature] FROM Users WITH (NOLOCK) WHERE UserID={userID} AND OrganizationID={organizationID}").FirstOrDefault();
if (!string.IsNullOrWhiteSpace(signature) && actionProxy.IsVisibleOnPortal && !actionProxy.IsKnowledgeBase && !actionProxy.Description.Contains(signature))
{
action.Description += "<br/><br/>" + signature;
}
action.ActionSource = actionProxy.ActionSource;
action.ActionTypeID = actionProxy.ActionTypeID;
action.DateStarted = actionProxy.DateStarted;
action.TimeSpent = actionProxy.TimeSpent;
action.IsKnowledgeBase = actionProxy.IsKnowledgeBase;
action.IsVisibleOnPortal = actionProxy.IsVisibleOnPortal;
action.Collection.Save();
actionProxy = action.GetProxy();
}
public IList <T> ExecuteQuery <T>(string commandText, params DbParameter[] parameters)
{
var ls = DataContext.ExecuteQuery <T>(commandText, parameters);
DataContext.SaveChanges();
return(ls);
}
public IList <INVStockSerial> SearchSerialByParty(int partyid)
{
try
{
string whereClause = string.Empty;
string queryTable = "INVStockSerial";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if (partyid != 0)
{
whereClause += " WHERE partyid = '" + partyid + "' ";
}
strSQL += whereClause;
return(dc.ExecuteQuery <INVStockSerial>(@strSQL).ToList());
}
catch (Exception ex)
{
_Logger.Error(ex);
return(null);
}
}
protected List <T> ConextRun(string sql)
{
List <T> ResultList = new List <T>();
LinqConextClass context = new LinqConextClass(this.Provider);
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collections = dataContext.ExecuteQuery((new T()).GetType(), sql);
foreach (var item in collections)
{
T temp = item as T;
ResultList.Add(temp);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(ResultList);
}
public static List <TSelectModel> Select2Model <TSelectModel>(IProvider provider, string sql) where TSelectModel : class, new()
{
List <TSelectModel> ResultList = new List <TSelectModel>();
LinqConextClass context = new LinqConextClass(provider);
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collections = dataContext.ExecuteQuery((new TSelectModel()).GetType(), sql);
foreach (var item in collections)
{
TSelectModel temp = item as TSelectModel;
ResultList.Add(temp);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(ResultList);
}
public static bool Exists(this DataContext context,
object dataObject, string[] fields, object[] values)
{
MetaTable table = context.Mapping.GetTable(dataObject.GetType());
//检查值是否和字段配对
if (fields.Length != values.Length)
{
throw new ArgumentException("length of fields and values mismatched..");
}
string sql = string.Format(
"SELECT 1 FROM [{0}] WHERE 1=1", table.TableName);
int index = 0;
foreach (string fieldName in fields)
{
MetaDataMember member =
table.RowType.PersistentDataMembers.Single(m => m.Name == fieldName);
sql += string.Format(" AND [{0}] = {{{1}}}", member.MappedName, index.ToString());
index++;
}
sql = string.Format(
"IF EXISTS ({0}) SELECT 1 ELSE SELECT 0", sql);
int count =
context.ExecuteQuery <int>(sql, values).Single();
return(count > 0);
}
public IList <SRVRegistration> SearchRegForTotalLoan(int partyid, string requisitioned)
{
try
{
string whereClause = string.Empty;
string queryTable = "SRVRegistration";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if ((partyid != 0))
{
whereClause += " WHERE partyID = '" + partyid + "' and requisitioned='" + requisitioned + "' ;";
}
strSQL += whereClause;
return(dc.ExecuteQuery <SRVRegistration>(@strSQL).ToList());
}
catch (Exception ex)
{
_Logger.Error(ex);
return(null);
}
}
public SRVRegistration SearchSRVBySerialParty(string serialNo, int partyid)
{
try
{
string whereClause = string.Empty;
string queryTable = "SRVRegistration";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if (!string.IsNullOrEmpty(serialNo))
{
whereClause += " WHERE SerialNo = '" + serialNo + "' and partyID='" + partyid + "' ";
}
strSQL += whereClause;
return(dc.ExecuteQuery <SRVRegistration>(@strSQL).ToList().First());
}
catch (Exception ex)
{
_Logger.Error(ex);
return(null);
}
}
public void ExecuteQuery()
{
context.Log = new StringWriter();
try
{
context.ExecuteQuery(typeof(Person), "select * from people", 1, 2, 3);
}
catch (NotSupportedException)
{
}
catch (Exception e)
{
Assert.Fail("# ExecuteQuery: unexpected exception: {0}", e.ToString());
}
Console.WriteLine("# ExecuteQuery: Log={0}", context.Log);
}
public IList <INVItemDetail> GetItemDetailByRegID(string regid)
{
try
{
string whereClause = string.Empty;
string queryTable = "INVItemDetail";
DataContext dc = new DataContext(ConfigurationManager.ConnectionStrings["CSMSysConnection"].ConnectionString);
string strSQL = "SELECT * FROM " + queryTable;
if (!string.IsNullOrEmpty(regid))
{
whereClause += " WHERE RegistrationID = '" + regid + "' ";
}
strSQL += whereClause;
return(dc.ExecuteQuery <INVItemDetail>(@strSQL).ToList());
}
catch (Exception ex)
{
_Logger.Error(ex);
return(null);
}
}
private void CopyDb(StandardKernel kernel, out FileInfo sandboxFile, out string connectionString)
{
var config = kernel.Get <IConfig>();
var db = new DataContext(config.ConnectionStrings("ConnectionString"));
TestDbName = string.Format("{0}_{1}", NameDb, DateTime.Now.ToString("yyyyMMdd_HHmmss"));
sandboxFile = new FileInfo(string.Format("{0}\\{1}.bak", Sandbox, TestDbName));
var sandboxDir = new DirectoryInfo(Sandbox);
//backupFile
var textBackUp = string.Format(@"-- Backup the database
BACKUP DATABASE [{0}]
TO DISK = '{1}'
WITH COPY_ONLY",
NameDb, sandboxFile.FullName);
db.ExecuteCommand(textBackUp);
var restoreFileList = string.Format("RESTORE FILELISTONLY FROM DISK = '{0}'", sandboxFile.FullName);
var fileListRestores = db.ExecuteQuery <FileListRestore>(restoreFileList).ToList();
var logicalDbName = fileListRestores.FirstOrDefault(p => p.Type == "D");
var logicalLogDbName = fileListRestores.FirstOrDefault(p => p.Type == "L");
var restoreDb = string.Format("RESTORE DATABASE [{0}] FROM DISK = '{1}' WITH FILE = 1, MOVE N'{2}' TO N'{4}\\{0}.mdf', MOVE N'{3}' TO N'{4}\\{0}.ldf', NOUNLOAD, STATS = 10", TestDbName, sandboxFile.FullName, logicalDbName.LogicalName, logicalLogDbName.LogicalName, sandboxDir.FullName);
db.ExecuteCommand(restoreDb);
connectionString = config.ConnectionStrings("ConnectionString").Replace(NameDb, TestDbName);
}
public int GetMaxId()
{
int maxId = 0;
StringBuilder strSql = new StringBuilder();
//select Max([DepartmentID]) from Department
strSql.Append("SELECT MAX([DepartmentID]) FROM Department");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collection = dataContext.ExecuteQuery((new int()).GetType(), strSql.ToString());
foreach (int item in collection)
{
maxId = item;
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(maxId);
}
public StatelessSqlQueueWriter(
IEnvelopeStreamer streamer,
string connectionString,
string name
)
{
_streamer = streamer;
_connectionString = connectionString;
Name = name;
using (var db = new DataContext(_connectionString))
{
_queueID = db.ExecuteQuery<int>("exec [dbo].[GetQueueID] {0}", name).Single();
}
}
public Common.Execution.QueryResult ExecuteQuery(String script)
{
var fullScript = String.Format("exec {0}", script);
Common.Execution.QueryResult executionResult;
try
{
using (var context = new DataContext(_connectionString))
{
var result = context.ExecuteQuery<Int32>(fullScript);
executionResult = Common.Execution.QueryResult.CreateSuccessfulResult(result);
}
}
catch (Exception exception)
{
executionResult = Common.Execution.QueryResult.CreateFailResult(exception);
}
return executionResult;
}
public GetEnvelopeResult TryGetMessage()
{
QueueMessage message;
try
{
using (var db = new DataContext(_config.ConnectionString))
{
message = db
.ExecuteQuery<QueueMessage>
(
"exec [dbo].[Dequeue] {0}, {1}, {2}",
_queueID,
_visibilityTimeout.TotalSeconds,
1
)
.FirstOrDefault();
}
}
catch(Exception ex)
{
_observer.Notify(new FailedToReadMessage(ex, _queueName));
return GetEnvelopeResult.Error();
}
if (message == null)
return GetEnvelopeResult.Empty;
try
{
var envelope = _streamer.ReadAsEnvelopeData(message.Envelope.ToArray());
var context = new EnvelopeTransportContext(message, envelope, _queueName);
return GetEnvelopeResult.Success(context);
}
catch(Exception ex)
{
_observer.Notify(new EnvelopeDeserializationFailed(ex, _queueName, message.EnvelopeID));
SendToPoisonQueue(message);
DeleteMessage(message);
return GetEnvelopeResult.Retry;
}
}
public void SetIdentityColumnNames(DataContext context)
{
this.IdentityColumnNames = context.ExecuteQuery<string>("select Name from sys.identity_columns where object_name(object_id) = {0}", this.TableName).ToList();
}
public void SetPrimaryKeyNames(DataContext context)
{
this.PrimaryKeyNames = context.ExecuteQuery<string>("SELECT column_name as ColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = {0}", this.TableName).ToList();
}
