SQL Server C # Connection and object existence judgment

C # connect to SQL Server

The introduction of C # Connection database, this article summarizes the more comprehensive. http://blog.csdn.net/candy1232009/article/details/7654927

For C # connectivity to MS SQL Server, use SqlConnection primarily. The authentication method mainly adopts Windows Integrated authentication mode or SQL Server Authentication model. 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 ";

Description:

Reference: http://blog.csdn.net/wyaspnet/article/details/5627334

1) Server and data source: After the experiment the effect is similar, any one can take one. You can use the host name or the current machine "." Replace. However, we usually install a data source or host similar to hostname\sqlexpress, so it needs to be written as "server=.\sqlexpress;" Or the form "Data source=hostname\sqlexpress".

2) database and initial Catalog: both seem to have the same effect. Used primarily to specify a database

3) Integrated Security: You can take a value of true, false, yes, no, or SSPI. SSPI represents Security support Provider Interface, which is equivalent to true. When set to True, Windows authentication will be used, and the user name password specified will be invalid. When you do not specify a integrated security value or a value of false, you need to specify a user name and password to 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 Varch            AR (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 Judging object exists

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

This URL lists some of the statements that determine the existence of objects, which is very useful. (http://www.cnblogs.com/slcc/archive/2012/04/13/2445308.html)

1 determining whether a database exists
if exists (SELECT * from sys.databases WHERE name = ' database name ')
drop database [DB name]
2 Determining whether a 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 determining 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 determining whether a temporary table exists
If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名
5 Judging whether the 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 determining whether a 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 '
/*
The xtype represents the parameter types, usually including the following
C = CHECK Constraint
D = defaults or DEFAULT constraints
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 determining whether a column exists
if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE table name drop column name
9 Judging whether a column is self-increment
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-increment column '
Else
print ' is not 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 ' exists '
Else
print ' does not exist
11 viewing objects in a database
SELECT * from sys.sysobjects WHERE name= ' object name '

SQL Server C # Connection and object existence judgment