I've done some Googling, but I can't seem to find an answer to what I'm looking for. Maybe my search terms are off. Here is my situation:
I have a stored procedure in my database that currently takes in and utilizes 11 parameters (all working great). I need to add a new parameter to this for a new column I added. We always explicitly define our columns in code, so there was no issue adding a column to the end of the table. However, if I add a new parameter in my stored procedure to populate this new column, will it throw an error back to my C# code if it isn't supplied, or will it default to null (or some other value) for the parameter?
Example C# code to call SP:
public static void InsertMailLog(string messageId, DateTime sentOrReceivedDate,
string fromAddress, string toAddress, string subject, string receivedMessage, string tailNumber,
string messageType, string direction, string sentOrReceived, string distributionList, ILogger AppEventLog, string filename = null)
{
List<string> lstParameterValues = new List<string>();
try
{
lstParameterValues.Add(messageId ?? "");
lstParameterValues.Add(sentOrReceivedDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
lstParameterValues.Add(fromAddress ?? "");
lstParameterValues.Add(toAddress);
lstParameterValues.Add(subject ?? "");
lstParameterValues.Add(receivedMessage ?? "");
lstParameterValues.Add(tailNumber ?? "");
lstParameterValues.Add(messageType ?? "");
lstParameterValues.Add(direction ?? "");
lstParameterValues.Add(sentOrReceived ?? "");
lstParameterValues.Add(distributionList ?? "");
lstParameterValues.Add(filename ?? ""); //THIS IS NEW, but it has not been published yet as the SP hasn't been updated.
CommonDAL.ExecSpNonQuery("spMailLogInsert", lstParameterValues);
}
catch (Exception ex)
{
CommonBLL.LogError(ex, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, "Error", messageId, tailNumber, messageType, "", Settings.Default.ContentProvider, AppEventLog);
}
}
Example SP:
ALTER PROCEDURE [dbo].[spMailLogInsert]
@SdMessageId varchar(50),
@SentOrReceivedDate datetime,
@FromAddress varchar(100),
@ToAddress varchar(100),
@Subject varchar(255),
@Message varchar(MAX),
@TailNumber varchar(50),
@MessageType varchar(50),
@Direction varchar(50),
@SentOrReceived varchar(50),
@DistributionList varchar(50),
@Filename varchar(50) --THIS IS NEW
AS
SET NOCOUNT ON
INSERT MailLog (
SdMessageId,
SentOrReceivedDate,
FromAddress,
ToAddress,
[Subject],
[Message],
TailNumber,
MessageType,
Direction,
SentOrReceived,
DistributionList,
Filename --THIS IS NEW
) VALUES (
@SdMessageId,
@SentOrReceivedDate,
@FromAddress,
@ToAddress,
@Subject,
@Message,
@TailNumber,
@MessageType,
@Direction,
@SentOrReceived,
@DistributionList,
@Filename --THIS IS NEW
)
I completely understand that this is a terrible use of a stored procedure. I should be using Entity Framework, but it's already written, and I have a project to update the entire project to use EF in the DAL at a later date (This is very old code). My question is, if I add the new parameter "Filename" to the stored procedure BEFORE the new C# code above gets published, will I get an error, or will the SP simply default to NULL? Or, if someone has a better way to default this to NULL or empty string, if it isn't supplied, I'm all ears.
Aucun commentaire:
Enregistrer un commentaire