C# Configuration Manager Connection String

Question

So I am trying to do an application that makes a simple transaction from account 1 to account 2

here is the code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Transactions;

namespace BankTransactions
{
    public class Transactions
    {
        public bool Transfer(int fromAcno, int toAcno, decimal amt)
        {
            try
            {
                string cs = ConfigurationManager.ConnectionStrings["Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = NORTHWND; Data Source = CRISTI"].ConnectionString;
                SqlConnection cn = new SqlConnection(cs);
                cn.Open();
                SqlTransaction trans;
                trans = cn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.Transaction = trans;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("update PSBank set amount=amount+{0} where accountno={1}", amt, toAcno);
                cmd.ExecuteNonQuery();
                cmd.CommandText = String.Format("update PSBank set amount=amount-{0} where accountno={1}", amt, fromAcno);
                cmd.ExecuteNonQuery();
                decimal balance;
                balance = (decimal)cmd.ExecuteScalar();
                decimal originalBalance;
                originalBalance = balance - amt;
                bool flag = true;
                if (originalBalance >= 5000)
                {
                    trans.Commit();
                    flag = true;
                }

                else
                {
                    trans.Rollback();
                    flag = false;
                }

                cn.Close();
                return flag;
            }

            catch (Exception ex)
            {
                return false;
            }
        }


    }
}

and here is the winform code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using BankTransactions;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            int fromAcno = int.Parse(txtFromAcno.Text);
            int toAcno = int.Parse(txtToAcno.Text);
            decimal amount = decimal.Parse(txtAmount.Text);

            Transactions obj = new Transactions();
            if (obj.Transfer(fromAcno, toAcno, amount) ==true)
            {
                MessageBox.Show("Amount transferred succesfully!", "Confirm");
            }
            else
            {
                MessageBox.Show("Error!", "Error");
            }
        }
    }
}

The form has 3 text boxes: txtFromAcno, txtToAcno and txtAmount. I have the database called NORTHWND with the table PSBank where I have these two accounts. I am getting the error message, not "amount transferred successfully". I am not sure how to make the connection to the DB in the connection string.

  string cs = ConfigurationManager.ConnectionStrings["Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = NORTHWND; Data Source = CRISTI"].ConnectionString;

How do I format it properly?

Answer 1

Put the connection string in app.config like so:

<connectionStrings>
    <add name="MyConnection" 
        connectionString="Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = NORTHWND; Data Source = CRISTI"
        providerName="System.Data.SqlClient" >
</connectionStrings>

And then reference it by name in code:

string cs = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;