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 ); } }
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); }
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 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("Удаление не было произведено", "Ошибка!"); } } }
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); }
/* * 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 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(""); }
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()); }
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(); } }
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 void removeAllWaitRecognizer() { using (var context = new DataContext(GetSqlConnection())) { context.ExecuteCommand("DELETE FROM WaitRecognizer"); context.SubmitChanges(); } }
// 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); }
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"); } }
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 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(); }
/// <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 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 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); } }
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 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 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); }
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); } }
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 ); } }
/// <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 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 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(); } }