c# 向MySQL添加数据的两种方法-木庄网络博客

本文整理自网络，侵删。

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

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

public class DataBaseMySqlHelper
{
    string connstr;
    MySqlConnection conn;
    //Server=xxxxxxx;Database=xxxxxxx;Uid=xxxxxxx;Pwd=xxxxxxx;CharSet=gbk;
    //Server=xxx.xx.xxx.xx;Database=MSUP;Uid=dvision;Pwd=dvision;Port=xxxx;allow zero datetime = true;
    MainWindow _MainWindow;
 
 
    public DataBaseMySqlHelper(MainWindow mainWindow)
    {
        _MainWindow = mainWindow;
         connstr = _MainWindow._ConfigInfo.MySqlConnectionStrings;        
    }
 
    public DataBaseMySqlHelper(MainWindow mainWindow, string connectionString)
    {
        _MainWindow = mainWindow;
        connstr = connectionString;
    }
 
    /// <summary>
    /// 打开数据库连接
    /// </summary>
    void Open()
    {
        try
        {
            conn = new MySqlConnection(connstr);
            conn.Open();
        }
        catch (Exception ex)
        {
 
            System.Windows.Forms.MessageBox.Show(ex.Message);
        }
         
    }
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    void Close()
    {
        conn.Close();
    }
 
    /// <summary>
    /// 返回影响数据库的行数            
    /// </summary>
    /// <param name="CmdString"></param>
    /// <returns></returns>
    public int ExecuteSQL(string CmdString)
    {
        try
        {
            Open();
            MySqlCommand myCmd = new MySqlCommand(CmdString, conn);
            int Cmd = myCmd.ExecuteNonQuery();
            Close();
            return Cmd;
        }
        catch (Exception ex)
        {
            _MainWindow.ShowErrorMessage(" MySql数据库查询失败!");
            return 0;
        }
    }
 
    /// <summary>
    /// 返回数据表
    /// </summary>
    /// <param name="CmdString"></param>
    /// <returns></returns>
    public DataTable GetDataTable(string CmdString)
    {
        try
        {
            Open();
            DataSet myDs = new DataSet();
            MySqlDataAdapter myDa = new MySqlDataAdapter();
            myDa.SelectCommand = new MySqlCommand(CmdString, conn);
            myDa.Fill(myDs);
            Close();
            return myDs.Tables[0];
        }
        catch (Exception e)
        {
            _MainWindow.ShowErrorMessage(" MySql数据库查询失败!");
            return null;
        }
    }
}

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

/// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(Maticsoft.Model.cameradetail model)
        {
            StringBuilder strSql=new StringBuilder();
            strSql.Append("insert into cameradetail_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 = {
                    new MySqlParameter("@EncodeDeviceUsername", MySqlDbType.VarChar,64),
                    new MySqlParameter("@MatrixIP", MySqlDbType.VarChar,16),
                    new MySqlParameter("@EncodeDevicePassword", MySqlDbType.VarChar,64),
                    new MySqlParameter("@id", MySqlDbType.VarChar,14),
                    new MySqlParameter("@Name", MySqlDbType.VarChar,100),
                    new MySqlParameter("@DisplayName", MySqlDbType.VarChar,100),
                    new MySqlParameter("@Location", MySqlDbType.VarChar,250),
                    new MySqlParameter("@Longitude", MySqlDbType.VarChar,16),
                    new MySqlParameter("@Latitude", MySqlDbType.VarChar,16),
                    new MySqlParameter("@CameraActive", MySqlDbType.Int32,11),
                    new MySqlParameter("@ServerID", MySqlDbType.VarChar,16),
                    new MySqlParameter("@ForwardSvrIP", MySqlDbType.VarChar,16),
                    new MySqlParameter("@ForwardSvrPort", MySqlDbType.VarChar,16),
                    new MySqlParameter("@EncodeDeviceIP", MySqlDbType.VarChar,16),
                    new MySqlParameter("@EncodeDevicePort", MySqlDbType.VarChar,6),
                    new MySqlParameter("@CameraType", MySqlDbType.VarChar,6),
                    new MySqlParameter("@CameraModel", MySqlDbType.VarChar,6),
                    new MySqlParameter("@DeviceChannel", MySqlDbType.VarChar,12),
                    new MySqlParameter("@MatrixPort", MySqlDbType.VarChar,6),
                    new MySqlParameter("@MatrixChannelNum", MySqlDbType.VarChar,14),
                    new MySqlParameter("@VideoStoreServerIP", MySqlDbType.VarChar,16),
                    new MySqlParameter("@VideoStoreServerPort", MySqlDbType.VarChar,6),
                    new MySqlParameter("@VideoStoreServerUserID", MySqlDbType.VarChar,50),
                    new MySqlParameter("@VideoStoreServerPassword", MySqlDbType.VarChar,20),
                    new MySqlParameter("@EncodeDeviceModelNum", MySqlDbType.VarChar,6),
                    new MySqlParameter("@EncodeDeviceModelName", MySqlDbType.VarChar,50),
                    new MySqlParameter("@CameraInstallAddress", MySqlDbType.VarChar,250),
                    new MySqlParameter("@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)，我们这里也贴出相应地源码，并做简要的分析：

阅读剩余部分