public static string GetString(this SQLiteDataReader reader, string ColumnName)
{
int i = reader.GetOrdinal(ColumnName);
if (reader.IsDBNull(i))
{
return(null);
}
else
{
return(reader.GetString(i));
}
}
private void AddFromRecordSet(SQLiteDataReader rs)
{
try
{
// if value from the recordset, to the PointsPolygonID _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("PointsPolygonID")) == false))
{
PointsPolygonID = ((long)(rs["PointsPolygonID"]));
}
// if value from the recordset, to the PolygonID _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("PolygonID")) == false))
{
PolygonID = ((long)(rs["PolygonID"]));
}
// if value from the recordset, to the oIndex _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("oIndex")) == false))
{
oIndex = ((int)(rs["oIndex"]));
}
// if value from the recordset, to the PtX _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("PtX")) == false))
{
PtX = ((int)(rs["PtX"]));
}
// if value from the recordset, to the PtY _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("PtY")) == false))
{
PtY = ((int)(rs["PtY"]));
}
}
catch (SQLiteException sqlExc)
{
throw sqlExc;
}
catch (Exception Exc)
{
throw Exc;
}
}
private void AddFromRecordSet(SQLiteDataReader rs)
{
try
{
// if value from the recordset, to the FBDBlockPinID _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("FBDBlockPinID")) == false))
{
FBDBlockPinID = ((long)(Convert.ChangeType(rs["FBDBlockPinID"], typeof(long))));
}
// if value from the recordset, to the FBDBlockID _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("FBDBlockID")) == false))
{
FBDBlockID = ((long)(Convert.ChangeType(rs["FBDBlockID"], typeof(long))));
}
// if value from the recordset, to the visable _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("visable")) == false))
{
visable = ((bool)(Convert.ChangeType(rs["visable"], typeof(bool))));
}
// if value from the recordset, to the InitValue _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("InitValue")) == false))
{
InitValue = ((string)(Convert.ChangeType(rs["InitValue"], typeof(string))));
}
// if value from the recordset, to the oIndex _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("oIndex")) == false))
{
oIndex = ((int)(Convert.ChangeType(rs["oIndex"], typeof(int))));
}
}
catch (SQLiteException sqlExc)
{
throw sqlExc;
}
catch (Exception Exc)
{
throw Exc;
}
}
public List <City> FetchCities()
{
var query = "SELECT * FROM city";
var cmd = new SQLiteCommand(query, conn);
SQLiteDataReader rd = cmd.ExecuteReader();
List <City> cities = new List <City>();
while (rd.Read())
{
string cityName = rd.GetString(rd.GetOrdinal("City"));
double cityPopulation = rd.GetDouble(rd.GetOrdinal("Population"));
City city = new City(cityName, cityPopulation);
cities.Add(city);
}
rd.Close();
return(cities);
}
public static long?GetNullableLong(this SQLiteDataReader reader, string ColumnName)
{
int i = reader.GetOrdinal(ColumnName);
if (reader.IsDBNull(i))
{
return(null);
}
else
{
return(reader.GetInt64(i));
}
}
public void AddFromRecordSet(SQLiteDataReader rs)
{
try
{
// if value from the recordset, to the ID _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("ID")) == false))
{
ID = ((long)(Convert.ChangeType(rs["ID"], typeof(long))));
}
// if value from the recordset, to the FunctionID _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("FunctionID")) == false))
{
FunctionID = ((long)(Convert.ChangeType(rs["FunctionID"], typeof(long))));
}
// if value from the recordset, to the FunctionType _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("FunctionType")) == false))
{
FunctionType = ((int)(Convert.ChangeType(rs["FunctionType"], typeof(int))));
}
// if value from the recordset, to the Status _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("Status")) == false))
{
Status = ((int)(Convert.ChangeType(rs["Status"], typeof(int))));
}
// if value from the recordset, to the oIndex _databasename is NOT null then set the value.
if ((rs.IsDBNull(rs.GetOrdinal("oIndex")) == false))
{
oIndex = ((int)(Convert.ChangeType(rs["oIndex"], typeof(int))));
}
}
catch (SQLiteException sqlExc)
{
throw sqlExc;
}
catch (Exception Exc)
{
throw Exc;
}
}
public void F3()
{
string cs = @"URI=file:test.db";
using (var con = new SQLiteConnection(cs))
{
con.Open();
SQLiteCommand comm = new SQLiteCommand("Select * From cars", con);
using (SQLiteDataReader read = comm.ExecuteReader())
{
while (read.Read())
{
dataGridView1.Rows.Add(new object[] {
read.GetValue(0), // U can use column index
read.GetValue(read.GetOrdinal("id")), // Or column name like this
read.GetValue(read.GetOrdinal("name")),
read.GetValue(read.GetOrdinal("price"))
});
}
}
}
}
private void onBB_ComboBoxClosed(object sender, EventArgs e)
{
R_details.Visibility = Visibility.Hidden;
BB_details.Visibility = Visibility.Visible;
Database d = new Database();
string query = "SELECT MI_ID, NAME, PHONE, WEBSITE, EMAIL, LOCATION, CITY FROM MED_INST WHERE NAME='" + BBList.Text + "'";
try
{
d.openConnection();
SQLiteCommand cmd = new SQLiteCommand(query, d.con);
SQLiteDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
bb_ID = dr["MI_ID"].ToString();
BB_Name.Text = dr["NAME"].ToString();
BB_Ph.Text = dr["PHONE"].ToString();
BB_Website.Text = dr.GetString(dr.GetOrdinal("WEBSITE"));
BB_Email.Text = dr.GetString(dr.GetOrdinal("EMAIL"));
BB_Loc.Text = dr.GetString(dr.GetOrdinal("LOCATION"));
BB_City.Text = dr.GetString(dr.GetOrdinal("CITY"));
}
}
//else
//{
// MessageBox.Show("No option selected");
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
d.closeConnection();
}
}
private List <Result> QuerySqllite(Doc doc, string key)
{
string dbPath = "Data Source =" + doc.DBPath;
SQLiteConnection conn = new SQLiteConnection(dbPath);
conn.Open();
string sql = GetSqlByDocDBType(doc.DBType).Replace("{0}", key);
SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmdQ.ExecuteReader();
List <Result> results = new List <Result>();
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("name"));
string docPath = reader.GetString(reader.GetOrdinal("path"));
results.Add(new Result
{
Title = name,
SubTitle = doc.Name.Replace(".docset", ""),
IcoPath = doc.IconPath,
Action = (c) =>
{
string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetBasePath,
doc.Name, docPath, name);
//frm.ShowDoc(url);
string browser = GetDefaultBrowserPath();
Process.Start(browser, String.Format("\"file:///{0}\"", url));
return(true);
}
});
}
conn.Close();
return(results);
}
public static long?GetNullableLong(this SQLiteDataReader reader, string ColumnName, long?fallbackValue = null)
{
var ordinal = reader.GetOrdinal(ColumnName);
try
{
return(reader.GetInt64(ordinal));
}
catch
{
return(fallbackValue);
}
}
public static double?GetNullableDouble(this SQLiteDataReader reader, string ColumnName, double?fallbackValue = null)
{
var ordinal = reader.GetOrdinal(ColumnName);
try
{
return(reader.GetDouble(ordinal));
}
catch
{
return(fallbackValue);
}
}
private void Data_Load(object sender, EventArgs e)
{
string select_sql = "SELECT * FROM Student where STATUS = '" + 1 + "'";
SQLiteConnection conn = new SQLiteConnection(db.ConnectionString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(select_sql, conn);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dataGridView1.Rows.Add(new object[]
{
reader.GetValue(reader.GetOrdinal("NAME")),
reader.GetValue(reader.GetOrdinal("ID"))
});
}
}
conn.Close();
}
public void ReadAllStudent()
{
//------------------------------------------------------------------------------------
listView1.BeginUpdate();
listView1.Items.Clear();
//读取整张表
SQLiteDataReader reader = sql.ReadFullTable("student");
while (reader.Read())
{
int no = listView1.Items.Count;
listView1.Items.Add(Convert.ToString(no + 1));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sno")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sname")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("ssex")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sage")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sdept")));
}
//-------------------------------------------------------------------------------
listView1.EndUpdate();
}
/// <summary>
/// Read in a binary blob; Per convention, must be formatted in "ID, FileName (Contain type) Content";
/// A complete copy of the binary data is saved in the memory
/// User responsible for disposing the memory stream.
/// </summary>
public static BinaryData SingleBlob(this SQLiteDataReader reader)
{
// If there is no row and then return null
if (!reader.HasRows)
{
reader.Close(); // Close reader
return(null);
}
// Type check
if (reader.FieldCount != 3 ||
reader.GetOrdinal("ID") == -1 ||
reader.GetOrdinal("FileName") == -1 ||
reader.GetOrdinal("Content") == -1)
{
throw new ArgumentException("Input data table must have ID, Name, Type and Content fields.");
}
BinaryData data = null;
while (reader.Read()) // Loop is necessary to close the reading even if we have only one row
{
SQLiteBlob blob = reader.GetBlob(reader.GetOrdinal("Content"), true);
if (blob.GetCount() != 0)
{
string filename = reader.GetString(reader.GetOrdinal("FileName"));
string type = Path.GetExtension(filename).Replace(".", "");
string name = Path.GetFileNameWithoutExtension(filename);
byte[] buffer = new byte[blob.GetCount()];
blob.Read(buffer, blob.GetCount(), 0);
data = new BinaryData(name, type, buffer);
}
blob.Close();
blob.Dispose();
}
reader.Close();
return(data);
}
private void btnSct_Click(object sender, EventArgs e)
{
string strsql = "select * from sct";
string[] value = { "sno", "cno", "tno", "grade" };
string[] content = { "学号", "课程号", "教工号", "成绩" };
for (int i = 0; i < content.Length; i++)
{
if (cbSct.Text == content[i])
{
strsql += " where ";
strsql += value[i];
strsql += " like '%";
strsql += txtSct.Text;
strsql += "%'";
break;
}
}
//MessageBox.Show(strsql);
//------------------------------------------------------------------------------------
listView5.BeginUpdate();
listView5.Items.Clear();
//读取整张表
SQLiteDataReader reader = sql.ExecuteQuery(strsql);
while (reader.Read())
{
int no = listView5.Items.Count;
listView5.Items.Add(Convert.ToString(no + 1));
listView5.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("GUID")));
listView5.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sno")));
listView5.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cno")));
listView5.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("tno")));
listView5.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("grade")));
}
//-------------------------------------------------------------------------------
listView5.EndUpdate();
}
public static long GetLong(this SQLiteDataReader reader, string ColumnName, long?Default = null)
{
int i = reader.GetOrdinal(ColumnName);
if (reader.IsDBNull(i))
{
return(Default ?? throw new ArgumentNullException(ColumnName));
}
else
{
return(reader.GetInt64(i));
}
}
public TransferForm(string arg)
{
InitializeComponent();
//Select the confirm button by default
ActiveControl = confirmButton;
origin = arg;
//Get any junctions that point to the folder being moved, and save where they were moved from if they exist
SQLiteDataReader reader = SQLiteManager.ExecuteSQLiteCommand("SELECT origin, target FROM junctions WHERE target = '" + origin + "';");
string databaseOrigin = null;
if (reader.Read())
{
databaseOrigin = reader.GetString(reader.GetOrdinal("origin"));
}
reader.Close();
SQLiteManager.CloseConnection();
if (!JunctionPoint.Exists(origin))
{
//If the directory given is not a junction and is registered in the database...
if (databaseOrigin != null)
{
//Hide some elements to just show a message instead of getting input from the user
junctionArg = true;
destinationInput.Visible = false;
browseButton.Visible = false;
//Assign the variables their proper values
target = origin;
origin = databaseOrigin;
//Give the user a message that this folder has been moved by this app, and ask if they want to move it back
label1.Text = target + " is already moved from " + origin + "! Would you like to move it back?";
}
else
{
//If the directory given is not a junction and isn't registered in the database...
//Leave the window in its standard move with junction state, and put the last used location as the default
junctionArg = false;
destinationInput.Text = Program.GetLastStorage() + "\\" + new DirectoryInfo(origin).Name;
}
}
else
{
//if the directory given is a junction
//Hide some elements to just show a message instead of getting input from the user
junctionArg = true;
destinationInput.Visible = false;
browseButton.Visible = false;
//Get the directory the junction is pointing to and ask the user if he would like to move that folder back
target = JunctionPoint.GetTarget(origin);
label1.Text = "Move " + target + " back to its original location at " + origin + "?";
}
}
public override bool Load()
{
bool ret = true;
List <long> idlist = new List <long>();
if (Common.Conn == null)
{
Common.Conn = new SQLiteConnection(Common.ConnectionString);
Common.Conn.Open();
}
SQLiteDataReader myReader = null;
SQLiteCommand myCommand = new SQLiteCommand();
try
{
myReader = null;
myCommand.CommandText = @"SELECT * FROM [tblRect] WHERE [DisplayID]= " + m_DisplayID_tblDisplay.DisplayID + " order by oIndex;";
myCommand.Connection = Common.Conn;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
idlist.Add(myReader.GetInt64(myReader.GetOrdinal("ID")));
}
myReader.Close();
myCommand.Dispose();
foreach (long id in idlist)// (int i = 0; i < count ; i++)
{
tblRect tblrect = new tblRect();
tblrect.ID = id;
tblrect.m_DisplayID_tblDisplay = this.m_DisplayID_tblDisplay;
tblrect.Select();
Add(tblrect);
}
}
catch (SQLiteException ae)
{
System.Windows.Forms.MessageBox.Show(ae.Message);
return(false);
}
return(ret);
}
private void btnStu_Click(object sender, EventArgs e)
{
string strsql = "select * from student";
string[] value = { "sno", "sname", "ssex", "sage", "sdept" };
string[] content = { "学号", "姓名", "性别", "年龄", "系别" };
for (int i = 0; i < content.Length; i++)
{
if (cbStu.Text == content[i])
{
strsql += " where ";
strsql += value[i];
strsql += " like '%";
strsql += txtStu.Text;
strsql += "%'";
break;
}
}
//MessageBox.Show(strsql);
//------------------------------------------------------------------------------------
listView1.BeginUpdate();
listView1.Items.Clear();
//读取整张表
SQLiteDataReader reader = sql.ExecuteQuery(strsql);
while (reader.Read())
{
int no = listView1.Items.Count;
listView1.Items.Add(Convert.ToString(no + 1));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sno")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sname")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("ssex")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sage")));
listView1.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("sdept")));
}
//-------------------------------------------------------------------------------
listView1.EndUpdate();
}
private void btnCur_Click(object sender, EventArgs e)
{
string strsql = "select * from course";
string[] value = { "cno", "cname", "cpno", "ccredit" };
string[] content = { "课程号", "课程名", "先行课", "学分" };
for (int i = 0; i < content.Length; i++)
{
if (cbCur.Text == content[i])
{
strsql += " where ";
strsql += value[i];
strsql += " like '%";
strsql += txtCur.Text;
strsql += "%'";
break;
}
}
//MessageBox.Show(strsql);
//------------------------------------------------------------------------------------
listView2.BeginUpdate();
listView2.Items.Clear();
//读取整张表
SQLiteDataReader reader = sql.ExecuteQuery(strsql);
while (reader.Read())
{
int no = listView2.Items.Count;
listView2.Items.Add(Convert.ToString(no + 1));
listView2.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cno")));
listView2.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cname")));
listView2.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cpno")));
listView2.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("ccredit")));
}
//-------------------------------------------------------------------------------
listView2.EndUpdate();
}
private void Reciv_ComboBoxClosed(object sender, EventArgs e)
{
Req.Visibility = Visibility.Hidden;
Reciv.Visibility = Visibility.Visible;
Database d = new Database();
try
{
string[] str = Reciv_List.Text.Split('|');
or_ID = str[0].Trim();
string query = "SELECT H.NAME, O.B_GRP, O.QUANTITY FROM ORDERS O, MED_INST H WHERE O.OR_ID='" + or_ID + "' AND H.MI_ID=O.DONOR_ID;";
d.openConnection();
SQLiteCommand cmd = new SQLiteCommand(query, d.con);
SQLiteDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
Reciv_Or_Id.Content = or_ID;
Reciv_BB_Name.Content = dr.GetString(dr.GetOrdinal("NAME"));
Reciv_B_grp.Content = dr.GetString(dr.GetOrdinal("B_GRP"));
Reciv_quantity.Text = dr["QUANTITY"].ToString();
}
}
//else
//{
// MessageBox.Show("No rows selected");
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
d.closeConnection();
}
}
private void btnTch_Click(object sender, EventArgs e)
{
string strsql = "select * from teacher";
string[] value = { "tno", "tname", "tsex", "tage", "teb", "tpt", "cno1", "cno2", "cno3" };
string[] content = { "教工号", "姓名", "性别", "年龄", "学历", "职称", "主课一", "主课二", "主课三" };
for (int i = 0; i < content.Length; i++)
{
if (cbTch.Text == content[i])
{
strsql += " where ";
strsql += value[i];
strsql += " like '%";
strsql += txtTch.Text;
strsql += "%'";
break;
}
}
//MessageBox.Show(strsql);
//------------------------------------------------------------------------------------
listView3.BeginUpdate();
listView3.Items.Clear();
//读取整张表
SQLiteDataReader reader = sql.ExecuteQuery(strsql);
while (reader.Read())
{
int no = listView3.Items.Count;
listView3.Items.Add(Convert.ToString(no + 1));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("tno")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("tname")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("tsex")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("tage")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("teb")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("tpt")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cno1")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cno2")));
listView3.Items[no].SubItems.Add(reader.GetString(reader.GetOrdinal("cno3")));
}
//-------------------------------------------------------------------------------
listView3.EndUpdate();
}
public void DrawAssestsDataGridView()
{
assestsDataGridView.Rows.Clear();
try
{
SQLiteConnection con = new SQLiteConnection(Variables.dataPath);
con.Open();
SQLiteCommand comm = new SQLiteCommand("Select * From Assests", con);
using (SQLiteDataReader read = comm.ExecuteReader())
{
while (read.Read())
{
assestsDataGridView.Rows.Add(new object[] {
read.GetValue(0), // U can use column index
read.GetValue(read.GetOrdinal("Description")), // Or column name like this
read.GetValue(read.GetOrdinal("Category")),
read.GetValue(read.GetOrdinal("Amount"))
});
}
}
con.Close();
//Set amount column to currency format
assestsDataGridView.Columns["assestAmtColumn"].DefaultCellStyle.Format = "c";
if (assestsDataGridView.Rows.Count != 0)
{
removeAssestButton.Enabled = true;
}
else
{
removeAssestButton.Enabled = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
LoadSpentVSavedChart();
}
/// <summary>
/// Get a professor user from the database
/// </summary>
/// <param name="username">Username of the professor</param>
/// <returns>A professor object with the necessary values</returns>
internal static Professor Get(string username)
{
using (SQLiteConnection conn = new SQLiteConnection("Data source=" + Database.DATABASE_NAME + ";"))
{
try
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "SELECT * FROM users WHERE username=@username AND user_type=@user_type;";
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@user_type", UserTypes.PROFESSOR);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
// Because we search with username which is unique
if (reader.HasRows)
{
reader.Read();
string name = reader.GetString(reader.GetOrdinal("name"));
string surname = reader.GetString(reader.GetOrdinal("surname"));
Professor prof = new Professor(username, name, surname);
return(prof);
}
else
{
return(null);
}
}
}
}
catch (Exception e)
{
Console.WriteLine("Exception at ProfessorMapper - Get: " + e.Message);
return(null);
}
}
}
private void Req_ComboBoxClosed(object sender, EventArgs e)
{
Reciv.Visibility = Visibility.Hidden;
Req.Visibility = Visibility.Visible;
Database d = new Database();
try
{
string query = "SELECT PH_NO, NAME, B_GRP, EMAIL, LOCATION, CITY FROM USER WHERE NAME='" + Req_List.Text + "';";
d.openConnection();
SQLiteCommand cmd = new SQLiteCommand(query, d.con);
SQLiteDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
d_id = dr["PH_NO"].ToString();
D_Name.Text = dr.GetString(dr.GetOrdinal("NAME"));
B_grp.Text = dr.GetString(dr.GetOrdinal("B_GRP"));
Email.Text = dr.GetString(dr.GetOrdinal("EMAIL"));
Loc.Text = dr.GetString(dr.GetOrdinal("LOCATION"));
City.Text = dr.GetString(dr.GetOrdinal("CITY"));
}
}
//else
//{
// MessageBox.Show("No rows selected");
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
d.closeConnection();
}
}
static void dllMain(string[] args)
{
sql = new SqLiteHelper();
sql.SqliteOpen("");
//创建名为table1的数据表
sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });
//插入两条数据
sql.InsertValues("table1", new string[] { "1", "张三", "22", "*****@*****.**" });
sql.InsertValues("table1", new string[] { "2", "李四", "25", "*****@*****.**" });
//更新数据，将Name="张三"的记录中的Name改为"Zhang3"
sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "ZhangSan" }, "Name", "Zhang3");
//删除Name="张三"且Age=26的记录,DeleteValuesOR方法类似
sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "张三", "22" }, new string[] { "=", "=" });
//读取整张表
SQLiteDataReader reader = sql.ReadFullTable("table1");
while (reader.Read())
{
//读取ID
Log("" + reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Log("" + reader.GetString(reader.GetOrdinal("Name")));
//读取Age
Log("" + reader.GetInt32(reader.GetOrdinal("Age")));
//读取Email
Log(reader.GetString(reader.GetOrdinal("Email")));
}
while (true)
{
Console.ReadLine();
}
}
protected override Customer InitEntryByReader(SQLiteDataReader reader)
{
Customer tmp;
int intId = reader.GetInt32(reader.GetOrdinal("id"));
ulong id = System.Convert.ToUInt64(intId);
string firstName = reader.GetString(reader.GetOrdinal("firstName"));
string lastName = reader.GetString(reader.GetOrdinal("lastName"));
DateTime birthDate = DateTime.Parse(reader.GetString(reader.GetOrdinal("birthDate")));
tmp = new Customer(id, firstName, lastName, birthDate);
string street = reader.GetString(reader.GetOrdinal("street"));
tmp.Street = street;
tmp.StreetNumber = Convert.ToInt32(reader.GetString(reader.GetOrdinal("streetNumber")));
tmp.AdditionalRoad = reader.GetString(reader.GetOrdinal("additionalRoad"));
tmp.ZipCode = reader.GetInt32(reader.GetOrdinal("zipCode"));
tmp.Town = reader.GetString(reader.GetOrdinal("town"));
tmp.Country = reader.GetString(reader.GetOrdinal("country"));
return(tmp);
}
private void Payment_Load(object sender, EventArgs e)
{
var db = new Db();
var con = db.connect();
SQLiteCommand comm = new SQLiteCommand("select * from servicehdr where PayStatus='N' AND Status = 'N' ", con);
using (SQLiteDataReader read = comm.ExecuteReader())
{
while (read.Read())
{
dataGridView1.Rows.Add(new object[] {
read.GetValue(0),
//read.GetValue(read.GetOrdinal("Id")), // Or column name like this
read.GetValue(read.GetOrdinal("InTime")),
read.GetValue(read.GetOrdinal("CarName")),
read.GetValue(read.GetOrdinal("CusName")),
read.GetValue(read.GetOrdinal("Service")),
read.GetValue(read.GetOrdinal("TotalPrice"))
});
}
}
}
private void Delfenlei_Click(object sender, EventArgs e)//删除分组
{
sql = new SqlHelp("data source=mydb.db");
sql.DeleteValuesAND("fenzu", new string[] { "Name" }, new string[] { Listfenzu.SelectedItem.ToString() }, new string[] { "=" });
Listfenzu.Items.Clear();
SQLiteDataReader reader = sql.ReadFullTable("fenzu");
while (reader.Read())
{
Listfenzu.Items.Add(reader.GetString(reader.GetOrdinal("Name")));
}
sql.CloseConnection();
MessageBox.Show("删除成功");
}
public static int GetInt32(this SQLiteDataReader rdr, string fieldName, int defaultValue = 0)
{
var obj = rdr[rdr.GetOrdinal(fieldName)];
if (obj == null)
{
return(defaultValue);
}
if (Convert.IsDBNull(obj))
{
return(defaultValue);
}
return(Convert.ToInt32(obj));
}
