CODEWITHSUNDEEP
c#sqlsql-server
Joe
Joe

Reputation: 13

Can't make SQL Server connection C#

I'm trying to connect to a SQL Server on a local machine, run a SQL command against it and log the results. The code keeps failing at making the connection to the server.

I can't quite see what I'm doing wrong. Everything I've searched for either doesn't apply to this method or seems to match what I'm doing. I think I have other problems in the code as well, but I can't even get past the SQL connection to test the rest. Here is my code:

string svrConnection = "Server=.\\sqlexpress;Database="+db+";User ID=user;Password=password;";

SqlConnection con;
SqlCommand cmd;

Directory.CreateDirectory("C:\\"db"\\");
FileInfo file = new FileInfo("C:\\script.sql");

string PCS = file.OpenText().ReadToEnd();
con = new SqlConnection(svrConnection);

StreamWriter PCSLog = new StreamWriter(@"C:\\" + db + "\\Log" + db + ".txt");

try
{
    con.Open();
    cmd = new SqlCommand(PCS, con);
    cmd.ExecuteNonQuery();

    using (SqlDataReader pcsrdr = cmd.ExecuteReader())
        using (PCSLog)
        {
            while (pcsrdr.Read())
                PCSLog.WriteLine(pcsrdr[0].ToString() + pcsrdr[1].ToString() + ",");
        }

    PCSLog.Close();
    cmd.Dispose();
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection !", ex.Message);
}

Upvotes: 0

Views: 152

Answers (2)

Hugh Jones
Hugh Jones

Reputation: 2694

There are additional problems with your code:

  • The Lines cmd.Dispose(); con.Close(); are not guaranteed to execute if an exception occurs.

  • You call PCSLog.Close AFTER after you have disposed of it

I would suggest this as a better alternative (irrespective of the other comments made here).

string svrConnection = "Server=.\\sqlexpress;Database="+db+";User ID=user;Password=password;"; 
Directory.CreateDirectory("C:\\" + db + "\\");
FileInfo file = new FileInfo("C:\\script.sql");
string PCS = file.OpenText().ReadToEnd();

try
{
    using (SqlConnection con = new SqlConnection(svrConnection))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand(PCS, con))
        {
            cmd.ExecuteNonQuery();
            using (SqlDataReader pcsrdr = cmd.ExecuteReader())
            using (StreamWriter PCSLog = new StreamWriter("C:\\" + db + "\\Log" + db + ".txt"))
            {
                while (pcsrdr.Read())
                    PCSLog.WriteLine(pcsrdr[0].ToString() + pcsrdr[1].ToString() + ","); 
            }

        }
    }
}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection !", ex.Message);
}

Upvotes: 2

Steve Harris
Steve Harris

Reputation: 5109

I think the issue is your connection string. Try this:

string svrConnection = "Data Source=.\\SQLEXPRESS;Initial Catalog=" + db + ";User Id=user;Password=password;"

Or to get a connection using your windows credentials:

string svrConnection = "Data Source=.\\SQLEXPRESS;Initial Catalog=" + db + ";Trusted_Connection=True;"

Also, ExecuteNonQuery does not run each command separated by GO. You will need to split your query into sections and run each individually.

Upvotes: 1

Related Questions