CODEWITHSUNDEEP
c#sql-serverasp.net-mvcconnection-string
triest23
triest23

Reputation: 127

Asp.net MVC SQL Server connection return null

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:

enter image description here

Configuration from Pyton (try, but another database):

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

Upvotes: 1

Views: 1156

Answers (2)

Christlin Panneer
Christlin Panneer

Reputation: 1647

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);
}

Upvotes: 3

Igor
Igor

Reputation: 62238

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>

Upvotes: 1

Related Questions