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();
    }
}

Answer 1

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

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

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();
    }
}

Answer 2

This is a simple example code and it's working. This might help you. Here NextMonth,NextYear,ProcessedDate are auto calculated values comes from another function don't think about that.

        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);
            }
        }
    }

Connection string can be found in DB properties. right click on DB -> properties and Get the Connection String

Answer 3

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.

Answer 4

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 ! ");
        }

Answer 5

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);
        }
    }
}

Answer 6

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();
                }
            }
        }
    }

Answer 7

Trick:

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