VS2008 C# 如何在前端界面操作数据表

操作数据表主要分为更新和查询

更新

更新包括增添、修改、删除三个功能，首先按正常操作敲好三个功能的代码

SqlConnection sqlConnection = new SqlConnection();                                             
            sqlConnection.ConnectionString =
               ConfigurationManager.ConnectionStrings["Sql"].ToString();                           

            SqlCommand insqlCommand = new SqlCommand();                                                      
            insqlCommand.Connection = sqlConnection;                                                         
            insqlCommand.CommandText =                                                                      
                "INSERT tb_药品信息表"
                + " (ypbh ,ypmc,yplx,dj	,sj,dw,pfj,lsj,jyj  ,zdj,kl ,yxq ,cd ,sccj  )	"
                + " VALUES(@No,@ypmc ,@yplx ,@dj ,@sj ,@dw,@pfj,@lsj ,@jyj,@zdj,@kl,@yxq,@cd,@sccj  )";
            insqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 0, "ypmc");                                 
            insqlCommand.Parameters.Add("@yplx", SqlDbType.Bit, 0, "yplx");
            insqlCommand.Parameters.Add("@dj", SqlDbType.Float, 0, "dj");
            insqlCommand.Parameters.Add("@sj", SqlDbType.Float, 0, "sj");
            insqlCommand.Parameters.Add("@dw", SqlDbType.VarChar, 0, "dw");
            insqlCommand.Parameters.Add("@pfj", SqlDbType.Float, 0, "pfj");
            insqlCommand.Parameters.Add("@lsj", SqlDbType.Float, 0, "lsj");
            insqlCommand.Parameters.Add("@jyj", SqlDbType.Float, 0, "jyj");
            insqlCommand.Parameters.Add("@zdj", SqlDbType.Float, 0, "zdj");
            insqlCommand.Parameters.Add("@kl", SqlDbType.Int, 0, "kl");
            insqlCommand.Parameters.Add("@yxq", SqlDbType.Date, 0, "yxq");
            insqlCommand.Parameters.Add("@cd", SqlDbType.VarChar, 0, "cd");
            insqlCommand.Parameters.Add("@sccj", SqlDbType.VarChar, 0, "sccj");
            insqlCommand.Parameters.Add("@No", SqlDbType.Char, 10, "ypbh");

            SqlCommand upsqlCommand = new SqlCommand();                                                      
            upsqlCommand.Connection = sqlConnection;                                                          
            upsqlCommand.CommandText =                                                                        
                "UPDATE tb_药品信息表"
                + " SET [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]"
                + " WHERE [email protected];";
            upsqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 0, "ypmc");                               
            upsqlCommand.Parameters.Add("@yplx", SqlDbType.Bit, 0, "yplx");
            upsqlCommand.Parameters.Add("@dj", SqlDbType.Float, 0, "dj");
            upsqlCommand.Parameters.Add("@sj", SqlDbType.Float, 0, "sj");
            upsqlCommand.Parameters.Add("@dw", SqlDbType.VarChar, 0, "dw");
            upsqlCommand.Parameters.Add("@pfj", SqlDbType.Float, 0, "pfj");
            upsqlCommand.Parameters.Add("@lsj", SqlDbType.Float, 0, "lsj");
            upsqlCommand.Parameters.Add("@jyj", SqlDbType.Float, 0, "jyj");
            upsqlCommand.Parameters.Add("@zdj", SqlDbType.Float, 0, "zdj");  
            upsqlCommand.Parameters.Add("@kl", SqlDbType.Int, 0, "kl");
            upsqlCommand.Parameters.Add("@yxq", SqlDbType.Date, 0, "yxq");
            upsqlCommand.Parameters.Add("@cd", SqlDbType.VarChar, 0, "cd");
            upsqlCommand.Parameters.Add("@sccj", SqlDbType.VarChar, 0, "sccj");
            upsqlCommand.Parameters.Add("@NewNo", SqlDbType.Char, 10, "ypbh");
            upsqlCommand.Parameters.Add("@OldNo", SqlDbType.Char, 10, "ypbh");
            upsqlCommand.Parameters["@OldNo"].SourceVersion = DataRowVersion.Original;

            SqlCommand desqlCommand = new SqlCommand();                                                    
            desqlCommand.Connection = sqlConnection;                                                     
            desqlCommand.CommandText =                                                                    
                "DELETE tb_药品信息表"
                + " WHERE [email protected];";
            desqlCommand.Parameters.Add("@No", SqlDbType.Char, 10, "ypbh");

需要注意的是，三个功能的命令名称互不相同，用以区分
接下来，创建一个新的SqlDataAdapter类，SqlDataAdapter类一边是功能，另一边是数据表，概念如下图所示

SqlDataAdapter类提供了三个属性，分别对应更新功能的更改、增加、删除，只要将刚才敲好的三个功能的命令名称分别对接，再把SqlDataAdapter类的另一边接上DataGridView控件即可，对接的代码为

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();     
            sqlDataAdapter.UpdateCommand = upsqlCommand;  
            sqlDataAdapter.InsertCommand = insqlCommand;
            sqlDataAdapter.DeleteCommand = desqlCommand;
            DataTable studentTable = (DataTable)this.dgv_drug.DataSource; 

至此，DataGridView控件的更新功能完成

查询

查询功能相对简单，首先，我在载入数据时，定义了一个静态的DataTable数据表来保存表

private  DataTable Table ;
......
this.Table = new DataTable();
            sqlConnection.Open();                                                                         
            sqlDataAdapter.Fill(Table);
            sqlConnection.Close();

这张表可以全局使用，接着，在查询按钮的代码下，使用一个用于查询的DataRow类，用于查询DataTable表中的一行数据，代码如下

DataRow[] searchResultRows =
               this.Table.Select("ypmc LIKE '%" + this.textBox_Seach.Text.Trim() + "%'");            
                DataTable searchTable = this.Table.Clone();                                         
                foreach (DataRow row in searchResultRows)                                                       
                {
                    searchTable.ImportRow(row);                                                         
                }
                this.dgv_drug.DataSource = searchTable;  

即可以实现查询功能