Пример #1
2
 void DeleteMessage(QueueMessage message)
 {
     using (var db = new DataContext(_config.ConnectionString))
     {
         db.ExecuteCommand
         (
             "exec [dbo].[Delete] {0}",
             message.MessageID
         );
     }
 }
Пример #2
1
 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
         );
     }
 }
Пример #3
0
        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);
        }
Пример #6
0
 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);
 }
Пример #7
0
        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}')");
                        }
                    }
                }
            });
        }
Пример #8
0
        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;
            }
        }
Пример #9
0
 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);
            }
        }
Пример #12
0
        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));
        }
Пример #13
0
        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);
     }
 }
Пример #15
0
 public void DeleteImageOfProduct(int id)
 {
     using (var dataContext = new DataContext(_connectionString))
     {
         dataContext.ExecuteCommand("DELETE FROM Photo WHERE product_id={0}", id);
     }
 }
Пример #16
0
        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("");
        }
Пример #17
0
        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());
        }
Пример #18
0
        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();
    }
Пример #20
0
        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();
            }
        }
Пример #21
0
        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);
            }
        }
Пример #22
0
 public void removeAllWaitRecognizer()
 {
     using (var context = new DataContext(GetSqlConnection()))
     {
         context.ExecuteCommand("DELETE FROM WaitRecognizer");
         context.SubmitChanges();
     }
 }
Пример #23
0
        // 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();
        }
Пример #27
0
        /// <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);
            }
        }
Пример #29
0
        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);
            }
        }
Пример #31
0
        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);
            }
        }
Пример #32
0
        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);
        }
Пример #33
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");
		}
Пример #35
0
        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);
        }
Пример #36
0
        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));
        }
Пример #37
0
        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
                );
            }
        }
Пример #38
0
        /// <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);
        }
Пример #40
-1
        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();
            }
        }