如何在前端界面操作数据表
一、插入表格并用代码连接数据库
此操作只是单纯读取数据表中的内容,不进行其他操作,如下图:
代码如下:
SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString = "Server=(local);Database=Message;Integrated Security=sspi"; SqlCommand sqlCommand = sqlConnection.CreateCommand(); sqlCommand.CommandText = "select * from PMESSAGE"; sqlConnection.Open(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = sqlCommand; DataSet d = new DataSet(); da.Fill(d, "PMESSAGE"); dataGridView2.DataSource = d; dataGridView2.DataMember = "PMESSAGE"; sqlConnection.Close();
二、实现数据表中下拉框的功能
首先将数据网格视图的指定列的表头文本设为中文,再嵌入下拉框,将不要必要出现的列隐藏起来,如下图:
将数据网格视图的指定列的表头文本设为中文的代码为:
this.dgv_message.Columns["D_ID"].HeaderText = "工号";
连接两张表:
SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString = "Server=(local);Database=Message;Integrated Security=sspi"; SqlCommand sqlCommand = new SqlCommand(); SqlCommand sqlCommand2 = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommand2.Connection = sqlConnection; sqlCommand.CommandText = "SELECT * FROM KESHI;"; sqlCommand2.CommandText = "SELECT * FROM DMESSAGE;"; SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); sqlDataAdapter.SelectCommand = sqlCommand; DataTable keshitable = new DataTable(); SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter(); sqlDataAdapter2.SelectCommand = sqlCommand2; DataTable doctortable = new DataTable(); sqlConnection.Open(); sqlDataAdapter.Fill(keshitable); sqlDataAdapter2.Fill(doctortable); sqlConnection.Close();
嵌入下拉框的内容:
DataGridViewAutoSizeColumnMode.Fill; DataGridViewComboBoxColumn classColumn = new DataGridViewComboBoxColumn(); classColumn.Name = "keshi"; classColumn.HeaderText = "科室"; classColumn.DataSource = keshitable; classColumn.DisplayMember = "Name"; classColumn.ValueMember = "No"; classColumn.DataPropertyName = "KEHSHI"; classColumn.DisplayIndex = 6; classColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; this.dgv_message.Columns.Add(classColumn);
总代码如下:
SqlConnection sqlConnection = new SqlConnection(); //声明并实例化SQL连接; sqlConnection.ConnectionString = "Server=(local);Database=Message;Integrated Security=sspi"; //在字符串变量中,描述连接字符串所需的服务器地址、数据库名称、集成安全性(即是否使用Windows验证); SqlCommand sqlCommand = new SqlCommand(); //声明并实例化SQL命令; SqlCommand sqlCommand2 = new SqlCommand(); //声明并实例化SQL命令; sqlCommand.Connection = sqlConnection; //将SQL命令的连接属性指向SQL连接; sqlCommand2.Connection = sqlConnection; //将SQL命令的连接属性指向SQL连接; sqlCommand.CommandText = "SELECT * FROM KESHI;"; //指定SQL命令的命令文本;该命令查询所有班级,以用作下拉框数据源; sqlCommand2.CommandText = "SELECT * FROM DMESSAGE;"; //指定SQL命令的命令文本;该命令查询所有学生; SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); //声明并实例化SQL数据适配器; sqlDataAdapter.SelectCommand = sqlCommand; //将SQL数据适配器的查询命令属性指向SQL命令; DataTable keshitable = new DataTable(); //声明并实例化数据表,用于保存所有班级,以用作下拉框数据源; SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter(); //声明并实例化SQL数据适配器; sqlDataAdapter2.SelectCommand = sqlCommand2; //将SQL数据适配器的查询命令属性指向SQL命令; DataTable doctortable = new DataTable(); //声明并实例化数据表,用于保存所有学生,以用作数据网格视图的数据源; sqlConnection.Open(); //打开SQL连接; sqlDataAdapter.Fill(keshitable); //SQL数据适配器读取数据,并填充班级数据表; sqlDataAdapter2.Fill(doctortable); //SQL数据适配器读取数据,并填充学生数据表; sqlConnection.Close(); //关闭SQL连接; this.dgv_message.Columns.Clear(); this.dgv_message.DataSource = doctortable; //将数据网格视图的数据源设为学生数据表; this.dgv_message.Columns["D_ID"].HeaderText = "工号"; //将数据网格视图的指定列的表头文本设为中文; this.dgv_message.Columns["DOCTOR"].HeaderText = "姓名"; this.dgv_message.Columns["DENGJI"].HeaderText = "等级"; this.dgv_message.Columns["TEL"].HeaderText = "电话"; this.dgv_message.Columns["ADDRESS"].HeaderText = "地址"; this.dgv_message.Columns["KESHI"].HeaderText = "科室"; this.dgv_message.Columns["DENGJI"].Visible = false; this.dgv_message.Columns["KESHI"].Visible = false; this.dgv_message.Columns["QUESTION"].Visible = false; //将数据网格视图的指定列设为不可见; this.dgv_message.Columns["ANSWER"].Visible = false; this.dgv_message.Columns["PASSWORD"].Visible = false; this.dgv_message.Columns[this.dgv_message.Columns.Count - 1].AutoSizeMode = //数据网格视图的最后一列的自动调整列宽模式设为填充(至数据网格视图右侧边缘); DataGridViewAutoSizeColumnMode.Fill; DataGridViewComboBoxColumn classColumn = new DataGridViewComboBoxColumn(); //声明并实例化数据网格视图下拉框列,用于设置学生的班级; classColumn.Name = "keshi"; //设置下拉框列的名称; classColumn.HeaderText = "科室"; //设置下拉框列的表头文本; classColumn.DataSource = keshitable; //设置下拉框列的数据源为班级数据表; classColumn.DisplayMember = "Name"; //设置下拉框列的显示成员为(班级数据表的)名称(列); classColumn.ValueMember = "No"; //设置下拉框列的值成员为(班级数据表的)编号(列); classColumn.DataPropertyName = "KEHSHI"; //设置下拉框列的数据属性名称为(学生数据表的)班级编号(列); classColumn.DisplayIndex = 6; //设置下拉框列的显示顺序; classColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; //设置下拉框列的自动调整列宽模式为填充; this.dgv_message.Columns.Add(classColumn); //将下拉框列加入数据网格视图的列集合;
三、实现更新功能
实现直接通过在数据表里修改、增加数据的功能,如下图:
代码如下:
SqlConnection sqlConnection = new SqlConnection(); //声明并实例化SQL连接; sqlConnection.ConnectionString = "Server=(local);Database=Message;Integrated Security=sspi"; //在字符串变量中,描述连接字符串所需的服务器地址、数据库名称、集成安全性(即是否使用Windows验证); SqlCommand sqlCommand = new SqlCommand(); //声明并实例化SQL命令; sqlCommand.Connection = sqlConnection; //将SQL命令的连接属性指向SQL连接; sqlCommand.CommandText = //指定SQL命令的命令文本; "UPDATE PMESSAGE" + " SET P_NAME=@P_NAME,SEX=@SEX,DATE=@DATE,MINZU=@MINZU,HUNYIN=@HUNYIN,JOB=@JOB,ADDRESS=@ADDRESS,GUOMIN=@GUOMIN" + " WHERE P_ID=@P_ID;"; sqlCommand.Parameters.Add("@P_NAME",SqlDbType.VarChar,0,"P_NAME"); //向SQL命令的参数集合添加参数的名称、SQL Server数据类型、长度(仅用于定长类型)、所绑定的数据表中的列名; sqlCommand.Parameters.Add("@SEX", SqlDbType.Bit, 0, "SEX"); sqlCommand.Parameters.Add("@DATE", SqlDbType.Date, 0, "DATE"); //sqlCommand.Parameters.Add("@ClassNo", SqlDbType.Int, 0, "ClassNo"); sqlCommand.Parameters.Add("@MINZU", SqlDbType.VarChar, 0, "MINZU"); sqlCommand.Parameters.Add("@HUNYIN", SqlDbType.VarChar, 0, "HUNYIN"); sqlCommand.Parameters.Add("@JOB", SqlDbType.VarChar, 0, "JOB"); sqlCommand.Parameters.Add("@ADDRESS", SqlDbType.VarChar, 0, "ADDRESS"); sqlCommand.Parameters.Add("@GUOMIN", SqlDbType.VarChar, 0, "GUOMIN"); sqlCommand.Parameters.Add("@P_ID", SqlDbType.Char, 6, "P_ID"); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); //声明并实例化SQL数据适配器,同时借助构造函数,将其SelectCommand属性设为先前创建的SQL命令; sqlDataAdapter.UpdateCommand = sqlCommand; //将SQL数据适配器的更新命令属性指向SQL命令; DataTable messagetable = (DataTable)this.dgv_message.DataSource; //声明数据表,并指向数据网格视图的数据源;数据源默认类型为object,还需强制转换类型; sqlConnection.Open(); //打开SQL连接; int rowAffected = sqlDataAdapter.Update(messagetable); //SQL数据适配器根据学生数据表提交更新,并返回受影响行数; sqlConnection.Close(); //关闭SQL连接; MessageBox.Show("更新成功。"); //在消息框显示受影响行数;
四、思维导图