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; }