public bool InsertQuery(SqlConnection cn, string tableName, SqlParameter[] sqlParams)
{
try
{
string query = "Insert into {0} ({1}) values({2})";
string columns = string.Empty;
string values = string.Empty;
var availableFields = sqlParams.Where(x => x.Value != null).ToList();
columns = String.Join(",", availableFields.Select(x => x.ParameterName));
values = String.Join(",", availableFields.Select(x => String.Concat("@", x.ParameterName)));
query = String.Format(query, tableName, columns, values);
MakeSureConnectionIsOpen(cn);
SqlCommand cmd = new SqlCommand(query, cn);
if (availableFields != null)
{
var idp = availableFields.Where(x => x.ParameterName.ToLower() == "id").FirstOrDefault();
if (idp != null)
availableFields.Remove(idp);
cmd.Parameters.AddRange(availableFields.ToArray());
}
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
}
}
public bool UpdateQuery(SqlConnection cn, string tablename, SqlParameter[] sqlParams = null, string primaryColumnName = "Id")
{
try
{
string query = "Update {0} set {1} where {2}";
var availableFields = sqlParams.Where(x => x.Value != null).ToList();
List<string> cols = new List<string>();
string whereClause = string.Empty;
foreach (var item in availableFields)
{
var s = String.Concat(item.ParameterName, "=", "@" + item.ParameterName);
if (item.ParameterName.ToLower() == primaryColumnName.ToLower())
{
whereClause = s;
continue;
}
cols.Add(s);
}
string columns = String.Join(",", cols);
query = String.Format(query, tablename, columns, whereClause);
MakeSureConnectionIsOpen(cn);
SqlCommand cmd = new SqlCommand(query, cn);
if (sqlParams != null)
cmd.Parameters.AddRange(availableFields.ToArray());
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
}
}
// Since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new SqlHelper()"
/// <summary>
/// This method is used to attach array of SqlParameters to a SqlCommand.
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">An array of SqlParameters to be added to command</param>
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException(nameof(command));
if (commandParameters == null) return;
foreach (var p in commandParameters.Where(p => p != null))
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
public void AddSQLParameters(SqlCommand sqlcmd, SqlParameter[] sqlParam)
{
foreach (SqlParameter sqlParameter in sqlParam.Where(s => s != null))
sqlcmd.Parameters.Add(sqlParameter);
}
