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