public string GetPreText(UInt16 RcpId)
{
string ret = "";
try
{
_SQLiteConnection.Open();
string qury = string.Format("select * from tb_recipe where ID={0}", RcpId);
SQLiteCommand command = new SQLiteCommand(qury, _SQLiteConnection);
_reader = command.ExecuteReader();
while (_reader.Read())
ret = _reader["ShowInfo"].ToString();
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
_reader.Close();
_SQLiteConnection.Close();
}
return ret;
}
private void Charts_Load(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
#region 读取状态
//获取表名
var tbListDT = sh.GetTableList();
List <string> tbList = new List <string>();
for (int i = 0; i < tbListDT.Rows.Count; i++)
{
tbList.Add(tbListDT.Rows[i][0].ToString());
}
//获取状态
for (int i = 0; i < tbList.Count; i++)
{
string listItem = tbList[i];
comboBox2.Items.Add(listItem);
}
#endregion
#region 读取测点列表
try
{
string tableName = tbList[0];
List <string> columnName = new List <string>();
string sql = "PRAGMA table_info([" + tableName + "]);";
SQLiteCommand cmd2 = new SQLiteCommand(sql, conn);
System.Data.SQLite.SQLiteDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
columnName.Add(dr[1].ToString());
}
dr.Close();
conn.Close();
var colName = new List <string>();
for (int i = 3; i < columnName.Count; i++)
{
checkedListBox1.Items.Add(columnName[i].ToString());
}
#endregion
}
catch (Exception)
{
}
conn.Close();
}
}
//温度范围
textBox2.Text = Global.tempLimitLow.ToString();
textBox3.Text = Global.tempLimitHigh.ToString();
}
/// <summary>
/// 获得所有分组集合
/// </summary>
public static List <exGroup> GetGroups()
{
List <exGroup> Groups = new List <exGroup>();
string sql = "select * from Groups order by orderID ";
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null);
if (dr != null)
{
while (dr.Read())
{
exGroup group = new exGroup();
{
group.GroupID = Convert.ToString(dr["GroupID"]);
group.GroupName = Convert.ToString(dr["GroupName"]);
group.SuperiorID = Convert.ToString(dr["SuperiorID"]);
group.OrderID = Convert.ToInt32(dr["orderID"]);
}
Groups.Add(group);
}
dr.Close();
}
dr.Dispose();
return(Groups);
}
public Report()
{
InitializeComponent();
// Connect to database file
sql_con = new SQLiteConnection("Data Source=" + applicationPath + "\\ExpenseTracker.db;Version=3;New=False;Compress=True;");
sql_cmd = new SQLiteCommand();
sql_con.Open();
sql_cmd.Connection = sql_con;
sql_cmd.CommandText = "SELECT * FROM Month";
sql_reader = sql_cmd.ExecuteReader();
while (sql_reader.Read())
{
dataGridView.Rows.Add(
sql_reader.GetInt32(0),
CustomDate.GetThaiMonth(sql_reader.GetInt32(1)),
sql_reader.GetDecimal(2).ToString("#,#0.00#"),
sql_reader.GetDecimal(3).ToString("#,#0.00#"),
sql_reader.GetDecimal(4).ToString("#,#0.00#")
);
}
sql_reader.Close();
dataGridView.ClearSelection();
}
/// <summary>
/// 获得所有群
/// </summary>
public static List <exRoom> GetRooms()
{
List <exRoom> Rooms = new List <exRoom>();
string sql = "select * from Rooms";
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null);
if (dr != null)
{
while (dr.Read())
{
exRoom Room = new exRoom();
{
Room.RoomID = Convert.ToString(dr["RoomID"]);
Room.RoomName = Convert.ToString(dr["RoomName"]);
Room.Notice = Convert.ToString(dr["Notice"]);
Room.UserIDs = Convert.ToString(dr["Users"]);
Room.OrderID = Convert.ToInt32(dr["OrderID"]);
Room.CreateUserID = Convert.ToString(dr["CreateUserID"]);
}
Rooms.Add(Room);
}
dr.Close();
}
dr.Dispose();
return(Rooms);
}
// 最原始的列出数据
public List <String[]> LocalGetDataFromOriginalSQL(String sql, String[] query)
{
using (SQLiteConnection conn = new SQLiteConnection(LocalConnStr))
{
using (SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn))
{
cmdCreateTable.CommandText = sql;
conn.Open();
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
String[] resultsStringArray = new String[query.Length];
List <String[]> resultsStringList = new List <String[]>();
while (reader.Read())
{
for (int i = 0; i < query.Length; i++)
{
resultsStringArray[i] = reader[i].ToString();
}
resultsStringList.Add(resultsStringArray);
resultsStringArray = new String[query.Length];
}
reader.Close();
return(resultsStringList);
}
}
}
public string[] LocalGetOneRowDataById(string table, string[] query, string id)
{
// ORDER BY id ASC
string innerSQL = "";
for (int i = 0; i < query.Length; i++)
{
innerSQL += query[i] + ",";
}
if (!innerSQL.Equals(""))
{
innerSQL = innerSQL.Substring(0, innerSQL.Length - 1); // 去掉最后的逗号
}
string sql = "SELECT " + innerSQL + " FROM " + table + " WHERE id='" + id + "'"; //建表语句
LocalDbOpen();
SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, localSqlConnectionCommand);
cmdCreateTable.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
string[] resultsStringArray = new string[query.Length];
while (reader.Read())
{
for (int i = 0; i < query.Length; i++)
{
resultsStringArray[i] = reader[query[i]].ToString();
}
}
reader.Close();
LocalDbClose();
return(resultsStringArray);
}
public List<Tuple<string, string, string, string, string>> GetAllBooks()
{
var books = new List<Tuple<string, string, string, string, string>>();
m_dbConnection = new SQLiteConnection("Data Source=" + db + ";Version=3;");
m_dbConnection.Open();
string sql = "SELECT * FROM books";
command = new SQLiteCommand(sql, m_dbConnection);
reader = command.ExecuteReader();
string id = "";
string title = "";
string auteur = "";
string picture = "";
string synopsis = "";
while (reader.Read())
{
id = reader["id"].ToString();
title = reader["title"].ToString();
auteur = reader["auteur"].ToString();
picture = reader["picture"].ToString();
synopsis = reader["synopsis"].ToString();
books.Add(new Tuple<string, string, string, string, string>(id, title, auteur, picture, synopsis));
}
reader.Close();
m_dbConnection.Close();
return books;
}
public Message ReadMail(string userMail, int rowNumber)
{
// Declare a variable for the converted results.
Message result;
// Open the database.
dbConnection.Open();
// Retrieve all records from the table called "mails" for the specified usermail.
dbCommand.CommandText = "SELECT * FROM mails WHERE address='" + userMail + "';";
// Execute the newly created command.
dbQuery = dbCommand.ExecuteReader();
// Read the retrieved query, and convert the result to bytes from the current string.
while (dbQuery.Read())
{
// Check if the current row is the one specified.
if (dbQuery.StepCount == rowNumber)
{
// Convert the result to bytes and then to a message and put this into the message variable.
result = new Message(Convert.FromBase64String(dbQuery["rawmessage"].ToString()));
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Break out of the loop by returning the converted mathing result.
return result;
}
}
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Return a null if nothing is found.
return null;
}
public float ReturnTotals(string command)
{
mycommand = new SQLiteCommand(connector);
mycommand.CommandText = command;
reader = mycommand.ExecuteReader();
reader.Read();
float tempValue = 0.0f;
if (!DBNull.Value.Equals(reader[0]))
{
tempValue = System.Convert.ToSingle(reader[0]);
}
reader.Close();
return tempValue;
}
/// <summary>
/// 获取用户资料
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public static UserVcard GetUserVcard(string UserID)
{
UserVcard card = null;
string sql = "select Vcard from UsersVcard where UserID=@UserID";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@UserID", UserID)
};
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, parameters);
if (dr != null && dr.Read())
{
card = Factory.CreateInstanceObject(Convert.ToString(dr["Vcard"])) as UserVcard;
dr.Close();
}
dr.Dispose();
return(card);
}
public void CreateChart(ZedGraphControl zgc)
{
GraphPane myPane = zgc.GraphPane;
PointPairList lstIncome = new PointPairList();
PointPairList lstExpense = new PointPairList();
// Set the title and axis labels
myPane.Title.FontSpec.Family = "Browallia New";
myPane.Title.FontSpec.Size = 24;
myPane.Title.Text = "สรุป";
myPane.XAxis.Title.FontSpec.Family = "Browallia New";
myPane.XAxis.Title.FontSpec.Size = 16;
myPane.XAxis.Title.Text = "เดือน";
myPane.XAxis.Type = AxisType.Text;
myPane.YAxis.Title.FontSpec.Family = "Browallia New";
myPane.YAxis.Title.FontSpec.Size = 24;
myPane.YAxis.Title.Text = "จำนวนเงิน";
// Load data for this month
sql_cmd.CommandText = "SELECT Month, TotalIncome, TotalExpense FROM Month WHERE Year = '" + CustomDate.GetThaiYear(DateTime.Today.Year) + "'";
sql_reader = sql_cmd.ExecuteReader();
while (sql_reader.Read())
{
monthsLabel.Add(CustomDate.GetThaiMonth(sql_reader.GetInt32(0)));
lstIncome.Add(0, sql_reader.GetDouble(1));
lstExpense.Add(0, sql_reader.GetDouble(2));
}
sql_reader.Close();
myPane.XAxis.Scale.FontSpec.Family = "Browallia New";
myPane.XAxis.Scale.FontSpec.Size = 16;
myPane.XAxis.Scale.TextLabels = monthsLabel.ToArray();
BarItem myCurve = myPane.AddBar("รายรับ", lstIncome, Color.Blue);
BarItem myCurve2 = myPane.AddBar("รายจ่าย", lstExpense, Color.Red);
myPane.Chart.Fill = new Fill(Color.White, Color.FromArgb(255, 255, 166), 45.0F);
myPane.YAxis.Scale.Max += myPane.YAxis.Scale.MajorStep;
//BarItem.CreateBarLabels(myPane, false, "#,#0.00#", "Tahoma", 10, Color.Black, true, false, false);
zgc.AxisChange();
}
public static List <CheckinRecordInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total)
{
string sql = string.Format("select * from CheckRecordTable");
System.Data.SQLite.SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql);
total = Looper(reader).Count;
sql = string.Format(
"select * from CheckRecordTable where (Operation like '%{0}%' " +
"or StudentName like '%{0}%')" +
"limit {1} offset {2}",
key, pageSize, (pageIndex - 1) * pageSize);
reader = SQLiteControl.ExecuteReader(sql);
List <CheckinRecordInfo> results = Looper(reader);
reader.Close();
return(results);
}
public AlertLog()
{
InitializeComponent();
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM ALERTLOG;";
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
var MsgNum = sqlite_datareader["msgid"].ToString();
var RdsNum = sqlite_datareader["rdsid"].ToString();
var SendTime = sqlite_datareader["alerttime"].ToString();
var ClientId = sqlite_datareader["clientid"].ToString();
var CheckTime = sqlite_datareader["checktime"].ToString();
txtLog.AppendText(string.Format("第{0}號訊息_訊息代碼:{1}_發送時間:{2}_手機ID:{3}_確認時間:{4}\r\n", MsgNum, RdsNum, SendTime, ClientId, CheckTime));
}
sqlite_datareader.Close();
sqlite_conn.Close();
}
// 列出数据
public List <String[]> LocalGetData(String table, String[] query, String order)
{
List <String[]> resultsStringList;
using (SQLiteConnection conn = new SQLiteConnection(LocalConnStr))
{
// ORDER BY id ASC
String innerSQL = "";
for (int i = 0; i < query.Length; i++)
{
innerSQL += query[i] + ",";
}
if (!innerSQL.Equals(""))
{
innerSQL = innerSQL.Substring(0, innerSQL.Length - 1); // 去掉最后的逗号
}
String sql = "SELECT " + innerSQL + " FROM " + table + " " + order; //建表语句
using (SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn))
{
cmdCreateTable.CommandText = sql;
conn.Open();
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
String[] resultsStringArray = new String[query.Length];
resultsStringList = new List <String[]>();
while (reader.Read())
{
for (int i = 0; i < query.Length; i++)
{
resultsStringArray[i] = reader[i].ToString();
}
resultsStringList.Add(resultsStringArray);
resultsStringArray = new String[query.Length];
}
reader.Close();
}
}
return(resultsStringList);
}
private void Form6_Load(object sender, EventArgs e)
{
comboBox1.Text = "";
var m_dbConnection = new SQLiteConnection("Data Source=28cm_db.sqlite;Version=3;");
int N = 0;
sql = "SELECT COUNT(rowid) FROM Groups";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
reader = command.ExecuteReader();
if (reader.Read())
N = Convert.ToInt32(reader["COUNT(rowid)"]);
reader.Close();
m_dbConnection.Close();
for (int i = 1; i <= N; i++)
{
sql = "SELECT name FROM Groups WHERE rowid = ('" + i + "')";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
group_name = Convert.ToString(reader["name"]);
}
reader.Close();
m_dbConnection.Close();
comboBox1.Items.Add(group_name);
}
}
private void GetAllGZ()
{
//if (1==2)
{
//string sql = "select [gzDay],[gzMonth],[gzYear],DateValue=([DateValue])+' '+ substring(JieQi,4,5),[weekDay],[constellation],JieQi,[nlMonth],[nlDay] from [ChineseTenThousandCalendar] where left(ltrim(JieQi),2) in (" + JieQiHelper.GetInJieQis() + ")";
//string datasource = Application.StartupPath + "/JieQi.db";
//SqlLiteHelper.SqlServerToSqlLite(datasource, sql);
}
int myDiffday = 0;
string jd = string.Empty;
string jm = string.Empty;
string jy = string.Empty;
string jnd = string.Empty;
string jnm = string.Empty;
int day = 0;
System.Data.SQLite.SQLiteDataReader reader = SelectJieQI.GetReadData(myDiffday);
if (reader != null)
{
while (reader.Read())
{
jieQi = reader["jieQi"].ToString().Trim() + " " + reader["DayGZ"].ToString().Trim() + "日" + reader["DateValue"].ToString();
jd = reader["DayGZ"].ToString();
jm = reader["MonthGZ"].ToString();
jy = reader["YearGZ"].ToString();
jnd = reader["NongLiDay"].ToString();
jnm = reader["NongLiMonth"].ToString();;
DateTime nowdate = DateTime.Now.AddDays(myDiffday);
DateTime jqt = DateTime.Parse(reader["DateValue"].ToString());
TimeSpan ts = nowdate - jqt;
day = ts.Days;
}
reader.Close();
reader.Dispose();
}
CalCurrentAllGZ(day, jd, jm, jy, jnd, jnm);
}
/// <summary>
/// 获得消息集合
/// </summary>
/// <param name="MsgInfoClass">消息类型</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static List <IMLibrary3.Protocol.Message> GetMsg(IMLibrary3.Enmu.MessageType MessageType, int pageIndex, int pageSize)
{
List <IMLibrary3.Protocol.Message> Msgs = null;
System.Data.SQLite.SQLiteParameter messageType = new System.Data.SQLite.SQLiteParameter("MessageType", MessageType);
string sql = "select * from MsgRecord where MessageType=@MessageType "
+ " order by ID limit " + pageSize.ToString() + " offset " + ((pageIndex - 1) * pageSize).ToString();
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, messageType);
if (dr != null)
{
Msgs = new List <IMLibrary3.Protocol.Message>();
while (dr.Read())
{
Msgs.Add(GetDrMsg(dr));
}
}
dr.Close(); dr.Dispose();
return(Msgs);
}
// 获取表的id
public List <string> LocalGetIdsOfTable(string table, string baseName, string order)
{
List <string> resultsStringList;
using (SQLiteConnection conn = new SQLiteConnection(LocalConnStr))
{
string sql = "SELECT " + baseName + " FROM " + table + " " + order;//建表语句
using (SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn))
{
cmdCreateTable.CommandText = sql;
conn.Open();
System.Data.SQLite.SQLiteDataReader reader = cmdCreateTable.ExecuteReader();
resultsStringList = new List <string>();
while (reader.Read())
{
resultsStringList.Add(reader.GetString(0));
}
reader.Close();
}
}
return(resultsStringList);
}
/// <summary>
/// 获得组织机构版本
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public static IMLibrary3.Protocol.OrgVersion GetOrgVersion(string UserID)
{
IMLibrary3.Protocol.OrgVersion orgVersion = null;
#region 判断登录的用户本地数据库文件夹及文件是否存在，不存在返回空值
//System.IO.DirectoryInfo dInfo = new System.IO.DirectoryInfo(UserID);
//if (!dInfo.Exists)
// return null;
//string FileNamePath = UserID + @"\Record.mdb";
//if (!System.IO.File.Exists(FileNamePath))
// return null;
////如果本地数据库存在，则设置数据库连接字符串
//DBFileNamePath = FileNamePath;
//conStr = "data source=" + DBFileNamePath;
#endregion
try
{
string sql = "select * from OrgVersion";
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null);
if (dr != null && dr.Read())
{
orgVersion = new IMLibrary3.Protocol.OrgVersion();
orgVersion.GroupsVersion = Convert.ToString(dr["GroupsVersion"]).Trim();
orgVersion.UsersVersion = Convert.ToString(dr["UsersVersion"]).Trim();
orgVersion.RoomsVersion = Convert.ToString(dr["RoomsVersion"]).Trim();
}
dr.Close(); dr.Dispose();
return(orgVersion);
}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ex.Message);
}
return(null);
}
public List<UserSettings> ReadUserSettings()
{
// Create a list for the user settings.
List<UserSettings> listUserSettings = new List<UserSettings>();
// Open the database.
dbConnection.Open();
// Retrieve all records from the table called "mailaddresses".
dbCommand.CommandText = "SELECT * FROM mailaddresses;";
// Execute the newly created command.
dbQuery = dbCommand.ExecuteReader();
// Read the retrieved query, and write the results to the newly created list.
while (dbQuery.Read())
listUserSettings.Add(new UserSettings
{
userMail = dbQuery["address"].ToString(),
password = dbQuery["password"].ToString(),
receiveServer = dbQuery["receiveserver"].ToString(),
receivePort = (int)dbQuery["receiveport"],
receiveSSL = dbQuery["receivessl"].ToString(),
sendServer = dbQuery["sendserver"].ToString(),
sendPort = (int)dbQuery["sendport"],
sendSSL = dbQuery["sendssl"].ToString(),
});
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Return the created list.
return listUserSettings;
}
private void getMoiveCount()
{
try
{
cmd.CommandText = "select count(*) from movies where status='0';";
reader = cmd.ExecuteReader();
if (reader.Read())
{
txttotalmovies.Text = "total movies : " + reader[0].ToString();
if (Convert.ToInt32(reader[0]) > 0)
{
deatilpane.Visibility = System.Windows.Visibility.Visible;
}
else
{
deatilpane.Visibility = System.Windows.Visibility.Hidden;
}
}
reader.Close();
}
catch (Exception)
{}
}
private void fillMovieList()
{
try
{
itemupdating = true;
movielist.Items.Clear();
cmd.CommandText = "SELECT title FROM movies order by movieID desc";
reader = cmd.ExecuteReader();
while (reader.Read())
{
movielist.Items.Add(reader["Title"]);
}
reader.Close();
itemupdating = false;
movielist.SelectedIndex = -1;
if (movielist.Items.Count >= 0)
{
movielist.SelectedIndex = 0;
}
}
catch (Exception)
{}
}// file the movie list with all movies
/// <summary>
/// 获得所有用户集合
/// </summary>
public static List <exUser> GetUsers()
{
List <exUser> Users = new List <exUser>();
string sql = "select * from Users order by orderID";
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null);
if (dr != null)
{
while (dr.Read())
{
exUser user = new exUser();
{
user.UserID = Convert.ToString(dr["UserID"]);
user.UserName = Convert.ToString(dr["UserName"]);
user.GroupID = Convert.ToString(dr["GroupID"]);
user.OrderID = Convert.ToInt32(dr["OrderID"]);
}
Users.Add(user);
}
dr.Close();
}
dr.Dispose();
return(Users);
}
private void movielist_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
if (movielist.SelectedIndex >= 0 && movielist.Items.Count > 0 && itemupdating == false)
{
string listitem = movielist.SelectedItem.ToString();
cmd.CommandText = "SELECT * FROM movies where title='" + listitem + "';";
reader = cmd.ExecuteReader();
if (reader.Read())
{
txtnameyr.Text = reader[1].ToString() + " " + "(" + reader[2].ToString() + ")";
txtreleased.Text = reader[4].ToString();
txtrating.Text = reader[16].ToString();
txtruntime.Text = reader[5].ToString();
txtgenre.Text = reader[6].ToString();
txtdirector.Text = reader[7].ToString();
txtwriter.Text = reader[8].ToString();
txtactor.Text = reader[9].ToString();
txtlanguage.Text = reader[11].ToString();
txtmetascore.Text = reader[15].ToString();
txtimdbvoters.Text = reader[17].ToString();
txtcountry.Text = reader[12].ToString();
txttype.Text = reader[19].ToString();
txtawards.Text = reader[13].ToString();
txtplot.Text = reader[10].ToString();
movieId = reader[0].ToString();
imdbID = reader[18].ToString();
status = Convert.ToBoolean(Convert.ToInt16(reader[20].ToString()));
like = Convert.ToBoolean(Convert.ToInt16(reader[21].ToString()));
}
if (File.Exists(util.getImagePath() + reader[18]))
{
posterimage.Source = img(util.getImagePath()+ reader[18]);
}
else
{
posterimage.Source = img(util.getblankPoster());
}
setStatus();
setLike();
reader.Close();
}
}
catch (Exception)
{}
}
private string getID(string regionID, string town)
{
string ID;
ID = "";
selectSQLCommand.CommandText = "SELECT towns.id FROM towns WHERE towns.regionID=:regionID AND towns.type || '.' ||towns.town =:town";
selectSQLCommand.Parameters.Add(":regionID", DbType.String);
selectSQLCommand.Parameters[":regionID"].Value = regionID;
selectSQLCommand.Parameters.Add(":town", DbType.String);
selectSQLCommand.Parameters[":town"].Value = town;
connect();
reader = selectSQLCommand.ExecuteReader();
if (reader.Read())
{
ID = reader[0].ToString();
}
reader.Close();
reader = null;
disconnect();
return ID;
}
void Export_RowsData_Insert_Ignore_Replace(SQLiteBackup manager)
{
manager.Command.CommandText = string.Format("SELECT * FROM `{0}`;", _name);
SQLiteDataReader rdr = manager.Command.ExecuteReader();
var sb = new StringBuilder((int)manager.ExportInfo.MaxSqlLength);
manager._currentRowIndexInCurrentTable = 0;
_insertStatementHeader = string.Empty;
while (rdr.Read())
{
if (manager.stopProcess)
{
return;
}
manager._currentRowIndexInAllTable += 1;
manager._currentRowIndexInCurrentTable += 1;
if (_insertStatementHeader == string.Empty)
{
_insertStatementHeader = Export_GetInsertStatementHeader(
manager.ExportInfo.RowsExportMode, rdr);
}
string sqlDataRow = Export_GetValueString(rdr);
if (sb.Length == 0)
{
sb.AppendLine(_insertStatementHeader);
sb.Append(sqlDataRow);
}
else if ((long)sb.Length + (long)sqlDataRow.Length < manager.ExportInfo.MaxSqlLength)
{
sb.AppendLine(",");
sb.Append(sqlDataRow);
}
else
{
sb.Append(";");
manager.Export_WriteLine(sb.ToString());
manager.textWriter.Flush();
sb = new StringBuilder((int)manager.ExportInfo.MaxSqlLength);
sb.AppendLine(_insertStatementHeader);
sb.Append(sqlDataRow);
}
}
rdr.Close();
if (sb.Length > 0)
{
sb.Append(";");
}
manager.Export_WriteLine(sb.ToString());
manager.textWriter.Flush();
sb = null;
}
public void deleteData(typesData typeData, string id)
{
switch (typeData)
{
case typesData.department:
updateSQLCommand.CommandText = "SELECT count() FROM departments WHERE id=:id";
break;
case typesData.diagnose:
updateSQLCommand.CommandText = "SELECT count() FROM diagnosis WHERE id=:id";
break;
case typesData.doctor:
updateSQLCommand.CommandText = "SELECT count() FROM doctors WHERE id=:id";
break;
case typesData.lkk:
updateSQLCommand.CommandText = "UPDATE lkk SET deleted=0 WHERE id=:id";
break;
case typesData.region:
updateSQLCommand.CommandText = "SELECT count() FROM regions WHERE id=:id";
break;
case typesData.town:
updateSQLCommand.CommandText = "SELECT count() FROM towns WHERE id=:id";
break;
}
connect();
updateSQLCommand.Parameters.Add(":id", DbType.String);
updateSQLCommand.Parameters[":id"].Value = id;
reader = updateSQLCommand.ExecuteReader();
int count=1;
if (reader.Read())
{
count = Convert.ToInt32(reader[0].ToString());
}
reader.Close();
reader = null;
disconnect();
if (count > 0)
setDeleted(typeData, id);
else
realyDelete(typeData, id);
}
public lkkData selectLKK(string id)
{
lkkData result = new lkkData();
SQLiteCommand selectData = new SQLiteCommand();
SQLiteDataReader reader = null;
selectData.Connection = connection;
selectData.CommandText = "SELECT * FROM infedenceView WHERE id=:id";
selectData.Parameters.Add(":id", DbType.String);
selectData.Parameters[":id"].Value = id;
connect();
reader = selectData.ExecuteReader();
if (reader.Read())
{
result.date = Convert.ToDateTime(reader["data"]);
result.number = reader["number"].ToString();
result.department = reader["department"].ToString();
result.doctor = reader["doctor"].ToString();
result.fio = reader["fio"].ToString();
result.birth = reader["birth"].ToString();
result.age = reader["age"].ToString();
result.region = reader["region"].ToString();
result.town = reader["town"].ToString();
result.address = reader["address"].ToString();
result.addressWork = reader["addressWork"].ToString();
result.position = reader["position"].ToString();
result.mkbCode = reader["mkbCode"].ToString();
result.diagnose = reader["diagnose"].ToString();
result.lkk = reader["lkk"].ToString();
result.msek = reader["msek"].ToString();
result.addition = reader["addition"].ToString();
result.sex = reader["sex"].ToString();
result.haveInvalidity = Convert.ToBoolean(reader["haveInvalidity"].ToString());
result.InvalidityDate = Convert.ToDateTime(reader["invalidityDate"]);
result.LPZ = reader["LPZ"].ToString();
}
reader.Close();
reader = null;
return result;
}
private bool DoMoveNextWay()
{
if (_way_reader == null)
{
SQLiteCommand way_command = new SQLiteCommand("select * from way where id > 26478817 order by id");
way_command.Connection = _connection;
_way_reader = way_command.ExecuteReader();
if (!_way_reader.Read())
{
_way_reader.Close();
}
SQLiteCommand way_tag_command = new SQLiteCommand("select * from way_tags where way_id > 26478817 order by way_id");
way_tag_command.Connection = _connection;
_way_tag_reader = way_tag_command.ExecuteReader();
if (!_way_tag_reader.IsClosed && !_way_tag_reader.Read())
{
_way_tag_reader.Close();
}
SQLiteCommand way_node_command = new SQLiteCommand("select * from way_nodes where way_id > 26478817 order by way_id,sequence_id");
way_node_command.Connection = _connection;
_way_node_reader = way_node_command.ExecuteReader();
if (!_way_node_reader.IsClosed && !_way_node_reader.Read())
{
_way_node_reader.Close();
}
}
// read next way.
if (!_way_reader.IsClosed)
{
Way way = new Way();
way.Id = _way_reader.GetInt64(0);
way.ChangeSetId = _way_reader.GetInt64(1);
way.TimeStamp = _way_reader.IsDBNull(3) ? DateTime.MinValue : _way_reader.GetDateTime(3);
//way.UserId = _way_reader.GetInt64(6);
//way.UserName = _way_reader.GetString(5);
way.Version = (ulong)_way_reader.GetInt64(4);
way.Visible = _way_reader.GetInt64(2) == 1;
if (!_way_tag_reader.IsClosed)
{
long returned_id = _way_tag_reader.GetInt64(_way_tag_reader.GetOrdinal("way_id"));
while (returned_id == way.Id.Value)
{
if (way.Tags == null)
{
way.Tags = new TagsCollection();
}
string key = _way_tag_reader.GetString(1);
string value = _way_tag_reader.GetString(2);
way.Tags.Add(key, value);
if (!_way_tag_reader.Read())
{
_way_tag_reader.Close();
returned_id = -1;
}
else
{
returned_id = _way_tag_reader.GetInt64(0);
}
}
}
if (!_way_node_reader.IsClosed)
{
long returned_id = _way_node_reader.GetInt64(_way_node_reader.GetOrdinal("way_id"));
while (returned_id == way.Id.Value)
{
if (way.Nodes == null)
{
way.Nodes = new List<long>();
}
long node_id = _way_node_reader.GetInt64(1);
way.Nodes.Add(node_id);
if (!_way_node_reader.Read())
{
_way_node_reader.Close();
returned_id = -1;
}
else
{
returned_id = _way_node_reader.GetInt64(0);
}
}
}
// set the current variable!
_current = way;
// advance the reader(s).
if (!_way_reader.Read())
{
_way_reader.Close();
}
if (!_way_tag_reader.IsClosed && !_way_tag_reader.Read())
{
_way_tag_reader.Close();
}
if (!_way_node_reader.IsClosed && !_way_node_reader.Read())
{
_way_node_reader.Close();
}
return true;
}
else
{
_way_reader.Close();
_way_reader.Dispose();
_way_reader = null;
_way_tag_reader.Close();
_way_tag_reader.Dispose();
_way_tag_reader = null;
_current_type = OsmGeoType.Relation;
return false;
}
}
private void MainForm_Load(object sender, EventArgs e)
{
// Resolve assemblies path
AppDomain currentDomain = AppDomain.CurrentDomain;
currentDomain.AssemblyResolve += new ResolveEventHandler(MyResolveEventHandler);
// Is there a record for this month ?
// If not then create one
sql_cmd.CommandText = "SELECT * FROM Month WHERE Year = '" + CustomDate.GetThaiYear(DateTime.Today.Year) + "' AND Month = '" + DateTime.Today.Month + "'";
sql_reader = sql_cmd.ExecuteReader();
if (!sql_reader.HasRows)
{
sql_reader.Close();
sql_cmd.CommandText = "INSERT INTO Month VALUES('" + CustomDate.GetThaiYear(DateTime.Today.Year) + "','" + DateTime.Today.Month + "',0,0,0)";
sql_cmd.ExecuteNonQuery();
string[] split = DateTime.Now.ToLongDateString().Split(' ');
toolStripHeader.Text = "บัญชีรับ-จ่าย ประจำเดือน " + split[1] + " พ.ศ. " + split[2];
}
else
{
sql_reader.Close();
LoadCurrentMonth();
}
LoadHistoryList();
}
void ReadHeader()
{
SQLiteCommand command = _connection.CreateCommand();
command.CommandText = "SELECT key, value FROM header;";
_reader = command.ExecuteReader();
while (_reader.Read())
{
var key = _reader.GetString(0);
var value = _reader.GetValue(1);
if (key.ToLower() == "clientbuild")
{
int build;
if (int.TryParse(value.ToString(), out build))
SetBuild(build);
break;
}
}
_reader.Close();
}
public string getHeadOfDepartment(string department)
{
SQLiteCommand selectData = new SQLiteCommand();
selectData.Connection = connection;
selectData.CommandText = "SELECT fio FROM doctors,departments WHERE departments.title=:department AND departments.headOfDepartment=doctors.id";
selectData.Parameters.Add(":department", DbType.String);
selectData.Parameters[":department"].Value = department;
SQLiteDataReader reader;
string result = "";
connect();
reader = selectData.ExecuteReader();
if (reader.Read())
{
result = reader[0].ToString();
}
reader.Close();
reader = null;
disconnect();
return result;
}
private void dgvClient_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
//-----20131209
var alertClient = (AlertClient)bsClient.Current;
try
{
// find out which column was clicked 開啟輸入一般訊息的新視窗
if (dgvClient.Columns[e.ColumnIndex] == btnOpenMsgBox)
{
SendNormalForm str = new SendNormalForm();
if (str.ShowDialog() != System.Windows.Forms.DialogResult.OK)
return;
//*************** 呼叫送一般訊息給client的方法 有使用到TalkMsg類別 ***************
alertClient.SendTalkMsg(new TalkMsg() { MsgContent = str.TalkMsg });
txtMsg.AppendText(DateTime.Now.ToString("MM-dd HH:mm") + " SendTo " + alertClient.Name + " " + alertClient.Location + ": " + str.TalkMsg + "\r\n");
}
//-----------20131209 1500 開啟輸入告警訊息的新視窗
// find out which column was clicked
if (dgvClient.Columns[e.ColumnIndex] == btnOpenAlarmBox)
{
//DataGridViewButtonCell btnOpen = new DataGridViewButtonCell();
SendAlertForm alert = new SendAlertForm();
alert.Show();
//form4的通知序號欄位歸零
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM ALERTLOG ORDER BY ALERTMSGID DESC;";
var Msg = sqlite_cmd.ExecuteReader();
Msg.Read();
serial_No = int.Parse(Msg["alertmsgid"].ToString());
Msg.Close();
alert.txtMsgId.Text = (serial_No + 1).ToString(); ;
//將Form2的資料輸入到Form4的欄位自動帶出資料
alert.txtServerId.Text = txtServiceID.Text;
alert.txtRDSSource.Text = txtServiceID.Text;
// 取DataGridView資料來源的Location欄位
alert.txtArea.Text = alertClient.Location;
LocationList = new List<string>();
// 查詢Location表單，取地區代號、地區說明
sqlite_cmd.CommandText = "SELECT * FROM Location where RDSLocID = '" + alertClient.Location + "'";
// 執行查詢Location塞入 sqlite_datareader
sqlite_datareader = sqlite_cmd.ExecuteReader();
LocationList = new List<string>();
// 一筆一筆列出查詢的資料
while (sqlite_datareader.Read())
{
// Print out the content of the LocationID field:
LocationID = sqlite_datareader["RDSLocID"].ToString();
LocationDesc = sqlite_datareader["RDSLocDesc"].ToString();
LocationList.Add(LocationDesc);
}
sqlite_datareader.Close();
sqlite_conn.Close();
alert.lblLocDesc.Text = LocationDesc;
//取DataGridView資料來源的Location欄位(地區代號)去資料庫查詢對應的中文說明 20131216
// 取DataGridView資料來源的Name欄位
alert.Text = "送告警給" + alertClient.Name;
alert.FormClosed += new FormClosedEventHandler(AlarmFormClose);
}
}
catch (Exception error)
{
txtMsg.AppendText(error.ToString() + "\r\n");
}
#endregion
}
public string getID(string parameter, typesData type)
{
string ID;
ID = "";
switch (type)
{
case typesData.department:
selectSQLCommand.CommandText = "SELECT id FROM departments WHERE title=:parameter";
break;
case typesData.doctor:
selectSQLCommand.CommandText = "SELECT id FROM doctors WHERE fio=:parameter";
break;
case typesData.region:
selectSQLCommand.CommandText = "SELECT id FROM regions WHERE title=:parameter";
break;
case typesData.lpz:
selectSQLCommand.CommandText = "SELECT id FROM lpz WHERE title=:parameter";
break;
case typesData.diagnose:
selectSQLCommand.CommandText = "SELECT codeMKB FROM diagnosis WHERE title=:parameter";
break;
case typesData.mkbcode:
selectSQLCommand.CommandText = "SELECT codeMKB FROM diagnosis WHERE codeMKB || ' ' || title =:parameter";
break;
case typesData.headOfDepartment:
selectSQLCommand.CommandText = "SELECT headOfDepartment FROM departments WHERE id =:parameter";
break;
case typesData.invalidityGroupe:
selectSQLCommand.CommandText = "SELECT id FROM invalidityGroups WHERE groupe =:parameter";
break;
}
selectSQLCommand.Parameters.Add(":parameter", DbType.String);
selectSQLCommand.Parameters[":parameter"].Value = parameter;
connect();
reader = selectSQLCommand.ExecuteReader();
if (reader.Read())
{
ID = reader[0].ToString();
}
reader.Close();
reader = null;
disconnect();
return ID;
}
public DataTable run_query(string sql_query)
{
DataTable sql_result = new DataTable();
try
{
using (connection = new SQLiteConnection(database_connection_string))
{
connection.Open();
command = new SQLiteCommand(connection);
command.CommandText = sql_query;
data_reader = command.ExecuteReader();
sql_result.Load(data_reader);
data_reader.Close();
connection.Close();
}
}
catch (Exception e)
{
close_connections();
throw e;
}
return sql_result;
}
void Export_RowsData_Update(SQLiteBackup manager)
{
bool allPrimaryField = true;
foreach (var col in Columns)
{
if (!col.IsPrimaryKey)
{
allPrimaryField = false;
break;
}
}
if (allPrimaryField)
{
return;
}
bool allNonPrimaryField = true;
foreach (var col in Columns)
{
if (col.IsPrimaryKey)
{
allNonPrimaryField = false;
break;
}
}
if (allNonPrimaryField)
{
return;
}
manager.Command.CommandText = string.Format("SELECT * FROM `{0}`;", _name);
SQLiteDataReader rdr = manager.Command.ExecuteReader();
while (rdr.Read())
{
if (manager.stopProcess)
{
return;
}
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE `");
sb.Append(_name);
sb.Append("` SET ");
Export_GetUpdateString(rdr, sb);
sb.Append(" WHERE ");
Export_GetConditionString(rdr, sb);
sb.Append(";");
manager.Export_WriteLine(sb.ToString());
manager.textWriter.Flush();
}
rdr.Close();
}
public invalidityData selectinvalidity(string id)
{
invalidityData result = new invalidityData();
SQLiteCommand selectData = new SQLiteCommand();
SQLiteDataReader reader = null;
selectData.Connection = connection;
selectData.CommandText = "SELECT fio, sex, dateBirth, age, region, town, address, addressWork, position, lpz, invalidityDate, groupe, mkbCode, diagnose,"
+" addition FROM invalidityView WHERE id=:id";
selectData.Parameters.Add(":id", DbType.String);
selectData.Parameters[":id"].Value = id;
connect();
reader = selectData.ExecuteReader();
if (reader.Read())
{
result.fio = reader["fio"].ToString();
result.sex = reader["sex"].ToString();
result.dateBirth = Convert.ToDateTime(reader["dateBirth"].ToString());
result.age = reader["age"].ToString();
result.region = reader["region"].ToString();
result.town = reader["town"].ToString();
result.address = reader["address"].ToString();
result.addressWork = reader["addressWork"].ToString();
result.position = reader["position"].ToString();
result.LPZ = reader["lpz"].ToString();
result.InvalidityDate = Convert.ToDateTime(reader["invalidityDate"].ToString());
result.invalidityGroupe = reader["groupe"].ToString();
result.mkbCode = reader["mkbCode"].ToString();
result.diagnose = reader["diagnose"].ToString();
result.addition = reader["addition"].ToString();
}
reader.Close();
reader = null;
return result;
}
private bool DoMoveNextRelation()
{
if (_relation_reader == null)
{
var relationCommand = new SQLiteCommand("select * from relation order by id", _connection);
_relation_reader = relationCommand.ExecuteReader();
if (!_relation_reader.Read())
{
_relation_reader.Close();
}
var relationTagCommand = new SQLiteCommand("select * from relation_tags order by relation_id", _connection);
_relation_tag_reader = relationTagCommand.ExecuteReader();
if (!_relation_tag_reader.IsClosed && !_relation_tag_reader.Read())
{
_relation_tag_reader.Close();
}
var relationNodeCommand = new SQLiteCommand("select * from relation_members order by relation_id,sequence_id", _connection);
_relation_member_reader = relationNodeCommand.ExecuteReader();
if (!_relation_member_reader.IsClosed && !_relation_member_reader.Read())
{
_relation_member_reader.Close();
}
}
// read next relation.
if (!_relation_reader.IsClosed)
{
// load/parse data.
long id = _relation_reader.GetInt64(0);
long changesetId = _relation_reader.GetInt64(1);
bool visible = _relation_reader.GetInt64(2) == 1;
DateTime timestamp = _relation_reader.IsDBNull(3) ? DateTime.MinValue : _relation_reader.GetDateTime(3);
long version = _relation_reader.GetInt64(4);
string user = _relation_reader.GetString(5);
long uid = _relation_reader.GetInt64(6);
var relation = new Relation
{
Id = id,
ChangeSetId = changesetId,
TimeStamp = timestamp,
UserId = null,
UserName = null,
Version = (ulong)version,
Visible = visible
};
relation.UserName = user;
relation.UserId = uid;
if (!_relation_tag_reader.IsClosed)
{
long returnedId = _relation_tag_reader.GetInt64(0);
while (returnedId == relation.Id.Value)
{
if (relation.Tags == null)
{
relation.Tags = new TagsCollection();
}
string key = _relation_tag_reader.GetString(1);
string value = _relation_tag_reader.GetString(2);
relation.Tags.Add(key, value);
if (!_relation_tag_reader.Read())
{
_relation_tag_reader.Close();
returnedId = -1;
}
else
{
returnedId = _relation_tag_reader.GetInt64(0);
}
}
}
if (!_relation_member_reader.IsClosed)
{
long returnedId = _relation_member_reader.GetInt64(0);
while (returnedId == relation.Id.Value)
{
if (relation.Members == null)
{
relation.Members = new List<RelationMember>();
}
string memberType = _relation_member_reader.GetString(1);
long memberId = _relation_member_reader.GetInt64(2);
object memberRole = _relation_member_reader.GetValue(3);
var member = new RelationMember();
member.MemberId = memberId;
if (memberRole != DBNull.Value)
{
member.MemberRole = memberRole as string;
}
switch (memberType)
{
case "Node":
member.MemberType = OsmGeoType.Node;
break;
case "Way":
member.MemberType = OsmGeoType.Way;
break;
case "Relation":
member.MemberType = OsmGeoType.Relation;
break;
}
relation.Members.Add(member);
if (!_relation_member_reader.Read())
{
_relation_member_reader.Close();
returnedId = -1;
}
else
{
returnedId = _relation_member_reader.GetInt64(0);
}
}
}
// set the current variable!
_current = relation;
// advance the reader(s).
if (!_relation_reader.Read())
{
_relation_reader.Close();
}
if (!_relation_tag_reader.IsClosed && !_relation_tag_reader.Read())
{
_relation_tag_reader.Close();
}
if (!_relation_member_reader.IsClosed && !_relation_member_reader.Read())
{
_relation_member_reader.Close();
}
return true;
}
else
{
_relation_reader.Close();
_relation_reader.Dispose();
_relation_reader = null;
_relation_tag_reader.Close();
_relation_tag_reader.Dispose();
_relation_tag_reader = null;
_current_type = OsmGeoType.Relation;
return false;
}
}
private void LoadHistoryList()
{
sql_cmd.CommandText = "SELECT DISTINCT Year FROM Month WHERE Year != '" + CustomDate.GetThaiYear(DateTime.Today.Year) + "' OR Month != '" + DateTime.Today.Month + "'";
sql_reader = sql_cmd.ExecuteReader();
if (sql_reader.HasRows)
{
while (sql_reader.Read())
{
cmbYear.Items.Add(sql_reader.GetString(0));
}
cmbYear.Enabled = true;
}
else
{
cmbYear.Enabled = false;
}
sql_reader.Close();
}
private void button1_Click(object sender, EventArgs e)
{
var m_dbConnection = new SQLiteConnection("Data Source=28cm_db.sqlite;Version=3;");
bool exist = false;
if ((comboBox1.Text.Length > 0) && (comboBox2.Text.Length > 0))
{
//string date = Convert.ToString(DateTime.Today);
//string les_column = comboBox2.Text;
/*sql = "SELECT date, les_type, group FROM poseshenie WHERE date = ('" + DateTime.Today + "') les_type = ('" + comboBox2.Text + "') group = ('" + comboBox1.Text + "')";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
reader = command.ExecuteReader();
if (reader.Read())
exist = true;
reader.Close();
m_dbConnection.Close();
if (!exist)
{*/
int count = 0;
sql = "SELECT COUNT(rowid) FROM ('" + comboBox1.Text + "')";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
reader = command.ExecuteReader();
if (reader.Read())
count = Convert.ToInt32(reader["COUNT(rowid)"]);
reader.Close();
m_dbConnection.Close();
for(int jesus = 1; jesus <= count; jesus++)
{
sql = "INSERT INTO poseshenie (date, les_type, student, prisut, grou_p) VALUES ('" + DateTime.Today + "' , '" + comboBox2.Text + "' , '" + jesus + "' , '" + 0 + "' , '" + comboBox1.Text + "')";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
command.ExecuteNonQuery();
m_dbConnection.Close();
}
Form1.tabControl1.TabPages.Clear();
Form1.Initialize();
Close();
/*}
else
{
MessageBox.Show("Занятие существует", "Ошибка",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}*/
/*sql = "INSERT INTO days (date, les_type, group) VALUES ('" + DateTime.Today + "','" + comboBox2.Text + "', '" + comboBox1.Text + "' )";
command = new SQLiteCommand(sql, m_dbConnection);
m_dbConnection.Open();
command.ExecuteNonQuery();
m_dbConnection.Close();*/
//Close();
}
else
{
MessageBox.Show("Введите данные", "Ошибка",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private bool DoMoveNextNode()
{
if (_node_reader == null)
{
SQLiteCommand node_command = new SQLiteCommand("select * from node left join node_tags on node_tags.node_id = node.id order by node.id");
node_command.Connection = _connection;
_node_reader = node_command.ExecuteReader();
if (!_node_reader.Read())
_node_reader.Close();
}
// read next node.
if (!_node_reader.IsClosed)
{
// load/parse data.
Node node = new Node();
node.Id = _node_reader.GetInt64(0);
node.Latitude = _node_reader.GetInt64(1) / 10000000.0;
node.Longitude = _node_reader.GetInt64(2) / 10000000.0;
node.ChangeSetId = _node_reader.GetInt64(3);
node.TimeStamp = _node_reader.GetDateTime(5);
node.Version = (ulong)_node_reader.GetInt64(7);
node.Visible = _node_reader.GetInt64(4) == 1;
//node.UserName = _node_reader.GetString(8);
//node.UserId = _node_reader.IsDBNull(9) ? -1 : _node_reader.GetInt64(9);
//Has tags?
if (!_node_reader.IsDBNull(10))
{
//if (node.Tags == null)
//node.Tags = new Dictionary<string, string>();
long currentnode = node.Id.Value;
while (currentnode == node.Id.Value)
{
//string key = _node_reader.GetString(11);
//string value = _node_reader.GetString(12);
//node.Tags.Add(key, value);
if (!_node_reader.Read())
{
_node_reader.Close();
break;
}
currentnode = _node_reader.GetInt64(0);
}
}
else if (!_node_reader.Read())
_node_reader.Close();
// set the current variable!
_current = node;
return true;
}
_node_reader.Close();
_node_reader.Dispose();
_node_reader = null;
_current_type = OsmGeoType.Way;
return false;
}
public List<Tuple<string, string, string>> GetAllAuthors()
{
var authors = new List<Tuple<string, string, string>>();
m_dbConnection = new SQLiteConnection("Data Source=" + db + ";Version=3;");
m_dbConnection.Open();
string sql = "SELECT * FROM authors";
command = new SQLiteCommand(sql, m_dbConnection);
reader = command.ExecuteReader();
string id = "";
string name = "";
string picture = "";
while (reader.Read())
{
id = reader["id"].ToString();
name = reader["name"].ToString();
picture = reader["picture"].ToString();
authors.Add(new Tuple<string, string, string>(id, name, picture));
}
reader.Close();
m_dbConnection.Close();
return authors;
}
public List<UserInfo> ReadUserInfo()
{
// Create a list for the user info.
List<UserInfo> listUserInfo = new List<UserInfo>();
// Open the database.
dbConnection.Open();
// Retrieve all records from the table called "mailaddresses".
dbCommand.CommandText = "SELECT * FROM mailaddresses;";
// Execute the newly created command.
dbQuery = dbCommand.ExecuteReader();
// Read the retrieved query, and write the results to the newly created list.
while (dbQuery.Read())
listUserInfo.Add(new UserInfo
{
userMail = dbQuery["address"].ToString(),
password = dbQuery["password"].ToString(),
autoLogin = dbQuery["autologin"].ToString()
});
// Close the query-reader again.
dbQuery.Close();
// Close the database again.
dbConnection.Close();
// Return the created list.
return listUserInfo;
}
public SendAlertForm()
{
InitializeComponent();
// Open
sqlite_conn.Open();
// 要下任何命令先取得該連結的執行命令物件
sqlite_cmd = sqlite_conn.CreateCommand();
// 查詢Status表單，取告警代號、告警說明
sqlite_cmd.CommandText = "SELECT * FROM Status";
// 執行查詢Status塞入 sqlite_datareader
sqlite_datareader = sqlite_cmd.ExecuteReader();
RDSStsDescList = new List<string>();
// 一筆一筆列出查詢的資料
while (sqlite_datareader.Read())
{
// Print out the content of the RDSStsID field:
RDSStsID = sqlite_datareader["RDSStsID"].ToString();
RDSStsDesc = sqlite_datareader["RDSStsDesc"].ToString();
cboWarn.Items.Add(RDSStsID);
RDSStsDescList.Add(RDSStsDesc);
//MessageBox.Show(s);
}
sqlite_datareader.Close();
cboWarn.SelectedIndex = 0;
//結束，關閉資料庫連線
sqlite_conn.Close();
}
