top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

    Get Free Article Updates

Facebook Login
Site Registration

Dynamically Generating Connection String for Entity Framework

+4 votes
127 views

Sometimes, there comes a scenario where you are using certain architecture for your work but due to some issue or for some enhancement in the system you need to use two different connection providers, one for a simple connection and other for entity framework.

In my case I am using LINQ to SQL and entity framework altogether for doing some work.

What is the need for creating connection string dynamically for entity framework?

The real need arises as .edmx entity framework data objects create different connection strings than the linq to sql or we simply use in web.config file that's why we need to generate dynamically by using built in classes in C#.

Simple Connection string:

  1. <add name="ConnectionString9" connectionString="server=ServerName;database=DatabaseName;uid=db_user_id;password=db_user_password; Connect Timeout=0; Max Pool Size=5000"/> 

Entity Connection String:

  1. <add name="DBEntities" connectionString="metadata=res://*/DB.csdl|res://*/DB.ssdl|res://*/DB.msl;provider=System.Data.SqlClient;provider connection string="data sourceServerName;initial catalog=DB_Name;persist security info=True;user id=DBUserID;password=DBUserPassword;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />  

So let's start it to build the connection string dynamically to be used in project. Firstly, we add required namespaces to be used in method.

  1. using System.Configuration;  
  2. using System.Data.SqlClient;  
  3. using System.Data.Entity.Core.EntityClient;  

Secondly, we Create Class and include the method for creating connection string dynamically.

  1. public string GenerateConnectionStringEntity(string connEntity)  
  2. {  
  3.   // Initialize the SqlConnectionStringBuilder.  
  4.     string dbServer = string.Empty;  
  5.     string dbName = string.Empty;  
  6.     // use it from previously built normal connection string  
  7.     string connectString = Convert.ToString(ConfigurationManager.ConnectionStrings[connEntity]);  
  8.     var sqlBuilder = new SqlConnectionStringBuilder(connectString);  
  9.     // Set the properties for the data source.  
  10.     dbServer = sqlBuilder.DataSource;  
  11.     dbName = sqlBuilder.InitialCatalog;  
  12.     sqlBuilder.UserID = "Database_User_ID";  
  13.     sqlBuilder.Password = "Database_User_Password";  
  14.     sqlBuilder.IntegratedSecurity = false;  
  15.     sqlBuilder.MultipleActiveResultSets = true;  
  16.     // Build the SqlConnection connection string.  
  17.     string providerString = Convert.ToString(sqlBuilder);  
  18.     // Initialize the EntityConnectionStringBuilder.  
  19.     var entityBuilder = new EntityConnectionStringBuilder();  
  20.     //Set the provider name.  
  21.     entityBuilder.Provider = "System.Data.SqlClient";  
  22.     // Set the provider-specific connection string.  
  23.     entityBuilder.ProviderConnectionString = providerString;  
  24.     // Set the Metadata location.  
  25.     entityBuilder.Metadata = @ "res://*/EntityDataObjectName.csdl|  
  26.     res: //*/EntityDataObjectName.ssdl|  
  27.         res: //*/EntityDataObjectName.msl";  
  28.         return entityBuilder.ToString();  
  29. }  

This is the final connection string that returns by this method.

  1. <add name="DBEntities" connectionString="metadata=res://*/DB.csdl|res://*/DB.ssdl|res://*/DB.msl;provider=System.Data.SqlClient;provider connection string="data sourceServerName;initial catalog=DB_Name;persist security info=True;user id=DBUserID;password=DBUserPassword;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />  

Thirdly, we would use it for whatever we needed by initializing the object.

  1. var entityhelper = new EntityHelper();  
  2. var getConnString = entityhelper.GenerateConnectionStringEntity(_connectionString);  
  3. var dbentity = new DBName_Entities(getConnString);  

The first line has initialization. Second line gets the dynamically generated entity connection string and finally in the third line it is used in the entity data object.

At last after doing our work of inserting or updating query we call entity Save Method like this to transfer our data to the Database server.

  1. dbentity.SaveChanges();  

That's  all, hope you found it useful and helpful.

Note: The dynamic connection string generator is not only used for entity connection string builder but you can also create a simple connection string.

posted Apr 6, 2016 by Jdk

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

Introduction

This is about creating controls dynamically using C# in web pages. So I have considered Textboxes and labels to develop this.

Important Tasks

  1. Creating the text boxes when button is clicked.
  2. Creating text boxes as numbers are predefined.

Example

 

Discussed 2 examples. One I crated textboxes when the button is created. Here we can create any number of textboxes. In the second example we created textboxes inside a table when the row and columns are specified.

 

Demonstration

Creating the textboxes when button is clicked each time

 

image



Creating text boxes as numbers are predefined.

 

image

 

Implementation

Creating the textboxes when button is clicked each time

Here I have created the text boxes using C# in the code behind. The controls are created in each button click.

Code Snippet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


public partial class MultipleTextBox : System.Web.UI.Page
{

    protected void Page_load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            //Remove the session when first time page loads.
            Session.Remove("clicks");
        }


    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        int rowCount = 0;

        //initialize a session.
        rowCount = Convert.ToInt32(Session["clicks"]);

        rowCount++;

        //In each button clic save the numbers into the session.
        Session["clicks"] = rowCount;


        //Create the textboxes and labels each time the button is clicked.
        for (int i = 0; i < rowCount; i++)
        {

            TextBox TxtBoxU = new TextBox();
        
            TextBox TxtBoxE = new TextBox();

            Label lblU = new Label();
            Label lblE = new Label();

            TxtBoxU.ID = "TextBoxU" + i.ToString();
            TxtBoxE.ID = "TextBoxE" + i.ToString();

            lblU.ID = "LabelU" + i.ToString();
            lblE.ID = "LabelE" + i.ToString();


            lblU.Text = "User " + (i + 1).ToString() + " : ";
            lblE.Text = "E-Mail : ";

            //Add the labels and textboxes to the Panel.
            Panel1.Controls.Add(lblU);
            Panel1.Controls.Add(TxtBoxU);

            Panel1.Controls.Add(lblE);
            Panel1.Controls.Add(TxtBoxE);
        

        }


    }

}



Creating text boxes as numbers are predefined.

Here I have created a table using C# and other controls as well.

Code Snippet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class AddDefinedTextBox : System.Web.UI.Page
{

    protected void Button1_Click1(object sender, EventArgs e)
    {


        int rowCount = Convert.ToInt32(TextBox1.Text);

        int columnCount = Convert.ToInt32(TextBox2.Text);

        Table table = new Table();

        table.ID = "table1";


        for (int i = 0; i < rowCount; i++)
        {

            TableRow row = new TableRow();

            for (int j = 0; j < columnCount; j++)
            {

                TableCell cell = new TableCell();

                TextBox TxtBoxU = new TextBox();


                TxtBoxU.ID = "TextBoxU" + i.ToString();

                cell.ID = "cell" + i.ToString();

                cell.Controls.Add(TxtBoxU);

                row.Cells.Add(cell);


            }


            table.Rows.Add(row);

        }


        Panel1.Controls.Add(table);

    }
}

READ MORE

Aspx Code:

<!DOCTYPE html>

<html>

<head runat="server">

    <title>Dynamically create textboxes in ASP.Net</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <table>

            <tr>

                <td>

                    No of Text boxes

                </td>

                <td>

                    <asp:TextBox ID="txtNumbers" runat="server"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                </td>

                <td>

                    <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit" />

                </td>

        </table>

        <br />

      

    </div>

    </form>

</body>

</html>

 

 C#.Net Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

 

public partial class DynamicControls : System.Web.UI.Page

{

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

        int noofcontrols = Convert.ToInt32(txtNumbers.Text);

        for (int i = 1; i <= noofcontrols; i++)

        {

            TextBox NewTextBox = new TextBox();

            NewTextBox.ID = "TextBox" + i.ToString();

            NewTextBox.Style["Clear"] = "Both";

            NewTextBox.Style["Float"] = "Left";

            NewTextBox.Style["Top"] = "25px";

            NewTextBox.Style["Left"] = "100px";

            //form1 is a form in my .aspx file with runat=server attribute

            form1.Controls.Add(NewTextBox);

        }

    }

}

 

Equivalent VB.Net Code:

Imports System.Collections.Generic

Imports System.Linq

Imports System.Web

Imports System.Web.UI

Imports System.Web.UI.WebControls

 

Partial Public Class DynamicControls

    Inherits System.Web.UI.Page

    Protected Sub btnSubmit_Click(sender As Object, e As EventArgs)

        Dim noofcontrols As Integer = Convert.ToInt32(txtNumbers.Text)

        For i As Integer = 1 To noofcontrols

            Dim NewTextBox As New TextBox()

            NewTextBox.ID = "TextBox" & i.ToString()

            NewTextBox.Style("Clear") = "Both"

            NewTextBox.Style("Float") = "Left"

            NewTextBox.Style("Top") = "25px"

            NewTextBox.Style("Left") = "100px"

            'form1 is a form in my .aspx file with runat=server attribute

            form1.Controls.Add(NewTextBox)

        Next

    End Sub

End Class

  
  

 

READ MORE
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...