public void Close_can_be_called_more_than_once()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.Close();
connection.Close();
}
}
public void Close_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var raised = false;
StateChangeEventHandler handler = (sender, e) =>
{
raised = true;
Assert.Equal(connection, sender);
Assert.Equal(ConnectionState.Open, e.OriginalState);
Assert.Equal(ConnectionState.Closed, e.CurrentState);
};
connection.StateChange += handler;
try
{
connection.Close();
Assert.True(raised);
Assert.Equal(ConnectionState.Closed, connection.State);
}
finally
{
connection.StateChange -= handler;
}
}
}
public void Close_can_be_called_before_open()
{
var connection = new SqliteConnection();
connection.Close();
}
public void Commit_throws_when_connection_closed()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
connection.Close();
var ex = Assert.Throws<InvalidOperationException>(() => transaction.Commit());
Assert.Equal(Strings.TransactionCompleted, ex.Message);
}
}
}
private void MigrateDb0_9(IUnitOfWork uow)
{
var db = new SqliteConnection("Data Source=data/nadekobot.sqlite");
if (!File.Exists("data/nadekobot.sqlite"))
{
_log.Warn("No data from the old database will be migrated.");
return;
}
db.Open();
var com = db.CreateCommand();
com.CommandText = "SELECT * FROM Announcement";
var reader = com.ExecuteReader();
var i = 0;
while (reader.Read())
{
var gid = (ulong)(long)reader["ServerId"];
var greet = (long)reader["Greet"] == 1;
var greetDM = (long)reader["GreetPM"] == 1;
var greetChannel = (ulong)(long)reader["GreetChannelId"];
var greetMsg = (string)reader["GreetText"];
var bye = (long)reader["Bye"] == 1;
var byeDM = (long)reader["ByePM"] == 1;
var byeChannel = (ulong)(long)reader["ByeChannelId"];
var byeMsg = (string)reader["ByeText"];
var grdel = false;
var byedel = grdel;
var gc = uow.GuildConfigs.For(gid);
if (greetDM)
gc.SendDmGreetMessage = greet;
else
gc.SendChannelGreetMessage = greet;
gc.GreetMessageChannelId = greetChannel;
gc.ChannelGreetMessageText = greetMsg;
gc.SendChannelByeMessage = bye;
gc.ByeMessageChannelId = byeChannel;
gc.ChannelByeMessageText = byeMsg;
gc.AutoDeleteGreetMessagesTimer = gc.AutoDeleteByeMessagesTimer = grdel ? 30 : 0;
_log.Info(++i);
}
var com2 = db.CreateCommand();
com.CommandText = "SELECT * FROM CurrencyState GROUP BY UserId";
i = 0;
var reader2 = com.ExecuteReader();
while (reader2.Read())
{
_log.Info(++i);
var curr = new Currency()
{
Amount = (long)reader2["Value"],
UserId = (ulong)(long)reader2["UserId"]
};
uow.Currency.Add(curr);
}
db.Close();
try { File.Move("data/nadekobot.sqlite", "data/DELETE_ME_nadekobot.sqlite"); } catch { }
}
public bool UpdateTableField(
string tableName,
string keyFieldName,
string keyFieldValue,
string dataFieldName,
string dataFieldValue,
string additionalWhere)
{
bool result = false;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE " + tableName + " ");
sqlCommand.Append(" SET " + dataFieldName + " = :fieldValue ");
sqlCommand.Append(" WHERE " + keyFieldName + " = " + keyFieldValue);
sqlCommand.Append(" " + additionalWhere + " ");
sqlCommand.Append(" ; ");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":fieldValue", DbType.String);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = dataFieldValue;
SqliteConnection connection = new SqliteConnection(connectionString);
connection.Open();
try
{
int rowsAffected = AdoHelper.ExecuteNonQuery(connection, sqlCommand.ToString(), arParams);
result = (rowsAffected > 0);
}
finally
{
connection.Close();
}
return result;
}
internal void Init()
{
try
{
using (SqliteConnection connection = new SqliteConnection(this.connectionString))
{
connection.Open();
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT version From Version";
try
{
using (SqliteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
connection.Close();
return;
}
}
}
catch (SqliteException)
{
}
}
// Here we have to create the database
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = @"CREATE TABLE Event(
Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
Title text NOT NULL,
Description text NULL,
Modification text NOT NULL,
Expiration text NOT NULL)";
command.ExecuteNonQuery();
}
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = @"CREATE TABLE Painting(
Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
Title text NOT NULL,
ThemeId integer NOT NULL,
Description text NULL,
Filename text NOT NULL,
OnSlider integer NULL)";
command.ExecuteNonQuery();
}
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = @"CREATE TABLE Theme(
Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
ParentId integer NULL,
Title text NOT NULL,
Description text NULL)";
command.ExecuteNonQuery();
}
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = @"CREATE TABLE User(
Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
Login text NOT NULL,
Password text NOT NULL)";
command.ExecuteNonQuery();
}
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = @"CREATE TABLE Version(
version integer)";
command.ExecuteNonQuery();
}
using (SqliteCommand command = connection.CreateCommand())
{
command.CommandText = @"INSERT INTO Version(version) VALUES(1)";
command.ExecuteNonQuery();
connection.Close();
}
}
}
catch (Exception)
{
throw;
}
}
public static void Main(string[] args)
{
Console.OutputEncoding = System.Text.Encoding.Unicode;
SqliteConnection conn = new SqliteConnection("Data Source = " + dbloc);
conn.Open();
Console.WriteLine("Select all customers whose name (CompanyName) starts with letter “D”)");
Console.WriteLine("------------------");
SqliteCommand comm = conn.CreateCommand();
comm.CommandText = @"SELECT CustomerID,CompanyName FROM customers Where CompanyName LIKE 'D%';";
var reader = comm.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}|{1}", reader.GetString(0),
reader.GetString(1));
}
Console.WriteLine("------------------");
Console.WriteLine("Convert names (CompanyNames) of all customers to Upper Case;");
Console.WriteLine("------------------");
SqliteCommand comm2 = conn.CreateCommand();
comm2.CommandText = @"SELECT UPPER(CompanyName) FROM customers;";
reader = comm2.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Select distinct country from Customers");
Console.WriteLine("------------------");
SqliteCommand comm3 = conn.CreateCommand();
comm3.CommandText = @"SELECT DISTINCT Country FROM customers;";
reader = comm3.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Select Contact name from Customers Table from London and title like 'Sales'");
Console.WriteLine("------------------");
SqliteCommand comm4 = conn.CreateCommand();
comm4.CommandText = @"SELECT ContactName FROM customers Where City='London' AND ContactTitle LIKE '%Sales%';";
reader = comm4.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Select all orders id where was bought 'Tofu'");
Console.WriteLine("------------------");
SqliteCommand comm5 = conn.CreateCommand();
comm5.CommandText = @"Select OrderID From 'Order Details' Where ProductID IN (SELECT ProductID FROM Products Where ProductName='Tofu');";
reader = comm5.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Select all product names that were shipped to Germany");
Console.WriteLine("------------------");
SqliteCommand comm6 = conn.CreateCommand();
comm6.CommandText = @"SELECT DISTINCT ProductName FROM Products Where ProductID IN (
SELECT ProductID FROM 'Order Details' Where OrderID IN (
SELECT OrderID FROM Orders Where ShipCountry='Germany'));";
reader = comm6.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Select all customers that ordered 'Ikura'");
Console.WriteLine("------------------");
SqliteCommand comm7 = conn.CreateCommand();
comm7.CommandText = @"Select CustomerID,CompanyName From Customers WHERE CustomerID IN (
Select CustomerID From Orders WHERE OrderID IN (
Select OrderID From 'Order Details' Where ProductID IN (
Select ProductID FROM Products Where ProductName='Ikura' )));";
reader = comm7.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1}", reader.GetString(0),reader.GetString(1));
}
Console.WriteLine("------------------");
Console.WriteLine("Select all phones from Shippers and Suppliers");
Console.WriteLine("------------------");
SqliteCommand comm10 = conn.CreateCommand();
comm10.CommandText = @"SELECT Phone FROM Shippers
UNION
SELECT Phone FROM Suppliers;";
reader = comm10.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Count all customers grouped by city");
Console.WriteLine("------------------");
SqliteCommand comm11 = conn.CreateCommand();
comm11.CommandText = @"SELECT City,Count(CustomerID) FROM Customers GROUP By City;";
reader = comm11.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1}", reader.GetString(0), reader.GetString(1));
}
Console.WriteLine("------------------");
Console.WriteLine("Select all customers that placed more than 10 orders with average Unit Price less than 17");
Console.WriteLine("------------------");
SqliteCommand comm12 = conn.CreateCommand();
comm12.CommandText = @"SELECT Customers.CompanyName, COUNT(selord.OrderID) AS NumberOfOrders FROM (
SELECT AVG(UnitPrice) as AVR, Orders.OrderID,Customers.CustomerID AS CustomerID FROM 'Order Details'
INNER JOIN Orders ON 'Order Details'.OrderID=Orders.OrderID
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID
Group BY Orders.OrderID
HAVING AVR>17) AS selord
INNER JOIN Customers
ON selord.CustomerID=Customers.CustomerID
GROUP BY CompanyName
HAVING COUNT(selord.OrderID) > 10;";
reader = comm12.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1}", reader.GetString(0), reader.GetString(1));
}
Console.WriteLine("------------------");
Console.WriteLine("Select all customers with phone that has format (’NNNN-NNNN’)");
Console.WriteLine("------------------");
SqliteCommand comm13 = conn.CreateCommand();
comm13.CommandText = @"SELECT ContactName,Phone FROM Customers WHERE Phone LIKE '____-____';";
reader = comm13.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1}", reader.GetString(0), reader.GetString(1));
}
Console.WriteLine("------------------");
Console.WriteLine("Select customer that ordered the greatest amount of goods (not price)");
Console.WriteLine("------------------");
SqliteCommand comm14 = conn.CreateCommand();
comm14.CommandText = @"SELECT ContactName,MAX(sum1) as Quantity FROM(
SELECT ContactName, Sum(Quantity) AS sum1 From Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID
Inner JOIN 'Order Details' ON 'Order Details'.OrderID=Orders.OrderID
GROUP BY ContactName);";
reader = comm14.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1}", reader.GetString(0), reader.GetString(1));
}
Console.WriteLine("------------------");
Console.WriteLine("Select only these customers that ordered the absolutely the same products as customer “FAMIA”");
Console.WriteLine("this returns the customer used as parameter as well, to remove this you can add AND CustomerID<>'FAMIA' at the end of query");
Console.WriteLine("------------------");
SqliteCommand comm15 = conn.CreateCommand();
comm15.CommandText = @"SELECT CustomerID
FROM Customers c1
WHERE NOT EXISTS
(
SELECT od1.ProductID
FROM 'Order Details' od1
Inner Join Orders ord1 ON od1.OrderID=ord1.OrderID
WHERE ord1.CustomerID='FAMIA' AND od1.ProductID NOT IN (
SELECT ProductID
FROM 'Order Details' od2
Inner Join Orders ord2 ON od2.OrderID=ord2.OrderID
Inner Join Customers c2 ON ord2.CustomerID=c2.CustomerID
WHERE c2.CustomerID=c1.CustomerID
)
)
AND NOT EXISTS(
SELECT od3.ProductID
FROM 'Order Details' od3
Inner Join Orders ord3 ON od3.OrderID=ord3.OrderID
Inner Join Customers c3 ON ord3.CustomerID=c3.CustomerID
WHERE c3.CustomerID=c1.CustomerID AND od3.ProductID NOT IN (
SELECT od4.ProductID
FROM 'Order Details' od4
Inner Join Orders ord4 ON od4.OrderID=ord4.OrderID
WHERE ord4.CustomerID='FAMIA'
)
)";
reader = comm15.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}", reader.GetString(0));
}
Console.WriteLine("------------------");
Console.WriteLine("Outputs from tasks 8 and 9 are very long, enter 8 or 9 to view (q to quit)");
Console.WriteLine("Task 9 implements outer join (not available in sqlite) by union");
string str;
while ((str = Console.ReadLine()) != "q")
{
if (str.StartsWith("8"))
{
Console.WriteLine("Select all employees and any orders they might have");
Console.WriteLine("------------------");
SqliteCommand comm8 = conn.CreateCommand();
comm8.CommandText = @"SELECT Employees.EmployeeID,Employees.LastName,Orders.OrderID
FROM Employees
LEFT JOIN Orders
ON Employees.EmployeeID=Orders.EmployeeID
ORDER BY LastName;";
reader = comm8.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1},{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
Console.WriteLine("------------------");
}
else if (str.StartsWith("9"))
{
Console.WriteLine("Selects all employees, and all orders");
Console.WriteLine("------------------");
SqliteCommand comm9 = conn.CreateCommand();
comm9.CommandText = @"SELECT Employees.EmployeeID,Employees.LastName,Orders.OrderID
FROM Employees
LEFT JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
UNION ALL
SELECT Employees.EmployeeID,Employees.LastName,Orders.OrderID
FROM Orders
LEFT JOIN Employees
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Employees.EmployeeID IS NULL";
reader = comm9.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0},{1},{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
Console.WriteLine("------------------");
}
}
conn.Close();
}
public static void Main(string[] args)
{
if (!File.Exists(dbloc)) {
FileStream fs = File.Create(dbloc);
fs.Dispose();
}
SqliteConnection conn = new SqliteConnection("Data Source = " + dbloc);
SqliteCommand comm = conn.CreateCommand();
conn.Open();
comm.CommandText = @"CREATE TABLE IF NOT EXISTS [companies] (
[id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[Title] string NOT NULL,
[Country] string NOT NULL,
[AddedDate] Date NOT NULL
);
";
comm.ExecuteNonQuery();
//
SqliteTransaction st2 = conn.BeginTransaction();
try
{
SqliteCommand comm2 = conn.CreateCommand();
comm2.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES
(@title, @country, @date);";
List<Company> companies = new List<Company>();
companies.Add(new Company("Roshen", "Ukraine", "10.10.2010"));
companies.Add(new Company("Sandora", "Ukraine", "11.09.2011"));
companies.Add(new Company("Svitoch","Ukraine","12.08.2012"));
companies.Add(new Company("Rosinka","Ukraine","13.07.2013"));
companies.Add(new Company("Korona","Ukraine","14.06.2014"));
companies.Add(new Company("Mircrosoft","USA","10.10.2009"));
companies.Add(new Company("Google","USA","10.10.2008"));
companies.Add(new Company("Facebook","USA","10.10.2007"));
companies.Add(new Company("Air France","France","10.10.2006"));
companies.Add(new Company("Koenisegg","Sweden","10.10.2005"));
comm2.Parameters.Add(new SqliteParameter("@title", SqliteType.Text));
comm2.Parameters.Add(new SqliteParameter("@country", SqliteType.Text));
comm2.Parameters.Add(new SqliteParameter("@date", SqliteType.Text));
foreach (Company comp in companies)
{
comm2.Parameters[0].Value = comp.Title;
comm2.Parameters[1].Value = comp.Country;
comm2.Parameters[2].Value = comp.AddedDate;
if (comm2.ExecuteNonQuery() != 1)
{
throw new InvalidProgramException();
}
}
st2.Commit();
}
catch (Exception ex)
{
st2.Rollback();
}
//
SqliteCommand comm3 = conn.CreateCommand();
comm3.CommandText = @"SELECT MAX(id),Title FROM companies";
var reader = comm3.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}|{1}", reader.GetInt32(0),
reader.GetString(1));
}
Console.WriteLine("------------------");
//
SqliteCommand comm4 = conn.CreateCommand();
comm4.CommandText = @"
UPDATE companies
SET Country= @country1
WHERE Country= @country2;
";
var country1Param = new SqliteParameter();
country1Param.ParameterName = "@country1";
country1Param.Value = "USA";
var country2Param = new SqliteParameter();
country2Param.ParameterName = "@country2";
country2Param.Value = "Ukraine";
comm4.Parameters.Add(country1Param);
comm4.Parameters.Add(country2Param);
comm4.ExecuteNonQuery();
//
SqliteCommand comm5 = conn.CreateCommand();
comm5.CommandText = @"
DELETE FROM companies
WHERE Country<>@country1;
";
comm5.Parameters.Add(country1Param);
comm5.ExecuteNonQuery();
//
SqliteCommand comm6 = conn.CreateCommand();
comm6.CommandText = @"
SELECT COUNT(*) FROM companies
";
Console.WriteLine(comm6.ExecuteScalar());
Console.WriteLine("------------------");
//
SqliteCommand comm7 = conn.CreateCommand();
comm7.CommandText = @"SELECT * FROM companies";
reader = comm7.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}|{1}|{2}|{3}", reader.GetInt32(0),
reader.GetString(1), reader.GetString(2), reader.GetString(3));
}
Console.WriteLine("------------------");
//
Console.WriteLine(@"input example:{'Title':'title','Country':'country','AddedDate':'20.10.2015'}");
Console.WriteLine("------------------");
List<Company> companiesToAdd = new List<Company>();
string str;
while ((str = Console.ReadLine()) != "q")
{
if (str.StartsWith("{"))
{
Company obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Company>(str);
companiesToAdd.Add(obj);
}
}
if (companiesToAdd.Count > 0)
{
SqliteTransaction st = conn.BeginTransaction();
try
{
SqliteCommand comm8 = conn.CreateCommand();
comm8.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES
(@title, @country, @date);";
comm8.Parameters.Add(new SqliteParameter("@title", SqliteType.Text));
comm8.Parameters.Add(new SqliteParameter("@country", SqliteType.Text));
comm8.Parameters.Add(new SqliteParameter("@date", SqliteType.Text));
foreach (Company comp in companiesToAdd)
{
comm8.Parameters[0].Value = comp.Title;
comm8.Parameters[1].Value = comp.Country;
comm8.Parameters[2].Value = comp.AddedDate;
if (comm8.ExecuteNonQuery() != 1)
{
throw new InvalidProgramException();
}
}
st.Commit();
}
catch (Exception ex)
{
st.Rollback();
}
}
conn.Close();
}
public void Close_can_be_called_before_open()
{
var connection = new SqliteConnection();
connection.Close();
}
