public static SqlParameter[] getPramater(SqlCommand sqlcommand, bool getValue)
{
SqlCommandBuilder.DeriveParameters(sqlcommand);
if (!getValue)
{
sqlcommand.Parameters.RemoveAt(0);
}
SqlParameter[] _paramter = new SqlParameter[sqlcommand.Parameters.Count];
sqlcommand.Parameters.CopyTo(_paramter, 0);
return(_paramter);
}
protected override void execute()
{
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter param in cmd.Parameters)
{
if (param.ParameterName != "@RETURN_RESULT")
{
parameters.Add(param.ParameterName.Substring(1));
}
// param.SourceColumn = param.ParameterName.Substring(1);
}
}
private void FillParameters(SqlCommand command)
{
SqlCommandBuilder.DeriveParameters(command);
foreach (var parameter in Parameters)
{
if (SqlParameter.ContainsParameter(command.Parameters, parameter.NormalizedName))
{
command.Parameters[parameter.NormalizedName].Value = parameter.Value;
}
}
}
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 SqlCommand ConfigurarComando(string NombreProcedimiento, object[] ListaValoresParametros)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.ConeccionSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = NombreProcedimiento;
SqlCommandBuilder.DeriveParameters(cmd);
cmd.CommandTimeout = 0;
AsignarParametros(cmd, ListaValoresParametros);
return(cmd);
}
private IDbCommand Comando(string ProcedimientoAlmacenado)
{
SqlCommand ComandoRetorno;
//comando usa la conexión el tipo del acceso a bd y la consulta en si para
//que el adapatador lo adapte a una tabla
ComandoRetorno = new SqlCommand(ProcedimientoAlmacenado, (SqlConnection)this.mConexion);
ComandoRetorno.CommandType = CommandType.StoredProcedure;
ComandoRetorno.Transaction = (SqlTransaction)this.mTransaccion;
SqlCommandBuilder.DeriveParameters(ComandoRetorno);
return((IDbCommand)ComandoRetorno);
}
/// <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 List <SqlParameter[]> GetParameterList(string procName, SqlConnection conn, SqlTransaction transaction, DataTable dt)
{
parameterList = new List <SqlParameter[]>();
List <SqlParameter> paramList = null;
List <SqlParameter> paramResultList = null;
SqlParameter[] parameters = null;
SqlParameter parameter = null;
try
{
paramResultList = new List <SqlParameter>();
command = new SqlCommand(procName, conn, transaction);
command.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(command);
parameters = new SqlParameter[command.Parameters.Count];
command.Parameters.CopyTo(parameters, 0);
command.Parameters.Clear();
paramList = new List <SqlParameter>();
paramList.AddRange(parameters);
paramList.RemoveAt(0);
foreach (SqlParameter param in paramList)
{
param.Value = "";
}
foreach (DataRow dr in dt.Rows)
{
paramResultList = new List <SqlParameter>();
foreach (DataColumn dc in dt.Columns)
{
parameter = paramList.Find(delegate(SqlParameter p)
{
return(p.ParameterName.IndexOf(string.Format("@{0}", dc.ColumnName)) > -1);
});
if (parameter != null)
{
parameter.Value = dr[dc];
paramResultList.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
}
}
parameterList.Add(paramResultList.ToArray());
}
}
catch (Exception ex)
{ throw ex; }
finally
{ }
return(parameterList);
}
public string obtenerAsistenteFICEPI(string sAp1, string sAp2, string sNombre)
{
SqlConnection oConn = Datos.abrirConexion;
SqlCommand cmd = new SqlCommand("FIC_PROFESIONAL", oConn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters[1].Value = sAp1;
cmd.Parameters[2].Value = sAp2;
cmd.Parameters[3].Value = sNombre;
StringBuilder strBuilder = new StringBuilder();
try
{
SqlDataReader dr;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
int i = 0;
strBuilder.Append("<table id='tblOpciones' name='tblOpciones'style='text-align:left; width:320px;' border='0' cellspacing='0' cellpadding='0'>");
while (dr.Read())
{
if (i % 2 == 0)
{
strBuilder.Append("<tr class=FA ");
}
else
{
strBuilder.Append("<tr class=FB ");
}
i++;
strBuilder.Append("id='" + dr["T001_IDFICEPI"].ToString() + "' onClick='marcarEstaFila(this,false)' onDblClick='convocar2(this);marcarEstaFila(this,false);' style='cursor:pointer;height:16px'><td style='padding-left:5px'><label class=texto id='lbl" + dr["T001_IDFICEPI"].ToString() + "' style='width:315px;text-overflow:ellipsis;overflow:hidden'");
if (dr["DESCRIPCION"].ToString().Length > 80)
{
strBuilder.Append(" title='" + dr["DESCRIPCION"].ToString() + "'");
}
strBuilder.Append("><NOBR>" + dr["DESCRIPCION"] + "</NOBR></label></td></tr>");
}
strBuilder.Append("</table>");
dr.Close();
dr.Dispose();
}
catch
{
strBuilder.Length = 0;
strBuilder.Append("Error");
}
//Response.Write(strTabla)*/
return(strBuilder.ToString());
}
private SqlCommand CreateSPCommandWithValues(string spName, string strConn, object[] selectCommandParms, ref ArrayList outPutParameters)
{
try
{
if ((outPutParameters != null))
{
outPutParameters.Clear();
}
SqlConnection cn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(spName, cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder cb = new SqlCommandBuilder();
cn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cn.Close();
int i = 0;
foreach (SqlParameter param in cmd.Parameters)
{
param.SourceVersion = DataRowVersion.Current;
param.SourceColumn = Convert.ToString(param.ParameterName).Remove(0, 1);
if ((selectCommandParms != null))
{
if ((param.Direction == ParameterDirection.Output | param.Direction == ParameterDirection.InputOutput | selectCommandParms[i] == null))
{
param.Value = DBNull.Value;
}
else
{
param.Value = selectCommandParms[i];
}
if (((outPutParameters != null)))
{
if ((param.Direction == ParameterDirection.Output | param.Direction == ParameterDirection.InputOutput))
{
object obj = new object();
obj = (object)param.ParameterName;
outPutParameters.Add(obj);
}
}
i += 1;
}
}
return(cmd);
}
catch (Exception ex)
{
throw ex;
}
}
public static XElement ModifyTelephoneIdentificationCode(Session session, Guid accountId, string oldCode, string newCode)
{
bool lastResult = false;
try
{
Token token = SessionManager.Default.GetToken(session);
string connectionString = SettingManager.Default.ConnectionString;
using (SqlConnection sqlconnection = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = sqlconnection.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "Account_UpdateDescription";
SqlCommandBuilder.DeriveParameters(sqlCommand);
sqlCommand.Parameters["@id"].Value = accountId;
sqlCommand.Parameters["@oldDescription"].Value = oldCode;
sqlCommand.Parameters["@newDescription"].Value = newCode;
sqlCommand.ExecuteNonQuery();
int result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
if (result == 0)
{
sqlCommand = sqlconnection.CreateCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = string.Format("UPDATE AccountHistory SET UpdatePersonID = '{0}' WHERE ID = '{1}' AND [Description] = '{2}' AND UpdateTime = (SELECT MAX(UpdateTime) FROM AccountHistory WHERE ID='{1}' AND [Description] = '{2}')", token.UserID, accountId, newCode);
sqlCommand.ExecuteNonQuery();
lastResult = true;
}
else
{
//maybe the accountId is an employee id
sqlCommand = sqlconnection.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "Employee_UpdateTelephonePin";
sqlconnection.Open();
SqlCommandBuilder.DeriveParameters(sqlCommand);
sqlCommand.Parameters["@id"].Value = accountId;
sqlCommand.Parameters["@oldPin"].Value = oldCode;
sqlCommand.Parameters["@newPin"].Value = newCode;
sqlCommand.ExecuteNonQuery();
result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
lastResult = (result == 0);
}
}
}
catch (System.Exception ex)
{
_Logger.Error(ex);
}
return(XmlResultHelper.NewResult(lastResult.ToPlainBitString()));
}
public bool ExecuteNonStoredProcedure(string nombreSp, ArrayList sqlParametros, string cadenaDeConexion)
{
if (Conectar(cadenaDeConexion))
{
command = new SqlCommand();
command.Connection = sqlConn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = nombreSp;
transaction = sqlConn.BeginTransaction();
command.Transaction = transaction;
try
{
SqlCommandBuilder.DeriveParameters(command);
if (sqlParametros == null)
{
cantidadParametros = 0;
}
else
{
cantidadParametros = sqlParametros.Count;
}
for (int i = 1; i <= command.Parameters.Count - 1; i++)
{
command.Parameters[i].Value = sqlParametros[i - 1];
}
command.ExecuteNonQuery();
transaction.Commit();
Desconectar();
return(true);
}
catch (SqlException sqlEx)
{
transaction.Rollback();
mensajeSQLExcepcion = sqlEx.Message;
return(false);
}
catch (Exception ex)
{
transaction.Rollback();
mensajeExcepcion = ex.Message;
return(false);
}
}
return(false);
}
public override Params ExecuteStoredProcedure(string strProcedureName, params object[] cmdParams)
{
Params params2;
Params @params = new SqlParams();
SqlConnection objConnection = (SqlConnection)base.objConnection;
try
{
SqlCommand command = new SqlCommand(strProcedureName, objConnection)
{
CommandType = CommandType.StoredProcedure
};
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
SqlCommandBuilder.DeriveParameters(command);
for (int i = 1; i < command.Parameters.Count; i++)
{
if (command.Parameters[i].Direction == ParameterDirection.Input)
{
if (cmdParams.Length <= (i - 1))
{
throw new Exception("Parameter Count does not match.");
}
command.Parameters[i].Value = cmdParams[i - 1];
}
else
{
command.Parameters[i].Value = DBNull.Value;
}
}
command.ExecuteNonQuery();
foreach (SqlParameter parameter in command.Parameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Output))
{
@params.Add(parameter);
}
}
params2 = @params;
}
catch (Exception exception)
{
throw exception;
}
finally
{
this.CloseConnection();
}
return(params2);
}
public Stream RegisterDevice()
{
try
{
IncomingWebRequestContext request = WebOperationContext.Current.IncomingRequest;
string deviceId = request.Headers["deviceId"];
string os = request.Headers["os"];
string package = request.Headers["package"];
string token = request.Headers["token"];
if (string.IsNullOrEmpty(deviceId) || string.IsNullOrEmpty(token))
{
return(Common.Utils.MakeTextAnswer("bad request"));
}
using (var conn = new SqlConnection(_solution.ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("[admin].[RegisterPushToken]", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
SqlCommandBuilder.DeriveParameters(cmd);
}
catch
{
try
{
TryUpdateDatabase();
SqlCommandBuilder.DeriveParameters(cmd);
}
catch
{
throw new Exception("Database does not support push notifications");
}
}
cmd.Parameters["@UserId"].Value = Guid.Parse(_credential.UserName);
cmd.Parameters["@DeviceId"].Value = deviceId;
cmd.Parameters["@OS"].Value = os;
cmd.Parameters["@Package"].Value = package;
cmd.Parameters["@Token"].Value = token;
cmd.ExecuteNonQuery();
return(Common.Utils.MakeTextAnswer("ok"));
}
}
catch (Exception e)
{
return(Common.Utils.MakeExceptionAnswer(e));
}
}
//string commonConnectionString = "Data Source=source;Initial Catalog=dbname;User ID=username;Password=password";
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
List <string> vs = new List <string>();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
DataSet ds = null;
if (!string.IsNullOrEmpty(spName))
{
using (SqlConnection cnn = new SqlConnection(connectionString))
{
cnn.Open();
using (SqlCommand cmd = new SqlCommand(spName, cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in cmd.Parameters)
{
if (p.ParameterName != "@RETURN_VALUE")
{
vs.Add(p.ParameterName);
}
}
cmd.Parameters.Clear();
for (int i = 0; i < parameterValues.Length; i++)
{
if (parameterValues[i] == null)
{
parameterValues[i] = (Object)DBNull.Value;
}
cmd.Parameters.AddWithValue(vs[i], parameterValues[i]);
}
sqlDataAdapter.SelectCommand = cmd;
try
{
using (var da = new SqlDataAdapter(cmd))
{
ds = new DataSet();
da.Fill(ds);
}
}
catch
{
}
cmd.Dispose();
cnn.Close();
}
}
}
return(ds);
}
public void TestByteArrayParameters()
{
string tableName = DataTestUtility.GetUniqueNameForSqlServer("CMD");
string procName = DataTestUtility.GetUniqueNameForSqlServer("CMD");
byte[] bArray = new byte[] { 1, 2, 3 };
using (var connection = new SqlConnection(DataTestUtility.TCPConnectionString))
using (var cmd = new SqlCommand(procName, connection))
{
try
{
connection.Open();
setupByteArrayArtifacts(connection, tableName, procName);
// Insert with SqlCommand
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@array"].Value = bArray;
cmd.ExecuteNonQuery();
//Insert with command Set
var commandSetType = mds.GetType("Microsoft.Data.SqlClient.SqlCommandSet");
var cmdSet = Activator.CreateInstance(commandSetType, true);
commandSetType.GetMethod("Append", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Invoke(cmdSet, new object[] { cmd });
commandSetType.GetProperty("Connection", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetSetMethod(true).Invoke(cmdSet, new object[] { connection });
commandSetType.GetMethod("ExecuteNonQuery", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Invoke(cmdSet, new object[] { });
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = $"SELECT * FROM {tableName}";
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SqlBytes byteArray = reader.GetSqlBytes(0);
Assert.Equal(byteArray.Length, bArray.Length);
for (int i = 0; i < bArray.Length; i++)
{
Assert.Equal(bArray[i], byteArray[i]);
}
}
}
}
finally
{
dropByteArrayArtifacts(connection, tableName, procName);
}
}
}
public int getNumFuncionarios()
{
myForm.CN.Open();
var cmd = new SqlCommand("dbo.fnGetNextNumFunc", myForm.CN);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery();
int retVal = (int)cmd.Parameters["@RETURN_VALUE"].Value;
myForm.CN.Close();
return(retVal);
}
private void DeriveParams()
{
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
command.Connection = conn;
conn.Open();
SqlCommandBuilder.DeriveParameters(command);
}
catch { }
}
}
private List <ParamInfo> ListParameters(string sProcName)
{
using (var cmd = new SqlCommand(sProcName, m_conn)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
var rgRes = new List <ParamInfo>();
foreach (SqlParameter oParam in cmd.Parameters)
{
rgRes.Add(new ParamInfo(oParam));
}
return(rgRes);
}
}
public async Task SaveListAsync <T>(String source, String command, System.Object prms, IEnumerable <T> list) where T : class
{
using var token = _profiler.Start(command);
using var cnn = await GetConnectionAsync(source);
using var cmd = cnn.CreateCommandSP(command, CommandTimeout);
SqlCommandBuilder.DeriveParameters(cmd);
var retParam = SetParametersWithList <T>(cmd, prms, list);
await cmd.ExecuteNonQueryAsync();
SetReturnParamResult(retParam, prms);
}
public ConnectionObject executeStoredProcedure(ConnectionObject currentConnection, string fullProcedureName, List <string> parameterList, bool hasResult)
{
try
{
connection = "Data Source= " + currentConnection.ServerSelectedName + "; Database=" + currentConnection.DatabaseSelectedName + "; User ID=" + userName + ";Password="******";";
SqlConnection databaseConnection = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = fullProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = databaseConnection;
cmd.CommandTimeout = 20000;
databaseConnection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
if (parameterList.Count() > 1)
{
for (var i = 0; i < parameterList.Count; i++)
{
cmd.Parameters[i + 1].Value = parameterList[i];
}
}
if (hasResult == true)
{
SqlDataReader result = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(result);
currentConnection.dataTable = dt;
}
else
{
int result = cmd.ExecuteNonQuery();
}
databaseConnection.Close();
currentConnection.isConnected = true;
}
catch (Exception ex)
{
//currentConnection.isConnected = false;
currentConnection.error = ex.Message;
}
return(currentConnection);
}
public void UDTParams_DeriveParameters_CheckAutoFixSuccess()
{
// the type and sproc must be commited to the database or this test will deadlock with a schema lock violation
// if you are missing these database entities then you should look for an updated version of the database creation script
string sprocName = "sp_insert_customers";
string typeName = "CustomerAddress";
string customerAddressTypeIncorrectName = $"{DataTestUtility.UdtTestDbName}.dbo.{typeName.Trim('[', ']')}";
string customerAddressTypeCorrectedName = $"[dbo].[{typeName.Trim('[', ']')}]";
string customerParameterName = "@customers";
Address addr = Address.Parse("123 baker st || Redmond");
DataTable table = new DataTable();
table.Columns.Add();
table.Columns.Add();
table.Rows.Add("john", addr);
using (SqlConnection connection = new SqlConnection(_connStr))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
using (SqlCommand cmd = new SqlCommand(sprocName, connection, transaction))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
Assert.NotNull(cmd.Parameters);
Assert.Equal(2, cmd.Parameters.Count); // [return_value, table]
SqlParameter p = cmd.Parameters[1];
Assert.Equal(customerParameterName, p.ParameterName);
Assert.Equal(SqlDbType.Structured, p.SqlDbType);
Assert.Equal(customerAddressTypeIncorrectName, p.TypeName); // the 3 part name is incorrect but needs to be maintained for compatibility
p.Value = table;
cmd.ExecuteNonQuery();
Assert.Equal(customerAddressTypeCorrectedName, p.TypeName); // check that the auto fix has been applied correctly
}
finally
{
transaction.Rollback();
}
}
}
}
public string ExecuteProcedure(string name, ICollection <Tuple <string, object> > parameters)
{
var result = string.Empty;
var db = GetContext();
{
using (var dbConnection = db.Database.Connection)
{
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var command = new SqlCommand(name, dbConnection as SqlConnection))
{
command.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(command);
foreach (SqlParameter item in command.Parameters)
{
var paramValue = parameters.FirstOrDefault(i => String.Equals(i.Item1, item.ParameterName, StringComparison.InvariantCultureIgnoreCase));
if (paramValue != null)
{
switch (item.DbType)
{
case DbType.Date:
case DbType.DateTime:
case DbType.DateTime2:
string dtValue = paramValue.Item2.ToString();
if (!item.IsNullable && string.IsNullOrEmpty(dtValue))
{
throw new Exception(string.Format("Please specify '{0}' required parameter!", paramValue.Item1));
}
item.Value = new DateTime(Convert.ToInt32(dtValue.Substring(0, 4)), Convert.ToInt32(dtValue.Substring(5, 2)), Convert.ToInt32(dtValue.Substring(8, 2)),
Convert.ToInt32(dtValue.Substring(11, 2)), Convert.ToInt32(dtValue.Substring(14, 2)), Convert.ToInt32(dtValue.Substring(17, 2)), new GregorianCalendar());
//item.Value = SPUtility.CreateDateTimeFromISO8601DateTimeString(paramValue.Item2.ToString());
break;
default:
item.Value = paramValue.Item2;
break;
}
}
}
command.ExecuteNonQuery();
result = command.Parameters.Cast <SqlParameter>().Where(item =>
item.Direction == ParameterDirection.Output).Aggregate(result, (current, item) => current + item.Value);
}
}
}
return(result);
}
/// <summary>
/// Lấy ra 1 thực thể by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
// <summary>
/// Lấy dữ liệu qua đối số
/// Created By : NVSON(24/06/2019)
/// </summary>
/// <param name="storeName">Tên Procedure</param>
/// <param name="value">Mảng các tham số </param>
/// <returns></returns>
public T GetEntity(string storeName, object[] value)
{
var entity = Activator.CreateInstance <T>();
try
{
using (DataAccess dataAccess = new DataAccess())
{
var cmd = dataAccess.GetSqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storeName;
//Gán giá trị các tham số đầu vào cho store:
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in cmd.Parameters)
{
var i = cmd.Parameters.IndexOf(p);
if (i > 0 && i <= value.Length)
{
p.Value = value[i - 1];
}
else if (i > value.Length)
{
break;
}
}
SqlDataReader sqlDataReader = cmd.ExecuteReader();
while (sqlDataReader.Read())
{
for (int i = 0; i < sqlDataReader.FieldCount; i++)
{
var propertyName = sqlDataReader.GetName(i);
var propertyValue = sqlDataReader.GetValue(i);
var propertyInfor = entity.GetType().GetProperty(propertyName);
if (propertyInfor != null && propertyValue != DBNull.Value)
{
propertyInfor.SetValue(entity, propertyValue);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(entity);
}
/// <summary>
/// 返回受影响的记录ID
/// </summary>
/// <param name="procname">存储过程名</param>
/// <param name="pars">参数</param>
/// <param name="isHasReturnVal">返回ID</param>
/// <returns>string受影响的记录数</returns>
public string ExecNonQuery(string procname, Hashtable pars, bool isHasReturnVal)
{
using (SqlConnection connection = this.GetConnection())
{
try
{
StringBuilder builder = new StringBuilder();
SqlCommand command = new SqlCommand(procname, connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = this._conn.ConnectionTimeout; //_commandTimeout;
SqlCommandBuilder.DeriveParameters(command);
builder = GetDataAdapter(command, pars, isHasReturnVal);
StringBuilder builder2 = new StringBuilder();
builder2.Append("Execute ");
builder2.Append(procname);
builder2.Append(" ");
builder2.Append(builder);
//command.ExecuteNonQuery();
string re = ParseHelper.Parse <string>(command.ExecuteNonQuery());
if (isHasReturnVal)
{
re = ParseHelper.Parse <string>(command.Parameters["@RETURN_VALUE"].Value);
}
return(re);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
if (this._conn.State == ConnectionState.Open)
{
this._conn.Close();
}
}
}
}
private void btnFetchParameters_Click(object sender, RoutedEventArgs e)
{
//SqlCommandBuilder.DeriveParameters(myCommand);
SqlConnection connection = new SqlConnection();
//connection.ConnectionString = StoredProcedure.connectionString;
connection.ConnectionString = "Network Library=DBMSSOCN;Data Source=10.6.14.136,1433;Initial Catalog=OneAutomation;User Id=FlxAdmin;Password=FlxAdmin;";
connection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = txtStoredProcedureName.Text;
myCommand.Connection = connection;
myCommand.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(myCommand);
connection.Close();
for (int i = 0; i < myCommand.Parameters.Count; i++)
{
if (myCommand.Parameters[i].Direction == ParameterDirection.Input || myCommand.Parameters[i].Direction == ParameterDirection.InputOutput)
{
procedureParametersList.Add(new ProcedureParameters {
dbType = myCommand.Parameters[i].DbType, Name = myCommand.Parameters[i].ParameterName
});
Label methodParameterName = new Label();
methodParameterName.Content = myCommand.Parameters[i].ParameterName;
methodParameterName.Margin = new Thickness(5);
TextBox methodParameterValue = new TextBox();
methodParameterValue.Margin = new Thickness(5);
textBoxList.Add(methodParameterValue);
procedureParameterGrid.RowDefinitions.Add(new RowDefinition()
{
Height = GridLength.Auto
});
procedureParameterGrid.RowDefinitions.Add(new RowDefinition()
{
Height = GridLength.Auto
});
Grid.SetColumn(methodParameterName, 0);
Grid.SetRow(methodParameterName, i);
Grid.SetColumn(methodParameterValue, 1);
Grid.SetRow(methodParameterValue, i);
procedureParameterGrid.Children.Add(methodParameterName);
procedureParameterGrid.Children.Add(methodParameterValue);
}
//rowCount++;
}
}
private static DbParameterCollection GetParameters(IDbCommand cmd)
{
if ((typeof(MySqlCommand)).IsInstanceOfType(cmd))
{
MySqlCommandBuilder.DeriveParameters((MySqlCommand)cmd);
return (DbParameterCollection)cmd.Parameters;
}
else if ((typeof(SqlCommand)).IsInstanceOfType(cmd))
{
SqlCommandBuilder.DeriveParameters((SqlCommand)cmd);
return (DbParameterCollection)cmd.Parameters;
}
else throw new Exception("Implementation not found");
}
/// <summary>
/// 探索运行时的存储过程,返回SqlParameter参数数组.
/// 初始化参数值为 DBNull.Value.
/// </summary>
/// <param>一个有效的数据库连接</param>
/// <param>存储过程名称</param>
/// <param>是否包含返回值参数</param>
/// <returns>返回SqlParameter参数数组</returns>
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (spName == null || spName.Length == 0)
{
throw new ArgumentNullException("spName");
}
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
// 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();
// 如果不包含返回值参数,将参数集中的每一个参数删除.
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
// 创建参数数组
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化参数值为 DBNull.Value.
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
public DataSet ExecuteStoredProcedure(string storedProcedureName, params object[] parameterValues)
{
var consulta = new DataSet();
try
{
// Open Connection
GetConnection();
var command = _dbConnection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
// Discover Parameters for Stored Procedure
// Populate command.Parameters Collection.
// Causes Rountrip to Database.
SqlCommandBuilder.DeriveParameters(command);
// Initialize Index of parameterValues Array
var index = 0;
// Populate the Input Parameters With Values Provided
foreach (
var parameter in
command.Parameters.Cast <SqlParameter>()
.Where(parameter => parameter.Direction == ParameterDirection.Input ||
parameter.Direction == ParameterDirection.InputOutput ||
parameter.Direction == ParameterDirection.Output
))
{
parameter.Value = parameterValues[index];
index++;
}
var da = new SqlDataAdapter {
SelectCommand = command
};
da.Fill(consulta);
}
catch (Exception ex)
{
var msj = ex.Message;
throw ex;
}
finally
{
//Nos desconectamos de la base de datos
CloseConnection();
}
return(consulta);
}
/// <summary>
/// Fillrer giữ liệu khi lấy dữ liệu theo ngày
/// </summary>
/// <param name="storeName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public IEnumerable <Ref> GetEntities(DateTime[] paramValue)
{
using (DataAccess dataAccess = new DataAccess())
{
var comand = dataAccess.SqlCommand;
comand.CommandType = CommandType.StoredProcedure;
comand.CommandText = "Proc_GetRefByDate";
//Băt đầu Transaction
//Gán giá trị các tham số đầu vào cho store:
//lấy các param trong store
SqlCommandBuilder.DeriveParameters(comand); // *1
foreach (SqlParameter p in comand.Parameters)
{
var i = comand.Parameters.IndexOf(p);
if (i > 0 && i <= paramValue.Length)
{
p.Value = paramValue[i - 1];
}
else if (i > paramValue.Length)
{
break;
}
}
using (var sqlDataReader = comand.ExecuteReader())
{
while (sqlDataReader.Read())
{
var entity = Activator.CreateInstance <Ref>();
for (int i = 0; i < sqlDataReader.FieldCount; i++)
{
string fieldName = sqlDataReader.GetName(i);
if (entity.GetType().GetProperty(fieldName) != null && sqlDataReader[fieldName] != DBNull.Value)
{
entity.GetType().GetProperty(fieldName).SetValue(entity, sqlDataReader[fieldName], null);
}
}
yield return(entity);
}
}
}
}