C#对数据库操作注意问题总结

                

1．  将对数据库连接的open,close，查询，等常用的操作放在一个类中，供其他类公用。

 

    public CGoble()

        {

            //

            // TODO: 在此处添加构造函数逻辑

            //sPCName="";//以后要改回来?????????????????????????????

            sPCName="M-C6C3J1X";

            GetHostName();//获取服务器名

            //workstation id="M-C6C3J1X";packet size=4096;integrated security=SSPI;initial catalog=AUTOTEST;persist security info=False

            //workstation id="M-C6C3J1X";packet size=4096;integrated security=SSPI;data source="M-C6C3J1X";persist security info=False;initial catalog=AUTOTEST

strConn="workstation id= " + sPCName + " ;packet size=4096;integrated security=SSPI;data source= " + sPCName + " ;persist security info=False; initial catalog=AUTOTEST";

                       

        }

        public void OpenConn(SqlConnection sqlConn)

        {

            if(sqlConn.State==ConnectionState.Closed )

                sqlConn.Open();    

        }

 

        public void CloseConn(SqlConnection sqlConn)

        {

            if(sqlConn.State==ConnectionState.Open  )

                sqlConn.Close();       

        }

 

    public DataSet ReturnTable(String strTable ,String strCommand ,bool IfSQL , DataSet sqlDS)

        {

            SqlCommand myCommand=new SqlCommand();

            SqlDataAdapter sqlAdapter;

            SqlConnection sqlConn=new SqlConnection();

            sqlAdapter=new SqlDataAdapter();

            try

            {

                sqlConn.ConnectionString=strConn;//new SqlConnection(strConn);

                //if(sqlConn.State==ConnectionState.Closed )

                OpenConn(sqlConn);

                myCommand.CommandText =strCommand;

                myCommand.Connection=sqlConn;

                //myCommand=new SqlCommand(strCommand,sqlConn);

                if (IfSQL)

                    myCommand.CommandType = CommandType.Text;

                else

                    myCommand.CommandType = CommandType.StoredProcedure;           

           

                sqlAdapter.TableMappings.Add("Table", strTable);

                sqlAdapter.SelectCommand = myCommand;

           

                sqlAdapter.Fill(sqlDS);

               

            }

            catch(Exception e)

            {

        MessageBox.Show(e.ToString(), "错误",MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            //if(sqlConn.State==ConnectionState.Open)

            CloseConn(sqlConn);            

            sqlAdapter.Dispose();

            myCommand.Dispose();

            sqlConn.Dispose();

            return sqlDS;      

               

        }

 

其他类在查询时，可直接调用：

strComm="select * from DTestType "; theSet=myGoble.ReturnTable("DTestType",strComm,true,theSet); //TypeNo:测试分类更新、

插入：

SqlConnection theConn=new SqlConnection(myGoble.strConn);

            myGoble.OpenConn(theConn);

            m_ParamNo=ParamNo.Text.Trim();

            strComm="insert into DControlParam values ('"+m_ParamNo+"' ,'"+m_TestTypeNo+"','"+m_ParamName+"',"+m_ParamIndex +","+m_ByteNum+","+m_ParamScale+")";

            SqlCommand theComm=new SqlCommand(strComm,theConn);

            theComm.CommandType =CommandType.Text ;

            try

            {

                theComm.ExecuteNonQuery();

                MessageBox.Show("添加成功!!", "信息",MessageBoxButtons.OK, MessageBoxIcon.Information );

                theParamNo=ParamNo.Text.Trim();

               

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.ToString(), "错误",MessageBoxButtons.OK, MessageBoxIcon.Error);

                theParamNo="";

            }

            myGoble.CloseConn(theConn);

            theConn.Dispose();

            theComm.Dispose();

 

修改：

string  strComm;

            if(!CheckInput())

                return;

            SqlConnection theConn=new SqlConnection(goble.strConn);

            goble.OpenConn(theConn);

            strComm="update In_OutputInfo set GroupNo='"+mGroupNo+"',StepNo='"+ mStepNo+"', SubStepNo="+ mSubNo +

                                ",IONo='"+ mIONo +"',IOPriority="+ priority +",FrameNo='"+ mFrameNo +"',ByteIndex='"+ mByteIndex +

                                "',ByteNum='"+ mByteNum +"',IOFlag='"+ mIOFlag +"',DelayTime="+ delayTime +",MaskValue='"+ mMaskValue +

                                "',ExpectValue='"+ mExpectValue +"'where GroupNo='"+ m_GroupNo +"' and StepNo='"+ m_StepNo +"' and SubStepNo="+ m_SubNo ;

            SqlCommand theComm=new SqlCommand(strComm,theConn);

            theComm.CommandType =CommandType.Text ;

            try

            {

                theComm.ExecuteNonQuery();             

                MessageBox.Show("修改成功!!", "信息",MessageBoxButtons.OK, MessageBoxIcon.Information );              

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.ToString(), "错误",MessageBoxButtons.OK, MessageBoxIcon.Error);             

            }

            goble.CloseConn(theConn);

            theConn.Dispose();

            theComm.Dispose();

删除操作：

string  strComm;

            SqlConnection theConn=new SqlConnection(goble.strConn);

            goble.OpenConn(theConn);

            strComm="delete In_OutputInfo where GroupNo='"+ m_GroupNo +"' and StepNo='"+ m_StepNo +"' and SubStepNo="+ m_SubNo ;

            SqlCommand theComm=new SqlCommand(strComm,theConn);

            theComm.CommandType =CommandType.Text ;

            try

            {

                theComm.ExecuteNonQuery();             

                MessageBox.Show("删除成功!!", "信息",MessageBoxButtons.OK, MessageBoxIcon.Information );              

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.ToString(), "错误",MessageBoxButtons.OK, MessageBoxIcon.Error);             

            }

            goble.CloseConn(theConn);

            theConn.Dispose();

            theComm.Dispose();

            ClearTextBox();

 

            strComm="select * from view_io where StepNo='"+ m_StepNo +"' and GroupNo='"+ m_GroupNo +"' order by GroupNo";

            ReturnIOInfo(strComm);

            button3.Enabled=false;

            button4.Enabled=false;

 

在DataGrid中显示数据：

try

            {

                theSet.Tables["IOInfo"].Clear();

            }

            catch

            {

                ;

            }

            theSet=goble.ReturnTable("IOInfo",strComm,true,theSet);

            dataGrid1.DataSource=theSet.Tables["IOInfo"];      

 

            DataGridTableStyle ds = new DataGridTableStyle(true);          

            ds.MappingName ="IOInfo";// myDataTable.TableName;

 

            this.dataGrid1.TableStyles.Clear();

            this.dataGrid1.TableStyles.Add(ds);

 

2．  在每次要通过查询获得更新DataSet时，要先Clear(),否则不能更新。如上所示

3．  两表联合查询：

strComm="select DControlParam.*,ValueDiscrible,ParamValue,ValueID from DControlParam LEFT JOIN ControlValueInfo on ControlValueInfo.ParamNo=DControlParam.ParamNo where DControlParam.ParamNo='"+ParamNo+"' ";