how to build a SQL connection using C# in Visual Studio 2017?

Question

I've always used Oledb Connection. but now I need to connect with my Database via Sql connection yet I don't know how to do so, can some one provide me an example of a database connected with sql connection?

this code needs a sql connection to be done successfully.

protected void Button1_Click(object sender, EventArgs e)
{
    string st = this.TextBox1.Text;
    string sqlstr2 = "select * from hsinfo WHERE rname='"+st+ "'";

    SqlCommand cmd = new SqlCommand(sqlstr2,);
    using (SqlDataReader rd = cmd.ExecuteReader())
    {
        this.Label1.Text = rd["rmail"].ToString();
    }
}

This code is wide open to a SQL Injection attack. Please take the time to learn how to securely build your SQL commands to prevent these attacks. — mason
– mason, Commented Jun 25, 2019 at 13:21
if you know what to do with Oracle, then it is virtually identical for SQL Server, except you use SqlConnection instead of OracleConnection...? (but: please please fix the SQL injection problem; tools like "Dapper" make that trivial) — Marc Gravell
– Marc Gravell, Commented Jun 25, 2019 at 13:51

TylerH · Accepted Answer · 2024-11-05 17:00:20Z

2

You can check the official Microsoft page for more details SqlConnection Class, but I will reproduce the given example below:

private static void CreateCommand(string queryString,
   string connectionString)
{
   using (SqlConnection connection = new SqlConnection(
              connectionString))
   {
       SqlCommand command = new SqlCommand(queryString, connection);
       command.Connection.Open();
       command.ExecuteNonQuery();
   }
}

Additionally you can check also the Connection String Syntax linked in the previous link.

edited Nov 5, 2024 at 17:00

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 25, 2019 at 12:48

3-14159265358979323846264

3,2703 gold badges18 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marco Salerno Over a year ago

Please provide answers without errors. SqlCommand is disposable.

Raghad Over a year ago

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBS.accdb"].ConnectionString)) ;

Raghad Over a year ago

I found the code above but i don't have a configuration manager,so it doesn't work for me, so how can I get it, and what is it in the first place?

Chris Dunaway Over a year ago

If you get an error on ConfigurationManager, then you need to make sure you have a reference to System.Configuration and add using System.Configuration to the top of your code.

Cleptus Over a year ago

@Raghad add reference in the project to "System.Configuration" and after that you can find it in the System.Configuration namespace.

|

Assassin · Accepted Answer · 2019-06-25 12:46:53Z

1

There is no enougth information to build connection for you, but in the shortes you sth like this:

Server=...;Database=...;User ID=...;Password=...;

For more information just check ConnectionStrings website.

answered Jun 25, 2019 at 12:46

Assassin

1,3023 gold badges14 silver badges19 bronze badges

2 Comments

Cleptus Over a year ago

Or integrated security ;-)

Assassin Over a year ago

This is a good option as well, the problem is that the poster did not add any information about connection and the ConnectionStrings sites describe all available options.

TylerH · Accepted Answer · 2024-11-05 16:58:16Z

1

Here NextMonth, NextYear, and ProcessedDate are auto-calculated values that come from another function; don't worry about them.

String cs = @"Data Source=LENOVO-G510;Initial Catalog=Nelna2;Persist Security Info=True;User ID=sa;Password=123";

protected void Save_Click(object sender, EventArgs e)
{
    // SqlConnection con = new SqlConnection(cs);

    using (SqlConnection con = new SqlConnection(cs))
    {
        try
        {
            SqlCommand command5 = new SqlCommand("insert into MonthEnd (month,year,ProcessedDate) values (@month2,@year2,@ProcessedDate2) ", con);

            command5.Parameters.AddWithValue("@month2", NextMonth);
            command5.Parameters.AddWithValue("@year2", NextYear);
            command5.Parameters.AddWithValue("@ProcessedDate2", ProcessedDate);

            command5.ExecuteNonQuery();

        }
        catch (SqlException ex)
        {
            Response.Write(ex.Message);
        }
    }
}

The connection string can be found in the database properties. Right-click on the database -> select Properties and get the Connection String

edited Nov 5, 2024 at 16:58

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 29, 2019 at 15:24

Sameera Udakumbura

518 bronze badges

1 Comment

TylerH Over a year ago

Why do you declare the connection string in a variable instead of declaring it during the SqlConnection declaration?

Amin Saadati · Accepted Answer · 2019-06-25 12:52:30Z

0

try below code and for more information about c# SQL server connection see this SQL Server Connection

string connetionString = null;
        SqlConnection cnn ;
        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
        cnn = new SqlConnection(connetionString);
        try
        {
            cnn.Open();
            MessageBox.Show ("Connection Open ! ");
            cnn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Can not open connection ! ");
        }

answered Jun 25, 2019 at 12:52

Amin Saadati

7368 silver badges22 bronze badges

5 Comments

Cleptus Over a year ago

Better link to the official documentation, its more likely to be long lived. Consider using using to proper handling the connection similar to other IDisposable classes.

Amin Saadati Over a year ago

I hope it be forever. i just want to reference to the right web site :)

Cleptus Over a year ago

This is a nice link learn.microsoft.com/en-us/dotnet/framework/data/adonet/… and this one has some sample code learn.microsoft.com/en-us/dotnet/api/…

Amin Saadati Over a year ago

@bradbury9 you are right , but i just wanted to show a simple sample of using SqlConnection . exactly Using Using is so fine for above code like this link stackoverflow.com/questions/4717789/…

Amin Saadati Over a year ago

@bradbury9 if you prefer i will change my above link to this second link you put it in above comment

Marco Salerno · Accepted Answer · 2019-06-25 13:37:54Z

I would do something like this:

public static List<Test> GetTests(string testVariable)
{
    DataTable result = new DataTable();
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
    {
        connection.Open();

        GetQuery(
            connection,
            QueryGetTests,
            ref result,
            new List<SqlParameter>()
            {
                new SqlParameter("@testVariable", testVariable)
            }
        );

        return result.Rows.OfType<DataRow>().Select(DataRowToTest).ToList();
    }
}

private static void GetQuery(SqlConnection connection, string query, ref DataTable dataTable, List<SqlParameter> parameters = null)
{
    dataTable = new DataTable();
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.CommandTimeout = 120;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        using (SqlDataAdapter reader = new SqlDataAdapter(command))
        {
            reader.Fill(dataTable);
        }
    }
}

Domenico · Accepted Answer · 2019-06-25 21:43:57Z

0

Trick:

Create a file with .udl Extension on your Desktop
Run it by Double click
Compile form by Choosing provider, username, password, etc...
Test connection and save
Close the form
Open now the .udl file with Notepad
You will see the connection string that you can use with ADO.NET

answered Jun 25, 2019 at 21:43

Domenico

3593 silver badges6 bronze badges

Comments

Jakub Sluka · Accepted Answer · 2024-11-07 10:07:34Z

Try this.

    protected void Button1_Click(object sender, EventArgs e)
    {
        string _connectionString = "";
        string st = this.TextBox1.Text;
        string query = "select top(1) * from hsinfo WHERE rname='" + st + "'";

        using (SqlConnection conn = new SqlConnection(_connectionString)) 
        {
            conn.Open();
            using (SqlCommand command = new SqlCommand(query, conn)) 
            { 
                var reader = command.ExecuteReader();
                reader.Read();
                this.Label1.Text = rd["rmail"].ToString();
            }
        }
    }

Of course you need to provide valid connection string that points to your database and lets you open the connection. It can be either stored in constructor, in the method, or provided from Configurastion Manager if yu use it

Here are examples of valid connection strings, where zou just replace the values related to your database.

Connection strings examples

Andrea Prestia · Accepted Answer · 2019-06-25 13:42:58Z

-1

I think this can help you.

    string sqlString = "select * from hsinfo WHERE rname=@st";

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseName"].ConnectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sqlString, conn))
        {
            cmd.Parameters.Add("@st", st);

            using (SqlDataReader rd = cmd.ExecuteReader())
            {
                if (rd.Read())
                {
                    this.Label1.Text = rd["rmail"].ToString();
                }
            }
        }
    }

answered Jun 25, 2019 at 13:42

Andrea Prestia

91 bronze badge

2 Comments

Raghad Over a year ago

i don't have a configuration manager,so it doesn't work for me, so how can I get it, and what is it in the first place?

Andrea Prestia Over a year ago

You have to include System.Configuration

Collectives™ on Stack Overflow

how to build a SQL connection using C# in Visual Studio 2017?

8 Answers 8

6 Comments

2 Comments

1 Comment

5 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

6 Comments

2 Comments

1 Comment

5 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related