public void UpdateDatabaseWithArteProps( string ConnectionString )
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString );
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn );
command.Transaction = transaction;
foreach ( var a in ArteList ) {
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateNames );
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateDescs );
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if ( a.Type == Arte.ArteType.Generic ) {
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateStatus );
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
private DBController()
{
database = DB.GetInstance();
GetLinkIdCommand = database.PrepareStatement(@"SELECT ID From URL WHERE URL=@link");
AddLinkCommand = database.PrepareStatement(@"INSERT INTO URL(URL,Title,OutBound,TIMESTAMP) VALUES (@link,@title,@outbound,date('now'))");
AddPageVectorCommand = database.PrepareStatement(@"INSERT INTO VECTOR(LID,Keyword,Rank) VALUES (@linkid, @keyword, @keywordrank)");
AddPageImagesCommand = database.PrepareStatement(@"INSERT INTO Images(LID,ImageLink,ImageAlt) VALUES (@linkid, @imagelink, @imagealt)");
AddPageStepsCommand = database.PrepareStatement(@"INSERT INTO StepsSuggestions(LID,Header,List) VALUES (@linkid,@header,@list)");
AddPageContentCommand = database.PrepareStatement(@"INSERT INTO PageContent SELECT ID, @content FROM URL WHERE URL=@link ");
CheckLinkCountCommand = database.PrepareStatement(@"SELECT COUNT(*) FROM URL WHERE URL=@link");
UpdateLinkInBoundCommand = database.PrepareStatement(@"UPDATE URL SET InBound=InBound+1 WHERE URL=@link ");
UpdateLinkDateCommand = database.PrepareStatement(@"UPDATE URL SET TIMESTAMP = date('now') WHERE URL = @link");
UpdateLinkTitleCommand = database.PrepareStatement(@"UPDATE URL SET Title = @title WHERE URL=@link");
DeleteOldLinkVectorCommand = database.PrepareStatement(@"DELETE FROM VECTOR WHERE LID = (SELECT ID FROM URL WHERE URL=@link)");
DeleteOldPageContentCommand = database.PrepareStatement(@"DELETE FROM PageContent WHERE LID = (SELECT ID FROM URL WHERE URL=@link)");
DeleteOldPageStepsCommand = database.PrepareStatement(@"DELETE FROM StepsSuggestions WHERE LID = (SELECT ID FROM URL WHERE URL=@link)");
LinkTable = new object();
ImageTable = new object();
LinkTableRead = new object();
VectorTable = new object();
PageContentTable = new object();
StepsTable = new object();
Prepare();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
#region Check direcrories
System.IO.DirectoryInfo DataTemp = new System.IO.DirectoryInfo(Environment.CurrentDirectory + "\\Data\\Temp");
if (!DataTemp.Exists)
{
DataTemp.Create();
Log.Message("Folder Data\\Temp crated.");
}
System.IO.DirectoryInfo DataDataBase = new System.IO.DirectoryInfo(Environment.CurrentDirectory + "\\Data\\DataBase");
if (!DataDataBase.Exists || !System.IO.File.Exists(Consts.DBPath))
{
DataDataBase.Create();
Log.Message("Folder Data\\DataBase crated.");
System.Data.SQLite.SQLiteConnection db = new System.Data.SQLite.SQLiteConnection($"Data source={Consts.DBPath}; Version=3");
db.Open();
var cmd = new System.Data.SQLite.SQLiteCommand("CREATE TABLE Songs (Name TEXT NOT NULL UNIQUE, Lang TEXT NOT NULL, Number INTEGER (0, 2500) NOT NULL, Category TEXT NOT NULL, MP3 BLOB NOT NULL, Popularity INTEGER NOT NULL DEFAULT(0));", db);
cmd.ExecuteNonQuery();
Log.Message("File Data\\Temp\\Songs.db crated.");
db.Close();
}
System.IO.DirectoryInfo log = new System.IO.DirectoryInfo(Environment.CurrentDirectory + "\\Data\\Log");
if (!log.Exists)
{
log.Create();
}
UpdateSongs();
#endregion
Log.Message("Window Loaded.");
GC.Collect();
}
public static DataTable ExecuteCmdTable(System.Data.SQLite.SQLiteCommand cmd)
{
System.Data.ConnectionState origSate = cmd.Connection.State;
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
DataTable dt;
System.Data.SQLite.SQLiteDataReader dr;
dt = new DataTable();
dr = cmd.ExecuteReader();
dt.Load(dr);
dr.Close();
dr = null;
if (origSate == ConnectionState.Closed)
{
cmd.Connection.Close();
}
return(dt);
}
private void btn_cancel_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Sei sicuro di voler uscire? Le modifiche apportatate non verranno salvate",
"Attenzione", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
//return to stored data
if (preChargeNeed)
{
tableRate.Clear();
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "Select * from Rata where CodIscritto='" + (Ntess) + "'";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableRate.Rows.Add(reader["Costo"], reader["PagamentoAvv"], reader["DataPagam"]);
}
}
conn.Close();
}
}
}
this.Close();
}
}
private void closeRaffle(Boolean dontEmitMessages = false)
{
string lotNumberSelectQuery = "update LotObjectsInWorld set LotDrawNumber = 0 where ObjectGuid = '" + objectGuid + "'";
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + HttpContext.Current.Server.MapPath("~/App_Data/Raffle.db")))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = lotNumberSelectQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery();
if (!dontEmitMessages)
{
pageResponse.Write("CLOSED");
pageResponse.Flush();
pageResponse.SuppressContent = true;
}
con.Close();
}
}
}
// POST api/values
public void Post([FromBody] Object value)
{
//strings.Add(value);
DataClass001 dataClass001 = System.Text.Json.JsonSerializer.Deserialize <DataClass001>(value.ToString());
string insertNewRowQuery = @"INSERT INTO MyTable (Key,Value) Values ('"
+ dataClass001.Key + "','" + dataClass001.Value + "')";
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|/databaseFile2.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); // Open the connection to the database
com.CommandText = insertNewRowQuery; // Set CommandText to our query that will insert a row into the table
com.ExecuteNonQuery(); // Execute the query
/*com.CommandText = "Select * FROM MyTable"; // Select all rows from our database table
*
* using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
* {
* while (reader.Read())
* {
* Console.WriteLine(reader["Key"] + " : " + reader["Value"]); // Display the value of the key and value column for every row
* }
* }*/
conn.Close(); // Close the connection to the database
}
}
}
/// <summary>
/// Recupera registro de associacao pelo id do servidor
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <ServidorPublicacao> GetServidor(int id)
{
List <ServidorPublicacao> retorno = null;
using (var comm = new System.Data.SQLite.SQLiteCommand(sqliteConnection))
{
comm.CommandText = "SELECT t1.*," +
"t2.vhr_nome as vhr_nomeservidor " +
"FROM tba_servidor_publicacao t1 " +
"INNER JOIN tbl_servidor t2 ON t1.int_idfservidor = t2.int_idaservidor " +
"WHERE t1.int_idfservidor = " + id;
var adapter = new System.Data.SQLite.SQLiteDataAdapter(comm);
var dataTable = new System.Data.DataTable();
adapter.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
retorno = new List <ServidorPublicacao>();
foreach (DataRow linha in dataTable.Rows)
{
retorno.Add(new ServidorPublicacao(linha));
}
}
}
return(retorno);
}
public List <String> checkProjects()
{
List <String> list = new List <String>();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM LASTPROJECTS";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["address"]);
if (!File.Exists(reader["address"].ToString()))
{
list.Add(reader["address"].ToString());
}
}
}
con.Close();
}
}
if (list.Count > 0)
{
removeProject(list);
}
return(getLastProjects());
}
public static DbCommand GetDbCommand(DbConnection conn)
{
DbCommand cmd = null;
if (conn is System.Data.SqlClient.SqlConnection)
{
cmd = new System.Data.SqlClient.SqlCommand();
}
if (conn is System.Data.OleDb.OleDbConnection)
{
cmd = new System.Data.OleDb.OleDbCommand();
}
if (conn is Devart.Data.Oracle.OracleConnection)
{
cmd = new Devart.Data.Oracle.OracleCommand();
}
if (conn is System.Data.SQLite.SQLiteConnection)
{
cmd = new System.Data.SQLite.SQLiteCommand();
}
if (conn is MySql.Data.MySqlClient.MySqlConnection)
{
cmd = new MySql.Data.MySqlClient.MySqlCommand();
}
return cmd;
}
void DB.IDB.Insert(object obj)
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 8000;
//
string sql = "";
string fields = "";
string values = "";
foreach (System.Reflection.PropertyInfo p in obj.GetType().GetProperties())
{
if (fields == "")
{
fields += p.Name;
values += "@" + p.Name;
}
else
{
fields += "," + p.Name;
values += "," + "@" + p.Name;
}
}
sql = "insert into " + DB.ReflectionHelper.GetDataTableNameByModel(obj) + "(" + fields + ")values(" + values + ")";
cmd.CommandText = sql;
cmd.Parameters.AddRange(ModelToSqlParameters(obj));
//
cmd.ExecuteScalar();
}
public void UpdateDatabaseWithArteProps(string ConnectionString)
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(ConnectionString);
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn);
command.Transaction = transaction;
foreach (var a in ArteList)
{
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateNames);
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateDescs);
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if (a.Type == Arte.ArteType.Generic)
{
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime(DateTime.UtcNow) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateStatus);
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
/// <summary>
/// 执行sql集
/// </summary>
/// <param name="sqls">要执行sql集</param>
public static void ExecuteNonQuery(string conn, List <string> sqls)
{
using (System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection(conn))
{
Conn.Open();
using (System.Data.SQLite.SQLiteTransaction transaction = Conn.BeginTransaction())
{
using (System.Data.SQLite.SQLiteCommand command = Conn.CreateCommand())
{
try
{
foreach (string sql in sqls)
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
Conn.Close();
}
}
private void chargeMessages()
{
int i = 0;
messageList.Clear();
clearStrings();
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "SELECT * FROM Messaggio JOIN User ON Messaggio.Id=User.Id ORDER BY Messaggio.Cod DESC";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (i == 10)
{
break;
}
messageList.Add(new Tuple <int, string, string, string>(Int16.Parse(reader["Cod"].ToString()), reader["Username"].ToString(), reader["Oggetto"].ToString(), reader["Text"].ToString()));
i++;
}
while (i < 10)
{
messageList.Add(new Tuple <int, string, string, string>(0, "Nessuno", "Nessuno", "Vuoto"));
i++;
}
}
conn.Close();
}
}
}
/// <summary>
/// Returns the article from its reference
/// </summary>
/// <param name="refArticle"></param>
/// <returns>the article</returns>
public Models.Article Get_Article(string refArticle)
{
Models.Article Article = new Models.Article();
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT RefArticle, Description, SousFamilles.Nom as SousFamille, m.Nom as Marque, PrixHT, Quantite FROM Articles natural join SousFamilles inner join Marques m on m.RefMarque = Articles.refMarque WHERE RefArticle = ?";
System.Data.SQLite.SQLiteParameter Id_Parameter = new System.Data.SQLite.SQLiteParameter();
Id_Parameter.Value = refArticle;
cmd.Parameters.Add(Id_Parameter);
System.Data.SQLite.SQLiteDataReader Articles_Reader = cmd.ExecuteReader();
int Article_Index = Articles_Reader.GetOrdinal("RefArticle");
int Description_Index = Articles_Reader.GetOrdinal("Description");
int Sub_Familly_Index = Articles_Reader.GetOrdinal("SousFamille");
int Brand_Index = Articles_Reader.GetOrdinal("Marque");
int Price_Index = Articles_Reader.GetOrdinal("PrixHT");
int Quantity_Index = Articles_Reader.GetOrdinal("Quantite");
if (Articles_Reader.Read())
{
Models.Article A = new Models.Article();
Article.Ref_Article = Articles_Reader.GetString(Article_Index);
Article.Description = Articles_Reader.GetString(Description_Index);
Article.Sub_Familly_Name = Articles_Reader.GetString(Sub_Familly_Index);
Article.Brand_Name = Articles_Reader.GetString(Brand_Index);
Article.Price_HT = Articles_Reader.GetFloat(Price_Index);
Article.Quantity = Articles_Reader.GetInt32(Quantity_Index);
}
return(Article);
}
/// <summary>
/// Returns the list of all the articles
/// </summary>
/// <returns>list of all the articles</returns>
public List <Models.Article> Get_Articles_List()
{
List <Models.Article> Articles = new List <Models.Article>();
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT RefArticle, Description, SousFamilles.Nom as SousFamille, m.Nom as Marque, PrixHT, Quantite FROM Articles natural join SousFamilles inner join Marques m on m.RefMarque = Articles.refMarque";
System.Data.SQLite.SQLiteDataReader Articles_Reader = cmd.ExecuteReader();
int Article_Index = Articles_Reader.GetOrdinal("RefArticle");
int Description_Index = Articles_Reader.GetOrdinal("Description");
int Sub_Familly_Index = Articles_Reader.GetOrdinal("SousFamille");
int Brand_Index = Articles_Reader.GetOrdinal("Marque");
int Price_Index = Articles_Reader.GetOrdinal("PrixHT");
int Quantity_Index = Articles_Reader.GetOrdinal("Quantite");
while (Articles_Reader.Read())
{
Models.Article A = new Models.Article();
A.Ref_Article = Articles_Reader.GetString(Article_Index);
A.Description = Articles_Reader.GetString(Description_Index);
A.Sub_Familly_Name = Articles_Reader.GetString(Sub_Familly_Index);
A.Brand_Name = Articles_Reader.GetString(Brand_Index);
A.Price_HT = Articles_Reader.GetFloat(Price_Index);
A.Quantity = Articles_Reader.GetInt32(Quantity_Index);
Articles.Add(A);
}
return(Articles);
}
public List <AccountVerticalData> GetAccountQuarterWiseData(string Month, string Year, string AccountName)
{
List <Account> accountList = new List <Account>();
Account account;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(Class1.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select AccountID,AccountName,AccountDescription FROM Accounts"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
account = new Account();
account.AccountID = Convert.ToInt32(reader["AccountID"]);
account.AccountName = Convert.ToString(reader["AccountName"]);
account.AccountDescription = Convert.ToString(reader["AccountDescription"]);
accountList.Add(account);
}
}
con.Close(); // Close the connection to the database
}
}
return(accountList);
}
catch (Exception ex)
{
return(null);
}
}
private void displayPersonDetail(string personNo)
{
//
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
//
string sql_findInfo = string.Format("select * from RentPersonInfo where personCardNum = '{0}'", personNo);
//
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_findInfo;
cmd.Connection = conn;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
//借书人姓名
string personName = reader.GetString(0);
string personNum = reader.GetString(1);
string mobile = reader.GetString(3);
label_personDetail.Text = "借书人详细信息：" + "\r\n" + "\r\n" + "姓名：" + personName + "\r\n" + "\r\n" + "身份证号：" + personNum + "\r\n" + "\r\n" + "手机号：" + mobile;
}
//
reader.Close();
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
private List <Stavka> LstStavki()
{
string joinQuery =
@"SELECT Stavka, Opis FROM [Stavki] AS S ORDER BY Stavka;";
List <Stavka> _lstStavka = new List <Stavka>();
string connStr = "Data Source = E:\\vs2017\\svPloter\\eOFIdata.db; Version = 3;";
//System.Data.SQLite.SQLiteConnection.CreateFile("Data Source = Config\\Data\\eOFIdata.db; Version = 3;");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(connStr))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = joinQuery; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Stavka s = new Stavka();
s.eKod = reader["Stavka"].ToString();
s.Vrska = reader["Opis"].ToString();
_lstStavka.Add(s);
}
}
con.Close(); // Close the connection to the database
}
}
return(_lstStavka);
}
private List <string> getCompanyList()
{
List <string> COs = new List <string>();
System.Data.SQLite.SQLiteConnection con = Sql.GetConnection();
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + _dbasePath))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "Select * FROM CompanyTable";
try
{
//con.Open();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
string c = (reader["Company"] as string) + "|" + (reader["Street1"] as string) + "|" + (reader["Street2"] as string) + "|" + (reader["City"] as string) + "|" + (reader["State"] as string) + "|" + (reader["Zip"] as string) + "|" + (reader["Phone"] as string) + "|" + (reader["Fax"] as string);
COs.Add(c);
}
}
}
catch (SqlException ex)
{
MessageBox.Show(this, "Database error: " + ex.Message);
}
}
}
return(COs);
}
//private int updatePartRow(string Table, string Name, string Columns)
//{
// using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + _dbasePath))
// {
// using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
// {
// #region update string for OrderTable
// com.CommandText =
// "UPDATE ReportsTable SET Category = @Category, Description = @Description, Price = @Price " +
// "Where Category = @Category and Description = @Description";
// #endregion
// #region Parameters for OrderTable
// com.Parameters.AddWithValue("@Category", Category);
// com.Parameters.AddWithValue("@Description", Description);
// com.Parameters.AddWithValue("@Price", Price);
// #endregion
// try
// {
// con.Open(); // Open the connection to the database
// return com.ExecuteNonQuery(); // Execute the query
// }
// catch (SqlException ex)
// {
// MessageBox.Show(this, "Database error: " + ex.Message);
// return 0;
// }
// }
// }
//}
private int insertReportRow(string TableName, string Name, string Columns)
{
System.Data.SQLite.SQLiteConnection con = Sql.GetConnection();
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + _dbasePath))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
#region insert string for ReportsTable
com.CommandText = "INSERT INTO ReportsTable (TableName, Name, Columns) VALUES (@TableName, @Name, @Columns)";
#endregion
#region Parameters for ReportsTable
com.Parameters.AddWithValue("@TableName", TableName);
com.Parameters.AddWithValue("@Name", Name);
com.Parameters.AddWithValue("@Columns", Columns);
#endregion
try
{
//con.Open(); // Open the connection to the database
return(com.ExecuteNonQuery()); // Execute the query
}
catch (SqlException ex)
{
MessageBox.Show(this, "Database error: " + ex.Message);
return(0);
}
}
}
}
public List <Currency> GetCurrencyList()
{
List <Currency> currencyList = new List <Currency>();
Currency currency;
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = "Select CurrencyID,CurrencyName,CurrencyFactor FROM Currencies"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
currency = new Currency();
currency.CurrencyID = Convert.ToInt32(reader["CurrencyID"]);
currency.CurrencyName = Convert.ToString(reader["CurrencyName"]);
currency.CurrencyConversionFactor = Convert.ToDecimal(reader["CurrencyFactor"]);
currencyList.Add(currency);
}
}
con.Close(); // Close the connection to the database
}
}
return(currencyList);
}
catch (Exception ex)
{
return(null);
}
}
public bool CreateCurrency(string CurrencyName, decimal CurrencyFactor)
{
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = string.Format("Select 1 from Currencies where CurrencyName='{0}'", CurrencyName); // Add the first entry into our database
var exists = com.ExecuteScalar();
if (exists == null)
{
com.CommandText = string.Format("INSERT INTO Currencies(CurrencyName,CurrencyFactor) Values ('{0}','{1}')", CurrencyName, CurrencyFactor); // Add the first entry into our database
com.ExecuteNonQuery();
}
else
{
return(false);
}
return(true);
}
}
}
catch (Exception ex)
{
return(false);
}
}
private void btn_elimina_Click(object sender, EventArgs e)
{
if (!tb_NomeDel.Text.Equals("") && !tb_pswDel.Equals(""))
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "DELETE FROM User WHERE Username='******'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
MessageBox.Show("Elemento eliminato con successo!", "Eliminazione avvenuta");
conn.Close();
}
}
LoadComboBox();
lockGroupBox();
}
else
{
PrintError(1);
}
}
private void btn_aggiungi_Click(object sender, EventArgs e)
{
if (!tb_nomeAdd.Text.Equals("") && !tb_pswAdd.Equals(""))
{
if (tb_pswAdd.Text.Equals(tb_pswReAdd.Text))
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "INSERT INTO User(Username,Password) VALUES ('" + tb_nomeAdd.Text + "','" + tb_pswAdd.Text + "');";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
MessageBox.Show("Inserimento avvenuto con successo!", "Inserimento avvenuto");
conn.Close();
}
}
LoadComboBox();
lockGroupBox();
}
else
{
PrintError(2);
}
}
else
{
PrintError(0);
}
}
public int MaxQuestionOrder(Category category)
{
int result = 0;
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = @"SELECT MAX([order]) from questions WHERE categoryid=@categoryid";
SqliteParameter parameter = new SqliteParameter("@categoryid", DbType.Int32);
parameter.Value = category.Id;
command.Parameters.Add(parameter);
object val = command.ExecuteScalar();
if (val != DBNull.Value)
{
result = Convert.ToInt32(val);
}
}
}
}
catch (SqliteException ex)
{
Logger.Fatal("Unable to perform MaxQuestionOrder: \n{0}", ex);
}
return(result);
}
private void btn_modify_Click(object sender, EventArgs e)
{
if (!tb_NomeMod.Text.Equals("") && !tb_pswMod.Equals(""))
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
string command = "UPDATE User SET Username='******', Password='******' WHERE Username='******'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
MessageBox.Show("Elemento modificato con successo!", "Modifica avvenuta");
conn.Close();
}
}
LoadComboBox();
lockGroupBox();
}
else
{
PrintError(0);
}
}
/// <summary>
/// Returns the id from the family name or create a new family if none found.
/// </summary>
/// <param name="Familly_Name"></param>
/// <returns>the family id</returns>
public int Get_Or_Create_Familly(string Familly_Name)
{
int Id = Get_Familly_ID(Familly_Name);
if (Id != -1)
{
return(Id);
}
Id = Get_New_Id("Familles");
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "INSERT INTO Familles (RefFamille, Nom) VALUES (?, ?)";
System.Data.SQLite.SQLiteParameter Id_Parameter = new System.Data.SQLite.SQLiteParameter();
System.Data.SQLite.SQLiteParameter Name_Parameter = new System.Data.SQLite.SQLiteParameter();
Id_Parameter.Value = Id;
Name_Parameter.Value = Familly_Name;
cmd.Parameters.Add(Id_Parameter);
cmd.Parameters.Add(Name_Parameter);
int Nb = cmd.ExecuteNonQuery();
if (Nb != 1)
{
return(-1);
}
else
{
return(Id);
}
}
public DatabaseManager()
{
string createTableQuery = @"CREATE TABLE IF NOT EXISTS LASTPROJECTS (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[address] VARCHAR(2048) NULL
)";
string path = Path.
GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
path = path + @"\bin\Debug\databaseFile.db3";
if(!File.Exists (path))
System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ALLO");
}
public List<String> getLastProjects()
{
List<String> list = new List<String>();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM LASTPROJECTS";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["address"]);
list.Add(reader["address"].ToString());
}
}
con.Close();
}
}
return list;
}
public void DeleteCategory(int id)
{
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
// The category
command.CommandText = "DELETE FROM categories WHERE id=@id";
SqliteParameter parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = id;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
// The questions for the category
command.CommandText = "DELETE FROM questions WHERE categoryid=@id";
command.ExecuteNonQuery();
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with DeleteCategory({0}): \n{1}", id, e);
}
}
public DatabaseManager()
{
string createTableQuery = @"CREATE TABLE IF NOT EXISTS LASTPROJECTS (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[address] VARCHAR(2048) NULL
)";
string path = Path.
GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
path = path + @"\bin\Debug\databaseFile.db3";
if (!File.Exists(path))
{
System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");
}
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ALLO");
}
private List <string> getColFromTable(string colName, string tableName)
{
List <string> POs = new List <string>();
System.Data.SQLite.SQLiteConnection con = Sql.GetConnection();
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + this._dbasePath))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "Select " + colName + " FROM " + tableName;
try
{
//con.Open();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
POs.Add(reader[colName] as string);
}
}
}
catch (SqlException ex)
{
MessageBox.Show(this, "Database error: " + ex.Message);
}
}
}
return(POs);
}
static void Main1(string[] args)
{
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实现了数据库密码保护
Console.WriteLine(connstr.ToString());
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 varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink','mypassword')";
cmd.CommandText = sql;
cmd.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));
}
Console.WriteLine(sb.ToString());
}
private Connection()
{
if (!System.IO.File.Exists(DB_FILE))
{
System.Data.SQLite.SQLiteConnection.CreateFile(DB_FILE);
}
sqliteConnection = new System.Data.SQLite.SQLiteConnection($"Data Source={DB_FILE};Version=3;Password={PASSWORD};");
sqliteConnection.Open();
var sql = "CREATE TABLE torrents (tag VARCHAR(20), url VARCHAR(255), name VARCHAR(255), added DATETIME)";
var command = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
command.ExecuteNonQuery();
}
public DataBase()
{
string dbPath = @"Database.db";
if (!System.IO.File.Exists(dbPath))
System.IO.File.Copy("Database_New.db", dbPath, false);
sqlConnection = new System.Data.SQLite.SQLiteConnection( "Data Source=" + dbPath);
sqlCommand = new System.Data.SQLite.SQLiteCommand(sqlConnection);
sqlConnection.Open();
VersionUpdate();
}
private void button1_Click_1(object sender, EventArgs e)
{
bool edadp = textBox2.Text.All(Char.IsNumber);
float pesov;
bool pesop = float.TryParse(textBox6.Text, out pesov);
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "" && textBox10.Text != "" && textBox11.Text != "" && textBox12.Text != "" && textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "" && textBox17.Text != "" && textBox18.Text != "" && textBox19.Text != "" && textBox20.Text != "" && textBox21.Text != "" && textBox22.Text != "")
{
if (edadp == true)
{
if (pesop == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Expediente Set Nombre = '" + textBox1.Text + "' , Sexo = '" + comboBox1.Text + "', Edad = '" + textBox2.Text + "', Ocupacion = '" + textBox4.Text + "', Estadocivil = '" + comboBox2.Text + "', Religion = '" + textBox3.Text + "', TA = '" + textBox5.Text + "', Peso = '" + textBox6.Text + "', Tema = '" + textBox7.Text + "',FC = '" + textBox8.Text + "', FR = '" + textBox9.Text + "', EnfermedadesFamiliares = '" + textBox10.Text + "', AreaAfectada = '" + comboBox3.Text + "', Antecedentes = '" + textBox11.Text + "', Habitos = '"+textBox13.Text+"', GPAC = '"+comboBox4.Text+"', FUMFUP = '"+comboBox5.Text+"', Motivo = '"+textBox14.Text+"', CuadroClinico = '"+textBox15.Text+"', ID = '"+textBox16.Text+"', EstudiosSolicitados = '"+textBox17.Text+"', TX = '"+textBox18.Text+"', PX = '"+textBox19.Text+"', Doctor = '"+textBox20.Text+"', CP = '"+textBox21.Text+"', SSA = '"+textBox22.Text+"' Where Folio =" + foliom + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
this.Close();
}
else
{
MessageBox.Show("Solo introduzca numeros en el peso", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en la edad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ha dejado campos en blanco", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Memberではなく、Members)
var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
public static string[] GetList(string startsWith)
{
List<string> ret = new List<string>();
using (System.Data.SQLite.SQLiteConnection conn = LocalStorage.DataBaseUtility.GetConnection())
{
string SQL = "SELECT DISTINCT screenname FROM users WHERE screenname LIKE @startswith";
using (System.Data.SQLite.SQLiteCommand comm = new System.Data.SQLite.SQLiteCommand(SQL, conn))
{
comm.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@startswith", startsWith + '%'));
conn.Open();
using (System.Data.SQLite.SQLiteDataReader r = comm.ExecuteReader())
{
while (r.Read())
{
ret.Add(r.GetString(0));
}
}
conn.Clone();
}
}
return ret.ToArray();
}
static void Main(string[] args)
{
// get remote records
//GetUnclaimedBugs();
// compare datatime with DB
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;
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 varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "Insert into test values('dontnetthiink', 'passwd')";
cmd.CommandText = sql;
cmd.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("passwd:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
// insert latest records
}
public System.Data.SQLite.SQLiteDataReader ExecuteReader(string SQL, System.Data.DbType[] ParaType, object[] ParaValue)
{
try
{
odCommSelect = new System.Data.SQLite.SQLiteCommand(SQL, odc);
odCommSelect.CommandTimeout = 60 * 60 * 24;
if (ParaType != null)
{
for (int i = 0; i < ParaType.Length; i++)
{
odCommSelect.Parameters.Add("Para" + i, ParaType[i]).Value = ParaValue[i];
}
}
odc.Open();
return odCommSelect.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
}
public System.Data.DataSet GetDatas(string[] MappingTables, string[] SQLs, System.Data.DbType[][] ParaTypes, object[][] ParaValues)
{
System.Data.DataSet ds = new System.Data.DataSet();
try
{
if (MappingTables.Length != SQLs.Length) throw new Exception("表名和SQL必须一一对应!!");
odCommSelect = new System.Data.SQLite.SQLiteCommand(SQLs[0], odc);
odCommSelect.CommandTimeout = 60 * 60 * 24;
oda.SelectCommand = odCommSelect;
for (int i = 0; i < MappingTables.Length; i++)
{
odCommSelect.CommandText = SQLs[i];
odCommSelect.Parameters.Clear();
if (ParaTypes != null)
{
if (ParaTypes[i] != null)
{
for (int j = 0; j < ParaTypes[i].Length; j++)
{
odCommSelect.Parameters.Add("Para" + j, ParaTypes[i][j]).Value = ParaValues[i][j];
}
}
}
if (MaxRecordes > -1)
oda.Fill(ds, PageIndex * MaxRecordes, MaxRecordes, MappingTables[i]);
else
oda.Fill(ds, MappingTables[i]);
}
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
return ds;
}
public System.Data.DataSet GetData(string MappingTable, string SQL, System.Data.DbType[] ParaType, object[] ParaValue)
{
System.Data.DataSet ds = new System.Data.DataSet();
try
{
odCommSelect = new System.Data.SQLite.SQLiteCommand(SQL, odc);
odCommSelect.CommandTimeout = 60 * 60 * 24;
if (ParaType != null)
{
for (int i = 0; i < ParaType.Length; i++)
{
odCommSelect.Parameters.Add("Para" + i, ParaType[i]).Value = ParaValue[i];
}
}
oda.SelectCommand = odCommSelect;
if (MaxRecordes > -1)
{
oda.Fill(ds, PageIndex * MaxRecordes, MaxRecordes, MappingTable);
// 统计分页时总记录数
DataSet dsRecordCount = new DataSet();
odCommSelect.CommandText = GenCountSQL(odCommSelect.CommandText);
oda.Fill(dsRecordCount, "RecordCount");
RecordCount = Convert.ToInt32(dsRecordCount.Tables[0].Rows[0][0]);
}
else
{
oda.Fill(ds, MappingTable);
RecordCount = ds.Tables[MappingTable].Rows.Count;
}
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
return ds;
}
public void insertFile(String file)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "INSERT INTO LASTPROJECTS (address) Values ('"+ file +"')";
com.ExecuteNonQuery();
con.Close();
}
}
}
protected int ExecSQL(string SQL, System.Data.SQLite.SQLiteTransaction odTrans, System.Data.DbType[] ParaType, object[] ParaValue)
{
int re = -1;
ConnectionState OldConnState = odc.State;
try
{
if (odc.State == ConnectionState.Closed) odc.Open();
odCommExec = new System.Data.SQLite.SQLiteCommand(SQL, odc);
odCommExec.CommandTimeout = 60 * 60 * 24;
odCommExec.Transaction = odTrans;
if (ParaType != null)
{
for (int i = 0; i < ParaType.Length; i++)
{
odCommExec.Parameters.Add("Para" + i, ParaType[i]).Value = ParaValue[i];
}
}
re = odCommExec.ExecuteNonQuery();
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
finally
{
if (OldConnState == ConnectionState.Closed) odc.Close();
}
return re;
}
private bool Save()
{
System.Data.SQLite.SQLiteTransaction transaction = null;
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
try
{
transaction = connection.BeginTransaction();
// --------------------------------------------------
// Deadline
if (DeadlineIsSelectedDateRadioButton.IsChecked.Value)
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
}
else if (DeadlineIsEventRadioButton.IsChecked.Value)
{
row["deadline_id"] = (EventsComboBox.SelectedValue != null) ? EventsComboBox.SelectedValue : System.DBNull.Value;
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
taskDataAdapter.Update(dataSet, "task");
// --------------------------------------------------
// Tags
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT tasks_tags.ID, tasks_tags.task_id, tasks_tags.tag_id, tags.name
FROM tasks_tags LEFT JOIN tags ON tags.ID = tasks_tags.tag_id
WHERE tasks_tags.task_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM tasks_tags WHERE task_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM tasks_tags WHERE task_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// --------------------------------------------------
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["task_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
// --------------------------------------------------
// Sub-Tasks
foreach (System.Data.DataRow row in dataSet.Tables["sub_tasks"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["child_of"] = id;
}
subTasksDataAdapter.Update(dataSet, "sub_tasks");
dataSet.Tables["sub_tasks"].Clear();
subTasksDataAdapter.Fill(dataSet, "sub_tasks");
// --------------------------------------------------
// Clean state
IsDirty = false;
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException e)
{
if (transaction != null)
transaction.Rollback();
switch (e.ErrorCode)
{
case System.Data.SQLite.SQLiteErrorCode.Constraint:
Util.ShowFieldMustBeUniqueMessage(this, Util.ExtractColumnName(e.Message));
break;
}
}
}
try
{
this.parent.MassReloadTasks();
}
catch
{
}
return !IsDirty;
}
public string TestDb()
{
var sb = new StringBuilder();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AccountConnection"].ConnectionString;
using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM AspNetRoles";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
}
}
con.Close(); // Close the connection to the database
}
return sb.ToString();
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries);
bool needsUTFConverted = false;
_fileName = Path.GetFileName(s1[0]);
if (s1[0].EndsWith(";"))
{
_fileName = Path.GetFileNameWithoutExtension(s1[1].Substring(7, s1[1].Length - 7)) + "utf8.db";
_connectionString = "Data Source=file://" + _fileName;
s1 = new string[1] { "file://" + _fileName };
needsUTFConverted = true;
_hadToConvert = true;
}
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = _connectionString.Replace("Data Source=", "Data Source=" + Util.BasePathCombine("") + "\\");
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
if (needsUTFConverted && _hadToConvert)
{
string file = connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries)[1].Substring(7);
if (File.Exists(file))
{
//UTF16 db, gotta convert it
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + file + ";version=3;UseUTF16Encoding=True");
conn.Open();
var RetVal = new List<string>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT name FROM Sqlite_master", conn))
{
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
RetVal.Add(rdr.GetValue(i).ToString());
}
}
}
}
foreach (string table in RetVal)
{
if (TableExists(table) && !table.StartsWith("sqlite") && !table.StartsWith("idx_") && table != "aurora_migrator_version")
{
var retVal = new List<object[]>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT * FROM " + table, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
List<object> obs = new List<object>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type r = reader[i].GetType();
if (r == typeof(DBNull))
obs.Add(null);
else
obs.Add(reader[i].ToString());
}
retVal.Add(obs.ToArray());
}
}
}
try
{
if(retVal.Count > 0)
InsertMultiple(table, retVal);
}
catch { }
}
}
}
}
}
public void removeProject(List<String> paths)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
for (int i = 0; i < paths.Count; i++)
{
com.CommandText = "DELETE FROM LASTPROJECTS WHERE address = '" + paths[i] + "'";
com.ExecuteNonQuery();
}
con.Close();
}
}
}
public int ExecuteNonQuery(string sql)
{
var sqlCmd = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
return sqlCmd.ExecuteNonQuery();
}
public async Task<int> ExecuteNonQueryAsync(string sql)
{
var sqlCmd = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
return await sqlCmd.ExecuteNonQueryAsync();
}
public async Task<DbDataReader> ExecuteReaderAsync(string sql)
{
var sqlCmd = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
return await sqlCmd.ExecuteReaderAsync();
}
public void CreateDatabase()
{
var createTableQueries = new[] {
@"CREATE TABLE IF NOT EXISTS [Recipes] (
[Id] INTEGER PRIMARY KEY,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (4000) NULL,
[SecurityStamp] NVARCHAR (4000) NULL,
[PhoneNumber] NVARCHAR (4000) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([UserId], [RoleId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
[UserId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[UserId] NVARCHAR (128) NOT NULL,
[ClaimType] NVARCHAR (4000) NULL,
[ClaimValue] NVARCHAR (4000) NULL,
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
[MigrationId] NVARCHAR (150) NOT NULL,
[ContextKey] NVARCHAR (300) NOT NULL,
[Model] VARBINARY (4000) NOT NULL,
[ProductVersion] NVARCHAR (32) NOT NULL,
PRIMARY KEY ([MigrationId], [ContextKey])
);"};
var dbFile = @"d:\work\Ricettario\Ricettario\App_Data\ricettario_shared.db3";
System.Data.SQLite.SQLiteConnection.CreateFile(dbFile);
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=" + dbFile))
{
con.Open();
foreach (var createTableQuery in createTableQueries)
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
}
}
con.Close();
}
}
public void CreateUserDatabase()
{
// This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
var createTableQueries = new[] {@"CREATE TABLE IF NOT EXISTS [AspNetRoles] (
[Id] NVARCHAR (128) NOT NULL PRIMARY KEY,
[Name] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUsers] (
[Id] NVARCHAR (128) NOT NULL PRIMARY KEY,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (4000) NULL,
[SecurityStamp] NVARCHAR (4000) NULL,
[PhoneNumber] NVARCHAR (4000) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([UserId], [RoleId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
[UserId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[UserId] NVARCHAR (128) NOT NULL,
[ClaimType] NVARCHAR (4000) NULL,
[ClaimValue] NVARCHAR (4000) NULL,
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
[MigrationId] NVARCHAR (150) NOT NULL,
[ContextKey] NVARCHAR (300) NOT NULL,
[Model] VARBINARY (4000) NOT NULL,
[ProductVersion] NVARCHAR (32) NOT NULL,
PRIMARY KEY ([MigrationId], [ContextKey])
);"};
System.Data.SQLite.SQLiteConnection.CreateFile(@"d:\work\Ricettario\Ricettario\App_Data ricettario.db3"); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=d:\work\Ricettario\Ricettario\App_Data\ricettario.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
foreach (var createTableQuery in createTableQueries)
{
com.CommandText = createTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
}
//com.CommandText = "Select * FROM MyTable"; // Select all rows from our database table
//using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
//{
// while (reader.Read())
// {
// Console.WriteLine(reader["Key"] + " : " + reader["Value"]); // Display the value of the key and value column for every row
// }
//}
con.Close(); // Close the connection to the database
}
}
}
private void FillTags(long taskID)
{
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT tags.name FROM tasks_tags INNER JOIN tags ON tasks_tags.tag_id=tags.ID WHERE tasks_tags.task_id=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", taskID));
var reader = command.ExecuteReader();
var builder = new System.Text.StringBuilder();
while (reader.Read())
{
builder.AppendFormat("[{0}]", reader[0]);
}
TagsTextBox.Text = builder.ToString();
}
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
private void FillDeadline(System.Data.DataRow row)
{
FillEventsComboBox();
if (!row.IsNull("deadline_id"))
{
long deadlineID = (long) row["deadline_id"];
using (var automatic = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID));
var reader = command.ExecuteReader();
if (reader.Read())
{
DeadlineDatePicker.SelectedDate = reader.GetDateTime(0);
if (reader.IsDBNull(1))
{
DeadlineIsSelectedDateRadioButton.IsChecked = true;
}
else
{
DeadlineIsEventRadioButton.IsChecked = true;
EventsComboBox.SelectedValue = reader.GetInt64(1);
}
}
}
}
}
protected System.Data.DataTable Update(System.Data.DataTable xdt, string SourceTable, System.Data.SQLite.SQLiteTransaction odTrans)
{
ConnectionState OldConnState = odc.State;
try
{
System.Data.DataTable dtColumns = new System.Data.DataTable();
System.Data.DataTable dt;
if (odc.State == ConnectionState.Closed) odc.Open();
odCommSelect = new System.Data.SQLite.SQLiteCommand("select * from " + SourceTable + " where 1 = 2", odc);
odCommSelect.Transaction = odTrans;
System.Data.SQLite.SQLiteDataReader read = odCommSelect.ExecuteReader();
dt = read.GetSchemaTable();
System.Data.DataColumn c = new System.Data.DataColumn();
c.DataType = System.Type.GetType("System.Int16");
c.ColumnName = "type";
dtColumns.Columns.Add(c);
System.Data.DataColumn c1 = new System.Data.DataColumn();
c1.DataType = System.Type.GetType("System.String");
c1.ColumnName = "name";
dtColumns.Columns.Add(c1);
System.Data.DataColumn c2 = new System.Data.DataColumn();
c2.DataType = System.Type.GetType("System.Int32");
c2.ColumnName = "length";
dtColumns.Columns.Add(c2);
System.Data.DataColumn c3 = new System.Data.DataColumn();
c3.DataType = System.Type.GetType("System.Byte");
c3.ColumnName = "prec";
dtColumns.Columns.Add(c3);
System.Data.DataColumn c4 = new System.Data.DataColumn();
c4.DataType = System.Type.GetType("System.Byte");
c4.ColumnName = "scale";
dtColumns.Columns.Add(c4);
DataRow dr;
foreach (DataRow r in dt.Rows)
{
dr = dtColumns.NewRow();
dr["type"] = 0;
dr["name"] = r[0].ToString();
dr["length"] = Convert.ToInt32(r[2]);
dr["prec"] = Convert.ToInt32(r[3]);
dr["scale"] = Convert.ToInt32(r[4]);
dtColumns.Rows.Add(dr);
}
read.Close();
dtColumns.PrimaryKey = new System.Data.DataColumn[] { dtColumns.Columns["name"] };
string Insert, Values, Where, Update, Delete;
int length;
byte prec, scale;
System.Data.DataRow row;
odCommInsert = new System.Data.SQLite.SQLiteCommand();
odCommInsert.Connection = odc;
odCommInsert.Transaction = odTrans;
oda.InsertCommand = odCommInsert;
odCommUpdate = new System.Data.SQLite.SQLiteCommand();
odCommUpdate.Connection = odc;
odCommUpdate.Transaction = odTrans;
oda.UpdateCommand = odCommUpdate;
odCommDelete = new System.Data.SQLite.SQLiteCommand();
odCommDelete.Connection = odc;
odCommDelete.Transaction = odTrans;
oda.DeleteCommand = odCommDelete;
Insert = "INSERT INTO " + SourceTable + " ( ";
Values = " VALUES( ";
Update = "UPDATE " + SourceTable + " SET ";
Delete = "DELETE FROM " + SourceTable;
foreach (System.Data.DataColumn dc in xdt.Columns)
{
Insert += "[" + dc.ColumnName + "],";
Values += "?,";
Update += "[" + dc.ColumnName + "]=?,";
row = dtColumns.Rows.Find(dc.ColumnName);
if (row == null) throw new Exception("无效列名：" + dc.ColumnName);
prec = (byte)row["prec"];
scale = (byte)row["scale"];
length = (int)row["length"];
odCommInsert.Parameters.Add(new System.Data.SQLite.SQLiteParameter(dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Current, null));
odCommUpdate.Parameters.Add(new System.Data.SQLite.SQLiteParameter(dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Current, null));
}
Insert = Insert.Remove(Insert.Length - 1, 1); // 消除最后的","号
Values = Values.Remove(Values.Length - 1, 1); // 消除最后的","号
Update = Update.Remove(Update.Length - 1, 1); // 消除最后的","号
// 构造Where语句及其参数
Where = " WHERE (1=1) ";
if (xdt.PrimaryKey.Length == 0)
{
foreach (System.Data.DataColumn dc in xdt.Columns)
{
row = dtColumns.Rows.Find(dc.ColumnName);
if (row == null) throw new Exception("无效列名：" + dc.ColumnName);
prec = (byte)row["prec"];
scale = (byte)row["scale"];
length = (int)row["length"];
if (length == 2147483647) continue;
Where += " AND ([" + dc.ColumnName + "]=?)";
odCommUpdate.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
odCommDelete.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
}
}
else
{
foreach (System.Data.DataColumn dc in xdt.PrimaryKey)
{
row = dtColumns.Rows.Find(dc.ColumnName);
if (row == null) throw new Exception("无效列名：" + dc.ColumnName);
prec = (byte)row["prec"];
scale = (byte)row["scale"];
length = (int)row["length"];
Where += " AND ([" + dc.ColumnName + "]=?)";
odCommUpdate.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
odCommDelete.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Original_" + dc.ColumnName, (System.Data.DbType)DynamicUpdateSqlite.htDataType[dc.DataType], length, System.Data.ParameterDirection.Input, false, prec, scale, dc.ColumnName, System.Data.DataRowVersion.Original, null));
}
}
odCommInsert.CommandText = Insert + ") " + Values + ")";
odCommUpdate.CommandText = Update + Where;
odCommDelete.CommandText = Delete + Where;
oda.Update(xdt);
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
finally
{
if (OldConnState == ConnectionState.Closed) odc.Close();
}
return xdt;
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
CommonFunction common = new CommonFunction();
common.setDbPath(path);
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " WORD_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " SHINSEI_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , WORD_ID INTEGER";
sql += " , STATUS INTEGER";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " USER_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , EMP_ID INTEGER UNIQUE ";
sql += " , USER_NAME TEXT";
sql += " , KENGEN INTEGER";
sql += " , MAIL_ID TEXT";
sql += " , PASSWORD TEXT";
sql += " , MAIL_ADDRESS TEXT";
sql += " , SANKA_KAHI INTEGER";
sql += " , DELETE_FLG INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += "); ";
sql += "insert into USER_MST(USER_ID,EMP_ID,USER_NAME,KENGEN,MAIL_ID,PASSWORD,MAIL_ADDRESS,SANKA_KAHI,DELETE_FLG,VERSION) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
public string RicettarioDb()
{
var sb = new StringBuilder();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RicettarioConnection"].ConnectionString;
using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM WeekSchedule";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
}
}
con.Close(); // Close the connection to the database
}
return sb.ToString().Substring(0, 300);
}
