c# 向MySQL添加数据的两种方法

2023-07-04 23:17:03 287

下面介绍两种执行SQL命令的方法，并作出相应地总结，第一种介绍一种常规用法，下面进行做简要地分析，首先我们需要执行打开数据库操作首先创建一个MySqlConnection对象，在其构造函数中传入一个连接字符串，然后执行Open操作打开数据库，在正确打开数据库之后我们才能进行相关的动作，在ExecuteSQL这个函数中，

我们执行MySqlCommandmyCmd=newMySqlCommand(CmdString,conn)，从而创建MySqlCommand对象，其中传入的两个参数分别为sql命令和第一步建立的MySqlConnection对象，然后执行intCmd=myCmd.ExecuteNonQuery()这一句执行相应的命令，并返回受影响的行数，最后我们需要关闭数据库连接，并释放非托管资源，从而完成整个数据库操作的过程;这个是比较常规的一种做法，也是我们使用比较多的一种形式。

publicclassDataBaseMySqlHelper
{
    stringconnstr;
    MySqlConnectionconn;
    //Server=xxxxxxx;Database=xxxxxxx;Uid=xxxxxxx;Pwd=xxxxxxx;CharSet=gbk;
    //Server=xxx.xx.xxx.xx;Database=MSUP;Uid=dvision;Pwd=dvision;Port=xxxx;allowzerodatetime=true;
    MainWindow_MainWindow;
 
 
    publicDataBaseMySqlHelper(MainWindowmainWindow)
    {
        _MainWindow=mainWindow;
         connstr=_MainWindow._ConfigInfo.MySqlConnectionStrings;        
    }
 
    publicDataBaseMySqlHelper(MainWindowmainWindow,stringconnectionString)
    {
        _MainWindow=mainWindow;
        connstr=connectionString;
    }
 
    ///
    ///打开数据库连接
    ///
    voidOpen()
    {
        try
        {
            conn=newMySqlConnection(connstr);
            conn.Open();
        }
        catch(Exceptionex)
        {
 
            System.Windows.Forms.MessageBox.Show(ex.Message);
        }
         
    }
    ///
    ///关闭数据库连接
    ///
    voidClose()
    {
        conn.Close();
    }
 
    ///
    ///返回影响数据库的行数            
    ///
    ///
    ///
    publicintExecuteSQL(stringCmdString)
    {
        try
        {
            Open();
            MySqlCommandmyCmd=newMySqlCommand(CmdString,conn);
            intCmd=myCmd.ExecuteNonQuery();
            Close();
            returnCmd;
        }
        catch(Exceptionex)
        {
            _MainWindow.ShowErrorMessage("MySql数据库查询失败!");
            return0;
        }
    }
 
    ///
    ///返回数据表
    ///
    ///
    ///
    publicDataTableGetDataTable(stringCmdString)
    {
        try
        {
            Open();
            DataSetmyDs=newDataSet();
            MySqlDataAdaptermyDa=newMySqlDataAdapter();
            myDa.SelectCommand=newMySqlCommand(CmdString,conn);
            myDa.Fill(myDs);
            Close();
            returnmyDs.Tables[0];
        }
        catch(Exceptione)
        {
            _MainWindow.ShowErrorMessage("MySql数据库查询失败!");
            returnnull;
        }
    }
}

第二种方式，这里也贴出关键代码并做简要的分析：这里的关键是MySqlParameter[]数组的使用，我们在执行SQL语句的时候DbHelperMySQL.ExecuteSql(strSql.ToString(),parameters);这里的parameters就是MySqlParameter[]数组，里面存储的是每个字段的数据，我们将在下面的代码中展示parameters的内容，

///
        ///增加一条数据
        ///
        publicvoidAdd(Maticsoft.Model.cameradetailmodel)
        {
            StringBuilderstrSql=newStringBuilder();
            strSql.Append("insertintocameradetail_gis(");
            strSql.Append("EncodeDeviceUsername,MatrixIP,EncodeDevicePassword,id,Name,DisplayName,Location,Longitude,Latitude,CameraActive,ServerID,ForwardSvrIP,ForwardSvrPort,EncodeDeviceIP,EncodeDevicePort,CameraType,CameraModel,DeviceChannel,MatrixPort,MatrixChannelNum,VideoStoreServerIP,VideoStoreServerPort,VideoStoreServerUserID,VideoStoreServerPassword,EncodeDeviceModelNum,EncodeDeviceModelName,CameraInstallAddress,CameraStatus)");
            strSql.Append("values(");
            strSql.Append("@EncodeDeviceUsername,@MatrixIP,@EncodeDevicePassword,@id,@Name,@DisplayName,@Location,@Longitude,@Latitude,@CameraActive,@ServerID,@ForwardSvrIP,@ForwardSvrPort,@EncodeDeviceIP,@EncodeDevicePort,@CameraType,@CameraModel,@DeviceChannel,@MatrixPort,@MatrixChannelNum,@VideoStoreServerIP,@VideoStoreServerPort,@VideoStoreServerUserID,@VideoStoreServerPassword,@EncodeDeviceModelNum,@EncodeDeviceModelName,@CameraInstallAddress,@CameraStatus)");
            MySqlParameter[]parameters={
                    newMySqlParameter("@EncodeDeviceUsername",MySqlDbType.VarChar,64),
                    newMySqlParameter("@MatrixIP",MySqlDbType.VarChar,16),
                    newMySqlParameter("@EncodeDevicePassword",MySqlDbType.VarChar,64),
                    newMySqlParameter("@id",MySqlDbType.VarChar,14),
                    newMySqlParameter("@Name",MySqlDbType.VarChar,100),
                    newMySqlParameter("@DisplayName",MySqlDbType.VarChar,100),
                    newMySqlParameter("@Location",MySqlDbType.VarChar,250),
                    newMySqlParameter("@Longitude",MySqlDbType.VarChar,16),
                    newMySqlParameter("@Latitude",MySqlDbType.VarChar,16),
                    newMySqlParameter("@CameraActive",MySqlDbType.Int32,11),
                    newMySqlParameter("@ServerID",MySqlDbType.VarChar,16),
                    newMySqlParameter("@ForwardSvrIP",MySqlDbType.VarChar,16),
                    newMySqlParameter("@ForwardSvrPort",MySqlDbType.VarChar,16),
                    newMySqlParameter("@EncodeDeviceIP",MySqlDbType.VarChar,16),
                    newMySqlParameter("@EncodeDevicePort",MySqlDbType.VarChar,6),
                    newMySqlParameter("@CameraType",MySqlDbType.VarChar,6),
                    newMySqlParameter("@CameraModel",MySqlDbType.VarChar,6),
                    newMySqlParameter("@DeviceChannel",MySqlDbType.VarChar,12),
                    newMySqlParameter("@MatrixPort",MySqlDbType.VarChar,6),
                    newMySqlParameter("@MatrixChannelNum",MySqlDbType.VarChar,14),
                    newMySqlParameter("@VideoStoreServerIP",MySqlDbType.VarChar,16),
                    newMySqlParameter("@VideoStoreServerPort",MySqlDbType.VarChar,6),
                    newMySqlParameter("@VideoStoreServerUserID",MySqlDbType.VarChar,50),
                    newMySqlParameter("@VideoStoreServerPassword",MySqlDbType.VarChar,20),
                    newMySqlParameter("@EncodeDeviceModelNum",MySqlDbType.VarChar,6),
                    newMySqlParameter("@EncodeDeviceModelName",MySqlDbType.VarChar,50),
                    newMySqlParameter("@CameraInstallAddress",MySqlDbType.VarChar,250),
                    newMySqlParameter("@CameraStatus",MySqlDbType.Int32,11)};
            parameters[0].Value=model.EncodeDeviceUsername;
            parameters[1].Value=model.MatrixIP;
            parameters[2].Value=model.EncodeDevicePassword;
            parameters[3].Value=model.id;
            parameters[4].Value=model.Name;
            parameters[5].Value=model.DisplayName;
            parameters[6].Value=model.Location;
            parameters[7].Value=model.Longitude;
            parameters[8].Value=model.Latitude;
            parameters[9].Value=model.CameraActive;
            parameters[10].Value=model.ServerID;
            parameters[11].Value=model.ForwardSvrIP;
            parameters[12].Value=model.ForwardSvrPort;
            parameters[13].Value=model.EncodeDeviceIP;
            parameters[14].Value=model.EncodeDevicePort;
            parameters[15].Value=model.CameraType;
            parameters[16].Value=model.CameraModel;
            parameters[17].Value=model.DeviceChannel;
            parameters[18].Value=model.MatrixPort;
            parameters[19].Value=model.MatrixChannelNum;
            parameters[20].Value=model.VideoStoreServerIP;
            parameters[21].Value=model.VideoStoreServerPort;
            parameters[22].Value=model.VideoStoreServerUserID;
            parameters[23].Value=model.VideoStoreServerPassword;
            parameters[24].Value=model.EncodeDeviceModelNum;
            parameters[25].Value=model.EncodeDeviceModelName;
            parameters[26].Value=model.CameraInstallAddress;
            parameters[27].Value=model.CameraStatus;
 
            DbHelperMySQL.ExecuteSql(strSql.ToString(),parameters);
        }

这里我们重点来关注DbHelperMySQL.ExecuteSql(strSql.ToString(),parameters)这个函数，函数的源码如下：

这里面有一个重要的函数PrepareCommand(cmd,connection,null,SQLString,cmdParms)，我们这里也贴出相应地源码，并做简要的分析：

这个函数首先是打开数据库连接，这里面最重要就是讲cmdParms里面的参数一个个添加到cmd.Parameters中，然后执行 introws=cmd.ExecuteNonQuery();命令来执行相应的操作，这是一种决然不同的思路，我们在使用的时候可以考虑这两种方式来进行数据库的插入操作！

privatestaticvoidPrepareCommand(MySqlCommandcmd,MySqlConnectionconn,MySqlTransactiontrans,stringcmdText,MySqlParameter[]cmdParms)
        {
            if(conn.State!=ConnectionState.Open)
                conn.Open();
            cmd.Connection=conn;
            cmd.CommandText=cmdText;
            if(trans!=null)
                cmd.Transaction=trans;
            cmd.CommandType=CommandType.Text;//cmdType;
            if(cmdParms!=null)
            {
                foreach(MySqlParameterparameterincmdParms)
                {
                    if((parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)&&
                        (parameter.Value==null))
                    {
                        parameter.Value=DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
 
        #endregion

///
        ///执行SQL语句，返回影响的记录数
        ///
        ///SQL语句
        ///影响的记录数
        publicstaticintExecuteSql(stringSQLString,paramsMySqlParameter[]cmdParms)
        {
            using(MySqlConnectionconnection=newMySqlConnection(connectionString))
            {
                using(MySqlCommandcmd=newMySqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd,connection,null,SQLString,cmdParms);
                        introws=cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        returnrows;
                    }
                    catch(MySql.Data.MySqlClient.MySqlExceptione)
                    {
                        throwe;
                    }
                }
            }
        }

#region 执行简单SQL语句
 
     ///
     ///执行SQL语句，返回影响的记录数
     ///
     ///SQL语句
     ///影响的记录数
     publicstaticintExecuteSql(stringSQLString)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             using(MySqlCommandcmd=newMySqlCommand(SQLString,connection))
             {
                 try
                 {
                     connection.Open();
                     introws=cmd.ExecuteNonQuery();
                     returnrows;
                 }
                 catch(MySql.Data.MySqlClient.MySqlExceptione)
                 {
                     connection.Close();
                     throwe;
                 }
             }
         }
     }
 
     publicstaticintExecuteSqlByTime(stringSQLString,intTimes)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             using(MySqlCommandcmd=newMySqlCommand(SQLString,connection))
             {
                 try
                 {
                     connection.Open();
                     cmd.CommandTimeout=Times;
                     introws=cmd.ExecuteNonQuery();
                     returnrows;
                 }
                 catch(MySql.Data.MySqlClient.MySqlExceptione)
                 {
                     connection.Close();
                     throwe;
                 }
             }
         }
     }
    
     ///
     ///执行MySql和Oracle滴混合事务
     ///
     ///SQL命令行列表
     ///Oracle命令行列表
     ///执行结果0-由于SQL造成事务失败-1由于Oracle造成事务失败1-整体事务执行成功
     publicstaticintExecuteSqlTran(Listlist,ListoracleCmdSqlList)
     {
         using(MySqlConnectionconn=newMySqlConnection(connectionString))
         {
             conn.Open();
             MySqlCommandcmd=newMySqlCommand();
             cmd.Connection=conn;
             MySqlTransactiontx=conn.BeginTransaction();
             cmd.Transaction=tx;
             try
             {
                 foreach(CommandInfomyDEinlist)
                 {
                     stringcmdText=myDE.CommandText;
                     MySqlParameter[]cmdParms=(MySqlParameter[])myDE.Parameters;
                     PrepareCommand(cmd,conn,tx,cmdText,cmdParms);
                     if(myDE.EffentNextType==EffentNextType.SolicitationEvent)
                     {
                         if(myDE.CommandText.ToLower().IndexOf("count(")==-1)
                         {
                             tx.Rollback();
                             thrownewException("违背要求"+myDE.CommandText+"必须符合selectcount(..的格式");
                             //return0;
                         }
                         objectobj=cmd.ExecuteScalar();
                         boolisHave=false;
                         if(obj==null&&obj==DBNull.Value)
                         {
                             isHave=false;
                         }
                         isHave=Convert.ToInt32(obj)>0;
                         if(isHave)
                         {
                             //引发事件
                             myDE.OnSolicitationEvent();
                         }
                     }
                     if(myDE.EffentNextType==EffentNextType.WhenHaveContine||myDE.EffentNextType==EffentNextType.WhenNoHaveContine)
                     {
                         if(myDE.CommandText.ToLower().IndexOf("count(")==-1)
                         {
                             tx.Rollback();
                             thrownewException("SQL:违背要求"+myDE.CommandText+"必须符合selectcount(..的格式");
                             //return0;
                         }
 
                         objectobj=cmd.ExecuteScalar();
                         boolisHave=false;
                         if(obj==null&&obj==DBNull.Value)
                         {
                             isHave=false;
                         }
                         isHave=Convert.ToInt32(obj)>0;
 
                         if(myDE.EffentNextType==EffentNextType.WhenHaveContine&&!isHave)
                         {
                             tx.Rollback();
                             thrownewException("SQL:违背要求"+myDE.CommandText+"返回值必须大于0");
                             //return0;
                         }
                         if(myDE.EffentNextType==EffentNextType.WhenNoHaveContine&&isHave)
                         {
                             tx.Rollback();
                             thrownewException("SQL:违背要求"+myDE.CommandText+"返回值必须等于0");
                             //return0;
                         }
                         continue;
                     }
                     intval=cmd.ExecuteNonQuery();
                     if(myDE.EffentNextType==EffentNextType.ExcuteEffectRows&&val==0)
                     {
                         tx.Rollback();
                         thrownewException("SQL:违背要求"+myDE.CommandText+"必须有影响行");
                         //return0;
                     }
                     cmd.Parameters.Clear();
                 }
                 stringoraConnectionString=PubConstant.GetConnectionString("ConnectionStringPPC");
                 boolres=OracleHelper.ExecuteSqlTran(oraConnectionString,oracleCmdSqlList);
                 if(!res)
                 {
                     tx.Rollback();
                     thrownewException("执行失败");
                     //return-1;
                 }
                 tx.Commit();
                 return1;
             }
             catch(MySql.Data.MySqlClient.MySqlExceptione)
             {
                 tx.Rollback();
                 throwe;
             }
             catch(Exceptione)
             {
                 tx.Rollback();
                 throwe;
             }
         }
     }       
     ///
     ///执行多条SQL语句，实现数据库事务。
     ///
     ///多条SQL语句    
     publicstaticintExecuteSqlTran(ListSQLStringList)
     {
         using(MySqlConnectionconn=newMySqlConnection(connectionString))
         {
             conn.Open();
             MySqlCommandcmd=newMySqlCommand();
             cmd.Connection=conn;
             MySqlTransactiontx=conn.BeginTransaction();
             cmd.Transaction=tx;
             try
             {
                 intcount=0;
                 for(intn=0;n1)
                     {
                         cmd.CommandText=strsql;
                         count+=cmd.ExecuteNonQuery();
                     }
                 }
                 tx.Commit();
                 returncount;
             }
             catch
             {
                 tx.Rollback();
                 return0;
             }
         }
     }
 
     ///
     ///执行带一个存储过程参数的的SQL语句。
     ///
     ///SQL语句
     ///参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
     ///影响的记录数
     publicstaticintExecuteSql(stringSQLString,stringcontent)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             MySqlCommandcmd=newMySqlCommand(SQLString,connection);
             MySql.Data.MySqlClient.MySqlParametermyParameter=newMySql.Data.MySqlClient.MySqlParameter("@content",SqlDbType.NText);
             myParameter.Value=content;
             cmd.Parameters.Add(myParameter);
             try
             {
                 connection.Open();
                 introws=cmd.ExecuteNonQuery();
                 returnrows;
             }
             catch(MySql.Data.MySqlClient.MySqlExceptione)
             {
                 throwe;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
     ///
     ///执行带一个存储过程参数的的SQL语句。
     ///
     ///SQL语句
     ///参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
     ///影响的记录数
     publicstaticobjectExecuteSqlGet(stringSQLString,stringcontent)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             MySqlCommandcmd=newMySqlCommand(SQLString,connection);
             MySql.Data.MySqlClient.MySqlParametermyParameter=newMySql.Data.MySqlClient.MySqlParameter("@content",SqlDbType.NText);
             myParameter.Value=content;
             cmd.Parameters.Add(myParameter);
             try
             {
                 connection.Open();
                 objectobj=cmd.ExecuteScalar();
                 if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
                 {
                     returnnull;
                 }
                 else
                 {
                     returnobj;
                 }
             }
             catch(MySql.Data.MySqlClient.MySqlExceptione)
             {
                 throwe;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
     ///
     ///向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
     ///
     ///SQL语句
     ///图像字节,数据库的字段类型为image的情况
     ///影响的记录数
     publicstaticintExecuteSqlInsertImg(stringstrSQL,byte[]fs)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             MySqlCommandcmd=newMySqlCommand(strSQL,connection);
             MySql.Data.MySqlClient.MySqlParametermyParameter=newMySql.Data.MySqlClient.MySqlParameter("@fs",SqlDbType.Image);
             myParameter.Value=fs;
             cmd.Parameters.Add(myParameter);
             try
             {
                 connection.Open();
                 introws=cmd.ExecuteNonQuery();
                 returnrows;
             }
             catch(MySql.Data.MySqlClient.MySqlExceptione)
             {
                 throwe;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
 
     ///
     ///执行一条计算查询结果语句，返回查询结果(object)。
     ///
     ///计算查询结果语句
     ///查询结果（object）
     publicstaticobjectGetSingle(stringSQLString)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             using(MySqlCommandcmd=newMySqlCommand(SQLString,connection))
             {
                 try
                 {
                     connection.Open();
                     objectobj=cmd.ExecuteScalar();
                     if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
                     {
                         returnnull;
                     }
                     else
                     {
                         returnobj;
                     }
                 }
                 catch(MySql.Data.MySqlClient.MySqlExceptione)
                 {
                     connection.Close();
                     throwe;
                 }
             }
         }
     }
     publicstaticobjectGetSingle(stringSQLString,intTimes)
     {
         using(MySqlConnectionconnection=newMySqlConnection(connectionString))
         {
             using(MySqlCommandcmd=newMySqlCommand(SQLString,connection))
             {
                 try
                 {
                     connection.Open();
                     cmd.CommandTimeout=Times;
                     objectobj=cmd.ExecuteScalar();
                     if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
                     {
                         returnnull;
                     }
                     else
                     {
                         returnobj;
                     }
                 }
                 catch(MySql.Data.MySqlClient.MySqlExceptione)
                 {
                     connection.Close();
                     throwe;
                 }
             }
         }
     }
     ///
     ///执行查询语句，返回MySqlDataReader(注意：调用该方法后，一定要对MySqlDataReader进行Close)
     ///
     ///查询语句
     ///MySqlDataReader
     publicstaticMySqlDataReaderExecuteReader(stringstrSQL)
     {
         MySqlConnectionconnection=newMySqlConnection(connectionString);
         MySqlCommandcmd=newMySqlCommand(strSQL,connection);
         try
         {
             connection.Open();
             MySqlDataReadermyReader=cmd.ExecuteReader(CommandBehavior.CloseCon		声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

c# 向MySQL添加数据的两种方法

热门推荐

随机推荐