Click here to Skip to main content
12,405,700 members (64,581 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# ASP.NET
The ConnectionString property has not been initialized- This is an exception.

My code:
<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 :
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 :
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
Comments
Sangramsingh Pawar 20-May-12 5:17am
   
Use Following


_connectionString = ConfigurationManager.ConnectionStrings["Server"].ConnectionString
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

change this line:
_connectionString = ConfigurationSettings.AppSettings["Server"]; 

to:
_connectionString=ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
hope it helps Smile | :)
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

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 Smile | :)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

// Instead of this 
_connectionString = ConfigurationSettings.AppSettings["Server"]; 
 
// Use Following
_connectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString
  Permalink  
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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 20 May 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100