/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">sql</param>
public static void ExecuteNonQuery(string sql)
{
try
{
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
Log4Allen.WriteLog(typeof(SQLiteHelper), ex);
}
}
/// <summary>
/// 检查数据库文件是否存在，若不存在则创建
/// </summary>
public static void CreateDB()
{
try
{
if (!Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + "Data"))
{
Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + "Data");
}
string dbPath = AppDomain.CurrentDomain.BaseDirectory + "Data\\data.db";
if (File.Exists(dbPath))
{
string connstr = "Data Source=" + dbPath;
conn = new SQLiteConnection(connstr);
conn.Open();
}
else
{
conn = new SQLiteConnection(connStr);
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = @"CREATE TABLE RealData(
id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
TargetNum DOUBLE,
InformationSource integer,
Country integer,
TargetProperty integer,
TargetType integer,
EquipModelNumber varchar(100),
PositionDate DOUBLE,
Longitude DOUBLE,
Latitude DOUBLE,
Altitude DOUBLE,
ScanRange DOUBLE,
ActionRange DOUBLE)";
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Log4Allen.WriteLog(typeof(SQLiteHelper), ex);
}
}
/// <summary>
/// 实时数据入库
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static int Data2Db(List <RealData> lst)
{
try
{
using (DbTransaction tr = conn.BeginTransaction())
{
try
{
foreach (var data in lst)
{
if (data == null)
{
continue;
}
StringBuilder strBuilder = new StringBuilder();
strBuilder.AppendFormat(@"insert into RealData (TargetNum,InformationSource,Country,TargetProperty,TargetType,EquipModelNumber,PositionDate,Longitude,Latitude,Altitude,ScanRange,ActionRange)
values({0},{1},{2},{3},{4},'{5}',{6},{7},{8},{9},{10},{11});", data.TargetNum, data.InformationSource, data.Country, data.TargetProperty, data.TargetType, data.EquipModelNumber.Trim('\0'), data.PositionDate, data.Longitude, data.Latitude, data.Altitude,
data.ScanRange, data.ActionRange);
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = strBuilder.ToString();
var ret = cmd.ExecuteNonQuery();
}
}
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
return(-1);
}
}
return(lst.Count);
}
catch (Exception ex)
{
Log4Allen.WriteLog(typeof(SQLiteHelper), ex.Message);
return(-1);
}
}
/// <summary>
/// 实时数据入库
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static int Data2Db(RealData data)
{
if (data == null)
{
return(-1);
}
try
{
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = string.Format(@"insert into RealData (TargetNum,InformationSource,Country,TargetProperty,TargetType,EquipModelNumber,PositionDate,Longitude,Latitude,Altitude,ScanRange,ActionRange)
values({0},{1},{2},{3},{4},'{5}',{6},{7},{8},{9},{10},{11})", data.TargetNum, data.InformationSource, data.Country, data.TargetProperty, data.TargetType, data.EquipModelNumber.Trim('\0'), data.PositionDate, data.Longitude, data.Latitude, data.Altitude,
data.ScanRange, data.ActionRange);
var ret = cmd.ExecuteNonQuery();
return(ret);
}
catch (Exception ex)
{
Log4Allen.WriteLog(typeof(SQLiteHelper), ex.Message);
return(-1);
}
}
/// <summary>
/// 查询 Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="commandText">Command text.</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText)
{
try
{
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = commandText;
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
return(ds);
}
catch (Exception ex)
{
Log4Allen.WriteLog(typeof(SQLiteHelper), ex);
return(null);
}
}
/// <summary>
/// 根据距离和方位角获取点
/// </summary>
/// <param name="distance"></param>
/// <param name="point"></param>
/// <param name="angle"></param>
/// <returns></returns>
public static MapLngLat GetPointByDistanceAndAngle(float distance, MapLngLat point, double angle)
{
try
{
double lng1 = point.Lng;
double lat1 = point.Lat;
// 将距离转换成经度的计算公式
double lon = lng1 + (distance * Math.Sin(angle * Math.PI / 180)) / (111 * Math.Cos(lat1 * Math.PI / 180));
// 将距离转换成纬度的计算公式
double lat = lat1 + (distance * Math.Cos(angle * Math.PI / 180)) / 111;
MapLngLat newPoint = new MapLngLat();
newPoint.Lng = lon;
newPoint.Lat = lat;
newPoint.Alt = 0;
return(newPoint);
}
catch (Exception ex)
{
Log4Allen.WriteLog(typeof(Utils), ex);
return(null);
}
}
