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);
}
static void Main(string[] args)
{
using (var ctx = new DataContext(ConnectionString))
{
string path = "csv-market-data-2016";
var historicalData = CsvUtils.ReadAllDataFrom(path, 5);
foreach (var item in historicalData)
{
ctx.ExecuteCommand($@"CREATE TABLE {item.Key} (
Id INT IDENTITY(1,1) NOT NULL,
DateTime DATETIME NOT NULL,
Price NUMERIC(18, 6) NOT NULL)" );
DateTime dt = new DateTime(2016, 1, 1);
foreach (var value in item.Value)
{
string dtString = dt.ToString("yyyy-MM-dd");
string valueString = value.ToString(CultureInfo.InvariantCulture);
ctx.ExecuteCommand($"INSERT INTO {item.Key} (DateTime, Price) VALUES ('{dt}', {valueString})");
dt = dt.AddDays(1);
}
}
}
}
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 void InitRepository()
{
_context.ExecuteCommand("truncate table dbo.IntegrationTest");
_context.ExecuteCommand("insert into dbo.IntegrationTest ([Value], [DateInserted]) values ('Test1', getdate())");
_context.ExecuteCommand("insert into dbo.IntegrationTest ([Value], [DateInserted]) values ('Test2', getdate())");
_context.ExecuteCommand("insert into dbo.IntegrationTest ([Value], [DateInserted]) values ('Test3', getdate())");
_repo = new TestRepository(_context);
}
private static async Task Parse(nestedPackage package, string connectionString)
{
await Task.Run(() =>
{
string serverConnectionString = $"Server={package.Server.ServerDomainName};Database={package.Server.DataBaseName};User={package.Server.ServerLogin};Password={package.Server.ServerPassword};";
using (SqlConnection connection = new SqlConnection(serverConnectionString))
{
string[] partsList = null;
DataContext pathContext = new DataContext(connection);
DataContext raportContext = new DataContext(connectionString);
string script = "";
connection.InfoMessage += (object sender, SqlInfoMessageEventArgs e) =>
{
raportContext.ExecuteCommand($"INSERT INTO Raports.Info (Data, ServerDB, Script, MessageInfo) VALUES (DEFAULT, 'Server:{package.Server.ServerDomainName} DB:{package.Server.DataBaseName}', '{script}', N'{e.Message}')");
};
bool hasError;
foreach (var i in package.Scripts)
{
script = i.Key;
partsList = i.Value.Split(new string[] { "go;", "GO;", "go", "GO" }, StringSplitOptions.RemoveEmptyEntries);
hasError = false;
foreach (var j in partsList)
{
try
{
pathContext.ExecuteCommand(j);
}
catch (System.Data.SqlClient.SqlException ex)
{
hasError = true;
raportContext.ExecuteCommand($"INSERT INTO Raports.Errors (DataTime, ServerDBUserPasswd, ScriptName, ErrorCode, ErrorMessage) VALUES (DEFAULT, 'Server:{package.Server.ServerDomainName} DB:{package.Server.DataBaseName}', '{i.Key}', {ex.Number}, N'{ex.Message}')");
}
catch (Microsoft.Data.SqlClient.SqlException ex)
{
hasError = true;
raportContext.ExecuteCommand($"INSERT INTO Raports.Errors (DataTime, ServerDBUserPasswd, ScriptName, ErrorCode, ErrorMessage) VALUES (DEFAULT, 'Server:{package.Server.ServerDomainName} DB:{package.Server.DataBaseName}', '{i.Key}', {ex.Number}, N'{ex.Message}')");
}
}
if (hasError == false)
{
raportContext.ExecuteCommand($"INSERT INTO Raports.Compleate (DataTime, ServerDBUserPasswd, ScriptName) VALUES (DEFAULT, 'Server:{package.Server.ServerDomainName} DB:{package.Server.DataBaseName}', '{i.Key}')");
}
}
}
});
}
void SaveOrganizationSentiment(DataContext db, Table <OrganizationSentiment> table)
{
// Ticket Sentiments by month
List <Metrics> metrics = _organizationAnalysis.Intervals; // each instance of Metrics = 1 month of data
if (metrics.Count > OrganizationSentimentMonths)
{
metrics = _organizationAnalysis.Intervals.GetRange(_organizationAnalysis.Intervals.Count - OrganizationSentimentMonths, OrganizationSentimentMonths);
}
metrics = metrics.Where(m => m._averageSentimentScore.HasValue).ToList();
// average score
double?score = null;
int ticketCount = metrics.Sum(m => m._closedCount);
if ((metrics.Count > 0) && (ticketCount > 0))
{
score = metrics.Sum(m => m._averageSentimentScore * m._closedCount) / ticketCount;
}
; // ticket sentiment based on closed tickets
// save to dbo
OrganizationSentiment organizationSentiment;
DBChangeType queryType = GetQueryType(score, out organizationSentiment);
switch (queryType)
{
case DBChangeType.DELETE: // DELETE
db.ExecuteCommand(String.Format(@"DELETE FROM [OrganizationSentiments] WHERE [OrganizationID] = {0}", _organizationAnalysis.OrganizationID));
break;
case DBChangeType.UPDATE: // UPDATE
db.ExecuteCommand(String.Format(@"UPDATE OrganizationSentiments SET OrganizationSentimentScore = {0}, TicketSentimentCount = {1} WHERE IsAgent=0 AND OrganizationID = {2}",
score.Value,
ticketCount,
_organizationAnalysis.OrganizationID));
break;
case DBChangeType.INSERT: // INSERT
table.InsertOnSubmit(new OrganizationSentiment()
{
OrganizationID = _organizationAnalysis.OrganizationID,
IsAgent = false,
OrganizationSentimentScore = score.Value,
TicketSentimentCount = ticketCount
});
break;
default: // NONE
break;
}
}
private void RemoveDb()
{
var config = DependencyResolver.Current.GetService <IConfig>();
var db = new DataContext(config.ConnectionStrings("ConnectionString"));
var textCloseConnectionTestDb = string.Format(@"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", TestDbName);
db.ExecuteCommand(textCloseConnectionTestDb);
var textDropTestDb = string.Format(@"DROP DATABASE [{0}]", TestDbName);
db.ExecuteCommand(textDropTestDb);
}
private void DELETEALL_Click(object sender, RoutedEventArgs e)
{
if (MessageBox.Show("Вы уверены?", "Удалить все", MessageBoxButton.YesNo, MessageBoxImage.Stop) == MessageBoxResult.Yes)
{
try
{
db.ExecuteCommand("DELETE FROM applicant");
db.ExecuteCommand("DELETE FROM vacancies");
db.ExecuteCommand("DELETE FROM R");
db.ExecuteCommand("DELETE FROM R1");
db.ExecuteCommand("DELETE FROM R2");
db.ExecuteCommand("DELETE FROM final");
db.ExecuteCommand("DELETE FROM org");
db.ExecuteCommand("DELETE FROM REG");
UpdateFin();
UpdateOrg();
UpdateVacs();
UpdateApp();
MessageBox.Show("Все данные удалены", "Чисто!");
}
catch (Exception abc)
{
MessageBox.Show("Удаление не было произведено", "Ошибка!");
}
}
}
/*
* www.codeproject.com/Tips/873677/SQL-Server-Database-Backup-and-Restore-in-Csharp
*
*/
private void BtnRestore_Click(object sender, EventArgs e)
{
try
{
if (string.IsNullOrEmpty(cbDatabaseName.Text))
{
MessageBox.Show("Tên database không được để trống");
return;
}
else
{
openFileDialog.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*";
DialogResult result = openFileDialog.ShowDialog();
if (result == DialogResult.OK)
{
DataContext db = new DataContext("Data Source=" + cbServerName.Text + ";Database=Master;Integrated Security=True");
if (db.DatabaseExists())
{
// Single user mode
var Alter1 = db.ExecuteCommand(@"ALTER DATABASE
[" + cbDatabaseName.Text + "] SET Single_User WITH Rollback Immediate");
// Restore database
var query = db.ExecuteCommand(@"RESTORE DATABASE
[" + cbDatabaseName.Text + "] FROM DISK = N'" +
openFileDialog.FileName + @"' WITH FILE = 1, NOUNLOAD, STATS = 10");
// Multi user mode
var Alter2 = db.ExecuteCommand(@"ALTER DATABASE
[" + cbDatabaseName.Text + "] SET Multi_User");
if (query != 0)
{
MessageBox.Show("Phục hồi thành công");
}
else
{
MessageBox.Show("Không thể phục hồi");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected void ReCreateDatabaseFromAttributeMapping(DataContext ctx)
{
bool success = false;
bool retry = false;
int retries = 0;
do
{
try
{
using (ctx)
{
CloseAllOpenConnections(ctx);
ctx.Log = new StringWriter();
if (ctx.DatabaseExists())
{
//drop all connections by disabling multi-user (and immediatly abort all current transactions)
ctx.ExecuteCommand("ALTER DATABASE " + _databaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
ctx.DeleteDatabase();
}
try
{
ctx.CreateDatabase();
}
catch (Exception e)
{
Debug.WriteLine(e);
Debug.WriteLine(ctx.Log);
throw;
}
//re-enable multi-user (again)
ctx.ExecuteCommand("ALTER DATABASE " + _databaseName + " SET MULTI_USER");
success = true;
}
}
catch (SqlException e)
{
retry = false;
if (e.Message.IndexOf("was deadlocked on lock resources with another process and has been chosen as the deadlock victim") > 0)
{
retry = true;
retries++;
}
}
} while (success == false && (retry && retries < 3));
}
public static void DeleteByPK <TSource, TPK>(TPK[] pkArray, DataContext data)
where TSource : class
{
Table <TSource> table = data.GetTable <TSource>();
TableDef tableDef = GetTableDef <TSource>();
var buffer = new StringBuilder();
buffer.Append("DELETE FROM [")
.Append(tableDef.TableName)
.Append("] WHERE [")
.Append(tableDef.PKFieldName)
.Append("] IN (");
for (int i = 0; i < pkArray.Length; i++)
{
buffer.Append('\'')
.Append(pkArray[i].ToString())
.Append('\'')
.Append(',');
}
buffer.Length--;
buffer.Append(')');
data.ExecuteCommand(buffer.ToString());
}
public void DeleteImageOfCategory(int id)
{
using (var dataContext = new DataContext(_connectionString))
{
dataContext.ExecuteCommand("DELETE FROM CategoryPhoto WHERE category_id={0}", id);
}
}
public void DeleteImageOfProduct(int id)
{
using (var dataContext = new DataContext(_connectionString))
{
dataContext.ExecuteCommand("DELETE FROM Photo WHERE product_id={0}", id);
}
}
public void Save(testtable table)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into testtable(");
strSql.Append("col1,col2)");
strSql.Append(" values (");
strSql.Append(string.Format("'{0}','{1}'", table.col1, table.col2));
strSql.Append(")");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
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();
}
public void Delete(testtable table)
{
StringBuilder strSql = new StringBuilder();
//delete from testtable where col1='55'
strSql.Append("delete from testtable ");
strSql.Append(string.Format("where col1='{0}'", table.col1));
//strSql.Append("where '{0}'='{0}' and col1='{1}'");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
public static void DeleteByPK <TS, TK>(TK[] primary_keys, DataContext data_context)
where TS : class
{
Table <TS> _table = data_context.GetTable <TS>();
TableDef _tableDef = GetTableDef <TS>();
var _buffer = new StringBuilder();
_buffer
.Append("DELETE FROM [")
.Append(_tableDef.TableName)
.Append("] WHERE [")
.Append(_tableDef.PrimaryKey)
.Append("] IN (");
for (int i = 0; i < primary_keys.Length; i++)
{
_buffer
.Append('\'')
.Append(primary_keys[i].ToString())
.Append('\'')
.Append(',');
}
_buffer.Length--;
_buffer.Append(')');
data_context.ExecuteCommand(_buffer.ToString());
}
static public void updateNovelList(int id, string html, SQLiteConnection aConnection)
{
using (var aConText = new DataContext(aConnection))
{
var htmlDoc = new HtmlAgilityPack.HtmlDocument();
htmlDoc.LoadHtml(html);
var title = htmlDoc.DocumentNode
.SelectNodes(@"//p[@class=""novel_title""]")
.Select(a => a.InnerText.Trim())
.ToList().FirstOrDefault();
var writername = htmlDoc.DocumentNode
.SelectNodes(@"//div[@class=""novel_writername""]")
.Select(a => a.InnerText.Trim())
.ToList().FirstOrDefault().Substring(3);
aConText.ExecuteCommand(
@"UPDATE Novellists SET html = {0}, title = {1}, writername = {2}, regdate = {3} WHERE id = {4}",
html,
title,
writername,
DateTime.Today.ToShortDateString() + " " + DateTime.Now.ToShortTimeString(),
id);
}
}
public string ADPublishFinished(Common.Business.Task task)
{
string configName = System.Configuration.ConfigurationManager.AppSettings["defaultDatabase"];
DataContext dd = new DataContext(System.Configuration.ConfigurationManager.ConnectionStrings[configName].ConnectionString);
string taskSource = task.TaskSource;
string[] ids = taskSource.Split(new char[] { '|' });
string ap_id = ids[0];
string userid = ids[1];
string companyid = ids[2];
int state = (task.TaskState == TaskState.Finished ? 1 : (task.TaskState == TaskState.Failed ? 2 : 0));
string sql = string.Format(@"declare @State smallint,@AP_ID int,@UserID varchar(10),@CompanyID char(4),@FinishedDate datetime
set @State = {0}
set @AP_ID = {1}
set @UserID = '{2}'
set @CompanyID = '{3}'
set @FinishedDate ='{4}'
update ADPublishUser set State = @State ,FinishedDate = GETDATE() where AP_ID = @AP_ID and UserID = @UserID and CompanyID = @CompanyID
if (@State = 2)
update ADUser set AP_ID= @AP_ID,AC_ID = b.AC_ID from ADUser a,ADPublish b,ADPublishUser c where a.UserID = c.UserID and a.CompanyID = c.CompanyID and b.AP_ID = c.AP_ID and b.CompanyID = c.CompanyID
and c.AP_ID = @AP_ID and c.UserID = @UserID and c.CompanyID =@CompanyID", state, ap_id, userid, companyid, task.Finished);
try
{
object[] param = new object[0];
dd.ExecuteCommand(sql, param);
}
catch (Exception e)
{
return(e.Message);
}
return("");
}
private static void ClearTpDB(DataContext tpDatabaseDataContext)
{
if (!tpDatabaseDataContext.DatabaseExists())
{
tpDatabaseDataContext.CreateDatabase();
tpDatabaseDataContext.SubmitChanges();
}
else
{
tpDatabaseDataContext.ExecuteCommand("delete from Project");
tpDatabaseDataContext.ExecuteCommand("delete from TpUser");
tpDatabaseDataContext.ExecuteCommand("delete from General");
tpDatabaseDataContext.ExecuteCommand("delete from MessageUid");
tpDatabaseDataContext.ExecuteCommand("delete from PluginProfile");
tpDatabaseDataContext.ExecuteCommand("delete from Revision");
}
}
// Originally sourced from Omar @ http://weblogs.asp.net/omarzabir/archive/2008/10/30/linq-to-sql-delete-an-entity-using-primary-key-only.aspx
// Modified by Eric Duncan to be an extension.
/// <summary>
/// Deletes an entity from the TSource table via the TKeyType primary key.
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <typeparam name="TPk"></typeparam>
/// <param name="table"></param>
/// <param name="pk"></param>
/// <param name="dc"></param>
public static void DeleteByPk <TSource, TPk>(this Table <TSource> table, TPk pk, DataContext dc)
where TSource : class
{
//Table<TSource> table = dc.GetTable<TSource>();
TableDef tableDef = GetTableDef <TSource>();
dc.ExecuteCommand("DELETE FROM [" + tableDef.TableName + "] WHERE [" + tableDef.PkFieldName + "] = {0}", pk);
}
public void removeAllWaitRecognizer()
{
using (var context = new DataContext(GetSqlConnection()))
{
context.ExecuteCommand("DELETE FROM WaitRecognizer");
context.SubmitChanges();
}
}
void Initialize(DataContext db)
{
db.ExecuteCommand("truncate table TicketSentiments");
// tone multipliers
Table <ToneSentiment> tones = db.GetTable <ToneSentiment>();
_toneSentiment = (from tone in tones select tone).ToArray();
}
public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable <Guid> resourceIds)
{
string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
if (!string.IsNullOrWhiteSpace(paramResourceIds))
{
string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
DataContext.ExecuteCommand(queryString);
}
}
public static void DeleteRemainder(DataContext db)
{
Table <OrganizationSentiment> table = db.GetTable <OrganizationSentiment>();
foreach (KeyValuePair <int, OrganizationSentiment> pair in _organizationSentiments)
{
db.ExecuteCommand(String.Format(@"DELETE FROM [OrganizationSentiments] WHERE [OrganizationID] = {0}", pair.Key)); // DELETE
}
_organizationSentiments.Clear();
}
public override void Delete(IEnumerable <Guid> ids)
{
string paramIds = string.Join(",", ids.Select(x => string.Format("'{0}'", x)));
if (!string.IsNullOrWhiteSpace(paramIds))
{
string query = string.Format(BatchDeleteQuery, paramIds);
DataContext.ExecuteCommand(query);
}
}
/// <summary></summary>
public void Execute()
{
if (_columnMapping.Count == 0)
{
throw new ArgumentException($"請指定複製的欄位");
}
string fromTable = getTableName <From>();
string toTable = getTableName <To>();
if (_conditionMapping.Count == 0)
{
string insertColumn = _columnMapping.Select(kv => $"[{kv.Value}]").JoinBy(", ");
string selectColumn = _columnMapping.Select(kv => $"[{kv.Key}]").JoinBy(", ");
/*新增不存在的資料*/
_context.ExecuteCommand($@"
INSERT {toTable} ( {insertColumn} )
SELECT {selectColumn} FROM {fromTable}
" );
}
else
{
string setColumn = _columnMapping.Select(kv => $"T.[{kv.Value}] = F.[{kv.Key}]").JoinBy(", ");
string condColumn = _conditionMapping.Select(kv => $"T.[{kv.Value}] = F.[{kv.Key}]").JoinBy(" AND ");
/*更新資料*/
_context.ExecuteCommand($@"
UPDATE {toTable} SET {setColumn}
FROM {toTable} T INNER JOIN {fromTable} F ON {condColumn}
" );
string insertColumn = _columnMapping.Select(kv => $"[{kv.Value}]").JoinBy(", ");
string selectColumn = _columnMapping.Select(kv => $"F.[{kv.Key}]").JoinBy(", ");
/*新增不存在的資料*/
_context.ExecuteCommand($@"
INSERT {toTable} ( {insertColumn} )
SELECT {selectColumn} FROM {fromTable} F
WHERE NOT EXISTS ( SELECT 1 FROM {toTable} T WHERE {condColumn} )
" );
}
}
public void DeleteByResourceAndGrantedUserId(Guid resourceId, IEnumerable <Guid> grantedUserId)
{
string paramIds = string.Join(",", grantedUserId.Select(x => string.Format("'{0}'", x)));
if (!string.IsNullOrWhiteSpace(paramIds))
{
string query = string.Format(DeleteByGrantedUserQuery, resourceId, paramIds);
DataContext.ExecuteCommand(query);
}
}
public void DeleteByProjectIds(IEnumerable <Guid> projectIds)
{
string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x)));
if (!string.IsNullOrWhiteSpace(paramProjectIds))
{
string query = string.Format(DeleteByProjectIdsQueryString, paramProjectIds);
DataContext.ExecuteCommand(query);
}
}
public void DeleteImageOfProduct(int id)
{
if (AppCache.FindPhoto(id) != null)
{
AppCache.DeletePhoto(id);
}
using (var dataContext = new DataContext(_connectionString))
{
dataContext.ExecuteCommand("DELETE FROM BigPhoto WHERE product_id={0}", id);
}
}
public int UpdateExpirationTime(IEnumerable <Guid> ids, DateTime time)
{
string paramIds = string.Join(",", ids.Select(x => string.Format("'{0}'", x)));
if (!string.IsNullOrWhiteSpace(paramIds))
{
string query = string.Format(UpdateExpirationTimeQuery, "{0}", paramIds);
return(DataContext.ExecuteCommand(query, time));
}
return(0);
}
private static void ClearTpDB(DataContext tpDatabaseDataContext)
{
tpDatabaseDataContext.ExecuteCommand("delete from Project");
tpDatabaseDataContext.ExecuteCommand("delete from CustomReport");
tpDatabaseDataContext.ExecuteCommand("delete from TpUser");
tpDatabaseDataContext.ExecuteCommand("delete from General");
tpDatabaseDataContext.ExecuteCommand("delete from MessageUid");
tpDatabaseDataContext.ExecuteCommand("delete from PluginProfile");
tpDatabaseDataContext.ExecuteCommand("delete from Revision");
}
public Form1()
{
InitializeComponent();
string connString = "DataSource=db/Pots.db3;";
SQLiteConnection connection = new SQLiteConnection(connString);
DataContext = new DataContext(connection);
DataContext.ExecuteCommand("PRAGMA foreign_keys=ON;");
formEditor = new FormEditor();
diagramView1.OnItemSelect += (obj, index) =>
{
propertyGrid1.SelectedObject = obj;
};
buttonRefresh_Click(null, null);
}
protected void ReCreateDatabaseFromAttributeMapping(DataContext ctx)
{
bool success = false;
bool retry = false;
int retries = 0;
do
{
try
{
using (ctx)
{
CloseAllOpenConnections(ctx);
ctx.Log = new StringWriter();
if (ctx.DatabaseExists())
{
//drop all connections by disabling multi-user (and immediatly abort all current transactions)
ctx.ExecuteCommand("ALTER DATABASE " + _databaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
ctx.DeleteDatabase();
}
try
{
ctx.CreateDatabase();
}
catch (Exception e)
{
Debug.WriteLine(e);
Debug.WriteLine(ctx.Log);
throw;
}
//re-enable multi-user (again)
ctx.ExecuteCommand("ALTER DATABASE " + _databaseName + " SET MULTI_USER");
success = true;
}
}
catch (SqlException e)
{
retry = false;
if (e.Message.IndexOf("was deadlocked on lock resources with another process and has been chosen as the deadlock victim") > 0)
{
retry = true;
retries++;
}
}
} while (success == false && (retry && retries < 3));
}
public void PutMessage(ImmutableEnvelope envelope)
{
var buffer = _streamer.SaveEnvelopeData(envelope);
var now = DateTime.UtcNow;
using (var db = new DataContext(_connectionString))
{
db.ExecuteCommand
(
"exec [dbo].[Enqueue] {0}, {1}, {2}, {3}, {4}",
_queueID,
envelope.EnvelopeId,
envelope.CreatedOnUtc,
envelope.DeliverOnUtc < now ? now : envelope.DeliverOnUtc,
buffer
);
}
}
private void RemoveDb()
{
var config = DependencyResolver.Current.GetService<IConfig>();
var db = new DataContext(config.ConnectionStrings("ConnectionString"));
var textCloseConnectionTestDb = string.Format(@"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", TestDbName);
db.ExecuteCommand(textCloseConnectionTestDb);
var textDropTestDb = string.Format(@"DROP DATABASE [{0}]", TestDbName);
db.ExecuteCommand(textDropTestDb);
}
void DeleteMessage(QueueMessage message)
{
using (var db = new DataContext(_config.ConnectionString))
{
db.ExecuteCommand
(
"exec [dbo].[Delete] {0}",
message.MessageID
);
}
}
void SendToPoisonQueue(QueueMessage message)
{
using (var db = new DataContext(_config.ConnectionString))
{
db.ExecuteCommand
(
"exec [dbo].[Enqueue] {0}, {1}, {2}, {3}, {4}",
_poisonQueueID,
message.EnvelopeID,
message.CreatedOnUtc,
DateTime.UtcNow,
message.Envelope
);
}
}
/// <summary>
/// 创建单个表
/// </summary>
/// <param name="linqTableClass"></param>
/// <param name="db"></param>
private static void CreateTable(Type linqTableClass, DataContext db)
{
var metaTable = db.Mapping.GetTable(linqTableClass);
//反射的方法创建对象
var typeName = "System.Data.Linq.SqlClient.SqlBuilder";
var type = typeof(DataContext).Assembly.GetType(typeName);
//反射调用私有的GetCreateTableCommand 方法。
var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod;
var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable });
var sqlAsString = sql.ToString();
db.ExecuteCommand(sqlAsString);
}
private async void button2_Click(object sender, EventArgs e)
{
DialogResult valasz;
if (cBoxMentes.Checked)
{
valasz = MessageBox.Show("Biztos benne, hogy véglegesen üríti a táblákat?", "Ürítés", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
}
else
{
valasz = MessageBox.Show("Biztos benne, hogy mentés nélkül, véglegesen üríti a táblákat?", "Ürítés", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
}
if (valasz == DialogResult.Yes)
{
using (var teletabyDB = new DataContext(belepes.connectionString))
{
var rendelesT = teletabyDB.GetTable <Rendelés>();
var tetelT = teletabyDB.GetTable <Rendelés_tételek>();
var termekT = teletabyDB.GetTable <Termék>();
var resultRend = (from t in rendelesT
select new MentesRendeles(t.ID, t.idő, t.összeg)).AsEnumerable <MentesRendeles>();
var resultTetel = (from t1 in tetelT
join t2 in termekT on t1.termékID equals t2.ID
select new MentesRendelesTetel(t1.rendelésID, t2.név, t2.mértékegység, t2.ár, t1.megjegyzés)).AsEnumerable <MentesRendelesTetel>();
switch (cbAdatbazis.SelectedIndex)
{
case 0:
dgvDT.DataSource = resultRend;
break;
case 1:
dgvDT.DataSource = resultTetel;
break;
}
if (cBoxMentes.Checked && resultRend.FirstOrDefault() != null && resultTetel.FirstOrDefault() != null)
{
Excel(resultRend, resultTetel);
}
teletabyDB.ExecuteCommand("TRUNCATE TABLE rendelés");
teletabyDB.ExecuteCommand("TRUNCATE TABLE rendelés_tételek");
}
await Visszajelzes(bMentesUrites);
AdatbázisLekér();
}
}
