|
se();
return ds;
}
catch (Exception e)
{
throw e;
}
}
///
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
///
///
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
/// 命令类型(存储过程, 文本, 等等)
/// 存储过程名称或者sql命令语句
/// 执行命令所用参数的集合
/// 用 Convert.To{Type}把类型转换为想要的
public static object ExecuteScalar(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
///
/// 将参数集合添加到缓存
///
/// 添加到缓存的变量
/// 一个将要添加到缓存的sql参数集合
public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
///
/// 找回缓存参数集合
///
/// 用于找回参数的关键字
/// 缓存的参数集合
public static MySqlParameter[] GetCachedParameters(string cacheKey)
{
MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
///
/// 准备执行一个命令
///
/// sql命令
/// OleDb连接
/// OleDb事务
/// 命令类型例如 存储过程或者文本
/// 命令文本,例如:Select * from Products
/// 执行命令的参数
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, 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 = 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);
}
}
}
}
?
?
?
提示：sql语句无论是oracle、
mysql、sqlserver，增删查改语句大体相似，皆为insert、delete、update、select操作，故熟悉一种数据库操作语句后，亦可相对容易学习其他数据库语句
?
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using DBUtility;
?
1、插入操作（insert）
?
示例代码：
?
public int Add( int userId, string name)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO T_Photo(Name,UserID) ");
sb.Append("VALUES(?Name,?UserID) ");
MySqlParameter[] parameters = {
new MySqlParameter("?Name", MySqlDbType.String),
new MySqlParameter("?UserID", MySqlDbType.Int32)
};
parameters[0].Value = name;
parameters[1].Value = userId;
return SQLHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
}
?
?
2、删除操作（delete）
?
示例代码：
?
public int Delete(long id, int userId)
{
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM T_Photo WHERE ID = ?ID AND UserID = ?UserID");
MySqlParameter[] parameters = {
new MySqlParameter("?ID", MySqlDbType.Int64),
new MyS |