Example #1
0
 static void ExecWithoutDB(string sql)
 {
     SqlConnection.ClearAllPools();
     using (var connection = new SqlConnection(@"Data Source=(localdb)\ProjectsV12;Integrated Security=True")) {
         connection.Open();
         connection.ExecuteNonQuery(sql);
     }
 }
        public void ExecuteNonQuery()
        {
            string sql = "DECLARE @FizzBuzz VARCHAR(MAX) = @Fizz";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                conn.ExecuteNonQuery(sql, dict.ToDbParameters(conn));
            }
        }
 public void RemoveDoc(CaseDoc doc)
 {
     using (var connection = new SqlConnection(_connectionString))
     {
         connection.Open();
         var prKey1 = new KeyValuePair<string, object>("案件编号", doc.案件编号);
         var prKey2 = new KeyValuePair<string, object>("文件名", doc.FileName);
         string command = string.Format("DELETE FROM [{0}] WHERE {1} = @{1} AND {2}=@{2}", CaseDocTableName,
                                        prKey1.Key, prKey2.Key);
         connection.ExecuteNonQuery(command);
     }
 }
 public void RemoveBookmarkRecord(string bookmarkName)
 {
     using (var connection = new SqlConnection(_connectionString))
     {
         connection.Open();
         string command = string.Format("DELETE FROM [{0}] WHERE {1} = @{1} AND {2} = @{2}",
                                        CaseWorkflowManager.BookmarkTableName,
                                        CaseWorkflowManager.CaseIdColumnName,
                                        CaseWorkflowManager.BookmarkNameColumnName);
         var parameters = new[]
                              {
                                  new SqlParameter("@" + CaseWorkflowManager.CaseIdColumnName, _caseId),
                                  new SqlParameter("@" + CaseWorkflowManager.BookmarkNameColumnName, bookmarkName)
                              };
         connection.ExecuteNonQuery(command, parameters);
     }
 }
        public void TestErrorSuccess()
        {
            // Arrange
            var guid = Guid.NewGuid();
            var logger = new OneHydraLogger("MyTestLogger"); var helper = new ConfigManagerHelper();
            var connString = helper.GetConnectionString("OneHydraLog");
            var query = string.Format(@"
                SELECT [Id], [Date], [Thread], [Level], [Logger], [Message], [Exception], [MachineName] 
                FROM [dbo].[OneSearchLog] 
                WHERE Message LIKE {0}", guid.ForSql());
            // Act
            logger.Error(guid.ToString(), new Exception("Test exception"));
            // Clean up
            dynamic logRow;
            using (var conn = new SqlConnection(connString))
            {
                logRow = conn.GetObject(query,
                    r =>new 
                    {
                        Id = r.GetInt32(0), 
                        Date = r.GetDateTime(1), 
                        Thread = r.GetString(2), 
                        Level = r.GetString(3), 
                        Logger = r.GetString(4), 
                        Message = r.GetString(5),
                        Exception = r.GetString(6), 
                        MachineName = r.GetString(7)
                    });
                conn.ExecuteNonQuery(string.Format("DELETE FROM [dbo].[OneSearchLog] WHERE Message LIKE {0} ", guid.ForSql()));
            }
            // Assert
            Assert.IsNotNull(logRow);
            Assert.AreEqual(guid.ToString(), logRow.Message);
            Assert.AreEqual("Test exception\r\n", logRow.Exception);
            Assert.AreEqual(Environment.MachineName, logRow.MachineName);
            Assert.AreEqual("ERROR", logRow.Level);
            Assert.AreEqual("MyTestLogger", logRow.Logger);

        }
        public void ExecuteEntities()
        {
            var date = new DateTime(1981, 04, 13);
            Guid guid = Guid.NewGuid();

            var entity = new EntityWithAllColumn();
            entity.BitIntColumn = 1;
            entity.BinaryColumn = new byte[] {1};
            entity.BitColumn = true;
            entity.CharColumn = "z";
            entity.DateColumn = date;
            entity.DateTimeColumn = date;
            entity.DateTime2Column = date;
            entity.DateTimeOffsetColumn = date;
            entity.DecimalColumn = 1.25m;
            entity.FloatColumn = 1.25f;
            entity.ImageColumn = new byte[] {1};
            entity.IntColumn = 1;
            entity.MoneyColumn = 1.25m;
            entity.NCharColumn = "z";
            entity.NTextColumn = "z";
            entity.NumericColumn = 1;
            entity.NVarcharColumn = "z";
            entity.NVarcharMaxColumn = "z";
            entity.RealColumn = 1.25f;
            entity.SmallDateTimeColumn = date;
            entity.SmallIntColumn = null;
            entity.SmallMoneyColumn = 1.25m;
            entity.TextColumn = "z";
            entity.TimeColumn = date.TimeOfDay;
            entity.TimestampColumn = new byte[] {1};
            entity.TinyIntColumn = 1;
            entity.UniqueIdentifierColumn = guid;
            entity.VarBinaryColumn = new byte[] {1};
            entity.VarBinaryMaxColumn = new byte[] {1};
            entity.VarcharColumn = "z";
            entity.VarcharMaxColumn = "z";
            entity.XmlColumn = "z";

            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                conn.ExecuteNonQuery("TRUNCATE TABLE EntityWithAllColumns");

                var dt = new DataTable();
                using (var copy = new SqlBulkCopy(conn))
                {
                    foreach (PropertyInfo property in entity.GetType().GetProperties())
                    {
                        if (property.Name == "SmallIntColumn")
                        {
                            dt.Columns.Add(property.Name, typeof (short));
                        }
                        else
                        {
                            dt.Columns.Add(property.Name, property.PropertyType);
                        }

                        copy.ColumnMappings.Add(property.Name, property.Name);
                    }

                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    foreach (PropertyInfo property in entity.GetType().GetProperties())
                    {
                        object value = property.GetValue(entity, null);
                        dr[property.Name] = value == null ? DBNull.Value : value;
                    }

                    copy.DestinationTableName = "EntityWithAllColumns";

                    copy.WriteToServer(dt);
                }

                // Unit Test
                EntityWithAllColumn result = conn.ExecuteEntities<EntityWithAllColumn>("SELECT TOP 1 * FROM EntityWithAllColumns").ToList()[0];
                Assert.AreEqual(1, result.BitIntColumn);
                Assert.AreEqual(1, result.BinaryColumn[0]);
                Assert.AreEqual(true, result.BitColumn);
                Assert.AreEqual("z", result.CharColumn);
                Assert.AreEqual(date, result.DateColumn);
                Assert.AreEqual(date, result.DateTimeColumn);
                Assert.AreEqual(date, result.DateTime2Column);
                Assert.AreEqual(date, result.DateTimeOffsetColumn);
                Assert.AreEqual(1.25m, result.DecimalColumn);
                Assert.AreEqual(1.25f, result.FloatColumn);
                Assert.AreEqual(1, result.ImageColumn[0]);
                Assert.AreEqual(1, result.IntColumn);
                Assert.AreEqual(1.25m, result.MoneyColumn);
                Assert.AreEqual("z", result.NCharColumn);
                Assert.AreEqual("z", result.NTextColumn);
                Assert.AreEqual(1, result.NumericColumn);
                Assert.AreEqual("z", result.NVarcharColumn);
                Assert.AreEqual("z", result.NVarcharMaxColumn);
                Assert.AreEqual(1.25f, result.RealColumn);
                Assert.AreEqual(date, result.SmallDateTimeColumn);
                Assert.AreEqual(null, result.SmallIntColumn);
                Assert.AreEqual(1.25m, result.SmallMoneyColumn);
                Assert.AreEqual("z", result.TextColumn);
                Assert.AreEqual(date.TimeOfDay, result.TimeColumn);
                Assert.AreEqual(1, result.TimestampColumn[0]);
                Assert.AreEqual((Byte) 1, result.TinyIntColumn);
                Assert.AreEqual(guid, result.UniqueIdentifierColumn);
                Assert.AreEqual(1, result.VarBinaryColumn[0]);
                Assert.AreEqual(1, result.VarBinaryMaxColumn[0]);
                Assert.AreEqual("z", result.VarcharColumn);
                Assert.AreEqual("z", result.VarcharMaxColumn);
                Assert.AreEqual("z", result.XmlColumn);
            }
        }
        public bool DeleteActivity(int id)
        {
            var sql = "DELETE FROM ActivityActivity WHERE ParentActivityId = " + id + " or ActivityId = " + id
                      + "; DELETE FROM RoleActivity WHERE ActivityId = " + id
                      + "; DELETE FROM Activity WHERE Id = " + id;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using(var transaction = conn.BeginTransaction())
                {
                    conn.ExecuteNonQuery(sql, null, CommandType.Text, transaction);
                    transaction.Commit();
                }
            }

            return true;
        }
        public bool DeleteUserAuthorisation(int id)
        {
            var sql = "DELETE FROM UserRole WHERE Id = " + id
                      + "; DELETE FROM UserAuthorisation WHERE Id = " + id;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (var transaction = conn.BeginTransaction())
                {
                    conn.ExecuteNonQuery(sql, null, CommandType.Text, transaction);
                    transaction.Commit();
                }
            }

            return true;
        }