C # code for connecting to the SQL database

I think it is necessary to summarize it.

I. C # connect to the SQL database
Data Source = myServerAddress; Initial Catalog = myDataBase; User Id = myUsername; Password = myPassword;
Data Source = 190.190.200.100, 1433; Network Library = DBMSSOCN; Initial Catalog = myDataBase; User ID = myUsername; Password = myPassword;
Server = myServerAddress; Database = myDataBase; User ID = myUsername; Password = myPassword; Trusted_Connection = False;
Server = myServerAddress; Database = myDataBase; Trusted_Connection = True;
Server = myServerName \ theInstanceName; Database = myDataBase; Trusted_Connection = True;
Data Source = myServerAddress; Initial Catalog = myDataBase; Integrated Security = SSPI;

1: Integrated Security parameters
When the value of Integrated Security is set to True, the user ID in front of the connection statement PW does not work, that is, the windows Authentication mode is used.
The connection is based on UserID and PW only when it is set to False or this item is omitted.
Integrated Security can also be set to sspi, which is equivalent to True. We recommend that you use this instead of True.
Data Source = myServerAddress; Initial Catalog = myDataBase; Integrated Security = SSPI;
Data Source = myServerAddress; Initial Catalog = myDataBase; Integrated Security = true;
Data Source = myServerAddress; Initial Catalog = myDataBase; User ID = myUsername; Password = myPasswordIntegrated Security = false;

2: Trusted_Connection
Trusted_Connection = true. The current Windows Account creden are used for authentication.
Trusted_Connection = false; the trusted connection method (that is, the Windows authentication method is not used) is not used, but the SQL Server 2000 authentication method is used.
Server = myServerAddress; Database = myDataBase; User ID = myUsername; Password = myPassword; Trusted_Connection = false;
Server = myServerAddress; Database = myDataBase; Trusted_Connection = True;

3: Initial Catalog is the name of the database you want to connect

4: WINCE connection
Data Source = myServerAddress; Initial Catalog = myDataBase; Integrated Security = SSPI; User ID = myDomain \ myUsername; Password = myPassword;

Ii. You can use SqlConnectionStringBuilder to remember the name.
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder ();
Scsb. DataSource = @ "(local) \ SQLExpress ";
Scsb. IntegratedSecurity = true;
Scsb. InitialCatalog = "Northwind ";
SqlConnection myConnection = new SqlConnection (scsb. ConnectionString );

3. You can use Setting in the property to automatically set the connection string.
1: Select (connection string) in type ),
2: select the data source from DataSouce, and then enter the Server name in the Server. Use local \ SQLExpress
3: select the login authentication method. This time, select Windows authentication (that is, trust the connection Integrated Security = True)
4: select the Database Name and click OK.
Data Source = (local) \ SQLExpress; Initial Catalog = Northwind; Integrated Security = True
Server =. \ sqlexpress; integrated security = true; database = northwind

Iv. SQL2005 remote server connection method

How to open port 2005 of SQL server 1433:
Configuration tool-> SQL Server Configuration Manager-> MSSQLSERVER protocol to check whether the TCP/IP protocol is enabled. If it is enabled, right-click "properties" and choose "IP address" from the page menu ", set "TCP port" in "IP1" and "IP2" to 1433, and "enabled" to "yes"
Configuration tool-> SQL Server Configuration Manager-> SQL Native Client Configuration-> Client protocol-> TCP/IP right-click TCP/IP and choose "properties" from the context menu ", make sure that the "Default port" is 1433 and "enabled" is "yes ".

SQL Server 2005 remote connection configuration TCP/IP attributes:
Surface Area Configuration --> Database Engine --> Remote Connections ---> Using TCP/it SQL Server peripheral application configurator? Service and connection peripheral configuration? Database englie? Remote connection? Enable (remote connection of TCP/IP and named pipes)
SQL Server Configuration Manager? SQL2005 network configuration? Enable TCP/IP and named pipes

For other instructions, see sqlserver2005 (Express Edition). To facilitate management, you need to go to the next manage Manager:
After installing the manage manager, connect to sqlserver2005 in the program. Pay attention to the following points.
1. to enable the sql2005 remote connection function, follow these steps, configuration tool-> SQL server peripheral application configurator-> service and connected peripheral application configurator-> open the Database Engine node under the MSSQLSERVER node, select "remote connection" first ", next, we recommend that you select "use TCP/IP and named pipes at the same time". After you confirm, restart the database service.
2. the Logon Setting is changed to "SQL server and windows Authentication". The specific settings are as follows: manage Manager-> windows Authentication (first entry in windows mode ), -> select your data server in object Explorer -- Right-click> Properties> security> SQL server and windows Authentication.
3: Set a user name and password for SQL server. The specific settings are as follows: manage Manager-> windows Authentication> new query> sp_password null, 'sa123456 ', in this way, a user with the username sa and password sa123456 is set. The next time you log on, you can use the SQL server method. The username is sa and the password is: the sa123456 user entered the database.
4: After completing the preceding three steps, you can write the connection string to the database,
(Server =. \ sqlexpress; uid = sa; pwd = sa123456; database = master ";

V. SQL2000 remote server connection method

1: Check whether the IP address of the ping server can be pinged.
2: Enter the IP port of the telnet server in Dos or command line to check whether the port can be connected. For example, telnet 202.114.100.100 1433 usually has a port value of 1433, because 1433 is the default listening port of SQL server 2000 for TCP/IP. If there is a problem, this step usually has a problem. The common prompt is "... Unable to open the connection, connection failed ".
If there is a problem with this step, check the following options.
1) check whether the remote server has started the SQL server 2000 Service. If not, start.
2) check whether the TCP/IP protocol is enabled on the server, because remote connection (via the Internet) depends on this protocol. The check method is to open the Start menu on the Server> program> Microsoft SQL Server> Server network utility to check whether the enabled protocol contains the TCP/IP protocol. If not, enable it.
3) check whether the server's TCP/IP Port is configured as port 1433. Check the TCP/IP attribute in the enabled protocol in the server network utility to make sure that the default port is 1433 and the check box of the hidden server is not checked. In fact, it is acceptable if the default port is modified. However, when the client performs a telnet test, the server port number must be consistent with the port number configured on the server. If the "Hide server" check box is selected, it means that the client cannot see this server by enumerating the server, which protects the server but does not affect the connection, however, the default port of TCP/IP protocol is implicitly modified to 2433, which must be changed when the client is connected.
4) if the operating system on the server has been patched with sp2, you must configure windows Firewall and open port 1433 to it, you can usually turn off the windows Firewall directly during testing (Other firewalls are also the best ).
5) check whether the server is listening on port 1433. If the server does not listen on port 1433 of the tcp connection, it cannot be connected. Check whether there are items similar to tcp 127.0.0.1 1433 listening in the result list by entering netstat-a-n or netstat-an Under the dos or command line of the server. If not, you usually need to patch SQL server 2000 with at least sp3. In fact, start the query analyzer on the server and enter select @ version to execute it. The version number must be patched for versions earlier than 8.0.2039. If there is no problem above, then you can perform the telnet server ip 1433 test. The screen will flash and the cursor will not stop flashing in the upper left corner. Congratulations, you can start to connect to the Enterprise Manager or query analyzer immediately.

3: Check the Client Setup Program-> Microsoft SQL Server-> client network tools. Make sure that the client TCP/IP protocol is enabled, as in the server network utility, and the default port is 1433 (or other ports, consistent with the server ).
4: in the Enterprise Manager or query the analyzer to connect to the test Enterprise Manager-> right-click SQlserver group-> Create sqlserver registration-> next-> Write Remote IP-> next-> select Sqlserver login-> next-> write the login name and password (sa, password)-> next-> query analyzer-> file-> connection-> Write Remote IP-> write login name and password (sa, password) -> it is generally recommended that you do this in the query analyzer. By default, the timeout setting for registering another SQL Server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds. To modify the default connection timeout method, choose Enterprise Manager> Tools> Options> SQL Server Enterprise Manager properties, click the "advanced" tab-> connection settings-> logon timeout (seconds) enter a large digital query analyzer in the box below-> tool-> options-> connection-> login timeout (seconds) enter a large number in the following box to connect. If an error is prompted, proceed to the next step.
5: the cause of the error is that SQL Server uses the "Windows only" authentication method, so you cannot use the Logon account of SQL Server (such as sa) to connect. The solution is as follows:
1) Use the enterprise manager on the Server side and select "use Windows Authentication" to connect to SQL Server.
2) Expand "SQL Server group", right-click the name of the SQL Server, select "properties", and then select the "Security" tab.
3) under "authentication", select "SQL Server and Windows ".
4) restart the SQL Server service. (In dos or command line, net stop mssqlserver to stop the service, and net start mssqlserver to start the service, which is also a quick method ).