SQL Always Encrypted Stored Proc params not being encrypted in C# - c#

I have encrypted some columns in my db, but when executing a stored proc for my .Net app that selects from that table I get an error. Executing the SP from SSMS works fine. I did add 'column encryption setting=Enabled' to my connection string. I'm sure that I'm just missing something obvious.
I get the following error:
'Operand type clash: nvarchar is incompatible with nvarchar(64)
encrypted with (encryption_type = 'DETERMINISTIC',
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'UserFields')Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.'
The SP:
ALTER PROCEDURE [dbo].[GetUserId]
#Username NVARCHAR (64)
AS
BEGIN
SET NOCOUNT ON;
DECLARE #AccountID AS INT;
SELECT TOP 1 #AccountID = [Users].[AccountID]
FROM [Users]
WHERE [Users].[Username] = #Username
END
Code:
string connectionStringName = "ConnectionString";
string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("#Username", "admin"));
command.CommandText = "GetUserId";
command.CommandType = CommandType.StoredProcedure;
if (parameters != null && parameters.Any())
command.Parameters.AddRange(parameters.ToArray());
using (IDataReader dataReader = command.ExecuteReader())
{
if (dataReader.Read())
{
//Extract data
}
}
}
}
Connection string:
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=MyDb;integrated security=True;Column Encryption Setting=enabled;" />

Related

Getting return value from stored procedure in C#

I have the following query:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
#a varchar(50),
#b varchar(50) output
AS
SET #Password =
(SELECT Password
FROM dbo.tblUser
WHERE Login = #a)
RETURN #b
GO
This compiles perfectly fine.
In C#, I want to execute this query and get the return value.
My code is as below:
SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("#a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter retval = sqlcomm.Parameters.Add("#b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
string retunvalue = (string)sqlcomm.Parameters["#b"].Value;
Note: Exception handling cut to keep the code short. Everytime I get to the last line, null is returned. What's the logic error with this code?
Thanks
Mehrdad makes some good points, but the main thing I noticed is that you never run the query...
SqlParameter retval = sqlcomm.Parameters.Add("#b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["#b"].Value;
retval.Direction = ParameterDirection.Output;
ParameterDirection.ReturnValue should be used for the "return value" of the procedure, not output parameters. It gets the value returned by the SQL RETURN statement (with the parameter named #RETURN_VALUE).
Instead of RETURN #b you should SET #b = something
By the way, return value parameter is always int, not string.
I was having tons of trouble with the return value, so I ended up just selecting stuff at the end.
The solution was just to select the result at the end and return the query result in your functinon.
In my case I was doing an exists check:
IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE #RoleName = RoleName))
SELECT 1
ELSE
SELECT 0
Then
using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RoleExists";
return (int) cmd.ExecuteScalar()
}
You should be able to do the same thing with a string value instead of an int.
This is building on Joel's and Mehrdad's answers: you're never binding the parameter of the retval to the sqlcommand. You need a
sqlcomm.Parameters.Add(retval);
and to make sure you're running the command
sqlcomm.ExecuteNonQuery();
I'm also not sure why you have 2 return value strings (returnValue and retunvalue).
You say your SQL compiles fine, but I get: Must declare the scalar variable "#Password".
Also you are trying to return a varchar (#b) from your stored procedure, but SQL Server stored procedures can only return integers.
When you run the procedure you are going to get the error:
'Conversion failed when converting the varchar value 'x' to data type int.'
This SP looks very strange. It does not modify what is passed to #b. And nowhere in the SP you assign anything to #b. And #Password is not defined, so this SP will not work at all.
I would guess you actually want to return #Password, or to have SET #b = (SELECT...)
Much simpler will be if you modify your SP to (note, no OUTPUT parameter):
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Validate] #a varchar(50)
AS
SELECT TOP 1 Password FROM dbo.tblUser WHERE Login = #a
Then, your code can use cmd.ExecuteScalar, and receive the result.
May be this will help.
Database script:
USE [edata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertNewUser](
#neuname NVARCHAR(255),
#neupassword NVARCHAR(255),
#neuposition NVARCHAR(255)
)
AS
BEGIN
BEGIN TRY
DECLARE #check INT;
SET #check = (SELECT count(eid) FROM eusers WHERE euname = #neuname);
IF(#check = 0)
INSERT INTO eusers(euname,eupassword,eposition)
VALUES(#neuname,#neupassword,#neuposition);
DECLARE #lastid INT;
SET #lastid = ##IDENTITY;
RETURN #lastid;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() as errline,
ERROR_MESSAGE() as errmessage,
ERROR_SEVERITY() as errsevirity
END CATCH
END
Application configuration file:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="conStr" value="Data Source=User\SQLEXPRESS;Initial Catalog=edata;Integrated Security=True"/>
</appSettings>
</configuration>
Data Access Layer (DAL):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public static class DAL
{
public static SqlConnection conn;
static DAL()
{
conn = new SqlConnection(ConfigurationManager.AppSettings["conStr"].ToString());
conn.Open();
}
}
}
Business Logic Layer(BLL):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DAL;
namespace BLL
{
public static class BLL
{
public static int InsertUser(string lastid, params SqlParameter[] coll)
{
int lastInserted = 0;
try
{
SqlCommand comm = new SqlCommand();
comm.Connection = DAL.DAL.conn;
foreach (var param in coll)
{
comm.Parameters.Add(param);
}
SqlParameter lastID = new SqlParameter();
lastID.ParameterName = lastid;
lastID.SqlDbType = SqlDbType.Int;
lastID.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(lastID);
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "InsertNewUser";
comm.ExecuteNonQuery();
lastInserted = (int)comm.Parameters[lastid].Value;
}
catch (SqlException ex)
{
}
finally {
if (DAL.DAL.conn.State != ConnectionState.Closed) {
DAL.DAL.conn.Close();
}
}
return lastInserted;
}
}
}
Implementation :
BLL.BLL.InsertUser("#lastid",new SqlParameter("neuname","Ded"),
new SqlParameter("neupassword","Moro$ilka"),
new SqlParameter("neuposition","Moroz")
);
There are multiple problems here:
It is not possible. You are trying to return a varchar. Stored
procedure return values can only be integer expressions. See
official RETURN documentation:
https://msdn.microsoft.com/en-us/library/ms174998.aspx.
Your sqlcomm was never executed. You have to call
sqlcomm.ExecuteNonQuery(); in order to execute your command.
Here is a solution using OUTPUT parameters. This was tested with:
Windows Server 2012
.NET v4.0.30319
C# 4.0
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Validate]
#a varchar(50),
#b varchar(50) OUTPUT
AS
BEGIN
DECLARE #b AS varchar(50) = (SELECT Password FROM dbo.tblUser WHERE Login = #a)
SELECT #b;
END
SqlConnection SqlConn = ...
var sqlcomm = new SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("#a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter output = sqlcomm.Parameters.Add("#b", SqlDbType.VarChar);
ouput.Direction = ParameterDirection.Output;
sqlcomm.ExecuteNonQuery(); // This line was missing
returnValue = output.Value.ToString();
// ... the rest of code
} catch (SqlException ex) {
throw ex;
}
You have mixed up the concept of the Return Value and Output variable.
1- Output Variable:
Database----->:
create proc MySP
#a varchar(50),
#b varchar(50) output
AS
SET #Password =
(SELECT Password
FROM dbo.tblUser
WHERE Login = #a)
C# ----->:
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("#a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;//This is optional because Input is the default
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter outputval = sqlcomm.Parameters.Add("#b", SqlDbType.VarChar);
outputval .Direction = ParameterDirection.Output//NOT ReturnValue;
string outputvalue = sqlcomm.Parameters["#b"].Value.ToString();
There are two things to fix about this. First set up the stored procedure to store the value in the output ( not return ) parameter.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
#a varchar(50),
#b varchar(50) output
AS
SET #b =
(SELECT Password
FROM dbo.tblUser
WHERE Login = #a)
RETURN
GO
This will but the password into #b and you will get it as a return parameter. Then to get it in your C# do this:
SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("#a", SqlDbType.VarChar, 50);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter retval = new SqlParameter("#b", SqlDbType.VarChar, 50);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.Parameters.Add(retval);
sqlcomm.ExecuteNonQuery();
SqlConn.Close();
string retunvalue = retval.Value.ToString();
}
When you use
cmd.Parameters.Add("#RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
you must then ensure your stored procedure has
return #RETURN_VALUE;
at the end of the stored procedure.
When we return a value from Stored procedure without select statement.
We need to use "ParameterDirection.ReturnValue" and "ExecuteScalar" command to get the value.
CREATE PROCEDURE IsEmailExists
#Email NVARCHAR(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(SELECT Email FROM Users where Email = #Email)
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 1
END
END
in C#
GetOutputParaByCommand("IsEmailExists")
public int GetOutputParaByCommand(string Command)
{
object identity = 0;
try
{
mobj_SqlCommand.CommandText = Command;
SqlParameter SQP = new SqlParameter("returnVal", SqlDbType.Int);
SQP.Direction = ParameterDirection.ReturnValue;
mobj_SqlCommand.Parameters.Add(SQP);
mobj_SqlCommand.Connection = mobj_SqlConnection;
mobj_SqlCommand.ExecuteScalar();
identity = Convert.ToInt32(SQP.Value);
CloseConnection();
}
catch (Exception ex)
{
CloseConnection();
}
return Convert.ToInt32(identity);
}
We get the returned value of SP "IsEmailExists" using above c# function.
Suppose you need to pass Username and Password to Stored Procedure and know whether login is successful or not and check if any error has occurred in Stored Procedure.
public bool IsLoginSuccess(string userName, string password)
{
try
{
SqlConnection SQLCon = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlConnector"].ConnectionString);
SqlCommand sqlcomm = new SqlCommand();
SQLCon.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.CommandText = "spLoginCheck"; // Stored Procedure name
sqlcomm.Parameters.AddWithValue("#Username", userName); // Input parameters
sqlcomm.Parameters.AddWithValue("#Password", password); // Input parameters
// Your output parameter in Stored Procedure
var returnParam1 = new SqlParameter
{
ParameterName = "#LoginStatus",
Direction = ParameterDirection.Output,
Size = 1
};
sqlcomm.Parameters.Add(returnParam1);
// Your output parameter in Stored Procedure
var returnParam2 = new SqlParameter
{
ParameterName = "#Error",
Direction = ParameterDirection.Output,
Size = 1000
};
sqlcomm.Parameters.Add(returnParam2);
sqlcomm.ExecuteNonQuery();
string error = (string)sqlcomm.Parameters["#Error"].Value;
string retunvalue = (string)sqlcomm.Parameters["#LoginStatus"].Value;
}
catch (Exception ex)
{
}
return false;
}
Your connection string in Web.Config
<connectionStrings>
<add name="SqlConnector"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Databasename;User id=yourusername;Password=yourpassword"
providerName="System.Data.SqlClient" />
</connectionStrings>
And here is the Stored Procedure for reference
CREATE PROCEDURE spLoginCheck
#Username Varchar(100),
#Password Varchar(100) ,
#LoginStatus char(1) = null output,
#Error Varchar(1000) output
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN
SET #Error = 'None'
SET #LoginStatus = ''
IF EXISTS(SELECT TOP 1 * FROM EMP_MASTER WHERE EMPNAME=#Username AND EMPPASSWORD=#Password)
BEGIN
SET #LoginStatus='Y'
END
ELSE
BEGIN
SET #LoginStatus='N'
END
END
END TRY
BEGIN CATCH
BEGIN
SET #Error = ERROR_MESSAGE()
END
END CATCH
END
GO

Database is not updating after running stored procedure in asp app

I have an asp app that should be able to let a user update an entry in a sql server database. When I run my stored procedure though in my method, nothing happens to the database.
public void updateFeature(Feature updatedFeature)
{
using (SqlConnection sqlConnection = new SqlConnection(GetConnectionString(updatedFeature.Environment)))
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_UpdateFeature";
sqlCommand.Parameters.AddWithValue("#FeatureName", updatedFeature.FeatureName);
sqlCommand.Parameters.AddWithValue("#FeatureID", updatedFeature.FeatureID);
sqlCommand.Parameters.AddWithValue("#FeatureDescription", updatedFeature.Description);
sqlCommand.Parameters.AddWithValue("#FieldName", updatedFeature.FieldName);
sqlCommand.Parameters.AddWithValue("#OnOffSwitch", updatedFeature.IsOn);
sqlCommand.Parameters.AddWithValue("#ChannelID", updatedFeature.ChannelID);
sqlCommand.Parameters.AddWithValue("#ProductID", updatedFeature.ProductID);
sqlConnection.Open();
int numberOfRowsChanged = sqlCommand.ExecuteNonQuery();
}
}
and my stored procedure looks like this
ALTER PROCEDURE [dbo].[usp_UpdateFeature]
(
#FeatureID int,
#FeatureName varchar(30),
#FeatureDescription varchar(200) = null,
#FieldName varchar(40),
#OnOffSwitch bit,
#ChannelID varchar(3),
#ProductID varchar(2)
)
AS
SET NOCOUNT ON
UPDATE FM_Feature
SET FeatureName = #FeatureName,
FeatureDescription = #FeatureDescription,
FieldName = #FieldName,
OnOffSwitch = #OnOffSwitch,
ProductID = #ProductID,
ChannelID = #ChannelID
WHERE FeatureID = #FeatureID
The numberOfRowsChanged parameter returns -1 but I do not get any exceptions or errors. Is there something that I am missing or not understanding?
I think that you need to open your connection before you create your command, i hope that will be helpful for you .
using (SqlConnection sqlConnection = new SqlConnection(GetConnectionString(updatedFeature.Environment))
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_UpdateFeature";
sqlCommand.Parameters.AddWithValue("#FeatureName", updatedFeature.FeatureName);
sqlCommand.Parameters.AddWithValue("#FeatureID", updatedFeature.FeatureID);
sqlCommand.Parameters.AddWithValue("#FeatureDescription", updatedFeature.Description);
sqlCommand.Parameters.AddWithValue("#FieldName", updatedFeature.FieldName);
sqlCommand.Parameters.AddWithValue("#OnOffSwitch", updatedFeature.IsOn);
sqlCommand.Parameters.AddWithValue("#ChannelID", updatedFeature.ChannelID);
sqlCommand.Parameters.AddWithValue("#ProductID", updatedFeature.ProductID);
int numberOfRowsChanged = sqlCommand.ExecuteNonQuery();
}
enjoy it .

Operand type clash: varchar is incompatible with varchar(50) trying to insert in encrypted database

I am getting a SqlException:
Operand type clash: varchar is incompatible with varchar(50) encrypted
with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name =
'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name =
'CEK_Auto1', column_encryption_key_database_name = 'PB')
collation_name = 'SQL_Latin1_General_CP1_CI_AS'\r\nIncorrect parameter
encryption metadata was received from the client. The error occurred
during the invocation of the batch and therefore the client can
refresh the parameter encryption metadata by calling
sp_describe_parameter_encryption and retry.
My C# code:
using (var connection = new SqlConnection(GetConnectionString()))
{
using (var cmd = new SqlCommand("Clients_Insert", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("#Email", SqlDbType.VarChar, 50).Value = client.Email;
cmd.Parameters.Add("#ContactPerson", SqlDbType.VarChar, 400).Value = client.ContactPerson;
connection.Open();
cmd.ExecuteNonQuery();
}
}
And my stored procedure:
ALTER PROCEDURE [dbo].[Clients_Insert]
#Email VARCHAR(50),
#ContactPerson VARCHAR(400)
AS
BEGIN
INSERT into dbo.Clients(Email, ContactPerson)
VALUES (#Email, #ContactPerson);
SELECT SCOPE_IDENTITY();
END;
I have no problems with inserting data into not encrypted fields.
I found this article
http://dataap.org/sql-2016-ctp/column-level-encryption-using-always-encrypted-in-sql-server-2016/
My issue is similiar but i haven't found the solution.
There are 2 things you can try,
Ensure that Column encryption setting is enabled in your connection string. This can be done using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled as follows
strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;
If your stored procedure was created before you encrypted your column, you will need to refresh metadata for your stored procedure as follows
Use [Database]
GO
--Do this for all stored procedures
EXEC sys.sp_refresh_parameter_encryption #name = '[dbo].[Clients_Insert]'

Programmatically get Windows Services and write to SQL

I am trying to query services on a remote server and insert them into SQL. I know I am missing something that will actually write this back but I cannot figure out what it is. Everything works until I try to write the data to SQL.
static void Main(string[] args)
{
string username = ConfigurationManager.AppSettings["UserName"].ToString();
string password = ConfigurationManager.AppSettings["Password"].ToString();
string domain = ConfigurationManager.AppSettings["Domain"].ToString();
var logonType = SimpleImpersonation.LogonType.NewCredentials;
using (SimpleImpersonation.Impersonation.LogonUser(domain, username, password, logonType))
{
var connection = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connection))
{
conn.Open();
using (SqlCommand command = new SqlCommand("SELECT ServerName, ServerIP FROM Server", conn))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string serverIP = reader["ServerIP"].ToString();
string serverName = reader["ServerName"].ToString();
ServiceController[] services = ServiceController.GetServices(serverIP);
foreach (ServiceController service in services)
{
using (SqlCommand cmd = new SqlCommand("sp_InsertServices", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter ID = new SqlParameter();
ID.ParameterName = "#ID";
ID.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.AddWithValue("#ID", ID);
cmd.Parameters.AddWithValue("#Display", service.DisplayName);
cmd.Parameters.AddWithValue("#Service", service.ServiceName);
cmd.Parameters.AddWithValue("#Status", service.Status);
cmd.Parameters.AddWithValue("#Server", serverName);
}
}
}
}
}
}
}
Here is the Stored Proc
PROCEDURE [dbo].[sp_InsertServices]
#ID int,
#Display nvarchar(50),
#Service nvarchar(50),
#Status nvarchar(50),
#Server nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Services
(ID,
DisplayName,
ServiceName,
Status,
Server)
VALUES
(#ID,
#Display,
#Service,
#Status,
#Server)
SET #ID = SCOPE_IDENTITY()
END
EDIT: Sorry some more info. I have added an execute at the end after the param adds and I get this error.
An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
Additional information: There is already an open DataReader associated with this Command which must be closed first.
Looks like I need to close the first reader but I still use it in the second command. Don't now where I can safely close it.
You never execute your SqlCommand. After you add your parameters, try adding cmd.ExecuteNonQuery();
The 2nd problem is that you are trying to reuse the same SqlConnection. You'll need to create a new SqlConnection since you are using your existing connection to loop through the list of servers.
You've built up the Command object (cmd), now you just need to save it, like cmd.ExecuteNonQuery();
First this you need to change the stored procedure declaration as
ALTER PROCEDURE [dbo].[sp_InsertServices]
#Display nvarchar(50),
#Service nvarchar(50),
#Status nvarchar(50),
#Server nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Services
(DisplayName, ServiceName, Status, Server)
VALUES
(#Display,#Service,#Status,#Server)
SELECT SCOPE_IDENTITY()
END
And now change the calling code to
using (SqlCommand cmd = new SqlCommand("sp_InsertServices", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("#Display", service.DisplayName);
cmd.Parameters.AddWithValue("#Service", service.ServiceName);
cmd.Parameters.AddWithValue("#Status", service.Status);
cmd.Parameters.AddWithValue("#Server", serverName);
int serviceID = (int)cmd.ExecuteScalar();
// What you want to do with the ID returned?
}
The ID parameter is not needed if your ID field is an IDENTITY column, it is automatically calculated by the database and you could return it using a simple select. If your stored procedure ends with a SELECT SCOPE_IDENTITY() the result can be retrieved on the C# code using a simple ExecuteScalar without the need of an output parameter.
Finally, if you try to ExecuteXXX an SqlCommand using the same connection in use by a SqlDataReader you get an exception because the connection is busy serving the SqlDataReader and cannot execute the command.
The simple solution is to add this to your connection string
....;MultipleActiveResultSets=True;....
See MSDN for MultipleActiveResultSets

The stored procedure doesn't exist when calling SqlCommandBuilder.DeriveParameters

I'm tring to get the parameters from a stored procedure but I am getting the error of
The stored procedure 'Generic.proc_UpdateGenericCatalog' doesn't exist.
The namespace Generic. is causing the error but I do not know which setting I should use to get beyond this error.
--My Procedure--
ALTER proc [Generic].[proc_UpdateGenericCatalog]
#UserID UNIQUEIDENTIFIER,
#Name VARCHAR(30),
#SupplierName VARCHAR(30),
#SupplierEmail VARCHAR(50),
#SupplierPhone VARCHAR(12),
#GenericCatalogID INT
AS
UPDATE [Generic].[GenericCatalog]
SET [UserID] = #UserID
,[Name] = #Name
,[SupplierName] = #SupplierName
,[SupplierEmail] = #SupplierEmail
,[SupplierPhone] = #SupplierPhone
WHERE ID = #GenericCatalogID
--The C# code I'm using to get the parameter info--
--The error is right here SqlCommandBuilder.DeriveParameters(cmd);
How should I alter my code below so that I can get the .DeriveParameter info?
private static SqlParameter[] DiscoverParameters(string connectionString, string spName)
{
SqlCommand cmd = null;
SqlParameter[] discoveredParameters = null;
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
conn.Close();
discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
cmd.Dispose();
}
}
catch (Exception) { throw; }
return discoveredParameters;
}
The most likely explanation is that the user specified in your connection string doesn't have permission to execute the Generic.proc_UpdateGenericCatalog procedure. I've just tested on one of my databases, and if the user doesn't have EXEC permission you'll get the "stored procedure doesn't exist" error.
Also, you should probably be cloning the parameters, rather than just copying them to an array. This is quite easy to do with a bit of LINQ:
private static SqlParameter[] DiscoverParameters(string connectionString, string spName)
{
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(spName, connection))
{
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(command);
connection.Close();
return command.Parameters
.Cast<ICloneable>()
.Select(p => p.Clone())
.Cast<SqlParameter>()
.ToArray();
}
}

Resources