mysql中怎么将数据添加到指定的数据库中

发布网友发布时间：2022-04-08 04:48

共2个回答

懂视网时间：2022-04-08 09:09

下面介绍两种执行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)，我们这里也贴出相应地源码，并做简要的分析：

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

private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, 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 (MySqlParameter parameter in cmdParms)
  {
   if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
   (parameter.Value == null))
   {
   parameter.Value = DBNull.Value;
   }
   cmd.Parameters.Add(parameter);
  }
  }
 }

 #endregion

/// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand())
  {
   try
   {
   PrepareCommand(cmd, connection, null, SQLString, cmdParms);
   int rows = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return rows;
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   throw e;
   }
  }
  }
 }

 #region 执行简单SQL语句

 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
  {
   try
   {
   connection.Open();
   int rows = cmd.ExecuteNonQuery();
   return rows;
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   connection.Close();
   throw e;
   }
  }
  }
 }

 public static int ExecuteSqlByTime(string SQLString, int Times)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
  {
   try
   {
   connection.Open();
   cmd.CommandTimeout = Times;
   int rows = cmd.ExecuteNonQuery();
   return rows;
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   connection.Close();
   throw e;
   }
  }
  }
 }
 
 /// <summary>
 /// 执行MySql和Oracle滴混合事务
 /// </summary>
 /// <param name="list">SQL命令行列表</param>
 /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
 public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
 {
  using (MySqlConnection conn = new MySqlConnection(connectionString))
  {
  conn.Open();
  MySqlCommand cmd = new MySqlCommand();
  cmd.Connection = conn;
  MySqlTransaction tx = conn.BeginTransaction();
  cmd.Transaction = tx;
  try
  {
   foreach (CommandInfo myDE in list)
   {
   string cmdText = 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();
    throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
    //return 0;
    }
    object obj = cmd.ExecuteScalar();
    bool isHave = 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();
    throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
    //return 0;
    }

    object obj = cmd.ExecuteScalar();
    bool isHave = false;
    if (obj == null && obj == DBNull.Value)
    {
    isHave = false;
    }
    isHave = Convert.ToInt32(obj) > 0;

    if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
    {
    tx.Rollback();
    throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
    //return 0;
    }
    if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
    {
    tx.Rollback();
    throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
    //return 0;
    }
    continue;
   }
   int val = cmd.ExecuteNonQuery();
   if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
   {
    tx.Rollback();
    throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
    //return 0;
   }
   cmd.Parameters.Clear();
   }
   string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
   bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
   if (!res)
   {
   tx.Rollback();
   throw new Exception("执行失败");
   // return -1;
   }
   tx.Commit();
   return 1;
  }
  catch (MySql.Data.MySqlClient.MySqlException e)
  {
   tx.Rollback();
   throw e;
  }
  catch (Exception e)
  {
   tx.Rollback();
   throw e;
  }
  }
 } 
 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param>		
 public static int ExecuteSqlTran(List<String> SQLStringList)
 {
  using (MySqlConnection conn = new MySqlConnection(connectionString))
  {
  conn.Open();
  MySqlCommand cmd = new MySqlCommand();
  cmd.Connection = conn;
  MySqlTransaction tx = conn.BeginTransaction();
  cmd.Transaction = tx;
  try
  {
   int count = 0;
   for (int n = 0; n < SQLStringList.Count; n++)
   {
   string strsql = SQLStringList[n];
   if (strsql.Trim().Length > 1)
   {
    cmd.CommandText = strsql;
    count += cmd.ExecuteNonQuery();
   }
   }
   tx.Commit();
   return count;
  }
  catch
  {
   tx.Rollback();
   return 0;
  }
  }
 }

 /// <summary>
 /// 执行带一个存储过程参数的的SQL语句。
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString, string content)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  MySqlCommand cmd = new MySqlCommand(SQLString, connection);
  MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
  myParameter.Value = content;
  cmd.Parameters.Add(myParameter);
  try
  {
   connection.Open();
   int rows = cmd.ExecuteNonQuery();
   return rows;
  }
  catch (MySql.Data.MySqlClient.MySqlException e)
  {
   throw e;
  }
  finally
  {
   cmd.Dispose();
   connection.Close();
  }
  }
 }
 /// <summary>
 /// 执行带一个存储过程参数的的SQL语句。
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
 /// <returns>影响的记录数</returns>
 public static object ExecuteSqlGet(string SQLString, string content)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  MySqlCommand cmd = new MySqlCommand(SQLString, connection);
  MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
  myParameter.Value = content;
  cmd.Parameters.Add(myParameter);
  try
  {
   connection.Open();
   object obj = cmd.ExecuteScalar();
   if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
   {
   return null;
   }
   else
   {
   return obj;
   }
  }
  catch (MySql.Data.MySqlClient.MySqlException e)
  {
   throw e;
  }
  finally
  {
   cmd.Dispose();
   connection.Close();
  }
  }
 }
 /// <summary>
 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
 /// </summary>
 /// <param name="strSQL">SQL语句</param>
 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  MySqlCommand cmd = new MySqlCommand(strSQL, connection);
  MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
  myParameter.Value = fs;
  cmd.Parameters.Add(myParameter);
  try
  {
   connection.Open();
   int rows = cmd.ExecuteNonQuery();
   return rows;
  }
  catch (MySql.Data.MySqlClient.MySqlException e)
  {
   throw e;
  }
  finally
  {
   cmd.Dispose();
   connection.Close();
  }
  }
 }

 /// <summary>
 ///执行一条计算查询结果语句，返回查询结果(object)。
 /// </summary>
 ///<param name="SQLString">计算查询结果语句</param>
 ///<returns>查询结果（object）</returns>
 public static object GetSingle(string SQLString)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
  {
   try
   {
   connection.Open();
   object obj = cmd.ExecuteScalar();
   if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
   {
    return null;
   }
   else
   {
    return obj;
   }
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   connection.Close();
   throw e;
   }
  }
  }
 }
 public static object GetSingle(string SQLString, int Times)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
  {
   try
   {
   connection.Open();
   cmd.CommandTimeout = Times;
   object obj = cmd.ExecuteScalar();
   if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
   {
    return null;
   }
   else
   {
    return obj;
   }
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   connection.Close();
   throw e;
   }
  }
  }
 }
 /// <summary>
 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
 /// </summary>
 /// <param name="strSQL">查询语句</param>
 /// <returns>MySqlDataReader</returns>
 public static MySqlDataReader ExecuteReader(string strSQL)
 {
  MySqlConnection connection = new MySqlConnection(connectionString);
  MySqlCommand cmd = new MySqlCommand(strSQL, connection);
  try
  {
  connection.Open();
  MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  return myReader;
  }
  catch (MySql.Data.MySqlClient.MySqlException e)
  {
  throw e;
  } 

 }
 /// <summary>
 /// 执行查询语句，返回DataSet
 /// </summary>
 /// <param name="SQLString">查询语句</param>
 /// <returns>DataSet</returns>
 public static DataSet Query(string SQLString)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  DataSet ds = new DataSet();
  try
  {
   connection.Open();
   MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
   command.Fill(ds, "ds");
  }
  catch (MySql.Data.MySqlClient.MySqlException ex)
  {
   throw new Exception(ex.Message);
  }
  return ds;
  }
 }
 public static DataSet Query(string SQLString, int Times)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  DataSet ds = new DataSet();
  try
  {
   connection.Open();
   MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
   command.SelectCommand.CommandTimeout = Times;
   command.Fill(ds, "ds");
  }
  catch (MySql.Data.MySqlClient.MySqlException ex)
  {
   throw new Exception(ex.Message);
  }
  return ds;
  }
 }



 #endregion

 #region 执行带参数的SQL语句

 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand())
  {
   try
   {
   PrepareCommand(cmd, connection, null, SQLString, cmdParms);
   int rows = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return rows;
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   throw e;
   }
  }
  }
 }


 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
 public static void ExecuteSqlTran(Hashtable SQLStringList)
 {
  using (MySqlConnection conn = new MySqlConnection(connectionString))
  {
  conn.Open();
  using (MySqlTransaction trans = conn.BeginTransaction())
  {
   MySqlCommand cmd = new MySqlCommand();
   try
   {
   //循环
   foreach (DictionaryEntry myDE in SQLStringList)
   {
    string cmdText = myDE.Key.ToString();
    MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
    int val = cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
   }
   trans.Commit();
   }
   catch
   {
   trans.Rollback();
   throw;
   }
  }
  }
 }
 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
 public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
 {
  using (MySqlConnection conn = new MySqlConnection(connectionString))
  {
  conn.Open();
  using (MySqlTransaction trans = conn.BeginTransaction())
  {
   MySqlCommand cmd = new MySqlCommand();
   try
   { int count = 0;
   //循环
   foreach (CommandInfo myDE in cmdList)
   {
    string cmdText = myDE.CommandText;
    MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
    
    if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
    {
    if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
    {
     trans.Rollback();
     return 0;
    }

    object obj = cmd.ExecuteScalar();
    bool isHave = false;
    if (obj == null && obj == DBNull.Value)
    {
     isHave = false;
    }
    isHave = Convert.ToInt32(obj) > 0;

    if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
    {
     trans.Rollback();
     return 0;
    }
    if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
    {
     trans.Rollback();
     return 0;
    }
    continue;
    }
    int val = cmd.ExecuteNonQuery();
    count += val;
    if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
    {
    trans.Rollback();
    return 0;
    }
    cmd.Parameters.Clear();
   }
   trans.Commit();
   return count;
   }
   catch
   {
   trans.Rollback();
   throw;
   }
  }
  }
 }
 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
 {
  using (MySqlConnection conn = new MySqlConnection(connectionString))
  {
  conn.Open();
  using (MySqlTransaction trans = conn.BeginTransaction())
  {
   MySqlCommand cmd = new MySqlCommand();
   try
   {
   int indentity = 0;
   //循环
   foreach (CommandInfo myDE in SQLStringList)
   {
    string cmdText = myDE.CommandText;
    MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
    foreach (MySqlParameter q in cmdParms)
    {
    if (q.Direction == ParameterDirection.InputOutput)
    {
     q.Value = indentity;
    }
    }
    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
    int val = cmd.ExecuteNonQuery();
    foreach (MySqlParameter q in cmdParms)
    {
    if (q.Direction == ParameterDirection.Output)
    {
     indentity = Convert.ToInt32(q.Value);
    }
    }
    cmd.Parameters.Clear();
   }
   trans.Commit();
   }
   catch
   {
   trans.Rollback();
   throw;
   }
  }
  }
 }
 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
 {
  using (MySqlConnection conn = new MySqlConnection(connectionString))
  {
  conn.Open();
  using (MySqlTransaction trans = conn.BeginTransaction())
  {
   MySqlCommand cmd = new MySqlCommand();
   try
   {
   int indentity = 0;
   //循环
   foreach (DictionaryEntry myDE in SQLStringList)
   {
    string cmdText = myDE.Key.ToString();
    MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
    foreach (MySqlParameter q in cmdParms)
    {
    if (q.Direction == ParameterDirection.InputOutput)
    {
     q.Value = indentity;
    }
    }
    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
    int val = cmd.ExecuteNonQuery();
    foreach (MySqlParameter q in cmdParms)
    {
    if (q.Direction == ParameterDirection.Output)
    {
     indentity = Convert.ToInt32(q.Value);
    }
    }
    cmd.Parameters.Clear();
   }
   trans.Commit();
   }
   catch
   {
   trans.Rollback();
   throw;
   }
  }
  }
 }
 /// <summary>
 /// 执行一条计算查询结果语句，返回查询结果（object）。
 /// </summary>
 /// <param name="SQLString">计算查询结果语句</param>
 /// <returns>查询结果（object）</returns>
 public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  using (MySqlCommand cmd = new MySqlCommand())
  {
   try
   {
   PrepareCommand(cmd, connection, null, SQLString, cmdParms);
   object obj = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
   {
    return null;
   }
   else
   {
    return obj;
   }
   }
   catch (MySql.Data.MySqlClient.MySqlException e)
   {
   throw e;
   }
  }
  }
 }

 /// <summary>
 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
 /// </summary>
 /// <param name="strSQL">查询语句</param>
 /// <returns>MySqlDataReader</returns>
 public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
 {
  MySqlConnection connection = new MySqlConnection(connectionString);
  MySqlCommand cmd = new MySqlCommand();
  try
  {
  PrepareCommand(cmd, connection, null, SQLString, cmdParms);
  MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  cmd.Parameters.Clear();
  return myReader;
  }
  catch (MySql.Data.MySqlClient.MySqlException e)
  {
  throw e;
  }
  //			finally
  //			{
  //				cmd.Dispose();
  //				connection.Close();
  //			}	

 }

 /// <summary>
 /// 执行查询语句，返回DataSet
 /// </summary>
 /// <param name="SQLString">查询语句</param>
 /// <returns>DataSet</returns>
 public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
 {
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
  MySqlCommand cmd = new MySqlCommand();
  PrepareCommand(cmd, connection, null, SQLString, cmdParms);
  using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
  {
   DataSet ds = new DataSet();
   try
   {
   da.Fill(ds, "ds");
   cmd.Parameters.Clear();
   }
   catch (MySql.Data.MySqlClient.MySqlException ex)
   {
   throw new Exception(ex.Message);
   }
   return ds;
  }
  }
 }


 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, 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 (MySqlParameter parameter in cmdParms)
  {
   if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
   (parameter.Value == null))
   {
   parameter.Value = DBNull.Value;
   }
   cmd.Parameters.Add(parameter);
  }
  }
 }

 #endregion
 }

关于MySQL中添加数据的两种方法

标签：

热心网友时间：2022-04-08 06:17

db1为原数据库，db2为要导出到的数据库，fromtable 是要导出的表名

1.方法一：
登录导出到的数据库，执行
create table fromtable select * from db1.fromtable;
2.方法二：
在cmd下执行，mysqlmp -u root -p db1 fromtable file=d:/fromtable.sql; 输入秘密，root为用户名
登录db2 执行 source d:/fromtable.sql;
3.方法三：
登录db1 执行 select * from fromtable into outfile "d:/fromtable .txt"; 导出纯数据格式
登录db2 执行 load data infile d:/fromtable .txt into table fromtable; 需要先建一张和原表结构一样的空表。
4.建一个odbc连接，先导出到access中，再导出到另一个库中。