internal IEnumerator <T> ExecuteReader <T>(SqlCommandModel model)
{
Type TypeT = typeof(T);
ConstructorInfo ctor = TypeT.GetConstructor(Type.EmptyTypes);
if (ctor == null)
{
throw new InvalidOperationException($"Type {TypeT.Name} does not have a default constructor.");
}
SqlCommand sqlCommand = new(model.CommandText, _sqlConnection);
sqlCommand.CommandType = model.CommandType;
foreach (SqlCommandParameterModel parameter in model.CommandParameters)
{
sqlCommand.Parameters.Add(new SqlParameter()
{
ParameterName = parameter.ParameterName,
DbType = parameter.DataType,
Value = parameter.Value
});
}
_sqlConnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
T newInst = (T)ctor.Invoke(null);
for (int i = 0; i < reader.FieldCount; i++)
{
string propertyName = reader.GetName(i);
PropertyInfo propertyInfo = TypeT.GetProperty(propertyName);
if (propertyInfo != null)
{
object value = reader[i];
if (value == DBNull.Value)
{
propertyInfo.SetValue(newInst, null);
}
else
{
propertyInfo.SetValue(newInst, value);
}
}
}
yield return(newInst);
}
}
_sqlConnection.Close();
}
public string ExecuteScalarSP(SqlCommandModel model)
{
try
{
_dbConnection.Open();
var parameters = new DynamicParameters();
parameters.Add(model.CommandParameters[0].ParameterName, model.CommandParameters[0].Value);
return(_dbConnection.Query <Product>(model.CommandText, parameters, commandType: CommandType.StoredProcedure).First().ProductName);
}
finally
{
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
{
_dbConnection.Close();
}
}
}
public void ExecuteNonQuery(SqlCommandModel model)
{
try
{
_dbConnection.Open();
var parameters = new DynamicParameters();
foreach (SqlCommandParameterModel parameter in model.CommandParameters)
{
parameters.Add(parameter.ParameterName, parameter.Value);
}
_dbConnection.Query(model.CommandText, parameters, commandType: CommandType.StoredProcedure);
}
finally
{
_dbConnection.Close();
}
}
internal void ExecuteNonQuery(SqlCommandModel model)
{
SqlCommand sqlCommand = new (model.CommandText, _sqlConnection);
sqlCommand.CommandType = model.CommandType;
foreach (SqlCommandParameterModel parameter in model.CommandParameters)
{
sqlCommand.Parameters.Add(new SqlParameter()
{
ParameterName = parameter.ParameterName,
DbType = parameter.DataType,
Value = parameter.Value
});
}
_sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
_sqlConnection.Close();
}
public void ReadScalarProductADNSP()
{
string connectionString = SecretsManager.GetSecrets <DatabaseSettings>("ConnectionString");
AdoDotNetData aaa = new(connectionString);
SqlCommandModel model = new SqlCommandModel()
{
CommandText = "GetProductName",
CommandType = CommandType.StoredProcedure,
CommandParameters = new SqlCommandParameterModel[] {
new SqlCommandParameterModel()
{
ParameterName = "@ProductName", DataType = DbType.String, Value = "ADO.NET Product"
}
}
};
string productName = aaa.ExecuteScalar <string>(model);
aaa.Dispose();
}
internal T ExecuteScalar <T>(SqlCommandModel model)
{
SqlCommand sqlCommand = new(model.CommandText, _sqlConnection);
sqlCommand.CommandType = model.CommandType;
foreach (SqlCommandParameterModel parameter in model.CommandParameters)
{
sqlCommand.Parameters.Add(new SqlParameter()
{
ParameterName = parameter.ParameterName,
DbType = parameter.DataType,
Value = parameter.Value
});
}
_sqlConnection.Open();
T data = (T)sqlCommand.ExecuteScalar();
_sqlConnection.Close();
return(data);
}
public IEnumerator <Product> ExecuteReaderSP <Product>(SqlCommandModel model)
{
try
{
_dbConnection.Open();
var parameters = new DynamicParameters();
foreach (SqlCommandParameterModel parameter in model.CommandParameters)
{
parameters.Add(parameter.ParameterName, parameter.Value);
}
return(_dbConnection.Query <Product>(model.CommandText, parameters, commandType: CommandType.StoredProcedure).GetEnumerator());
}
finally
{
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
{
_dbConnection.Close();
}
}
}
public int ExecuteNonQuerySP(SqlCommandModel model)
{
SqlParameter[] parameters = new SqlParameter[model.CommandParameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
parameters[i] = new SqlParameter(
model.CommandParameters[i].ParameterName,
model.CommandParameters[i].Value
);
}
if (parameters.Length == 4)
{
return(Database.ExecuteSqlRaw(model.CommandText, parameters[0], parameters[1], parameters[2], parameters[3]));
}
else if (parameters.Length == 2)
{
return(Database.ExecuteSqlRaw(model.CommandText, parameters[0], parameters[1]));
}
else
{
return(Database.ExecuteSqlRaw(model.CommandText, parameters[0]));
}
}
