locked
Database Connection does not work when build up using sqlbuilder for entity RRS feed

  • Question

  • User686469789 posted

    When I try to build up my connection string for entity frame work I cant see the data when I pass connection string to the base constructer object as directed

    Here you will see I have a constructer in my entites object.

         // private  SystemDa _systemDB = new SystemDa();
              private string connectionstring;
            public string Connectionstring
        {
                get { return connectionstring; }
                set { connectionstring = SetupConnections(); } //instead of 'value', "Hip Hop" is written because 
                                        //'value' in 'g1' was set to "Hip Hop" by previously
                                        //writing 'g1.Name = "Hip Hop"'
            }
    

    Which I pass to my entite using below

     public partial class SMBASchedulerEntities : DbContext
        {
            public SMBASchedulerEntities()
                : base("name=SMBASchedulerEntities")
            {
            }
           
            public SMBASchedulerEntities( string connectionString)
            : base(connectionString)
        {
                Database.Connection.ConnectionString = connectionString;
            }

    If I use the standard constructor everything works and data loads when I use the connection string constructor no data 

    This is what is generated by the below method

    "Data Source=sourcenet;Initial Catalog=COASTALCAROLINAPODIATRY;Integrated Security=True;User ID=scheduler;Password=\"pass""
       public string SetupConnections()
            {
    
                SqlConnectionStringBuilder sqlBuilder =
                        new SqlConnectionStringBuilder();
    
                try
                {
                    //Specify the provider name, server and database.
                    string providerName = "System.Data.SqlClient";
                    string serverName = "sourcenet";
                    string databaseName = "COASTALCAROLINAPODIATRY";
    
                    // Initialize the connection string builder for the
                    // underlying provider.
                    
                    // Set the properties for the data source.
                    sqlBuilder.DataSource = serverName;
                    sqlBuilder.InitialCatalog = databaseName;
                    sqlBuilder.IntegratedSecurity = true;
                   sqlBuilder.UserID = "scheduler";
                  sqlBuilder.Password = "pass";
    
                    // Build the SqlConnection connection string.
                    string providerString = sqlBuilder.ToString();
    
                    // Initialize the EntityConnectionStringBuilder.
                    EntityConnectionStringBuilder entityBuilder =
                        new EntityConnectionStringBuilder();
    
                    //Set the provider name.
                    entityBuilder.Provider = providerName;
    
                    // Set the provider-specific connection string.
                    entityBuilder.ProviderConnectionString = providerString;
    
                    // Set the Metadata location.
                    entityBuilder.Metadata = @"res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl";
                    Console.WriteLine(entityBuilder.ToString());
    
                    using (EntityConnection conn =
                        new EntityConnection(entityBuilder.ToString()))
                    {
                        conn.Open();
                        Console.WriteLine("Just testing the connection.");
                        conn.Close();
                    }
                }
                catch (DbEntityValidationException ex)
                {
                    foreach (var entityValidationErrors in ex.EntityValidationErrors)
                    {
                        foreach (var validationError in entityValidationErrors.ValidationErrors)
                        {
                            Console.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
                        }
                    }
    
                }
    
                return sqlBuilder.ToString();
    Tuesday, January 2, 2018 4:57 PM

All replies

  • User753101303 posted

    "no data" that is ? Do you mean you have an error message when trying to fetch data or that your app doesn't fails but just doesn't retrieve anything ? Do you use the same db that in your  SMBASchedulerEntities connection string or could it be just that you do use some other db that just doesn't have any data for now ?

    Try perhaps to show yourContext.SomeTable.Count() ? If it shows 0 it seems you really have no data to show.

    Tuesday, January 2, 2018 7:47 PM
  • User1120430333 posted

    "Data Source=sourcenet;Initial Catalog=COASTALCAROLINAPODIATRY;Integrated Security=True;User ID=scheduler;Password"

    ----------------------------------------------------------------------------------------------------------

    How can that be right where Windows Authentication and User ID and password are given as the credentials to login to SQL Server on the same connectionstring? 

    Tuesday, January 2, 2018 11:13 PM
  • User-707554951 posted

    Hi david40ni,

    Check the following article about Dynamically Generating Connection String for Entity Framework

    http://www.c-sharpcorner.com/blogs/dynamically-generating-connection-string-for-entity-framework

    http://www.cosairus.com/Blog/2015/3/10/programmatic-connection-strings-in-entity-framework-6

    Related thread for reference:

    https://stackoverflow.com/a/15919627/9143922

    Best regards 

    Cathy

    Wednesday, January 3, 2018 9:12 AM
  • User-707554951 posted

    Hi david40ni,

    Correct code as below:

    public string GenerateConnectionStringEntity(string connEntity)  
    {  
      // Initialize the SqlConnectionStringBuilder.  
        string dbServer = string.Empty;  
        string dbName = string.Empty;  
        // use it from previously built normal connection string  
        string connectString = Convert.ToString(ConfigurationManager.ConnectionStrings[connEntity]);  
        var sqlBuilder = new SqlConnectionStringBuilder(connectString);  
        // Set the properties for the data source.  
        dbServer = sqlBuilder.DataSource;  
        dbName = sqlBuilder.InitialCatalog;  
        sqlBuilder.UserID = "Database_User_ID";  
        sqlBuilder.Password = "Database_User_Password";  
        sqlBuilder.IntegratedSecurity = false;  
        sqlBuilder.MultipleActiveResultSets = true;  
        // Build the SqlConnection connection string.  
        string providerString = Convert.ToString(sqlBuilder);  
        // Initialize the EntityConnectionStringBuilder.  
        var entityBuilder = new EntityConnectionStringBuilder();  
        //Set the provider name.  
        entityBuilder.Provider = "System.Data.SqlClient";  
        // Set the provider-specific connection string.  
        entityBuilder.ProviderConnectionString = providerString;  
        // Set the Metadata location.  
        entityBuilder.Metadata = @ "res://*/EntityDataObjectName.csdl|  
        res: //*/EntityDataObjectName.ssdl|  
            res: //*/EntityDataObjectName.msl";  
            return entityBuilder.ToString();  
    }  

    Check the following article about Dynamically Generating Connection String for Entity Framework

    http://www.c-sharpcorner.com/blogs/dynamically-generating-connection-string-for-entity-framework

    http://www.cosairus.com/Blog/2015/3/10/programmatic-connection-strings-in-entity-framework-6

    Best regards 

    Cathy

    Wednesday, January 3, 2018 9:13 AM