form connection to SQL server

[JohnSe]

Hi,

Could someone provide some codes examples that can help me connect C# forms to SQL server that is trusted (without user name or password, just the server and database name).

Thanks

 

[NoUserHere]

I am going to use MySQL as an example, since I don't have SQL installed. However, if you're set on using SQL, just remove My from MySQL and It should work for SQL providing you use a connection string built for SQL server with your own credentials. This example code was pulled from a static class in a working MySQL project. For your sanity, I will remove the static modifiers and the classes, and assume you know how to structure the code yourself in a custom database class.

Your connection string would look Similar to this ::

public const string cString = "server=localhost;user id=root;password=root;persistsecurityinfo=True;database=visuals_db;port=8889";

I think the connection string is self explanatory, so I won't bother elaborating on it; suffice to say you can compare them at ConnectionStrings.Com - MySQL - SQLServer - Notice the difference with SQL, you need to qualify an instance name for your SQL installation as where with MySQL, you can provide a server address.

In order to begin executing statements, you will need some which look like this ::

        public const string execSelectUnameQuery = "SELECT username from users WHERE email=@email";
        public const string execSelectIDQuery = "SELECT id from users WHERE email=@email";
        public const string execUpdateNameQuery = "UPDATE users SET name=@Fname WHERE email=@Email AND password=@Pass;";
        public const string execInsertUserQuery = "INSERT INTO users (name,username,email,password) VALUES (@Fname,@Uname,@Email,@Pass)";
        public const string execDeleteUserQuery = "DELETE FROM users WHERE id=@id";

Next step it to declare a connection with the connection string ::

public MySqlConnection Con = new MySqlConnection(cString);

Then you will want to build up a method to execute your statements. I prefer to pass in my statements to the method. At this point, you're dealing with pseudo code, as I explained, I've pulled this from a working project. It's up to you to jigsaw it back together.

        //The insert method :: Each method takes one argument, this will be the statement declared above. Just pass in the variable name for the insert statement.
        public bool Insert(string Statement)
        {
            try //Do not nest try catch blocks, except, declare more than one catch exception in one try block. Each catch can be added for the different exceptions that may arise.
            {
                if (Con != null)
                //Check connection reachable and open it if its closed.
                {
                    DoConnection(); //While the connection is closed, open it
                    using (MySqlCommand cmd = new MySqlCommand(Statement, Con)) //Build the command, and pass in the statement with the connection.
                    {    /* Use using blocks for they are self disposing when done using.
                        The below parameters takes the parameter name, and then the value of that parameter
                        So for the first parameter, we are setting the Name of the person for Fname parameter name */
                        cmd.Parameters.AddWithValue("@Fname", myAccount.Name); //Where myAccount would be the class holding your clients user details
                        cmd.Parameters.AddWithValue("@Uname", myAccount.Username);
                        cmd.Parameters.AddWithValue("@Email", myAccount.Email);
                        cmd.Parameters.AddWithValue("@Pass", myAccount.Password);
                        //Notice Fname, Uname, Email, Pass are the placeholders for the values and they are found in the executing statement above. This is how parameters should be used or you risk [URL='https://en.wikipedia.org/wiki/SQL_injection']SQL Injection[/URL] attacks.
                        //Always use parameterized command queries!! And use add with value since add is deprecated
                        cmd.ExecuteNonQuery(); //Execute the query
                    }
                    DoConnection(); //Close the connection.
                    return true; //Tell the calling code success
                }
                return false; //Statement wont execute
            }
            catch (Exception ex)
            {   //Then catch all errors you might miss
                MessageBox.Show(string.Concat("Tell the developer there is an error at ", ex.StackTrace));
                return false;
            }

I prefer to handle my open connections with a switch. DoConnection() method being called above opens and closes the connection based on its state.

        public bool DoConnection() //Open and close the connection with this method
        {
            //We devised a switch statement to open and close the connection depending on its current state
            switch (Con.State == ConnectionState.Closed)
            {
                case true:
                    Con.Open();
                    return true;

                case false:
                    Con.Close();
                    return false;
            }
            return false;
        }

You will need to get it working yourself. Your database table should look something like this ::

If you want to use the Select/Delete queries, you need to add an ID field to the table and make it AI. Normally I don't provide code snipped off the bat without seeing what you've attempted first. But in a nutshell, It should look something like that. If you have any questions, be sure to post back with your own implementation of how you implemented this example. Hope it helps.

 

[JohnSe]

Can you supply a simple code that uses C# to connect to the database that is trusted without password just the server and database name ? I am still looking at your code though. Thanks

 

[NoUserHere]

Ehh, what i gave you does exactly that. I could have been difficult about it and only given you this link and told you to work it out; given you've shown no attempt yourself. You should study what you were given. The answers are on my post. Look at the links to the connection strings and set them up however you wish, with or without a password.

 

[JohnSe]

Alright let me work it out and get back to you. Thanks for your advice. ?

 

[Skydiver]

ConnectionStrings.com is your friend in building the connection string. In particular see: Trusted Connection

Beyond that, just look at the MSDN documentation: SqlConnection

 

[JohnSe]

Ehh, what i gave you does exactly that. I could have been difficult about it and only given you this link and told you to work it out; given you've shown no attempt yourself. You should study what you were given. The answers are on my post. Look at the links to the connection strings and set them up however you wish, with or without a password.

Sheepings, your connection has user name and password. mines doesn't have username or password, is Trusted Connection. Just because yours work doesn't mean if I follow it mine would, there is some uniqueness to it.

 

[NoUserHere]

It will work if you follow the instructions I gave you. I was very descriptive as to what to do. Regarding trusted connections, see above post by Skydiver.

I've given this advice to many people before and they never had a problem with it. Give it a shot, and post back if you get stuck.

 

[JohnSe]

I have this code problem. It always stucks on conn.Open with message - An unhandled exception of type "System.Data.SqlClient.SqlException occurred in System.Data.dll. What do you advise. I didn't alter it to what you have yet.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

namespace WFA61719
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server= xyz; Database = DatabaseName; Integrated Security = SSPI");
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT count(*) FROM table";
            string record = cmd.ExecuteScalar().ToString();
            MessageBox.Show("Total records : " + record);
            conn.Close();
        }
    }
}

 

[Skydiver]

If you are running your code in Visual Studio, click on on the "Copy exception details to clipboard" and then reply to this thread and paste in the contents. The Message property of the exception often has more detailed information regarding why the exception was thrown.

If your server name or database name has spaces in it, then you'll have to surround them with with quotes in your connection string. I assume that your use "xyz" and "DatabaseName" above is just you anonymizing your real server and database names for the sake of posting here on the forums.

 

[JohnSe]

The exact error message as I see it :

SqlException was unhandled

An unhandled excption of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A non-recoverable error occurred during a dtabase lookup.)

 

[JuggaloBrotha]

The exact error message as I see it :

SqlException was unhandled

An unhandled excption of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A non-recoverable error occurred during a dtabase lookup.)

The error message tells you exactly what's wrong: the sql server your connection string specifies doesn't exist.

 

[NoUserHere]

Post your connection string.

Then navigate the computer management (Right click my computer, then go to manage.) You will see a screen like the one below. Navigate the Services Applications data tree. Just like I have done. You will find your instance name in there. You should know your instance name, as the SQL installer asks you to name it, when you install it.

 

[JohnSe]

After further investigation of exception detail to clipboard.

I noticed is
ErrorCode = -2146232060,

similar to this link's error message:

Error Code -2146232060

social.msdn.microsoft.com

Except error message is : A non-recoverable error occurred during a database lookup

I read somewhere that said

The cause is the SQL server for the environment had been built with the wrong collation setting. Designer requires that the collation setting is case-insensitive (Latin1_General_CI_AS), but the server was configured as case sensitive (Latin1_General_CS_AS).

Resolution, is to re-install or install new instance of SQL server with the collation setting of 'Latin1_General_C1_AS'.
Please advise ! Thx

 

[JohnSe]

The error message tells you exactly what's wrong: the sql server your connection string specifies doesn't exist.

But I could connect it with .NET and other languages and it connected and was fine.
I don't know why I can't connect using C#.