The ‘Connection string’ string of connection with database. An example of using in applications

The ‘Connection string’ string of connection with database. An example of using in applications


Content


Task

This topic shows how to determine the connection string with the ‘Connection String’ database. With this string application (program) can have access to the database elements (tables, views, charts, and the like). It is assumed that the local database is located in the file “MyDataBase.mdf”. The database was created earlier and included to the list of data ‘Server Explorer’ databases.

If the database has not yet been created, then download the archive with the finished database files here.

The database consists of two files:

  • “MyDataBase.mdf”;
  • “MyDataBase.ldf”.

An example of connecting a database to the list of Server Explorer databases in MS Visual Studio is given in the article:

 

Instructions

1. Defining the ‘Connection String’ string

To get the ‘Connection String’ to the database is necessary to perform the following steps (Figure 1)

  1. Go to the Server Explorer utility (Figure 1-1)
  2. Select the file “MyDataBase.mdf” (Figure 1-2).
  3. In the Properties window, select the line (property) “Connection Strings” (the context menu is the “Select All” command and copy it to the clipboard Clipboard (the context menu is the “Copy” command) (Figure 1-3).
  4. Go to the text portion of the program file. This can be, for example, the “Form1.cs” file of the main form of the program created using the Windows Forms template.
  5. Create a variable in the form class Form1 of type ‘string’. Let the name of the variable ConnStr. Insert the string “Connection String” in the initialization string of the ConnStr variable (or choose a different name) as shown below:
// Connection string with database
string ConnStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Programs\C_SHARP\TermPaper1\TermPaper1\MyDataBase.mdf;Integrated Security=True;User Instance=True";

In this case, the database connection string specifies the full path to the database file “MyDataBase.mdf”.

Figure 1. Copying a string (ConnectionString) of a database connection to a program

The approximate form of the file “Form1.cs” (C# programming language) can be as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TermPaper1
{
    public partial class Form1 : Form
    {
        // Connection string with database
        string ConnStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Programs\C_SHARP\TermPaper1\TermPaper1\MyDataBase.mdf;Integrated Security=True;User Instance=True";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
   }
}

 

2. Using the Connection String in the program. Example

Let the database, which is located in the file “MyDataBase.mdf”, be given. Let the database have a table named Source that contains the following fields:

  • ID_Source;
  • Name;
  • Address.

Example 1. You need to display the Source table in the dataGridView1 control of DataGridView type based on the following SQL query:

SELECT * FROM [Source]

Then the function that displays the table will have approximately the following form:

private void FillSource()
{
    string SqlText = "SELECT * FROM [Source]"; // SQL query text
    SqlDataAdapter da = new SqlDataAdapter(SqlText,ConnStr);
    DataSet ds = new DataSet();
    da.Fill(ds,"[Source]");
    dataGridView1.DataSource = ds.Tables["[Source]"].DefaultView;
}

In this function, the ‘Connection String’ string is represented by the ConnStr variable.

Example 2. Adding a new record to the Source table based on the following SQL query:

INSERT INTO [Source] ([ID_Source], [Name], [Address]) VALUES (1, ‘Source-01’, ‘Address-01’)”;

A snippet of code that demonstrates the addition of a new record:

string SqlText = "INSERT INTO [Source] ([ID_Source],[Name],[Address]) VALUES (1, 'Source-01','Address-01') ";
SqlConnection cn; // An instance of a class of type SqlConnection
SqlCommand cmd;

// Memory allocation with the initialization of the connection string to the database
cn = new SqlConnection(ConnStr);
cn.Open(); // Open the data source

// Set the SQL command
cmd = cn.CreateCommand();

cmd.CommandText = SqlText; // Set the command line
cmd.ExecuteNonQuery();
cn.Close();

In the above example, the ConnStr variable is the connection string to the database.

 


Related topics