public void MultiRSSqlCE() { if (File.Exists("Test.sdf")) File.Delete("Test.sdf"); var cnnStr = "Data Source = Test.sdf;"; var engine = new SqlCeEngine(cnnStr); engine.CreateDatabase(); using (var cnn = new SqlCeConnection(cnnStr)) { cnn.Open(); cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)"); cnn.Execute("create table Authors (ID int, Name nvarchar(50))"); cnn.Execute("insert Posts values (1,'title','body',1)"); cnn.Execute("insert Posts values(2,'title2','body2',null)"); cnn.Execute("insert Authors values(1,'sam')"); var data = cnn.Query<PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return post; }).ToList(); var firstPost = data.First(); firstPost.Title.IsEqualTo("title"); firstPost.Author.Name.IsEqualTo("sam"); data[1].Author.IsNull(); cnn.Close(); } }
static SqlCETestSuite() { if (File.Exists(FileName)) { File.Delete(FileName); } var engine = new SqlCeEngine(ConnectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(ConnectionString)) { connection.Open(); connection.Execute(@"CREATE TABLE Stuff (TheId int IDENTITY(1,1) not null, Name nvarchar(100) not null, Created DateTime null) "); connection.Execute(@"CREATE TABLE People (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) "); connection.Execute(@"CREATE TABLE Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE Results (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, [Order] int not null) "); connection.Execute(@"CREATE TABLE ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE ObjectY (ObjectYId int not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE ObjectZ (Id int not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE GenericType (Id nvarchar(100) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE NullableDates (Id int IDENTITY(1,1) not null, DateValue DateTime null) "); } Console.WriteLine("Created database"); }
public void MultiRSSqlCE() { if (File.Exists("Test.DB.sdf")) { File.Delete("Test.DB.sdf"); } const string cnnStr = "Data Source = Test.DB.sdf;"; var engine = new SqlCeEngine(cnnStr); engine.CreateDatabase(); using (var cnn = new SqlCeConnection(cnnStr)) { cnn.Open(); cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)"); cnn.Execute("create table Authors (ID int, Name nvarchar(50))"); cnn.Execute("insert Posts values (1,'title','body',1)"); cnn.Execute("insert Posts values(2,'title2','body2',null)"); cnn.Execute("insert Authors values(1,'sam')"); var data = cnn.Query <PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return(post); }).ToList(); var firstPost = data.First(); firstPost.Title.IsEqualTo("title"); firstPost.Author.Name.IsEqualTo("sam"); data[1].Author.IsNull(); } }
public void Append(IEnumerable<LineCoverage> coverage) { string[] testMethods = coverage.Select(x => x.TestPath).Distinct().ToArray(); if (testMethods.Length == 0) return; using (var connection = new SqlCeConnection(GetConnectionString())) { connection.Open(); string delete; if (testMethods.Length == 1) { delete = "DELETE FROM Coverage where TestPath = @testMethod"; connection.Execute(delete, new { testMethod = testMethods[0] }); } else { delete = "DELETE FROM Coverage where TestPath in @testMethods"; connection.Execute(delete, new { testMethods }); } InsertLineCoverage(connection, coverage.ToArray()); } }
public HttpResponseMessage Put(int matchId, int winningracerid) { using (var connection = new SqlCeConnection(Settings.Cnn)) { connection.Open(); if (winningracerid < 1) { connection.Execute("update match set winningracerid=null, modified=null where matchid=@matchId", new { matchId }); return new HttpResponseMessage(HttpStatusCode.OK); } connection.Execute("update match set winningracerid=@winningracerid, modified=GETDATE() where matchid=@matchId", new { matchId, winningracerid }); Match match = connection.Query<Match>("select * from match where matchId=@matchId", new {matchId}).First(); var sql = match.NextWinningMatchSlot == 1 ? "update match set racer1id=@winningracerid where matchid=@NextWinningMatchId" : "update match set racer2id=@winningracerid where matchid=@NextWinningMatchId"; connection.Execute(sql, new { match.NextWinningMatchId, winningracerid }); return new HttpResponseMessage(HttpStatusCode.OK); } }
public void MaterialsTruncateTable() { using ( var conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString) ) { conn.Open(); conn.Execute("DELETE FROM Material;"); conn.Execute("ALTER TABLE Material ALTER COLUMN id IDENTITY (1,1);"); } }
public void SetSettings(Settings settings) { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); conn.Execute("DELETE FROM Settings"); conn.Execute( "INSERT INTO Settings (InvoiceOwnerName, InvoiceOwnerAddress, InvoiceOwnerCity, InvoiceOwnerPostalCode, InvoiceOwnerFiscalCode, InvoiceOwnerVatCode, MaxInvoiceValue) VALUES (@InvoiceOwnerName, @InvoiceOwnerAddress, @InvoiceOwnerCity, @InvoiceOwnerPostalCode, @InvoiceOwnerFiscalCode, @InvoiceOwnerVatCode, @MaxInvoiceValue)", new { settings.InvoiceOwnerName, settings.InvoiceOwnerAddress, settings.InvoiceOwnerCity, settings.InvoiceOwnerPostalCode, settings.InvoiceOwnerFiscalCode, settings.InvoiceOwnerVatCode, settings.MaxInvoiceValue }); } }
public bool AssignBracketsByStartSlot() { using (var connection = new SqlCeConnection(Settings.Cnn)) { connection.Open(); connection.Execute("update match set racer1id=null, racer2id=null, winningracerid=null"); var racers = connection.Query<Racer>("select * from racer order by startslot").ToList(); var matches1 = connection.Query<Match>("select * from match where roundid=1 order by matchid").ToList(); var racerIndex = 0; foreach (var match in matches1) { if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 1)) { var racer1Id = racers[racerIndex].RacerId; match.Racer1Id = racer1Id; connection.Execute("update match set racer1id=@racer1Id where matchid=@MatchId", new { racer1Id, match.MatchId }); racerIndex++; } if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 2)) { var racer2Id = racers[racerIndex].RacerId; match.Racer2Id = racer2Id; connection.Execute("update match set racer2id=@racer2Id where matchid=@MatchId", new { racer2Id, match.MatchId }); racerIndex++; } } var matches2 = connection.Query<Match>("select * from match where roundid=2 order by matchid").ToList(); foreach (var match in matches2) { if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 1)) { var racer1Id = racers[racerIndex].RacerId; match.Racer1Id = racer1Id; connection.Execute("update match set racer1id=@racer1Id where matchid=@MatchId", new { racer1Id, match.MatchId }); racerIndex++; } } } return true; }
public static int DeleteUser(int userId) { using (var connection = new SqlCeConnection($"Data Source=\"{AppDomain.CurrentDomain.BaseDirectory}Data\\UserData.sdf\"; Password=\"test_password\"")) { return(connection.Execute("DELETE FROM TestUser WHERE TestUserId = @TestUserId", new { TestUserId = userId })); } }
public void UpdateProductCategory(ProductCategory productCategory) { var param = new { ProductID = productCategory.ProductID, CategoryID = productCategory.CategoryID, IsFeaturedProduct = productCategory.IsFeaturedProduct, DisplayOrder = productCategory.DisplayOrder, ChangeUser = "******", ChangeDate = DateTime.Now }; using (SqlCeConnection cn = Connection2) { try { var i = cn.Execute(@"Update ProductCategoryMapping set pcm_cat_id = @CategoryID ,pcm_is_featured_product = @IsFeaturedProduct ,pcm_display_order = @DisplayOrder ,pcm_change_date = @ChangeDate , pcm_change_user = @ChangeUser where pcm_prd_id = @ProductID", param); } catch { } } }
public async Task <HttpResponseMessage> Post(JObject value) { dynamic data = value; IEnumerable <int> result; using (var connection = new SqlCeConnection(_connString)) { await connection.OpenAsync(); connection.Execute( "insert into Tasks (Title, Description, CreatedDate) values (@title, @description, @createdDate);", new { title = (string)data.title, description = (string)data.description, createdDate = DateTime.Parse((string)data.createdDate) } ); result = await connection.QueryAsync <int>("select max(Id) as id from Tasks;"); } int id = result.First(); data.id = id; var response = Request.CreateResponse(HttpStatusCode.Created, (JObject)data); response.Headers.Location = new Uri(Url.Link("DefaultApi", new { controller = "Tasks", id = id })); return(response); }
public bool SelfInvoiceDelete(SelfInvoicesMaster selfInvoice) { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); var selfInvoices = conn.Query <SelfInvoices>("SELECT * FROM SelfInvoices WHERE InvoiceGroupId = @InvoiceGroupId AND CashFlowId IS NULL", new { selfInvoice.InvoiceGroupId }); var totInCashFlow = selfInvoices != null?selfInvoices.Count() : 0; if (totInCashFlow == 0) { return(false); } conn.Execute("DELETE FROM SelfInvoices WHERE InvoiceGroupId = @InvoiceGroupId", new { selfInvoice.InvoiceGroupId }); } return(true); }
public static KeyValuePair <string, string> UpdateKeyValue(string key, string value) { try { using (var cn = new SqlCeConnection(ConnString)) { cn.Open(); var p = new DynamicParameters(); p.Add("@key", key); p.Add("@value", value); var res = cn.Execute("UPDATE KeysTable SET ValueString=@Value WHERE KeyName=@key", p); if (res == 1) { return(new KeyValuePair <string, string>(key, value)); } else { throw new Exception(""); } } } catch (Exception) { throw; } }
public bool AssignRandomStartSlots() { using (var connection = new SqlCeConnection(Settings.Cnn)) { connection.Open(); connection.Execute("update racer set startslot=-1"); var numbers = Enumerable.Range(1, 100).ToList(); var racers = connection.Query<Racer>("select * from racer order by racerid").ToList(); var rnd = new Random(); foreach (var racer in racers) { var x = rnd.Next(0, numbers.Count); racer.StartSlot = numbers[x]; numbers.RemoveAt(x); connection.Execute("update racer set startslot=@StartSlot where racerid=@RacerId", new { racer.StartSlot, racer.RacerId }); } } return true; }
public void Update(Product item) { var param = new { ID = item.ID, Name = item.Name, Description = item.Description, Price = item.Price, ShortDescription = item.ShortDescription, SKU = item.SKU, ManufacturePartNo = item.ManufacturePartNo, StockQuantity = item.StockQuantity, ChangeUser = Environment.UserName, ChangeDate = DateTime.Now }; using (SqlCeConnection cn = Connection2) { try { var i = cn.Execute(@"update products set prd_name = @Name, prd_description = @Description, prd_price = @Price, prd_short_description = @ShortDescription, prd_sku = @SKU, prd_manufacturepart_no = @ManufacturePartNo, prd_stock_quatity = @StockQuantity, prd_change_user = @ChangeUser, prd_change_date = @Changedate where prd_id = @ID", param); } catch { } } }
public bool MaterialDelete(Material material) { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); var ret = conn.Query <int>("SELECT Count(*) FROM SelfInvoices WHERE MaterialId = @Id", new { material.Id }); var totUsedMaterials = ret != null?ret.First() : 0; if (totUsedMaterials > 0) { return(false); } conn.Execute("DELETE FROM Materials WHERE Id = @Id", new { material.Id }); } return(true); }
private void SetSameDayIndexes(SqlCeConnection cn) { var days = cn.Query <MultiEntryDay>( @"SELECT DATEPART(yy, [Date]) AS [Year], DATEPART(m, [Date]) AS [Month], DATEPART(d, [Date]) AS [Day], COUNT(1) AS [Count] FROM [JournalEntry] GROUP BY DATEPART(yy, [Date]), DATEPART(m, [Date]), DATEPART(d, [Date]) HAVING COUNT(1)>1" , null); foreach (var day in days) { var entries = cn.Query <int>( @"SELECT [ID] FROM [JournalEntry] WHERE DATEPART(yy, [Date])=@year AND DATEPART(m, [Date])=@month AND DATEPART(d, [Date])=@day ORDER BY [Date]" , new { year = day.Year, month = day.Month, day = day.Day }); int index = 0; foreach (var entryID in entries) { index++; cn.Execute("UPDATE [JournalEntry] SET [SameDayIndex]=@index WHERE [ID]=@id", new { index = index, id = entryID }); } } }
public static MessageItem UpsertMessage(int id, string text, string style, bool isActive, bool onHomePage, bool onLoginPage) { try { using (var cn = new SqlCeConnection(ConnString)) { cn.Open(); var p = new DynamicParameters(); p.Add("@messageId", id); p.Add("@messagevalue", text); p.Add("@style", style); p.Add("@isActive", isActive); p.Add("@onLoginPage", onLoginPage); p.Add("@onHomePage", onHomePage); string lastPart = ""; string firstquery = ""; string secondquery = ""; if (id > 0) { // edit firstquery = "UPDATE Messages SET MessageValue=@messagevalue, style=@style, isActive=@isActive, onLoginPage=@onloginpage, onhomepage=@onhomepage WHERE MessageId=@MessageId; "; lastPart = "@messageid"; } else { // insert firstquery = "INSERT Messages(MessageValue, Style, IsActive, OnLoginPage, OnHomePage) VALUES (@MessageValue, @Style, @IsActive, @OnLoginPage, @OnHomePage);"; lastPart = "@@IDENTITY"; } secondquery = "SELECT MessageId, MessageValue, OnLoginPage, OnHomePage, IsActive, Style FROM Messages WHERE MessageId=" + lastPart; var exec = cn.Execute(firstquery, p); var res = cn.Query(secondquery, p); if (res == null) { return(null); } return(res.Select(x => new MessageItem { Id = x.MessageId, Value = x.MessageValue, OnLoginPage = x.OnLoginPage, OnHomePage = x.OnHomePage, IsActive = x.IsActive, Style = x.Style }).FirstOrDefault()); } } catch (Exception ex) { throw ex; } }
static void Main(string[] args) { var oldFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoSh.sdf"); var newFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoShNew.sdf"); using (var oldConn = new SqlCeConnection("Data Source=" + oldFile)) using (var newConn = new SqlCeConnection("Data Source=" + newFile)) { oldConn.Open(); newConn.Open(); newConn.Execute("delete from timecard"); newConn.Execute("delete from bartender"); var countBefore = newConn.Query <int>("select count(id) from bartender").Single(); var bts = oldConn.Query("select * from bartender"); newConn.Execute("insert into bartender(id, name, hire, fire, pin) values(@Id,@Name,@Hire,@Fire,@Pin)", bts); var countAfter = newConn.Query <int>("select count(id) from bartender").Single(); bool worked = countAfter > countBefore; var last = newConn.Query <DateTime>("select max(ClockOut) from timecard").Single(); var newTimecards = oldConn.Query <Timecard>("Select * from timecard where ClockOut > @LastClockOut", new { LastClockOut = last } ); newConn.Execute("insert into timecard(Id, BartenderId, ClockIn, ClockOut) values (@Id, @BartenderId, @ClockIn, @ClockOut)", newTimecards); var result = newConn.Query("Select * from timecard where ClockOut > @LastClockOut", new { LastClockOut = last } ); foreach (var item in result) { Console.WriteLine("{0} {1} {2}", item.BartenderId, item.ClockIn, item.ClockOut); } } Console.Read(); }
private static void Setup() { var projLoc = Assembly.GetAssembly(typeof(Program)).Location; var projFolder = Path.GetDirectoryName(projLoc); if (File.Exists(projFolder + "\\Test.sdf")) { File.Delete(projFolder + "\\Test.sdf"); } var connectionString = "Data Source = " + projFolder + "\\Test.sdf;"; var engine = new SqlCeEngine(connectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(connectionString)) { connection.Open(); connection.Execute(@" create table Stuff (TheId int IDENTITY(1,1) not null, Name nvarchar(100) not null, Created DateTime null) "); connection.Execute(@" create table People (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) "); connection.Execute(@" create table Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); connection.Execute(@" create table Results (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, [Order] int not null) "); connection.Execute(@" create table ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) "); connection.Execute(@" create table ObjectY (ObjectYId int not null, Name nvarchar(100) not null) "); } Console.WriteLine("Created database"); }
private void insertInternal(string key, string value, int count, bool isDistinct, SqlCeTransaction trans, SqlCeConnection db) { var info = new { Date = DateTime.UtcNow, Key = key, Value = value, Count = count }; // removal of similar item if (isDistinct && count != 1) { db.Execute("Delete From \"" + TableName + "\" Where [Key] = @Key And [Value] = @Value", info, trans); } // insert item db.Execute("INSERT INTO \"" + TableName + "\" ([Date], [Key], [Value]) values (@Date, @Key, @Value)", info, trans); // removal of history items if (count > 0) { db.Execute("Delete from \"" + TableName + "\" Where [Id] in (Select [Id] FROM \"" + TableName + "\" Where [Key] = @Key Order by [Key], [Date] DESC OFFSET @Count ROWS FETCH NEXT 10000 ROWS ONLY)", info, trans); } }
private static void Setup() { var projLoc = Assembly.GetAssembly(typeof(Program)).Location; var projFolder = Path.GetDirectoryName(projLoc); if (File.Exists(projFolder + "\\Test.sdf")) File.Delete(projFolder + "\\Test.sdf"); var connectionString = "Data Source = " + projFolder + "\\Test.sdf;"; var engine = new SqlCeEngine(connectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(connectionString)) { connection.Open(); connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) "); connection.Execute(@" create table Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); } Console.WriteLine("Created database"); }
private void CreatePost(string body) { dbConnection.Execute("insert Posts (CreateDate, Author, Body) values (@CreateDate, @Author, @Body)", new { CreateDate = DateTime.UtcNow, Author = "Spec Easy", Body = body }, transaction: transaction); }
public static UserItem UpsertUser(int id, string name, string rights, string password, bool isActive) { try { using (var cn = new SqlCeConnection(ConnString)) { cn.Open(); var p = new DynamicParameters(); p.Add("@userId", id); p.Add("@name", name); p.Add("@rights", rights); p.Add("@password", password); p.Add("@isActive", isActive); string lastPart = ""; string firstquery = ""; string secondquery = ""; if (id > 0) { // edit firstquery = "UPDATE Users SET UserName=@name, Password=@password, Rights=@rights, isActive=@isActive WHERE UserId=@UserId; "; lastPart = "@UserId"; } else { // insert firstquery = "INSERT USERS(UserName, Rights, Password, IsActive) VALUES (@name, @rights, @password, @isActive);"; lastPart = "@@IDENTITY"; } secondquery = "SELECT UserId, UserName, Password, Rights, IsActive FROM Users WHERE UserId=" + lastPart; var exec = cn.Execute(firstquery, p); var res = cn.Query(secondquery, p); if (res == null) { return(null); } return(res.Select(x => new UserItem { Id = x.UserId, Name = x.UserName, Password = x.Password, Rights = x.Rights, IsActive = x.IsActive }).FirstOrDefault()); } } catch (Exception ex) { throw ex; } }
static void Main(string[] args) { var oldFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoSh.sdf"); var newFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoShNew.sdf"); using(var oldConn = new SqlCeConnection("Data Source=" + oldFile)) using(var newConn = new SqlCeConnection("Data Source=" + newFile)) { oldConn.Open(); newConn.Open(); newConn.Execute("delete from timecard"); newConn.Execute("delete from bartender"); var countBefore = newConn.Query<int>("select count(id) from bartender").Single(); var bts = oldConn.Query("select * from bartender"); newConn.Execute("insert into bartender(id, name, hire, fire, pin) values(@Id,@Name,@Hire,@Fire,@Pin)", bts); var countAfter = newConn.Query<int>("select count(id) from bartender").Single(); bool worked = countAfter > countBefore; var last = newConn.Query<DateTime>("select max(ClockOut) from timecard").Single(); var newTimecards = oldConn.Query<Timecard>("Select * from timecard where ClockOut > @LastClockOut", new { LastClockOut = last } ); newConn.Execute("insert into timecard(Id, BartenderId, ClockIn, ClockOut) values (@Id, @BartenderId, @ClockIn, @ClockOut)", newTimecards); var result = newConn.Query("Select * from timecard where ClockOut > @LastClockOut", new { LastClockOut = last } ); foreach(var item in result) Console.WriteLine("{0} {1} {2}", item.BartenderId, item.ClockIn, item.ClockOut); } Console.Read(); }
public void CashFlowAdd(CashFlow cashFlow) { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); conn.Execute( "INSERT INTO CashFlow (Cash, Description, FlowDate, CashFlowType) VALUES (@Cash, @Description, @FlowDate, @CashFlowType)", new { cashFlow.Cash, cashFlow.Description, cashFlow.FlowDate, cashFlow.CashFlowType }); } }
public void AddCashFlow() { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); var res = conn.Execute( "INSERT INTO CashFlow (Cash, Description, FlowDate) VALUES (@Cash, @Description, @FlowDate)", new { Cash = 50000, Description = "Prelievo pre cassa", FlowDate = DateTime.Now }); } }
public static void CreateDatabaseAndTables() { using (var engine = new SqlCeEngine($"Data Source=\"{AppDomain.CurrentDomain.BaseDirectory}Data\\UserData.sdf\"; Password=\"test_password\"")) { if (!engine.Verify()) { engine.CreateDatabase(); using (var connection = new SqlCeConnection($"Data Source=\"{AppDomain.CurrentDomain.BaseDirectory}Data\\UserData.sdf\"; Password=\"test_password\"")) { connection.Execute(@"REMOVE TABLE TestUser;"); connection.Execute(@"CREATE TABLE User( [UserId] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL, [FirstName] [NVARCHAR](256) NOT NULL, [LastName] [NVARCHAR](256) NOT NULL, [EmailAddress] [NVARCHAR](256) NOT NULL, [CreatedDate] [DATETIME] NOT NULL DEFAULT(GETDATE()), [ModifiedDate] [DATETIME] NOT NULL DEFAULT(GETDATE()));" ); } } } }
private static void Setup() { var projLoc = Assembly.GetAssembly(typeof(Program)).Location; var projFolder = Path.GetDirectoryName(projLoc); if (File.Exists(projFolder + "\\Test.sdf")) { File.Delete(projFolder + "\\Test.sdf"); } var connectionString = "Data Source = " + projFolder + "\\Test.sdf;"; var engine = new SqlCeEngine(connectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(connectionString)) { connection.Open(); connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null) "); connection.Execute(@" create table Car (CarId int IDENTITY(1,1) not null, Make nvarchar(100) not null, Model nvarchar(100) not null) "); } Console.WriteLine("Created database"); }
public void AppendByDocumentPath(string documentPath, IEnumerable<LineCoverage> coverage) { using (var connection = new SqlCeConnection(GetConnectionString())) { connection.Open(); const string delete = "DELETE FROM Coverage where DocumentPath=@documentPath or TestDocumentPath=@documentPath"; connection.Execute(delete, new { documentPath }); InsertLineCoverage(connection, coverage.ToArray()); } }
static SqlCETestSuite() { SqlDatabase.CacheQueries = false; ResetDapperTypes(); if (!File.Exists(FileName)) { try { var engine = new SqlCeEngine(ConnectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(ConnectionString)) { connection.Open(); var line = string.Empty; var commandText = string.Empty; var file = new StreamReader(".\\Scripts\\sqlceawlite.sql"); while ((line = file.ReadLine()) != null) { if (line.Equals("GO", StringComparison.OrdinalIgnoreCase)) { connection.Execute(commandText); commandText = string.Empty; } else { commandText += "\r\n" + line; } } } } catch (Exception) { throw; } } try { using (var connection = new SqlCeConnection(ConnectionString)) { connection.Execute("delete from [Person]"); } } catch (SqlCeException) { _skip = true; } }
public static User AddUpdateUser(User user) { using (var connection = new SqlCeConnection($"Data Source=\"{AppDomain.CurrentDomain.BaseDirectory}Data\\UserData.sdf\"; Password=\"test_password\"")) { if (user.TestUserId == 0) { connection.Execute(@"INSERT INTO TestUser ( [FirstName] ,[LastName] ,[EmailAddress] ) VALUES ( @FirstName ,@LastName ,@EmailAddress )" , new { user.TestUserId, user.FirstName, user.LastName, user.EmailAddress }); user.TestUserId = connection.ExecuteScalar <int>("SELECT MAX([TestUserId]) FROM TestUser"); } else { connection.Execute(@"UPDATE TestUser SET [FirstName] = @FirstName ,[LastName] = @LastName ,[EmailAddress] = @EmailAddress ,[ModifiedDate] = GETDATE() WHERE [TestUserId] = @TestUserId" , new { user.TestUserId, user.FirstName, user.LastName, user.EmailAddress }); } return(GetUser(user.TestUserId)); } }
public void RunBeforeAnyTests() { string connectionString = ConfigurationManager.ConnectionStrings["__DefaultSqlCe"].ConnectionString; string[] connectionParts = connectionString.Split(';'); string file = connectionParts .ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1]) .Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase)) .Select(k => k.Value).Single(); if (File.Exists(file)) { File.Delete(file); } using (SqlCeEngine ce = new SqlCeEngine(connectionString)) { ce.CreateDatabase(); } Container = new Castle.Windsor.WindsorContainer(); DapperConfiguration .Use() .UseClassMapper(typeof(AutoClassMapper <>)) .UseContainer <ContainerForWindsor>(cfg => cfg.UseExisting(Container)) .UseSqlDialect(new SqlCeDialect()) .WithDefaultConnectionStringNamed("__DefaultSqlCe") .FromAssembly("Dapper.Extensions.Linq.Test.Entities") .FromAssembly("Dapper.Extensions.Linq.Test.Maps") .Build(); _connection = new SqlCeConnection(connectionString); var files = new List <string> { ReadScriptFile("CreateAnimalTable"), ReadScriptFile("CreateFooTable"), ReadScriptFile("CreateMultikeyTable"), ReadScriptFile("CreatePersonTable"), ReadScriptFile("CreateCarTable"), ReadScriptFile("CreatePhoneTable") }; foreach (var setupFile in files) { _connection.Execute(setupFile); } }
public void MaterialAdd(Material material) { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); conn.Execute( "INSERT INTO Materials (Description, Price, MeasureId) VALUES (@Description, @Price, @MeasureId)", new { material.Description, material.Price, material.MeasureId }); } }
public void MaterialUpdate(Material material) { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); conn.Execute( "UPDATE Materials SET Description = @Description, Price = @Price, MeasureId = @MeasureId WHERE Id = @Id", new { material.Description, material.Price, material.MeasureId, material.Id }); } }
public void RunBeforeAnyTests() { string connectionString = ConfigurationManager.ConnectionStrings["__DefaultSqlCe"].ConnectionString; string[] connectionParts = connectionString.Split(';'); string file = connectionParts .ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1]) .Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase)) .Select(k => k.Value).Single(); if (File.Exists(file)) File.Delete(file); using (SqlCeEngine ce = new SqlCeEngine(connectionString)) { ce.CreateDatabase(); } Container = new Castle.Windsor.WindsorContainer(); DapperConfiguration .Use() .UseClassMapper(typeof(AutoClassMapper<>)) .UseContainer<ContainerForWindsor>(cfg => cfg.UseExisting(Container)) .UseSqlDialect(new SqlCeDialect()) .WithDefaultConnectionStringNamed("__DefaultSqlCe") .FromAssembly("Dapper.Extensions.Linq.Test.Entities") .FromAssembly("Dapper.Extensions.Linq.Test.Maps") .Build(); _connection = new SqlCeConnection(connectionString); var files = new List<string> { ReadScriptFile("CreateAnimalTable"), ReadScriptFile("CreateFooTable"), ReadScriptFile("CreateMultikeyTable"), ReadScriptFile("CreatePersonTable"), ReadScriptFile("CreateCarTable"), ReadScriptFile("CreatePhoneTable") }; foreach (var setupFile in files) { _connection.Execute(setupFile); } }
public void MaterialsAddSampleData() { using (var conn = new SqlCeConnection( ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString)) { conn.Open(); for (var i = 0; i < 25; i++) { var res = conn.Execute(@"INSERT INTO Material(name, price) VALUES (@name, @price);", new { name = "prova " + i, price = 1800 }); } } }
static SqlCETestSuite() { Environment.SetEnvironmentVariable("NoCache", "True"); if (!File.Exists(FileName)) { try { var engine = new SqlCeEngine(ConnectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(ConnectionString)) { connection.Open(); var line = string.Empty; var commandText = string.Empty; var file = new StreamReader(".\\Scripts\\sqlceawlite.sql"); while ((line = file.ReadLine()) != null) { if (line.Equals("GO", StringComparison.OrdinalIgnoreCase)) { connection.Execute(commandText); commandText = string.Empty; } else { commandText += "\r\n" + line; } } } } catch (Exception) { throw; } } else { using (var connection = new SqlCeConnection(ConnectionString)) { connection.Execute("delete from [Person]"); } } }
public void BeforeDatabaseIntegration() { if (File.Exists("TestDB.sdf")) File.Delete("TestDB.sdf"); var engine = new SqlCeEngine(TestDBConnectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(TestDBConnectionString)) { connection.Execute(@" create table Posts ( Id INT Primary Key Identity(1,1), CreateDate DATETIME NOT NULL, Author nvarchar(100), Body nvarchar(4000) )"); } }
public static int DeleteMessage(int id) { try { using (var cn = new SqlCeConnection(ConnString)) { cn.Open(); var p = new DynamicParameters(); p.Add("@messageId", id); var exec = cn.Execute("DELETE FROM MESSAGES WHERE MessageId=@MessageID", p); return(exec); } } catch (Exception ex) { throw ex; } }
public virtual void Setup() { string connectionString = string.Format("Data Source=.\\dapperTest_{0}.sdf", Guid.NewGuid()); string[] connectionParts = connectionString.Split(';'); string file = connectionParts .ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1]) .Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase)) .Select(k => k.Value).Single(); if (File.Exists(file)) { File.Delete(file); } using (SqlCeEngine ce = new SqlCeEngine(connectionString)) { ce.CreateDatabase(); } SqlCeConnection connection = new SqlCeConnection(connectionString); var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqlCeDialect()); var sqlGenerator = new SqlGeneratorImpl(config); Db = new Database(connection, sqlGenerator); var files = new List<string> { ReadScriptFile("CreateAnimalTable"), ReadScriptFile("CreateFooTable"), ReadScriptFile("CreateMultikeyTable"), ReadScriptFile("CreatePersonTable"), ReadScriptFile("CreateCarTable") }; foreach (var setupFile in files) { connection.Execute(setupFile); } }
static SqlCETestSuite() { if (File.Exists(FileName)) { File.Delete(FileName); } var engine = new SqlCeEngine(ConnectionString); engine.CreateDatabase(); using (var connection = new SqlCeConnection(ConnectionString)) { connection.Open(); connection.Execute(@"CREATE TABLE Stuff (TheId int IDENTITY(1,1) not null, Name nvarchar(100) not null, Created DateTime null) "); connection.Execute(@"CREATE TABLE People (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) "); connection.Execute(@"CREATE TABLE Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE Results (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, [Order] int not null) "); connection.Execute(@"CREATE TABLE ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE ObjectY (ObjectYId int not null, Name nvarchar(100) not null) "); connection.Execute(@"CREATE TABLE ObjectZ (Id int not null, Name nvarchar(100) not null) "); } Console.WriteLine("Created database"); }
public void Create(string databasename) { _log.Debug(string.Format("Creating database: {0}", databasename)); if (_fileSystemHelper.FileExists(databasename)) { _log.Debug("Deleting current database"); _fileSystemHelper.DeleteFile(databasename); } var connectionString = string.Format("Data Source = {0};", databasename); using (var engine = new SqlCeEngine(connectionString)) { _log.Debug(string.Format("Creating new database with connectionstring: {0}", connectionString)); engine.CreateDatabase(); _log.Debug("Database was created"); } using (IDbConnection connection = new SqlCeConnection(connectionString)) { connection.Open(); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaErrorLogsTable); _log.Debug(string.Format("Created table: {0}", "ErrorLogs")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerVariablesTable); _log.Debug(string.Format("Created table: {0}", "ServerVariables")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaCookieValuesTable); _log.Debug(string.Format("Created table: {0}", "CookieValues")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaFormValuesTable); _log.Debug(string.Format("Created table: {0}", "FormValues")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaQuerystringValuesTable); _log.Debug(string.Format("Created table: {0}", "QuerystringValues")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaClientInformationTable); _log.Debug(string.Format("Created table: {0}", "ClientInformation")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerInformationTable); _log.Debug(string.Format("Created table: {0}", "ServerInformation")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerVariablesForeignKeys); _log.Debug(string.Format("Created foreign keys for: {0}", "ServerVariables")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaFormValuesForeignKeys); _log.Debug(string.Format("Created foreign keys for: {0}", "FormValues")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaCookieValuesForeignKeys); _log.Debug(string.Format("Created foreign keys for: {0}", "CookieValues")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaQuerystringValuesForeignKeys); _log.Debug(string.Format("Created foreign keys for: {0}", "QuerystringValues")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaClientInformationForeignKeys); _log.Debug(string.Format("Created foreign keys for: {0}", "ClientInformation")); connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerInformationForeignKeys); _log.Debug(string.Format("Created foreign keys for: {0}", "ServerInformation")); } }
public async Task<HttpResponseMessage> Post(JObject value) { dynamic data = value; IEnumerable<int> result; using (var connection = new SqlCeConnection(_connString)) { await connection.OpenAsync(); connection.Execute( "insert into Tasks (Title, Description, CreatedDate) values (@title, @description, @createdDate);", new { title = (string)data.title, description = (string)data.description, createdDate = DateTime.Parse((string)data.createdDate) } ); result = await connection.QueryAsync<int>("select max(Id) as id from Tasks;"); } int id = result.First(); data.id = id; var response = Request.CreateResponse(HttpStatusCode.Created, (JObject)data); response.Headers.Location = new Uri(Url.Link("DefaultApi", new { controller = "Tasks", id = id })); return response; }
public bool AssignSequentialStartSlots() { using (var connection = new SqlCeConnection(Settings.Cnn)) { connection.Open(); connection.Execute("update racer set startslot=racerId"); } return true; }
public void RemoveByDocumentTestNodePath(string documentFilePath) { using (var connection = new SqlCeConnection(GetConnectionString())) { var sql = "delete from Coverage where TestPath in (SELECT TestPath from Coverage WHERE DocumentPath=@documentFilePath)"; connection.Execute(sql, new { documentFilePath }); } }
public void RemoveByFile(string filePath) { using (var connection = new SqlCeConnection(GetConnectionString())) { var sql = "DELETE FROM Coverage where DocumentPath=@path"; connection.Execute(sql, new {path = filePath}); } }
public bool ClearWinningRacers() { using (var connection = new SqlCeConnection(Settings.Cnn)) { connection.Open(); connection.Execute("update match set winningracerid=null"); } return true; }
private void SetupTable() { using (var connection = new SqlCeConnection(GetConnectionString())) { var sql = "create table Coverage(" + "NodePath nvarchar(500) not null, " + "TestPath nvarchar(500) not null, " + "ErrorMessage nvarchar(4000) null, " + "DocumentPath nvarchar(500) not null, " + "TestDocumentPath nvarchar(500) not null, " + "Span int not null, " + "IsSuccess bit not null)"; connection.Execute(sql); connection.Execute("CREATE INDEX TestPathIndex ON Coverage (TestPath);"); } }