private static void AddCommonParameters(UserProfileAddRequest model, SqlParameterCollection paramCol)
{
paramCol.AddWithValue("@FirstName", model.FirstName);
paramCol.AddWithValue("@LastName", model.LastName);
paramCol.AddWithValue("@Mi", model.Mi);
paramCol.AddWithValue("@AvatarUrl", model.AvatarUrl);
}
#pragma warning disable CA1822 // Mark members as static
public void SetParameters(SqlParameterCollection prms, Object values)
#pragma warning restore CA1822 // Mark members as static
{
if (values == null)
{
return;
}
if (values is ExpandoObject)
{
foreach (var e in values as IDictionary <String, Object> )
{
var val = e.Value;
if (val != null)
{
prms.AddWithValue($"@{e.Key}", val);
}
}
}
else
{
var props = values.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in props)
{
var val = prop.GetValue(values);
if (val != null)
{
prms.AddWithValue($"@{prop.Name}", val);
}
}
}
}
private static void MapCommonParameter(BlogCommentAddRequest model, SqlParameterCollection paramCollection)
{
paramCollection.AddWithValue("@Comment", model.Comment);
paramCollection.AddWithValue("@BlogId", model.BlogId);
paramCollection.AddWithValue("@ParentId", model.ParentId);
paramCollection.AddWithValue("@UserIdCreated", UserService.GetCurrentUserId());
}
private static void MapNoteAddRequest(AddNoteRequest model, int createdById, SqlParameterCollection col)
{
col.AddWithValue("@Notes", model.Notes);
col.AddWithValue("@SeekerId", model.SeekerId);
col.AddWithValue("@TagId", model.TagId);
col.AddWithValue("@CreatedBy", createdById);
}
public static int CreateSubmission(SubmissionCreateRequest application)
{
int id;
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("dbo.Submission_Insert", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameterCollection parameterCollection = command.Parameters;
parameterCollection.AddWithValue("@ListingId", application.ListingId);
parameterCollection.AddWithValue("@SeekerId", application.SeekerId);
parameterCollection.AddWithValue("@ResumeUrl", application.ResumeUrl);
parameterCollection.AddWithValue("@videoUrl", application.VideoUrl);
SqlParameter idParameter = new SqlParameter("@SubmissionId", SqlDbType.Int);
idParameter.Direction = ParameterDirection.Output;
parameterCollection.Add(idParameter);
connection.Open();
command.ExecuteNonQuery();
int.TryParse(idParameter.Value.ToString(), out id);
}
}
return(id);
}
private void MapCommonParameters(AnnouncementAddRequest model, SqlParameterCollection paramCollection)
{
paramCollection.AddWithValue("@title", model.Title);
paramCollection.AddWithValue("@announcementCategoryId", model.AnnouncementCategoryId);
paramCollection.AddWithValue("@body", model.Body);
paramCollection.AddWithValue("@userId", UserService.GetCurrentUserId());
}
public static int TestInsert()
{
int id;
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("dbo.Users_Insert", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameterCollection parameterCollection = command.Parameters;
parameterCollection.AddWithValue("@FirstName", "HeyThere");
parameterCollection.AddWithValue("@LastName", "ZipHACK!!");
SqlParameter idParameter = new SqlParameter("@UserId", SqlDbType.Int);
idParameter.Direction = ParameterDirection.Output;
parameterCollection.Add(idParameter);
connection.Open();
command.ExecuteNonQuery();
int.TryParse(idParameter.Value.ToString(), out id);
}
}
return(id);
}
public override void AddParameters(WeddingItineraryItem entity, SqlParameterCollection parameterCollection)
{
parameterCollection.AddWithValue("@WeddingId", entity.WeddingId);
parameterCollection.AddWithValue("@Name", entity.Name);
parameterCollection.AddWithValue("@State", entity.State);
parameterCollection.AddWithValue("@Description", entity.Description);
}
private static void AddParams(VendorAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@Name", model.Name);
col.AddWithValue("@Description", model.Description);
col.AddWithValue("@Headline", model.Headline);
col.AddWithValue("@Url", model.Url);
}
private void UserProfileParamsCollection(UserProfileAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@FirstName", model.FirstName);
col.AddWithValue("@LastName", model.LastName);
col.AddWithValue("@Mi", model.Mi);
col.AddWithValue("@AvatarUrl", model.AvatarUrl);
}
public void Update(imSabayaContext context)
{
try
{
IDbConnection objConn = context.PersistencySession.Connection;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
IDbCommand objComm = objConn.CreateCommand();
objComm.CommandText = @" UPDATE Password
SET password = @password, EffectiveFrom = @EffectiveFrom, EffectiveTo = @EffectiveTo
WHERE (passwordId = @passwordId)";
SqlParameterCollection Parms = (SqlParameterCollection)objComm.Parameters;
Parms.AddWithValue("@passwordId", this.PasswordId);
Parms.AddWithValue("@password", this.Password);
Parms.AddWithValue("@EffectiveFrom", this.EffectiveFrom);
Parms.AddWithValue("@EffectiveTo", this.EffectiveTo);
int rowUpdated = objComm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
private static void AddCommonParams(FileAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@Url", model.Url);
col.AddWithValue("@EntityTypeId", model.EntityTypeId);
col.AddWithValue("@Name", model.Name);
col.AddWithValue("@FileTypeId", model.FileTypeId);
}
/// <summary>Método que representa la llamada al procedure 'Usuario_Listar'</summary>
static public List <Entity.Usuario.Listar> Listar(Entity.Usuario Item, Cursor oCursor)
{
var ResultSet = new List <Entity.Usuario.Listar>();
SqlCommand oComando = oCursor.ObtenerComando(Contexto.CnControlPresupuesto);
string Esquema = Galeria.Conexiones[(int)Contexto.CnControlPresupuesto].EsquemaDefecto;
oComando.CommandText = Esquema + "Usuario_Listar";
try
{
SqlParameterCollection oP = oComando.Parameters;
oP.AddWithValue("usr", Uso.NoNulo(Item.usr, DBNull.Value));
oP.AddWithValue("codigoPersona", Uso.NoNulo(Item.codigoPersona, DBNull.Value));
oP.AddWithValue("idEstado", Uso.NoNulo(Item.idEstado, DBNull.Value));
using (SqlDataReader oLector = oComando.ExecuteReader())
{
while (oLector.Read())
{
var LItem = new Entity.Usuario.Listar();
LItem.usr = (string)oLector["usr"];
LItem.codigoPersona = (string)Uso.NoNulo(oLector["codigoPersona"]);
LItem.usrNombre = (string)oLector["usrNombre"];
LItem.clave = (string)oLector["clave"];
LItem.idEstado = (string)Uso.NoNulo(oLector["idEstado"]);
ResultSet.Add(LItem);
}
}
return(ResultSet);
}
catch (System.Exception)
{
throw;
}
}
public static void TransferLikeParameteres(ref string sql, string mark1, string mark2,
Dictionary <string, object> conditions,
SqlParameterCollection parameters)
{
try
{
foreach (var condition in conditions)
{
sql += condition.Key + " like " + mark1 + condition.Key + " " + mark2 + " ";
if (condition.Key.Equals("ID"))
{
int patientId = int.Parse(condition.Value.ToString());
parameters.AddWithValue(condition.Key, "%" + patientId + "%");
}
else
{
parameters.AddWithValue(condition.Key, "%" + condition.Value + "%");
}
}
}
catch (Exception e)
{
MainWindow.Log.WriteErrorLog("ComplexDao.cs-TransferLikeParameteres", e);
}
}
/// <summary>Método que representa la llamada al procedure 'Personal_ListarDes'</summary>
static public List <Entity.Personal.ListarDes> ListarDes(Entity.Personal Item, Cursor oCursor)
{
var ResultSet = new List <Entity.Personal.ListarDes>();
SqlCommand oComando = oCursor.ObtenerComando(Contexto.CnControlPresupuesto);
string Esquema = Galeria.Conexiones[(int)Contexto.CnControlPresupuesto].EsquemaDefecto;
oComando.CommandText = Esquema + "Personal_ListarDes";
try
{
SqlParameterCollection oP = oComando.Parameters;
oP.AddWithValue("idSexo", Uso.NoNulo(Item.idSexo, DBNull.Value));
oP.AddWithValue("idEstado", Uso.NoNulo(Item.idEstado, DBNull.Value));
using (SqlDataReader oLector = oComando.ExecuteReader())
{
while (oLector.Read())
{
var LItem = new Entity.Personal.ListarDes();
LItem.idPersona = (int)oLector["idPersona"];
LItem.nombre = (string)oLector["nombre"];
ResultSet.Add(LItem);
}
}
return(ResultSet);
}
catch (System.Exception)
{
throw;
}
}
public static DataTable RetriveDataReport(string SPName, string DateFrom, string DateTo, string ID)
{
DataTable table = new DataTable();
string cstr = "Data Source=" + Var.SERVERIP + ";User ID=sa;Password="******";Initial Catalog=" + Var.DATABASE + ";Connection Timeout=1800;";
using (SqlConnection conn = new SqlConnection(cstr))
{
conn.Open();
string sqlStr = SPName;
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 1800;
SqlParameterCollection spc = cmd.Parameters;
spc.AddWithValue("@DateFrom", DateFrom);
spc.AddWithValue("@DateTo", DateTo);
spc.AddWithValue("@ID", ID);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
cmd.Dispose();
}
conn.Close();
conn.Dispose();
}
return(table);
}
/// <summary>Método que representa la llamada al procedure 'Personal_Actualizar'</summary>
static public int Actualizar(Entity.Personal Item, Cursor oCursor)
{
SqlCommand oComando = oCursor.ObtenerComando(Contexto.CnControlPresupuesto);
string Esquema = Galeria.Conexiones[(int)Contexto.CnControlPresupuesto].EsquemaDefecto;
oComando.CommandText = Esquema + "Personal_Actualizar";
try
{
SqlParameterCollection oP = oComando.Parameters;
oP.AddWithValue("idPersona", Uso.NoNulo(Item.idPersona, DBNull.Value));
oP.AddWithValue("codigoPersona", Uso.NoNulo(Item.codigoPersona, DBNull.Value));
oP.AddWithValue("nombre", Uso.NoNulo(Item.nombre, DBNull.Value));
oP.AddWithValue("apellidoPaterno", Uso.NoNulo(Item.apellidoPaterno, DBNull.Value));
oP.AddWithValue("apellidoMarterno", Uso.NoNulo(Item.apellidoMarterno, DBNull.Value));
oP.AddWithValue("direccion", Uso.NoNulo(Item.direccion, DBNull.Value));
oP.AddWithValue("tefelofono", Uso.NoNulo(Item.tefelofono, DBNull.Value));
oP.AddWithValue("fechaNac", Uso.NoNulo(Item.fechaNac, DBNull.Value));
oP.AddWithValue("idSexo", Uso.NoNulo(Item.idSexo, DBNull.Value));
oP.AddWithValue("idEstado", Uso.NoNulo(Item.idEstado, DBNull.Value));
int NumFila = oComando.ExecuteNonQuery();
return(NumFila);
}
catch (System.Exception)
{
throw;
}
}
private static void ColAddParams(NotificationAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@UserId", model.UserId);
col.AddWithValue("@NotificationTypeId", model.NotificationTypeId);
col.AddWithValue("@NotificationText", model.NotificationText);
col.AddWithValue("@IsRead", model.IsRead);
}
private static void AddParameters(OwnerAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@FirstName", model.FirstName);
col.AddWithValue("@LastName", model.LastName);
col.AddWithValue("@Password", model.Password);
col.AddWithValue("@Email", model.Email);
}
public void SetParameters(SqlParameterCollection prms, Object values)
{
if (values == null)
{
return;
}
if (values is ExpandoObject)
{
foreach (var e in values as IDictionary <String, Object> )
{
var val = e.Value;
if (val != null)
{
prms.AddWithValue("@" + e.Key, e.Value);
}
}
}
else
{
var props = values.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in props)
{
var val = prop.GetValue(values);
if (val != null)
{
prms.AddWithValue("@" + prop.Name, val);
}
}
}
}
private static void AddCommonParams(FAQAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@Question", model.Question);
col.AddWithValue("@Answer", model.Answer);
col.AddWithValue("@CategoryId", model.CategoryId);
col.AddWithValue("@SortOrder", model.SortOrder);
}
/// <summary>Método que representa la llamada al procedure 'Documento_Lin_ListarUso'</summary>
static public List <Entity.Documento_Lin.ListarUso> ListarUso(Entity.Documento_Lin Item, Cursor oCursor)
{
var ResultSet = new List <Entity.Documento_Lin.ListarUso>();
SqlCommand oComando = oCursor.ObtenerComando(Contexto.CnControlPresupuesto);
string Esquema = Galeria.Conexiones[(int)Contexto.CnControlPresupuesto].EsquemaDefecto;
oComando.CommandText = Esquema + "Documento_Lin_ListarUso";
try
{
SqlParameterCollection oP = oComando.Parameters;
oP.AddWithValue("ccosto", Uso.NoNulo(Item.ccosto, DBNull.Value));
oP.AddWithValue("doc", Uso.NoNulo(Item.doc, DBNull.Value));
oP.AddWithValue("numero", Uso.NoNulo(Item.numero, DBNull.Value));
using (SqlDataReader oLector = oComando.ExecuteReader())
{
while (oLector.Read())
{
var LItem = new Entity.Documento_Lin.ListarUso();
LItem.idPartida = (string)oLector["idPartida"];
LItem.partida = (string)oLector["Partida"];
LItem.cantidad = (int)oLector["cantidad"];
LItem.precio = (decimal)oLector["precio"];
LItem.total = (decimal)oLector["total"];
ResultSet.Add(LItem);
}
}
return(ResultSet);
}
catch (System.Exception)
{
throw;
}
}
public static DataTable LookData(string[] tabel, string[] key)
{
DataTable table = new DataTable();
string cstr = serverid + ";Initial Catalog=" + tabel[0] + ";";
using (SqlConnection conn = new SqlConnection(cstr))
{
conn.Open();
string sqlStr = "Transaction_Look";
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameterCollection spc = cmd.Parameters;
spc.AddWithValue("@Table", tabel[1]);
if (key[0] != null)
{
spc.AddWithValue("@Key1", key[0]);
}
if (key[1] != null)
{
spc.AddWithValue("@Key2", key[1]);
}
if (key[2] != null)
{
spc.AddWithValue("@Key3", key[2]);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
cmd.Dispose();
}
conn.Close();
conn.Dispose();
}
return(table);
}
/// <summary>Método que representa la llamada al procedure 'Documento_Lin_Obtener'</summary>
static public int Obtener(Entity.Documento_Lin Item, Cursor oCursor)
{
SqlCommand oComando = oCursor.ObtenerComando(Contexto.CnControlPresupuesto);
string Esquema = Galeria.Conexiones[(int)Contexto.CnControlPresupuesto].EsquemaDefecto;
oComando.CommandText = Esquema + "Documento_Lin_Obtener";
try
{
SqlParameterCollection oP = oComando.Parameters;
oP.AddWithValue("ccosto", Uso.NoNulo(Item.ccosto, DBNull.Value));
oP.AddWithValue("doc", Uso.NoNulo(Item.doc, DBNull.Value));
oP.AddWithValue("numero", Uso.NoNulo(Item.numero, DBNull.Value));
oP.AddWithValue("idPartida", Uso.NoNulo(Item.idPartida, DBNull.Value));
oP.Add("ej", SqlDbType.Char, 4).Direction = ParameterDirection.Output;
oP.Add("mes", SqlDbType.Char, 2).Direction = ParameterDirection.Output;
oP.Add("cantidad", SqlDbType.Int).Direction = ParameterDirection.Output;
oP.Add(new SqlParameter("precio", SqlDbType.Decimal, 0, ParameterDirection.Output, false, 12, 2, null, DataRowVersion.Default, null));
oP.Add(new SqlParameter("total", SqlDbType.Decimal, 0, ParameterDirection.Output, false, 12, 2, null, DataRowVersion.Default, null));
int NumFila = oComando.ExecuteNonQuery();
Item.ej = (string)Uso.NoNulo(oP["ej"].Value);
Item.mes = (string)Uso.NoNulo(oP["mes"].Value);
Item.cantidad = (int?)Uso.NoNulo(oP["cantidad"].Value);
Item.precio = (decimal?)Uso.NoNulo(oP["precio"].Value);
Item.total = (decimal?)Uso.NoNulo(oP["total"].Value);
return(NumFila);
}
catch (System.Exception)
{
throw;
}
}
public static void AddCommonParams(UserProfileAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@FirstName", model.FirstName);
col.AddWithValue("@LastName", model.LastName);
col.AddWithValue("@Mi", model.MI);
col.AddWithValue("@AvatarUrl", model.AvatarUrl);
}
public static int Create(ReviewCreateRequest review)
{
int id;
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("dbo.Review_Insert", connection))
{
command.CommandType = CommandType.StoredProcedure;
SqlParameterCollection parameterCollection = command.Parameters;
parameterCollection.AddWithValue("@SubmissionID", review.SubmissionId);
parameterCollection.AddWithValue("@Rating", review.Rating);
parameterCollection.AddWithValue("@Comment", review.Comment);
parameterCollection.AddWithValue("@DepartmentID", review.DepartmentId);
parameterCollection.AddWithValue("@EmployeeName", review.Employee);
SqlParameter reviewId = new SqlParameter("@ReviewID", SqlDbType.Int);
reviewId.Direction = ParameterDirection.Output;
parameterCollection.Add(reviewId);
connection.Open();
command.ExecuteNonQuery();
int.TryParse(reviewId.Value.ToString(), out id);
}
}
return(id);
}
internal static void MapCommonParameters(BlogAddRequest model, SqlParameterCollection paramCollection)
{
paramCollection.AddWithValue("@userIdCreated", UserService.GetCurrentUserId());
paramCollection.AddWithValue("@title", model.Title);
paramCollection.AddWithValue("@body", model.Body);
paramCollection.AddWithValue("@blogCategory", model.BlogCategory);
paramCollection.AddWithValue("@private", model.Private);
DataTable BlogTagIdArray = new DataTable();
BlogTagIdArray.Columns.Add("BlogTagId", typeof(Int32));
if (model.BlogTagIds != null)
{
for (int i = 0; i < model.BlogTagIds.Count; i++)
{
BlogTagIdArray.Rows.Add(model.BlogTagIds[i]);
}
}
SqlParameter BlogTagIdTable = new SqlParameter();
BlogTagIdTable.ParameterName = "@blogTagIds";
BlogTagIdTable.SqlDbType = System.Data.SqlDbType.Structured;
BlogTagIdTable.Value = BlogTagIdArray;
paramCollection.Add(BlogTagIdTable);
}
private static void AddCommonParams(ExternalLinkAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@UserId", model.UserId);
col.AddWithValue("@UrlTypeId", model.UrlTypeId);
col.AddWithValue("@Url", model.Url);
col.AddWithValue("@EntityId", model.EntityId);
col.AddWithValue("@EntityTypeId", model.EntityTypeId);
}
// Null olabilecek parametreler için DBNull kullanılmalı, bunun kontolünü bu metod yapıyor
// http://www.codeproject.com/Tips/310792/Handling-null-values-in-SqlParameter-parameter-obj
/// <summary>
/// Null olan parametreler için default değer ataması yapar.
/// </summary>
/// <param name="target"></param>
/// <param name="parameterName">Parametre ismi</param>
/// <param name="value">Paremetre değeri</param>
/// <param name="nullValue">Null ise, girilecek değer</param>
/// <returns></returns>
public static SqlParameter AddWithValue(this SqlParameterCollection target, string parameterName, object value, object nullValue)
{
if (value == null)
{
return(target.AddWithValue(parameterName, nullValue ?? DBNull.Value));
}
return(target.AddWithValue(parameterName, value));
}
public static void MapAddWithValue(CommentAddRequest model, SqlParameterCollection col)
{
col.AddWithValue("@Subject", model.Subject);
col.AddWithValue("@Text", model.Text);
col.AddWithValue("@ParentId", model.ParentId);
col.AddWithValue("@EntityTypeId", model.EntityTypeId);
col.AddWithValue("@EntityId", model.EntityId);
}