How to connect to SQL Database with C#?

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

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.
|||

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

|||What error you get when it fails to connect?|||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.
|||

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.

|||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.

|||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.

|||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.
|||

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.

|||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.
|||

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

|||o SSPI is Security Support Provider Interface|||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.

|||

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?

sql