mysql添加记录_关于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;
//;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;
}
///
/// 打开数据库连接
///
void Open()
{
try
{
conn = new MySqlConnection(connstr);
conn.Open();
}
catch (Exception ex)
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
///
/// 关闭数据库连接
///
void Close()
{
conn.Close();
}
///
/// 返回影响数据库的⾏数
///
///
///
public int ExecuteSQL(string CmdString)
{
try
{
Open();
MySqlCommand myCmd = new MySqlCommand(CmdString, conn); int Cmd = myCmd.ExecuteNonQuery();
Close();
return Cmd;
}增加字段的sql语句
catch (Exception ex)
{
_MainWindow.ShowErrorMessage(" MySql数据库查询失败!");
return 0;
}
}
///
/// 返回数据表
///
///
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的内容，
///
/// 增加⼀条数据
///
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,Cam
strSql.Append(" values (");
strSql.Append("@EncodeDeviceUsername,@MatrixIP,@EncodeDevicePassword,@id,@Name,@DisplayName,@Location,@Longitu
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)，我们这⾥也贴出相应地源码，并做简要的分析：
这个函数⾸先是打开数据库连接，这⾥⾯最重要就是讲cmdParms⾥⾯的参数⼀个个添加到cmd.Parameters中，然后执⾏ int rows = cmd.ExecuteNonQuery()；命令来执⾏相应的操作，这是⼀种决然不同的思路，我们在使⽤的时候可以考虑这两种⽅式来进⾏数据库的插⼊操作！
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
{
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论