Asp.net MVC SQL Server connection return null

Question

I am trying to connect to Microsoft SQL Server on localhost. My code is as follows.

public SqlConnection con;

// To Handle connection related activities
private void connection()
{
    string constr = ConfigurationManager.ConnectionStrings["Data Source = MACHINE-VOIV7EH\\SQLEXPRESS; Initial Catalog = geolog; Persist Security Info = False; "].ToString();
    con = new SqlConnection(constr);
}

public List<Bob> GetAllBobs()
{
    try
    {
        connection();
        con.Open();

        IList<Bob> EmpList = SqlMapper.Query<Bob>(con, "GetBobs").ToList();
        con.Close();

        return EmpList.ToList();
    }
    catch (Exception)
    {
        throw;
    }
}

con return null

SQL Server settings:

Configuration from Pyton (try, but another database):

conn = pypyodbc.connect('DRIVER={SQL Server};'
                        r'SERVER=MACHINE-VOIV7EH\SQLEXPRESS;'
                        r'DATABASE=vibori;'
                        r' autocommit=True'
                        )

Christlin Panneer · Accepted Answer · 2018-04-02 19:25:40Z

3

The Problem is with your connection string. You should reference your connection string from the web.config file.

Web.Config

<connectionStrings>
    <add name="master" providerName="System.Data.SqlClient" connectionString="Data Source=ACHINE-VOIV7EH\\SQLEXPRESS;Initial Catalog=geolog;Integrated Security=False;User Id=your_user_id;Password=xxxxxx;MultipleActiveResultSets=True" />
  </connectionStrings>

C# File

 SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["master"].ConnectionString);

Or you can just send a connection string as follows

private void connection()
{
   string constr = "Data Source=MACHINE-VOIV7EH\\SQLEXPRESS; Initial Catalog = geolog; Persist Security Info = False;";
   con = new SqlConnection(constr);
}

answered Apr 2, 2018 at 19:25

Christlin Panneer

1,6652 gold badges20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Igor · Accepted Answer · 2018-04-02 19:22:47Z

1

The string index of ConfigurationManager.ConnectionStrings[string] is the connection name, not the connection string which is what it returns. Use your web configuration file (web.config) to add one or more named connection strings which can be returned by this indexer.

Example:

string constr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString

and partial web.config content

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <connectionStrings>
    <add name="MyConnection" connectionString="Data Source=MACHINE-VOIV7EH\SQLEXPRESS;Initial Catalog=vibori" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

answered Apr 2, 2018 at 19:22

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Collectives™ on Stack Overflow

Asp.net MVC SQL Server connection return null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related