Web.config file for get connection

Question

1.00/5 (3 votes)

See more:

The ConnectionString property has not been initialized- This is an exception.

My code:

XML

<configuration>



  <connectionStrings>
    <add name="SqlConnectionString" connectionString="server=TRES-LED-2\SQLEXPRESS;persist security info=true;database =dbSCBL;user=sa;password=database;connect Timeout=5"/>

  </connectionStrings>

My C# code is :

C#

SqlDBAccess db = new SqlDBAccess();
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            db.command.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(txtid.Text.ToString());
            db.command.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text.ToString();
            db.Adapter("InsertTest", true);
            
        }
        catch (Exception ex)
        {
            lblConfirm.Visible = true;
            lblConfirm.Text = ex.Message.ToString();
        }
    }

My SqlDBAccess class is :

C#

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace DatabaseOperation
{
    public class SqlDBAccess
    {

        #region Variables
        private string _connectionString;
        private string _databaseName;
        private string _serverName;
        private string _userName;
        private string _password;
        public SqlConnection connection = new SqlConnection();
        public SqlCommand command = new SqlCommand();
        private SqlDataAdapter adapter = new SqlDataAdapter();
        private SqlDataReader reader;
        public DataSet dataSet = new DataSet();
        private DataTable dataTable = new DataTable();
        private SqlCommandBuilder commandBld = new SqlCommandBuilder();
        public SqlTransaction transaction = null;
        private Object obj;
        #endregion
        #region Properties
        public string ConnectionString
        {
            set
            {
                _connectionString = value;

            }
            get
            {
                return _connectionString;
            }
        }
        public string DatabaseName
        {
            set
            {
                _databaseName = value;
                connection.ChangeDatabase(_databaseName);
            }
            get
            {
                return _databaseName;
            }
        }
        public string ServerName
        {
            set
            {
                _serverName = value;
            }
            get
            {
                return _serverName;
            }
        }
        public string UserName
        {
            set
            {
                _userName = value;
            }
            get
            {
                return _userName;
            }
        }
        public string Password
        {
            set
            {
                _password = value;
            }
            get
            {
                return _password;
            }
        }
        #endregion
        #region Constructor
        public SqlDBAccess()
        {
            _connectionString = ConfigurationSettings.AppSettings["Server"];         //"server=(local);database = db_IFRMS;persist Security Info=False;user=sa;password=@pwd99$02!;connect Timeout=5";
            //DecriptString();
        }
        public SqlDBAccess(string conStr)
        {
            _connectionString = conStr;
            //DecriptString();
        }
        #endregion
        #region Security Methods
        private void DecriptString()
        {
            string[] parts = _connectionString.Split(';');
            string[] words = parts[0].Split('=');
            _serverName = words[1].Trim();
            words = parts[1].Split('=');
            _databaseName = words[1].Trim();
            words = parts[3].Split('=');
            _userName = words[1].Trim();
            words = parts[4].Split('=');
            _password = words[1].Trim();

        }
        #endregion
        #region Connection Method
        public void OpenConnection()
        {
            try
            {
                if (!(connection.State.ToString() == "Open"))
                {
                    connection.ConnectionString = _connectionString;
                    connection.Open();
                }
            }
            catch (SqlException ex)
            {
                Log("Error in open connection", ex);
                throw ex;
            }
            catch (Exception ex)
            {
                Log("Error in open connection", ex);
                throw ex;

            }
        }
        public void CloseConnection()
        {
            try
            {
                if (connection.State.ToString() == "Open")
                    connection.Close();
            }
            catch (SqlException ex)
            {
                Log("Error in close connection", ex);
                throw new ApplicationException("Error in close connection", ex);

            }
            catch (Exception ex)
            {
                Log("Error in close connection", ex);
                throw new ApplicationException("Error in close connection", ex);
            }
        }
        #endregion
        #region ExecuteNonQuery
        public int ExecuteNonQuery(string sqlStmt)
        {
            OpenConnection();
            int rowCount = 0;
            try
            {
                transaction = connection.BeginTransaction();
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.Transaction = transaction;
                rowCount = command.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (SqlException ex)
            {
                if (transaction != null)
                    transaction.Rollback();
                Log("Error in ExecuteQuery", ex);
                throw new ApplicationException(ex.Message);

            }
            catch (Exception ex)
            {
                if (transaction != null)
                    transaction.Rollback();
                Log("Error in ExecuteQuery", ex);
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
            return rowCount;
        }
        public int ExecuteNonQuery(string sqlStmt, bool IsStoredProcedure)
        {
            OpenConnection();
            int rowCount = 0;
            try
            {
                transaction = connection.BeginTransaction();
                command.CommandText = sqlStmt;
                command.Connection = connection;
                if (IsStoredProcedure)
                    command.CommandType = CommandType.StoredProcedure;
                else
                    command.CommandType = CommandType.Text;
                command.Transaction = transaction;
                rowCount = command.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (SqlException ex)
            {
                if (transaction != null)
                    transaction.Rollback();
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
                command.Parameters.Clear();
            }
            return rowCount;

        }
        public int ExecuteMulNonQuery(string sqlStmt)
        {
            int rowCount = 0;
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                rowCount = command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            return rowCount;

        }
        #endregion
        #region Execute Reader Methods
        public SqlDataReader ExecuteReader(string sqlStmt)
        {
            try
            {
                OpenConnection();
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                reader = command.ExecuteReader();

            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);

            }
            return reader;

        }
        public SqlDataReader ExecuteReader(string sqlStmt, bool IsStoredProcedure)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                if (IsStoredProcedure)
                    command.CommandType = CommandType.StoredProcedure;
                else
                    command.CommandType = CommandType.Text;
                reader = command.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            return reader;
        }
        public Object ExecuteScalar(string sqlStmt)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                obj = command.ExecuteScalar();

            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return obj;
        }
        #endregion Reader Methods
        #region Adapter Methods
        public DataTable Adapter(string sqlStmt)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                if (dataTable != null)
                    dataTable.Clear();
                adapter.Fill(dataTable);
                commandBld.DataAdapter = adapter;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return dataTable;
        }
        public DataTable Adapter(string sqlStmt, bool IsStoredProcedure)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                if (IsStoredProcedure)
                    command.CommandType = CommandType.StoredProcedure;
                else
                    command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                if (dataTable != null)
                    dataTable.Clear();
                adapter.Fill(dataTable);
                commandBld.DataAdapter = adapter;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
                command.Parameters.Clear();
            }
            return dataTable;
        }
        public void Adapter(string sqlStmt, string tableName, bool IsStoredProcedure)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                if (IsStoredProcedure)
                    command.CommandType = CommandType.StoredProcedure;
                else
                    command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                if (dataSet.Tables[tableName] != null)
                    dataSet.Tables[tableName].Clear();
                adapter.Fill(dataSet, tableName);
                commandBld.DataAdapter = adapter;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
                command.Parameters.Clear();
            }
        }
        public void Adapter(string sqlStmt, string tableName)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                if (dataSet.Tables[tableName] != null)
                    dataSet.Tables[tableName].Clear();
                adapter.Fill(dataSet, tableName);
                commandBld.DataAdapter = adapter;
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
            }
        }
        public void Adapter(string sqlStmt, DataTable table)
        {

            try
            {
                OpenConnection();
                command.CommandText = sqlStmt;
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                adapter.Fill(table);
                commandBld.DataAdapter = adapter;

            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);

            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);

            }
            finally
            {
                CloseConnection();
            }
        }
        public void Adapter(string sqlStmt, DataTable table, bool IsStoredProcedure)
        {
            OpenConnection();
            try
            {
                command.CommandText = sqlStmt;
                command.Connection = connection;
                adapter.SelectCommand = command;
                if (IsStoredProcedure)
                    command.CommandType = CommandType.StoredProcedure;
                else
                    command.CommandType = CommandType.Text;
                adapter.Fill(table);
                commandBld.DataAdapter = adapter;

            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);

            }
            finally
            {
                CloseConnection();
                command.Parameters.Clear();
            }
        }
        public void DataSetToDB(DataTable table)
        {
            OpenConnection();
            try
            {
                adapter.Update(table);
                dataSet.AcceptChanges();
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion Adapter Methods
        #region Error Log Methods
        public static void Log(string Message)
        {
            Log(Message, null);
        }
        public static void Log(string Message, Exception Ex)
        {
            string fileName = Environment.CurrentDirectory + "\\" + "Error.log";
            using (StreamWriter logFile = new StreamWriter(fileName, true))
            {
                logFile.WriteLine("{0}: {1}", DateTime.Now, Message);
                if (Ex != null)
                    logFile.WriteLine(Ex.ToString());
                logFile.Close();
            }
        }
        #endregion Error Log Methods
    }
}

Than please explain me where is the wrong to get the connection ?

Posted 19-May-12 23:00pm

UL UL ALBAB

Add a Solution

Comments

Sangramsingh Pawar 20-May-12 5:17am

Use Following

_connectionString = ConfigurationManager.ConnectionStrings["Server"].ConnectionString

3 solutions

Solution 2

change this line:

C#

_connectionString = ConfigurationSettings.AppSettings["Server"];

to:

C#

_connectionString=ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;

hope it helps :)

Posted 19-May-12 23:16pm

Uday P.Singh

Updated 19-May-12 23:18pm

v2

Solution 3

It might be helpful,

AppSettings In web.config[^]
ConfigurationSettings Class[^]

In your AppSettings["Server"] need to use SqlConnectionString based on you web.Config.

Hope it helps you :)

Posted 19-May-12 23:20pm

Mohammad A Rahman

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sangramsingh Pawar · Accepted Answer · 2012-05-19T23:16:00

Solution 1

C#

// Instead of this 
_connectionString = ConfigurationSettings.AppSettings["Server"]; 

// Use Following
_connectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString

Posted 19-May-12 23:16pm

Sangramsingh Pawar

Updated 19-May-12 23:26pm

v2

Comments

Uday P.Singh 20-May-12 5:23am

the name of the connection string is SqlConnectionString in web.config, hence use SqlConnectionString instead of server.

UL UL ALBAB 20-May-12 7:10am

Now, I have got new Exception:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

UL UL ALBAB 20-May-12 7:40am

I have added Integrated Security=SSPI; Than the last exception is solved.