CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-web-apiconnection-string
trx
trx

Reputation: 2167

Issue connecting to the SQL server from Web API

I have the below Controller that gets the account as the input parameter, which connects to the Sql server and will have to call the stored procedure passing account.The stored procedure inserts a new record if the Account is not present and updates it when already in there

        string strcon = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
       SqlConnection DbConnection = new SqlConnection(strcon);
       DbConnection.Open();
       SqlCommand command = new SqlCommand("[dbo].[usp_InserUpadte]", DbConnection);
       command.CommandType = CommandType.StoredProcedure;

       //create type table
       DataTable table = new DataTable();
       table.Columns.Add("AccountID", typeof(string));
       table.Rows.Add(Account);

       SqlParameter parameter = command.Parameters.AddWithValue("@Account_TT", table);
       parameter.SqlDbType = SqlDbType.Structured;
       parameter.TypeName = "Account_TT";

Below is the ConnectionString in web.config

<connectionStrings>
 <add name="DBConnection"
providerName="System.Data.SqlClient"
connectionString="Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True" />
</connectionStrings>

The stored procedure is like

ALTER PROCEDURE [dbo].[usp_InserUpadte] 
@account_TT AS account_TT READONLY

AS
BEGIN
SET NOCOUNT ON;

BEGIN TRANSACTION;

 MERGE dbo.[Account] prj
 USING @account_TT tt
 ON prj.AccountID = tt.AccountID
 WHEN MATCHED THEN UPDATE SET prj.CounterSeq = prj.CounterSeq+1
 WHEN NOT MATCHED THEN INSERT (AccountID,CounterSeq)
 VALUES (tt.AccountID, 1);

 COMMIT TRANSACTION;

 END;

where the table type is created with 

CREATE TYPE account_TT AS TABLE
(
 AccountID     nvarchar(50),
 )
 GO

When I try to call the API it doesnot throw any exception but neither creates/update any records with the stored procedure. I tried to debug the adding breakpoints. I see the in 

DbConnection.Open();

enter image description here Looks like the connection is not opened. I am able to connect to the SQL server from the same server I am working on though SQL Management Studio. Can anyone please suggest me what could be the issue.

Upvotes: 1

Views: 215

Answers (2)

Mohammad
Mohammad

Reputation: 2764

you didn't connect to data base correct. try this:

        using (var connection = new SqlConnection(ConnectionString))
        using (var command = new SqlCommand(commandText, connection) { CommandTimeout = 160, CommandType = commandType })
        using (var dataAdaptor = new SqlDataAdapter(command))
        {
            command.Parameters.AddRange(parameters);

            connection.Open();
            dataAdaptor.Fill(dS);
        }

Upvotes: 1

Kalyan
Kalyan

Reputation: 1200

You probably have open connection.Correct way of opening connection

string strcon = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

using(SqlConnection dbConnection = new SqlConnection(strcon))
    {
      if (dbConnection.State==ConnectionState.Closed)
      {                      
          con.Open();   
      }
    }

Upvotes: 2

Related Questions