void drawTaskListinPage()
{
// hide the back button
NavigationPage.SetHasBackButton (this, false);
// create object of list view
var listview = new ListView {
RowHeight = 60
};
SqliteHelper helper = new SqliteHelper ();
listview.ItemsSource = helper.FetchUserTask ();
// feed data to the list item
/*listview.ItemsSource = new string[] {
"Fill iTrac",
"Commit code",
"Be amazing :P"
};*/
// display which item is visible
listview.ItemTemplate = new DataTemplate(typeof(TaskCell));
// add the list view to the current content page
Content = new StackLayout {
Children = { listview }
};
}
public Produit Get(int id)
{
var sb = new StringBuilder();
sb.Append("SELECT p.Code, p.Libelle, f.Id famId, f.Libelle famLibelle");
sb.Append("FROM produit p , famille f WHERE p.familleId = f.id AND p.Id = @id");
Produit produit = null;
using (var helper = new SqliteHelper(sb.ToString()))
{
helper.AddInParameter("id", DbType.Int32, id);
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
{
produit =
new Produit
{
Id = reader.GetIntFromReader("Id"),
Code = reader.GetIntFromReader("Code"),
Libelle = reader.GetStringFromReader("Libelle"),
Famille = new Famille
{
Id = reader.GetIntFromReader("famId"),
Libelle = reader.GetStringFromReader("famLibelle")
}
};
}
}
}
return produit;
}
public Famille Create(Famille famille)
{
using (var helper = new SqliteHelper("INSERT INTO Famille (Libelle) VALUES(@libelle); Select last_insert_rowid();"))
{
helper.AddInParameter("libelle", DbType.String, famille.Libelle);
famille.Id = helper.ExecuteCreateQuery();
}
return famille;
}
public bool Delete(Famille famille)
{
bool isDeleted;
using (var helper = new SqliteHelper("DELETE FROM famille WHERE Id = @Id"))
{
helper.AddInParameter("Id", DbType.Int32, famille.Id);
isDeleted = helper.ExecuteNonQuery();
}
return isDeleted;
}
public Produit Create(Produit produit)
{
using (var helper = new SqliteHelper("INSERT INTO produit (Code, Libelle, familleId) VALUES(@code, @libelle, @familleId); Select last_insert_rowid(); "))
{
helper.AddInParameter("code", DbType.Int32, produit.Code);
helper.AddInParameter("libelle", DbType.String, produit.Libelle);
helper.AddInParameter("familleId", DbType.Int32, produit.Famille.Id);
produit.Id = helper.ExecuteCreateQuery();
}
return produit;
}
public bool IsCanDelete(Famille famille)
{
var isCanDelete = true;
using (var helper = new SqliteHelper("SELECT Id FROM Produit Where FamilleId = @Id"))
{
helper.AddInParameter("Id", DbType.Int32, famille.Id);
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
isCanDelete = false;
}
}
return isCanDelete;
}
public bool Deliver(Commande commande)
{
bool isUpdated;
using (var helper = new SqliteHelper("UPDATE Commande set IsLivree = @IsLivree, DateLivraison = @DateLivraison WHERE Id = @Id"))
{
helper.AddInParameter("Id", DbType.Int32, commande.Id);
helper.AddInParameter("IsLivree", DbType.Int32, true);
helper.AddInParameter("DateLivraison", DbType.Date, DateTime.Now.Date);
isUpdated = helper.ExecuteNonQuery();
}
return isUpdated;
}
public bool Update(Famille famille)
{
bool isUpdated;
using (var helper = new SqliteHelper("UPDATE famille set Libelle = @Libelle WHERE Id = @Id"))
{
helper.AddInParameter("Libelle", DbType.String, famille.Libelle);
helper.AddInParameter("Id", DbType.Int32, famille.Id);
isUpdated = helper.ExecuteNonQuery();
}
return isUpdated;
}
public AddTask ()
{
InitializeComponent ();
databaseOP = new SqliteHelper ();
dtPicker.Date = DateTime.Now;
taskTime.Time = DateTime.Now.TimeOfDay;
this.Title = "New Task";
NavigationPage.SetBackButtonTitle(this,"List");
ToolbarItems.Add(new ToolbarItem {
Text = "Save",
Order = ToolbarItemOrder.Primary,
Command = new Command(() => this.saveTask()),
});
}
public List<Famille> ListAll()
{
var familles = new List<Famille>();
using (var helper = new SqliteHelper("SELECT Id, Libelle FROM Famille ORDER BY Id"))
{
using (var reader = helper.ExecuteQuery())
{
while (reader.Read())
{
familles.Add(new Famille
{
Id = reader.GetIntFromReader("Id"),
Libelle = reader.GetStringFromReader("Libelle")
});
}
}
}
return familles;
}
private bool isSyned = false; //表示是否有同义词
#endregion Fields
#region Constructors
public DbFormator(string dictDirectory)
{
try
{
idxFile = Directory.GetFiles(dictDirectory, "*.idx")[0];//获取idx文件
dictFile = Directory.GetFiles(dictDirectory, "*.dict")[0];//获取dict文件
}
catch (System.Exception ex)
{
isFileOK = false;
return;
}
string[] syns = Directory.GetFiles(dictDirectory, "*.syn");//获取syn文件
if (syns.Length == 1)//如果存在syn文件，说明字典有同义词
{
isSyned = true;
synFile = syns[0];
}
FileInfo idxInfo = new FileInfo(idxFile);//索引文件info
string dbTableName = idxInfo.Name.Substring(0, idxInfo.Name.Length - 4);//获取表名
string dbName = idxInfo.DirectoryName + "\\" + dbTableName + ".db";//获取数据库名
helper = new SqliteHelper(dbName, dbTableName);//创建sqlitehelper对象
}
public BaseController()
{
var userName = "******";
var tc = new TopConfigs();
if (string.IsNullOrWhiteSpace(tc.SessionKey))
{
using (var sqlDb = new SqliteHelper())
{
const string queryTxt = @"select * from users where U_Name=@U_Name";
var userInfo = sqlDb.ExecuteList(queryTxt, new[]
{
new SQLiteParameter("@U_Name",userName)
});
if (userInfo != null && userInfo.Count > 0)
{
var dr = userInfo[0];
tc.AppKey = dr["U_Top_AppKey"].ToString();
tc.AppSecret = dr["U_Top_AppSecret"].ToString();
tc.SessionKey = dr["U_Top_SessionKey"].ToString();
tc.TopCallBackData = HttpUtility.ParseQueryString(dr["U_Top_CallbackData"].ToString());
}
}
}
}
public List<Produit> GetByFamille(Famille famille)
{
var produits = new List<Produit>();
using (var helper = new SqliteHelper("SELECT Id, Code, Libelle FROM produit WHERE familleId = @id order by code"))
{
helper.AddInParameter("id", DbType.Int32, famille.Id);
using (var reader = helper.ExecuteQuery())
{
while (reader.Read())
{
produits.Add(
new Produit
{
Id = reader.GetIntFromReader("Id"),
Code = reader.GetIntFromReader("Code"),
Libelle = reader.GetStringFromReader("Libelle"),
Famille = famille
}
);
}
}
}
return produits;
}
public async Task <IEnumerable <LineLengthDto> > GetAllAsync()
{
return(await SqliteHelper.GetAllAsync <LineLengthDto>());
}
/// <summary>
/// Inserts a row in the i7_sflexi_values table. Returns rows affected count.
/// </summary>
/// <param name="valueGuid"> valueGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="fieldGuid"> fieldGuid </param>
/// <param name="fieldValue"> fieldValue </param>
/// <returns>int</returns>
public static int Create(
Guid valueGuid,
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
Guid itemGuid,
Guid fieldGuid,
string fieldValue)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("insert into i7_sflexi_values ({0}) values ({1});"
, @"ValueGuid
,SiteGuid
,FeatureGuid
,ModuleGuid
,ItemGuid
,FieldGuid
,FieldValue"
, @":ValueGuid
,:SiteGuid
,:FeatureGuid
,:ModuleGuid
,:ItemGuid
,:FieldGuid
,:FieldValue");
var sqlParams = new List <SqliteParameter>
{
new SqliteParameter(":SiteGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = siteGuid.ToString()
},
new SqliteParameter(":FeatureGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = featureGuid.ToString()
},
new SqliteParameter(":ModuleGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = moduleGuid.ToString()
},
new SqliteParameter(":ItemGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = itemGuid.ToString()
},
new SqliteParameter(":FieldGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = fieldGuid.ToString()
},
new SqliteParameter(":ValueGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = valueGuid.ToString()
},
new SqliteParameter(":FieldValue", DbType.Object)
{
Direction = ParameterDirection.Input, Value = fieldValue
}
};
return(Convert.ToInt32(SqliteHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
sqlParams.ToArray()).ToString()));
}
public int InsertItems(IList <HistoryEntity> items)
{
int iSuccessRows = 0;
if (0 == items.Count)
{
return(iSuccessRows);
}
string sqlInsert = string.Format(sqlInsertFormat, DataAccess.TABLE_NAME_HISTORY);
SQLiteConnection conn = new SQLiteConnection(DataAccess.ConnectionStringProfile);
conn.Open();
SQLiteTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
SQLiteParameter[] parms =
{
new SQLiteParameter("@PATH", DbType.String),
new SQLiteParameter("@NAME", DbType.String),
new SQLiteParameter("@STAR", DbType.Int32),
new SQLiteParameter("@COMMENT", DbType.String),
new SQLiteParameter("@CATEGORYIDS", DbType.String),
new SQLiteParameter("@ISDELETED", DbType.Int32),
new SQLiteParameter("@LIBRARYID", DbType.Int32)
};
try
{
//SqliteHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, parms);
foreach (HistoryEntity item in items)
{
parms[0].Value = item.Path;
parms[1].Value = item.Name;
parms[2].Value = item.Star;
parms[3].Value = item.Comment;
string ids = string.Empty;
foreach (int id in item.CategoryIDs)
{
ids += id.ToString();
}
parms[4].Value = ids;
parms[5].Value = item.IsDeleted;
parms[6].Value = item.LibraryID;
//iSuccessRows += SqliteHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);
object objRel = SqliteHelper.ExecuteScalar(DataAccess.ConnectionStringProfile, CommandType.Text, sqlInsert, parms);
if (null != objRel)
{
iSuccessRows++;
int id = Convert.ToInt32(objRel);
item.ID = id;
}
}
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new ApplicationException(e.Message);
}
finally
{
conn.Close();
}
return(iSuccessRows);
}
/// <summary>
/// Inserts a row in the mp_SurveyResponses table. Returns rows affected count.
/// </summary>
/// <param name="responseGuid"> responseGuid </param>
/// <param name="surveyGuid"> surveyGuid </param>
/// <param name="userId"> userId </param>
/// <param name="annonymous"> annonymous </param>
/// <param name="complete"> complete </param>
/// <returns>int</returns>
public static int Add(
Guid responseGuid,
Guid surveyGuid,
Guid userGuid,
bool annonymous,
bool complete)
{
#region Bit Conversion
int intAnnonymous;
if (annonymous)
{
intAnnonymous = 1;
}
else
{
intAnnonymous = 0;
}
int intComplete;
if (complete)
{
intComplete = 1;
}
else
{
intComplete = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SurveyResponses (");
sqlCommand.Append("ResponseGuid, ");
sqlCommand.Append("SurveyGuid, ");
//sqlCommand.Append("SubmissionDate, ");
sqlCommand.Append("Annonymous, ");
sqlCommand.Append("Complete, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ResponseGuid, ");
sqlCommand.Append(":SurveyGuid, ");
//sqlCommand.Append(":SubmissionDate, ");
sqlCommand.Append(":Annonymous, ");
sqlCommand.Append(":Complete, ");
sqlCommand.Append(":UserGuid )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[5];
arParams[0] = new SqliteParameter(":ResponseGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
arParams[1] = new SqliteParameter(":SurveyGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = surveyGuid.ToString();
//arParams[2] = new SqliteParameter(":SubmissionDate", DbType.DateTime);
//arParams[2].Direction = ParameterDirection.Input;
//arParams[2].Value = submissionDate;
arParams[2] = new SqliteParameter(":Annonymous", DbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = intAnnonymous;
arParams[3] = new SqliteParameter(":Complete", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = intComplete;
arParams[4] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = userGuid.ToString();
int rowsAffected = 0;
rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected);
}
/// <summary>
/// 检查数据库文件
/// </summary>
private static void CheckDb(string dbPath)
{
//如果不存在数据库文件，则创建该数据库文件
if (!File.Exists(dbPath))
{
//创建数据库
SqliteHelper.CreateDb(dbPath);
}
//sqlite帮助类实例化
_sqliteHelper = new SqliteHelper(dbPath);
}
public List<Produit> ListToUpdateAfterDelete(int codeProduit)
{
var produits = new List<Produit>();
using (var helper = new SqliteHelper("SELECT Id, Code, Libelle FROM produit WHERE Code > @Code order by code"))
{
helper.AddInParameter("Code", DbType.Int32, codeProduit);
using (var reader = helper.ExecuteQuery())
{
while (reader.Read())
{
produits.Add(
new Produit
{
Id = reader.GetIntFromReader("Id"),
Code = reader.GetIntFromReader("Code"),
Libelle = reader.GetStringFromReader("Libelle"),
}
);
}
}
}
return produits;
}
public static IDataReader GetPageOfModuleItems(
Guid moduleGuid,
int pageNumber,
int pageSize,
string searchTerm = "",
string searchField = "",
//string sortField = "",
bool descending = false)
{
StringBuilder sqlCommand = new StringBuilder();
if (String.IsNullOrWhiteSpace(searchField) && !String.IsNullOrWhiteSpace(searchTerm))
{
sqlCommand.Append(@"select row_number() over (order by SortOrder) as rowid
, count(*) over() as totalrows
, i.*
from i7_sflexi_items i
join(
select distinct ItemGuid
from i7_sflexi_values
where FieldValue like '%:SearchTerm%'
) v on v.ItemGuid = i.ItemGuid
where ModuleGuid = ':ModuleGuid'
order by SortOrder :SortDirection
limit :PageSize " + (pageNumber > 1 ? "offset :OffsetRows;" : ";"));
}
else if (!String.IsNullOrWhiteSpace(searchField) && !String.IsNullOrWhiteSpace(searchTerm))
{
sqlCommand.Append(@"select row_number() over (order by SortOrder) as rowid
, count(*) over() as totalrows
, i.*
from i7_sflexi_items i
join(
select distinct ItemGuid, FieldGuid
from i7_sflexi_values
where FieldValue like '%:SearchTerm%'
) v on v.ItemGuid = i.ItemGuid
join(
select distinct FieldGuid
from i7_sflexi_fields
where name = :SearchField
) f on f.FieldGuid = v.FieldGuid
where ModuleGuid = :ModuleGuid
order by SortOrder :SortDirection
limit :PageSize " + (pageNumber > 1 ? "offset :OffsetRows;" : ";"));
}
else
{
sqlCommand.Append(@"select row_number() over (order by SortOrder) as rowid
, count(*) over() as totalrows
, i.*
from i7_sflexi_items i
where ModuleGuid = ':ModuleGuid'
order by SortOrder :SortDirection
limit :PageSize " + (pageNumber > 1 ? "offset :OffsetRows;" : ";"));
}
int offsetRows = (pageSize * pageNumber) - pageSize;
var sqlParams = new List <SqliteParameter>
{
new SqliteParameter(":PageSize", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = pageSize
},
new SqliteParameter(":OffsetRows", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = offsetRows
},
new SqliteParameter(":SearchTerm", DbType.String, 255)
{
Direction = ParameterDirection.Input, Value = searchTerm
},
new SqliteParameter(":SearchField", DbType.String, 50)
{
Direction = ParameterDirection.Input, Value = searchField
},
new SqliteParameter(":ModuleGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = moduleGuid.ToString()
},
new SqliteParameter(":SortDirection", DbType.String, 4)
{
Direction = ParameterDirection.Input, Value = descending ? "desc" : "asc"
}
};
return(SqliteHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
sqlParams.ToArray()));
}
private void btnUploadData_Click(object sender, EventArgs e)
{
#region 数据验证
//if (CommonHandler. == 0)
//{
// CommonHandler.ShowMessage(MessageType.Information, "请选择\"项目\"");
// cboProjects.Focus();
// return;
//}
if (txtShopName.Text == "")
{
CommonHandler.ShowMessage(MessageType.Information, "请选择\"经销商\"");
txtShopName.Focus();
return;
}
if (btnDataPath.Text == "")
{
CommonHandler.ShowMessage(MessageType.Information, "请选择\"数据路径\"");
btnDataPath.Focus();
return;
}
ProjectCode_Golbal = CommonHandler.GetComboBoxSelectedValue(cboProjects).ToString();
ShopCode_Golbal = btnShopCode.Text;
DirectoryInfo dataDir = new DirectoryInfo(btnDataPath.Text);
FileInfo[] filesInfo = dataDir.GetFiles();
bool isExistDBFile = false;
foreach (FileInfo fileInfo in filesInfo)
{
if (fileInfo.Name == "writeable.db")
{
isExistDBFile = true;
SqliteHelper.SetConnectionString("Data Source=" + fileInfo.FullName, "");
}
}
if (!isExistDBFile)
{
CommonHandler.ShowMessage(MessageType.Information, "路径中不存在数据库文件'writeable.db'");
return;
}
#endregion
//if (!RecheckStatus())
//{
#region Answer表数据
{
List <String> dataList = SqliteHelper.Search("SELECT ProjectCode,SubjectCode,ShopCode,Score,Remark,ImageName,InUserID,'0','',AssessmentDate,InDateTime from Answer WHERE ProjectCode='" + ProjectCode_Golbal + "' AND ShopCode='" + ShopCode_Golbal + "'");
List <String> updateStringList = new List <string>();
foreach (String data in dataList)
{
String[] properties = data.Split('$');
String updateString = @"update Answer Set Flag=1 WHERE ProjectCode='{0}' " +
"AND SubjectCode='{1}' " +
"AND ShopCode='{2}'";
updateString = String.Format(updateString, properties[0], properties[1], properties[2]);
updateStringList.Add(updateString);
}
service.SaveAnswerList(dataList.ToArray());
SqliteHelper.InsertOrUpdata(updateStringList);
}
#endregion
#region AnswerLog表数据
{
List <String> dataList = SqliteHelper.Search("SELECT ProjectCode,SubjectCode,ShopCode,Score,Desc,InUserID,StatusCode from AnswerLog WHERE ProjectCode='" + ProjectCode_Golbal + "' AND ShopCode='" + ShopCode_Golbal + "'");
List <String> updateStringList = new List <string>();
foreach (String data in dataList)
{
String[] properties = data.Split('$');
String updateString = @"update AnswerLog Set Flag=1 WHERE ProjectCode='{0}' " +
"AND SubjectCode='{1}' " +
"AND ShopCode='{2}'" +
"AND StatusCode='{3}'";
updateString = String.Format(updateString, properties[0], properties[1], properties[2], properties[6]);
updateStringList.Add(updateString);
}
service.SaveAnswerLogList(dataList.ToArray());
SqliteHelper.InsertOrUpdata(updateStringList);
}
#endregion
#region AnswerDtl表数据
{
List <String> dataList = SqliteHelper.Search("SELECT ProjectCode,SubjectCode,ShopCode,SeqNO,InUserID,CheckOptionCode,PicNameList from AnswerDtl WHERE ProjectCode='" + ProjectCode_Golbal + "' AND ShopCode='" + ShopCode_Golbal + "'");
List <String> updateStringList = new List <string>();
foreach (String data in dataList)
{
String[] properties = data.Split('$');
String updateString = @"update AnswerDtl Set Flag=1,PicNameList='{4}' WHERE ProjectCode='{0}' " +
"AND SubjectCode='{1}' " +
"AND ShopCode='{2}' " +
"AND SeqNO={3}";;
updateString = String.Format(updateString, properties[0], properties[1], properties[2], properties[3], properties[6]);
updateStringList.Add(updateString);
}
service.SaveAnswerDtlList(dataList.ToArray());
SqliteHelper.InsertOrUpdata(updateStringList);
}
#endregion
#region AnswerDtl2表数据
{
List <String> dataList = SqliteHelper.Search("SELECT ProjectCode,SubjectCode,ShopCode,SeqNO,InUserID,CheckOptionCode from AnswerDtl2 WHERE ProjectCode='" + ProjectCode_Golbal + "' AND ShopCode='" + ShopCode_Golbal + "'");
List <String> updateStringList = new List <string>();
foreach (String data in dataList)
{
String[] properties = data.Split('$');
String updateString = @"update AnswerDtl2 Set Flag=1 WHERE ProjectCode='{0}' " +
"AND SubjectCode='{1}' " +
"AND ShopCode='{2}' " +
"AND SeqNO={3}";
updateString = String.Format(updateString, properties[0], properties[1], properties[2], properties[3]);
updateStringList.Add(updateString);
}
service.SaveAnswerDtl2StreamList(dataList.ToArray());
SqliteHelper.InsertOrUpdata(updateStringList);
}
#endregion
#region AnswerDtl3表数据
{
List <String> dataList = SqliteHelper.Search("SELECT ProjectCode,SubjectCode,ShopCode,SeqNO,LossDesc,PicName from AnswerDtl3 WHERE ProjectCode='" + ProjectCode_Golbal + "' AND ShopCode='" + ShopCode_Golbal + "'");
List <String> updateStringList = new List <string>();
foreach (String data in dataList)
{
String[] properties = data.Split('$');
String updateString = @"update AnswerDtl3 Set Flag=1 WHERE ProjectCode='{0}' " +
"AND SubjectCode='{1}' " +
"AND ShopCode='{2}' " +
"AND SeqNO={3}";
updateString = String.Format(updateString, properties[0], properties[1], properties[2], properties[3]);
updateStringList.Add(updateString);
}
service.SaveAnswerDtl3StringList(dataList.ToArray());
SqliteHelper.InsertOrUpdata(updateStringList);
}
#endregion
//}
//else
//{
// CommonHandler.ShowMessage(MessageType.Information, "已经提交复审了，分数不会上传只会上传照片信息");
//}
#region 图片文件
{
if (chkPic.Checked)
{
//DateTime st = DateTime.Now;
DirectoryInfo[] dirInfos = dataDir.GetDirectories();
foreach (DirectoryInfo dirInfo in dirInfos)
{
if (dirInfo.Name == ProjectCode_Golbal + txtShopName.Text)
{
FileInfo[] fileList = dirInfo.GetFiles("Thumbs.db");
if (fileList != null && fileList.Length != 0)
{
foreach (FileInfo file in fileList)
{
if (file.Name == "Thumbs.db")
{
file.Delete();
break;
}
}
}
UploadImgZipFileBySubDirectory(dirInfo.FullName);
}
}
}
CommonHandler.ShowMessage(MessageType.Information, "数据上传完毕。");
//TimeSpan ts = DateTime.Now - st;
//CommonHandler.ShowMessage(MessageType.Information,ts.ToString());
}
#endregion
}
public DocumentManager(SqliteHelper sqlite)
{
this.sqlite = sqlite;
docDAO = new DocumentDAO(sqlite);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbFilePath"></param>
public ListSheetInDB(string dbFilePath)
{
_DBHelper = new SqliteHelper(dbFilePath);
}
private void ExportServerDatabaseToEmbedded(DatabaseType databaseType, DatabaseMenuCommandParameters parameters)
{
string filter = DataConnectionHelper.GetSqlCeFileFilter();
Scope scope = Scope.SchemaData;
if (databaseType == DatabaseType.SQLite)
{
filter = DataConnectionHelper.GetSqliteFileFilter();
scope = Scope.SchemaDataSQLite;
}
Debug.Assert(databaseType == DatabaseType.SQLite || databaseType == DatabaseType.SQLCE40, "Unexpected database type");
try
{
string connectionString = parameters.DatabaseInfo != null
? parameters.DatabaseInfo.ConnectionString :
DataConnectionHelper.PromptForConnectionString(package);
if (!string.IsNullOrEmpty(connectionString))
{
PickTablesDialog ptd = new PickTablesDialog();
int totalCount = 0;
using (IRepository repository = Helpers.DataConnectionHelper.CreateRepository(new DatabaseInfo
{ ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer }))
{
ptd.Tables = repository.GetAllTableNamesForExclusion();
totalCount = ptd.Tables.Count;
}
bool? res = ptd.ShowModal();
if (res.HasValue && res.Value == true && (ptd.Tables.Count < totalCount))
{
string dbName;
string dbConnectionString = null;
SaveFileDialog fd = new SaveFileDialog();
fd.Title = "Export as";
fd.Filter = filter;
fd.OverwritePrompt = true;
fd.ValidateNames = true;
bool? result = fd.ShowDialog();
if (result.HasValue && result.Value == true)
{
dbName = fd.FileName;
try
{
if (databaseType == DatabaseType.SQLCE40)
{
package.SetStatus("Creating SQL Server Compact database...");
SqlCeScripting.SqlCeHelper4 helper = new SqlCeScripting.SqlCeHelper4();
dbConnectionString = string.Format("Data Source={0};Max Database Size=4091", dbName);
if (System.IO.File.Exists(dbName))
File.Delete(dbName);
helper.CreateDatabase(dbConnectionString);
}
if (databaseType == DatabaseType.SQLite)
{
package.SetStatus("Creating SQLite database...");
var helper = new SqliteHelper();
dbConnectionString = string.Format("Data Source={0};", dbName);
if (System.IO.File.Exists(dbName))
File.Delete(dbName);
helper.CreateDatabase(dbConnectionString);
}
BackgroundWorker bw = new BackgroundWorker();
List<object> workerParameters = new List<object>();
workerParameters.Add(dbConnectionString);
workerParameters.Add(connectionString);
workerParameters.Add(ptd.Tables);
workerParameters.Add(databaseType.ToString());
workerParameters.Add(scope.ToString());
bw.DoWork += new DoWorkEventHandler(bw_DoWork);
bw.RunWorkerCompleted += (s, ea) =>
{
try
{
if (ea.Error != null)
{
Helpers.DataConnectionHelper.SendError(ea.Error, databaseType, false);
}
Helpers.DataConnectionHelper.LogUsage("DatabasesExportFromServer");
}
finally
{
bw.Dispose();
}
};
bw.RunWorkerAsync(workerParameters);
}
catch (Exception ex)
{
Helpers.DataConnectionHelper.SendError(ex, databaseType, false);
}
}
}
}
}
catch (Exception ex)
{
Helpers.DataConnectionHelper.SendError(ex, DatabaseType.SQLServer);
}
}
//修改好 未测试
///java // 测试通过
/// <summary>
/// 插入贴吧标题 到本地数据
/// </summary>
/// <param name="sqlconn"></param>
/// <param name="listBarNameEntity"></param>
/// <returns></returns>
public int InsertLocalContent(List <ContentEntity> listContentEntity)
{
// ISQLConnection iSQLConnection = new SQLiteConn();
// var sqlconn = iSQLConnection.GetSQLiteConn();
//var sqlconn = SQLiteConn.GetSQLiteConn();
int rowids = GetSqliteRowId();
string strsql = "INSERT INTO tbtitlelist(id,content,titlecreatetime,replytime,floor,replynum,createcode,createtime，updatetime) VALUES(@Id,@Title,@Url,@Uname,@Uid,@Replies,@Createcode,@Createtime);select last_insert_rowid();";
foreach (var item in listContentEntity)
{
if (rowids == 0)
{
rowids = 1;
}
else if (Intreback == 2 || rowids == 1)
{
rowids = 2;
}
else if (rowids > 1)
{
rowids = rowids + 1;
}
else
{
rowids = Intreback;
}
SQLiteParameter[] parameters =
{
new SQLiteParameter("Id", DbType.Int32),
new SQLiteParameter("@Content", DbType.String),
new SQLiteParameter("@Titlecreatetime", DbType.String),
new SQLiteParameter("@Replytime", DbType.String),
new SQLiteParameter("@Floor", DbType.String),
new SQLiteParameter("@Replynum", DbType.String),
new SQLiteParameter("@CreateCode", DbType.String),
new SQLiteParameter("@CreateTime", DbType.DateTime),
new SQLiteParameter("@Updatetime", DbType.DateTime)
};
parameters[0].Value = rowids;
Debug.WriteLine("==rowids====" + rowids + "=====");
parameters[1].Value = item.Content;
parameters[2].Value = item.TitleCreateTime;
parameters[3].Value = item.ReplyTime;
parameters[4].Value = item.Floor;
parameters[5].Value = item.ReplyNum;
parameters[6].Value = item.CreateCode;
parameters[7].Value = item.CreateTime;
parameters[8].Value = item.UpdateTime;
var data = SqliteHelper.ExecuteScalar(strsql, CommandType.Text, parameters);
Debug.WriteLine("==data====" + data + "=====");
Intreback = data + 1;
rowids = -1;
if (data == 0)
{
Debug.WriteLine("-------------InsertLocalTBTitle ERROR-----------");;
return(0);
}
//SQLiteCommand cmd = new SQLiteCommand(sql, sqlconn);
//cmd.Parameters.Add(id);
//cmd.Parameters.Add(title);
//cmd.Parameters.Add(url);
//cmd.Parameters.Add(uname);
//cmd.Parameters.Add(uid);
//cmd.Parameters.Add(replies);
//cmd.Parameters.Add(createcode);
//cmd.Parameters.Add(createtime);
//sqlconn.Open();
//rowids = cmd.ExecuteNonQuery();
//sqlconn.Close();
//};
}
return(2);
}
private void frmAdd_Load(object sender, EventArgs e)
{
var errorMessage = "";
cbMinistries.ValueMember = "rowid";
cbMinistries.DisplayMember = "ministry";
cbDepartment.ValueMember = "rowid";
cbDepartment.DisplayMember = "Department";
MemberMinistry = SqliteHelper.ExecuteReader(WpcHelper.DbConnection,
@"select a.rowid,a.ministryid,b.ministry from memberministries a
inner join ministries b on a.ministryid=b.rowid
where a.memberid=@memberid",
new List <SqliteParam>
{
new SqliteParam("memberid", MemberId, DbType.String)
}, out errorMessage);
MemberDepartments = SqliteHelper.ExecuteReader(WpcHelper.DbConnection,
@"select a.rowid,a.departmentid,b.department,a.start,a.end from memberdepartments a
inner join departments b on a.departmentid=b.rowid
where a.memberid=@memberid",
new List <SqliteParam>
{
new SqliteParam("memberid", MemberId, DbType.String)
}, out errorMessage);
_ministries = SqliteHelper.ExecuteReader(WpcHelper.DbConnection, "select rowid,ministry from ministries",
new List <SqliteParam>
{
}, out errorMessage);
_departments = SqliteHelper.ExecuteReader(WpcHelper.DbConnection, "select rowid,department from departments",
new List <SqliteParam>
{
}, out errorMessage);
if (OpState == OpStateEnum.Edit)
{
var dt = SqliteHelper.ExecuteReader(WpcHelper.DbConnection,
"select * from members where rowid='" + MemberId + "'",
new List <SqliteParam>(), out errorMessage);
if (dt.Rows.Count > 0)
{
var row = dt.Rows[0];
tbFirstName.Text = row["firstname"].ToString();
tbMiddleName.Text = row["middlename"].ToString();
tbLastName.Text = row["lastname"].ToString();
tbStreetAddress.Text = row["street"].ToString();
tbBarangay.Text = row["barangay"].ToString();
tbCity.Text = row["city"].ToString();
tbCountry.Text = row["country"].ToString();
dtBirthDate.Value = DateTime.Parse(row["birthdate"].ToString());
cbCivilStatus.Text = row["civilstatus"].ToString();
tbMobile.Text = row["mobile"].ToString();
tbLandLine.Text = row["landline"].ToString();
tbOccupation.Text = row["occupation"].ToString();
cbMemberType.Text = row["type"].ToString();
cbStatus.Text = row["status"].ToString();
}
else
{
MessageBox.Show("Unable to load record #" + MemberId);
}
}
dvDepartments.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.Fill);
}
/// <summary>
/// Updates a row in the i7_sflexi_items table. Returns true if row updated.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="moduleID"> moduleID </param>
/// <param name="definitionGuid"> definitionGuid </param>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="itemID"> itemID </param>
/// <param name="sortOrder"> sortOrder </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <returns>bool</returns>
public static bool Update(
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
int moduleID,
Guid definitionGuid,
Guid itemGuid,
int sortOrder,
DateTime createdUtc,
DateTime lastModUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("update i7_sflexi_items set {0} where ItemGuid = :ItemGuid;"
, @"SiteGuid = :SiteGuid
,FeatureGuid = :FeatureGuid
,ModuleGuid = :ModuleGuid
,ModuleId = :ModuleId
,DefinitionGuid = :DefinitionGuid
,SortOrder = :SortOrder
,CreatedUTC = :CreatedUTC
,LastModUTC = :LastModUTC"
);
var sqlParams = new List <SqliteParameter>
{
new SqliteParameter(":ItemGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = itemGuid.ToString()
},
new SqliteParameter(":SiteGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = siteGuid.ToString()
},
new SqliteParameter(":FeatureGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = featureGuid.ToString()
},
new SqliteParameter(":ModuleGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = moduleGuid.ToString()
},
new SqliteParameter(":ModuleId", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = moduleID
},
new SqliteParameter(":DefinitionGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = definitionGuid.ToString()
},
new SqliteParameter(":SortOrder", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = sortOrder
},
new SqliteParameter(":CreatedUTC", DbType.DateTime)
{
Direction = ParameterDirection.Input, Value = createdUtc
},
new SqliteParameter(":LastModUTC", DbType.DateTime)
{
Direction = ParameterDirection.Input, Value = lastModUtc
}
};
int rowsAffected = Convert.ToInt32(SqliteHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
sqlParams.ToArray()).ToString());
return(rowsAffected > 0);
}
/// <summary>
/// Updates a row in the mp_SurveyQuestions table. Returns true if row updated.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>bool</returns>
public static bool Update(
Guid questionGuid,
Guid pageGuid,
string questionText,
int questionTypeId,
bool answerIsRequired,
int questionOrder,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_SurveyQuestions ");
sqlCommand.Append("SET ");
sqlCommand.Append("PageGuid = :PageGuid, ");
sqlCommand.Append("QuestionText = :QuestionText, ");
sqlCommand.Append("QuestionTypeId = :QuestionTypeId, ");
sqlCommand.Append("AnswerIsRequired = :AnswerIsRequired, ");
sqlCommand.Append("QuestionOrder = :QuestionOrder, ");
sqlCommand.Append("ValidationMessage = :ValidationMessage ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid = :QuestionGuid ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[7];
arParams[0] = new SqliteParameter(":QuestionGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new SqliteParameter(":PageGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new SqliteParameter(":QuestionText", DbType.Object);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new SqliteParameter(":QuestionTypeId", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new SqliteParameter(":AnswerIsRequired", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnswerIsRequired;
arParams[5] = new SqliteParameter(":QuestionOrder", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = questionOrder;
arParams[6] = new SqliteParameter(":ValidationMessage", DbType.String, 256);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = validationMessage;
int rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected > -1);
}
/// <summary>
/// 处理 授权 回调
/// </summary>
public static bool AuthCallBack(bool isRedirect)
{
var result = false;
var httpContext = HttpContext.Current;
if (httpContext != null)
{
var urlParams = httpContext.Request.QueryString;
var encoding = Encoding.UTF8;
var topParameters = encoding.GetString(Convert.FromBase64String(urlParams.Get("top_parameters") ?? ""));
var topParams = HttpUtility.ParseQueryString(topParameters);
var tc = new TopConfigs
{
SessionKey = urlParams.Get("top_session"),
TopCallBackData = urlParams,
TopCallBackParams = topParams,
RefreshToken = topParams.Get("refresh_token")
};
#region 更新数据库
#region 原生方法
using (var dbHelper = new SqliteHelper())
{
var queryTxt = "select * from users where u_top_appkey=@appkey";
var sqlParameters = new[] { new SQLiteParameter("@appkey", tc.AppKey) };
var topInfo = dbHelper.ExecuteList(queryTxt, sqlParameters);
if (topInfo != null && topInfo.Count > 0)
{
queryTxt = "update users set U_Top_SessionKey=@SessionKey,U_LastUpdateTime=@LastupdateTime,U_Top_CallbackData=@U_Top_CallbackData where u_top_appkey=@appkey";
sqlParameters = new[]
{
new SQLiteParameter("@appkey", tc.AppKey),
new SQLiteParameter("@SessionKey", tc.SessionKey),
new SQLiteParameter("@U_Top_CallbackData", tc.TopCallBackData),
new SQLiteParameter("@LastupdateTime", DateTime.Now)
};
dbHelper.ExecuteNonQuery(queryTxt, sqlParameters);
}
else
{
queryTxt = @"insert into users(U_Guid,U_Name,U_Password,U_LastUpdateTime,U_Top_AppKey,U_Top_AppSecret,U_Top_SessionKey,U_Top_CallbackData)
values(@U_guid,@U_Name,@U_Password,@U_LastUpdateTime,@U_Top_AppKey,@U_Top_AppSecret,@U_Top_SessionKey,@U_Top_CallbackData); SELECT @@IDENTITY";
sqlParameters = new[]
{
new SQLiteParameter("@U_Guid", Guid.NewGuid().ToString("N")),
new SQLiteParameter("@U_Name", "admin"),
new SQLiteParameter("@U_Password", "admin888"),
new SQLiteParameter("@U_LastUpdateTime", DateTime.Now),
new SQLiteParameter("@U_Top_AppKey", tc.AppKey),
new SQLiteParameter("@U_Top_AppSecret", tc.AppSecret),
new SQLiteParameter("@U_Top_SessionKey", tc.SessionKey),
new SQLiteParameter("@U_Top_CallbackData", tc.TopCallBackData)
};
dbHelper.ExecuteScalar(queryTxt, sqlParameters);
}
}
#endregion
#region Ef
/*
using (var topDb = new TopCore.DataAccess.TopEntities())
{
var queryTxt = from a in topDb.Users
where a.U_Top_AppKey == tc.AppKey && a.U_Top_AppSecret == tc.AppSecret
select a;
var topInfo = queryTxt.First();
if (topInfo != null)
{
topInfo.U_Top_SessionKey = tc.SessionKey;
topInfo.U_LastUpdateTime = DateTime.Now;
topDb.SaveChanges();
}
else
{
var topEntity = new User
{
U_Name = "admin",
U_Password = "******",
U_LastUpdateTime = DateTime.Now,
U_Top_AppKey = tc.AppKey,
U_Top_AppSecret = tc.AppSecret,
U_Top_SessionKey = tc.SessionKey,
U_Top_CallbackData = tc.TopCallBackData.ToString()
};
topDb.AddToUsers(topEntity);
}
}
*/
#endregion
#endregion
result = true;
if (isRedirect)
httpContext.Response.Redirect(TopApiMainUrl);
}
return result;
}
/// <summary>
/// Inserts a row in the mp_SurveyQuestions table. Returns rows affected count.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>int</returns>
public static int Add(
Guid questionGuid,
Guid pageGuid,
string questionText,
int questionTypeId,
bool answerIsRequired,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SurveyQuestions (");
sqlCommand.Append("QuestionGuid, ");
sqlCommand.Append("PageGuid, ");
sqlCommand.Append("QuestionText, ");
sqlCommand.Append("QuestionTypeId, ");
sqlCommand.Append("AnswerIsRequired, ");
sqlCommand.Append("QuestionOrder, ");
sqlCommand.Append("ValidationMessage )");
sqlCommand.Append("SELECT :QuestionGuid, :PageGuid, :QuestionText, ");
sqlCommand.Append(":QuestionTypeId, :AnswerIsRequired, Count(*), :ValidationMessage ");
sqlCommand.Append("FROM mp_SurveyPages; ");
SqliteParameter[] arParams = new SqliteParameter[6];
arParams[0] = new SqliteParameter(":QuestionGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new SqliteParameter(":PageGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new SqliteParameter(":QuestionText", DbType.Object);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new SqliteParameter(":QuestionTypeId", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new SqliteParameter(":AnswerIsRequired", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnswerIsRequired;
arParams[5] = new SqliteParameter(":ValidationMessage", DbType.String, 256);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = validationMessage;
int rowsAffected = 0;
rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected);
}
public bool IsCanDelete(Produit produit)
{
var isCanDelete = true;
using (var helper = new SqliteHelper("SELECT ProduitId FROM LigneCommande Where ProduitId = @Id"))
{
helper.AddInParameter("Id", DbType.Int32, produit.Id);
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
isCanDelete = false;
}
}
return isCanDelete;
}
/// <summary>
/// 删除
/// </summary>
/// <param name="pro"></param>
/// <returns></returns>
public static int DeleteUserInfo(UserInfo ur)
{
string sql = "delete from UserInfo where UserId=" + ur.UserId;
return(SqliteHelper.ExecuteNonQuery(sql));
}
public void Add(Tag tagType)
{
SqliteHelper.Insert(AddQuery, AddCommandBinder, tagType);
}
public DataSet GetDirectories()
{
SqliteHelper sqlHelper = new SqliteHelper();
return(sqlHelper.QueryBySQL("select dir from Directories"));
}
private void Form1_Load(object sender, EventArgs e)
{
this.Text = Properties.Resources.softwareName;
foreach (var pair in _bh.BibTexFields)
{
if (pair.Key == "type")
{
var colcombo = new DataGridViewComboBoxColumn();
colcombo.HeaderText = pair.Value; colcombo.Name = pair.Key;
foreach (var item in Properties.Resources.types.Split(';'))
{
colcombo.Items.Add(item);
}
colcombo.DisplayStyle = DataGridViewComboBoxDisplayStyle.DropDownButton;
dgv.Columns.Add(colcombo);
}
else
{
dgv.Columns.Add(pair.Key, pair.Value);
}
}
if (System.IO.File.Exists(Properties.Settings.Default.RecentLib))
{
_sh = new SqliteHelper(Properties.Settings.Default.RecentLib);
refreshRows();
}
if (Program.args.Length > 0)
{
if (System.IO.File.Exists(Program.args[0]))
{
if (Program.args[0].EndsWith(".bdb"))
{
_sh = new SqliteHelper(Program.args[0]); refreshRows();
}
else
{
_bh.ParseFile(Program.args[0]);
}
}
}
// Hotkey
W32ApiHelper.Regist(this.Handle, 0, Keys.F10, new W32ApiHelper.HotKeyCallBackHanlder(() =>
{
try
{
if (this.Handle == W32ApiHelper.GetForegroundWindow()) { addBibTexCodeToolStripMenuItem_Click(null, null); return; }
string str = Clipboard.GetText().ToString();
if (string.IsNullOrEmpty(str)) return;
if (str[0] == '@' || str[0] == '%')
{
importRecords(str);
W32ApiHelper.SetForegroundWindow(this.Handle);
}
else
{
SendKeys.SendWait("^c");
System.Threading.Thread.Sleep(750);
str = Clipboard.GetText().ToString();
if (str[0] == '@' || str[0] == '%')
{
importRecords(str);
W32ApiHelper.SetForegroundWindow(this.Handle);
}
else
{
System.Diagnostics.Process.Start(string.Format(Properties.Settings.Default.DefaultSearchEngine, str));
}
}
}
catch (Exception) { }
}));
}
public void DeleteDirectories(string dir)
{
SqliteHelper sqlHelper = new SqliteHelper();
sqlHelper.ExecuteNonQuery($"delete from Directories where dir='{dir}'");
}
public void entryGame(GameObject sender)
{
Sql = new SqliteHelper("data source=sqlite4unity.db"); //连接数据库
Sql.UpdateValues("Game", new string[] { "Score" }, new string[] { ("'" + "0" + "'").ToString() }, "UserName", "=", ("'" + Login.Name + "'").ToString()); //将分数置0
Sql.CloseConnection(); //关闭数据库连接
describedText.text = "";
menu.SetActive(false);
modeUI.SetActive(false);
ChoosePlanes.SetActive(false);
SingleGame.SetActive(true);
UICamera.SetActive(true);
//myPlane.SetActive(true);
bgMusic.SetActive(true);
lifebarBG.SetActive(true);
//lifeBar.SetActive(true);
pausebtn.SetActive(true);
pauseUI.SetActive(false);
WinUI.SetActive(false);
DefeatUI.SetActive(false);
//刷新时间和生命值
GameObject.Find("SingleGame").GetComponent <GameManager>().lifeBar.fillAmount = 1f;
GameObject.Find("SingleGame").GetComponent <GameManager>().survivalTime = 0;
switch (sender.name)
{
case "ButtonEasy":
GameManager.successTime = 10f;
EnemyController.updateTimer = 1.5f; //飞机生成频率
EnemyPlane.updateTimer = 1.5f; //子弹更新频率
//GameManager.map = "bg1";
GameObject.Find("SingleGame").GetComponent <GameManager>().bgImage1.sprite = Resources.Load("bgImage/bg1", typeof(Sprite)) as Sprite;
GameObject.Find("SingleGame").GetComponent <GameManager>().bgImage2.sprite = Resources.Load("bgImage/bg1", typeof(Sprite)) as Sprite;
break;
case "ButtonUsual":
GameManager.successTime = 20f;
EnemyController.updateTimer = 1.0f; //飞机生成频率
EnemyPlane.updateTimer = 1.0f; //子弹更新频率
//GameManager.map = "bg2";
//加载地图
GameObject.Find("SingleGame").GetComponent <GameManager>().bgImage1.sprite = Resources.Load("bgImage/bg2", typeof(Sprite)) as Sprite;
GameObject.Find("SingleGame").GetComponent <GameManager>().bgImage2.sprite = Resources.Load("bgImage/bg2", typeof(Sprite)) as Sprite;
break;
case "ButtonHard":
GameManager.successTime = 30f;
EnemyController.updateTimer = 0.5f; //飞机生成频率
EnemyPlane.updateTimer = 0.5f; //子弹更新频率
//GameManager.map = "bg3";
GameObject.Find("SingleGame").GetComponent <GameManager>().bgImage1.sprite = Resources.Load("bgImage/bg3", typeof(Sprite)) as Sprite;
GameObject.Find("SingleGame").GetComponent <GameManager>().bgImage2.sprite = Resources.Load("bgImage/bg3", typeof(Sprite)) as Sprite;
break;
default:
break;
}
//transform.GetComponent<GameManager>().pauseMenu.SetActive(false);
Time.timeScale = 1f;
if (selfPlane == null)
{
if (SelectPlane.IsdefaultplaneChosen == true)
{
selfPlane = Instantiate(Plane[0], new Vector3(-8f, 0f, 0f), Quaternion.identity);
}
else if (SelectPlane.IsheliChosen == true)
{
selfPlane = Instantiate(Plane[1], new Vector3(-8f, 0f, 0f), Quaternion.identity);
}
else
{
selfPlane = Instantiate(Plane[2], new Vector3(-8f, 0f, 0f), Quaternion.identity);
}
}
}
public string SyncFiles()
{
//添加或更新文件
int errorCount = 0;
int insertCount = 0;
int updateCount = 0;
List <FileInfo> ls = new List <FileInfo>();
ls = GetAllDirectoriesFiles();
//ls = FilterSupportedFiles(ls);
SqliteHelper sqlHelper = new SqliteHelper();
//保存最新的所有文件
sqlHelper.ExecuteNonQuery($"delete from LatestFiles");
foreach (var f in ls)
{
try
{
if (f.FullName.Contains("'") == false)
{
string fileMD5 = MD5Tools.GetMD5(f.FullName);
sqlHelper.ExecuteNonQuery($"insert into LatestFiles(md5,fullname) values('{fileMD5}','{f.FullName}')");
}
}
catch
{
errorCount += 1;
}
}
//删除过期的文件
sqlHelper.ExecuteNonQuery($"delete from files where md5 not in (select md5 from LatestFiles)");
foreach (var f in ls)
{
try
{
//排除含有'符号的文件，否则插入数据库会出错
if (f.FullName.Contains("'") == false || f.FullName.Contains("~") == false)
{
string fileMD5 = MD5Tools.GetMD5(f.FullName);
string fileModifiedtime = f.LastWriteTime.ToString();
//如果数据库中不存在，直接添加
if (sqlHelper.ExecuteScalar($"select count(MD5) from Files where MD5='{fileMD5}'") != 1)
{
sqlHelper.ExecuteNonQuery($"insert into Files(MD5,fullname,modifiedtime,gettime) values('{fileMD5}','{f.FullName}','{fileModifiedtime}','{DateTime.Now}')");
insertCount = insertCount + 1;
}
else
{
//如果数据库中存在，但是修改日期不一样，先删除，后添加
if (sqlHelper.ExecuteScalar($"select count(MD5) from Files where MD5='{fileMD5}' and modifiedtime<>'{fileModifiedtime}'") == 1)
{
sqlHelper.ExecuteNonQuery($"delete from Files where MD5='{fileMD5}'");
sqlHelper.ExecuteNonQuery($"insert into Files(MD5,fullname,modifiedtime,gettime) values('{fileMD5}','{f.FullName}','{fileModifiedtime}','{DateTime.Now}')");
updateCount = updateCount + 1;
}
}
}
}
catch
{
errorCount = errorCount + 1;
}
}
int total = sqlHelper.ExecuteScalar("select count(*) from files");
return("索引文件共计：" + total.ToString() + "，新增：" + insertCount.ToString() + "，更新：" + updateCount.ToString() + "，错误：" + errorCount.ToString());
}
public static IDataReader GetPageOfValuesForField(
Guid moduleGuid,
Guid definitionGuid,
string field,
int pageNumber,
int pageSize,
string searchTerm = "",
//string searchField = "",
bool descending = false)
{
StringBuilder sqlCommand = new StringBuilder();
int offsetRows = (pageSize * pageNumber) - pageSize;
if (!String.IsNullOrWhiteSpace(searchTerm))
{
sqlCommand.Append(@"select sql_calc_found_rows found_rows() as totalrows, v.*
from i7_sflexi_values v
join(
select distinct FieldGuid
from i7_sflexi_fields
where name = ':Field'
and DefinitionGuid = :DefinitionGuid
) f on f.FieldGuid = v.FieldGuid
where ModuleGuid = :ModuleGuid
and v.FieldValue like '%:SearchTerm%'
order by id :SortDirection
limit :PageSize " + (pageNumber > 1 ? "offset :OffsetRows;" : ";"));
}
else
{
sqlCommand.Append(@"select sql_calc_found_rows found_rows() as totalrows, v.*
from i7_sflexi_values v
join(
select distinct FieldGuid
from i7_sflexi_fields
where name = ':Field'
and DefinitionGuid = :DefinitionGuid
) f on f.FieldGuid = v.FieldGuid
where ModuleGuid = :ModuleGuid
order by id :SortDirection
limit :PageSize " + (pageNumber > 1 ? "offset :OffsetRows;" : ";"));
}
var sqlParams = new List <SqliteParameter>
{
new SqliteParameter(":PageSize", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = pageSize
},
new SqliteParameter(":OffsetRows", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = offsetRows
},
new SqliteParameter(":SearchTerm", DbType.String, 255)
{
Direction = ParameterDirection.Input, Value = searchTerm
},
new SqliteParameter(":Field", DbType.String, 50)
{
Direction = ParameterDirection.Input, Value = field
},
new SqliteParameter(":ModuleGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = moduleGuid.ToString()
},
new SqliteParameter(":SortDirection", DbType.String, 4)
{
Direction = ParameterDirection.Input, Value = descending ? "DESC" : "ASC"
}
};
return(SqliteHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
sqlParams.ToArray()));
}
/// <summary>
/// 处理命令
/// </summary>
/// <param name="e"></param>
void hmiConfigInit(StartupEventArgs e)
{
StartupLog.StartArgs = string.Join(",", e.Args);
updateLoadingMessage("正在解析命令...", 0.01);
Parser.Default.ParseArguments <CmdOptions>(e.Args).WithParsed(opt => {
//指定Hmi名称（调试的时候可用，方便启用某机台的配置）
opt.HmiName = opt.HmiName.ToUpper();
//Profiles 文件夹
opt.ProfilesFolder = YUtil.GetAbsolutePath(opt.ProfilesFolder);
//Profiles/Dev(Prod) 文件夹
var configFolder = opt.ProfilesFolder + "\\" + opt.Mode;
opt.ConfigFolder = configFolder;
//Profiles/Assets 文件夹
var assetsFolder = opt.ProfilesFolder + @"\Assets";
updateLoadingMessage("正在唤醒终端...", 0.01);
if (bool.Parse(opt.ShowConsole))
{
ConsoleHelper.Show();
}
Console.WriteLine("当前系统环境：-" + YUtil.GetOsVersion());
Console.WriteLine("开机自启动: -" + opt.AutoSatrt);
Console.WriteLine("配置文件夹：-" + opt.ProfilesFolder);
Console.WriteLine("当前运行模式：-" + opt.Mode);
Console.WriteLine("当前程序版本：-" + YUtil.GetAppVersion(Assembly.GetExecutingAssembly()));
Console.WriteLine("启用mock：-" + opt.Mock);
Console.WriteLine("Sqlite数据库：-" + opt.SqlitePath);
Console.WriteLine("资源文件夹：-" + assetsFolder);
if (!string.IsNullOrEmpty(opt.HmiName))
{
Console.WriteLine("指定 Hmi：-" + opt.HmiName);
}
updateLoadingMessage("配置开机自启...", 0.02);
YUtil.SetAppAutoStart(GetType().ToString(), bool.Parse(opt.AutoSatrt));
updateLoadingMessage("初始化Hmi配置...", 0.03);
var configFile = configFolder + $@"\Hmi.Config.{opt.Config}.json";
HmiConfig.Load(configFile);
Console.WriteLine("指定配置文件：-" + configFile);
updateLoadingMessage("初始化工艺字典...", 0.04);
HmiConfig.InitCraftBomZhsDict(assetsFolder + @"\Dicts\工艺Bom.xls");
updateLoadingMessage("初始化资源文件...", 0.05);
AssetsHelper.Init(YUtil.GetAbsolutePath(assetsFolder));
updateLoadingMessage("初始化日志服务...", 0.06);
LoggerHelper.Init(YUtil.GetAbsolutePath(HmiConfig.LogFolder));
updateLoadingMessage("初始化 Sqlite...", 0.08);
HmiConfig.SqlitePath = YUtil.GetAbsolutePath(opt.SqlitePath);
SqliteHelper.Init(YUtil.GetAbsolutePath(HmiConfig.SqlitePath));
//保留启动参数
CmdOptions.GlobalOptions = opt;
CmdOptions.StartupEventArgs = e;
}).WithNotParsed(err => {
var logger = new LoggerService(HmiConfig.LogFolder)
{
DefaultLocation = "StartError"
};
updateLoadingMessage($"解析命令异常..", 0);
string msg = "";
err?.ToList()?.ForEach(r => {
msg += r.ToString();
});
logger.Error("解析命令参数异常:\r\n参数： " + string.Join(",", e.Args) + "\r\n内容：" + msg);
throw new Exception("启动参数异常" + msg);
});
//记录程序崩溃日志
AppDomain.CurrentDomain.UnhandledException += (s, ue) => {
var logger = new LoggerService(HmiConfig.LogFolder)
{
DefaultLocation = "UnhandleExp"
};
var message = $"程序崩溃：{ue.ExceptionObject}\r\n当前可用内存：{YUtil.GetAvaliableMemoryByte() / 1000000} M";
//将错误日志写入mongoDb
logger.ErrorWithDb(message, MongoHelper.LogsDb, MongoHelper.UnhandleExceptionCollection);
//1 秒钟后重启程序
if (!HmiConfig.IsDevUserEnv)
{
YUtil.SetTimeout(1000, Restart);
}
};
}
private void Save()
{
var errorMessage = "";
if (OpState == OpStateEnum.Add)
{
var sql =
@"insert into members(firstname,middlename,lastname,street,barangay,city,country,birthdate,civilstatus,
mobile,landline,occupation,type,status,membersince) values (@firstname,@middlename,@lastname,@street,
@barangay,@city,@country,@birthdate,@civilstatus,@mobile,@landline,@occupation,@type,@status,@membersince)";
SqliteHelper.ExecuteNonQuery(WPC.WpcHelper.DbConnection, sql, new List <SqliteParam>
{
new SqliteParam("firstname", tbFirstName.Text.Trim(), DbType.String),
new SqliteParam("middlename", tbMiddleName.Text.Trim(), DbType.String),
new SqliteParam("lastname", tbLastName.Text.Trim(), DbType.String),
new SqliteParam("street", tbStreetAddress.Text.Trim(), DbType.String),
new SqliteParam("barangay", tbBarangay.Text.Trim(), DbType.String),
new SqliteParam("city", tbCity.Text.Trim(), DbType.String),
new SqliteParam("country", tbCountry.Text.Trim(), DbType.String),
new SqliteParam("birthdate", dtBirthDate.Value.Date, DbType.Date),
new SqliteParam("civilstatus", cbCivilStatus.Text.Trim(), DbType.String),
new SqliteParam("mobile", tbMobile.Text.Trim(), DbType.String),
new SqliteParam("landline", tbLandLine.Text.Trim(), DbType.String),
new SqliteParam("occupation", tbOccupation.Text.Trim(), DbType.String),
new SqliteParam("type", cbMemberType.Text.Trim(), DbType.String),
new SqliteParam("status", cbStatus.Text.Trim(), DbType.String),
new SqliteParam("membersince", dtMemberSince.Value.Date, DbType.Date),
//new SqliteParam("staff", cbStaff.Checked, DbType.Boolean),
}, out errorMessage);
}
else
{
var sql =
@"update members set firstname=@firstname,middlename=@middlename,lastname=@lastname,street=@street,barangay=@barangay,
city=@city,country=@country,birthdate=@birthdate,civilstatus=@civilstatus,
mobile=@mobile,landline=@landline,occupation=@occupation,type=@type,status=@status,membersince=@membersince
where rowid=@rowid";
SqliteHelper.ExecuteNonQuery(WpcHelper.DbConnection, sql, new List <SqliteParam>
{
new SqliteParam("firstname", tbFirstName.Text.Trim(), DbType.String),
new SqliteParam("middlename", tbMiddleName.Text.Trim(), DbType.String),
new SqliteParam("lastname", tbLastName.Text.Trim(), DbType.String),
new SqliteParam("street", tbStreetAddress.Text.Trim(), DbType.String),
new SqliteParam("barangay", tbBarangay.Text.Trim(), DbType.String),
new SqliteParam("city", tbCity.Text.Trim(), DbType.String),
new SqliteParam("country", tbCountry.Text.Trim(), DbType.String),
new SqliteParam("birthdate", dtBirthDate.Value.Date, DbType.Date),
new SqliteParam("civilstatus", cbCivilStatus.Text.Trim(), DbType.String),
new SqliteParam("mobile", tbMobile.Text.Trim(), DbType.String),
new SqliteParam("landline", tbLandLine.Text.Trim(), DbType.String),
new SqliteParam("occupation", tbOccupation.Text.Trim(), DbType.String),
new SqliteParam("type", cbMemberType.Text.Trim(), DbType.String),
new SqliteParam("status", cbStatus.Text.Trim(), DbType.String),
new SqliteParam("membersince", dtMemberSince.Value.Date, DbType.Date),
new SqliteParam("rowid", MemberId, DbType.String),
}, out errorMessage);
}
if (string.IsNullOrWhiteSpace(errorMessage))
{
// clear existing
SqliteHelper.ExecuteNonQuery(WpcHelper.DbConnection,
"delete from memberministries where memberid='" + MemberId + "'", new List <SqliteParam>(),
out errorMessage);
// save the memberministries
foreach (DataRow row in MemberMinistry.Rows)
{
SqliteHelper.ExecuteNonQuery(WpcHelper.DbConnection, "insert into memberministries values(@memberid,@ministryid)",
new List <SqliteParam>
{
new SqliteParam("memberid", MemberId, DbType.String),
new SqliteParam("ministryid", row["ministryid"].ToString(), DbType.String)
}, out errorMessage);
}
MessageBox.Show("Successfully saved member data");
Close();
}
else
{
MessageBox.Show("Failed saving. Error message " + errorMessage);
}
}
void Start()
{
sql = new SqliteHelper("data source=sqlite4unity.db");
}
private void ExportServerDatabaseToEmbedded(DatabaseType databaseType, DatabaseMenuCommandParameters parameters)
{
var filter = DataConnectionHelper.GetSqlCeFileFilter();
var scope = Scope.SchemaData;
if (databaseType == DatabaseType.SQLite)
{
filter = DataConnectionHelper.GetSqliteFileFilter();
scope = Scope.SchemaDataSQLite;
}
Debug.Assert(databaseType == DatabaseType.SQLite || databaseType == DatabaseType.SQLCE40, "Unexpected database type");
try
{
var connectionString = parameters.DatabaseInfo != null
? parameters.DatabaseInfo.ConnectionString :
DataConnectionHelper.PromptForConnectionString(_package);
if (!string.IsNullOrEmpty(connectionString))
{
var ptd = new PickTablesDialog();
int totalCount;
using (var repository = DataConnectionHelper.CreateRepository(new DatabaseInfo
{
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
ptd.Tables = repository.GetAllTableNamesForExclusion();
totalCount = ptd.Tables.Count;
}
var res = ptd.ShowModal();
if (!res.HasValue || res.Value != true || (ptd.Tables.Count >= totalCount))
{
return;
}
string dbConnectionString = null;
var fd = new SaveFileDialog
{
Title = "Export as",
Filter = filter,
OverwritePrompt = true,
ValidateNames = true
};
var result = fd.ShowDialog();
if (!result.HasValue || result.Value != true)
{
return;
}
var dbName = fd.FileName;
try
{
if (databaseType == DatabaseType.SQLCE40)
{
_package.SetStatus("Creating SQL Server Compact database...");
var helper = new SqlCeHelper4();
dbConnectionString = string.Format("Data Source={0};Max Database Size=4091", dbName);
if (File.Exists(dbName))
{
File.Delete(dbName);
}
helper.CreateDatabase(dbConnectionString);
}
if (databaseType == DatabaseType.SQLite)
{
_package.SetStatus("Creating SQLite database...");
var helper = new SqliteHelper();
dbConnectionString = string.Format("Data Source={0};", dbName);
if (File.Exists(dbName))
{
File.Delete(dbName);
}
helper.CreateDatabase(dbConnectionString);
}
var bw = new BackgroundWorker();
var workerParameters = new List <object>
{
dbConnectionString,
connectionString,
ptd.Tables,
databaseType.ToString(),
scope.ToString()
};
bw.DoWork += bw_DoExportWork;
bw.RunWorkerCompleted += (s, ea) =>
{
try
{
if (ea.Error != null)
{
DataConnectionHelper.SendError(ea.Error, databaseType, false);
}
DataConnectionHelper.LogUsage("DatabasesExportFromServer");
}
finally
{
bw.Dispose();
}
};
bw.RunWorkerAsync(workerParameters);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseType, false);
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer);
}
}
/// <summary>
/// 检查数据库文件
/// </summary>
/// <param name="dbPath">数据库路径</param>
private static void InitDb(string dbPath)
{
//如果不存在数据库文件，则创建该数据库文件
if (!File.Exists(dbPath))
{
//创建数据库
SqliteHelper.CreateDb(dbPath);
}
//sqlite帮助类实例化
_sqliteHelper = new SqliteHelper(DatabasePath);
//判断数据库是否含有shell表
DataTable dt = _sqliteHelper.GetSchema();
bool isAvailableDb = false;
foreach (DataRow row in dt.Rows)
{
if ((string)row["TABLE_NAME"] == "shell" && (string)row["TABLE_TYPE"] == "table")
{
isAvailableDb = true;
break;
}
}
if (!isAvailableDb)
{
//创建shell表
CreateTable_shell();
}
}
/// <summary>
/// Inserts a row in the i7_sflexi_items table. Returns new integer id.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="moduleID"> moduleID </param>
/// <param name="definitionGuid"> definitionGuid </param>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="sortOrder"> sortOrder </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
int moduleID,
Guid definitionGuid,
Guid itemGuid,
int sortOrder,
DateTime createdUtc,
DateTime lastModUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("insert into i7_sflexi_items ({0}) values ({1});"
, @"ItemGuid
,SiteGuid
,FeatureGuid
,ModuleGuid
,ModuleId
,DefinitionGuid
,SortOrder
,CreatedUTC
,LastModUTC"
, @":ItemGuid
,:SiteGuid
,:FeatureGuid
,:ModuleGuid
,:ModuleId
,:DefinitionGuid
,:SortOrder
,:CreatedUTC
,:LastModUTC"
);
sqlCommand.AppendLine("SELECT last_insert_rowid();");
var sqlParams = new List <SqliteParameter>
{
new SqliteParameter(":ItemGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = itemGuid.ToString()
},
new SqliteParameter(":SiteGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = siteGuid.ToString()
},
new SqliteParameter(":FeatureGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = featureGuid.ToString()
},
new SqliteParameter(":ModuleGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = moduleGuid.ToString()
},
new SqliteParameter(":ModuleId", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = moduleID
},
new SqliteParameter(":DefinitionGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input, Value = definitionGuid.ToString()
},
new SqliteParameter(":SortOrder", DbType.Int32)
{
Direction = ParameterDirection.Input, Value = sortOrder
},
new SqliteParameter(":CreatedUTC", DbType.DateTime)
{
Direction = ParameterDirection.Input, Value = createdUtc
},
new SqliteParameter(":LastModUTC", DbType.DateTime)
{
Direction = ParameterDirection.Input, Value = lastModUtc
}
};
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
sqlParams.ToArray()).ToString()));
}
public bool IsCommandeIdExist(int id)
{
bool isExist = false;
using (var helper = new SqliteHelper("SELECT Id FROM Commande WHERE Id = @ID "))
{
helper.AddInParameter("ID", DbType.Int32, id);
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
isExist = reader.GetBoolFromReader("ID");
}
}
return isExist;
}
private void newToolStripMenuItem_Click(object sender, EventArgs e)
{
if (_sh != null) _sh.Close();
_rendering = true;
dgv.Rows.Clear();
sfd.Filter = "Reference Database|*.bdb";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) return;
_sh = new SqliteHelper(sfd.FileName);
_sh.CreateTable();
_rendering = false;
}
public int GetMaxCodeByFamille(Famille famille)
{
var code = 0;
using (var helper = new SqliteHelper("SELECT max(code) code FROM produit WHERE familleId = @familleId"))
{
helper.AddInParameter("familleId", DbType.Int32, famille.Id);
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
code = reader.GetIntFromReader("code");
}
}
return code;
}
public bool Update(Produit produit)
{
bool isUpdated;
using (var helper = new SqliteHelper("UPDATE produit set Libelle = @Libelle, familleId = @familleId, Code = @Code WHERE Id = @Id"))
{
helper.AddInParameter("Libelle", DbType.String, produit.Libelle);
helper.AddInParameter("Code", DbType.Int32, produit.Code);
helper.AddInParameter("familleId", DbType.Int32, produit.Famille.Id);
helper.AddInParameter("Id", DbType.Int32, produit.Id);
isUpdated = helper.ExecuteNonQuery();
}
return isUpdated;
}
public void Update(Tag tagType)
{
SqliteHelper.Update(UpdateQuery, UpdateCommandBinder, tagType);
}
public SortableBindingList<Produit> ListProduitStock()
{
var sb = new StringBuilder();
sb.Append("SELECT p.Id as IdProduit, p.code, p.Libelle as LibelleProduit,");
sb.Append(" f.Id as IdFamille, f.Libelle as LibelleFamille ,");
sb.Append(" sum(l.QteKilo) as TotalKilo , sum(l.QteDemiKilo) as TotalDemiKilo");
sb.Append(" FROM LigneCommande l, Produit p , Famille f, Commande c");
sb.Append(" WHERE l.CommandeId = c.Id AND l.ProduitId = p.Id AND p.FamilleId = f.id AND c.IsLivree = 0");
sb.Append(" group by p.id order by f.id, p.code ");
var produits = new SortableBindingList<Produit>();
using (var helper = new SqliteHelper(sb.ToString()))
{
using (var reader = helper.ExecuteQuery())
{
while (reader.Read())
{
produits.Add(
new Produit
{
Id = reader.GetIntFromReader("IdProduit"),
Code = reader.GetIntFromReader("Code"),
Libelle = reader.GetStringFromReader("LibelleProduit"),
TotalQteKilo = reader.GetIntFromReader("TotalKilo"),
TotalQteDemiKilo = reader.GetIntFromReader("TotalDemiKilo"),
Famille = new Famille
{
Id = reader.GetIntFromReader("IdFamille"),
Libelle = reader.GetStringFromReader("LibelleFamille")
}
}
);
}
}
}
return produits;
}
public void Delete(int id)
{
SqliteHelper.Delete(DeleteQuery, DeleteCommandBinder, new TagType {
Id = id
});
}
public void ReadData()
{
var Datos = SqliteHelper.LlenarDataSet(databasePath, "SELECT id as \"ID\", ci as \"CI\", name as \"Nombre\", level as \"Nivel\" from users where name LIKE '%" + txt_SearchFied.Text + "%' OR ci LIKE '%" + txt_SearchFied.Text + "%'").Tables[0];
dgv_List.DataSource = Datos;
}
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) return;
if (ofd.FileName.EndsWith("bdb"))
{
if (_sh != null) _sh.Close();
_sh = new SqliteHelper(ofd.FileName); refreshRows();
Properties.Settings.Default.RecentLib = ofd.FileName;
Properties.Settings.Default.Save();
}
else
{
importRecords(System.IO.File.ReadAllText(ofd.FileName));
}
}
public int GetNextId()
{
int nextId = -1;
using (var helper = new SqliteHelper("SELECT SEQ FROM SQLITE_SEQUENCE WHERE NAME = 'Commande' "))
{
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
nextId = reader.GetIntFromReader("SEQ");
}
}
if (nextId == -1)
throw new Exception("le code de la commande retourer par la séquence n'est pas valide !");
return ++nextId;
}
/// <summary>
/// Updates a row in the mp_SurveyQuestions table. Returns true if row updated.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionName"> questionName </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>bool</returns>
public static bool Update(
Guid questionGuid,
Guid pageGuid,
string questionName,
string questionText,
int questionTypeId,
bool answerIsRequired,
int questionOrder,
string validationMessage
)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
string sqlCommand = @"
UPDATE
mp_SurveyQuestions
SET
PageGuid = :PageGuid,
QuestionName = :QuestionName,
QuestionText = :QuestionText,
QuestionTypeId = :QuestionTypeId,
AnswerIsRequired = :AnswerIsRequired,
QuestionOrder = :QuestionOrder,
ValidationMessage = :ValidationMessage
WHERE
QuestionGuid = :QuestionGuid;" ;
var arParams = new List <SqliteParameter>
{
new SqliteParameter(":QuestionGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input,
Value = questionGuid.ToString()
},
new SqliteParameter(":PageGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input,
Value = pageGuid.ToString()
},
new SqliteParameter(":QuestionName", DbType.String, 256)
{
Direction = ParameterDirection.Input,
Value = questionName
},
new SqliteParameter(":QuestionText", DbType.Object)
{
Direction = ParameterDirection.Input,
Value = questionText
},
new SqliteParameter(":QuestionTypeId", DbType.Int32)
{
Direction = ParameterDirection.Input,
Value = questionTypeId
},
new SqliteParameter(":AnswerIsRequired", DbType.Int32)
{
Direction = ParameterDirection.Input,
Value = intAnswerIsRequired
},
new SqliteParameter(":QuestionOrder", DbType.Int32)
{
Direction = ParameterDirection.Input,
Value = questionOrder
},
new SqliteParameter(":ValidationMessage", DbType.String, 256)
{
Direction = ParameterDirection.Input,
Value = validationMessage
}
};
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand,
arParams.ToArray()
);
return(rowsAffected > -1);
}
public SortableBindingList<Commande> Recherche(Commande commande)
{
var sb = new StringBuilder("SELECT Id, DateCommande, NomPrenomClient, IsLivree, DateLivraison FROM Commande WHERE 1 = 1");
if (commande.DateCommande.HasValue)
sb.Append(" AND DateCommande = @DateCommande");
if (!string.IsNullOrEmpty(commande.NomPrenomClient.Trim()))
sb.Append(" AND upper(NomPrenomClient) like upper('%' || @NomPrenomClient || '%') ");
if (commande.Id > 0)
sb.Append(" AND Id = @Id");
if (!commande.IsLivree)
sb.Append(" AND IsLivree = @IsLivree");
sb.Append(" ORDER BY Id ");
var commandes = new SortableBindingList<Commande>();
using (var helper = new SqliteHelper(sb.ToString()))
{
if (commande.DateCommande.HasValue)
helper.AddInParameter("DateCommande", DbType.DateTime, commande.DateCommande.Value.Date);
if (!string.IsNullOrEmpty(commande.NomPrenomClient.Trim()))
helper.AddInParameter("NomPrenomClient", DbType.String, commande.NomPrenomClient);
if (commande.Id > 0)
helper.AddInParameter("Id", DbType.Int32, commande.Id);
if (!commande.IsLivree)
helper.AddInParameter("IsLivree", DbType.Boolean, false);
using (var reader = helper.ExecuteQuery())
{
while (reader.Read())
{
commandes.Add(new Commande()
{
Id = reader.GetIntFromReader("Id"),
DateCommande = reader.GetDateTimeFromReader("DateCommande"),
NomPrenomClient = reader.GetStringFromReader("NomPrenomClient"),
IsLivree = reader.GetBoolFromReader("IsLivree"),
DateLivraison = reader.GetDateTimeNullableFromReader("DateLivraison")
});
}
}
}
return commandes;
}
/// <summary>
/// Inserts a row in the mp_SurveyQuestions table. Returns rows affected count.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionName"> questionName </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>int</returns>
public static int Add(
Guid questionGuid,
Guid pageGuid,
string questionName,
string questionText,
int questionTypeId,
bool answerIsRequired,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
string sqlCommand = @"
INSERT INTO
mp_SurveyQuestions (
QuestionGuid,
PageGuid,
QuestionName,
QuestionText,
QuestionTypeId,
AnswerIsRequired,
QuestionOrder,
ValidationMessage
)
SELECT
:QuestionGuid,
:PageGuid,
:QuestionName,
:QuestionText,
:QuestionTypeId,
:AnswerIsRequired,
Count(*),
:ValidationMessage
FROM
mp_SurveyPages;" ;
var arParams = new List <SqliteParameter>
{
new SqliteParameter(":QuestionGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input,
Value = questionGuid.ToString()
},
new SqliteParameter(":PageGuid", DbType.String, 36)
{
Direction = ParameterDirection.Input,
Value = pageGuid.ToString()
},
new SqliteParameter(":QuestionName", DbType.String, 256)
{
Direction = ParameterDirection.Input,
Value = questionName
},
new SqliteParameter(":QuestionText", DbType.Object)
{
Direction = ParameterDirection.Input,
Value = questionText
},
new SqliteParameter(":QuestionTypeId", DbType.Int32)
{
Direction = ParameterDirection.Input,
Value = questionTypeId
},
new SqliteParameter(":AnswerIsRequired", DbType.Int32)
{
Direction = ParameterDirection.Input,
Value = intAnswerIsRequired
},
new SqliteParameter(":ValidationMessage", DbType.String, 256)
{
Direction = ParameterDirection.Input,
Value = validationMessage
},
};
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand,
arParams.ToArray()
);
return(rowsAffected);
}
public Commande Get(int id)
{
var commande = new Commande() { Id = id };
using (var helper = new SqliteHelper("SELECT Id, DateCommande, NomPrenomClient, IsLivree, DateLivraison FROM Commande WHERE Id = @Id"))
{
helper.AddInParameter("Id", DbType.Int32, id);
using (var reader = helper.ExecuteQuery())
{
if (reader.Read())
{
commande.NomPrenomClient = reader.GetStringFromReader("NomPrenomClient");
commande.DateCommande = reader.GetDateTimeFromReader("DateCommande");
commande.IsLivree = reader.GetBoolFromReader("IsLivree");
commande.DateLivraison = reader.GetDateTimeNullableFromReader("DateLivraison");
}
}
}
return commande;
}
/// <summary>
/// 检查数据库文件
/// </summary>
/// <param name="dbPath">数据库路径</param>
private static void InitDb(string dbPath)
{
//如果不存在数据库文件，则创建该数据库文件
if (!File.Exists(dbPath))
{
//创建数据库
SqliteHelper.CreateDb(dbPath);
}
//sqlite帮助类实例化
_sqliteHelper = new SqliteHelper(DatabasePath);
//判断数据库是否含有shell表
DataTable dt = _sqliteHelper.GetSchema();
bool isAvailableDb = false;
foreach (DataRow row in dt.Rows)
{
if ((string)row["TABLE_NAME"] == "shell" && (string)row["TABLE_TYPE"] == "table")
{
//修复之前旧版数据库缺少（status）的问题
DataTable old = GetDataTable();
if (!old.Columns.Contains("status"))
{
//添加status列
_sqliteHelper.ExecuteNonQuery("alter table shell add column status TEXT;", null);
}
isAvailableDb = true;
break;
}
}
if (!isAvailableDb)
{
//创建shell表
CreateTable_shell();
}
}
