ODBC
o Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
o Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
o Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
· OLEDB, OleDbConnection (.NET)
o Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
o Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
o Prompt for username and password:
oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=Aron1;Initial Catalog=pubs;"
o Connect via an IP address:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
· SqlConnection (.NET)
o Standard Security:
"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
o Trusted Connection:
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
o Connect via an IP address:
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
o Declare the SqlConnection:
C#:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString="my connectionstring";
oSQLConn.Open(); VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()
o Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
o Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
o Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
· OLEDB, OleDbConnection (.NET)
o Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
o Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
o Prompt for username and password:
oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=Aron1;Initial Catalog=pubs;"
o Connect via an IP address:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
· SqlConnection (.NET)
o Standard Security:
"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
o Trusted Connection:
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
o Connect via an IP address:
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
o Declare the SqlConnection:
C#:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString="my connectionstring";
oSQLConn.Open(); VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()
{
SqlConnection myConnection = new SqlConnection();//生成新的实例
myConnection.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30";//连接字符串
myConnection.Open();//打开
}
using System.Data;
using System.Data.SqlClient;
public static SqlDataReader myDataReader(string sqlcmd)
{
string ConStr="Server=server1; UID=sa; PWD=; DataBase=mydb";
SqlConnection SqlServer=new SqlConnection(ConStr);
SqlCommand SqlCmd =new SqlCommand();
SqlCmd.CommandText=sqlcmd;
SqlCmd.CommandType=CommandType.Text;
SqlCmd.Connection=SqlServer;
SqlServer.Open();
return SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
}public static DataSet myDataSet(string sqlcmd)
{
string ConStr="Server=server1; UID=sa; PWD=; DataBase=mydb";
SqlConnection SqlServer=new SqlConnection(ConStr);
SqlCommand SqlCmd =new SqlCommand();
DataSet ds=new DataSet();
SqlDataAdapter sda= new SqlDataAdapter(sqlcmd,ConStr);
SqlServer.Open();
sda.Fill(ds);
SqlServer.Close();
return ds;
}public static string StarExecute(string sqlcmd)
{
string ConStr="Server=server1; UID=sa; PWD=; DataBase=mydb";
SqlConnection SqlServer=new SqlConnection(ConStr);
SqlCommand SqlCmd =new SqlCommand();
string res="";
try
{
SqlCmd.Connection=SqlServer;
SqlCmd.CommandType=CommandType.Text;
SqlCmd.CommandText=sqlcmd;
SqlServer.Open();
SqlCmd.ExecuteNonQuery();
res="0";
}
catch(Exception ee)
{
res=ee.ToString();
}
finally
{
SqlServer.Close();
}
return res;
}
这很简单,C#访问数据库方式有三种,有一个专门用于SQL的。
如果你的是用WINNT方式登录SQL的,可以用这样的代码:
string myConnectionString;
myConnectionString="Initial Catalog=要连接的数据名;DataSource=localhost;Integrated Security=SSPI;";
SqlConnection myConnection;
myConnection=new SqlConnection(myConnectionString);
myConnection.Open();
如果你的SQL需要用户名和密码登录,可以用这样的代码连接:
string myConnectionString;
myConnectionString="Initial Catalog=要连接的数据名;DataSource=localhost;uid=用户名,pwd=密码;";
SqlConnection myConnection;
myConnection=new SqlConnection(myConnectionString);
myConnection.Open();
string str ="server=workflow;uid=sa;pwd=workflow123;database=Northwind";Web.config for an ASP.NET Web application: <configuration>
<appSettings>
<add key="DBConnStr" value="server=(local);Integrated Security=SSPI;database=northwind"/>
</appSettings>
</configuration>
1. 选用的数据库引擎不一样。访问Sql server 7.0的指向数据库的连接要用以下语句:
string strConnect = " Provider=SQLOLEDB.1 ; Persist Security Info=False ; User ID = sa ; Initial Catalog=xsgl ; Data Source = czdy1 " ;
其中"Initial Catalog"是要选用的数据库名称。"Data Source"是提供数据库服务的服务器名称。
2. 不要在导入System..Windows.Forms名称空间
这是因为访问的是远程数据库,不需要用到Application类了。
访问远程数据库的程序源代码--second.cs
using System ;
using System.Data.OleDb ;
// 导入程序中用的的所有名称空间
class OleDbTest {
public static void Main ( )
{
string strConnect = " Provider=SQLOLEDB.1 ; Persist Security Info=False
; User ID = sa ; Initial Catalog=xsgl ; Data Source = czdy1 " ;
OleDbConnection aConnection = new OleDbConnection ( strConnect ) ;
// 建立指向数据库的连接
OleDbCommand aCommand = new OleDbCommand ( "select * from xsk" ,
aConnection ) ;
// 设计所需要返回的数据集的内容
try {
aConnection.Open ( ) ;
// 打开指向数据库连接
OleDbDataReader aReader = aCommand.ExecuteReader ( ) ;
// 返回需要的数据集内容
Console.WriteLine ( "以下就是打开后的数据集的一个字段的所有内容!" ) ;
while ( aReader.Read ( ) ) {
Console.WriteLine ( aReader.GetString (0) ) ;
}
// 屏幕输出数据集的第一个字段的所有内容,如果要第二个字段把"0"改为"1"
aReader.Close ( ) ;
// 关闭数据集
aConnection.Close ( ) ;
// 关闭指向数据库的连接
}
catch ( OleDbException e )
{
Console.WriteLine ( "错误类型:", e.Errors[0].Message ) ;
file://如果出错,输出错误信息
}
}
}