C # connects to the SQL Server database instance and determines whether the object exists

C # Connecting to SQL Server databases


For the different. NET data provider, ADO. NET uses different connection objects to connect to the database. These connection objects mask the specific implementation details, and provide a unified implementation method.

There are four types of connection: Sqlconnection,oledbconnection,odbcconnection and OracleConnection.

The object of the SqlConnection class connects to the SQL Server database, the object of the OracleConnection class connects to the Oracle database, and the object of the OleDbConnection class connects to a database that supports OLE DB, such as access , and objects of the OdbcConnection class connect to any ODBC-enabled database. All communication with the database is ultimately done through the connection object.
(1) Connect SQL Server with SqlConnection

Add namespace: Using System.Data.SqlClient;

Connection database: String constring = "Data source=ip address;" database= database name; user id= username; password= password ";

SqlConnection myconnection = new SqlConnection (constring);

Myconnection.open ();

(2) Connect Oracle with OracleConnection

Add namespace: Using System.Data.OracleClient;

Connection database: String constring = "Data source=ip address;" database= database name; user id= username; password= password ";

OracleConnection myconnection = new OracleConnection (constring);

Myconnection.open ();

(3) Connect MySQL with Mysqlconnection

There are two ways to connect to a MySQL database in. NET: MySQL connector/odbc and MySQL CONNECTOR/NET,ODBC connectors are ODBC-compliant interactive platforms and the best choice for. NET to access the MySQL database.

First, you need to download the installation Mysql-connector-net-5.1.5.data.msi this component. If this is the default installation, you can files\mysql\mysql Connector Net 5.1.5\binaries\ in C:\Program. Find MySql.Data.dll in NET2.0 and copy the file to the project's Bin directory. and add reference MySql.Data.dll to the project. The implementation code is as follows:

Add namespace: Using MySql.Data.MySqlClient;

Connection database: String constring = "Server=ip address;" database= database name; user id= username; password= password ";

Mysqlconnection myconnection = new Mysqlconnection (constring);

Myconnection.open ();

(4) Connect various data sources with OleDbConnection

Because the data source is different, the corresponding connection string is also different.

Add namespace: Using System.Data.OleDb;

Connect SQL server:string constring = "Provider=SQLOLEDB.1; Persist Security Info=false; User id= username; database= database name; Data source=computer; ";

OleDbConnection myconnection = new OleDbConnection (constring);

Myconnection.open ();

Connection access:string constring = "provider=microsoft.jet.oledb.4.0; Data source=c:\\database1.mdb; Persist security info=false; ";

OleDbConnection myconnection = new OleDbConnection (constring);

Myconnection.open ();

(You can also get a string by creating a. udl file)

Connection oracle:string constring = "Provider=msdaora; User id= username; password= password; Data source=db; Persist security info=false; ";

OleDbConnection myconnection = new OleDbConnection (constring);

Myconnection.open ();

(can also be connected via OracleConnection)

Note: You need to import different namespaces using different connection objects. The OleDbConnection namespace is System.Data.OleDb. The SqlConnection namespace is System.Data.SqlClient. The OracleConnection namespace is System.Data.OracleClinet.

We can connect to the database in two ways, using Integrated Windows authentication and using SQL Server Authentication to log on to the database.

1. Integrated Windows Authentication Syntax paradigm

String constr = "server=.; Database=myschool;integrated Security=sspi ";

Description: In the program code, a connection string is set up for the SQL Server database. Where server represents the name of the computer running SQL Server, because the program and the database system are located on the same computer, so we can use. (or localhost) replaces the current computer name. The database represents the name (MySchool) that is used. Because we want to adopt Integrated Windows authentication method, we set integrated security as SSPI.

2, the Windows Authentication Mode in SQL Server 2005 is as follows:

String constr = "server=.; Database=myschool;uid=sa;pwd=sa ";

Description: In the program code, a login with a known username and password is used to authenticate the database. The database connection string is case-insensitive. The UID is the specified database user name, PWD to the specified user password. For security reasons, generally do not include the username and password in your code, you can improve the security of the program by using the Integrated Windows authentication method above or by encrypting the connection string in the Web.config file.

3. SQL Server authentication mode in SQL Server 2005 is as follows:

String constr = "Data source=.; Initial Catalog=myschool;user Id=sa;pwd=sa ";

Description: Data source in program code represents the computer name of the running database, and initial catalog represents the database name used. The UID is the specified database user name, PWD to the specified user password.

4. The connection string for the Access database is in the following form:

String connectionString =@ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\datasource\myschool.mdb";

Description: In program code, you implement a database connection by using an OLE DB provider specifically for an Access database. This uses an OLE DB provider for microsoft.jet.oledb.4.0, and the database is stored in the C:\DataSource directory, and its database file is Myschool.mdb.

String constr = "server=.; Database=myschool;integrated Security=sspi ";
String constr = "server=.; Database=myschool;uid=sa;pwd=sa ";
String constr = "Data source=.; Initial Catalog=myschool;user Id=sa;pwd=sa ";
SqlConnection con = new SqlConnection (CONSTR);
Con. ConnectionString = Constr;
String sql = "SELECT COUNT (*) from grade";
SqlCommand com = new SqlCommand (Sql,con);
Try
{
Con. Open ();
MessageBox.Show ("Successful Connection Database");
int x = (int) com. ExecuteScalar ();
MessageBox.Show (String. Format ("successfully read {0}, record", X));
}
catch (Exception)
{

Throw
}
Finally
{
Con. Close ();
MessageBox.Show ("Successful shutdown of database connection", "hint info", messageboxbuttons.yesnocancel);
}

5, web.config configuration

In ASP.net 2.0, a new declarative expression syntax that resolves to a connection string value at run time is used, referencing the database connection string by name. The connection string itself is stored under the <connectionStrings> configuration section in the Web.config file so that it is easy to maintain for all pages in the application in a single location.

<?xml version= "1.0"?>
<configuration>
<connectionStrings>
<add name= "MySchool" connectionstring= "server=localhost;integrated security=true;database=myschool; Persist security Info=true "Providername=" System.Data.SqlClient "/>
</connectionStrings>
<system.web>
<pages stylesheettheme= "Default"/>
</system.web>
</configuration>

We can also read the database connection string directly from the configuration file in the following way. First we need to reference the using System.Web.Configuration namespace, which contains the classes that are used to set up the ASP.net configuration. String connectionString =configurationmanager.connectionstrings["MySchool"]. ConnectionString;

First you should distinguish between Windows validation and the SQL itself.
Windows authentication is a server that uses Windows with its own authentication system, and if you specify access permissions for a group of Windows within SQL Server, Windows users who join this group have permission to access the database. The disadvantage of this validation is that if you are not able to join a user of a remote computer in domain mode, you can use Windows authentication to make the local computer's Windows account inaccessible to the remote database server if you write the program using C/s mode.

SQL validation is much simpler, by using SQL Server's Enterprise Manager to define user permissions, and to specify user rights. This account information is maintained by SQL Server itself, so the information will not be lost after SQL Server has replaced the computer, without having to reset it.

So if your project is in a larger network and has high security requirements, you should set up a domain, use Windows authentication, and work with your system administrator to set up a Windows account that can access SQL Server in detail. If you are using a small network, and this network is used only for projects and has no high requirements for security, then use SQL Server Authentication, and update?? upgrades are easy.

Windows authentication is different from the database join string that SQL Server validates.


For C # connection to MS SQL Server, the main use is SqlConnection. Authentication methods are mainly used in Windows Integrated authentication method or SQL Server authentication mode. The connection statements are mainly as follows:

String constr = "server=.; Database=myschool;integrated Security=sspi ";

String constr = "server=.; Database=myschool;uid=sa;pwd=sa ";

String constr = "Data source=.; Initial Catalog=myschool;uid=sa;pwd=sa ";




about integrated Security=sspi/ture/false in SQL connection statements

Workaround:

namely: Security Support Provider Interface

When the integrated security is set to True, the UserID in front of the connection statement, PW, is not working, that is, the Windows Authentication mode.

Only set to False or omit the item, and then follow UserID, PW to connect.

Integrated security can be set to: true, false, yes, no, these four meanings are clear and can also be set to: SSPI, which is equivalent to true, is recommended instead of true.

What is the difference between initial catalog and database?

Initial Catalog:

DataBase:

There is no difference between the two, but the name is different, as if it were the real name of the human being. Can call you.

********************************************

Integrated SECURITY=SSPI This representation is logged on to the SQL Server server with the current Windows system user, and an error occurs if the SQL Server server does not support this way of logging in.

You can log in using SQL Server's username and password, such as:

"Provider=SQLOLEDB.1; Persist Security info=false;initial catalog= database name; Data source=192.168.0.1; User Id=sa; password= Password "

***************************************************

Integrated security-or-trusted_connection ' false ' when false, the user ID and password are specified in the connection. When True, authentication is performed using the current Windows account credentials. The recognized value is true, false, yes, no, and SSPI (strongly recommended) equivalent to True.

*************************************************

How to connect database in Ado.net

System.Data.SqlClient.SqlConnection

Some common connection strings (C # code):

SqlConnection conn = new SqlConnection ("server= (local); integrated security=sspi;database=pubs");

SqlConnection conn = new SqlConnection ("server= (local) \netsdk;database=pubs;integrated Security=sspi");

SqlConnection conn = new SqlConnection ("Data source=localhost;integrated security=sspi;initial catalog=northwind;");

SqlConnection conn = new SqlConnection ("Data source= (local); initial catalog=xr;integrated;

Persist Security Info=false;workstation Id=xurui;packet size=4096; “);

SqlConnection myconn = new System.Data.SqlClient.SqlConnection ("Persist Security info=false;integrated

Security=sspi;database=northwind;server=mysqlserver ");

SqlConnection conn = new SqlConnection ("Uid=sa;pwd=passwords;initial catalog=pubs;data source=127.0.0.1; Connect timeout=900 ");

A network-related or instance-specific error occurred while establishing a connection with SQL Server. The server was not found or could not be accessed. Please verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named pipe provider, error:40-cannot open a connection to SQL Server)

If your machine has sql2000 that data source=, it's not going to work.

Because the default for instance names 2000 and 2005 is the same, the instance of 2005 must not be source= with data. Represents

View the sql2005 instance name the SQLEXPRESS in the data source=.\sqlexpress replaced with your new instance names.

I installed the SQL Server, VS2008 EXPRESS, which is a self-contained database, so write data source as:

Data source=.\sqlexpress can. The. SQL2000 used before is not available on 2005.

Another problem that we have today is that the SQL?????????????????????????????????????????????????????? Login on Management Studio Express.

VS2008 actually has brought the database, as well as the database driver, the normal development and commissioning can be used to play. Just no database management tools, so unable to build the table, in fact, Microsoft provides a free management tool:

Install Microsoft SQL Server Management Studio Express to manipulate the database.

Download

Enable the SA method as follows, turn on MSE, log in with Windows authentication,

This can be used to log in with SA, of course, you can modify your password.

Once the database is open,. NET can connect the database, carry on the normal data access, then the development is much easier.

1 server and Data source: After the experiment both effect is similar, any take one can. You can use the host name or current machine "." Replace. But generally we install is similar to hostname\sqlexpress data source or host, so here needs to write "server=.\sqlexpress;" Or the "Data source=hostname\sqlexpress" form.

2 Database and Initial Catalog: Both effects seem to be similar. Used primarily to specify the database

3 Integrated Security: You can take a value of true, false, yes, no, or SSPI. SSPI represents the security Support Provider Interface, the effect is equivalent to true. When set to True, Windows authentication is used, and the username password specified will not be valid. When you do not specify a integrated security value or a value of false, you need to specify a username and password and log on using SQL Server.

Example:

String constr = @ "Data source=tan\sqlexpress;initial catalog=tan;integrated Security=sspi";
String constr = @ "Data source=tan\sqlexpress;initial catalog=tan;integrated security=false;uid=sa;pwd=123";
String constr = @ "server=.\sqlexpress;database=tan;uid=sa;pwd=123";
SqlConnection con = new SqlConnection (CONSTR);
String sql = @ ' IF not EXISTS (SELECT * from sysobjects WHERE id = object_id (N ' Persons ') and OBJECTPROPERTY (Id,n ' isusertabl E ') = 1)
CREATE TABLE Persons (id_p int,lastname varchar (255), FirstName varchar (255), address varchar (255), City varchar (255)) ";
Try
{
SqlCommand com = new SqlCommand (sql, con);
Con. Open ();
Console.WriteLine ("Connect sucess!");
Com. ExecuteNonQuery ();
}
catch (Exception e)
{
Console.WriteLine (E.tostring ());
}
Finally
{
Con. Close ();
}
Console.readkey ();

SQL Server judgment object exists

When we do SQL operations, if we need to automate the script runtime, often run the second time the third time, may be the first run of some operations do not need. Or we might decide what to do later depending on the state of the database, and it's important to judge whether some objects in the database exist or not. For example, if you want to know if a database exists, you want to create a table but don't know if it is already there, and want to avoid mistakes caused by misoperation, it is useful to judge them.

The following is a list of some of the statements that determine the existence of objects, which is of reference significance.

1 determine if the database exists
if exists (SELECT * from sys.databases WHERE name = ' database name ')
Drop DB [Database name]
2 Determine if the table exists
if exists (select * from sysobjects where id = object_id (n ' [table name] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table [table name]
3 determine if a stored procedure exists
if exists (select * from sysobjects where id = object_id (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [Stored procedure name]
4 to determine whether a temporary table exists
If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名
5 determining whether a view exists
--sql Server 2000
IF EXISTS (SELECT * from sysviews WHERE object_id = ' [dbo].[ View name] '
--sql Server 2005
IF EXISTS (SELECT * from sys.views WHERE object_id = ' [dbo].[ View name] '
6 determine if the function exists
--Determine if the name of the function to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [function name]
7 getting user-created object information
SELECT [name],[id],crdate from sysobjects where xtype= ' U '
/*
Xtype represents the type of parameter that typically includes the following
C = CHECK Constraint
D = defaults or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inline Table function
P = Stored Procedure
PK = PRIMARY KEY constraint (type is K)
RF = copy Filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User Table
UQ = UNIQUE constraint (type is K)
V = view
X = Extended Stored Procedure
*/
8 Determine whether a column exists
if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE name drop column column name
9 Determine if the column is self added
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-add column '
Else
print ' is not a self-adding column '
SELECT * from Sys.columns WHERE object_id=object_id (' Table name ')
and Is_identity=1
10 determine if an index exists in the table
if exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ')
print ' presence '
Else
print ' does not exist
11 viewing objects in a database
SELECT * from sys.sysobjects WHERE name= ' object name '