Click here to Skip to main content
14,922,057 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,
based of my previous Question answers. and 2 day of search i decide to change my old approach that i use for setting a custom connection parameters.

but i still have some issues, i preferred to create separated question because i thing it different.

update:
i change this line and the issue now was different
entityBuilder.Metadata = @"res://*/MrSModels.MrSalesModel.csdl|res://*/MrSModels.MrSalesModel.ssdl|res://*/MrSModels.MrSalesModel.msl";


What I have tried:

i use this function to build MySQL Entity Connection :
public static EntityConnection buildEntityConnection()
        {
            string mrsales_Coonn = "SERVER=" + server + ";Port=" + port + ";DATABASE=" +
                   database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";" + OPTION + ";" + "charset=" + charset + ";Connect Timeout=1999999;";
            myconn.mrsales_Coonn = mrsales_Coonn;
// Specify the provider name, server and database.
            string providerName = "MySql.Data.MySqlClient";
            
            // Initialize the EntityConnectionStringBuilder.
            EntityConnectionStringBuilder entityBuilder =  new EntityConnectionStringBuilder();

            //Set the provider name.
            entityBuilder.Provider = providerName;

            // Set the provider-specific connection string.
            entityBuilder.ProviderConnectionString = myconn.mrsales_Coonn;

            // Set the Metadata location.
            entityBuilder.Metadata = @"res://*/MrSModels.MrSalesModel.csdl|res://*/MrSModels.MrSalesModel.ssdl|res://*/MrSModels.MrSalesModel.msl";


            EntityConnection conn = new EntityConnection(entityBuilder.ToString());
            return conn;
        }


also i added this code in the context :
public partial class mrsalesdbEntities : DbContext
    {
        //public mrsalesdbEntities()
        //    : base("name=mrsalesdbEntities")
        //{
        //}


        public mrsalesdbEntities(DbConnection existingConnection, string ConnectionString, bool contextOwnsConnection)
            : base(existingConnection, contextOwnsConnection)
        {
        }


also i create this function to make it easy to edit.
public static mrsalesdbEntities OpenConn()
        {
            mrsalesdbEntities MrSalesContext = new mrsalesdbEntities(myconn.EFConnection, myconn.mrsales_EFCoonn, true);
            return MrSalesContext;
        }




when i need to create any query i use this line :
mrsalesdbEntities dbContext = ConnectionTools.OpenConn();



this work in query and failed in another query in the same function and i don't know the reasons
ex:- this work
mrsalesdbEntities db01 = ConnectionTools.OpenConn();

          var test1 = db01.people_data.Find(1).pepole_Name;
          var testcount = db01.people_data.Count();

ex:- this failed
mrsalesdbEntities db01 = ConnectionTools.OpenConn();
            var latest = db01.ad_backup_config;
            if (latest.Count() != 0)
            {
                DateTime BKDate = Convert.ToDateTime( latest.OrderByDescending(m=> m.backup_ID).FirstOrDefault().backup_Date);
                if (DateTime.Now >= BKDate.AddHours(Convert.ToDouble(SysSettings.BackupPeriod)))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return true;
            }



the error :

The supplied connection is not valid because it contains insufficient mapping or metadata information.
Parameter name: connection
Posted
Updated 5-Jun-20 6:43am
v3

1 solution

I think that no one is able to help you due to the fact that EF configuration should be done in several places.

I'd suggest to read official MySQL tutorials:
MySQL :: MySQL Connector/NET Developer Guide :: 6.6 Tutorial: Using an Entity Framework Entity as a Windows Forms DataSource[^]
MySQL :: MySQL Connector/NET Developer Guide :: 7.1 Entity Framework 6 Support[^]

According to your code...

If below line is responsible for connection string creation, you're in trouble!
C#
string mrsales_Coonn = "SERVER=" + server + ";Port=" + port + ";DATABASE=" +
                   database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";" + OPTION + ";" + "charset=" + charset + ";Connect Timeout=1999999;";


First of all: you should store connection string in config file, because it might change several times while developing process.
Second: do not use such of string concatenation. Rather than it, use:
C#
string mrsales_Coonn = string.Format("SERVER={0};Port={1};DATABASE={2};UID={3};PASSWORD={4};{5};charset={6};Connect Timeout=1999999;", server, port, database, uid, password, option, charset);

or
C#
string mrsales_Coonn = $"SERVER={server};Port={port};DATABASE={database};UID={uid};PASSWORD={password};{OPTION};charset={charset};Connect Timeout=1999999;";


As you can see, there's no name of option near OPTION variable! This might be the reason of error.

For proper connection string to MySQL database, please see: MySQL connection strings - ConnectionStrings.com[^]

Good luck!
   
Comments
Golden Basim 5-Jun-20 16:00pm
   
hi, yes i use this approach usually, i changed this line as you suggest :

 string mrsales_Coonn = string.Format("SERVER={0};Port={1};DATABASE={2};UID={3};PASSWORD={4};{5};charset={5};Connect Timeout=1999999;", server, port, database, uid, password, OPTION , charset); 

but it seem that EntityConnection have another diffrent format because it can't read "Port","convert zero datetime","charset". can you guide me please how to write this connection string?
Golden Basim 5-Jun-20 16:00pm
   
hi, yes i use this approach usually, i changed this line as you suggest :

 string mrsales_Coonn = string.Format("SERVER={0};Port={1};DATABASE={2};UID={3};PASSWORD={4};{5};charset={5};Connect Timeout=1999999;", server, port, database, uid, password, OPTION , charset); 

but it seem that EntityConnection have another diffrent format because it can't read "Port","convert zero datetime","charset". can you guide me please how to write this connection string?
Maciej Los 5-Jun-20 16:18pm
   
You did not read my answer catrefully. As i mentioned, you need to put connection string into config file. Then, you have to read it from there.
I have no idea what you mean by "convert zero datetime".
Golden Basim 5-Jun-20 16:36pm
   
i want the user can change the connection with UI , also if the connection string in the App.config file will be insecure and any developer can access the database.
Maciej Los 5-Jun-20 16:44pm
   
And you think that storing connection string in the apllication is more secure? If yes, you're wrong.
You can secure config file by using one of AES algorithms. See: Protecting Connection Information - ADO.NET | Microsoft Docs[^]
Golden Basim 5-Jun-20 19:08pm
   
no, i stored it into Encrypted conn.txt, but still inscure because it will loaded on memory so i will read this article because i prefer putting connection string into config file. , how to edit the connection string in the config file with code at runtime ?
Golden Basim 6-Jun-20 17:09pm
   
now i improved my code , using App.config file to store the connection also i Encrypted it, is my code now clean ?
public mrsalesdbEntities()
            : base(Functions.ConvertHashToText(ConfigurationManager.ConnectionStrings["mrsalesdbEntities"].ConnectionString))
        {
        }
Maciej Los 8-Jun-20 1:50am
   
Yes!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900