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