SQL Server C # connection and object existence judgment

SQL Server C # connection and object existence judgment

This article provides a comprehensive overview of C # database connection.

For C # connection to ms SQL Server, sqlconnection is primarily used. The authentication mode is windows integrated authentication or SQL Server Authentication. The connection statement is 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 ";

Note:

Refer:

1) server and data source: after the experiment, the effect is similar to that of the two, just take one of them. You can use the host name or the current host. However, we usually install data sources or hosts similar to hostname \ SQLEXPRESS. Therefore, we need to write them as "server =. \ SQLEXPRESS;" or "data source = hostname \ SQLEXPRESS.

2) database and initial catalog: the effects of both are similar. Mainly used to specify the database

3) integrated security: Optional values: true, false, yes, no, or SSPI. SSPI indicates Security Support Provider Interface, and the effect is equivalent to true. If this parameter is set to true, windows authentication is used. In this case, the specified user name and password are invalid. If the integrated security value is not specified or the value is false, you must specify the username and password and use SQL Server to log on.

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 'isusertable') = 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 determines that an object exists

When we perform SQL operations, if we need to run automated scripts, we often run the second and third times, some operations that we run for the first time may not be needed. Or we may need to decide what operations are needed based on the database status. In this case, it is very important to judge whether some objects in the database exist or not. For example, to know whether a database exists, to create a table, but not to know if it already exists, and to avoid errors caused by misoperations, it is useful to judge whether they exist.

The following lists some statements used to determine the existence of an object, which is of reference significance.

1. Determine whether the database exists
If exists (select * from sys. databases where name = 'database name ')
Drop database [database name]
2. Check whether 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 whether 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. Determine whether a temporary table exists
If object_id ('tempdb .. # temporary table name') is not null
Drop table # temporary table name
5. Determine 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 whether a function exists.
-- Determine whether 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. obtain information about the object created by the user
SELECT [name], [id], crdate FROM sysobjects where xtype = 'U'
/*
Xtype indicates the parameter type, which usually includes the following
C = CHECK Constraints
D = DEFAULT value or DEFAULT Constraint
F = foreign key constraint
L = Log
FN = scalar function
IF = embedded table functions
P = Stored Procedure
PK = primary key constraint (type: K)
RF = copy and filter the Stored Procedure
S = system table
TF = table functions
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 name
9. Determine whether the column is an auto-incrementing column.
If columnproperty (object_id ('table'), 'col', 'isidentity ') = 1
Print 'auto-incrementing column'
Else
Print 'not auto-incrementing column'
SELECT * FROM sys. columns WHERE object_id = OBJECT_ID ('table name ')
AND is_identity = 1
10. Check whether an index exists in the table.
If exists (select * from sysindexes where id = object_id ('table name') and name = 'index name ')
Print 'exist'
Else
Print 'does not exist
11. view objects in the database
SELECT * FROM sys. sysobjects WHERE name = 'object name'

This article permanently updates the link address: