Here I bring my problem.
I use SqlServer 2014 and VisualStudio 2015
I have an Object named CashRegister which has these attributes:
public class CashRegister
{
private static CashRegister CashRegisterInstance;
public int? Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public string DgiSucCode { get; set; }
public bool? ForceMoneyControl { get; set; }
public bool? Active { get; set; }
public Entities.Printer Printer { get; set; }
public Entities.Localidad Localidad { get; set; }
public List<Entities.User> User { get; set; }
public List<Entities.DocumentModel> DocumentModel { get; set; }
}
As you can see, I have 2 Lists within the, User and DocumentModel, what I want to do is to pass an Array of the Id of Each one so that I can Insert them in a Table with the same Procedure.
My Persistence:
/// <summary>
/// Create or Update a User Data
/// </summary>
/// <param name="Data"></param>
/// <returns></returns>
public Entities.CashRegister Save(Entities.CashRegister Data)
{
try
{
using (SqlCommand cmd = new SqlCommand("spCashRegisters_SaveUpdate", Connection))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Id", Data.Id));
cmd.Parameters.Add(new SqlParameter("@Name", Data.Name));
cmd.Parameters.Add(new SqlParameter("@Description", Data.Description));
cmd.Parameters.Add(new SqlParameter("@DgiSucCode", Data.DgiSucCode));
cmd.Parameters.Add(new SqlParameter("@ForceMoneyControl", Data.ForceMoneyControl));
cmd.Parameters.Add(new SqlParameter("@Active", Data.Active));
cmd.Parameters.Add(new SqlParameter("@LocalidadId", Data.Localidad.Id));
cmd.Parameters.Add(new SqlParameter("@DefaultPrinterID", Data.Printer.id));
cmd.Parameters.Add(new SqlParameter("@Users", this.ConvertToStringArray(Data.User)));
cmd.Parameters.Add(new SqlParameter("@DocumentModel", this.ConvertToStringArray(Data.DocumentModel)));
//Agregado para recibir el error en caso de que halla
SqlParameter RuturnValue = new SqlParameter("@RequestStatus", SqlDbType.Int);
RuturnValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(RuturnValue);
/// Fin de Agregado
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Data.Id = Convert.ToInt32(reader[0]);
}
}
int retval = (int)cmd.Parameters["@RequestStatus"].Value;
Data.Id = retval;
}
return Data;
}
catch (SqlException ex)
{
log.Error(ex);
throw ex;
}
catch (Exception ex)
{
log.Error(ex);
throw ex;
}
}
Method to Convert the list into a String
private string ConvertToStringArray(object o)
{
List< Entities.DocumentModel> doc = new List<Entities.DocumentModel>();
List<Entities.User> us = new List<User>();
string returned = "";
try
{
doc = (List< Entities.DocumentModel>)o;
var last = doc.Last();
foreach (var item in doc)
{
if (item.Equals(last))
{
returned = returned + (item.Id).ToString();
}else
{
returned = (item.Id).ToString() + ",";
}
}
}
catch (Exception)
{
us = (List<Entities.User>)o;
var last = us.Last();
foreach (var item in us)
{
if (item.Equals(last))
{
returned = returned + (item.Id).ToString();
}
else
{
returned = (item.Id).ToString() + ",";
}
}
}
return returned;
}
My Procedure:
-- Create or Update a Message
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spCashRegisters_SaveUpdate')
DROP PROCEDURE [dbo].[spCashRegisters_SaveUpdate]
GO
CREATE PROCEDURE [dbo].[spCashRegisters_SaveUpdate] (
@Id int = null
,@Name varchar(129) = null
,@Description varchar(256) = null
,@DefaultPrinterID int = null
,@LocalidadId int = null
,@DgiSucCode varchar(10) = null
,@ForceMoneyControl bit = null
,@Active bit = null
,@DocumentModel nvarchar(max) = null
,@Users nvarchar(max) = null
,@RequestStatus int output
)
AS
set @RequestStatus = 1 --Lo seteo en uno en caso de que todo salga bien
If NOT Exists(select top(1) ID from ApplicaAccCS_Max.dbo.Localidades where ID = @LocalidadId )
BEGIN
--RAISERROR('Localidad: ingresada no existe: Error en @LocalidadId', 16, 1);
set @RequestStatus = -2
return -2; ---Localidad Error = -2
END
If NOT Exists(select top(1) pri.Id from Printer as pri where pri.Id = @DefaultPrinterID )
BEGIN
set @RequestStatus = -4
--RAISERROR('Impresora: ingresada no existe: Error en @DefaultPrinterID', 16, 1);
return; ---impresora Error = -4
END
If NOT Exists(select top(1) doc.Id from tbDocumentModels as doc where doc.Id = @DocumentModel )
BEGIN
--RAISERROR('Documento: ingresado no existe: Error en @@DocumentModelId', 16, 1);
set @RequestStatus = -5
return -5; ---Documento Error = -5
END
BEGIN
IF @id IS NULL BEGIN
declare @Id_Ultimo int = 0;
-- Insert statements for procedure here
begin Transaction
begin Try
INSERT INTO [dbo].[tbCashRegister]
(
[Name]
,[Description]
,[DefaultPrinterID]
,[LocalidadId]
,[DgiSucCode]
,[ForceMoneyControl]
,[Active]
)
VALUES
(
@Name
,@Description
,@DefaultPrinterID
,@LocalidadId
,@DgiSucCode
,@ForceMoneyControl
,@Active
);
Select @@IDENTITY;
SELECT @Id_Ultimo = SCOPE_IDENTITY()
INSERT INTO tbUserByCashRegisters(CashRegisterId, UserId)
Select @Id_Ultimo
,r.Name
from [dbo].[splitstring](@Users) r
where Name != ''
INSERT INTO tbDocumentModelByCashRegisters(DocumentModelId, CashRegisterId)
Select r.Item
,@Id_Ultimo
from [dbo].[SplitInts](@DocumentModel, ',') r
where Item != ''
Commit
End Try
begin Catch
rollback
End Catch
END
ELSE BEGIN
-- Insert statements for procedure here
begin Transaction
begin Try
UPDATE [dbo].[tbCashRegister]
SET
[Name] = @Name
,[Description] = @Description
,[DefaultPrinterID] = @DefaultPrinterID
,[LocalidadId] = @LocalidadId
,[DgiSucCode] = @DgiSucCode
,[ForceMoneyControl]= @ForceMoneyControl
,[Active]= @Active
WHERE
[Id] = @Id;
Select @Id;
--UPDATE tbUserByCashRegisters
--SET
-- [UserId] = @UserId
-- ,[CashRegisterId] = @Id
--WHERE
-- [CashRegisterId] = @Id
--UPDATE [dbo].[tbDocumentModelByCashRegisters]
--SET
-- [DocumentModelId] = @DocumentModelId
-- ,[CashRegisterId] = @Id
--WHERE
-- [CashRegisterId] = @Id
Commit
End Try
begin Catch
rollback
End Catch
END
END
GO
Methods for Split the String:
Option 1:
USE [Applica.Acc]
GO
/****** Object: UserDefinedFunction [dbo].[splitstring] Script Date: 16/02/2018 12:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[splitint] ( @stringToSplit nvarchar(MAX) )
RETURNS
@returnList TABLE ([id] [int])
AS
BEGIN
DECLARE @id int
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @id = CAST(SUBSTRING(@stringToSplit, 1, @pos-1) as int)
INSERT INTO @returnList
SELECT @id AS [int]
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
Option 2:
CREATE FUNCTION dbo.SplitInts
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN (
SELECT Item = CONVERT(INT, Item) FROM
( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
FROM
( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
WHERE Item IS NOT NULL
);
GO