//连接字符串
namespace LocoManagement
{
class global_variables
{
//sqlite3 odbc 连接字符串
public static string connStr = "Dsn=SQLite3 Datasource;database=D:\\database\\jiche.db;StepAPI=0;SyncPragma=;NoTXN=0;Timeout=;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=WAL;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;";
}
}
//数据库表格窗口
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
namespace LocoManagement
{
public partial class FormStationEditor : Form
{
public FormStationEditor()
{
InitializeComponent();
}
public string connStr = global_variables.connStr;
public TextBox[] textbox_array;
public Label[] label_array;
private void FormStationEditor_Load(object sender, EventArgs e)
{
string strSqlQuery = string.Format("select num as 序号,ID as 编号,IP as IP地址,UDP_port as UDP端口号,name as 名称,description as 描述,x as x,y as y,z as z from station");
OdbcConnection conn = new OdbcConnection(connStr);
OdbcDataAdapter da = new OdbcDataAdapter(strSqlQuery, conn); //引用数据库连接dbconn并依据SQL语句"select * from kaizhi"创建OleDbDataAdapter对象da
DataSet ds = new DataSet(); //创建DataSet对象
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
Label[] label_array_temp = { label1, label2, label3, label4, label5, label6, label7, label8, label9 };
TextBox[] textbox_array_temp = { textBox1, textBox2, textBox3, textBox4, textBox5, textBox6, textBox7, textBox8, textBox9 };
textbox_array = textbox_array_temp;
label_array = label_array_temp;
int i;
for (i = 0; i < label_array.Length; i++)
{
if (i < ds.Tables[0].Columns.Count)
label_array[i].Text = ds.Tables[0].Columns[i].ToString();
else
{
label_array[i].Visible = false;
textbox_array[i].Visible = false;
}
}
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i;
int index = dataGridView1.CurrentRow.Index;
for (i = 0; i < dataGridView1.ColumnCount; i++)
{
textbox_array[i].Text = dataGridView1.Rows[index].Cells[i].Value.ToString();
}
}
private void button_add_Click(object sender, EventArgs e)
{
string sql = "insert into station(ID,IP,UDP_port,name,description,x,y,z) values (?,?,?,?,?,?,?,?)";
if (textbox_array[1].Text.Trim() == "" || textbox_array[2].Text.Trim() == ""
|| textbox_array[3].Text.Trim() == "" || textbox_array[4].Text.Trim() == ""
|| textbox_array[5].Text.Trim() == "" || textbox_array[6].Text.Trim() == ""
|| textbox_array[7].Text.Trim() == "" || textbox_array[8].Text.Trim() == "")
{
MessageBox.Show("文本框的输入不能为空!");
return;
}
OdbcParameter[] param ={
new OdbcParameter("@ID",textbox_array[1].Text),
new OdbcParameter("@IP",textbox_array[2].Text),
new OdbcParameter("@UDP_port",textbox_array[3].Text),
new OdbcParameter("@name",textbox_array[4].Text),
new OdbcParameter("@description",textbox_array[5].Text),
new OdbcParameter("@x",textbox_array[6].Text),
new OdbcParameter("@y",textbox_array[7].Text),
new OdbcParameter("@z",textbox_array[8].Text)
};
OdbcConnection conn = new OdbcConnection(connStr);
OdbcCommand cmd = new OdbcCommand(sql, conn);
conn.Open();
cmd.Parameters.AddRange(param);
int n = 0;
try
{
n = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);//显示异常信息
}
if (n == 0)
{
MessageBox.Show("添加失败!");
return;
}
else if (n > 0)
{
MessageBox.Show("添加成功!");
}
conn.Close();
datagridview_refresh();
}
private void button_delete_Click(object sender, EventArgs e)
{
//使用sql删除语句
string sql = "delete from station where 1=1 ";
//如果datagridview的当前行被选中
if (dataGridView1.CurrentRow.Selected)
{
//将sql语句 delete from Record where 1=1 + and Id = + 当前选中行的第0个单元格的号码(即Id号)
sql = sql + "and num=" + Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
}
else return;
OdbcConnection conn = new OdbcConnection(connStr);
OdbcCommand cmd = new OdbcCommand(sql, conn);
int n = 0;
conn.Open();
try
{
n = cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);//显示异常信息
}
if (n == 0)
{
MessageBox.Show("不存在的ID!");
return;
}
else if (n > 0)
{
MessageBox.Show("删除成功!");
}
conn.Close();
datagridview_refresh();
}
private void button_edit_Click(object sender, EventArgs e)
{
//在对数据进行修改之前 对文本框的内容做一下检查, 如果为空 则提示重新输入
if (textbox_array[1].Text.Trim() == "" || textbox_array[2].Text.Trim() == ""
|| textbox_array[3].Text.Trim() == "" || textbox_array[4].Text.Trim() == ""
|| textbox_array[5].Text.Trim() == "" || textbox_array[6].Text.Trim() == ""
|| textbox_array[7].Text.Trim() == "" || textbox_array[8].Text.Trim() == "")
{
MessageBox.Show("文本框的输入不能为空!");
return;
}
//使用SQL update 更新语句
//获取文本框中输入的内容, 通过Id进行更新(Id为当前鼠标点击行的Id)
string sqlUpdate = "update station set ID=?,IP=?,UDP_port=?,name=?,description=?,x=?,y=?,z=?"
+ "where num=" + dataGridView1.CurrentRow.Cells[0].Value.ToString();
OdbcConnection conn = new OdbcConnection(connStr);
OdbcCommand cmd = new OdbcCommand(sqlUpdate, conn);
OdbcParameter[] param ={
new OdbcParameter("@ID",textbox_array[1].Text),
new OdbcParameter("@IP",textbox_array[2].Text),
new OdbcParameter("@UDP_port",textbox_array[3].Text),
new OdbcParameter("@name",textbox_array[4].Text),
new OdbcParameter("@description",textbox_array[5].Text),
new OdbcParameter("@x",textbox_array[6].Text),
new OdbcParameter("@y",textbox_array[7].Text),
new OdbcParameter("@z",textbox_array[8].Text)
};
conn.Open();
cmd.Parameters.AddRange(param);
int n = 0;
try
{
n = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);//显示异常信息
}
if (n == 0)
{
//提示更新失败
MessageBox.Show("更新失败!");
return;// 并且返回
}
else if (n > 0)
{
//否则更新成功
MessageBox.Show("更新成功!");
}
conn.Close();//执行完数据更新操作后 需要关闭数据库 节省资源
datagridview_refresh();//更新完以后 调用刷新方法,将更新后的数据 显示在datagridview上面
}
private void datagridview_refresh()
{
string strSqlQuery = string.Format("select num as 序号,ID as 编号,IP as IP地址,UDP_port as UDP端口号,name as 名称,description as 描述,x as x,y as y,z as z from station");
OdbcConnection conn = new OdbcConnection(connStr);
OdbcDataAdapter da = new OdbcDataAdapter(strSqlQuery, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Refresh();
}
private void button_query_Click(object sender, EventArgs e)
{
datagridview_refresh();
}
}
}
