private void CustomerTransactionReportForm_Load(object sender, EventArgs e)
{
ReportDocument cryRpt;
using (Devart.Data.SQLite.SQLiteConnection myConn = new Devart.Data.SQLite.SQLiteConnection(myConfig.connstr))
{
using (SQLiteCommand myCmd = new SQLiteCommand(sqlcmd, myConn))
{
myConn.Open();
using (SQLiteDataReader myReader = myCmd.ExecuteReader())
{
dataSet2.customer_trans.Load(myReader);
myConn.Close();
}
}
}
this.customerTableAdapter1.Fill(this.dataSet2.customer);
this.configurationTableAdapter1.Fill(this.dataSet2.configuration);
CustomerTransactionReport rpt = new CustomerTransactionReport();
rpt.SetDataSource(this.dataSet2);
cryRpt = new ReportDocument();
cryRpt.Load(rpt.FileName.ToString());
cryRpt.SetDataSource(this.dataSet2);
cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, ReportFolder.reportFolderName + @"\CustomerTransactions.pdf");
}
private static void InsertBook(SQLiteConnection dbCon)
{
Console.WriteLine("Enter Book title");
string title = Console.ReadLine();
title = EscapeSQLString(title);
Console.WriteLine("Enter Book author");
string author = Console.ReadLine();
author = EscapeSQLString(author);
Console.WriteLine("Enter Book publish date in format dd.MM.yyyy");
DateTime publishDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
Console.WriteLine("Enter Book ISBN");
string isbn = Console.ReadLine();
isbn = EscapeSQLString(isbn);
using (dbCon)
{
SQLiteCommand cmdInsert = new SQLiteCommand(
"INSERT INTO Books(Title, Author, PublishDate, ISBN) " +
"VALUES (@title, @author, @publishDate, @isbn)", dbCon);
cmdInsert.Parameters.AddWithValue("@title", title);
cmdInsert.Parameters.AddWithValue("@author", author);
cmdInsert.Parameters.AddWithValue("@publishDate", publishDate);
cmdInsert.Parameters.AddWithValue("@isbn", isbn);
cmdInsert.ExecuteNonQuery();
}
}
private void CreateDatabase()
{
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = "create table users( name varchar not null unique, password varchar not null, email varchar not null, id integer not null primary key)";
cmd.Connection = this.dbconn;
cmd.ExecuteNonQuery();
}
private static void FindBook(SQLiteConnection dbCon)
{
Console.WriteLine("Enter Book title");
string name = Console.ReadLine();
name = EscapeSQLString(name);
using (dbCon)
{
SQLiteCommand cmdSelect = new SQLiteCommand(
"SELECT * FROM Books WHERE Title=@name", dbCon);
cmdSelect.Parameters.AddWithValue("@name", name);
Console.WriteLine("Books:");
Console.WriteLine("ID\tTitle\t\tAuthor\t\tPublish Date\tISBN");
SQLiteDataReader reader = cmdSelect.ExecuteReader();
using (reader)
{
while (reader.Read())
{
int id = (int)reader["BookId"];
string title = (string)reader["Title"];
string author = (string)reader["Author"];
DateTime publishDate = (DateTime)reader["PublishDate"];
string isbn = (string)reader["ISBN"];
Console.WriteLine("{0}\t{1}\t{2}\t{3:dd.MM.yyyy}\t{4}",
id, title.Trim(), author.Trim(), publishDate, isbn.Trim());
}
}
}
}
static void Main(string[] args)
{
//SQLiteConnection.CreateFile("MyDatabase.sqlite");
SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
string sql = "create table highscores (name varchar(20), score int)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Me', 3000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "select * from highscores order by score desc";
command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
}
public Kanji search(String kanji)
{
SQLiteCommand sqlComm = new SQLiteCommand(dbConn);
sqlComm.CommandText = "select * from kanjin3 where kanji='" + kanji + "'";
List<Kanji> result = sqlComm.ExecuteQuery<Kanji>();
return result.First<Kanji>();
}
public void criando_populando(string s)
{
dbConn = new SQLiteConnection(DB_path);
SQLiteCommand cmd = new SQLiteCommand(dbConn);
cmd.CommandText = s;
cmd.ExecuteNonQuery();
dbConn.Close();
}
public string getCustomers()
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = @"Select count(*) from customer";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
return sqLiteCommand1.ExecuteScalar().ToString();
}
internal static void execute(string queryString)
{
using (var conn = new SQLiteConnection(SQLite.connString)) {
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(queryString, conn)) {
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
/// <summary>
/// Creates the command.
/// </summary>
/// <param name="connection">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">Command parameters.</param>
/// <returns>SQLite Command</returns>
public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
{
SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
if (commandParameters.Length > 0)
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
}
public string getCurrentAcntPeriod()
{
SQLiteConnection sqlCon = new SQLiteConnection();
sqlCon.ConnectionString = myConfig.connstr;
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = @"Select acnt_period from configuration";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqlCon;
sqlCon.Open();
return sqLiteCommand1.ExecuteScalar().ToString();
}
public override void Execute(string query)
{
try {
using (SQLiteCommand cmd = new SQLiteCommand(query, connection, transaction)) {
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
System.IO.File.AppendAllText("MySQL_error.log", DateTime.Now + " " + query + "\r\n");
Server.ErrorLog(e);
throw; // Ensures that only one error is thrown (though two will be caught.)
}
}
private void btnForce_Click(object sender, EventArgs e)
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = @"update configuration
set acnt_period = (select t_week_id from acnt_period where t_date = date('now','localtime'))";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
sqLiteCommand1.ExecuteNonQuery();
sqLiteConnection1.Close();
getAcntPeriod();
}
public decimal getSales()
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = "";
sqLiteCommand1.CommandText = @"Select sum(t_amount )from customer_trans where (t_type = 'Invoice')";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader sqReader = sqLiteCommand1.ExecuteReader();
sqReader.Read();
return sqReader.GetDecimal(0);
}
void xeqSQL(string script)
{
Devart.Data.SQLite.SQLiteConnection sqLiteConnection1 = new SQLiteConnection();
sqLiteConnection1.ConnectionString = myConfig.connstr;
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = script;
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
sqLiteCommand1.ExecuteNonQuery();
sqLiteConnection1.Close();
}
public void getInvoiceStats()
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
string myCmd = @"select count(*), sum(t_unpaid) from customer_trans where (t_type = 'Invoice') and (t_unpaid > 0)";
sqLiteCommand1.CommandText = myCmd;
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader sqReader = sqLiteCommand1.ExecuteReader();
sqReader.Read();
unPaidCustomers = sqReader.GetInt16(0);
unPaidAmount = sqReader.GetDecimal(1);
}
public static ObservableCollection<MeetingParticipant> GetMeetings(string EventId, string AttendeeWantId)
{
ObservableCollection<MeetingParticipant> Meetings = new ObservableCollection<MeetingParticipant>();
SQLiteConnection sqlCon = new SQLiteConnection(Utilities.GetConnectionString());
SQLiteCommand sqlCmd = new SQLiteCommand("SELECT * FROM Meetings WHERE EventId = @EventId AND AttendeeWantId = @AttendeeWantId", sqlCon);
sqlCmd.Parameters.AddWithValue("@EventId", EventId);
sqlCmd.Parameters.AddWithValue("@AttendeeWantId", AttendeeWantId);
try
{
sqlCon.Open();
SQLiteDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
MeetingParticipant ThisMeeting = new MeetingParticipant();
ThisMeeting.MeetingId = reader.GetInt32(0);
ThisMeeting.Id = reader.GetString(2);
ThisMeeting.Name = reader[3] != DBNull.Value ? reader.GetString(3) : string.Empty;
ThisMeeting.CompanyName = reader[4] != DBNull.Value ? reader.GetString(4) : string.Empty;
ThisMeeting.Role = reader[5] != DBNull.Value ? reader.GetString(5) : string.Empty;
ThisMeeting.Status = reader[6] != DBNull.Value ? reader.GetString(6) : string.Empty;
ThisMeeting.IsWalkin = reader.GetBoolean(7);
ThisMeeting.JobTitle = reader[8] != DBNull.Value ? reader.GetString(8) : string.Empty;
ThisMeeting.AllocatedTable = reader[9] != DBNull.Value ? reader.GetString(9) : string.Empty;
ThisMeeting.MeetingSlotStartTime = reader.GetDateTime(11);
ThisMeeting.MeetingSlotEndTime = reader.GetDateTime(12);
ThisMeeting.IsPrimary = reader.GetBoolean(13);
ThisMeeting.TableName = reader.GetString(14);
Meetings.Add(ThisMeeting);
}
}
}
catch (Exception ex)
{
string Meesage = ex.Message;
}
finally
{
sqlCon.Close();
}
return Meetings;
}
public static void AddGeocache(string cacheID, string title, string cords, int areaID)
{
CheckConnection();
SQLiteCommand cmd = new SQLiteCommand(_con);
cmd.CommandText = "INSERT INTO geocaches (cacheID, title, cords, areaID) VALUES (@cacheID, @title, @cords, @areaID);";
cmd.Bind("@cacheID", cacheID);
cmd.Bind("@title", title);
cmd.Bind("@cords", cords);
cmd.Bind("@areaID", areaID);
cmd.ExecuteNonQuery();
}
public decimal getReceipts()
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = "";
sqLiteCommand1.CommandText = @"Select sum(t_amount )from customer_trans where (t_type = 'Payment')";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader sqReader = sqLiteCommand1.ExecuteReader();
sqReader.Read();
decimal result = sqReader.GetDecimal(0);
sqLiteConnection1.Close();
return result;
}
internal static bool ExcuteSqlCmd(SQLiteConnection conn, string cmdTxt)
{
SQLiteCommand cmd = new SQLiteCommand(cmdTxt, conn);
//cmd.CommandText = ;
cmd.CommandType = System.Data.CommandType.Text;
try
{
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
Console.WriteLine(e.Message);
return false;
}
return true;
}
public void Rollback()
{
using (var conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
conn.Execute("create table Test (Id int primary key);");
using (var trans = conn.BeginTransaction())
{
conn.Execute("insert into Test(Id) values(1)", transaction: trans);
trans.Rollback();
}
using (var cmd = new SQLiteCommand(@"select count(Id) from Test", conn))
Assert.AreEqual(0L, (long) cmd.ExecuteScalar());
}
}
private void getAcntPeriod()
{
SQLiteCommand sqLiteCommand1 = new SQLiteCommand();
sqLiteCommand1.CommandText = @"Select acnt_period, StatementDate from configuration";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader dr = sqLiteCommand1.ExecuteReader();
dr.Read();
this.textBox1.Text = dr.GetInt32(0).ToString();
this.textBox4.Text = getFridaysDate(dr.GetInt32(0)).ToString("dd-MMM-yyyy");
sqLiteConnection1.Close();
sqLiteCommand1.CommandText = @"select t_week_id from acnt_period where t_date = date('now','localtime')";
sqLiteCommand1.CommandType = CommandType.Text;
sqLiteCommand1.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
SQLiteDataReader dr1 = sqLiteCommand1.ExecuteReader();
dr1.Read();
this.textBox6.Text = dr1.GetInt32(0).ToString();
this.textBox5.Text = getFridaysDate(dr1.GetInt32(0)).ToString("dd-MMM-yyyy");
sqLiteConnection1.Close();
string cmd = @"select strftime('%d-%m-%Y',t_timestamp) as '{0}',
t_week_id as '{1}' ,count(*) as '{2}'
from invoice_header h, customer_trans t, configuration c
where invoice_number = t_src_id and t_week_id > (acnt_period - 3)
group by Date(t_timestamp), t_week_id";
string sqlcmd = String.Format(cmd, "Entry Date","Accounting Period", "Invoices");
sqLiteConnection1.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(sqlcmd.ToString(), sqLiteConnection1);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
dataGridView1.Width = dataGridView1.Columns[0].Width + dataGridView1.Columns[1].Width + dataGridView1.Columns[2].Width + 5;
dataGridView1.ReadOnly = true;
sqLiteConnection1.Close();
}
public static void AddMeetingsDB(ObservableCollection<MobileMeetingProject.EsEngine.AttendeeWrapper> Attendees, string EventId)
{
DeleteMeetingsDB(EventId);
foreach (AttendeeWrapper TempAtt in Attendees)
{
foreach (MeetingParticipant ThisMeeting in TempAtt.Meetings)
{
SQLiteConnection sqlCon = new SQLiteConnection(Utilities.GetConnectionString());
SQLiteCommand sqlCmd = new SQLiteCommand("INSERT INTO Meetings(MeetingId,EventId,Id,Name,CompanyName,Role,Status,IsWalkin,JobTitle,AllocatedTable,"+
"AttendeeWantId,StartTime,EndTime,IsPrimary,TableName) VALUES(@MeetingId,@EventId,@Id,@Name,@CompanyName,@Role,"+
"@Status,@IsWalkin,@JobTitle,@AllocatedTable,@AttendeeWantId,@StartTime,@EndTime,@IsPrimary,@TableName)", sqlCon);
sqlCmd.Parameters.AddWithValue("@MeetingId", ThisMeeting.MeetingId);
sqlCmd.Parameters.AddWithValue("@EventId", EventId);
sqlCmd.Parameters.AddWithValue("@Id", ThisMeeting.Id);
sqlCmd.Parameters.AddWithValue("@Name", ThisMeeting.Name);
sqlCmd.Parameters.AddWithValue("@CompanyName", ThisMeeting.CompanyName);
sqlCmd.Parameters.AddWithValue("@Role", ThisMeeting.Role);
sqlCmd.Parameters.AddWithValue("@Status", ThisMeeting.Status);
sqlCmd.Parameters.AddWithValue("@IsWalkin", ThisMeeting.IsWalkin);
sqlCmd.Parameters.AddWithValue("@JobTitle", ThisMeeting.JobTitle);
sqlCmd.Parameters.AddWithValue("@AllocatedTable", ThisMeeting.AllocatedTable);
sqlCmd.Parameters.AddWithValue("@AttendeeWantId", TempAtt.ThisAttendee.Id);
sqlCmd.Parameters.AddWithValue("@StartTime", ThisMeeting.MeetingSlotStartTime);
sqlCmd.Parameters.AddWithValue("@EndTime", ThisMeeting.MeetingSlotEndTime);
sqlCmd.Parameters.AddWithValue("@IsPrimary", ThisMeeting.IsPrimary);
sqlCmd.Parameters.AddWithValue("@TableName", ThisMeeting.TableName);
try
{
sqlCon.Open();
int RowsAffected = sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string Message = ex.Message;
}
finally
{
sqlCon.Close();
}
}
}
}
public async void Calculate()
{
try
{
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "expenses.sqlite");
using (var db = new SQLite.SQLiteConnection(dbPath))
{
SQLiteCommand comm = new SQLiteCommand(db);
comm.CommandText = "SELECT SUM(DataPrice) FROM Data";
var sum = comm.ExecuteScalar<int>();
await new MessageDialog("Money Spent: " + sum.ToString() + " EGP", "Sum").ShowAsync();
}
}
catch (Exception ex)
{
await new MessageDialog("There's no data!","Error").ShowAsync();
}
}
private static void CheckConnection()
{
//Check for connection
if (_con == null)
{
try
{
StorageFolder folder = ApplicationData.Current.LocalFolder;
string conString = folder.Path + "\\caches.db";
_con = new SQLiteConnection(conString);
}
catch (Exception)
{
}
}
SQLiteCommand cmd = new SQLiteCommand(_con);
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `geocaches` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`cacheID` TEXT,
`title` TEXT,
`cords` TEXT,
`notes` TEXT,
`areaID` INTEGER
);";
cmd.ExecuteNonQuery();
cmd = new SQLiteCommand(_con);
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `areas` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`areaName` TEXT,
`countryName` TEXT
);";
cmd.ExecuteNonQuery();
}
public DB(int type, string ConnectionString)
{
CType = type;
switch (CType)
{
case 0:
CLink = new MySqlConnection(ConnectionString);
((MySqlConnection)CLink).Open();
CCommand = new MySqlCommand("", (MySqlConnection)CLink);
break;
case 1:
CLink = new SqlConnection(ConnectionString);
((SqlConnection)CLink).Open();
CCommand = new SqlCommand("", (SqlConnection)CLink);
break;
case 2:
CLink = new SQLiteConnection(ConnectionString);
((SQLiteConnection)CLink).Open();
CCommand = new SQLiteCommand("", (SQLiteConnection)CLink);
break;
}
}
//创建一个连接到指定数据库
//public static void connectToDatabase()
// {
// m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
// m_dbConnection.Open();
// }
//在指定数据库中创建一个table
//void createTable()
//{
// string sql = "create table highscores (name varchar(20), score int)";
// SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
// command.ExecuteNonQuery();
//}
public static int GetMaxNumber()
{
string sql = @"select max(orderNumber) from Task";
int i = 0;
DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection cnn = fact.CreateConnection())
{
cnn.ConnectionString = sqlPath;
cnn.Open();
SQLiteCommand command = new SQLiteCommand(sql, (SQLiteConnection)cnn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string iresult =reader[0].ToString();
if(!string.IsNullOrEmpty(iresult))
{
i = int.Parse(iresult)+1;
}
}
}
return i;
}
public static void Add(string UserPath, string TrackingPath, string Name, string SurName, int Age, string Tracking)
{
//Console.WriteLine("UserPath" + UserPath);
//Console.WriteLine("TrackingPath" + TrackingPath);
// "C:\TestApp\users.db"";
using (SQLiteConnection ConnectUsers = new SQLiteConnection($@"Data Source={UserPath}; Version=3;"))
{
ConnectUsers.Open();
SQLiteCommand Command = new SQLiteCommand
{
Connection = ConnectUsers,
CommandText = $@"INSERT INTO [USERS] (Name,Surname,Age,Tracking)
values('{Name}',
'{SurName}',
'{Age}',
'{Tracking}')"
};
int sqlReader = Command.ExecuteNonQuery();
SQLiteCommand GetId = new SQLiteCommand
{
Connection = ConnectUsers,
CommandText = $@"select id from [USERS] where Name ='{Name}'
and
Surname='{SurName}'
and
Age='{Age}'
and
Tracking='{Tracking}'
"
};
IdUser = Convert.ToInt32(GetId.ExecuteScalar());
ConnectUsers.Close();
}
using (SQLiteConnection ConnectTracking =
new SQLiteConnection($@"Data Source={TrackingPath}; Version=3;"))
{
ConnectTracking.Open();
SQLiteCommand InsertTracking = new SQLiteCommand
{
Connection = ConnectTracking,
CommandText = $@"INSERT INTO [tracking] (UserId,Track)
values({IdUser},
'{Tracking}')"
};
int sqlReader2 = InsertTracking.ExecuteNonQuery();
Console.WriteLine($@"В таблицу users Добавлены следующие данные:
Name:{Name}
SurName:{SurName}
Age:{Age}
Tracking:{Tracking}
");
ConnectTracking.Close();
}
}
public async Task <SQLiteDataReader> ConsultaLectura(string query)
{
SQLiteCommand sQLiteCommand = new SQLiteCommand(query, this.SQLiteConnection);
return(sQLiteCommand.ExecuteReader());
}
private void Button_Send(object sender, RoutedEventArgs e)
{
double amountSpent;
if (textBoxForSpent.Text != "")
{
amountSpent = Convert.ToDouble(textBoxForSpent.Text.Trim());
}
else
{
MessageBox.Show("Введите сумму!");
return;
}
string getCategoryId;
if (listOfCategories.SelectedIndex > -1)
{
getCategoryId = listOfCategories.SelectedItems[0].ToString();
}
else
{
MessageBox.Show("Выберите категорию!");
return;
}
string getComment = textBoxForComments.Text.Trim();
string getBill = listOfUsers.Text.ToString();
UserSpend userSpend = new UserSpend(amountSpent, Convert.ToInt32(getCategoryId), getComment);
using (ApplicationContext context = new ApplicationContext())
{
DB db = new DB();
SQLiteCommand command = new SQLiteCommand("UPDATE Totals SET totalMoney = totalMoney - @amountSpent WHERE billName = @billName", db.getConnection());
command.Parameters.Add("@amountSpent", DbType.Double).Value = amountSpent;
command.Parameters.Add("@billName", DbType.String).Value = getBill;
db.openConnection();
command.ExecuteNonQuery();
int index = listOfUsers.SelectedIndex;
List <Total> total = context.Totals.ToList();
listOfUsers.ItemsSource = total;
listOfUsers.SelectedIndex = index;
context.UserSpends.Add(userSpend);
context.SaveChanges();
textBoxForSpent.Clear();
listOfCategories.UnselectAll();
textBoxForComments.Clear();
}
using (ApplicationContext context = new ApplicationContext())
{
List <UserSpend> userSpends = context.UserSpends
.Include(c => c.Category)
.ToList();
historySpend.ItemsSource = userSpends;
new Sorting().SortDesc(historySpend);
}
}
private async void button1_Click(object sender, EventArgs e)
{
var bk = new Book()
{
Author = author.Text,
Available = (int)available.Value,
Coverurl = CoverURL.Text,
Description = description.Text,
ISBN = isbn.Text,
Title = title.Text
};
await helper.AddBook(bk);
//description??
string Author = author.Text;
int Available = (int)available.Value;
string Coverurl = CoverURL.Text;
string Description = description.Text;
string ISBN = isbn.Text;
string Title = title.Text;
//string isbn1 = isbn.Text; //could be null
//string title = textBox2.Text; //must not be null
//string author = textBox3.Text; //must not be null
//string description = textBox5.Text; //could be null
//string numAvailable =textBox4.Text; //could be null
/*//condition so the user fill the important parts
* if (title.Equals(""))
* {
* MessageBox.Show("please enter title");
* }
* else if (author.Equals(""))
* {
* MessageBox.Show("please enter author");
* }
* else
* {
* SqlCommand insertCommand = new SqlCommand("insert into books(isbn,title,author,description,available) values(@isbn, @title, @author, @description, @numAvailable)");
* insertCommand.Parameters.AddWithValue("@isbn", isbn);
* insertCommand.Parameters.AddWithValue("@title", title);
* insertCommand.Parameters.AddWithValue("@author", author);
* insertCommand.Parameters.AddWithValue("@description", description);
* insertCommand.Parameters.AddWithValue("@numAvailable", numAvailable);
*
* int row = dbobj.executeQuery(insertCommand);
* if (row == 1)
* {
* MessageBox.Show("done!");
* }
* else
* {
* MessageBox.Show("error retry!");
* }
*
* //MessageBox.Show("Book Added", "The Book Has Beed Added to The Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
* } */
string query = "INSERT into Books (ISBN,Title,Author,CoverURL,Description,NumAvailable) values (@ISBN,@Title,@Author,@Coverurl,@Description,@Available)";
SQLiteCommand insertCommand = new SQLiteCommand(query, dbobject.myConnection);
dbobject.OpenConnection();
insertCommand.Parameters.AddWithValue("@ISBN", ISBN);
insertCommand.Parameters.AddWithValue("@Title", Title);
insertCommand.Parameters.AddWithValue("@Author", Author);
insertCommand.Parameters.AddWithValue("@Coverurl", Coverurl);
insertCommand.Parameters.AddWithValue("@Description", Description);
insertCommand.Parameters.AddWithValue("@Available", Available);
var resault = insertCommand.ExecuteNonQuery();
dbobject.CloseConnection();
if (resault == 1)
{
MessageBox.Show("The Book Has Beed Added to The Database", "Book Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("error retry!");
}
this.Hide();
}
private void btn_restock_Click(object sender, EventArgs e)
{
DateTime today = DateTime.Today;
string date = today.ToString("dd/MM/yyyy");
string yesterday = DateTime.Today.AddDays(-1).ToString("dd/MM/yyyy");
myConnection = new SQLiteConnection("Data Source=database.sqlite3");
string qry_date = "SELECT COUNT(*) FROM tbl_insandlos WHERE date = '" + date + "'";
SQLiteCommand cmd_date = new SQLiteCommand(qry_date, myConnection);
openConnection();
int check_date = Convert.ToInt32(cmd_date.ExecuteScalar());
if (check_date == 0)
{
SQLiteCommand sqlComm = new SQLiteCommand("begin", myConnection);
sqlComm.ExecuteNonQuery();
string qry_trans = "INSERT INTO tbl_insandlos ('date', 'pitapastlo', 'pitapresentin', 'pitapresentlo', 'ricepastlo', 'ricepresentin', 'ricepresentlo', 'kebabpastlo', 'kebabpresentin', 'kebabpresentlo', 'mealboxpastlo', 'mealboxpresentin', 'mealboxpresentlo', 'beefpastlo', 'beefpresentin', 'beefpresentlo', 'chickenpastlo', 'chickenpresentin', 'chickenpresentlo', 'bsteakpastlo', 'bsteakpresentin', 'bsteakpresentlo', 'csteakpastlo', 'csteakpresentin', 'csteakpresentlo', 'cheesepastlo', 'cheesepresentin', 'cheesepresentlo', 'friesbagspastlo', 'friesbagspresentin', 'friesbagspresentlo', 'minicupspastlo', 'minicupspresentin', 'minicupspresentlo', 'conespastlo', 'conespresentin', 'conespresentlo', 'cupspastlo', 'cupspresentin', 'cupspresentlo', 'waterpastlo', 'waterpresentin', 'waterpresentlo', 'sodapastlo', 'sodapresentin', 'sodapresentlo', 'platterboxpastlo', 'platterboxpresentin', 'platterboxpresentlo', 'minipitapastlo', 'minipitapresentin', 'minipitapresentlo', 'hotdogpastlo', 'hotdogpresentin', 'hotdogpresentlo') " +
"VALUES (@date, @pitapastlo, @pitapresentin, @pitapresentlo, @ricepastlo, @ricepresentin, @ricepresentlo, @kebabpastlo, @kebabpresentin, @kebabpresentlo, @mealboxpastlo, @mealboxpresentin, @mealboxpresentlo, @beefpastlo, @beefpresentin, @beefpresentlo, @chickenpastlo, @chickenpresentin, @chickenpresentlo, @bsteakpastlo, @bsteakpresentin, @bsteakpresentlo, @csteakpastlo, @csteakpresentin, @csteakpresentlo, @cheesepastlo, @cheesepresentin, @cheesepresentlo, @friesbagspastlo, @friesbagspresentin, @friesbagspresentlo, @minicupspastlo, @minicupspresentin, @minicupspresentlo, @conespastlo, @conespresentin, @conespresentlo, @cupspastlo, @cupspresentin, @cupspresentlo, @waterpastlo, @waterpresentin, @waterpresentlo, @sodapastlo, @sodapresentin, @sodapresentlo, @platterboxpastlo, @platterboxpresentin, @platterboxpresentlo, @minipitapastlo, @minipitapresentin, @minipitapresentlo, @hotdogpastlo, @hotdogpresentin, @hotdogpresentlo)";
SQLiteCommand cmd_trans = new SQLiteCommand(qry_trans, myConnection);
cmd_trans.Parameters.AddWithValue("@date", date);
cmd_trans.Parameters.AddWithValue("@pitapastlo", 0);
cmd_trans.Parameters.AddWithValue("@pitapresentin", 0);
cmd_trans.Parameters.AddWithValue("@pitapresentlo", 0);
cmd_trans.Parameters.AddWithValue("@ricepastlo", 0);
cmd_trans.Parameters.AddWithValue("@ricepresentin", 0);
cmd_trans.Parameters.AddWithValue("@ricepresentlo", 0);
cmd_trans.Parameters.AddWithValue("@kebabpastlo", 0);
cmd_trans.Parameters.AddWithValue("@kebabpresentin", 0);
cmd_trans.Parameters.AddWithValue("@kebabpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@mealboxpastlo", 0);
cmd_trans.Parameters.AddWithValue("@mealboxpresentin", 0);
cmd_trans.Parameters.AddWithValue("@mealboxpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@beefpastlo", 0);
cmd_trans.Parameters.AddWithValue("@beefpresentin", 0);
cmd_trans.Parameters.AddWithValue("@beefpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@chickenpastlo", 0);
cmd_trans.Parameters.AddWithValue("@chickenpresentin", 0);
cmd_trans.Parameters.AddWithValue("@chickenpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@bsteakpastlo", 0);
cmd_trans.Parameters.AddWithValue("@bsteakpresentin", 0);
cmd_trans.Parameters.AddWithValue("@bsteakpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@csteakpastlo", 0);
cmd_trans.Parameters.AddWithValue("@csteakpresentin", 0);
cmd_trans.Parameters.AddWithValue("@csteakpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@cheesepastlo", 0);
cmd_trans.Parameters.AddWithValue("@cheesepresentin", 0);
cmd_trans.Parameters.AddWithValue("@cheesepresentlo", 0);
cmd_trans.Parameters.AddWithValue("@friesbagspastlo", 0);
cmd_trans.Parameters.AddWithValue("@friesbagspresentin", 0);
cmd_trans.Parameters.AddWithValue("@friesbagspresentlo", 0);
cmd_trans.Parameters.AddWithValue("@minicupspastlo", 0);
cmd_trans.Parameters.AddWithValue("@minicupspresentin", 0);
cmd_trans.Parameters.AddWithValue("@minicupspresentlo", 0);
cmd_trans.Parameters.AddWithValue("@conespastlo", 0);
cmd_trans.Parameters.AddWithValue("@conespresentin", 0);
cmd_trans.Parameters.AddWithValue("@conespresentlo", 0);
cmd_trans.Parameters.AddWithValue("@cupspastlo", 0);
cmd_trans.Parameters.AddWithValue("@cupspresentin", 0);
cmd_trans.Parameters.AddWithValue("@cupspresentlo", 0);
cmd_trans.Parameters.AddWithValue("@waterpastlo", 0);
cmd_trans.Parameters.AddWithValue("@waterpresentin", 0);
cmd_trans.Parameters.AddWithValue("@waterpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@sodapastlo", 0);
cmd_trans.Parameters.AddWithValue("@sodapresentin", 0);
cmd_trans.Parameters.AddWithValue("@sodapresentlo", 0);
cmd_trans.Parameters.AddWithValue("@platterboxpastlo", 0);
cmd_trans.Parameters.AddWithValue("@platterboxpresentin", 0);
cmd_trans.Parameters.AddWithValue("@platterboxpresentlo", 0);
cmd_trans.Parameters.AddWithValue("@minipitapastlo", 0);
cmd_trans.Parameters.AddWithValue("@minipitapresentin", 0);
cmd_trans.Parameters.AddWithValue("@minipitapresentlo", 0);
cmd_trans.Parameters.AddWithValue("@hotdogpastlo", 0);
cmd_trans.Parameters.AddWithValue("@hotdogpresentin", 0);
cmd_trans.Parameters.AddWithValue("@hotdogpresentlo", 0);
var res_trans = cmd_trans.ExecuteNonQuery();
sqlComm = new SQLiteCommand("end", myConnection);
sqlComm.ExecuteNonQuery();
}
string qry = "SELECT * FROM tbl_ingredients ORDER BY category_id";
string qry2 = "SELECT COUNT(*) FROM tbl_ingredients";
SQLiteCommand myCommand = new SQLiteCommand(qry, myConnection);
SQLiteCommand myCommand2 = new SQLiteCommand(qry2, myConnection);
openConnection();
int row = Convert.ToInt32(myCommand2.ExecuteScalar());
SQLiteDataReader result = myCommand.ExecuteReader();
string[] itemname = new string[row];
string[] totalstock = new string[row];
int cnt = 0;
if (result.HasRows)
{
while (result.Read())
{
itemname[cnt] = result["in_name"].ToString();
cnt++;
}
}
foreach (string s in itemname)
{
TextBox t = (tbl_inventory.Controls.Find("txt_" + s, true).First() as TextBox);
if (t.Text != "")
{
Label c = (tbl_inventory.Controls.Find("lbl_" + s, true).First() as Label);
var current = Convert.ToInt32(c.Text);
var add = Convert.ToInt32(t.Text);
c.Text = (current + add).ToString();
t.Text = "";
Label d = (tbl_inventory.Controls.Find(s, true).First() as Label);
SQLiteCommand sqlComm = new SQLiteCommand("begin", myConnection);
sqlComm.ExecuteNonQuery();
string qry3 = "UPDATE tbl_ingredients SET in_stock = in_stock + '" + add + "' WHERE in_name = '" + d.Name + "'";
SQLiteCommand myCommand3 = new SQLiteCommand(qry3, myConnection);
myCommand3.ExecuteNonQuery();
sqlComm = new SQLiteCommand("end", myConnection);
sqlComm.ExecuteNonQuery();
SQLiteCommand sqlComm2 = new SQLiteCommand("begin", myConnection);
sqlComm2.ExecuteNonQuery();
if (s == "Pita")
{
string qry4 = "UPDATE tbl_insandlos SET pitapresentin = pitapresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Mini Pita")
{
string qry4 = "UPDATE tbl_insandlos SET minipitapresentin = minipitapresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Rice")
{
string qry4 = "UPDATE tbl_insandlos SET ricepresentin = ricepresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Kebab")
{
string qry4 = "UPDATE tbl_insandlos SET kebabpresentin = kebabpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Beef")
{
string qry4 = "UPDATE tbl_insandlos SET beefpresentin = beefpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Chicken")
{
string qry4 = "UPDATE tbl_insandlos SET chickenpresentin = chickenpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Hotdog")
{
string qry4 = "UPDATE tbl_insandlos SET hotdogpresentin = hotdogpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Beef Steak")
{
string qry4 = "UPDATE tbl_insandlos SET bsteakpresentin = bsteakpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Chicken Steak")
{
string qry4 = "UPDATE tbl_insandlos SET csteakpresentin = csteakpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Cheddar Cheese")
{
string qry4 = "UPDATE tbl_insandlos SET cheesepresentin = cheesepresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Water")
{
string qry4 = "UPDATE tbl_insandlos SET waterpresentin = waterpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Coke" || s == "Sprite" || s == "Royal")
{
string qry4 = "UPDATE tbl_insandlos SET sodapresentin = sodapresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
sqlComm2 = new SQLiteCommand("end", myConnection);
sqlComm2.ExecuteNonQuery();
}
}
closeConnection();
string qry_mat = "SELECT * FROM tbl_materials";
string qry_mat2 = "SELECT COUNT(*) FROM tbl_materials";
SQLiteCommand myCommand_mat = new SQLiteCommand(qry_mat, myConnection);
SQLiteCommand myCommand_mat2 = new SQLiteCommand(qry_mat2, myConnection);
openConnection();
int row2 = Convert.ToInt32(myCommand_mat2.ExecuteScalar());
SQLiteDataReader result2 = myCommand_mat.ExecuteReader();
string[] itemname2 = new string[row2];
string[] totalstock2 = new string[row2];
int cnt2 = 0;
if (result2.HasRows)
{
while (result2.Read())
{
itemname2[cnt2] = result2["mat_name"].ToString();
cnt2++;
}
}
foreach (string s in itemname2)
{
TextBox t = (tbl_materials.Controls.Find("txt_" + s, true).First() as TextBox);
if (t.Text != "")
{
Label c = (tbl_materials.Controls.Find("lbl_" + s, true).First() as Label);
var current = Convert.ToInt32(c.Text);
var add = Convert.ToInt32(t.Text);
c.Text = (current + add).ToString();
t.Text = "";
Label d = (tbl_materials.Controls.Find(s, true).First() as Label);
SQLiteCommand sqlComm = new SQLiteCommand("begin", myConnection);
sqlComm.ExecuteNonQuery();
string qry3 = "UPDATE tbl_materials SET mat_stock = mat_stock + '" + add + "' WHERE mat_name = '" + d.Name + "'";
SQLiteCommand myCommand3 = new SQLiteCommand(qry3, myConnection);
myCommand3.ExecuteNonQuery();
sqlComm = new SQLiteCommand("end", myConnection);
sqlComm.ExecuteNonQuery();
SQLiteCommand sqlComm2 = new SQLiteCommand("begin", myConnection);
sqlComm2.ExecuteNonQuery();
if (s == "Cones")
{
string qry4 = "UPDATE tbl_insandlos SET conespresentin = conespresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Meal Box")
{
string qry4 = "UPDATE tbl_insandlos SET mealboxpresentin = mealboxpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Platter Box")
{
string qry4 = "UPDATE tbl_insandlos SET platterboxpresentin = platterboxpresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Fries Bags")
{
string qry4 = "UPDATE tbl_insandlos SET friesbagspresentin = friesbagspresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Cups")
{
string qry4 = "UPDATE tbl_insandlos SET cupspresentin = cupspresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
else if (s == "Mini Cups")
{
string qry4 = "UPDATE tbl_insandlos SET minicupspresentin = minicupspresentin + " + add + " WHERE date = '" + date + "'";
SQLiteCommand myCommand4 = new SQLiteCommand(qry4, myConnection);
myCommand4.ExecuteNonQuery();
}
sqlComm2 = new SQLiteCommand("end", myConnection);
sqlComm2.ExecuteNonQuery();
}
}
closeConnection();
}
public string Save(Users.NewUser user, NewClient x, string lang)
{
SaveResponse r = new SaveResponse();
try {
//db.CreateDataBase(user.userGroupId, db.clients);
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "note"); //new column in clients tbl.
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "cids", "VARCHAR(200)"); //new column in cids tbl.
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "dailyActivities", "TEXT");
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "myRecommendedEnergyIntake", "VARCHAR(50)");
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "myRecommendedEnergyExpenditure", "VARCHAR(50)");
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "myMeals", "TEXT");
db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.clients, "allergens", "TEXT");
string sql = null;
if (x.clientId == null && !Check(user.userGroupId, x))
{
r.data = null;
r.isSuccess = false;
r.msg = t.Tran("client is already registered", lang);
return(JsonConvert.SerializeObject(r, Formatting.None));
}
Global G = new Global();
x.note = G.RemoveSingleQuotes(x.note);
if (x.clientId == null)
{
x.clientId = Convert.ToString(Guid.NewGuid());
}
//************TODO***************
int clientsLimit = MonthlyLimitOfClients(user.userType);
if (NumberOfClientsPerMonth(user.userGroupId) > clientsLimit)
{
r.data = null;
r.msg = string.Format("{0} {1}.", t.Tran("client was not saved. the maximum number of clients in one month is", lang), clientsLimit);
return(JsonConvert.SerializeObject(r, Formatting.None));
}
sql = string.Format(@"INSERT OR REPLACE INTO clients VALUES
('{0}', '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}', '{8}', {9}, '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}')"
, x.clientId
, x.firstName
, x.lastName
, x.birthDate
, x.gender.value
, x.phone
, x.email
, x.userId
, x.date
, x.isActive
, x.note
, x.cids
, x.clientData.dailyActivities.activities == null ? null : JsonConvert.SerializeObject(x.clientData.dailyActivities.activities, Formatting.None)
, x.clientData.myCalculation.recommendedEnergyIntake
, x.clientData.myCalculation.recommendedEnergyExpenditure
, JsonConvert.SerializeObject(x.clientData.myMeals, Formatting.None)
, x.allergens);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(user.userGroupId, dataBase))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteTransaction transaction = connection.BeginTransaction()) {
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
r.data = x;
r.data.gender.title = GetGenderTitle(r.data.gender.value);
r.data.userGroupId = user.userGroupId;
r.msg = null;
r.isSuccess = true;
return(JsonConvert.SerializeObject(r, Formatting.None));
} catch (Exception e) {
r.data = null;
r.msg = e.Message;
r.isSuccess = false;
L.SendErrorLog(e, x.clientId, x.userId, "Clients", "Save");
return(JsonConvert.SerializeObject(r, Formatting.None));
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (username.Text.Equals("Email") || password.Password.Equals(""))
{
inavlidLogin.Visibility = Visibility.Hidden;
notFound.Visibility = Visibility.Hidden;
empty.Visibility = Visibility.Visible;
}
else
{
Database d = new Database();
d.openConnection();
string query = "SELECT * FROM USER WHERE EMAIL='" + username.Text + "';";
SQLiteCommand cmd = new SQLiteCommand(query, d.con);
SQLiteDataReader result = cmd.ExecuteReader();
try
{
if (result.HasRows)
{
if (result.Read())
{
if (password.Password.Equals(result["PASSWORD"]))
{
inavlidLogin.Visibility = Visibility.Hidden;
empty.Visibility = Visibility.Hidden;
notFound.Visibility = Visibility.Hidden;
UserDashboard user = new UserDashboard(
result["PH_NO"].ToString(),
result["NAME"].ToString(),
result["B_GRP"].ToString(),
result["EMAIL"].ToString(),
result["LOCATION"].ToString(),
result["CITY"].ToString(),
result["TYPE_OF_USER"].ToString(),
result["MI_ID"].ToString());
this.Hide();
user.Show();
}
else
{
empty.Visibility = Visibility.Hidden;
notFound.Visibility = Visibility.Hidden;
inavlidLogin.Visibility = Visibility.Visible;
}
}
}
else
{
query = "SELECT * FROM MED_INST WHERE EMAIL='" + username.Text + "';";
cmd = new SQLiteCommand(query, d.con);
result = cmd.ExecuteReader();
if (result.HasRows)
{
if (result.Read())
{
if (password.Password.Equals(result["PASSWORD"]))
{
inavlidLogin.Visibility = Visibility.Hidden;
empty.Visibility = Visibility.Hidden;
notFound.Visibility = Visibility.Hidden;
HosDashboard hos = new HosDashboard(
result["MI_ID"].ToString(),
result["NAME"].ToString(),
result["PHONE"].ToString(),
result["LOCATION"].ToString(),
result["CITY"].ToString(),
result["WEBSITE"].ToString(),
result["EMAIL"].ToString(),
result["TYPE_OF_MI"].ToString());
this.Hide();
hos.Show();
}
else
{
empty.Visibility = Visibility.Hidden;
notFound.Visibility = Visibility.Hidden;
inavlidLogin.Visibility = Visibility.Visible;
}
}
}
else
{
inavlidLogin.Visibility = Visibility.Hidden;
empty.Visibility = Visibility.Hidden;
notFound.Visibility = Visibility.Visible;
}
}
}
catch (Exception excp)
{
MessageBox.Show("Exception in login page\n" + excp.Message);
}
finally
{
d.closeConnection();
}
}
//if(username.Text.Equals("admin") && password.Password.Equals("admin"))
//{
// inavlidLogin.Visibility = Visibility.Hidden;
// User user = new User();
// this.Hide();
// user.Show();
//}
}
public static void SavePierToSqliteHelper(string dbPath, string tableName, List <CRailwayProject> cl)
{
//string dbPath = CGisDataSettings.gDataPath + @"\jiqing\JQGis.db";
string sql = "INSERT INTO ";
sql += tableName;
sql += " (BridgeName,Name,Longitude,Latitude,Altitude,YawOffset,MileagePrefix,Mileage,Project_B_DW_ID,ProjectID,ProjectLength,FinishTime,SerialNo,IsValid, IsFinish)" +
"values(@BridgeName,@Name,@Longitude,@Latitude,@Altitude,@YawOffset,@MileagePrefix,@Mileage,@Project_B_DW_ID,@ProjectID,@ProjectLength,@FinishTime,@SerialNo,@IsValid,@IsFinish)";
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
foreach (CRailwayProject p in cl)
{
if (p.mPierList.Count == 0)
{
continue;
}
foreach (CRailwayDWProj dwp in p.mPierList)
{
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@BridgeName", p.ProjectName),
new SQLiteParameter("@Name", dwp.DWName + "#墩"),
new SQLiteParameter("@Longitude", dwp.mLongitude_Mid),
new SQLiteParameter("@Latitude", dwp.mLatitude_Mid),
new SQLiteParameter("@Altitude", dwp.mAltitude_Mid),
new SQLiteParameter("@YawOffset", dwp.mHeading_Mid),
new SQLiteParameter("@MileagePrefix", dwp.DKCode_Start),
new SQLiteParameter("@Mileage", dwp.Mileage_Start),
new SQLiteParameter("@Project_B_DW_ID", dwp.mDWID),
new SQLiteParameter("@ProjectID", dwp.mParentID),
new SQLiteParameter("@ProjectLength", dwp.mLength),
new SQLiteParameter("@FinishTime", dwp.FinishTime.ToShortDateString()),
new SQLiteParameter("@SerialNo", dwp.mSerialNo),
new SQLiteParameter("@IsValid", dwp.mIsValid),
new SQLiteParameter("@IsFinish", dwp.mIsDone)
};
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
//command.Parameters.Clear();
}
}
transaction.Commit();
}
}
using (SQLiteTransaction transaction2 = connection.BeginTransaction())
{
sql = "INSERT INTO ProjFBInfo " +
" (StubIndex,ParentID,ProjectID,IsFinish,SerialNO,Name,Type)" +
" values(@StubIndex,@ParentID,@ProjectID,@IsFinish,@SerialNO,@Name,@Type)";
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
foreach (CRailwayProject p in cl)
{
if (p.mPierList.Count == 0)
{
continue;
}
foreach (CRailwayPier dwp in p.mPierList)
{
for (int i = 0; i < dwp.mStubDone.Count; i++)
{
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@StubIndex", i),
new SQLiteParameter("@ParentID", dwp.mDWID),
new SQLiteParameter("@ProjectID", dwp.mParentID),
new SQLiteParameter("@IsFinish", dwp.mStubDone[i]),
new SQLiteParameter("@SerialNO", dwp.mStubSNOs[i]),
new SQLiteParameter("@Name", i + ""),
new SQLiteParameter("@Type", 2 + "")
};
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
//command.Parameters.Clear();
}
for (int i = 0; i < dwp.mBeamDone.Count; i++)
{
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@StubIndex", i),
new SQLiteParameter("@ParentID", dwp.mDWID),
new SQLiteParameter("@ProjectID", dwp.mParentID),
new SQLiteParameter("@IsFinish", dwp.mBeamDone[i]),
new SQLiteParameter("@SerialNO", dwp.mBeamSNOs[i]),
new SQLiteParameter("@Name", dwp.mBeamName[i]),
new SQLiteParameter("@Type", 1 + "")
};
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
//command.Parameters.Clear();
}
}
}
transaction2.Commit();
}
}
}
}
public int SellDiginotes(int quantity, IUser user) // apenas para as que estao disponiveis
{
Log("----- User " + user.IdUser + " wants to sell " + quantity + " diginotes. -----");
SQLiteTransaction t = _mDbConnection.BeginTransaction();
string sql = "SELECT * FROM BUYORDER WHERE not closed ORDER BY date asc";
SQLiteCommand command = new SQLiteCommand(sql, _mDbConnection);
SQLiteDataReader reader = command.ExecuteReader();
List <IOrder> orders = new List <IOrder>();
int howManyLeft = quantity;
while (reader.Read())
{
Order o1 = new Order(OrderOptionEnum.Sell, Convert.ToInt32(reader["idBuyOrder"]), Convert.ToInt32(reader["user"]), Convert.ToInt32(reader["wanted"]), Convert.ToInt32(reader["satisfied"]), Convert.ToString(reader["date"]));
Log("Considering buy order: " + o1.IdOrder + " Timestamp: " + o1.Date);
int howManyToOffer = o1.Wanted - o1.Satisfied;
if (howManyLeft > howManyToOffer)
{
o1.Satisfied = o1.Wanted;
orders.Add(o1);
howManyLeft -= howManyToOffer;
TransferDiginotes(user.IdUser, o1.IdUser, howManyToOffer);
}
else
{
o1.Satisfied += howManyLeft;
orders.Add(o1);
TransferDiginotes(user.IdUser, o1.IdUser, howManyLeft);
howManyLeft = 0;
break;
}
}
if (orders.Count > 0)
{
InsertSellOrder(quantity, user, quantity - howManyLeft, true);
}
foreach (IOrder o in orders)
{
if (o.Satisfied < o.Wanted)
{
sql = String.Format("UPDATE BUYORDER SET satisfied = {0} WHERE idBuyOrder = {1}", o.Satisfied, o.IdOrder);
}
else
{
sql = String.Format("UPDATE BUYORDER SET satisfied = {0}, closed = 1 WHERE idBuyOrder = {1}", o.Satisfied, o.IdOrder);
}
try
{
Log(sql);
command = new SQLiteCommand(sql, _mDbConnection);
command.ExecuteNonQuery();
}
catch (SQLiteException exception)
{
Log("ERROR: " + sql);
Debug.WriteLine("exception in " + exception.Source + ": '" + exception.Message + "'");
//TODO fazer try catch global para rollback
}
}
t.Commit();
foreach (IOrder o in orders)
{
IClientNotify c = GetUserChannel(o.IdUser);
if (c != null)
{
c.UpdateClientView();
}
}
_myWindow.UpdateView();
UpdateClients();
Log("User " + user.IdUser + " sold " + (quantity - howManyLeft) + " diginotes. -----");
return(quantity - howManyLeft);
}
static void Main(string[] args)
{
SQLiteConnection dbConnect;
SQLiteCommand cmd;
SQLiteDataReader dbread = null;
string inputstring;
string cmc = ""; string power = "";
StreamReader inputReader = new StreamReader("input.txt");
dbConnect = new SQLiteConnection("Data Source=C://Users/Korik/Documents/Visual Studio 2015/Projects/GAdeckbuilder/GAdeckbuilder/bin/Debug/mtg.db;");
dbConnect.Open();
string sql;
bool white = false, blue = false, black = false, red = false, green = false;
while (inputReader.Peek() != -1)
{
inputstring = inputReader.ReadLine();
sql = "SELECT * FROM cards INNER JOIN colors ON cards.name = colors.name where cards.setcode = 'BFZ' AND colors.setcode = 'BFZ' AND cards.name = '" + inputstring + "';";
// Console.WriteLine(sql);
cmd = new SQLiteCommand(sql, dbConnect);
dbread = cmd.ExecuteReader();
while (dbread.Read())
{
//Console.WriteLine(dbread[1].ToString());
white = false; blue = false; black = false; red = false; green = false;
if (Convert.ToBoolean(dbread["white"].ToString()))
{
white = true;
}
if (Convert.ToBoolean(dbread["blue"].ToString()))
{
blue = true;
}
if (Convert.ToBoolean(dbread["black"].ToString()))
{
black = true;
}
if (Convert.ToBoolean(dbread["red"].ToString()))
{
red = true;
}
if (Convert.ToBoolean(dbread["green"].ToString()))
{
green = true;
}
// Console.WriteLine(dbread["cmc"].ToString());
cmc = dbread["cmc"].ToString();
//Console.WriteLine(cmc);
power = dbread["PowerLevel"].ToString();
//Console.WriteLine(power);
}
// Console.WriteLine(inputstring + white + blue + red + black + green + cmc + power);
CardPool.Add(new Card(inputstring, white, blue, black, red, green, int.Parse(cmc), int.Parse(power)));
}
inputReader.Close();
dbread.Close();
dbConnect.Close();
foreach (Card c in CardPool)
{
// Console.WriteLine(c.White);
}
Console.ReadLine();
var selection = new EliteSelection();
var crossover = new OnePointCrossover();
var mutation = new ReverseSequenceMutation();
var fitness = new DeckFitness();
var chromosome = new DeckChromosome();
var population = new Population(400, 400, chromosome);
var ga = new GeneticAlgorithm(population, fitness, selection, crossover, mutation);
ga.Termination = new FitnessStagnationTermination(4000);
ga.CrossoverProbability = 0.80f;
ga.MutationProbability = 1.00f;
Console.WriteLine("GA running...");
ga.Start();
printDecklist(ga.BestChromosome);
Console.WriteLine("Number of Generations " + ga.GenerationsNumber);
Console.ReadLine();
Console.ReadLine();
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Pow(4, 2) >= 42))
{
{}
}
else if (!(Math.Pow(4, 2) >= 42))
{
StringBuilder text = new StringBuilder(tainted_2);
text.Replace("&", "&");
text.Replace("'", "'");
text.Replace(@"""", """);
text.Replace("<", "<");
text.Replace(">", ">");
tainted_3 = text.ToString();
}
else
{
{}
}
//flaw
string query = "SELECT * FROM '" + tainted_3 + "'";
SQLiteConnection dbConnection = null;
try{
dbConnection = new SQLiteConnection("data source=C:\\data");
SQLiteCommand command = new SQLiteCommand(query, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
public override Collection <uint> GetObjectIDsInView(Envelope bbox)
{
var objectlist = new Collection <uint>();
using (var conn = GetConnection(ConnectionString))
{
string strSql = "SELECT \"" + ObjectIdColumn + "\" ";
if (_spatiaLiteIndex != SpatiaLiteIndex.RTree)
{
strSql += ", \"" + _geometryColumn + "\" ";
}
strSql += "FROM " + Table + " WHERE ";
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSql += DefinitionQuery + " AND ";
}
strSql += GetBoxClause(bbox);
using (var command = new SQLiteCommand(strSql, conn))
{
using (var dr = command.ExecuteReader())
{
var geoReader = new GaiaGeoReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel,
_ordinates);
while (dr.Read())
{
if (dr.IsDBNull(0))
{
continue;
}
//If we didn't have a spatial index we need to compare geometries manually
if (_spatiaLiteIndex != SpatiaLiteIndex.RTree)
{
if (dr.IsDBNull(1))
{
continue;
}
var geom = geoReader.Read((byte[])dr.GetValue(1));
if (geom == null)
{
continue;
}
if (!bbox.Intersects(geom.EnvelopeInternal))
{
continue;
}
}
var id = Convert.ToUInt32(dr[0]);
objectlist.Add(id);
}
}
conn.Close();
}
}
return(objectlist);
}
public override void ExecuteIntersectionQuery(Envelope box, FeatureDataSet ds)
{
GetNonSpatialColumns();
using (var conn = GetConnection(ConnectionString))
{
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\" AS \"_smtmp_\" ";
strSql += "FROM " + Table + " WHERE ";
// Attribute constraint
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSql += DefinitionQuery + " AND ";
}
// Spatial constraint
strSql += GetBoxClause(box);
using (var cmd = new SQLiteCommand(strSql, conn))
{
using (var reader = cmd.ExecuteReader())
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new GaiaGeoReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel,
_ordinates);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
{
g = geoReader.Read((byte[])reader.GetValue(geomIndex));
}
//No geometry, no feature!
if (g == null)
{
continue;
}
//If not using RTree index we need to filter in code
if (_spatiaLiteIndex != SpatiaLiteIndex.RTree && !box.Intersects(g.EnvelopeInternal))
{
continue;
}
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow)fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
ds.Tables.Add(fdt);
}
}
}
}
public override Envelope GetExtents()
{
if (_cachedExtents != null)
{
return(new Envelope(_cachedExtents));
}
Envelope box = null;
if (_spatiaLiteIndex == SpatiaLiteIndex.RTree)
{
using (var conn = GetConnection(ConnectionString))
{
var strSQL = string.Format("SELECT \"data\" FROM \"idx_{0}_{1}_node\" WHERE \"nodeno\"=1;",
_table, _geometryColumn);
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = strSQL;
var result = cmd.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
var buffer = (byte[])result;
var node = new RTreeNode(buffer);
_cachedExtents = float.IsNaN(node.XMin)
? new Envelope()
: new Envelope(node.XMin, node.XMax, node.YMin, node.YMax);
return(new Envelope(_cachedExtents));
}
throw new Exception();
}
}
}
using (var conn = GetConnection(ConnectionString))
{
var strSQL = string.Format("SELECT \"{1}\" AS \"_smtmp_\" FROM \"{0}\"",
_table, _geometryColumn);
using (var command = new SQLiteCommand(strSQL, conn))
{
using (var dr = command.ExecuteReader())
{
if (dr.HasRows)
{
double minx = double.MaxValue,
miny = double.MaxValue,
maxx = double.MinValue,
maxy = double.MinValue;
var geoReader = new GaiaGeoReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel,
_ordinates);
while (dr.Read())
{
var geom = geoReader.Read((byte[])dr.GetValue(0));
var env = geom.EnvelopeInternal;
if (minx > env.MinX)
{
minx = env.MinX;
}
if (miny > env.MinY)
{
miny = env.MinY;
}
if (maxx < env.MaxX)
{
maxx = env.MaxX;
}
if (maxy < env.MaxY)
{
maxy = env.MaxY;
}
box = new Envelope(minx, maxx, miny, maxy);
}
dr.Close();
}
else
{
box = new Envelope();
}
}
conn.Close();
}
_cachedExtents = box;
return(new Envelope(box));
}
}
//登录账户
/// <summary>
/// 登陆
/// </summary>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public static UserAcount LoginAcount(string userName, string pwd)
{
if (pwd == "0" || string.IsNullOrEmpty(pwd))
{
return(null);
}
if (userAcount != null)
{
return(userAcount);
}
string connStr = string.Format("Data Source={0};Version=3;", @".\data\convertDB");
try
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
using (SQLiteCommand comm = new SQLiteCommand(conn))
{
//获取MD5加密后的密码
pwd = GetMd5_32bit(pwd);
comm.CommandText = "SELECT Level,NickName,ExpireTime from UserTable WHERE User=@User AND Pwd=@Pwd";
comm.Parameters.AddWithValue("@User", userName);
comm.Parameters.AddWithValue("@Pwd", pwd);
conn.Open();
SQLiteDataReader dataReader = comm.ExecuteReader();
if (dataReader.HasRows)
{
dataReader.Read();
if (DateTime.Compare(dataReader.GetDateTime(2), DateTime.Now) < 0)
{
//重置密码 为0,
//获取 加密密码
comm.Parameters.Clear();
dataReader.Close();
string newPwd = GetMd5_32bit("0");
string expireTime = "1970-01-01";
comm.CommandText = "UPDATE UserTable SET Pwd=@NewPwd,ExpireTime=@ExpireTime WHERE User=@User";
comm.Parameters.AddWithValue("@NewPwd", newPwd);
comm.Parameters.AddWithValue("@User", userName);
comm.Parameters.AddWithValue("@ExpireTime", expireTime);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//int affectInt = comm.ExecuteNonQuery();
comm.ExecuteNonQuery();
return(new UserAcount(0));
}
return(new UserAcount(userName, dataReader.GetInt16(0), 1, dataReader["NickName"].Equals(DBNull.Value) ? null : dataReader["NickName"].ToString()));
}
else
{
return(new UserAcount(2));
}
}
}
}
catch (Exception ex)
{
return(null);
}
}
public static void SaveProgressToSqlite(string dbPath, List <CRailwayProject> cl)
{
string sql = "INSERT INTO FXDict(ProjectID,ProjectDictID,ProjectDictName,DesignNum,InitNum) values (@ProjectID,@ProjectDictID,@ProjectDictName,@DesignNum,@InitNum)";
string sql2 = "INSERT INTO FXData(ProjectID,ProjectDictID,ReportDate,DictTotal) values (@ProjectID,@ProjectDictID,@ReportDate,@DictTotal)";
CSqliteWrapper db = new CSqliteWrapper(dbPath);
db.ExecuteNonQuery("DELETE from FXDict", null);
db.ExecuteNonQuery("DELETE from FXData", null);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
foreach (CRailwayProject p in cl)
{
foreach (CFXProj fx in p.FXProgress)
{
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@ProjectID", p.mProjectID),
new SQLiteParameter("@ProjectDictID", fx.fxID),
new SQLiteParameter("@ProjectDictName", fx.FxName),
new SQLiteParameter("@DesignNum", fx.TotalAmount),
new SQLiteParameter("@InitNum", fx.initAmount)
};
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
}
}
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql2;
foreach (CRailwayProject p in cl)
{
foreach (CFXProj fx in p.FXProgress)
{
for (int i = 0; i < fx.strDate.Count; i++)
{
SQLiteParameter[] para = new SQLiteParameter[]
{
new SQLiteParameter("@ProjectID", p.mProjectID),
new SQLiteParameter("@ProjectDictID", fx.fxID),
new SQLiteParameter("@ReportDate", fx.strDate[i]),
new SQLiteParameter("@DictTotal", fx.doneAmount[i])
};
command.Parameters.AddRange(para);
command.ExecuteNonQuery();
}
}
}
}
transaction.Commit();
}
}
Console.WriteLine("分项工程信息存储完成");
}
///添加用户
//创建\注册用户,独立于实例,默认level4，注册时默认，观众添加可指定
/// <summary>
/// 创建注册用户
/// </summary>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <param name="level"></param>
/// <param name="nickname">默认为空</param>
/// <returns>返回int，0：失败，1：成功，2：用户名重复</returns>
public static int CreatAcount(string userName, string pwd, int level = 4, string nickname = null)
{
//if (Level>2)
//{
//}
string connStr = string.Format("Data Source={0};Version=3;", @".\data\convertDB");
try
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
using (SQLiteCommand comm = new SQLiteCommand(conn))
{
//查询是否存在用户
comm.CommandText = "SELECT * from UserTable WHERE User=@User";
comm.Parameters.AddWithValue("@User", userName);
conn.Open();
SQLiteDataReader dataReader = comm.ExecuteReader();
if (dataReader.HasRows)//用户名重复
{
return(2);
}
//关闭DataReader的链接
dataReader.Close();
comm.Parameters.Clear();//清空参数
//获取MD5加密后的密码
pwd = GetMd5_32bit(pwd);
if (nickname != null)
{
comm.CommandText = "INSERT INTO UserTable VALUES(@User,@Pwd,@Level,@NickName)";
comm.Parameters.AddWithValue("@NickName", nickname);
}
else
{
comm.CommandText = "INSERT INTO UserTable(User,Pwd,Level) VALUES(@User,@Pwd,@Level)";
}
comm.Parameters.AddWithValue("@User", userName);
comm.Parameters.AddWithValue("@Pwd", pwd);
comm.Parameters.AddWithValue("@Level", level);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//插入用户
int affectRow = comm.ExecuteNonQuery();
if (affectRow != 1)
{
//创建用户失败
return(0);
}
else
{
return(1);
}
}
}
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// FIXME 表格已经换了，这个需要修正
/// </summary>
/// <param name="dbPath"></param>
/// <param name="cl"></param>
public static void SaveLineListToSqlite(string dbPath, List <CRailwayLine> cl)
{
string sql = "INSERT INTO ChainInfo(fromMeter,toMeter,fromID,toID,DKCode) values (@fromMeter,@toMeter,@fromID,@toID,@DKCode)";
string sql2 = "INSERT INTO MileageInfo(Mileage,Longitude,Latitude,Altitude,MileageID,MileagePrefix) values (@Mileage,@Longitude,@Latitude,@Altitude,@MileageID,@MileagePrefix)";
//CSqliteWrapper db = new CSqliteWrapper(dbPath);
int countID = 1;
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
for (int i = 0; i < cl.Count; i++)
{
int toID = countID + cl[i].mPointNum - 1;
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@fromMeter", cl[i].mStart),
new SQLiteParameter("@toMeter", cl[i].mEnd),
new SQLiteParameter("@fromID", countID),
new SQLiteParameter("@toID", toID),
new SQLiteParameter("@DKCode", cl[i].mDKCode)
};
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
//db.ExecuteNonQuery(sql, parameters);
countID = toID + 1;
}
}
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql2;
countID = 1;
bool isR;
for (int i = 0; i < cl.Count; i++)
{
isR = cl[i].mIsReverse;
for (int j = 0; j < cl[i].mPointNum; j++)
{
double mil;
if (isR)
{
mil = cl[i].mStart - cl[i].meter[j];
}
else
{
mil = cl[i].mStart + cl[i].meter[j];
}
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@Mileage", mil),
new SQLiteParameter("@Longitude", cl[i].longitude[j]),
new SQLiteParameter("@Latitude", cl[i].latitude[j]),
new SQLiteParameter("@Altitude", cl[i].altitude[j]),
new SQLiteParameter("@MileageID", countID),
new SQLiteParameter("@MileagePrefix", cl[i].mDKCode),
//new SQLiteParameter("@LongitudeMars",cl[i].longitudeMars[j]),
//new SQLiteParameter("@LatitudeMars",cl[i].latitudeMars[j])
};
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
countID++;
}
}
}
transaction.Commit();
}
}
Console.WriteLine("里程信息存储完成");
}
public static string EditarCronograma(int id_cronograma, List <string> vs)
{
string consulta = "UPDATE Cronogramas_vehiculos SET " +
"Id_vehiculo = @Id_vehiculo, " +
"Id_estado = @Id_estado, " +
"Fecha_cronograma = @Fecha_cronograma, " +
"Estado_cronograma = @Estado_cronograma " +
"WHERE Id_cronograma = @Id_cronograma ";
SQLiteConnection SqlCon = DConexion.Conex(out string rpta);
try
{
int contador = 0;
if (SqlCon == null)
{
throw new Exception(rpta);
}
SqlCon.Open();
SQLiteCommand SqlCmd = new SQLiteCommand
{
Connection = SqlCon,
CommandText = consulta,
CommandType = CommandType.Text
};
SQLiteParameter Id_cronograma = new SQLiteParameter
{
ParameterName = "@Id_cronograma",
Value = id_cronograma
};
SqlCmd.Parameters.Add(Id_cronograma);
SQLiteParameter Id_vehiculo = new SQLiteParameter
{
ParameterName = "@Id_vehiculo",
Value = Convert.ToInt32(vs[contador])
};
SqlCmd.Parameters.Add(Id_vehiculo);
contador += 1;
SQLiteParameter Id_estado = new SQLiteParameter
{
ParameterName = "@Id_estado",
Value = Convert.ToInt32(vs[contador])
};
SqlCmd.Parameters.Add(Id_estado);
contador += 1;
SQLiteParameter Fecha_cronograma = new SQLiteParameter
{
ParameterName = "@Fecha_cronograma",
Value = vs[contador]
};
SqlCmd.Parameters.Add(Fecha_cronograma);
contador += 1;
SQLiteParameter Estado_cronograma = new SQLiteParameter
{
ParameterName = "@Estado_cronograma",
Value = vs[contador].Trim().ToUpper()
};
SqlCmd.Parameters.Add(Estado_cronograma);
contador += 1;
rpta = SqlCmd.ExecuteNonQuery() >= 1 ? "OK" : "NO se ingresó el registro";
if (!rpta.Equals("OK"))
{
if (Mensaje_respuesta != null)
{
rpta = Mensaje_respuesta;
}
}
}
catch (SQLiteException ex)
{
rpta = ex.Message;
}
catch (Exception ex)
{
rpta = ex.Message;
}
finally
{
//Si la cadena SqlCon esta abierta la cerramos
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(rpta);
}
protected SqliteQueryCommand(SQLiteCommand sqliteCommand)
{
this._commandFunc = null;
this._sqliteCommand = sqliteCommand;
}
public static DataTable BuscarCronogramas(string tipo_busqueda, string texto_busqueda1, string texto_busqueda2,
out string rpta)
{
StringBuilder consulta = new StringBuilder();
consulta.Append("SELECT * FROM Vehiculos vh " +
"LEFT JOIN Cronogramas_vehiculos crvh ON vh.Id_vehiculo = crvh.Id_vehiculo ");
if (tipo_busqueda.Equals("FECHA ID VEHICULO"))
{
consulta.Append("WHERE crvh.Fecha_cronograma = '@Texto_busqueda1' and crvhh.Id_vehiculo = @Texto_busqueda2 ");
}
else if (tipo_busqueda.Equals("ID CRONOGRAMA"))
{
consulta.Append("WHERE crvh.Id_cronograma = @Texto_busqueda ");
}
else if (tipo_busqueda.Equals("ID VEHICULO"))
{
consulta.Append("WHERE crvh.Id_vehiculo = @Texto_busqueda ");
}
consulta.Append("ORDER BY crvh.Id_cronograma DESC ");
DataTable DtResultado = new DataTable("Cronogramas");
SQLiteConnection SqlCon = DConexion.Conex(out rpta);
try
{
if (SqlCon == null)
{
throw new Exception(rpta);
}
SqlCon.Open();
SQLiteCommand SqlCmd = new SQLiteCommand
{
Connection = SqlCon,
CommandText = Convert.ToString(consulta),
CommandType = CommandType.Text
};
SQLiteParameter Texto_busqueda1 = new SQLiteParameter
{
ParameterName = "@Texto_busqueda1",
Size = 50,
Value = texto_busqueda1.Trim().ToUpper()
};
SqlCmd.Parameters.Add(Texto_busqueda1);
SQLiteParameter Texto_busqueda2 = new SQLiteParameter
{
ParameterName = "@Texto_busqueda2",
Size = 50,
Value = texto_busqueda2.Trim().ToUpper()
};
SqlCmd.Parameters.Add(Texto_busqueda2);
SQLiteDataAdapter SqlData = new SQLiteDataAdapter(SqlCmd);
SqlData.Fill(DtResultado);
if (DtResultado.Rows.Count < 1)
{
DtResultado = null;
}
}
catch (SQLiteException ex)
{
rpta = ex.Message;
DtResultado = null;
}
catch (Exception ex)
{
rpta = ex.Message;
DtResultado = null;
}
finally
{
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(DtResultado);
}
internal void RunImport()
{
SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=PhotoSorter.db;Version=3;");
try
{
//Get Data
m_dbConnection.Open();
string sql = "SELECT * FROM tbl_configs";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
string prefix = "";
string suffix = "";
bool useCameraMake = false;
string sfUserID = "";
string sfPasswd = "";
string sfAppID = "";
string sfSS = "";
string sfAuthID = "";
while (reader.Read())
{
this.importDir = Environment.ExpandEnvironmentVariables(reader["importDirectory"].ToString());
this.picDir = Environment.ExpandEnvironmentVariables(reader["pictureDirectory"].ToString());
this.videoDir = Environment.ExpandEnvironmentVariables(reader["videoDirectory"].ToString());
prefix = reader["prefix"].ToString();
suffix = reader["suffix"].ToString();
if (reader["includeCameraMake"] != DBNull.Value)
{
useCameraMake = Convert.ToBoolean(reader["includeCameraMake"]);
}
sfUserID = reader["sfUserID"].ToString();
if (reader["sfPasswd"] != DBNull.Value)
{
sfPasswd = Encrypting.ToInsecureString(Encrypting.DecryptString(reader["sfPasswd"].ToString()));
}
sfAppID = reader["sfAppID"].ToString();
if (reader["sfSS"] != DBNull.Value)
{
sfSS = Encrypting.ToInsecureString(Encrypting.DecryptString(reader["sfSS"].ToString()));
}
sfAuthID = reader["sfAuthID"].ToString();
}
reader.Close();
//Initialize Shutterfly
if (sfUserID != "" && sfPasswd != "" && sfAppID != "" && sfSS != "")
{
string authenticationID = Shutterfly.getAuthenticationID(sfUserID, sfPasswd, sfAppID, sfSS, sfAuthID);
if (!authenticationID.StartsWith("Failed:") && authenticationID != sfAuthID)
{
string sfsql = String.Format("UPDATE tbl_configs SET sfAuthID = '{0}' WHERE user_pk = 1", authenticationID);
SQLiteCommand sqlcmd = new SQLiteCommand(sql, m_dbConnection);
sqlcmd.ExecuteNonQuery();
sfAuthID = authenticationID;
}
}
//Get video extensions
List <string> videoEXT = new List <string>();
string query = "SELECT * FROM tbl_videos ORDER BY extension";
SQLiteCommand cmd = new SQLiteCommand(query, m_dbConnection);
SQLiteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
videoEXT.Add(rdr["extension"].ToString());
}
rdr.Close();
if (Directory.Exists(importDir))
{
if (!Directory.Exists(picDir))
{
Directory.CreateDirectory(picDir);
}
if (!Directory.Exists(videoDir))
{
Directory.CreateDirectory(videoDir);
}
FileInfo[] Pictures = (from fi in new DirectoryInfo(importDir).GetFiles("*.*", SearchOption.AllDirectories)
where !videoEXT.Contains(fi.Extension.ToLower())
select fi)
.ToArray();
FileInfo[] Videos = (from fi in new DirectoryInfo(importDir).GetFiles("*.*", SearchOption.AllDirectories)
where videoEXT.Contains(fi.Extension.ToLower())
select fi)
.ToArray();
List <string> moveErrors = new List <string>();
List <string> uploadErrors = new List <string>();
foreach (FileInfo pictureFile in Pictures)
{
processPicture(pictureFile, sfAuthID, sfAppID, prefix, suffix, useCameraMake, ref moveErrors, ref uploadErrors);
}
foreach (FileInfo videoFile in Videos)
{
processVideo(videoFile, prefix, suffix, useCameraMake, ref moveErrors);
}
if (moveErrors.Count > 0 || uploadErrors.Count > 0)
{
//sendEmail(moveErrors, uploadErrors);
}
cleanUp();
}
else
{
string error = "Import directory does not exist. Check your settings.";
}
}
catch (Exception ex) { }
finally
{
m_dbConnection.Close();
}
}
public static string InsertarCronograma(out int id_cronograma, List <string> vs)
{
id_cronograma = 0;
string consulta = "INSERT INTO Cronogramas_vehiculos(Id_vehiculo, Id_estado, " +
"Fecha_cronograma, Estado_cronograma) " +
"VALUES(@Id_vehiculo, @Id_estado, " +
"@Fecha_cronograma, @Estado_cronograma); " +
"SELECT last_insert_rowid() ";
SQLiteConnection SqlCon = DConexion.Conex(out string rpta);
try
{
int contador = 0;
if (SqlCon == null)
{
throw new Exception(rpta);
}
SqlCon.Open();
SQLiteCommand SqlCmd = new SQLiteCommand
{
Connection = SqlCon,
CommandText = consulta,
CommandType = CommandType.Text
};
SQLiteParameter Id_cronograma = new SQLiteParameter
{
ParameterName = "@Id_cronograma",
Value = ParameterDirection.Output
};
SqlCmd.Parameters.Add(Id_cronograma);
SQLiteParameter Id_vehiculo = new SQLiteParameter
{
ParameterName = "@Id_vehiculo",
Value = Convert.ToInt32(vs[contador])
};
SqlCmd.Parameters.Add(Id_vehiculo);
contador += 1;
SQLiteParameter Id_estado = new SQLiteParameter
{
ParameterName = "@Id_estado",
Value = Convert.ToInt32(vs[contador])
};
SqlCmd.Parameters.Add(Id_estado);
contador += 1;
SQLiteParameter Fecha_cronograma = new SQLiteParameter
{
ParameterName = "@Fecha_cronograma",
Value = vs[contador]
};
SqlCmd.Parameters.Add(Fecha_cronograma);
contador += 1;
SQLiteParameter Estado_cronograma = new SQLiteParameter
{
ParameterName = "@Estado_cronograma",
Value = vs[contador].Trim().ToUpper()
};
SqlCmd.Parameters.Add(Estado_cronograma);
contador += 1;
int id = Convert.ToInt32(SqlCmd.ExecuteScalar());
id_cronograma = id;
if (id > 0)
{
rpta = "OK";
}
else
{
throw new Exception("La identificación única (ID) no se obtuvo correctamente: " + rpta);
}
if (!rpta.Equals("OK"))
{
if (Mensaje_respuesta != null)
{
rpta = Mensaje_respuesta;
}
}
}
catch (SQLiteException ex)
{
rpta = ex.Message;
}
catch (Exception ex)
{
rpta = ex.Message;
}
finally
{
//Si la cadena SqlCon esta abierta la cerramos
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(rpta);
}
private DbCommand CreateCommand(DbProviderManifest manifest, DbCommandTree commandTree)
{
if (manifest == null)
throw new ArgumentNullException("manifest");
if (commandTree == null)
throw new ArgumentNullException("commandTree");
SQLiteCommand command = new SQLiteCommand();
try
{
List<DbParameter> parameters;
CommandType commandType;
command.CommandText = SqlGenerator.GenerateSql((SQLiteProviderManifest)manifest, commandTree, out parameters, out commandType);
command.CommandType = commandType;
// Get the function (if any) implemented by the command tree since this influences our interpretation of parameters
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = ((DbFunctionCommandTree)commandTree).EdmFunction;
}
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair<string, TypeUsage> queryParameter in commandTree.Parameters)
{
SQLiteParameter parameter;
// Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and
// type trump user-defined facets and type in the EntityCommand).
FunctionParameter functionParameter;
if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter))
{
parameter = CreateSqlParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value);
}
else
{
parameter = CreateSqlParameter(queryParameter.Key, queryParameter.Value, ParameterMode.In, DBNull.Value);
}
command.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which
// does not support user parameters, where there is no risk of name collision)
if (null != parameters && 0 < parameters.Count)
{
if (!(commandTree is DbInsertCommandTree) &&
!(commandTree is DbUpdateCommandTree) &&
!(commandTree is DbDeleteCommandTree))
{
throw new InvalidOperationException("SqlGenParametersNotPermitted");
}
foreach (DbParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
}
catch
{
command.Dispose();
throw;
}
}
/// <summary>
/// Diese Methode speichert die Elemente, welche in der Listbox angezeigt wird als .csv ab
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Speichern_Click(object sender, EventArgs e)
{
try
{
//Diser string beeinhaltet die Einschränkung für die SQL - Abfrage
string bedingung = "";
//Durch die Combobox kann die Schwierigkeitsstufe gewählt werden
switch (this.cbHighscore.SelectedItem.ToString())
{
case ("Alle"):
bedingung = "";
this.schwierigkeit = "Alle";
break;
case ("Einfach"):
bedingung = "where Schwierigkeit = 'Einfach'";
this.schwierigkeit = "Einfach";
break;
case ("Normal"):
bedingung = "where Schwierigkeit = 'Normal'";
this.schwierigkeit = "Normal";
break;
case ("Schwer"):
bedingung = "where Schwierigkeit = 'Schwer'";
this.schwierigkeit = "Schwer";
break;
case ("Sehr Schwer"):
bedingung = "where Schwierigkeit = 'Sehr Schwer'";
this.schwierigkeit = "Sehr Schwer";
break;
case ("Veteran"):
bedingung = "where Schwierigkeit = 'Veteran'";
this.schwierigkeit = "Veteran";
break;
}
//Der Dialog zum Abspeichern wird geöffnet
SaveFileDialog save = new SaveFileDialog();
//Die Datei wird nur als .csv abgespeichert
save.DefaultExt = "*.csv";
save.Filter = "CSV-Dateien (*.csv)|*.csv|Alle Dateien (*.*)|*.*";
DialogResult dialogRes = save.ShowDialog();
//Nun wird das Aussehen der .csv Datei festgelegt
if (dialogRes == DialogResult.OK)
{
String pfad = save.FileName;
FileInfo info = new FileInfo(pfad);
FileStream streamer = info.Open(FileMode.OpenOrCreate, FileAccess.Write, FileShare.None);
StreamWriter writer = new StreamWriter(streamer);
//Verbindung zur Datenbank
verbindung.ConnectionString = "Data Source=" + datenquelle;
verbindung.Open();
SQLiteCommand command = new SQLiteCommand(verbindung);
//Kommandotext, der sich je nach ausgewähltem Index der Combobox verändert
command.CommandText = "Select Name, Score, Schwierigkeit from Highscore " + bedingung + " order by Score desc";
command.ExecuteNonQuery();
SQLiteDataReader reader = command.ExecuteReader();
int platz = 1;
//Die Datenbank wird ausgelesen und gleichzeitig in die .csv Datei geschrieben
if (reader.HasRows)
{
//Kopfzeile der .csv Datei
writer.WriteLine("Highscoreliste fuer Schwierigkeitstufe: " + schwierigkeit);
writer.WriteLine();
writer.WriteLine();
writer.Write("Plazierung;");
writer.Write("Spielername;");
if (bedingung == "")
{
writer.Write("Score;");
writer.WriteLine("Schwierigkeit;");
writer.WriteLine();
}
else
{
writer.WriteLine("Score;");
writer.WriteLine();
}
//Lesen und Schreiben
while (reader.Read())
{
writer.Write(platz.ToString() + ". Platz;");
writer.Write(reader.GetString(reader.GetOrdinal("Name")) + ";");
if (bedingung == "")
{
writer.Write(reader.GetInt32(reader.GetOrdinal("Score")) + ";");
writer.WriteLine(reader.GetString(reader.GetOrdinal("Schwierigkeit")) + ";");
}
else
{
writer.WriteLine(reader.GetInt32(reader.GetOrdinal("Score")) + ";");
}
platz++;
}
}
//Verbindung und Writer wird geschlossen
writer.Close();
verbindung.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Highscore konnte nicht abgespeichert werden");
}
}
/// <summary>
/// By passing an Entity type, this method will use the Attribute's
/// attached to each of the entities properties to generate an
/// SQL command, that will create a table on the database.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="flags">Additional flags for SQL generation</param>
public static void CreateTable <TEntity>(this SQLiteContext context, TableCreationOptions flags = TableCreationOptions.None)
where TEntity : class
{
// Get our table mapping
Type entityType = typeof(TEntity);
TableMapping table = EntityCache.GetTableMap(entityType);
// Column defined foreign keys
List <AttributeInfo> withFKs = new List <AttributeInfo>();
// -----------------------------------------
// Begin the SQL generation
// -----------------------------------------
StringBuilder sql = new StringBuilder("CREATE ");
sql.AppendIf(flags.HasFlag(TableCreationOptions.Temporary), "TEMP ");
sql.Append("TABLE ");
sql.AppendIf(flags.HasFlag(TableCreationOptions.IfNotExists), "IF NOT EXISTS ");
sql.AppendLine($"{context.QuoteIdentifier(table.TableName)} (");
// -----------------------------------------
// Append attributes
// -----------------------------------------
foreach (var colData in table.Columns)
{
// Get attribute data
AttributeInfo info = colData.Value;
Type propertyType = info.Property.PropertyType;
SQLiteDataType pSqlType = GetSQLiteType(propertyType);
// Start appending column definition SQL
sql.Append($"\t{context.QuoteIdentifier(colData.Key)} {pSqlType}");
// Primary Key and Unique column definition
if (info.AutoIncrement || (table.HasRowIdAlias && info.PrimaryKey))
{
sql.AppendIf(table.HasRowIdAlias && info.PrimaryKey, $" PRIMARY KEY");
sql.AppendIf(info.AutoIncrement && pSqlType == SQLiteDataType.INTEGER, " AUTOINCREMENT");
}
else if (info.Unique)
{
// Unique column definition
sql.Append(" UNIQUE");
}
// Collation
sql.AppendIf(
info.Collation != Collation.Default && pSqlType == SQLiteDataType.TEXT,
" COLLATE " + info.Collation.ToString().ToUpperInvariant()
);
// Nullable definition
bool canBeNull = !propertyType.IsValueType || (Nullable.GetUnderlyingType(propertyType) != null);
if (info.HasRequiredAttribute || (!info.PrimaryKey && !canBeNull))
{
sql.Append(" NOT NULL");
}
// Default value
if (info.DefaultValue != null)
{
sql.Append($" DEFAULT ");
// Do we need to quote this?
SQLiteDataType type = info.DefaultValue.SQLiteDataType;
if (type == SQLiteDataType.INTEGER && info.DefaultValue.Value is Boolean)
{
// Convert bools to integers
int val = ((bool)info.DefaultValue.Value) ? 1 : 0;
sql.Append($"{val}");
}
else if (info.DefaultValue.Quote)
{
sql.Append($"\"{info.DefaultValue.Value}\"");
}
else
{
sql.Append($"{info.DefaultValue.Value}");
}
}
// Add last comma
sql.AppendLine(",");
// For later use
if (info.ForeignKey != null)
{
withFKs.Add(info);
}
}
// -----------------------------------------
// Composite Keys
// -----------------------------------------
string[] keys = table.PrimaryKeys.ToArray();
if (!table.HasRowIdAlias && keys.Length > 0)
{
sql.Append($"\tPRIMARY KEY(");
sql.Append(String.Join(", ", keys.Select(x => context.QuoteIdentifier(x))));
sql.AppendLine("),");
}
// -----------------------------------------
// Composite Unique Constraints
// -----------------------------------------
foreach (var cu in table.UniqueConstraints)
{
sql.Append($"\tUNIQUE(");
sql.Append(String.Join(", ", cu.Attributes.Select(x => context.QuoteIdentifier(x))));
sql.AppendLine("),");
}
// -----------------------------------------
// Foreign Keys
// -----------------------------------------
foreach (ForeignKeyConstraint info in table.ForeignKeys)
{
// Primary table attributes
ForeignKeyAttribute fk = info.ForeignKey;
string attrs1 = String.Join(", ", fk.Attributes.Select(x => context.QuoteIdentifier(x)));
string attrs2 = String.Join(", ", info.InverseKey.Attributes.Select(x => context.QuoteIdentifier(x)));
// Build sql command
TableMapping map = EntityCache.GetTableMap(info.ParentEntityType);
sql.Append('\t');
sql.Append($"FOREIGN KEY({context.QuoteIdentifier(attrs1)}) ");
sql.Append($"REFERENCES {context.QuoteIdentifier(map.TableName)}({attrs2})");
// Add integrety options
sql.AppendIf(fk.OnUpdate != ReferentialIntegrity.NoAction, $" ON UPDATE {ToSQLite(fk.OnUpdate)}");
sql.AppendIf(fk.OnDelete != ReferentialIntegrity.NoAction, $" ON DELETE {ToSQLite(fk.OnDelete)}");
// Finish the line
sql.AppendLine(",");
}
// -----------------------------------------
// SQL wrap up
// -----------------------------------------
string sqlLine = String.Concat(
sql.ToString().TrimEnd(new char[] { '\r', '\n', ',' }),
Environment.NewLine,
")"
);
// Without row id?
if (table.WithoutRowID)
{
sqlLine += " WITHOUT ROWID;";
}
// -----------------------------------------
// Execute the command on the database
// -----------------------------------------
using (SQLiteCommand command = context.CreateCommand(sqlLine))
{
command.ExecuteNonQuery();
}
}
public UserDataSet.ArrowDataTable GetData(string query, Dictionary<string, object> paras)
{
UserDataSet.ArrowDataTable dt = new UserDataSet.ArrowDataTable();
SQLiteCommand cmd = new SQLiteCommand(query, this.Connection);
foreach (var item in paras)
{
cmd.Parameters.Add(
new SQLiteParameter(item.Key, item.Value)
);
}
Adapter.SelectCommand = cmd;
Adapter.Fill(dt);
return dt;
}
void TestSQLite2()
{
try
{
//创建一个数据库文件
string datasource = "./test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
// connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password text)";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
SQLiteCommand cmd2 = new SQLiteCommand("INSERT INTO test(username, password) VALUES('dotnetthink', ?)", conn);
cmd2.Parameters.Add("password");
byte[] password = new byte[] { 1, 2, 3, 4, 5 };
cmd2.Parameters["password"].Value = password;
cmd2.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Returns a page of errors from the databse in descending order
/// of logged time.
/// </summary>
public override int GetErrors(int pageIndex, int pageSize, IList errorEntryList)
{
if (pageIndex < 0)
throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null);
if (pageSize < 0)
throw new ArgumentOutOfRangeException("pageSize", pageSize, null);
const string sql = @"
SELECT
ErrorId,
Application,
Host,
Type,
Source,
Message,
User,
StatusCode,
TimeUtc
FROM
Error
ORDER BY
ErrorId DESC
LIMIT
@PageIndex * @PageSize,
@PageSize;
SELECT COUNT(*) FROM Error";
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
SQLiteParameterCollection parameters = command.Parameters;
parameters.Add("@PageIndex", DbType.Int16).Value = pageIndex;
parameters.Add("@PageSize", DbType.Int16).Value = pageSize;
connection.Open();
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (errorEntryList != null)
{
while (reader.Read())
{
string id = reader["ErrorId"].ToString();
Error error = new Error();
error.ApplicationName = reader["Application"].ToString();
error.HostName = reader["Host"].ToString();
error.Type = reader["Type"].ToString();
error.Source = reader["Source"].ToString();
error.Message = reader["Message"].ToString();
error.User = reader["User"].ToString();
error.StatusCode = Convert.ToInt32(reader["StatusCode"]);
error.Time = Convert.ToDateTime(reader["TimeUtc"]).ToLocalTime();
errorEntryList.Add(new ErrorLogEntry(this, id, error));
}
}
//
// Get the result of SELECT COUNT(*) FROM Page
//
reader.NextResult();
reader.Read();
return reader.GetInt32(0);
}
}
}
private static void DecryptCourse(string courseSrcDir)
{
var courseName = Path.GetFileName(courseSrcDir);
var courseDstDir = Path.Combine(OutputDir, courseName);
var hasTranscript = false;
Console.WriteLine("Processing course " + courseName + " ..");
// Reset Directory
if (Directory.Exists(courseDstDir))
{
Util.DeleteDirectory(courseDstDir);
}
Util.CreateDirectory(courseDstDir);
try
{
// Copy Image
File.Copy(Path.Combine(courseSrcDir, "image.jpg"), Path.Combine(courseDstDir, "image.jpg"));
Console.WriteLine(" > Done copying course image.");
}
catch (Exception exc)
{
Console.WriteLine("Oop. The image file must not be found. Never mind.");
}
// Read Course Info
var command =
new SQLiteCommand("select * from Course where Name=@Name", _dbConn)
{
CommandType = CommandType.Text
};
command.Parameters.Add(new SQLiteParameter("@Name", courseName));
var reader = command.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count == 0)
{
Console.WriteLine(" > Error: cannot find course in database.");
return;
}
hasTranscript = (long)dataTable.Rows[0]["HasTranscript"] == 1;
// Save Course Info to JSON
File.WriteAllText(Path.Combine(courseDstDir, "course-info.json"),
JsonConvert.SerializeObject(dataTable, Formatting.Indented));
Console.WriteLine(" > Done saving course info.");
// Read Module Info
command = new SQLiteCommand("select * from Module where CourseName=@CourseName", _dbConn)
{
CommandType = CommandType.Text
};
command.Parameters.Add(new SQLiteParameter("@CourseName", courseName));
reader = command.ExecuteReader();
dataTable = new DataTable();
dataTable.Load(reader);
Console.WriteLine(" > Found " + dataTable.Rows.Count + " module(s).");
var dataTableAsList =
JsonConvert.DeserializeObject <List <object> >(JsonConvert.SerializeObject(dataTable));
// Process Each Module
for (var i = 0; i < dataTable.Rows.Count; i++)
{
var moduleItem = dataTable.Rows[i];
Console.WriteLine(" > Processing module: " + moduleItem["Title"]);
// Get Module Dir
var moduleHash = Util.GetModuleHash(moduleItem["Name"] as string,
moduleItem["AuthorHandle"] as string);
var moduleSrcDir = Path.Combine(courseSrcDir, moduleHash);
var moduleDstDir = Path.Combine(courseDstDir,
(moduleItem["ModuleIndex"].ToString()).PadLeft(2, '0') + "." +
Util.TitleToFileName(moduleItem["Title"] as string));
if (!Directory.Exists(moduleDstDir))
{
Util.CreateDirectory(moduleDstDir);
}
// Save Module Info to JSON
File.WriteAllText(Path.Combine(moduleDstDir, "module-info.json"),
JsonConvert.SerializeObject(dataTableAsList[i], Formatting.Indented));
Console.WriteLine(" > Done saving module info.");
// Read Clip Info
var clipsCommand =
new SQLiteCommand("select * from Clip where ModuleId=@ModuleId", _dbConn)
{
CommandType = CommandType.Text
};
clipsCommand.Parameters.Add(new SQLiteParameter("@ModuleId", moduleItem["Id"]));
var clipsReader = clipsCommand.ExecuteReader();
var clipsDataTable = new DataTable();
clipsDataTable.Load(clipsReader);
// Save Clips Info to JSON
File.WriteAllText(Path.Combine(moduleDstDir, "clips-info.json"),
JsonConvert.SerializeObject(clipsDataTable, Formatting.Indented));
Console.WriteLine(" > Done saving clips info.");
// Process Each Clip
for (var j = 0; j < clipsDataTable.Rows.Count; j++)
{
var clipItem = clipsDataTable.Rows[j];
Console.WriteLine(" > Processing clip: " + clipItem["Title"]);
var clipSrc = Path.Combine(moduleSrcDir, (string)clipItem["Name"]) + ".psv";
var clipDst = Path.Combine(moduleDstDir,
(clipItem["ClipIndex"].ToString()).PadLeft(2, '0') + "." +
Util.TitleToFileName((string)clipItem["Title"])) + ".mp4";
// Decrypt Clip
Util.DecryptFile(clipSrc, clipDst);
Console.WriteLine(" > Done decrypting clip.");
// Save Transcript
if (!hasTranscript)
{
continue;
}
var transcriptsCommand =
new SQLiteCommand("select * from ClipTranscript where ClipId=@ClipId", _dbConn)
{
CommandType = CommandType.Text
};
transcriptsCommand.Parameters.Add(new SQLiteParameter("@ClipId", clipItem["Id"]));
var transcriptsReader = transcriptsCommand.ExecuteReader();
var transcriptsDataTable = new DataTable();
transcriptsDataTable.Load(transcriptsReader);
if (transcriptsDataTable.Rows.Count == 0)
{
continue;
}
// Generate Srt File
var sb = new StringBuilder();
var sequenceI = 0;
foreach (DataRow transcriptItem in transcriptsDataTable.Rows)
{
sequenceI++;
sb.Append(sequenceI + "\n");
var startMs = (long)transcriptItem["StartTime"];
var endMs = (long)transcriptItem["EndTime"];
var startTime = TimeSpan.FromMilliseconds(startMs);
var endTime = TimeSpan.FromMilliseconds(endMs);
sb.Append(startTime.ToString(@"hh\:mm\:ss") + "," + (startMs % 1000));
sb.Append(" --> ");
sb.Append(endTime.ToString(@"hh\:mm\:ss") + "," + (endMs % 1000));
sb.Append("\n");
sb.Append(string.Join("\n",
((string)transcriptItem["Text"]).Replace("\r", "").Split('\n')
.Select(text => "- " + text)));
sb.Append("\n\n");
}
File.WriteAllText(clipDst + ".srt", sb.ToString());
Console.WriteLine(" > Done saving subtitles.");
}
}
}
private void LoadDevices()
{
this.deviceList.Clear();
using (SQLiteConnection connection = new SQLiteConnection(database))
{
connection.Open();
string strainStatement = "select RemoteIP,LocalPort,DeviceId,Type,Desc,Path,IsCalculateForce,Threshold,LocalIP from SensorInfo";
SQLiteCommand command2 = new SQLiteCommand(strainStatement, connection);
using (SQLiteDataReader reader = command2.ExecuteReader())
{
while (reader.Read())
{
string remoteIP = reader.GetString(0);
int localPort = reader.GetInt32(1);
string deviceId = reader.GetString(2);
string type = reader.GetString(3);
string description = reader.GetString(4);
string path = reader.GetString(5);
bool isCalculateForce = bool.Parse(reader.GetString(6));
double threshold = reader.GetDouble(7);
string loclaIP = reader.GetString(8);
string[] itemString = { description, type, deviceId, remoteIP, localPort.ToString(), path, loclaIP };
ListViewItem item = new ListViewItem(itemString);
item.Checked = true;
listView1.Items.Add(item);
ACT12x device = null;
if (type == "ACT1228CableForce")
{
device = new ACT1228CableForce(deviceId, remoteIP, loclaIP, localPort, chart1, type, path, this.database, textBoxLog, threshold, redis);
}
else if (type == "ACT12816Vibrate")
{
device = new ACT12816Vibrate(deviceId, remoteIP, loclaIP, localPort, chart1, type, path, this.database, textBoxLog, threshold, redis);
}
else if (type == "ACT1228EarthQuake")
{
device = new ACT1228EarthQuake(deviceId, remoteIP, loclaIP, localPort, chart1, type, path, this.database, textBoxLog, threshold, redis);
}
else if (type == "ACT1228Vibrate")
{
device = new ACT1228Vibrate(deviceId, remoteIP, loclaIP, localPort, chart1, type, path, this.database, textBoxLog, threshold, redis);
}
else if (type == "ACT1228CableForceV4")
{
device = new ACT1228CableForceV4(deviceId, remoteIP, loclaIP, localPort, chart1, type, path, this.database, textBoxLog, threshold, redis);
}
else if (type == "ACT1228VibrateV4")
{
device = new ACT1228VibrateV4(deviceId, remoteIP, loclaIP, localPort, chart1, type, path, this.database, textBoxLog, threshold, redis);
}
else
{
}
if (device != null)
{
this.deviceList.Add(deviceId, device);
}
}
}
strainStatement = "select ip,user,password,database,tableName from dbconfig";
SQLiteCommand command3 = new SQLiteCommand(strainStatement, connection);
using (SQLiteDataReader reader2 = command3.ExecuteReader())
{
while (reader2.Read())
{
databaseIp = reader2.GetString(0);
databaseUser = reader2.GetString(1);
databasePwd = reader2.GetString(2);
databaseName = reader2.GetString(3);
databaseTable = reader2.GetString(4);
}
}
connection.Close();
}
}
public int Update(string query, Dictionary<string, object> paras)
{
ConnectionState state = this.Connection.State;
SQLiteCommand cmd = new SQLiteCommand(query, this.Connection);
foreach (var item in paras)
{
cmd.Parameters.Add(
new SQLiteParameter(item.Key, item.Value)
);
}
if (state != ConnectionState.Open) cmd.Connection.Open();
int affected = cmd.ExecuteNonQuery();
if (state != ConnectionState.Open) cmd.Connection.Close();
return affected;
}
/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public ManagedSpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
: base(-2)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
var op = UseLike ? "LIKE" : "=";
using (var cn = new SQLiteConnection(connectionStr))
{
cn.Open();
var cm = new SQLiteCommand(
String.Format(
"SELECT \"srid\", \"coord_dimension\", \"spatial_index_enabled\" FROM \"geometry_columns\" WHERE(\"f_table_name\" {2} '{0}' AND \"f_geometry_column\" {2} '{1}');",
tablename, geometryColumnName, op), cn);
var dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
SRID = dr.GetInt32(0);
var coordDim = dr.GetFieldType(1) == typeof(long)
? dr.GetInt64(1).ToString(NumberFormatInfo.InvariantInfo)
: dr.GetString(1);
switch (coordDim)
{
case "2":
case "XY":
_ordinates = Ordinates.XY;
break;
case "3":
case "XYZ":
_ordinates = Ordinates.XYZ;
break;
case "XYM":
_ordinates = Ordinates.XYM;
break;
case "4":
case "XYZM":
_ordinates = Ordinates.XYZM;
break;
default:
throw new Exception("Cannot evaluate number of ordinate dimensions");
}
switch (dr.GetInt32(2))
{
case 1: //RTree
var indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
const string whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
GetNonSpatialColumns();
}
catch (Exception)
{
SRID = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
