How to use C# to connect to the SQL Server, there is a connection string we can use:
string conn = "Integrated Security=false; Data Source=****;Initial Catalog=****;User ID=****;Password=****";
Data Source (Server; Address ; Addr ; Network Address): The name or network address of the instance of SQL Server to which to connect. The port number can be specified after the server name:
server=tcp:servername, portnumber
When specifying a local instance, always use (local).
Initial Catalog (Database): The name of the database.
Integrated Security (Trusted_Connection): By default, it is false, and User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.
To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.
using (SqlConnection connection = new SqlConnection(conn))
{
connection.Open();
// Do work here; connection closed on following line.
}
If a SqlException is generated by the method executing a SqlCommand, the SqlConnection remains open when the severity level is 19 or less. When the severity level is 20 or greater, the server ordinarily closes the SqlConnection. However, the user can reopen the connection and continue.
Some SQL scripts can be executed by using SqlCommand Class:
string queryString = "Select * from ****";
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader(); // IDataRecord reader
try
{
while (reader.Read())
{
// Do read work here: reader[0];
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
We can also set the command type to execute some other scripts, such as stored procedure. You should provide the name of the stored procedure. Then the command executes this stored procedure when you call one of the Execute methods.
