mysql中删除某一纵的方法_MySql数据库操作类---MySqlHelper

///

///MySql数据库操作类///

public classMySqlHelper

{///public static string connectionStringMYSQL = System.Configuration.ConfigurationManager.ConnectionStrings["EZRMySQL"].ConnectionString;存储过程

public static DataTable ExecuteDataTableCommand(string CommandText, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{

MySqlCommand cmd= newMySqlCommand();

cmd.CommandType=CommandType.StoredProcedure;

cmd.CommandText=CommandText;using (MySqlDataAdapter da = newMySqlDataAdapter(cmd))

{

DataSet ds= newDataSet();try{

da.Fill(ds,"ds");

cmd.Parameters.Clear();

}catch(MySql.Data.MySqlClient.MySqlException ex)

{throw newException(ex.Message);

}return ds.Tables[0];

}

}

}#region ExecuteNonQuery

//执行SQL语句，返回影响的记录数

///

///执行SQL语句，返回影响的记录数///

/// SQL语句

/// 影响的记录数

public static int ExecuteNonQuery(string SQLString, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))

{try{

connection.Open();int rows =cmd.ExecuteNonQuery();returnrows;

}catch(MySql.Data.MySqlClient.MySqlException e)

{

connection.Close();throwe;

}

}

}

}///

///执行SQL语句，返回影响的记录数///

/// SQL语句

/// 影响的记录数

public static int ExecuteNonQuery(string SQLString, string MySQLConStr, paramsMySqlParameter[] cmdParms)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{using (MySqlCommand cmd = newMySqlCommand())

{try{

PrepareCommand(cmd, connection,null, SQLString, cmdParms);int rows =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnrows;

}catch(MySql.Data.MySqlClient.MySqlException e)

{throwe;

}

}

}

}///

///是否存在///

///

///

public static bool Exists(string strSql, stringMySQLConStr)

{object obj =GetSingle(strSql, MySQLConStr);intcmdresult;if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))

{

cmdresult= 0;

}else{

cmdresult= 1;

}if (cmdresult == 0)

{return false;

}else{return true;

}

}///

///执行一条计算查询结果语句，返回查询结果(object)。///

/// 计算查询结果语句

/// 查询结果(object)

public static object GetSingle(string SQLString, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))

{try{

connection.Open();object obj =cmd.ExecuteScalar();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))

{return null;

}else{returnobj;

}

}catch(MySql.Data.MySqlClient.MySqlException e)

{

connection.Close();throwe;

}

}

}

}public static object GetSingle(string SQLString, int Times, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{using (MySqlCommand cmd = newMySqlCommand(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{returnobj;

}

}catch(MySql.Data.MySqlClient.MySqlException e)

{

connection.Close();throwe;

}

}

}

}//执行多条SQL语句，实现数据库事务。

///

///执行多条SQL语句，实现数据库事务。///

/// 多条SQL语句

public static bool ExecuteNoQueryTran(List SQLStringList, stringMySQLConStr)

{using (MySqlConnection conn = newMySqlConnection(MySQLConStr))

{

conn.Open();

MySqlCommand cmd= newMySqlCommand();

cmd.Connection=conn;

MySqlTransaction tx=conn.BeginTransaction();

cmd.Transaction=tx;try{for (int n = 0; n < SQLStringList.Count; n++)

{string strsql =SQLStringList[n];if (strsql.Trim().Length > 1)

{

cmd.CommandText=strsql;

PrepareCommand(cmd, conn, tx, strsql,null);

cmd.ExecuteNonQuery();

}

}

cmd.ExecuteNonQuery();

tx.Commit();return true;

}catch{

tx.Rollback();return false;

}

}

}#endregion ExecuteNonQuery

#region ExecuteScalar

///

///执行一条计算查询结果语句，返回查询结果(object)。///

/// 计算查询结果语句

/// 查询结果(object)

public static object ExecuteScalar(string SQLString, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))

{try{

connection.Open();object obj =cmd.ExecuteScalar();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))

{return null;

}else{returnobj;

}

}catch(MySql.Data.MySqlClient.MySqlException e)

{

connection.Close();throwe;

}

}

}

}///

///执行一条计算查询结果语句，返回查询结果(object)。///

/// 计算查询结果语句

/// 查询结果(object)

public static object ExecuteScalar(string SQLString, string MySQLConStr, paramsMySqlParameter[] cmdParms)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{using (MySqlCommand cmd = newMySqlCommand())

{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{returnobj;

}

}catch(MySql.Data.MySqlClient.MySqlException e)

{throwe;

}

}

}

}#endregion ExecuteScalar

#region ExecuteReader

///

///执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )///

/// 查询语句

/// MySqlDataReader

public static MySqlDataReader ExecuteReader(string strSQL, stringMySQLConStr)

{

MySqlConnection connection= newMySqlConnection(MySQLConStr);

MySqlCommand cmd= newMySqlCommand(strSQL, connection);try{

connection.Open();

MySqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);returnmyReader;

}catch(MySql.Data.MySqlClient.MySqlException e)

{throwe;

}

}///

///执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )///

/// 查询语句

/// MySqlDataReader

public static MySqlDataReader ExecuteReader(string SQLString, string MySQLConStr, paramsMySqlParameter[] cmdParms)

{

MySqlConnection connection= newMySqlConnection(MySQLConStr);

MySqlCommand cmd= newMySqlCommand();try{

PrepareCommand(cmd, connection,null, SQLString, cmdParms);

MySqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();returnmyReader;

}catch(MySql.Data.MySqlClient.MySqlException e)

{throwe;

}//finally//{//cmd.Dispose();//connection.Close();//}

}#endregion ExecuteReader

#region ExecuteDataTable

///

///执行查询语句，返回DataTable///

/// 查询语句

/// DataTable

public DataTable ExecuteDataTable(string SQLString, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{

DataSet ds= newDataSet();try{

connection.Open();

MySqlDataAdapter command= newMySqlDataAdapter(SQLString, connection);

command.Fill(ds,"ds");

}catch(MySql.Data.MySqlClient.MySqlException ex)

{throw newException(ex.Message);

}

connection.Close();return ds.Tables[0];

}

}#region ExecuteDataTable

///

///执行查询语句，返回DataTable///

/// 数据库类型(Nozzle,Feeder,Head)

/// 查询语句

/// DataTable

public static DataTable ExecuteDataTableSta(string db, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{

DataSet ds= newDataSet();try{

connection.Open();

MySqlDataAdapter command= newMySqlDataAdapter(MySQLConStr, connection);

command.Fill(ds,"ds");

}catch(MySql.Data.MySqlClient.MySqlException ex)

{throw newException(ex.Message);

}return ds.Tables[0];

}

}#endregion ExecuteDataTable

///

///执行查询语句，返回DataSet///

/// 查询语句

/// DataTable

public static DataTable ExecuteDataTable(string SQLString, string MySQLConStr, paramsMySqlParameter[] cmdParms)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{

MySqlCommand cmd= newMySqlCommand();

PrepareCommand(cmd, connection,null, SQLString, cmdParms);using (MySqlDataAdapter da = newMySqlDataAdapter(cmd))

{

DataSet ds= newDataSet();try{

da.Fill(ds,"ds");

cmd.Parameters.Clear();

}catch(MySql.Data.MySqlClient.MySqlException ex)

{throw newException(ex.Message);

}return ds.Tables[0];

}

}

}//获取起始页码和结束页码

public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord, stringMySQLConStr)

{using (MySqlConnection connection = newMySqlConnection(MySQLConStr))

{

DataSet ds= newDataSet();try{

connection.Open();

MySqlDataAdapter command= newMySqlDataAdapter(cmdText, connection);

command.Fill(ds, startResord, maxRecord,"ds");

}catch(MySql.Data.MySqlClient.MySqlException ex)

{throw newException(ex.Message);

}return ds.Tables[0];

}

}#endregion ExecuteDataTable

///

///获取分页数据 在不用存储过程情况下///

/// 总记录条数

/// 选择的列逗号隔开,支持top num

/// 表名字

/// 条件字符 必须前加 and

/// 排序 例如 ID

/// 当前索引页

/// 每页记录数

///

public DataTable getPager(out int recordCount, string selectList, string tableName, string whereStr, string orderExpression, int pageIdex, int pageSize, stringMySQLConStr)

{int rows = 0;

DataTable dt= newDataTable();

MatchCollection matchs= Regex.Matches(selectList, @"top\s+\d{1,}", RegexOptions.IgnoreCase);//含有top

string sqlStr = sqlStr = string.Format("select {0} from {1} where 1=1 {2}", selectList, tableName, whereStr);if (!string.IsNullOrEmpty(orderExpression)) { sqlStr += string.Format("Order by {0}", orderExpression); }if (matchs.Count > 0) //含有top的时候

{

DataTable dtTemp=ExecuteDataTable(sqlStr, MySQLConStr);

rows=dtTemp.Rows.Count;

}else //不含有top的时候

{string sqlCount = string.Format("select count(*) from {0} where 1=1 {1}", tableName, whereStr);//获取行数

object obj =ExecuteScalar(sqlCount, MySQLConStr);if (obj != null)

{

rows=Convert.ToInt32(obj);

}

}

dt= ExecuteDataTable(sqlStr, (pageIdex - 1) *pageSize, pageSize, MySQLConStr);

recordCount=rows;returndt;

}#region 创建command

private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, 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 (MySqlParameter parameter incmdParms)

{if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&(parameter.Value== null))

{

parameter.Value=DBNull.Value;

}

cmd.Parameters.Add(parameter);

}

}

}#endregion 创建command}