/// <summary>
/// 检索SQL参数信息并填充
/// </summary>
/// <param name="cmd"></param>
public void DeriveParameters(IDbCommand cmd)
{
if ((cmd as OleDbCommand) != null)
{
OleDbCommandBuilder.DeriveParameters(cmd as OleDbCommand);
}
}
/// <summary>
/// 查询：返回查询到的第一行第一列的值
/// (参数：checkNullTips有值传入时，会去检查空值)
/// </summary>
/// <typeparam name="T">返回值的类型</typeparam>
/// <param name="sql">查询语句：如果多行，也只是返回第一行第一列的值</param>
/// <param name="parameters">参数</param>
/// <param name="checkNullTips">查询结果空值时出错提示信息</param>
/// <returns></returns>
public T Select <T>(string sql, IDataParameter[] parameters, string checkNullTips)
{
OleDbCommand command = GetCommand();
command.CommandText = sql;
OleDbCommandBuilder.DeriveParameters(command);
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
object obj = command.ExecuteScalar();
if (obj == null)
{
obj = "";
}
T t = obj.To <T>();
//断开数据库连接, 使用外部事务时，不能关闭连接
if (DBConnection == null)
{
command.Parameters.Clear();
command.Connection.Close();
}
command.Dispose();
//空值检查（有传入空值提示信息nullTips时，才去检查）
if (t == null && checkNullTips != string.Empty)
{
throw new Exception(checkNullTips);
}
return(t);
}
/// <summary>
/// The discover sp parameter set.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="spName">
/// The sp name.
/// </param>
/// <param name="includeReturnValueParameter">
/// The include return value parameter.
/// </param>
/// <returns>
/// The <see>
/// <cref>OleDbParameter[]</cref>
/// </see>
/// .
/// </returns>
private static OleDbParameter[] DiscoverSpParameterSet(
string connectionString,
string spName,
bool includeReturnValueParameter)
{
using (var cn = new OleDbConnection(connectionString))
using (var cmd = new OleDbCommand(spName, cn))
{
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;
OleDbCommandBuilder.DeriveParameters(cmd);
if (!includeReturnValueParameter)
{
if (ParameterDirection.ReturnValue == cmd.Parameters[0].Direction)
{
cmd.Parameters.RemoveAt(0);
}
}
var discoveredParameters = new OleDbParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
return(discoveredParameters);
}
}
/// <summary>
/// <see cref="OleDbCommandBuilder.DeriveParameters"/>
/// </summary>
/// <param name="cmd"></param>
public override void DeriveParameters(System.Data.IDbCommand cmd)
{
if (cmd is OleDbCommand)
{
OleDbCommandBuilder.DeriveParameters(cmd as OleDbCommand);
}
}
protected override void DeriveParameters(IDbCommand cmd)
{
if (!(cmd is OleDbCommand))
{
throw new ArgumentException("The command provided is not a OleDbCommand instance.", "cmd");
}
OleDbCommandBuilder.DeriveParameters((OleDbCommand)cmd);
}
public void DeriveParameters_NullCommand_Throws(CommandType commandType)
{
using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand())
{
cmd.CommandType = commandType;
AssertExtensions.Throws <InvalidOperationException>(
() => OleDbCommandBuilder.DeriveParameters(cmd),
$"{nameof(OleDbCommand)} DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{cmd.CommandType.ToString()}.");
}
}
private static IDbDataParameter[] DiscoverSpParameterSet(IDbConnection connection, String spName, bool includeReturnValueParameter)
{
IDbCommand command = null;
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if ((spName == null) || (spName.Length == 0))
{
throw new ArgumentNullException("spName");
}
switch (DataAccessHelper.ProviderType)
{
case Util.ConnectionLibrary.SQlClient:
command = new SqlCommand(spName, (SqlConnection)connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters((SqlCommand)command);
break;
case Util.ConnectionLibrary.Oledb:
command = new OleDbCommand(spName, (OleDbConnection)connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
OleDbCommandBuilder.DeriveParameters((OleDbCommand)command);
break;
case Util.ConnectionLibrary.ODBC:
command = new OdbcCommand(spName, (OdbcConnection)connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
OdbcCommandBuilder.DeriveParameters((OdbcCommand)command);
break;
default:
command = null;
break;
}
connection.Close();
if (!includeReturnValueParameter)
{
command.Parameters.RemoveAt(0);
}
IDbDataParameter[] array = new IDbDataParameter[command.Parameters.Count];
command.Parameters.CopyTo(array, 0);
foreach (IDbDataParameter parameter in array)
{
parameter.Value = DBNull.Value;
}
return(array);
}
public void DeriveParameters_NullCommandText_Throws()
{
using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = null;
AssertExtensions.Throws <InvalidOperationException>(
() => OleDbCommandBuilder.DeriveParameters(cmd),
$"{nameof(OleDbCommandBuilder.DeriveParameters)}: {nameof(cmd.CommandText)} property has not been initialized");
}
}
/// <summary>
/// Resolve at run time the appropriate set of DbParameters for a stored procedure
/// </summary>
/// <param name="connection">A valid DbConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
/// <returns>The parameter array discovered.</returns>
private static DbParameter[] DiscoverSpParameterSet(DbConnection connection, string spName,
bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (spName == null || spName.Length == 0)
{
throw new ArgumentNullException("spName");
}
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
if (connection is SqlConnection)
{
SqlCommandBuilder.DeriveParameters((SqlCommand)cmd);
}
else if (connection is OracleConnection)
{
OracleCommandBuilder.DeriveParameters((OracleCommand)cmd);
}
else if (connection is OdbcConnection)
{
OdbcCommandBuilder.DeriveParameters((OdbcCommand)cmd);
}
else if (connection is OleDbConnection)
{
OleDbCommandBuilder.DeriveParameters((OleDbCommand)cmd);
}
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
DbParameter[] discoveredParameters = new DbParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (DbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
protected IDataParameterCollection DeriveParameters(string sSql, CommandType oType)
{
ValidateConnection();
ClearParameters();
m_oCommand.CommandText = sSql;
m_oCommand.CommandType = oType;
switch (this.PROVIDER)
{
case PROVIDER_TYPE.PROVIDER_SQLCLIENT:
SqlCommandBuilder.DeriveParameters((SqlCommand)m_oCommand);
break;
case PROVIDER_TYPE.PROVIDER_OLEDB:
OleDbCommandBuilder.DeriveParameters((OleDbCommand)m_oCommand);
break;
case PROVIDER_TYPE.PROVIDER_ODBC:
OdbcCommandBuilder.DeriveParameters((OdbcCommand)m_oCommand);
break;
//case PROVIDER_TYPE.PROVIDER_ORACLE:
// OracleCommandBuilder.DeriveParameters((OracleCommand)m_oCommand);
// break;
case PROVIDER_TYPE.PROVIDER_OTHER:
{
Type oCmdBuilderType = Type.GetType(ProviderCommandBuilderClassName);
MethodInfo oMth = oCmdBuilderType.GetMethod("DeriveParameters");
if (oMth == null)
{
throw (new Exception("DeriveParameters method is not suppored by the selected provider"));
}
object[] oParams = new Object[1];
oParams[0] = m_oCommand;
// DeriveParameters is static method
oMth.Invoke(null, oParams);
break;
}
default:
throw (new Exception("Invalid provider type"));
}
return(m_oCommand.Parameters);
}
public void DeriveParameters_NullCommand_Throws(CommandType commandType)
{
using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand())
{
cmd.CommandType = commandType;
var exception = Record.Exception(() => OleDbCommandBuilder.DeriveParameters(cmd));
Assert.NotNull(exception);
Assert.IsType <InvalidOperationException>(exception);
Assert.Equal(
string.Format("{0} DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{1}.", nameof(OleDbCommand), cmd.CommandType.ToString()),
exception.Message);
}
}
public void DeriveParameters_NulllCommandText_Throws()
{
using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = null;
var exception = Record.Exception(() => OleDbCommandBuilder.DeriveParameters(cmd));
Assert.NotNull(exception);
Assert.IsType <InvalidOperationException>(exception);
Assert.Equal(
string.Format("{0}: {1} property has not been initialized", nameof(OleDbCommandBuilder.DeriveParameters), nameof(cmd.CommandText)),
exception.Message);
}
}
public void DeriveParameters_NullConnection_Throws()
{
RunTest((command, tableName) => {
using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = @"SELECT * FROM " + tableName;
cmd.Connection = null;
AssertExtensions.Throws <InvalidOperationException>(
() => OleDbCommandBuilder.DeriveParameters(cmd),
$"{nameof(OleDbCommandBuilder.DeriveParameters)}: {nameof(cmd.Connection)} property has not been initialized.");
}
});
}
void IDatabase.DeriveParameters()
{
try
{
OleDbCommandBuilder.DeriveParameters(this.sqlDataAdapter.SelectCommand);
}
catch (AtomusException exception)
{
throw exception;
}
catch (Exception exception)
{
throw new AtomusException(exception);
}
}
public void DeriveParameters_ClosedConnection_Throws()
{
RunTest((command, tableName) => {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = @"SELECT * FROM " + tableName;
connection.Close();
var exception = Record.Exception(() => OleDbCommandBuilder.DeriveParameters(command));
Assert.NotNull(exception);
Assert.IsType <InvalidOperationException>(exception);
Assert.Contains(
$"{nameof(OleDbCommandBuilder.DeriveParameters)} requires an open and available Connection.",
exception.Message);
command.CommandType = CommandType.Text;
connection.Open(); // reopen when done
});
}
/// <summary>
/// <para>Dicover the parameters for a stored procedure using a separate connection and command.</para>
/// </summary>
/// <param name="parameterToken"><para>The parameter delimeter for database commands.</para></param>
protected override void DoDiscoverParameters(char parameterToken)
{
this.parameterToken = parameterToken;
using (OleDbCommand newCommand = CreateNewCommandAndConnectionForDiscovery())
{
OleDbCommandBuilder.DeriveParameters(newCommand);
foreach (IDataParameter parameter in newCommand.Parameters)
{
IDataParameter cloneParameter = (IDataParameter)((ICloneable)parameter).Clone();
cloneParameter.ParameterName = BuildParameterName(cloneParameter.ParameterName);
this.command.Parameters.Add(cloneParameter);
}
newCommand.Connection.Close();
}
}
private void Add(OleDbCommand Cmd)
{
if (Cmd.Transaction == null)
{
OleDbCommandBuilder.DeriveParameters(Cmd);
BaseAdd(Cmd.CommandText, new ArrayOfParameterDef(Cmd.Parameters));
}
else
{
OleDbCommand Cmd1 = new OleDbCommand(Cmd.CommandText, new OleDbConnection(Cmd.Connection.ConnectionString));
Cmd1.CommandType = CommandType.StoredProcedure;
Cmd1.Connection.Open();
Add(Cmd1);
Cmd1.Connection.Close();
this[Cmd].CopyTo(Cmd);
}
}
/// <summary>
/// 通用调用存储过程的方法，提供字符参数的支持
/// 使用OleDbCommandBuider获取存储过程参数名称和类型
/// 可以直接赋值使用，缺点：增加一次数据库访问，不适合在频繁访问中使用
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="paraValue">字符参数值的数组</param>
public override void CallProcedureCommon(string procedureName, string[] paraValue)
{
OleDbCommand command = new OleDbCommand();
command.Connection = this.conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName;
//获取过程的参数信息和类型，产生一次数据库访问
OleDbCommandBuilder.DeriveParameters(command);
//
for (int i = 0; i < paraValue.Length; i++)
{
command.Parameters[i].Value = paraValue[i];
}
//
command.ExecuteNonQuery();
}
public void DeriveParameters_ClosedConnection_Throws()
{
RunTest((command, tableName) => {
using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = @"SELECT * FROM " + tableName;
cmd.Connection = (OleDbConnection)OleDbFactory.Instance.CreateConnection();
cmd.Connection.Close();
var exception = Record.Exception(() => OleDbCommandBuilder.DeriveParameters(cmd));
Assert.NotNull(exception);
Assert.IsType <InvalidOperationException>(exception);
Assert.Contains(
$"{nameof(OleDbCommandBuilder.DeriveParameters)} requires an open and available Connection.",
exception.Message);
}
});
}
private OleDbCommand CreateCommand(CommandType commandType, string commandText, Dictionary <string, OleDbParameter> parameters, OleDbTransaction transaction)
{
OleDbCommand command = _connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = commandText;
command.CommandType = commandType;
command.CommandTimeout = _commandTimeout;
if (commandType == CommandType.StoredProcedure)
{
OleDbCommandBuilder.DeriveParameters(command);
}
if (parameters != null)
{
if (commandType == CommandType.StoredProcedure)
{
// Set command parameter values as per input values
foreach (OleDbParameter currentParameter in command.Parameters)
{
if (parameters.ContainsKey(currentParameter.ParameterName))
{
currentParameter.Value = parameters[currentParameter.ParameterName].Value;
}
else
{
currentParameter.Value = null;
}
}
}
else
{
foreach (OleDbParameter parameter in parameters.Values)
{
_ = command.Parameters.Add(parameter);
}
}
}
return(command);
}
protected virtual void DeriveParameters(string providerName, DbCommand command)
{
if (string.Equals(providerName, "System.Data.Odbc", StringComparison.OrdinalIgnoreCase))
{
OdbcCommandBuilder.DeriveParameters((OdbcCommand)command);
}
else if (string.Equals(providerName, "System.Data.OleDb", StringComparison.OrdinalIgnoreCase))
{
OleDbCommandBuilder.DeriveParameters((OleDbCommand)command);
}
else if (string.Equals(providerName, "System.Data.SqlClient", StringComparison.OrdinalIgnoreCase) || string.IsNullOrEmpty(providerName))
{
SqlCommandBuilder.DeriveParameters((SqlCommand)command);
}
else
{
UIServiceHelper.ShowError(this.SqlDataSource.Site, System.Design.SR.GetString("SqlDataSourceDesigner_InferStoredProcedureNotSupported", new object[] { providerName }));
}
}
public override IDbCommand CreateProcedureCommand(string procedureName)
{
using (IDbConnection connection = OpenDbConnection())
{
IDbCommand command = CreateDbCommand();
command.Connection = connection;
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
try
{
OleDbCommandBuilder.DeriveParameters(command as OleDbCommand);
return(command);
}
finally
{
connection.Close();
}
}
}
protected override void DeriveParameters(System.Data.Common.DbCommand discoveryCommand)
{
if (discoveryCommand.CommandType == System.Data.CommandType.StoredProcedure)
{
OleDbCommandBuilder.DeriveParameters((OleDbCommand)discoveryCommand);
}
else
{
// Add the return parameter normally expected by stored procs
discoveryCommand.Parameters.Add(CreateParameter(discoveryCommand, "@RETURN_VALUE"));
discoveryCommand.Parameters[0].Direction = ParameterDirection.ReturnValue;
// Find the parameters in the string based on the parameter token
string strSearch = @"\" + ParameterToken + "[a-zA-Z0-9]+";
MatchCollection matchParameters = Regex.Matches(discoveryCommand.CommandText, strSearch);
foreach (Match paramMatch in matchParameters)
{
discoveryCommand.Parameters.Add(CreateParameter(discoveryCommand, paramMatch.Value));
}
}
}
private static void DeriveParametersFromConnection(IDbCommand command)
{
var sqlCommand = command as SqlCommand;
if (sqlCommand != null)
{
SqlCommandBuilder.DeriveParameters(sqlCommand);
return;
}
var oleDbCommand = command as OleDbCommand;
if (oleDbCommand != null)
{
OleDbCommandBuilder.DeriveParameters(oleDbCommand);
return;
}
throw new ArgumentOutOfRangeException(
nameof(command),
$"Cannot derive parameters for command type [{command.GetType().Name}].");
}
/// <summary>
/// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary
/// </summary>
/// <param name="cmd">The IDbCommand referencing the stored procedure from which the parameter information is to be derived. The derived parameters are added to the Parameters collection of the IDbCommand. </param>
public override void DeriveParameters(IDbCommand cmd)
{
bool mustCloseConnection = false;
if (!(cmd is OleDbCommand))
{
throw new ArgumentException("The command provided is not a OleDbCommand instance.", "cmd");
}
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
mustCloseConnection = true;
}
OleDbCommandBuilder.DeriveParameters((OleDbCommand)cmd);
if (mustCloseConnection)
{
cmd.Connection.Close();
}
}
/// <summary>
/// Returns parameters for programability object (E.g. Stored procedure,
/// UDF etc)
/// </summary>
/// <param name="objectName"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private Dictionary <string, OleDbParameter> GetProgramabilityParameters(string objectName, OleDbTransaction transaction)
{
var parameters = new Dictionary <string, OleDbParameter>();
using (var command = new OleDbCommand(objectName, _connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Transaction = transaction;
OleDbCommandBuilder.DeriveParameters(command);
foreach (OleDbParameter parameter in command.Parameters)
{
OleDbParameter parameterClone = CloneParameter(parameter);
if (parameterClone.Value == null)
{
parameterClone.Value = DBNull.Value;
}
parameters.Add(parameter.ParameterName, parameterClone);
}
}
return(parameters);
}
protected void ButtonSave_Click(object sender, EventArgs e)
{
OleDbConnection
connection = null;
try
{
try
{
connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["SybaseASEServer"].ConnectionString);
connection.Open();
OleDbCommand
cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SaveTest";
OleDbCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["Id"].Value = HtmlInputHiddenId.Value != string.Empty ? (object)Convert.ToInt64(HtmlInputHiddenId.Value) : DBNull.Value;
cmd.Parameters["Value"].Value = TextBoxValue.Text != string.Empty ? (object)TextBoxValue.Text : DBNull.Value;
cmd.ExecuteNonQuery();
Response.Write("<script type=\"text/javascript\">alert('" + ((int)cmd.Parameters["RETURN_VALUE"].Value == 0 ? "oB!" : "Tampax") + "'); opener.location.reload(); window.close()</script>");
}
catch (Exception eException)
{
throw (new Exception(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace));
}
}
finally
{
if (connection != null && connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
/// <summary>
/// Resolve at run time the appropriate set of OleDbParameters for a stored procedure
/// </summary>
/// <param name="connection">A valid OleDbConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
/// <returns>The parameter array discovered.</returns>
private static OleDbParameter[] DiscoverSpParameterSet(OleDbConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (string.IsNullOrEmpty(spName))
{
throw new ArgumentNullException(nameof(spName));
}
var cmd = new OleDbCommand(spName, connection)
{
CommandType = CommandType.StoredProcedure
};
connection.Open();
OleDbCommandBuilder.DeriveParameters(cmd);
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
var discoveredParameters = new OleDbParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (var discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
/// <summary>
/// 根据给定的数据库连接和存储过程名查询数据库名称获取参数数组信息
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">返回</param>
/// <returns>返回存储过程参数数组</returns>
private static OleDbParameter[] DiscoverSpParameterSet(OleDbConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (spName == null || spName.Length == 0)
{
throw new ArgumentNullException("spName");
}
OleDbCommand cmd = new OleDbCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
OleDbCommandBuilder.DeriveParameters(cmd);
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
OleDbParameter[] discoveredParameters = new OleDbParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化数组中每个参数值为DBNull
foreach (OleDbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
public void DeriveParameters_NullCommand_Throws()
{
Assert.Throws <ArgumentNullException>(() => OleDbCommandBuilder.DeriveParameters(null));
}
