110,537
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Management;
namespace notebook
{
public partial class Form2 : Form
{
public string dongzuo = "查询记录";
string dongzuocode = "";
public Form2()
{
InitializeComponent();
comboBox1.SelectedIndex = 0;
}
private void 導入ToolStripMenuItem_Click(object sender, EventArgs e)
{
}
#region 重置按钮事件
private void button2_Click(object sender, EventArgs e)
{
dongzuo = comboBox1.SelectedText;
zichanbianhaotB.ResetText();
guanzhibianmatB.ResetText();
shebeixinghaotB.ResetText();
loudongloucengtB.ResetText();
jifangtB.ResetText();
jifangtB.ResetText();
MACtB.ResetText();
iptB.ResetText();
sntB.ResetText();
beizhutB.ResetText();
baoguanrentB.ResetText();
shiyequntB.ResetText();
shujuxierutimetB.ResetText();
zuihouxiugaitimetB.ResetText();
comboBox1.SelectedText = "查询数据";
}
#endregion
private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellEventArgs e)
{
zichanbianhaotB.Text=dataGridView1.CurrentRow.Cells.ToString();
}
private void button1_Click(object sender, EventArgs e)
{
dongzuo = comboBox1.SelectedItem.ToString();
#region 数据库连接
string str = "Data Source=10.244.170.110;Database=NetAssetManagement; User ID=netadmin; Password=chinamisadmin; pooling=false ";
DataSet ds=new DataSet();
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("", con);
cmd.CommandType=CommandType.Text;
SqlDataAdapter adpt = new SqlDataAdapter(cmd);
//SqlParameter [] para=new SqlParameter[15];
#endregion
string sysnumber = "";
string zichanbianhao = "";
string guanzhibianhao = "";
string xinghao = "";
string loudong = "";
string jifang = "";
string jigui = "";
string ipaddress = "";
string mac = "";
string sn = "";
string beizhu = "";
string baoguanren = "";
string shiyechu = "";
string shujuxierutime = "";
string zuihouxiugaitime = "";
sysnumber = sysnumbervalue.Text.Trim();
zichanbianhao = zichanbianhaotB.Text.Trim();
guanzhibianhao = guanzhibianmatB.Text.Trim();
xinghao = shebeixinghaotB.Text.Trim();
loudong = loudongloucengtB.Text.Trim();
jifang = jifangtB.Text.Trim();
jigui = jiguitB.Text.Trim();
mac = MACtB.Text.Trim();
ipaddress = iptB.Text.Trim();
sn = sntB.Text.Trim();
beizhu = beizhutB.Text.Trim();
shiyechu = shiyequntB.Text.Trim();
shujuxierutime = shujuxierutimetB.Text.Trim();
zuihouxiugaitime = zuihouxiugaitimetB.Text.Trim();
//定义两个数组来分别存储数据库表字段和对应从页面上获取到的值
string[] fieldName = { "资产编号", "管制编号", "型号", "楼栋", "机房", "机柜", "MAC地址", "IP地址", "SN", "事业处", "备注", "资产保管人" };
string[] fieldValue = { zichanbianhao, guanzhibianhao, xinghao, loudong, jifang, jigui, mac, ipaddress, sn, shiyechu, beizhu, baoguanren };
#region 判断执行代码
switch (dongzuo)
{
case "查询记录":
// 因数据库写入的数据含有繁体中文,所以查询的值中含有中文的需要在查询的值前加上大写的N
dongzuocode = "select 序号,资产编号,管制编号,型号,楼栋,机房,机柜,MAC地址,IP地址,SN,事业处,备注,资产保管人 from netadmin.newnetworkzichan where ";
for (int i = 0; i < fieldValue.Length; i++)
{
if (!string.IsNullOrEmpty(fieldValue[i]))
{
dongzuocode += (fieldName[i] + " like'%" + fieldValue[i] + "%' and ");
}
}
string abc = dongzuocode.Substring(0, dongzuocode.Length);
string bc = dongzuocode.Substring(0, dongzuocode.Length - 5);
dongzuocode = dongzuocode.Substring(0, dongzuocode.Length - 5)+ " order by 楼栋,机房,机柜";
//连接字符串中还有空格以使生成的SQL代码正确且可执行。
break;
case "新增记录"://新增资产时,可以插入空值。所以SQL插入代码生成的循环可以直接连接一起来。
//dongzuocode = "insert into netadmin.newnetworkzichan (资产编号,管制编号,型号,楼栋,机房,机柜,MAC地址,IP地址,SN,事业处,备注,资产保管人) VALUES ('" + zichanbianhao + "','" + guanzhibianhao + "','" + xinghao + "','" + loudong + "','" + jifang + "','" + jigui + "','" + mac + "','" + ipaddress + "','" + sn + "','" + shiyechu + "','" + beizhu + "','" + baoguanren + "');";
dongzuocode = "insert into networkzichan ";
for (int i = 0; i < fieldValue.Length; i++)
{
dongzuocode += (fieldName[i] + "='" + fieldValue[i] + "',");
}
break;
case "更新记录"://更新记录时候我们最好获取序列号来定位需要更新的记录,然后判断直接用循环组合所有的数据来更新数据,这样可以避免像更新某个栏位为空时不好操作。
dongzuocode = "update networkzichan set ";
//利用for循环动态生成更新语句。只更新有值的,没有值的就保持原来的值不变。
for (int i = 0; i < fieldValue.Length; i++)
{
if (!string.IsNullOrEmpty(fieldValue[i]))
{
dongzuocode += (fieldName[i] + "='" + fieldValue[i] + "',");
}
}
break;
case "删除记录"://从左侧列表中获取数据的ID值,通过指定ID值来删除记录,从而限制只能一条一条的删除数据,批量删除数据后面批处理的时候再开发。
dongzuocode = "drop * from netadmin.newnetworkzichan where 序号="+sysnumber;
break;
case "导出记录":
//当单击导出按钮时,先盘点datagride
dongzuocode = "";
break;
}
#endregion
cmd.CommandText = dongzuocode;
try
{
con = new SqlConnection(str);
con.Open();
adpt.Fill(ds);
}
catch (Exception ex)
{
string err = ex.Message;
}
finally
{
con.Close();
}
DataTable dt=new DataTable();
if (ds != null)
{
dt = ds.Tables[0];
}
dataGridView1.DataSource = dt;
int a;
a=dataGridView1.RowCount;
chaxunjieguotiaoshu.Text = a.ToString()+"条数据";//统计查询结果中一共有多少条记录
benjiyonghulB.Text = "本机登录账户:" + System.Environment.UserName.ToUpper();//获取系统当前登录的用户名
#region 获取本机MAC地址
string strMac = string.Empty;
ManagementClass mc = new ManagementClass("Win32_NetworkAdapterConfiguration");
ManagementObjectCollection moc = mc.GetInstances();
foreach (ManagementObject mo in moc)
{
if ((bool)mo["IPEnabled"] == true)
{
strMac += mo["MacAddress"].ToString() + " ";
}
}
#endregion
macxianshilB.Text = "本机MAC:"+strMac;//显示本机MAC地址
}
private void Form2_FormClosing(object sender, FormClosingEventArgs e)
{
Application.Exit();//关闭并退出程序
}
}
}
string abc = dongzuocode.Substring(0, dongzuocode.Length);
string bc = dongzuocode.Substring(0, dongzuocode.Length - 5);
dongzuocode = dongzuocode.Substring(0, dongzuocode.Length - 5)+ " order by 楼栋,机房,机柜";
case "查询记录":
// 因数据库写入的数据含有繁体中文,所以查询的值中含有中文的需要在查询的值前加上大写的N
dongzuocode = "select 序号,资产编号,管制编号,型号,楼栋,机房,机柜,MAC地址,IP地址,SN,事业处,备注,资产保管人 from netadmin.newnetworkzichan";
//WHERE条件集合
var whereStr = new List<string>();
for (int i = 0; i < fieldValue.Length; i++)
{
if (!string.IsNullOrEmpty(fieldValue[i]))
{
whereStr.Add(fieldName[i] + " like'%" + fieldValue[i] + "%'");
}
}
//string abc = dongzuocode.Substring(0, dongzuocode.Length);
//string bc = dongzuocode.Substring(0, dongzuocode.Length - 5);
//使用String的Join函数去拼接条件(空格建议在拼接时加,不要在原语句添加)
dongzuocode = dongzuocode + " WHERE" + String.Join(" AND ",whereStr) + " order by 楼栋,机房,机柜";
//连接字符串中还有空格以使生成的SQL代码正确且可执行。
break;
或者可以使用这种对你改动不大的写法:
//去除尾部空格,这样截取5个字节就行了
var dongzuocodeTemp = dongzuocode.TrimEnd();
string abc = dongzuocodeTemp.Substring(0, dongzuocodeTemp.Length);
string bc = dongzuocodeTemp.Substring(0, dongzuocodeTemp.Length - 5);
dongzuocode = dongzuocodeTemp.Substring(0, dongzuocodeTemp.Length - 5)+ " order by 楼栋,机房,机柜";