c# 向MySQL添加数据的两种方法
下面介绍两种执行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,List oracleCmdSqlList) { 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(List SQLStringList) { using(MySqlConnectionconn=newMySqlConnection(connectionString)) { conn.Open(); MySqlCommandcmd=newMySqlCommand(); cmd.Connection=conn; MySqlTransactiontx=conn.BeginTransaction(); cmd.Transaction=tx; try { intcount=0; for(intn=0;n 1) { 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(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。