none
How to connect to SQL Database with C#? RRS feed

  • Question

  • Hi, I am trying to connect to an SQL Database with my C# program, and am having the following problems.

    1) Sometimes it connects fine, other times it fails to connect.  I am using Integrated Security / Windows Login.

    2) I am trying to pass a Select Statement (Which works from SQL Server Management Studio), but it doesn't seem to work in my program.  My code doesn't appear to do anything really...

    3) When I use only the code that was generated by VS, it loads all the information into the dataGridView, but I want to filter it using the Select Statement.

    Here is the code generated by VS:

    private void Form1_Load(object sender, EventArgs e)
    {
    this.collectionTableAdapter.Fill(this.DBDataSet.Collection);
    }


    Here is my code:

    Code Snippet

    private void Form1_Load(object sender, EventArgs e)
    {
                //Connect to the database for Collections Table
                SqlConnection thisConnection = new SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
                    "Database=DB");

                //Create DataAdapter Object
                SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT Number AS [#], Image1 AS [Image], Title1 AS [Title], Issue1 AS [Issue] FROM Table1 WHERE Tab = 'Tab1'", thisConnection);

                //Create DataSet to contain related data tables, rows, and columns
                DataSet thisDataSet = new DataSet();

                //Fill DataSet using query defined previously for DataAdapter
                thisAdapter.Fill(thisDataSet, "Collection");
                foreach (DataRow theRow in thisDataSet.Tables["Collection"].Rows)
                {
                    Console.WriteLine(theRow["Number"] + "\t" +
                        theRow["Image1"] + "\t" +
                        theRow["Title1"] + "\t" +
                        theRow["Issue1"]);
                }

                //Close Connection
                thisConnection.Close();
            }

    Friday, June 8, 2007 4:21 PM

Answers

All replies

  • One other thing, I don't know if this helps, but it appears that more often than not, it fails to log in to the database when I open the application the first time.  If I exit out of the failure notice, then open the application again, it loads the data into the dataGridView (Not the data I want mind you, but at least I can connect to the database...  lol).

    Thank you.
    Friday, June 8, 2007 4:33 PM
  • the very first thing you need to check is the connection string....

     

    Refer : http://www.connectionstrings.com/?carrier=sqlserver2005

     

    And also check the user /login credential

     

    Madhu

    Saturday, June 9, 2007 7:43 AM
    Moderator
  • What error you get when it fails to connect?
    Monday, June 11, 2007 1:46 PM
    Moderator
  • Thank you, I will take a look at that.  I have read a lot about this, but for whatever reason have had no luck getting this to work.

    Here are the error messages I am getting.  I sometimes log in to the server fine, usually on a second attempt, when I can't login I get one of these errors.

    1) Cannot open database "DB" requested by the login. The login failed.
    Login failed for user '_____'.

    2) Unhandled exception has occurred in your application.  If you click Continue, the application will ignore this error and attempt to continue.  If you click Quit, the application will close immediately.

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Thank you.
    Monday, June 11, 2007 3:12 PM
  • Try  to set timeout=0 in web.config   and also set query time out=0 in sql server.

     

    See this http://www.aspnettutorials.com/tutorials/database/connect-sql-datasource-csharp.aspx link for relevant connection infrmation.

    Monday, June 11, 2007 3:19 PM
    Moderator
  • Thanks, I'll give that a shot and take a look at that link.  Does it matter that this is an application, not a website?

    Thank you.


    Monday, June 11, 2007 6:21 PM
  • I'm sorry, I'm still very confused by this.  Where should I put the timeout = 0?  Isn't it important to have a timeout period?  Shouldn't it be able to access the database within the timeout period?  Should I delete the dataGridView and associated controls that VS added to my project automatically?  I'm not sure what I should be doing.  I enter the code in as examples outline, but it still doesn't do anything.  Here is my most recent code:

            private void Form1_Load(object sender, EventArgs e)

    string strCon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Resources\DB.mdf;Integrated Security=True;User Instance=True";

    string strSQL = “select * from table1”;

     

    SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strCon);

    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

     

    // Populate a new data table and bind it to the BindingSource.

    DataTable table = new DataTable();

    table.Locale = System.Globalization.CultureInfo.InvariantCulture;

    dataAdapter.Fill(table);

    dbBindingSource.DataSource = table;

     

    // Resize the DataGridView columns to fit the newly loaded content.

    dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

    // you can make it grid readonly.

    dataGridView1.ReadOnly = true; 

    // finally bind the data to the grid

    dataGridView1.DataSource = dbBindingSource;


    Thank you.


    Tuesday, June 12, 2007 5:00 PM
  • I have also tried this:

    Code Snippet

    // create an open the connection
                SqlConnection conn =
                   new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Resources\DB.mdf;Integrated Security=True;User Instance=True");

                conn.Open();

                // create a SqlCommand object for this connection
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT Number AS [#], Image AS [Image], Title AS [Title], FROM table1 WHERE Tab = '1'";
                command.CommandType = CommandType.Text;

                // execute the command that returns a SqlDataReader
                SqlDataReader reader = command.ExecuteReader();

                // display the results
                while (reader.Read())
                {
                    string output = reader.ToString();
                    Console.WriteLine(output);
                }

                // close the connection
                reader.Close();
                conn.Close();



    This didn't work either.
    Tuesday, June 12, 2007 5:41 PM
  • Try this:

     

    SqlConnection conn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=pubs");

     

     

    Replace "Localhost" with your server name, if you have a named instance try giving it in single codes.

    Tuesday, June 12, 2007 6:28 PM
  • Hi and thank you very much for your response.  I tried that and get this error:

    Cannot open database "pubs" requested by the login. The login failed.
    Login failed for user '____'.

    I tried replacing "pubs" with my database name, but that gave me the same error.  I don't understand this.  No matter what I do I don't seem to be able to get this to work.  What is SSPI by the way?

    Thank you.
    Tuesday, June 12, 2007 7:00 PM
  • Alright, thats looks ok

     

    Connect to SQL Server 2005 MS go to security >> logins >> check your windows userid exists in there (hoepfully yes if your server is on the same machine, but still check once)  >> right click on the user id >> click on properties >> check what all database rights that user id has >> if your target DB is not checked give the acecss >> select the role you want to give if required or leave it as is if you can live with public role

     

     

    Tuesday, June 12, 2007 7:08 PM
  • o SSPI is Security Support Provider Interface
    Tuesday, June 12, 2007 7:10 PM
  • O.K., thank you very much for your help.  I followed your instructions and am not entirely sure what I am looking at.

    I have several users under Login:

    My name and computer login is Michael.  The following shows up as a login:

    MICHAEL\SQLServer2005MSSQLUser$MICHAEL$SQLEXPRESS

    I clicked on Properties of this user and selected User Maping, which listed several databases.  I checked the box for this database and it was set to Public. 

    Is all this going to be necessary if I install this application on another machine?  I would like to make this able to be easily distributable.  Should I not use the Windows Login?

    Thank you.


    Tuesday, June 12, 2007 8:07 PM
  • Well that is all up to what roles you want to have, how you want to setup the security for SQL Server or your application considering the options provided by Microsoft in DOT NET and also in SQL Server.

     

    But lets get on to the main point, doing that help you getting connected to the database thru your application?

    Tuesday, June 12, 2007 8:25 PM
  • Thank you very much.  I still get the same error that the login failed.  I just do not understand this...

    Here is the code that I am using as of now:

    Code Snippet

            private void Form1_Load(object sender, EventArgs e)
            {
                //Attempt 2
                // create and open the connection
               SqlConnection conn =
                    new SqlConnection(@"Data Source=MICHAEL\SQLEXPRESS; Integrated Security=SSPI; Initial Catalog=DB.mdf");

                conn.Open();

                // create a SqlCommand object for this connection
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT Number AS [#], Image AS [Image], Title AS [Title], FROM table1 WHERE Tab = '1'";
                command.CommandType = CommandType.Text;

                // execute the command that returns a SqlDataReader
                SqlDataReader reader = command.ExecuteReader();

                // display the results
                while (reader.Read())
                {
                    string output = reader.ToString();
                    Console.WriteLine(output);
                }

                // close the connection
                reader.Close();
                conn.Close();
            }


    Wednesday, June 13, 2007 2:44 PM
  • Just wondering, i never came across a code where in database name is mentioned as "db.mdf" (i probably might be wrong here) but usually its always just the db name no mdf/ldf here, also in your connection string not sure if you really have to write "@"

     

    Your connection string:

    SqlConnection conn =
                    new SqlConnection(@"Data Source=MICHAEL\SQLEXPRESS; Integrated Security=SSPI; Initial Catalog=DB.mdf");

     

    Also not sure if you have a named instance of SQL Server, else normally you can just use "michael". If you have named instance of SQL Server use in single codes

     

    If i have to write it might look like:

     

    SqlConnection conn =
                    new SqlConnection("Data Source='MICHAEL\SQLEXPRESS'; Integrated Security=SSPI; Initial Catalog=DB");

     

    this should work keeping mind database has proper permission for the windows user id you are trying to connect with. You can also check connecting thru query analyzer with windows authentication to confirm

     

    Wednesday, June 13, 2007 10:46 PM
  • To configure SQL Server for integrated security

    1. From the Windows Start menu, choose Microsoft SQL Server, and then choose Enterprise Manager.
    2. Open the node for the server and expand the node for the database you want to give users permissions for.
    3. Right-click the Users node and choose New Database User.
    4. In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK.
    5. Provide proper role as required for the application

     

    Wednesday, June 13, 2007 10:50 PM
  • Hi, I will try this tonight.  I don't know.  I followed the steps in the book, which said that I could copy the generated Connection String from the app.config file, so that's what I did.  ?

    Thank you.
    Thursday, June 14, 2007 6:57 PM
  • Hello, I have tried all of this, and still cannot log in to my database, or get anything to work.  What steps should I have taken to add the database and such?  I followed all of the steps in the walkthrough's to the letter...  I just don't understand this and am getting a bit frustrated... 

    Thank you very much,

    Thursday, June 21, 2007 4:08 PM
  • Add a new user called "MICHAEL\Michael" (without the quotes) and grant it the sysadmin server role.  If that works, you can scale the permissions back from there until it breaks.  If that does not work, there are a few other things to check.
    Thursday, June 21, 2007 4:59 PM
  • Thank you very much for your response.  I went to SQL Server Management Studio Express, and opened the database, then right clicked on users and selected new user and entered the information in this screen.  When I entered MICHAEL\Michael as a Login it said that it couldn't be processed because it contained illegal characters.  When I entered Michael I got this error:

    Create failed for User 'MICHAEL'.  (Microsoft.SqlServer.Express.Smo)

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)


    'Michael' is not a valid login or you do not have permission. (.Net SqlClient Data Provider)

    I just do not understand this.  In the books, they really fly though this part, as it is supposedly so easy...

    I really wish I could get this working...
    Friday, June 22, 2007 3:57 PM
  • Are you logged onto your machine as the same user originally used to install SQL 2005 Express?

    When you create the user as attempted earlier (MICHAEL\Michael), ensure you select Windows Authentication instead of SQL Server authentication.

    A quick and dirty solution would be using a standard SQL Server login and password instead of a Windows login (SSPI / Trusted Security / Windows Authentication).

    If you choose to go the quick-n-dirty route, then select SQL Server authentication and make up both a username (in this case, you would use something such as "Michael" instead of "MICHAEL\Michael") and password.  Make sure you give the user sysadmin privileges.  You will then need to specify the username and password in the connection string instead of the section with SSPI.  You should be able to find a bunch of syntax examples under "SqlConnection" in your MSDN Library, Visual Studio 2005 Documentation, or SQL Server Books Online (a shortcut in your SQL Server program group).
    Friday, June 22, 2007 8:31 PM
  • I have an issue similar to this one. I'm trying to connect to my SQL database on my local machine and I'm using the following code:


    SqlConnection sqlConnection = new SqlConnection("server=jchaney-pc;integrated security=true;" + "database=sqlexpress.master.dbo");<br>sqlConnection.Open(); 


    But when I run that command, I get this exception:

    {"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

    Any ideas as to why?

    Thanks,
    --Jeremy

    Monday, March 16, 2009 1:03 AM
  • Saturday, December 4, 2010 7:52 AM
    • Proposed as answer by Waqas Silat Thursday, August 9, 2012 8:26 PM
    Sunday, August 5, 2012 8:13 PM
  • SqlConnection conn =
                    new SqlConnection("Data Source=MICHAEL\SQLEXPRESS;  Initial Catalog=DB; Integrated Security=true")


    • Edited by KiritM Saturday, May 18, 2013 3:50 AM
    Saturday, May 18, 2013 3:49 AM
  • Friday, November 8, 2013 12:50 AM
  • I had try it's not working, how i can contact with u?

    Wednesday, November 20, 2013 6:43 AM