// Creates a new parameter for a value in this expression translator
internal SQLiteParameter CreateParameter(object value, DbType dbType)
{
string parameterName = string.Concat("@p", parameterNameCount.ToString(CultureInfo.InvariantCulture));
parameterNameCount++;
SQLiteParameter parameter = new SQLiteParameter(parameterName, value);
parameter.DbType = dbType;
_parameters.Add(parameter);
return parameter;
}
/// <summary>
/// Creates the parameter.
/// </summary>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="parameterType">Parameter type.</param>
/// <param name="parameterValue">Parameter value.</param>
/// <returns>SQLiteParameter</returns>
public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.DbType = parameterType;
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
return parameter;
}
public void addNewRecord(QuoteHeader quoteRecord)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
if (conn == null)
{
throw new Exception("Connection String is Null. Set the value of Connection String in MovieCatalog->Properties-?Settings.settings");
}
else if (quoteRecord == null)
{
throw new Exception("The passed argument 'movieRecord' is null");
}
SQLiteCommand query = new SQLiteCommand("addRecord", conn);
conn.Open();
query.CommandType = CommandType.StoredProcedure;
SQLiteParameter param1 = new SQLiteParameter("jobno", SqlDbType.VarChar);
SQLiteParameter param2 = new SQLiteParameter("qt_date", SqlDbType.VarChar);
SQLiteParameter param3 = new SQLiteParameter("cust", SqlDbType.VarChar);
SQLiteParameter param4 = new SQLiteParameter("cust_contact", SqlDbType.VarChar);
SQLiteParameter param5 = new SQLiteParameter("cust_phone", SqlDbType.VarChar);
SQLiteParameter param6 = new SQLiteParameter("cust_email", SqlDbType.VarChar);
SQLiteParameter param7 = new SQLiteParameter("loc", SqlDbType.VarChar);
SQLiteParameter param8 = new SQLiteParameter("salesman", SqlDbType.VarChar);
SQLiteParameter param9 = new SQLiteParameter("days_est", SqlDbType.Int);
SQLiteParameter param10 = new SQLiteParameter("status", SqlDbType.VarChar);
SQLiteParameter param11 = new SQLiteParameter("pipe_line_size", SqlDbType.VarChar);
SQLiteParameter param12 = new SQLiteParameter("pipe_length", SqlDbType.VarChar);
SQLiteParameter param13 = new SQLiteParameter("pressure", SqlDbType.VarChar);
SQLiteParameter param14 = new SQLiteParameter("endclient", SqlDbType.VarChar);
SQLiteParameter param15 = new SQLiteParameter("supervisor", SqlDbType.VarChar);
SQLiteParameter param16 = new SQLiteParameter("est_start_date", SqlDbType.VarChar);
SQLiteParameter param17 = new SQLiteParameter("est_end_date", SqlDbType.VarChar);
SQLiteParameter param18 = new SQLiteParameter("value", SqlDbType.Real);
param1.Value = quoteRecord.jobno;
param2.Value = quoteRecord.qt_date;
param3.Value = quoteRecord.cust;
param4.Value = quoteRecord.cust_contact;
param5.Value = quoteRecord.cust_phone;
param6.Value = quoteRecord.cust_email;
param7.Value = quoteRecord.loc;
param8.Value = quoteRecord.salesman;
param9.Value = quoteRecord.days_est;
param10.Value = quoteRecord.status;
param11.Value = quoteRecord.pipe_line_size;
param12.Value = quoteRecord.pipe_length;
param13.Value = quoteRecord.pressure;
param14.Value = quoteRecord.endclient;
param15.Value = quoteRecord.supervisor;
param16.Value = quoteRecord.est_startdate;
param17.Value = quoteRecord.est_enddate;
param18.Value = quoteRecord.value;
query.Parameters.Add(param1);
query.Parameters.Add(param2);
query.Parameters.Add(param3);
query.Parameters.Add(param4);
query.Parameters.Add(param5);
query.Parameters.Add(param6);
query.Parameters.Add(param7);
query.Parameters.Add(param8);
query.Parameters.Add(param9);
query.Parameters.Add(param10);
query.Parameters.Add(param11);
query.Parameters.Add(param12);
query.Parameters.Add(param13);
query.Parameters.Add(param14);
query.Parameters.Add(param15);
query.Parameters.Add(param16);
query.Parameters.Add(param17);
query.Parameters.Add(param18);
query.ExecuteNonQuery();
}
}
public static List <string> GetAvailableEpisodes(int progid, bool fetchAll)
{
Guid providerId;
string progExtId;
ProgrammeInfo progInfo;
using (SQLiteCommand command = new SQLiteCommand("select pluginid, extid, name, description, singleepisode from programmes where progid=@progid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@progid", progid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
throw new DataNotFoundException(progid, "Programme does not exist");
}
providerId = new Guid(reader.GetString(reader.GetOrdinal("pluginid")));
progExtId = reader.GetString(reader.GetOrdinal("extid"));
progInfo = new ProgrammeInfo();
progInfo.Name = reader.GetString(reader.GetOrdinal("name"));
int descriptionOrdinal = reader.GetOrdinal("description");
if (!reader.IsDBNull(descriptionOrdinal))
{
progInfo.Description = reader.GetString(descriptionOrdinal);
}
progInfo.SingleEpisode = reader.GetBoolean(reader.GetOrdinal("singleepisode"));
}
}
if (!Provider.Exists(providerId))
{
return(null);
}
// Fetch a list of previously available episodes for the programme
List <string> previousAvailable = new List <string>();
using (SQLiteCommand command = new SQLiteCommand("select extid from episodes where progid=@progid and available=1 order by date desc", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@progid", progid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int epidOrdinal = reader.GetOrdinal("extid");
while (reader.Read())
{
previousAvailable.Add(reader.GetString(epidOrdinal));
}
}
}
List <string> allEpExtIds = new List <string>();
int page = 1;
IRadioProvider providerInst = Provider.GetFromId(providerId).CreateInstance();
AvailableEpisodes available;
do
{
try
{
available = providerInst.GetAvailableEpisodes(progExtId, progInfo, page);
}
catch (Exception provExp)
{
provExp.Data.Add("Programme ExtID", progExtId);
throw new ProviderException("Call to GetAvailableEpisodeIds failed", provExp, providerId);
}
if (available.EpisodeIds == null || available.EpisodeIds.Length == 0)
{
break;
}
int trackOverlap = -1;
foreach (string epExtId in available.EpisodeIds)
{
// Append the returned IDs to the list of all episodes (minus duplicates)
if (!allEpExtIds.Contains(epExtId))
{
allEpExtIds.Add(epExtId);
}
if (previousAvailable.Contains(epExtId))
{
// Store where the available & previously available ID lists overlap
trackOverlap = previousAvailable.IndexOf(epExtId);
}
else if (trackOverlap >= 0)
{
// Bump up the overlap index to show there are more after the overlap
trackOverlap++;
}
}
if (available.MoreAvailable && !fetchAll)
{
if (trackOverlap >= 0)
{
// Remove previously available programmes before this page from the list so that they
// are not incorrectly un-flagged as available in the database
if (trackOverlap < previousAvailable.Count - 1)
{
previousAvailable.RemoveRange(trackOverlap + 1, previousAvailable.Count - (trackOverlap + 1));
}
// Stop fetching available episode pages
break;
}
}
page++;
}while (available.MoreAvailable);
// Remove the still available episodes from the previously available list
foreach (string epExtId in allEpExtIds)
{
previousAvailable.Remove(epExtId);
}
// Unflag any no-longer available episodes in the database
if (previousAvailable.Count > 0)
{
lock (Database.DbUpdateLock)
{
using (SQLiteMonTransaction transMon = new SQLiteMonTransaction(FetchDbConn().BeginTransaction()))
{
using (SQLiteCommand command = new SQLiteCommand("update episodes set available=0 where progid=@progid and extid=@extid", FetchDbConn(), transMon.Trans))
{
SQLiteParameter extidParam = new SQLiteParameter("@extid");
command.Parameters.Add(new SQLiteParameter("@progid", progid));
command.Parameters.Add(extidParam);
foreach (string epExtId in previousAvailable)
{
extidParam.Value = epExtId;
command.ExecuteNonQuery();
}
}
transMon.Trans.Commit();
}
}
}
return(allEpExtIds);
}
internal static SQLiteCommand UpdateEntryPoint(ConnectionContextSQLite ctx, EntryPoint ep)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
@"UPDATE YcEntry SET
Id = @Id,
Type = @Type,
RateId = @RateId,
YieldCurveId = @YieldCurveId,
Length = @Length,
TimeUnit = @TimeUnit,
DateStart = @DateStart,
DateFinish = @DateFinish,
DataProviderId = @DataProviderId,
DataReference = @DataReference,
IpCreated = @IpCreated
WHERE 1 == 1 AND UserCreated = @user"
);
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
p = new SQLiteParameter("@Id", DbType.Int32);
p.Value = ep.Id;
command.Parameters.Add(p);
p = new SQLiteParameter("@Type", DbType.String);
p.Value = ep.Type;
command.Parameters.Add(p);
p = new SQLiteParameter("@RateId", DbType.Int32);
p.Value = ep.Instrument.Id;
command.Parameters.Add(p);
p = new SQLiteParameter("@YieldCurveId", DbType.Int32);
p.Value = ep.YieldCurveId;
command.Parameters.Add(p);
p = new SQLiteParameter("@Length", DbType.Int32);
p.Value = ep.Length;
command.Parameters.Add(p);
p = new SQLiteParameter("@TimeUnit", DbType.String);
p.Value = ep.TimeUnit;
command.Parameters.Add(p);
p = new SQLiteParameter("@DateStart", DbType.DateTime);
p.Value = ep.ValidDateBegin;
command.Parameters.Add(p);
p = new SQLiteParameter("@DateEnd", DbType.DateTime);
p.Value = ep.ValidDateEnd;
command.Parameters.Add(p);
p = new SQLiteParameter("@DataProviderId", DbType.Int32);
p.Value = ep.DataProviderId;
command.Parameters.Add(p);
p = new SQLiteParameter("@DataReference", DbType.String);
p.Value = ep.DataReference;
command.Parameters.Add(p);
p = new SQLiteParameter("@IpCreated", DbType.String);
p.Value = null;
command.Parameters.Add(p);
command.CommandText = sb.ToString();
return(command);
}
/// <summary>
/// Constructs a new table exists command instance
/// </summary>
public TableExistsCommand(SQLiteConnection connection)
{
_command = new SQLiteCommand(Queries.TableExists, connection);
_tableName = new SQLiteParameter("@tableName");
_command.Parameters.Add(_tableName);
}
protected override void addIsosResults(List <IsosResult> isosResultList)
{
var myconnection = (SQLiteConnection)cnn;
using (var mytransaction = myconnection.BeginTransaction())
{
using (var mycommand = new SQLiteCommand(myconnection))
{
var featureIDParam = new SQLiteParameter();
var scanNumParam = new SQLiteParameter();
var chargeParam = new SQLiteParameter();
var abundanceParam = new SQLiteParameter();
var mzParam = new SQLiteParameter();
var fitParam = new SQLiteParameter();
var averageMWParam = new SQLiteParameter();
var monoIsotopicMWParam = new SQLiteParameter();
var mostAbundantMWParam = new SQLiteParameter();
var fwhmParam = new SQLiteParameter();
var sigNoiseParam = new SQLiteParameter();
var monoAbundanceParam = new SQLiteParameter();
var monoPlus2AbundParam = new SQLiteParameter();
var flagCodeParam = new SQLiteParameter();
int n;
mycommand.CommandText = "INSERT INTO T_MSFeatures ([feature_id],[scan_num],[charge],[abundance],[mz],[fit],[average_mw],[monoisotopic_mw],[mostabundant_mw],[fwhm],[signal_noise],[mono_abundance],[mono_plus2_abundance],[flag]) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
mycommand.Parameters.Add(featureIDParam);
mycommand.Parameters.Add(scanNumParam);
mycommand.Parameters.Add(chargeParam);
mycommand.Parameters.Add(abundanceParam);
mycommand.Parameters.Add(mzParam);
mycommand.Parameters.Add(fitParam);
mycommand.Parameters.Add(averageMWParam);
mycommand.Parameters.Add(monoIsotopicMWParam);
mycommand.Parameters.Add(mostAbundantMWParam);
mycommand.Parameters.Add(fwhmParam);
mycommand.Parameters.Add(sigNoiseParam);
mycommand.Parameters.Add(monoAbundanceParam);
mycommand.Parameters.Add(monoPlus2AbundParam);
mycommand.Parameters.Add(flagCodeParam);
for (n = 0; n < isosResultList.Count; n++)
{
featureIDParam.Value = isosResultList[n].MSFeatureID;
scanNumParam.Value = isosResultList[n].ScanSet.PrimaryScanNumber;
chargeParam.Value = isosResultList[n].IsotopicProfile.ChargeState;
abundanceParam.Value = isosResultList[n].IsotopicProfile.GetAbundance();
mzParam.Value = isosResultList[n].IsotopicProfile.GetMZ();
fitParam.Value = isosResultList[n].IsotopicProfile.GetScore();
averageMWParam.Value = isosResultList[n].IsotopicProfile.AverageMass;
monoIsotopicMWParam.Value = isosResultList[n].IsotopicProfile.MonoIsotopicMass;
mostAbundantMWParam.Value = isosResultList[n].IsotopicProfile.MostAbundantIsotopeMass;
fwhmParam.Value = isosResultList[n].IsotopicProfile.GetFWHM();
sigNoiseParam.Value = isosResultList[n].IsotopicProfile.GetSignalToNoise();
monoAbundanceParam.Value = isosResultList[n].IsotopicProfile.GetMonoAbundance();
monoPlus2AbundParam.Value = isosResultList[n].IsotopicProfile.GetMonoPlusTwoAbundance();
flagCodeParam.Value = ResultValidators.ResultValidationUtils.GetStringFlagCode(isosResultList[n].Flags);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
}
/// <summary>
/// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection.
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>SQLiteParameterCollection</returns>
/// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks>
private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == 0)
{
return(null);
}
SQLiteParameterCollection coll = cmd.Parameters;
string parmString = commandText.Substring(commandText.IndexOf("@"));
// pre-process the string so always at least 1 space after a comma.
parmString = parmString.Replace(",", " ,");
// get the named parameters into a match collection
string pattern = @"(@)\S*(.*?)\b";
Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = ex.Matches(parmString);
string[] paramNames = new string[mc.Count];
int i = 0;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
}
// now let's type the parameters
int j = 0;
Type t = null;
foreach (object o in paramList)
{
t = o.GetType();
SQLiteParameter parm = new SQLiteParameter();
switch (t.ToString())
{
case ("DBNull"):
case ("Char"):
case ("SByte"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
throw new SystemException("Invalid data type");
case ("System.String"):
parm.DbType = DbType.String;
parm.ParameterName = paramNames[j];
parm.Value = (string)paramList[j];
coll.Add(parm);
break;
case ("System.Byte[]"):
parm.DbType = DbType.Binary;
parm.ParameterName = paramNames[j];
parm.Value = (byte[])paramList[j];
coll.Add(parm);
break;
case ("System.Int32"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = (int)paramList[j];
coll.Add(parm);
break;
case ("System.Boolean"):
parm.DbType = DbType.Boolean;
parm.ParameterName = paramNames[j];
parm.Value = (bool)paramList[j];
coll.Add(parm);
break;
case ("System.DateTime"):
parm.DbType = DbType.DateTime;
parm.ParameterName = paramNames[j];
parm.Value = System.Convert.ToDateTime(paramList[j]);
coll.Add(parm);
break;
case ("System.Double"):
parm.DbType = DbType.Double;
parm.ParameterName = paramNames[j];
parm.Value = System.Convert.ToDouble(paramList[j]);
coll.Add(parm);
break;
case ("System.Decimal"):
parm.DbType = DbType.Decimal;
parm.ParameterName = paramNames[j];
parm.Value = System.Convert.ToDecimal(paramList[j]);
break;
case ("System.Guid"):
parm.DbType = DbType.Guid;
parm.ParameterName = paramNames[j];
parm.Value = (System.Guid)(paramList[j]);
break;
case ("System.Object"):
parm.DbType = DbType.Object;
parm.ParameterName = paramNames[j];
parm.Value = paramList[j];
coll.Add(parm);
break;
default:
throw new SystemException("Value is of unknown data type");
} // end switch
j++;
}
return(coll);
}
/// <summary>
/// 添加多个用户
/// </summary>
/// <param name="Users"></param>
public static void AddUsers(List <exUser> Users)
{
#region 多次添加
//try
//{
// string sql = "insert into Users(UserID,UserName,GroupID,orderID) values(@UserID,@UserName,@GroupID,@orderID)";
// foreach (OurMsg.Organization.exUser user in Users)
// {
// System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[]{
// new System.Data.SQLite.SQLiteParameter("@UserID",user.UserID ),
// new System.Data.SQLite.SQLiteParameter("@UserName",user.UserName),
// new System.Data.SQLite.SQLiteParameter("@GroupID",user.GroupID),
// new System.Data.SQLite.SQLiteParameter("@orderID",user.OrderID),
// };
// SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
// }
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Source + ex.Message);
//}
#endregion
#region 一次性事务添加
SQLiteConnection con = new SQLiteConnection(); //创建连接
SQLiteCommand cmd = null;
con.ConnectionString = IMLibrary3.Data.SQLiteDBHelper.connectionString;
try
{
con.Open();
using (SQLiteTransaction dbTrans = con.BeginTransaction()) //使用事务
{
using (cmd = con.CreateCommand())
{
cmd.CommandText = "insert into Users(UserID,UserName,GroupID,orderID) values(?,?,?,?)";
SQLiteParameter Field1 = cmd.CreateParameter(); //添加字段
SQLiteParameter Field2 = cmd.CreateParameter();
SQLiteParameter Field3 = cmd.CreateParameter();
SQLiteParameter Field4 = cmd.CreateParameter();
cmd.Parameters.Add(Field1);
cmd.Parameters.Add(Field2);
cmd.Parameters.Add(Field3);
cmd.Parameters.Add(Field4);
foreach (exUser user in Users)
{
Field1.Value = user.UserID; //字符赋值
Field2.Value = user.UserName;
Field3.Value = user.GroupID;
Field4.Value = user.OrderID;
cmd.ExecuteNonQuery();
}
}
dbTrans.Commit(); //提交事务执行
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ex.Message);
}
finally
{
con.Close();
}
#endregion
}
private static void DbInsertString(string hit, SQLiteConnection dbCon)
{
string insertText1 = "INSERT INTO strings (string, length, ascii_only";
string insertText2 = ") VALUES (?," + hit.Length + "," + (IsAscii(hit)?1:0);
foreach (KeyValuePair<string, string> re in _regExPatterns)
{
Regex reg = new Regex(re.Value, RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace);
if (reg.IsMatch(hit))
{
insertText1 += "," + re.Key;
insertText2 += ",1";
}
}
insertText2 += ")";
//_logger.Info(insertText1 + insertText2);
SQLiteCommand dbCmd = new SQLiteCommand(insertText1 + insertText2, dbCon);
SQLiteParameter data = new SQLiteParameter();
dbCmd.Parameters.Add(data);
data.Value = hit;
dbCmd.ExecuteNonQuery();
}
/// <summary>
/// 批量保存生活费信息,存在就修改,否则就新增
/// </summary>
/// <param name="lifes"></param>
/// <returns></returns>
public bool Save(List <LifingCost> lifes)
{
try
{
List <SqlHashTable> list = new List <SqlHashTable>();
foreach (var index in lifes)
{
var b = Select(index.Id);
if (b == null)
{
#region 新增
string sql = string.Format(@"insert into Lifing_Cost(Id,TIME,Reason,Price,Cost_Type_Id,Notes,Img_Url,Create_By,Create_Time,UpDate_By,UpDate_Time,IsMark,FamilyPay,CusGroup)
values(@Id,@TIME,@Reason,@Price,@Cost_Type_Id,@Notes,@Img_Url,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time,@IsMark,@FamilyPay,@CusGroup)");
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Reason", index.Reason),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Cost_Type_Id", index.CostTypeId),
new SQLiteParameter("@Notes", index.Notes),
new SQLiteParameter("@Img_Url", index.ImgUrl),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime),
new SQLiteParameter("@IsMark", index.IsMark),
new SQLiteParameter("@FamilyPay", index.FamilyPay),
new SQLiteParameter("@CusGroup", index.CusGroup)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
else
{
#region 修改
string sql = "update Lifing_Cost set Id=@Id,TIME=@TIME,Reason=@Reason,Price=@Price,Cost_Type_Id=@Cost_Type_Id,Notes=@Notes,Img_Url=@Img_Url,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time,IsMark=@IsMark,FamilyPay=@FamilyPay,CusGroup=@CusGroup where Id=@Id";
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Reason", index.Reason),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Cost_Type_Id", index.CostTypeId),
new SQLiteParameter("@Notes", index.Notes),
new SQLiteParameter("@Img_Url", index.ImgUrl),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime),
new SQLiteParameter("@IsMark", index.IsMark),
new SQLiteParameter("@FamilyPay", index.FamilyPay),
new SQLiteParameter("@CusGroup", index.CusGroup)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
}
SqlLiteHelper.ExecuteSql(list);
return(true);
}
catch (Exception ex)
{
LogHelper.Log.Error("批量保存生活费信息", ex);
return(false);
}
}
/// <summary>
/// 创建一个传出参数
/// </summary>
/// <param name="name">参数名称</param>
/// <param name="type">参数类型</param>
/// <param name="size">参数大小</param>
/// <returns></returns>
public static SQLiteParameter MakeOutParam(string name, DbType type = DbType.String, int size = 50)
{
var para = new SQLiteParameter(name, type, size);
para.Direction = ParameterDirection.Output;
return para;
}
/// <summary>
/// Zapise pozadovane data do SQLite databazy
/// </summary>
/// <param name="items">Pole itemov ktore chceme ulozit do databazy</param>
private void WriteTraceItems(TraceItem[] items)
{
//osetrenie
if (items == null || items.Length == 0)
{
return;
}
try
{
//overime pripojenie
if (this._connection == null ||
this._connection.State != ConnectionState.Open)
{
//vykonname obnovu pripojenia
this.Reconnect();
}
//inicializacia command
using (SQLiteCommand command = new SQLiteCommand())
{
command.Connection = _connection;
#region - Create Table -
//command na overenie existencie tabulky
String commandString = "CREATE TABLE IF NOT EXISTS 'TraceItems' (" +
"'DateTime' DATETIME NOT NULL," +
"'Type' TEXT NOT NULL," +
"'Modul' TEXT NOT NULL," +
"'Message' TEXT NOT NULL" +
")";
command.CommandText = commandString;
//vykoname prikaz na pridanie tabulky
command.ExecuteNonQuery();
#endregion
#region - Create Index -
//command na pridanie indexov do tabulky
commandString = "CREATE INDEX IF NOT EXISTS 'IX_TraceItems_DateTime'" +
"ON 'TraceItems' ('DateTime' ASC, 'Modul' ASC)";
command.CommandText = commandString;
//vykoname prikaz na pridanie indexu
command.ExecuteNonQuery();
#endregion
#region - Delete Old -
//ak je poziadavka na zmazanie starsich zaznamov
if (this._deleteOlderThan)
{
//zmazeme starsie zaznamy ak recipients je pozadovane
commandString = "DELETE FROM 'TraceItems' WHERE DateTime <= @dateTime";
command.CommandText = commandString;
//parametre
SQLiteParameter deleteParam = new SQLiteParameter("@dateTime", DbType.DateTime);
deleteParam.Value = DateTime.Now.Subtract(this._deleteOlderThanTimeout);
//pridame parameter
command.Parameters.Clear();
command.Parameters.Add(deleteParam);
//vykoname prikaz
command.ExecuteNonQuery();
}
#endregion
#region - Insert Data -
//vynulujeme command
commandString = String.Empty;
//zmazeme predosle parametre
command.Parameters.Clear();
//vytvorime command na vsetky inserty
for (int i = 0; i < items.Length; i++)
{
//command na pridanie zaznamu do tabulky
commandString += String.Format("INSERT INTO 'TraceItems' (" +
"DateTime, Type, Modul, Message" +
")" +
"VALUES (" +
"@dateTime{0}, @type{0}, @modul{0}, @message{0}" +
"); ", i);
command.CommandText = commandString;
//Vytvorime parametre
SQLiteParameter dt_param = new SQLiteParameter(
String.Format("@dateTime{0}", i), DbType.DateTime);
dt_param.Value = items[i].Time;
SQLiteParameter type_param = new SQLiteParameter(
String.Format("@type{0}", i), DbType.String);
type_param.Value = items[i].TraceType.ToString();
SQLiteParameter modul_param = new SQLiteParameter(
String.Format("@modul{0}", i), DbType.String);
modul_param.Value = items[i].ModulName;
SQLiteParameter msg_param = new SQLiteParameter(
String.Format("@message{0}", i), DbType.String);
msg_param.Value = items[i].Message;
//pridame parametre do zoznamu
command.Parameters.AddRange(new SQLiteParameter[] { dt_param, type_param,
modul_param, msg_param });
}
//vykoname prikaz na pridanie tabulky
command.ExecuteNonQuery();
#endregion
}
}
catch (SQLiteException ex)
{
//interna chyba
this.InternalException(ex, "Error during tracing data to SQLite [{0}]. {1}", ex.ErrorCode, ex.Message);
}
catch (Exception ex)
{
//interna chyba
this.InternalException(ex, "Error during tracing data to SQLite. {0}", ex.Message);
}
}
/// <summary>
/// Inserts a row in the mp_TaskQueue table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="queuedBy"> queuedBy </param>
/// <param name="taskName"> taskName </param>
/// <param name="notifyOnCompletion"> notifyOnCompletion </param>
/// <param name="notificationToEmail"> notificationToEmail </param>
/// <param name="notificationFromEmail"> notificationFromEmail </param>
/// <param name="notificationSubject"> notificationSubject </param>
/// <param name="taskCompleteMessage"> taskCompleteMessage </param>
/// <param name="canStop"> canStop </param>
/// <param name="canResume"> canResume </param>
/// <param name="updateFrequency"> updateFrequency </param>
/// <param name="queuedUTC"> queuedUTC </param>
/// <param name="completeRatio"> completeRatio </param>
/// <param name="status"> status </param>
/// <param name="serializedTaskObject"> serializedTaskObject </param>
/// <param name="serializedTaskType"> serializedTaskType </param>
/// <returns>int</returns>
public int Create(
Guid guid,
Guid siteGuid,
Guid queuedBy,
string taskName,
bool notifyOnCompletion,
string notificationToEmail,
string notificationFromEmail,
string notificationSubject,
string taskCompleteMessage,
bool canStop,
bool canResume,
int updateFrequency,
DateTime queuedUTC,
double completeRatio,
string status,
string serializedTaskObject,
string serializedTaskType)
{
#region Bit Conversion
int intNotifyOnCompletion;
if (notifyOnCompletion)
{
intNotifyOnCompletion = 1;
}
else
{
intNotifyOnCompletion = 0;
}
int intCanStop;
if (canStop)
{
intCanStop = 1;
}
else
{
intCanStop = 0;
}
int intCanResume;
if (canResume)
{
intCanResume = 1;
}
else
{
intCanResume = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_TaskQueue (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("QueuedBy, ");
sqlCommand.Append("TaskName, ");
sqlCommand.Append("NotifyOnCompletion, ");
sqlCommand.Append("NotificationToEmail, ");
sqlCommand.Append("NotificationFromEmail, ");
sqlCommand.Append("NotificationSubject, ");
sqlCommand.Append("TaskCompleteMessage, ");
sqlCommand.Append("CanStop, ");
sqlCommand.Append("CanResume, ");
sqlCommand.Append("UpdateFrequency, ");
sqlCommand.Append("QueuedUTC, ");
sqlCommand.Append("CompleteRatio, ");
sqlCommand.Append("Status, ");
sqlCommand.Append("SerializedTaskObject, ");
sqlCommand.Append("SerializedTaskType )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":Guid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":QueuedBy, ");
sqlCommand.Append(":TaskName, ");
sqlCommand.Append(":NotifyOnCompletion, ");
sqlCommand.Append(":NotificationToEmail, ");
sqlCommand.Append(":NotificationFromEmail, ");
sqlCommand.Append(":NotificationSubject, ");
sqlCommand.Append(":TaskCompleteMessage, ");
sqlCommand.Append(":CanStop, ");
sqlCommand.Append(":CanResume, ");
sqlCommand.Append(":UpdateFrequency, ");
sqlCommand.Append(":QueuedUTC, ");
sqlCommand.Append(":CompleteRatio, ");
sqlCommand.Append(":Status, ");
sqlCommand.Append(":SerializedTaskObject, ");
sqlCommand.Append(":SerializedTaskType )");
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[17];
arParams[0] = new SQLiteParameter(":Guid", DbType.String);
arParams[0].Value = guid.ToString();
arParams[1] = new SQLiteParameter(":SiteGuid", DbType.String);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new SQLiteParameter(":QueuedBy", DbType.String);
arParams[2].Value = queuedBy.ToString();
arParams[3] = new SQLiteParameter(":TaskName", DbType.String);
arParams[3].Value = taskName;
arParams[4] = new SQLiteParameter(":NotifyOnCompletion", DbType.Int32);
arParams[4].Value = intNotifyOnCompletion;
arParams[5] = new SQLiteParameter(":NotificationToEmail", DbType.String);
arParams[5].Value = notificationToEmail;
arParams[6] = new SQLiteParameter(":NotificationFromEmail", DbType.String);
arParams[6].Value = notificationFromEmail;
arParams[7] = new SQLiteParameter(":NotificationSubject", DbType.String);
arParams[7].Value = notificationSubject;
arParams[8] = new SQLiteParameter(":TaskCompleteMessage", DbType.Object);
arParams[8].Value = taskCompleteMessage;
arParams[9] = new SQLiteParameter(":CanStop", DbType.Int32);
arParams[9].Value = intCanStop;
arParams[10] = new SQLiteParameter(":CanResume", DbType.Int32);
arParams[10].Value = intCanResume;
arParams[11] = new SQLiteParameter(":UpdateFrequency", DbType.Int32);
arParams[11].Value = updateFrequency;
arParams[12] = new SQLiteParameter(":QueuedUTC", DbType.DateTime);
arParams[12].Value = queuedUTC;
arParams[13] = new SQLiteParameter(":CompleteRatio", DbType.Double);
arParams[13].Value = completeRatio;
arParams[14] = new SQLiteParameter(":Status", DbType.String);
arParams[14].Value = status;
arParams[15] = new SQLiteParameter(":SerializedTaskObject", DbType.Object);
arParams[15].Value = serializedTaskObject;
arParams[16] = new SQLiteParameter(":SerializedTaskType", DbType.String);
arParams[16].Value = serializedTaskType;
int rowsAffected = AdoHelper.ExecuteNonQuery(
connectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
private static void TestSQLite()
{
int i;
string databaseName = "Benchmark_SQLite.sqlite";
Console.WriteLine("\n\r" + databaseName);
if (File.Exists(databaseName))
{
File.Delete(databaseName);
}
SQLiteConnectionStringBuilder constring = new SQLiteConnectionStringBuilder();
constring.PageSize = 1024;
constring.SyncMode = SynchronizationModes.Off;
constring.DataSource = databaseName;
SQLiteConnection con = new SQLiteConnection(constring.ToString());
con.Open();
SQLiteCommand com = con.CreateCommand();
for (i = 0; i < PRAGMA_Commands.Length; i++)
{
com.CommandText = PRAGMA_Commands[i];
com.ExecuteNonQuery();
}
for (i = 0; i < CREATE_Commands.Length; i++)
{
com.CommandText = CREATE_Commands[i];
com.ExecuteNonQuery();
}
com.CommandText = "BEGIN EXCLUSIVE";
com.ExecuteNonQuery();
com.CommandText = "INSERT INTO Root VALUES (?,?)";
SQLiteParameter p1 = com.CreateParameter();
p1.DbType = DbType.Int64;
com.Parameters.Add(p1);
SQLiteParameter p2 = com.CreateParameter();
p2.DbType = DbType.String;
com.Parameters.Add(p2);
long start = DateTime.Now.Ticks;
long key = 1999;
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
p1.Value = key;
p2.Value = key.ToString();
com.ExecuteNonQuery();
}
com.CommandText = "END";
com.Parameters.Clear();
com.ExecuteNonQuery();
timer[0, 0] = DateTime.Now.Ticks - start;
Console.WriteLine("inserting " + nRecords + " records: "
+ ((timer[0, 0]) * 10e-8 + .05).ToString("#.0") + " seconds");
com.CommandText = "BEGIN EXCLUSIVE";
com.ExecuteNonQuery();
using (SQLiteCommand com2 = con.CreateCommand())
{
com.CommandText = SELECT_Bind_i;
com.Parameters.Clear();
com.Parameters.Add(p1);
com2.CommandText = SELECT_Bind_s;
com2.Parameters.Clear();
com2.Parameters.Add(p2);
start = DateTime.Now.Ticks;
key = 1999;
object[] resValues = new object[2];
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
p1.Value = key;
p2.Value = key.ToString();
using (SQLiteDataReader res = com.ExecuteReader())
{
res.Read();
res.GetValues(resValues);
}
long id = (long)resValues[0];
using (SQLiteDataReader res = com2.ExecuteReader())
{
res.Read();
res.GetValues(resValues);
}
Debug.Assert(id == ((long)resValues[0]));
}
}
timer[0, 1] = DateTime.Now.Ticks - start;
Console.WriteLine("performing " + nRecords * 2 + " index searches: "
+ ((timer[0, 1]) * 10e-8 + .05).ToString("#.0") + " seconds");
com.CommandText = "END";
com.Parameters.Clear();
com.ExecuteNonQuery();
com.CommandText = "BEGIN EXCLUSIVE";
com.ExecuteNonQuery();
start = DateTime.Now.Ticks;
com.CommandText = SELECT_Command_i;
com.Parameters.Clear();
key = Int64.MinValue;
i = 0;
using (SQLiteDataReader reader = com.ExecuteReader())
{
object[] resValues = new object[2];
while (reader.Read())
{
reader.GetValues(resValues);
long intKey = (long)resValues[0];
Debug.Assert(intKey >= key);
key = intKey;
i += 1;
}
Debug.Assert(i == nRecords);
}
com.CommandText = SELECT_Command_s;
using (SQLiteDataReader reader = com.ExecuteReader())
{
i = 0;
String strKey = "";
object[] resValues = new object[2];
while (reader.Read())
{
reader.GetValues(resValues);
string recStrKey = (string)resValues[1];
Debug.Assert(recStrKey.CompareTo(strKey) >= 0);
strKey = recStrKey;
i += 1;
}
Debug.Assert(i == nRecords);
}
timer[0, 2] = DateTime.Now.Ticks - start;
Console.WriteLine("iteration through " + (nRecords * 2) + " records: " +
((timer[0, 2]) * 10e-8 + .05).ToString("#.0") + " seconds");
com.CommandText = "END";
com.Parameters.Clear();
com.ExecuteNonQuery();
com.CommandText = "BEGIN EXCLUSIVE";
com.ExecuteNonQuery();
com.CommandText = DELETE_Bind;
com.Parameters.Clear();
com.Parameters.Add(p1);
start = DateTime.Now.Ticks;
key = 1999;
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
p1.Value = key;
com.ExecuteNonQuery();
}
com.CommandText = "END";
com.Parameters.Clear();
com.ExecuteNonQuery();
timer[0, 3] = DateTime.Now.Ticks - start;
Console.WriteLine("deleting " + nRecords + " records: "
+ ((timer[0, 3]) * 10e-8 + .05).ToString("#.0") + " seconds");
con.Close();
}
/// <summary>
/// Creates a SQLiteParameter given a name, type, and direction
/// </summary>
internal static SQLiteParameter CreateSqlParameter(SQLiteProviderManifest manifest, string name, TypeUsage type, ParameterMode mode, object value)
{
int? size;
//
// NOTE: Adjust the parameter type so that it will work with textual
// GUIDs. Please see ticket [a4d9c7ee94] for more details.
//
if ((manifest != null) && !manifest._binaryGuid &&
(MetadataHelpers.GetPrimitiveTypeKind(type) == PrimitiveTypeKind.Guid))
{
type = TypeUsage.CreateStringTypeUsage(
PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String),
false, true);
}
SQLiteParameter result = new SQLiteParameter(name, value);
// .Direction
ParameterDirection direction = MetadataHelpers.ParameterModeToParameterDirection(mode);
if (result.Direction != direction)
{
result.Direction = direction;
}
// .Size and .DbType
// output parameters are handled differently (we need to ensure there is space for return
// values where the user has not given a specific Size/MaxLength)
bool isOutParam = mode != ParameterMode.In;
DbType sqlDbType = GetSqlDbType(type, isOutParam, out size);
if (result.DbType != sqlDbType)
{
result.DbType = sqlDbType;
}
// Note that we overwrite 'facet' parameters where either the value is different or
// there is an output parameter.
if (size.HasValue && (isOutParam || result.Size != size.Value))
{
result.Size = size.Value;
}
// .IsNullable
bool isNullable = MetadataHelpers.IsNullable(type);
if (isOutParam || isNullable != result.IsNullable)
{
result.IsNullable = isNullable;
}
return result;
}
private void SaveToDatabase(FileInfo fileName, int i)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
List <Statistics> statistic_list = ExtractStatisticsFromCSV();
List <Summary> summary_list = ExtractSummaryFromCSV();
List <Errors> error_list = ExtractErrorsFromCSV();
List <Warnings> warning_list = ExtractWarningsFromCSV();
SQLiteConnection sqlite_connection;
SQLiteCommand sqlite_command;
string databasebName = fileName.FullName;
sqlite_connection = new SQLiteConnection("Data Source=" + databasebName + ";Version=3;New=True;Compress=True;");
sqlite_command = new SQLiteCommand("begin", sqlite_connection);
sqlite_connection.Open();
sqlite_command = sqlite_connection.CreateCommand();
if (numberOfClickOnDatabaseButton == 1)
{
try
{
//Statistics
sqlite_command.CommandText = "CREATE TABLE Statistics (CircuitName varchar(100), ErrorCount integer, WarningCount integer,State varchar(100),Date DATETIME,LogDirectory varchar(100),TimeStamp DATETIME);";
sqlite_command.ExecuteNonQuery();
DateTime _dateTime = DateTime.Now;
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < statistic_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Statistics (CircuitName,ErrorCount,WarningCount,State,Date,LogDirectory,TimeStamp) VALUES (@CircuitName, @ErrorCount,@WarningCount,@State, @Date, @LogDirectory,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@CircuitName", @statistic_list[n].Circuit);
mycommand.Parameters.AddWithValue("@ErrorCount", @statistic_list[n].ErrorCount);
mycommand.Parameters.AddWithValue("@WarningCount", @statistic_list[n].WarningCount);
mycommand.Parameters.AddWithValue("@State", @statistic_list[n].State);
mycommand.Parameters.AddWithValue("@Date", @statistic_list[n].Date);
mycommand.Parameters.AddWithValue("@LogDirectory", @statistic_list[n].LogDirectory);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
//Summary
sqlite_command.CommandText = "CREATE TABLE Summary (Category varchar(100), Count integer,TimeStamp DATETIME);";
sqlite_command.ExecuteNonQuery();
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < summary_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Summary (Category,Count,TimeStamp) VALUES (@Category, @Count,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@Category", @summary_list[n].Category);
mycommand.Parameters.AddWithValue("@Count", @summary_list[n].Count);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
//Errors
sqlite_command.CommandText = "CREATE TABLE Errors (CircuitName varchar(100), FileContent varchar(100), File varchar(100),Date DATETIME,FileState varchar(100),LogDirectory varchar(100),TimeStamp DATETIME);";
sqlite_command.ExecuteNonQuery();
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < error_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Errors (CircuitName,FileContent,File,Date,FileState,LogDirectory,TimeStamp) VALUES (@CircuitName, @FileContent,@File,@Date, @FileState, @LogDirectory,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@CircuitName", @error_list[n].Circuit);
mycommand.Parameters.AddWithValue("@FileContent", @error_list[n].FileContent);
mycommand.Parameters.AddWithValue("@File", @error_list[n].File);
mycommand.Parameters.AddWithValue("@Date", @error_list[n].Date);
mycommand.Parameters.AddWithValue("@FileState", @error_list[n].FileState);
mycommand.Parameters.AddWithValue("@LogDirectory", @error_list[n].LogDirectory);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
//Warnings
sqlite_command.CommandText = "CREATE TABLE Warnings(CircuitName varchar(100), FileContent varchar(100), File varchar(100),Date DATETIME,FileState varchar(100),LogDirectory varchar(100),TimeStamp DATETIME);";
sqlite_command.ExecuteNonQuery();
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < warning_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Warnings (CircuitName,FileContent,File,Date,FileState,LogDirectory,TimeStamp) VALUES (@CircuitName, @FileContent,@File,@Date, @FileState, @LogDirectory,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@CircuitName", @warning_list[n].Circuit);
mycommand.Parameters.AddWithValue("@FileContent", @warning_list[n].FileContent);
mycommand.Parameters.AddWithValue("@File", @warning_list[n].File);
mycommand.Parameters.AddWithValue("@Date", @warning_list[n].Date);
mycommand.Parameters.AddWithValue("@FileState", @warning_list[n].FileState);
mycommand.Parameters.AddWithValue("@LogDirectory", @warning_list[n].LogDirectory);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
sqlite_command = new SQLiteCommand("end", sqlite_connection);
System.Windows.MessageBox.Show("Saved to database successfully!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
sqlite_connection.Close();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
else
{
string databasebName1 = fileName.FullName;
sqlite_connection = new SQLiteConnection("Data Source=" + databasebName1 + ";Version=3;New=True;Compress=True;");
sqlite_command = new SQLiteCommand("begin", sqlite_connection);
sqlite_connection.Open();
sqlite_command = sqlite_connection.CreateCommand();
try
{
DateTime _dateTime = DateTime.Now;
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < statistic_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Statistics (CircuitName,ErrorCount,WarningCount,State,Date,LogDirectory,TimeStamp) VALUES (@CircuitName, @ErrorCount,@WarningCount,@State, @Date, @LogDirectory,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@CircuitName", @statistic_list[n].Circuit);
mycommand.Parameters.AddWithValue("@ErrorCount", @statistic_list[n].ErrorCount);
mycommand.Parameters.AddWithValue("@WarningCount", @statistic_list[n].WarningCount);
mycommand.Parameters.AddWithValue("@State", @statistic_list[n].State);
mycommand.Parameters.AddWithValue("@Date", @statistic_list[n].Date);
mycommand.Parameters.AddWithValue("@LogDirectory", @statistic_list[n].LogDirectory);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
//Summary
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < summary_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Summary (Category,Count,TimeStamp) VALUES (@Category, @Count,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@Category", @summary_list[n].Category);
mycommand.Parameters.AddWithValue("@Count", @summary_list[n].Count);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
//Errors
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < error_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Errors (CircuitName,FileContent,File,Date,FileState,LogDirectory,TimeStamp) VALUES (@CircuitName, @FileContent,@File,@Date, @FileState, @LogDirectory,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@CircuitName", @error_list[n].Circuit);
mycommand.Parameters.AddWithValue("@FileContent", @error_list[n].FileContent);
mycommand.Parameters.AddWithValue("@File", @error_list[n].File);
mycommand.Parameters.AddWithValue("@Date", @error_list[n].Date);
mycommand.Parameters.AddWithValue("@FileState", @error_list[n].FileState);
mycommand.Parameters.AddWithValue("@LogDirectory", @error_list[n].LogDirectory);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
//Warnings
using (SQLiteTransaction mytransaction = sqlite_connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_connection))
{
SQLiteParameter myparam = new SQLiteParameter();
for (int n = 0; n < warning_list.Count; n++)
{
mycommand.CommandText = "INSERT INTO Warnings (CircuitName,FileContent,File,Date,FileState,LogDirectory,TimeStamp) VALUES (@CircuitName, @FileContent,@File,@Date, @FileState, @LogDirectory,@TimeStamp);";
mycommand.Parameters.Add(myparam);
mycommand.Parameters.AddWithValue("@TimeStamp", @_dateTime);
mycommand.Parameters.AddWithValue("@CircuitName", @warning_list[n].Circuit);
mycommand.Parameters.AddWithValue("@FileContent", @warning_list[n].FileContent);
mycommand.Parameters.AddWithValue("@File", @warning_list[n].File);
mycommand.Parameters.AddWithValue("@Date", @warning_list[n].Date);
mycommand.Parameters.AddWithValue("@FileState", @warning_list[n].FileState);
mycommand.Parameters.AddWithValue("@LogDirectory", @warning_list[n].LogDirectory);
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
sqlite_command = new SQLiteCommand("end", sqlite_connection);
System.Windows.MessageBox.Show("Saved to database successfully!", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
sqlite_connection.Close();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
private void AddSingleValueParameter(IDbCommand cmd, string parameterName, object value)
{
var parameter = new SQLiteParameter(parameterName, value ?? DBNull.Value);
cmd.Parameters.Add(parameter);
}
/// <summary>
/// 获取参数
/// </summary>
/// <param name="parameterName">目标字段</param>
/// <param name="parameterValue">值</param>
/// <param name="dbType">数据类型</param>
/// <param name="parameterSize">长度</param>
/// <param name="parameterDirection">参数类型</param>
/// <returns>参数</returns>
public IDbDataParameter MakeParameter(string parameterName, object parameterValue, DbType dbType, Int32 parameterSize, ParameterDirection parameterDirection)
{
SQLiteParameter parameter;
if (parameterSize > 0)
{
parameter = new SQLiteParameter(dbType, parameterSize, parameterName);
}
else
{
parameter = new SQLiteParameter(parameterName, (DbType)dbType);
}
parameter.Direction = parameterDirection;
if (!(parameterDirection == ParameterDirection.Output && parameterValue == null))
{
parameter.Value = parameterValue;
}
return parameter;
}
private void button2_Click(object sender, EventArgs e)
{
connection.Close();
StreamReader csv = new StreamReader(direct);
string fl = csv.ReadLine(), // fl - FirstLine
columns = "";
fl = fl.Replace("\"", "").Replace("(", "").Replace(")", "");
switch (comboBox1.SelectedIndex)
{
case 0:
columns = "Year, Score, Title";
tableName = "deniro";
break; // deniro
case 1:
columns = "Index, Living Space sq ft, Beds, Baths, Zip, Year, List Price $";
tableName = "zillow";
break; // zillow
case 2:
columns = "Sell, List, Living, Rooms, Beds, Baths, Age, Acres, Taxes";
tableName = "homes";
break; // homes
case 3:
columns = "Name, Sex, Age, Height(in), Weight(lbs)";
tableName = "biostats";
break; // biostats
case 4:
columns = "Last name, First name, SSN, Test1, Test2, Test3, Test4, Final, Grade";
tableName = "grades";
break; // grades
}
if (fl == columns)
{
connection.Open();
command.Connection = connection;
string[] elements = columns.Split(',');
string values = "@";
for (int p = 1; p < elements.Length; p++)
{
elements[p] = elements[p].Substring(1, elements[p].Length - 1);
}
for (int g = 0; g < elements.Length; g++)
{
elements[g] = elements[g].Replace(" ", "_");
}
for (int i = 0; i < elements.Length; i++)
{
if (i != elements.Length - 1)
{
values += elements[i] + ", @";
}
else
{
values += elements[i];
}
}
for (int i = 0; i < elements.Length; i++)
{
string a = csv.ReadLine();
string[] data = a.Split(',');
clear_garbage(data);
command.CommandText = "INSERT INTO " + tableName + " (" + values.Replace("@", "") + ") VALUES (" + values + ")";
SQLiteParameter[] param = new SQLiteParameter[data.Length];
for (int j = 0; j < data.Length; j++)
{
command.Parameters.AddWithValue(elements[j].ToString(), data[j]);
}
SQLiteDataReader reader = command.ExecuteReader();
command.Reset();
command.ExecuteNonQuery();
}
MessageBox.Show("Операция прошла успешно", "Успешно", MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
}
else
{
MessageBox.Show("Невозможно записать .csv в выбранную таблицу", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
void QInputVoltageTypes_QInputVoltageTypesRowDeleting(object sender, DataSetQuery.QInputVoltageTypesRowChangeEvent e)
{
try
{
if (e.Action == DataRowAction.Delete)
{
if (Convert.ToInt64(e.Row["ReadOnly"]) != 0)
{
MyLocalizer.XtraMessageBoxShow("Недостаточно прав для удаления записи.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
m_bAcceptChanges = false;
return;
}
if (MyLocalizer.XtraMessageBoxShow("Удалить запись?", "Предупреждение", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) != DialogResult.Yes)
{
m_bAcceptChanges = false;
return;
}
else
{
long id = Convert.ToInt64(e.Row["InputVoltageTypeID"]);
SQLiteConnection connection = new SQLiteConnection(global::DiarMain.Properties.Settings.Default.diarConnectionString);
connection.Open();
SQLiteCommand com = new SQLiteCommand(connection);
/*com.CommandText = "Select COUNT(*) AS Cnt from Equipments WHERE InputVoltageTypeID = ?";
* com.CommandType = CommandType.Text;
* SQLiteParameter param1 = new SQLiteParameter("@Param1", DbType.Int64);
* param1.Value = id;
* com.Parameters.Add(param1);
* SQLiteDataReader dr = com.ExecuteReader();
* while (dr.Read())
* {
* if (Convert.ToInt64(dr["Cnt"]) > 0)
* {
* MyLocalizer.XtraMessageBoxShow("Существует оборудование, имеющее РПН данного типа.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
* m_bAcceptChanges = false;
* dr.Close();
* connection.Close();
* return;
* }
* }
* dr.Close();*/
com.CommandText = "Select COUNT(*) AS Cnt from Inputs AS i WHERE i.InputTypeID = ?";
SQLiteParameter param1 = new SQLiteParameter("@Param1", DbType.Int64);
param1.Value = id;
com.Parameters.Add(param1);
SQLiteDataReader dr2 = com.ExecuteReader();
while (dr2.Read())
{
if (Convert.ToInt64(dr2["Cnt"]) > 0)
{
MyLocalizer.XtraMessageBoxShow("Существуют вводы данного типа.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
m_bAcceptChanges = false;
dr2.Close();
connection.Close();
return;
}
}
dr2.Close();
m_bAcceptChanges = true;
connection.Close();
}
}
}
catch (SQLiteException ex)
{
MyLocalizer.XtraMessageBoxShow(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (DBConcurrencyException ex)
{
MyLocalizer.XtraMessageBoxShow(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public IEnumerable <WebSearchResult> Search(string text)
{
using (DatabaseConnection connection = OpenConnection())
{
SQLiteParameter param = new SQLiteParameter("@search", "%" + text + "%");
string artistSql = "SELECT DISTINCT strArtist, strAlbumArtist, strAlbum FROM tracks WHERE strArtist LIKE @search";
IEnumerable <WebSearchResult> artists = ReadList <IEnumerable <WebSearchResult> >(artistSql, delegate(SQLiteDataReader reader)
{
IEnumerable <string> albumArtists = reader.ReadPipeList(1);
return(reader.ReadPipeList(0)
.Where(name => name.Contains(text))
.Select(name => new WebSearchResult()
{
Type = WebMediaType.MusicArtist,
Id = name,
Title = name,
Score = (int)Math.Round(40 + (decimal)text.Length / name.Length * 40)
})
.Concat(new[] { new WebSearchResult()
{
Type = WebMediaType.MusicAlbum,
Id = (string)AlbumIdReader(reader, 2),
Title = reader.ReadString(2),
Score = (int)Math.Round(20 + (decimal)text.Length / reader.ReadString(0).Length * 40),
Details = new WebDictionary <string>()
{
{ "Artist", albumArtists.First() },
{ "ArtistId", albumArtists.First() }
}
} }));
}, param).SelectMany(x => x);
string songSql = "SELECT DISTINCT idTrack, strTitle, strAlbumArtist, strAlbum, iDuration, iYear FROM tracks WHERE strTitle LIKE @search";
IEnumerable <WebSearchResult> songs = ReadList <WebSearchResult>(songSql, delegate(SQLiteDataReader reader)
{
IEnumerable <string> allArtists = reader.ReadPipeList(2);
string title = reader.ReadString(1);
string artist = allArtists.Count() == 0 ? String.Empty : allArtists.First();
return(new WebSearchResult()
{
Type = WebMediaType.MusicTrack,
Id = reader.ReadIntAsString(0),
Title = title,
Score = (int)Math.Round(40 + (decimal)text.Length / title.Length * 40),
Details = new WebDictionary <string>()
{
{ "Artist", artist },
{ "ArtistId", artist },
{ "Album", reader.ReadString(3) },
{ "AlbumId", (string)AlbumIdReader(reader, 3) },
{ "Duration", reader.ReadIntAsString(4) },
{ "Year", reader.ReadIntAsString(5) }
}
});
}, param);
string albumsSql =
"SELECT DISTINCT t.strAlbumArtist, t.strAlbum, " +
"CASE WHEN i.iYear ISNULL THEN MIN(t.iYear) ELSE i.iYear END AS year " +
"FROM tracks t " +
"LEFT JOIN albuminfo i ON t.strAlbum = i.strAlbum AND t.strArtist LIKE '%' || i.strArtist || '%' " +
"WHERE t.strAlbum LIKE @search " +
"GROUP BY t.strAlbumArtist, t.strAlbum ";
IEnumerable <WebSearchResult> albums = ReadList <IEnumerable <WebSearchResult> >(albumsSql, delegate(SQLiteDataReader reader)
{
string title = reader.ReadString(1);
IEnumerable <string> artistList = reader.ReadPipeList(0);
var albumResult = new WebSearchResult()
{
Type = WebMediaType.MusicAlbum,
Id = (string)AlbumIdReader(reader, 1),
Title = title,
Score = (int)Math.Round(40 + (decimal)text.Length / title.Length * 40),
Details = new WebDictionary <string>()
{
{ "Artist", artistList.First().Trim() },
{ "ArtistId", artistList.First().Trim() },
{ "Year", reader.ReadIntAsString(2) }
}
};
string allArtists = String.Join("", artistList);
return(artistList
.Select(name => new WebSearchResult()
{
Type = WebMediaType.MusicArtist,
Id = name,
Title = name,
Score = (int)Math.Round((decimal)name.Length / allArtists.Length * 30)
}).Concat(new[] { albumResult }));
}, param).SelectMany(x => x);
return(artists.Concat(songs).Concat(albums));
}
}
private void GridView_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
{
try
{
long id = 0;
if (qInputVoltageTypesBindingSource.Current == null)
{
return;
}
DataRowView row = (DataRowView)(qInputVoltageTypesBindingSource.Current);
bool bNew = row.IsNew;
if (!bNew)
{
if (Convert.ToInt64(row["ReadOnly"]) != 0)
{
MyLocalizer.XtraMessageBoxShow("Недостаточно прав для редактирования записи.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
e.Valid = false;
return;
}
id = Convert.ToInt64(row["InputVoltageTypeID"]);
}
string strName = row["InputVoltageTypeName"].ToString();
strName = strName.Trim();
if (strName == "")
{
e.ErrorText = "Необходимо указать наименование типа ввода.";
e.Valid = false;
return;
}
if (row["EquipmentKindID"] == DBNull.Value || Convert.ToInt64(row["EquipmentKindID"]) == 0)
{
e.ErrorText = "Необходимо указать категорию оборудования.";
e.Valid = false;
return;
}
long EquipmentKindID = Convert.ToInt64(row["EquipmentKindID"]);
SQLiteConnection connection = new SQLiteConnection(global::DiarMain.Properties.Settings.Default.diarConnectionString);
connection.Open();
SQLiteCommand com = new SQLiteCommand(connection);
com.CommandText = "Select * from InputVoltageTypes WHERE EQUAL_STR(InputVoltageTypeName, ?) = 0 AND EquipmentKindID = ? AND InputVoltageTypeID <> ?";
com.CommandType = CommandType.Text;
SQLiteParameter param1 = new SQLiteParameter("@Param1", DbType.String);
param1.Value = strName;
SQLiteParameter param2 = new SQLiteParameter("@Param2", DbType.Int64);
param2.Value = EquipmentKindID;
SQLiteParameter param3 = new SQLiteParameter("@Param3", DbType.Int64);
param3.Value = id;
com.Parameters.Add(param1);
com.Parameters.Add(param2);
com.Parameters.Add(param3);
SQLiteDataReader dr = com.ExecuteReader();
if (dr.HasRows)
{
e.ErrorText = "Тип ввода с таким наименованием уже существует в указанном виде оборудования.";
e.Valid = false;
dr.Close();
connection.Close();
return;
}
dr.Close();
// если меняется вид оборудования, то смотрим на существование оборудования
if (!bNew)
{
com.Parameters.Clear();
SQLiteParameter param1_ = new SQLiteParameter("@input_type_id", DbType.Int64);
param1_.Value = id;
SQLiteParameter param2_ = new SQLiteParameter("@kind_id", DbType.Int64);
param2_.Value = EquipmentKindID;
com.Parameters.Add(param1_);
com.Parameters.Add(param2_);
/*com.CommandText = "SELECT COUNT(*) AS Cnt FROM Equipments WHERE (InputIDHighA IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id) OR " +
* " InputIDHighB IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id) OR " +
* " InputIDHighC IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id) OR " +
* " InputIDMiddleA IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id) OR " +
* " InputIDMiddleB IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id) OR " +
* " InputIDMiddleC IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id) OR " +
* " InputIDNeutral IN (Select InputID AS Cnt from Inputs AS i WHERE i.InputTypeID = @input_id))" +
* " AND EquipmentKindID <> @kind_id ";*/
com.CommandText = "Select i.InputID from Inputs AS i INNER JOIN Equipments AS e ON " +
"e.InputIDHighA = i.InputID OR e.InputIDHighB = i.InputID OR e.InputIDHighC = i.InputID OR e.InputIDMiddleA = i.InputID OR e.InputIDMiddleB = i.InputID OR e.InputIDMiddleC = i.InputID OR e.InputIDNeutral = i.InputID " +
"WHERE i.InputTypeID = @input_type_id AND e.EquipmentKindID <> @kind_id";
/*com.CommandText = "SELECT COUNT(*) AS Cnt FROM Equipments "
+ "INNER JOIN (Select InputID from Inputs AS i WHERE i.InputTypeID = @input_type_id) AS tmp ON (tmp.InputID = InputIDHighA OR " +
+ "tmp.InputID = InputIDHighB OR tmp.InputID = InputIDHighC OR tmp.InputID = InputIDMiddleA OR tmp.InputID = InputIDMiddleB OR " +
+ "tmp.InputID = InputIDMiddleC OR tmp.InputID = InputIDNeutral) AND EquipmentKindID <> @kind_id";*/
SQLiteDataReader dr2 = com.ExecuteReader();
if (dr2.HasRows)
{
e.ErrorText = "Существует оборудование, имеющее вводы данного типа.\nКатегорию оборудования у данного типа менять запрещено.";
e.Valid = false;
dr2.Close();
connection.Close();
return;
}
/*while (dr2.Read())
* {
* if (Convert.ToInt64(dr2["Cnt"]) > 0)
* {
* MyLocalizer.XtraMessageBoxShow("Существует оборудование, имеющее вводы данного типа.\nКатегорию оборудования у данного типа менять запрещено.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
* m_bAcceptChanges = false;
* dr2.Close();
* connection.Close();
* return;
* }
* }*/
dr2.Close();
/*com.Parameters.Clear();
* com.CommandText = "Select * from Equipments WHERE InputVoltageTypeID = ? AND EquipmentKindID <> ?";
* com.CommandType = CommandType.Text;
* SQLiteParameter param1_ = new SQLiteParameter("@Param1", DbType.Int64);
* param1_.Value = id;
* SQLiteParameter param2_ = new SQLiteParameter("@Param2", DbType.Int64);
* param2_.Value = EquipmentKindID;
* com.Parameters.Add(param1_);
* com.Parameters.Add(param2_);
* SQLiteDataReader dr2 = com.ExecuteReader();
* if (dr2.HasRows)
* {
* e.ErrorText = "Существует оборудование, имеющее РПН данного типа.\nКатегорию оборудования у данного типа менять запрещено.";
* e.Valid = false;
* dr2.Close();
* connection.Close();
* return;
* }
* dr2.Close();*/
}
connection.Close();
}
catch (SQLiteException ex)
{
MyLocalizer.XtraMessageBoxShow(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (DBConcurrencyException ex)
{
MyLocalizer.XtraMessageBoxShow(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public int newbsRecord()
{
pf pf = new pf();
bsRecord dbs = new bsRecord();
dbs = vulDefaultBs();
int newBsId = new int();
string sCs = "Data Source=" + gv.sDataFilePad + ";Version=3;New=False;";
using (SQLiteConnection dbcDa = new SQLiteConnection(sCs))
{
dbcDa.Open();
string findstring = pf.randomString(6);
string sqlStr = "Insert Into Bestelling (Bstl_StatusId, Bstlr_DispStatus, Bstl_BgnrId, Bstl_DispBgnr, Bstl_KlBgId, Bstl_DispKlBg " +
"Bstl_Verwerkperiode, Bstl_Valutadatum, Bstl_Bestelbedrag, Bstl_Diversen, Bstl_DiversenId, Bstl_DispDiversen, Bstl_Verschil, " +
"Bstl_VerschilId, Bstl_DispVerschil, Bstl_Vastekostenperbestelling, Bstl_BonnengevraagdJN, Bstl_Mutatiedatum, Bstl_Opmerking) Values " +
"(@2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20)";
using (SQLiteCommand sqlCmd = new SQLiteCommand(sqlStr, dbcDa))
{
SQLiteParameter p2 = new SQLiteParameter(); p2.ParameterName = "@2"; p2.Value = 120009; sqlCmd.Parameters.Add(p2);
SQLiteParameter p3 = new SQLiteParameter(); p3.ParameterName = "@3"; p3.Value = "Bestelling-record is leeg / Tabelinitrecord"; sqlCmd.Parameters.Add(p3);
SQLiteParameter p4 = new SQLiteParameter(); p4.ParameterName = "@4"; p4.Value = 1; sqlCmd.Parameters.Add(p4);
SQLiteParameter p5 = new SQLiteParameter(); p5.ParameterName = "@5"; p5.Value = "NL59ONBB0000000000"; sqlCmd.Parameters.Add(p5);
SQLiteParameter p6 = new SQLiteParameter(); p6.ParameterName = "@6"; p6.Value = 1; sqlCmd.Parameters.Add(p6);
SQLiteParameter p7 = new SQLiteParameter(); p7.ParameterName = "@7"; p7.Value = "Klant-Begunstigde"; sqlCmd.Parameters.Add(p7);
SQLiteParameter p8 = new SQLiteParameter(); p8.ParameterName = "@8"; p8.Value = 1; sqlCmd.Parameters.Add(p8);
SQLiteParameter p9 = new SQLiteParameter(); p9.ParameterName = "@9"; p9.Value = DateTime.Parse("2000-01-01 00:00:00"); sqlCmd.Parameters.Add(p9);
SQLiteParameter p10 = new SQLiteParameter(); p10.ParameterName = "@10"; p10.Value = 0; sqlCmd.Parameters.Add(p10);
SQLiteParameter p11 = new SQLiteParameter(); p11.ParameterName = "@11"; p11.Value = 0; sqlCmd.Parameters.Add(p11);
SQLiteParameter p12 = new SQLiteParameter(); p12.ParameterName = "@12"; p12.Value = 121009; sqlCmd.Parameters.Add(p12);
SQLiteParameter p13 = new SQLiteParameter(); p13.ParameterName = "@13"; p13.Value = "Bstl_Div-Leeg"; sqlCmd.Parameters.Add(p13);
SQLiteParameter p14 = new SQLiteParameter(); p14.ParameterName = "@14"; p14.Value = 0; sqlCmd.Parameters.Add(p11);
SQLiteParameter p15 = new SQLiteParameter(); p15.ParameterName = "@15"; p15.Value = 122009; sqlCmd.Parameters.Add(p12);
SQLiteParameter p16 = new SQLiteParameter(); p16.ParameterName = "@16"; p16.Value = "Bstl_Vschl-Leeg"; sqlCmd.Parameters.Add(p13);
SQLiteParameter p17 = new SQLiteParameter(); p17.ParameterName = "@17"; p17.Value = 0; sqlCmd.Parameters.Add(p17);
SQLiteParameter p18 = new SQLiteParameter(); p18.ParameterName = "@18"; p18.Value = 1; sqlCmd.Parameters.Add(p18);
SQLiteParameter p19 = new SQLiteParameter(); p19.ParameterName = "@19"; p19.Value = DateTime.Parse("2000-01-01 00:00:00"); sqlCmd.Parameters.Add(p19);
SQLiteParameter p20 = new SQLiteParameter(); p20.ParameterName = "@20"; p20.Value = findstring; sqlCmd.Parameters.Add(p20);
sqlCmd.ExecuteNonQuery();
dbcDa.Close();
}
// Zoek toegevoegde record
tblBs bs = new tblBs();
bs.zoekBestellingRecord("Bstl_Opmerking = " + "\"" + findstring + "\"");
newBsId = bs.lstBestellingRecord[0].Bstl_Id;
// Verwijder infor uit Opmerking-veld
dbcDa.Open();
sqlStr = "Update Bestelling set Bstl_Opmerking=@20 where Bstl_Id = @1;";
using (SQLiteCommand sqlCmd = new SQLiteCommand(sqlStr, dbcDa))
{
sqlCmd.Parameters.AddWithValue("@1", newBsId);
sqlCmd.Parameters.AddWithValue("@20", "");
sqlCmd.ExecuteNonQuery();
}
dbcDa.Close();
return(newBsId);
}
}
public static void UpdatePaths(Status status, string newPath, string newFormat)
{
status.StatusText = "Fetching downloads...";
List <Download> downloads = new List <Download>();
using (SQLiteCommand command = new SQLiteCommand("select episodes.epid, progid, name, description, date, duration, autodownload, status, errortype, errordetails, filepath, playcount from episodes, downloads where episodes.epid=downloads.epid", FetchDbConn()))
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
while (reader.Read())
{
downloads.Add(new Download(reader));
}
}
if (downloads.Count > 0)
{
Dictionary <int, Programme> programmes = new Dictionary <int, Programme>();
int progress = 0;
status.StatusText = "Updating download paths...";
status.ProgressBarMax = downloads.Count;
status.ProgressBarMarquee = false;
using (SQLiteCommand command = new SQLiteCommand("update downloads set filepath=@filepath where epid=@epid", FetchDbConn()))
{
SQLiteParameter epidParam = new SQLiteParameter("epid");
SQLiteParameter filepathParam = new SQLiteParameter("filepath");
command.Parameters.Add(epidParam);
command.Parameters.Add(filepathParam);
foreach (Download download in downloads)
{
if (File.Exists(download.DownloadPath))
{
if (!programmes.ContainsKey(download.Progid))
{
programmes.Add(download.Progid, new Programme(download.Progid));
}
string newDownloadPath = MoveToSaveFolder(newFormat, programmes[download.Progid], download, newPath, Path.GetExtension(download.DownloadPath), download.DownloadPath);
if (newDownloadPath != download.DownloadPath)
{
lock (DbUpdateLock)
{
epidParam.Value = download.Epid;
filepathParam.Value = newDownloadPath;
command.ExecuteNonQuery();
}
}
}
status.ProgressBarValue = ++progress;
}
}
status.ProgressBarValue = status.ProgressBarMax;
}
}
internal static SQLiteCommand AddEntryPoint(ConnectionContextSQLite ctx, EntryPoint ep)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
@"INSERT into YcEntry (
Id,
Type,
RateId,
YieldCurveId,
Length,
TimeUnit,
DateStart,
DateFinish,
DataProviderId,
DataReference,
UserCreated,
IpCreated )
VALUES (
@Id,
@Type,
@RateId,
@YieldCurveId,
@Length,
@TimeUnit,
@DateStart,
@DateFinish,
@DataProviderId,
@DataReference,
@user,
@IpCreated
)"
);
SQLiteParameter p = new SQLiteParameter("@Id", DbType.Int32);
p.Value = ep.Id;
command.Parameters.Add(p);
p = new SQLiteParameter("@Type", DbType.String);
p.Value = ep.Type;
command.Parameters.Add(p);
p = new SQLiteParameter("@RateId", DbType.Int32);
p.Value = ep.Instrument.Id;
command.Parameters.Add(p);
p = new SQLiteParameter("@YieldCurveId", DbType.Int32);
p.Value = ep.YieldCurveId;
command.Parameters.Add(p);
p = new SQLiteParameter("@Length", DbType.Int32);
p.Value = ep.Length;
command.Parameters.Add(p);
p = new SQLiteParameter("@TimeUnit", DbType.String);
p.Value = ep.TimeUnit;
command.Parameters.Add(p);
p = new SQLiteParameter("@DateStart", DbType.DateTime);
p.Value = ep.ValidDateBegin;
command.Parameters.Add(p);
p = new SQLiteParameter("@DateEnd", DbType.DateTime);
p.Value = ep.ValidDateEnd;
command.Parameters.Add(p);
p = new SQLiteParameter("@DataProviderId", DbType.Int32);
p.Value = ep.DataProviderId;
command.Parameters.Add(p);
p = new SQLiteParameter("@DataReference", DbType.String);
p.Value = ep.DataReference;
command.Parameters.Add(p);
p = new SQLiteParameter("@IpCreated", DbType.String);
p.Value = null;
command.Parameters.Add(p);
// 1 == Demo User
p = new SQLiteParameter("@user", DbType.Int32);
p.Value = (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
command.CommandText = sb.ToString();
return(command);
}
void ConvertLatLongs()
{
Coordinate Point, NorthEast, SouthWest;
Coordinate mPoint, mNorthEast, mSouthWest;
double latitude, longitude, viewport_x_ne, viewport_y_ne, viewport_x_sw, viewport_y_sw;
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
int rowcount = 0;
using (SQLiteCommand cmd = new SQLiteCommand("select count(*) from geocode where latitude <> 0 and longitude <> 0", InstanceConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
reader.Read();
int.TryParse(reader[0].ToString(), out rowcount);
}
}
#region update cmd
using (SQLiteCommand updateCmd = new SQLiteCommand("update geocode set latm=?, longm=?, viewport_x_ne=?, viewport_y_ne=?, viewport_x_sw=?, viewport_y_sw=? where location = ?", InstanceConnection))
{
SQLiteParameter param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
updateCmd.Prepare();
Progress p = new Progress(rowcount);
p.Show();
int row = 0;
using (SQLiteCommand cmd = new SQLiteCommand("select location, latitude, longitude, viewport_x_ne, viewport_y_ne, viewport_x_sw, viewport_y_sw from geocode where latitude <> 0 and longitude <> 0", InstanceConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
latitude = longitude = viewport_x_ne = viewport_x_sw = viewport_y_ne = viewport_y_sw = 0;
string location = reader["location"].ToString();
double.TryParse(reader["latitude"].ToString(), out latitude);
double.TryParse(reader["longitude"].ToString(), out longitude);
double.TryParse(reader["viewport_x_ne"].ToString(), out viewport_x_ne);
double.TryParse(reader["viewport_y_ne"].ToString(), out viewport_y_ne);
double.TryParse(reader["viewport_x_sw"].ToString(), out viewport_x_sw);
double.TryParse(reader["viewport_y_sw"].ToString(), out viewport_y_sw);
Point = new Coordinate(longitude, latitude);
NorthEast = new Coordinate(viewport_y_ne, viewport_x_ne); // old viewports had x & y wrong way round
SouthWest = new Coordinate(viewport_y_sw, viewport_x_sw); // x is stored as lat y as long
mPoint = MapTransforms.TransformCoordinate(Point);
mNorthEast = MapTransforms.TransformCoordinate(NorthEast);
mSouthWest = MapTransforms.TransformCoordinate(SouthWest);
// now write back the m versions
updateCmd.Parameters[0].Value = mPoint.Y;
updateCmd.Parameters[1].Value = mPoint.X;
updateCmd.Parameters[2].Value = mNorthEast.X;
updateCmd.Parameters[3].Value = mNorthEast.Y;
updateCmd.Parameters[4].Value = mSouthWest.X;
updateCmd.Parameters[5].Value = mSouthWest.Y;
updateCmd.Parameters[6].Value = location;
updateCmd.ExecuteNonQuery();
p.Update(++row);
}
}
}
p.Dispose();
}
#endregion
}
//
// DateFeed
//
internal static SQLiteCommand SelectYieldCurveDefCommand(ConnectionContextSQLite ctx, long?idYc /*, long? idYcFamily*/)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
#if _NO_VIEWS_
var sb = new StringBuilder(
@"SELECT
yc.Id as Id,
CASE WHEN c.Name IS NOT NULL THEN (c.Name) ELSE ('') END as ZcCompounding,
-- CASE WHEN b.Name IS NOT NULL THEN (b.Name) ELSE ('') END as ZcBasis,
CASE WHEN f.Name IS NOT NULL THEN (f.Name) ELSE ('') END as ZcFrequency,
yc.ifZC as ifZc,
yc.ZcColor as ZcColor,
yc.ZcBasisId as ZcBasisId,
yc.ForwBasisId as FrwBasisId,
yc.FamilyId as FamilyId,
CASE WHEN fc.Name IS NOT NULL THEN (fc.Name) ELSE ('') END as FrwCompounding,
-- CASE WHEN fb.Name IS NOT NULL THEN (fb.Name) ELSE ('') END as FrwBasis,
CASE WHEN ff.Name IS NOT NULL THEN (ff.Name) ELSE ('') END as FrwFrequency,
yc.ForwTermBase as FrwTermBase,
yc.ForwTerm as FrwTerm,
yc.ifForw as ifFrw,
yc.ForwColor as FrwColor,
yc.SpreadType as SpreadType,
yc.SpreadSize as SpreadSize,
yc.SpreadFamily as SpreadFamily,
yc.Name as Name,
-- CASE WHEN ycf.Name IS NOT NULL THEN (ycf.Name) ELSE ('') END as Family,
CASE WHEN ycf.CurrencyId IS NOT NULL THEN (ccy.Id) ELSE (0) END as CurrencyId,
CASE WHEN yc.CalendarId IS NOT NULL THEN (yc.CalendarId) ELSE ('') END as calId,
CASE WHEN yc.CalendarId IS NOT NULL THEN (cal.ClassName) ELSE ('') END as calClassName,
CASE WHEN yc.CalendarId IS NOT NULL THEN (cal.MarketName) ELSE ('') END as calMarketName
FROM YieldCurve yc
LEFT JOIN EnumCompounding c ON c.Id = yc.ZcCompoundingId
-- LEFT JOIN EnumBasis b ON b.Id = yc.ZcBasisId
LEFT JOIN EnumFrequency f ON f.Id = yc.ZcFrequencyId
LEFT JOIN EnumCompounding fc ON fc.Id = yc.ForwCompoundingId
-- LEFT JOIN EnumBasis fb ON fb.Id = yc.ForwBasisId
LEFT JOIN EnumFrequency ff ON ff.Id = yc.ForwFrequencyId
LEFT JOIN YcFamily ycf ON ycf.Id = yc.FamilyId
LEFT JOIN Currency ccy ON ccy.Id = ycf.CurrencyId
LEFT JOIN Calendar cal ON cal.Id = yc.CalendarId
WHERE 1 = 1 AND yc.UserCreated = @user");
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
#else
var sb = new StringBuilder("SELECT * FROM vwYieldCurve WHERE 1 = 1");
#endif
if (idYc != null)
{
sb.Append(" AND Id = @idYc");
p = new SQLiteParameter("@idYc", DbType.Int32);
p.Value = (int)idYc;
command.Parameters.Add(p);
}
/*
* if (idYcFamily != null)
* {
* sb.Append(" AND FamilyId = @idYcFamily");
* command.Parameters.Add(new MySqlParameter("@idYcFamily", MySqlDbType.Int32)).Value = (int)idYcFamily;
* }
*/
command.CommandText = sb.ToString();
return(command);
}
public static void UpdateGeocode(FactLocation loc)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SQLiteCommand updateCmd = new SQLiteCommand("update geocode set founddate=date('now'), level = ?, latitude = ?, longitude = ?, foundlocation = ?, foundlevel = ?, viewport_x_ne = ?, viewport_y_ne = ?, viewport_x_sw = ?, viewport_y_sw = ?, geocodestatus = ?, foundresulttype = ?, latm = ?, longm = ? where location = ?", InstanceConnection))
{
SQLiteParameter param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
updateCmd.Prepare();
updateCmd.Parameters[0].Value = loc.Level;
updateCmd.Parameters[1].Value = loc.Latitude;
updateCmd.Parameters[2].Value = loc.Longitude;
updateCmd.Parameters[3].Value = loc.FoundLocation;
updateCmd.Parameters[4].Value = loc.FoundLevel;
updateCmd.Parameters[5].Value = loc.ViewPort.NorthEast.Long;
updateCmd.Parameters[6].Value = loc.ViewPort.NorthEast.Lat;
updateCmd.Parameters[7].Value = loc.ViewPort.SouthWest.Long;
updateCmd.Parameters[8].Value = loc.ViewPort.SouthWest.Lat;
updateCmd.Parameters[9].Value = loc.GeocodeStatus;
updateCmd.Parameters[10].Value = loc.FoundResultType;
updateCmd.Parameters[11].Value = loc.LatitudeM;
updateCmd.Parameters[12].Value = loc.LongitudeM;
updateCmd.Parameters[13].Value = loc.ToString();
int rowsaffected = updateCmd.ExecuteNonQuery();
if (rowsaffected != 1)
{
Console.WriteLine("Problem updating");
}
OnGeoLocationUpdated(loc);
}
}
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuery query, List <esComparison> items, string prefix)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
string compareTo = String.Empty;
foreach (esComparison comparisonItem in items)
{
esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem;
esDynamicQuery subQuery = null;
bool requiresParam = true;
std.needsStringParameter = false;
std.needsIntegerParameter = false;
std.needsDoubleParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == esParenthesis.Open)
{
sql += "(";
}
else
{
sql += ")";
}
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case esConjunction.And: sql += " AND "; break;
case esConjunction.Or: sql += " OR "; break;
case esConjunction.AndNot: sql += " AND NOT "; break;
case esConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary <string, SQLiteParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQueryInternal iLocalQuery = comparisonData.Column.Query as IDynamicQueryInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
if (comparisonData.Column.Name[0] == '<')
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
}
else
{
sql += comparisonData.Column.Name;
}
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as esDynamicQuery;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQueryInternal iColQuery = comparisonData.Column.Query as IDynamicQueryInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case esComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case esComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case esComparisonOperand.Equal:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo;
}
else
{
sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo;
}
else
{
sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo;
}
else
{
sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo;
}
else
{
sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo;
}
else
{
sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo;
}
else
{
sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
std.needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
std.needsStringParameter = true;
}
break;
case esComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
std.needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
std.needsStringParameter = true;
}
break;
case esComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) +
", " + compareTo + ")";
std.needsStringParameter = true;
break;
case esComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL";
requiresParam = false;
break;
case esComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case esComparisonOperand.In:
case esComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(std, query, comparisonData);
sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == esComparisonOperand.In)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
{
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
}
else
{
sql += comma + Convert.ToString(o);
}
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case esComparisonOperand.Between:
SQLiteCommand sqlCommand = std.cmd as SQLiteCommand;
sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQueryInternal iColQuery = comparisonData.Column.Query as IDynamicQueryInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
sql += " AND " + compareTo;
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
SQLiteParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (std.needsStringParameter)
{
p.DbType = DbType.String;
}
else if (std.needsIntegerParameter)
{
p.DbType = DbType.Int32;
}
else if (std.needsDoubleParameter)
{
p.DbType = DbType.Double;
}
}
else
{
p = new SQLiteParameter(compareTo, comparisonData.Value);
}
std.cmd.Parameters.Add(p);
}
}
}
return(sql);
}
public IEnumerable <WebSearchResult> Search(string text)
{
SQLiteParameter parameter = new SQLiteParameter("@search", "%" + text + "%");
string artistSql = "SELECT id, artist FROM artist_info WHERE artist LIKE @search";
IEnumerable <WebSearchResult> artistResults = ReadList <WebSearchResult>(artistSql, delegate(SQLiteDataReader reader)
{
string name = reader.ReadString(1);
return(new WebSearchResult()
{
Type = WebMediaType.MusicArtist,
Id = reader.ReadString(0),
Title = name,
Score = (int)Math.Round(40 + (decimal)text.Length / name.Length * 40)
});
}, parameter);
string albumSql = "SELECT a.id, a.album, p.id, p.artist " +
"FROM album_info a " +
"LEFT JOIN album_info__track_info at ON at.album_info_id = a.id " +
"LEFT JOIN artist_info__track_info pt ON pt.track_info_id = at.track_info_id " +
"LEFT JOIN artist_info p ON pt.artist_info_id = p.id " +
"WHERE a.album LIKE @search OR p.artist LIKE @search " +
"GROUP BY a.id, a.album, p.id, p.artist " +
"HAVING COUNT(p.id) > 0 ";
IEnumerable <WebSearchResult> albumResults = ReadList <WebSearchResult>(albumSql, delegate(SQLiteDataReader reader)
{
string name = reader.ReadString(1);
string artist = reader.ReadString(3);
int score = name.Contains(text) ?
(int)Math.Round(40 + (decimal)text.Length / name.Length * 40) :
(int)Math.Round(20 + (decimal)text.Length / artist.Length * 40);
return(new WebSearchResult()
{
Type = WebMediaType.MusicAlbum,
Id = reader.ReadString(0),
Title = name,
Score = score,
Details = new WebDictionary <string>()
{
{ "ArtistId", reader.ReadString(2) },
{ "Artist", artist },
}
});
}, parameter);
string trackSql = "SELECT t.id, t.track, MIN(l.duration) AS duration, a.id, a.album, p.id, p.artist " +
"FROM track_info t " +
"LEFT JOIN local_media__track_info lt ON lt.track_info_id = t.id " +
"LEFT JOIN local_media l ON l.id = lt.local_media_id " +
"LEFT JOIN album_info__track_info at ON at.track_info_id = t.id " +
"LEFT JOIN album_info a ON at.album_info_id = a.id " +
"LEFT JOIN artist_info__track_info pt ON pt.track_info_id = t.id " +
"LEFT JOIN artist_info p ON pt.artist_info_id = p.id " +
"WHERE t.track LIKE @search OR a.album LIKE @search OR p.artist LIKE @search " +
"GROUP BY t.id, t.track ";
IEnumerable <WebSearchResult> trackResults = ReadList <WebSearchResult>(trackSql, delegate(SQLiteDataReader reader)
{
string name = reader.ReadString(1);
string album = reader.ReadString(4);
string artist = reader.ReadString(6);
int score = 0;
if (name.Contains(text))
{
score = (int)Math.Round(40 + (decimal)text.Length / name.Length * 40);
}
if (album.Contains(text))
{
score = (int)Math.Round(20 + (decimal)text.Length / album.Length * 40);
}
if (artist.Contains(text))
{
score = (int)Math.Round((decimal)text.Length / artist.Length * 40);
}
return(new WebSearchResult()
{
Type = WebMediaType.MusicTrack,
Id = reader.ReadString(0),
Title = name,
Score = score,
Details = new WebDictionary <string>()
{
{ "Duration", reader.ReadString(2) },
{ "AlbumId", reader.ReadString(3) },
{ "Album", album },
{ "ArtistId", reader.ReadString(5) },
{ "Artist", artist },
{ "Year", "" }
}
});
}, parameter);
return(trackResults.Concat(artistResults).Concat(albumResults));
}
public void addNewRecord(QuoteItem quoteRecord)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
if (conn == null)
{
throw new Exception("Connection String is Null. Set the value of Connection String in MovieCatalog->Properties-?Settings.settings");
}
else if (quoteRecord == null)
{
throw new Exception("The passed argument 'movieRecord' is null");
}
SQLiteCommand query = new SQLiteCommand("addRecord", conn);
conn.Open();
query.CommandType = CommandType.StoredProcedure;
SQLiteParameter param1 = new SQLiteParameter("qty", SqlDbType.Int);
SQLiteParameter param2 = new SQLiteParameter("item", SqlDbType.VarChar);
SQLiteParameter param3 = new SQLiteParameter("rate", SqlDbType.Real);
SQLiteParameter param4 = new SQLiteParameter("descr", SqlDbType.VarChar);
SQLiteParameter param5 = new SQLiteParameter("group", SqlDbType.Int);
SQLiteParameter param6 = new SQLiteParameter("taxable", SqlDbType.Bit);
SQLiteParameter param7 = new SQLiteParameter("discountable", SqlDbType.Bit);
SQLiteParameter param8 = new SQLiteParameter("printable", SqlDbType.Bit);
SQLiteParameter param9 = new SQLiteParameter("jobno", SqlDbType.VarChar);
SQLiteParameter param10 = new SQLiteParameter("line_total", SqlDbType.Real);
SQLiteParameter param11 = new SQLiteParameter("tax_total", SqlDbType.Real);
SQLiteParameter param12 = new SQLiteParameter("tab_index", SqlDbType.Int);
SQLiteParameter param13 = new SQLiteParameter("row_index", SqlDbType.Int);
param1.Value = quoteRecord.qty;
param2.Value = quoteRecord.item;
param3.Value = quoteRecord.rate;
param4.Value = quoteRecord.descr;
param5.Value = quoteRecord.group;
param6.Value = quoteRecord.taxable;
param7.Value = quoteRecord.discountable;
param8.Value = quoteRecord.printable;
param9.Value = quoteRecord.jobno;
param10.Value = quoteRecord.line_total;
param11.Value = quoteRecord.tax_total;
param12.Value = quoteRecord.tab_index;
param13.Value = quoteRecord.row_index;
query.Parameters.Add(param1);
query.Parameters.Add(param2);
query.Parameters.Add(param3);
query.Parameters.Add(param4);
query.Parameters.Add(param5);
query.Parameters.Add(param6);
query.Parameters.Add(param7);
query.Parameters.Add(param8);
query.Parameters.Add(param9);
query.Parameters.Add(param10);
query.Parameters.Add(param11);
query.Parameters.Add(param12);
query.Parameters.Add(param13);
query.ExecuteNonQuery();
}
}
private void DeleteRecord()
{
try
{
if (GridView.FocusedRowHandle < 0)
{
return;
}
DataRowView drv = (DataRowView)(this.qCondenserTestBindingSource.Current);
if (MyLocalizer.XtraMessageBoxShow("Удалить испытание?", "Предупреждение", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) != DialogResult.Yes)
{
return;
}
else
{
long id = Convert.ToInt64(drv.Row["CondenserTestID"]);
long condenser_id = Convert.ToInt64(drv.Row["CondenserID"]);
long condenser_test_type = Convert.ToInt64(drv.Row["CondenserTestType"]);
SQLiteConnection connection = new SQLiteConnection(global::Condenser.Properties.Settings.Default.condenserConnectionString);
connection.Open();
SQLiteCommand com = new SQLiteCommand(connection);
com.CommandType = CommandType.Text;
SQLiteParameter param1 = new SQLiteParameter("@id", DbType.Int64);
param1.Value = id;
com.Parameters.Add(param1);
SQLiteParameter param2 = new SQLiteParameter("@cid", DbType.Int64);
param2.Value = condenser_id;
com.Parameters.Add(param2);
if (condenser_test_type == (long)CondenserTest.CondenserTestType.Acceptance)
{
com.CommandText = "SELECT COUNT(*) AS Cnt FROM CondenserTest AS ct WHERE ct.CondenserID = @cid AND CondenserTestType <> 2 ";
SQLiteDataReader dr = com.ExecuteReader();
while (dr.Read())
{
if (Convert.ToInt64(dr["Cnt"]) > 0)
{
MyLocalizer.XtraMessageBoxShow("Данное испытание удалить невозможно, т.к. существуют эксплуатационные испытания этого конденсатора.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
dr.Close();
connection.Close();
return;
}
}
dr.Close();
}
com.CommandText = "DELETE FROM CondenserTestSheduler WHERE CondenserTestID = @id";
com.ExecuteNonQuery();
com.CommandText = "DELETE FROM CondenserTest WHERE CondenserTestID = @id";
com.ExecuteNonQuery();
/*SQLiteParameter param2 = new SQLiteParameter("@cid", DbType.Int64);
* param2.Value = condenser_id;
* com.Parameters.Add(param2);*/
com.CommandText = "UPDATE Condensers SET CondenserState = COALESCE((SELECT CondenserState FROM CondenserTest " +
"WHERE CondenserTest.CondenserID = @cid ORDER BY CondenserTestDate DESC LIMIT 1), 0), " +
"CondenserTestType = (SELECT CondenserTestType FROM CondenserTest " +
"WHERE CondenserTest.CondenserID = @cid ORDER BY CondenserTestDate DESC LIMIT 1) " +
"WHERE CondenserID = @cid";
com.ExecuteNonQuery();
connection.Close();
RefreshGridPos(-1);
}
}
catch (SQLiteException ex)
{
MyLocalizer.XtraMessageBoxShow("Ошибка при работе с базой данных. Описание: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (DBConcurrencyException ex)
{
MyLocalizer.XtraMessageBoxShow("В программе произошла ошибка. Описание: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void TestForm_Load(object sender, EventArgs e)
{
if (Program.m_bExpertMode)
{
panelExpertMode.Visible = true;
}
else
{
panelExpertMode.Visible = false;
}
this.qCondenserTestTableAdapter.Fill(this.dataSetQuery.QCondenserTest, m_CondenserID);
listTestType.Add(new DataSourceString(1, "ресурсные"));
listTestType.Add(new DataSourceString(2, "приемо-сдаточные"));
listTestType.Add(new DataSourceString(3, "эксплуатация"));
repTestType.DataSource = listTestType;
repTestType.DisplayMember = "VAL";
repTestType.ValueMember = "KEY";
repTestType.DropDownRows = listTestType.Count;
Graphics formGraphics = this.CreateGraphics();
DpiXRel = formGraphics.DpiX / 96.0f;
DpiYRel = formGraphics.DpiY / 96.0f;
SQLiteConnection connection_ = new SQLiteConnection(global::Condenser.Properties.Settings.Default.condenserConnectionString);
connection_.Open();
SQLiteCommand com_ = new SQLiteCommand(connection_);
com_.CommandText = "SELECT c.CondenserNumber, ct.CondenserTypeName FROM Condensers AS c " +
"INNER JOIN CondenserTypes AS ct ON c.CondenserTypeID = ct.CondenserTypeID WHERE c.CondenserID = @id";
SQLiteParameter param1 = new SQLiteParameter("@id", DbType.Int64);
param1.Value = m_CondenserID;
com_.Parameters.Add(param1);
com_.CommandType = CommandType.Text;
SQLiteDataReader dr_ = com_.ExecuteReader();
string strCondenserTypeName = "", strCondenserNumber = "";
while (dr_.Read())
{
strCondenserTypeName = Convert.ToString(dr_["CondenserTypeName"]);
strCondenserNumber = Convert.ToString(dr_["CondenserNumber"]);
}
dr_.Close();
connection_.Close();
teEquipmentNumber.EditValue = strCondenserNumber;
teEquipmentType.EditValue = strCondenserTypeName;
/*foreach (GridColumn col in GridView.Columns)
* {
* GetColumnBestHeight(col);
* }
* SetMaxColumnHeights();*/
ControlNavigatorButtons cnb = controlNavigator1.Buttons;
if (GridView.FocusedRowHandle < 0)
{
cnb.CustomButtons[0].Enabled = false; // cnb.Remove.Enabled;
cnb.CustomButtons[1].Enabled = false; // cnb.Remove.Enabled;
}
else
{
cnb.CustomButtons[0].Enabled = true;
cnb.CustomButtons[1].Enabled = true;
}
if (!Program.m_bExpertMode)
{
bandCH3OH.Visible = false;
bandCO2_CO.Visible = false;
}
for (int i = 0; i < GridView.Bands.Count; i++)
{
GridView.Bands[i].Width = (int)(GridView.Bands[i].Width * formGraphics.DpiX / 96);
}
//BandedGridViewInfo viewInfo = GridView.GetViewInfo() as BandedGridViewInfo;
Size captionSize = GridView.Appearance.BandPanel.CalcTextSize(formGraphics, "www", 1000).ToSize();
m_singleLineHeight = captionSize.Height;// viewInfo.BandRowHeight;
//DevExpress.Utils.AppearanceObject[] styles = {GridView.Appearance.BandPanel};
//m_singleLineHeight = viewInfo.CalcMaxHeight(styles);
//int ii = viewInfo.ColumnRowHeight;
SetBandsHeight();
colCondenserState.MaxWidth = (int)(30 * DpiXRel);
colCondenserState.MinWidth = (int)(30 * DpiXRel);
m_bDataLoadEnd = true;
}
public static void GetCommand(ref DbCommand cmd, string strSQL, object argsObject)
{
if (argsObject != null)
{
//如果参数对象为Hashtable，则直接从里面获取参数
if (argsObject is Hashtable)
{
Hashtable ht = argsObject as Hashtable;
object obj = null;
foreach (string key in ht.Keys)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = key;
obj = ht[key];
parameter.Value = (obj == null || obj == DBNull.Value) ? DBNull.Value : obj;
cmd.Parameters.Add(parameter);
}
}
else if (argsObject is DbParameter[])
{
DbParameter[] parameters = argsObject as DbParameter[];
cmd.Parameters.AddRange(parameters);
}
else
{
PropertyInfo[] properties = argsObject.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo pInfo in properties)
{
object value = pInfo.FastGetValue(argsObject);
string name = "@" + pInfo.Name;
if (value == null || value == DBNull.Value)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = name;
parameter.Value = DBNull.Value;
cmd.Parameters.Add(parameter);
}
else if (value is ICollection)
{
StringBuilder sb = new StringBuilder(128);
sb.Append("(");
bool bFirst = true;
foreach (object obj in value as ICollection)
{
string tmpValue = null;
if (obj is string || obj is Guid)
{
tmpValue = "N'" + obj.ToString().Replace("'", "''") + "'";
}
else if (obj is DateTime)
{
throw new InvalidOperationException("不支持在IN条件中使用DateTime类型");
}
else if (obj is Guid)
{
tmpValue = "'" + obj.ToString() + "'";
}
else
{
tmpValue = obj.ToString();
}
if (bFirst)
{
sb.Append(tmpValue);
bFirst = false;
}
else
{
sb.AppendFormat(",{0}", tmpValue);
}
}
if (sb.Length == 1)
{
sb.Append("NULL");
}
sb.Append(")");
string condation = sb.ToString();
strSQL.Replace(name, condation);
}
else
{
SQLiteParameter parameter = value as SQLiteParameter;
if (parameter != null)
{
cmd.Parameters.Add(parameter);
}
else
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = name;
dbParameter.Value = value;
cmd.Parameters.Add(dbParameter);
}
}
}
}
}
cmd.CommandText = strSQL;
}
/// <summary>
/// Creates a SQLiteParameter given a name, type, and direction
/// </summary>
internal static SQLiteParameter CreateSqlParameter(string name, TypeUsage type, ParameterMode mode, object value)
{
int? size;
SQLiteParameter result = new SQLiteParameter(name, value);
// .Direction
ParameterDirection direction = MetadataHelpers.ParameterModeToParameterDirection(mode);
if (result.Direction != direction)
{
result.Direction = direction;
}
// .Size and .DbType
// output parameters are handled differently (we need to ensure there is space for return
// values where the user has not given a specific Size/MaxLength)
bool isOutParam = mode != ParameterMode.In;
DbType sqlDbType = GetSqlDbType(type, isOutParam, out size);
if (result.DbType != sqlDbType)
{
result.DbType = sqlDbType;
}
// Note that we overwrite 'facet' parameters where either the value is different or
// there is an output parameter.
if (size.HasValue && (isOutParam || result.Size != size.Value))
{
result.Size = size.Value;
}
// .IsNullable
bool isNullable = MetadataHelpers.IsNullable(type);
if (isOutParam || isNullable != result.IsNullable)
{
result.IsNullable = isNullable;
}
return result;
}
/// <summary>
/// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection.
/// </summary>
/// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>SQLiteParameterCollection</returns>
/// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks>
private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == 0) return null;
SQLiteParameterCollection coll = cmd.Parameters;
string parmString = commandText.Substring(commandText.IndexOf("@"));
// pre-process the string so always at least 1 space after a comma.
parmString = parmString.Replace(",", " ,");
// get the named parameters into a match collection
string pattern = @"(@)\S*(.*?)\b";
Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = ex.Matches(parmString);
string[] paramNames = new string[mc.Count];
int i = 0;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
}
// now let's type the parameters
int j = 0;
Type t = null;
foreach (object o in paramList)
{
t = o.GetType();
SQLiteParameter parm = new SQLiteParameter();
switch (t.ToString())
{
case ("DBNull"):
case ("Char"):
case ("SByte"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
throw new SystemException("Invalid data type");
case ("System.String"):
parm.DbType = DbType.String;
parm.ParameterName = paramNames[j];
parm.Value = (string)paramList[j];
coll.Add(parm);
break;
case ("System.Byte[]"):
parm.DbType = DbType.Binary;
parm.ParameterName = paramNames[j];
parm.Value = (byte[])paramList[j];
coll.Add(parm);
break;
case ("System.Int32"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = (int)paramList[j];
coll.Add(parm);
break;
case ("System.Boolean"):
parm.DbType = DbType.Boolean;
parm.ParameterName = paramNames[j];
parm.Value = (bool)paramList[j];
coll.Add(parm);
break;
case ("System.DateTime"):
parm.DbType = DbType.DateTime;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDateTime(paramList[j]);
coll.Add(parm);
break;
case ("System.Double"):
parm.DbType = DbType.Double;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDouble(paramList[j]);
coll.Add(parm);
break;
case ("System.Decimal"):
parm.DbType = DbType.Decimal;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDecimal(paramList[j]);
break;
case ("System.Guid"):
parm.DbType = DbType.Guid;
parm.ParameterName = paramNames[j];
parm.Value = (System.Guid)(paramList[j]);
break;
case ("System.Object"):
parm.DbType = DbType.Object;
parm.ParameterName = paramNames[j];
parm.Value = paramList[j];
coll.Add(parm);
break;
default:
throw new SystemException("Value is of unknown data type");
} // end switch
j++;
}
return coll;
}