数据库连接语句集

数据库连接语句
Access:
String conStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*.mdb";

SQL Server2000:
1、<appSettings>
  <add key="DSN" value="Server=(local)/IP;database=ShuXinOA;User id=sa;Password=; Max Pool Size = 512"/>
</appSettings>

2、string  conStr="initial catalog=northwind;integrated security=sspi;data source=127.0.0.1"; //系统验证
需要手动在数据库上加上aspnet用户 
  
3、string  conStr="uid=sa;password=;initial catalog=northwind;data source=127.0.0.1";

string connstr = "data source=(local);initial catalog=ShuXinOA;integrated security=SSPI;Connect Timeout=30;";

SQL Server2005:

string conStr="Persist Security Info=false; integrated Security=true;database=northwind;";

ADO.NET
1.ADO.NET五大对象:
SqlConnection,
SqlCommand,
SqlDataReader,
qlDataAdapter,
DataSet

2.参数使用方法:

CREATE Proc Proc_Address @CardNo char(20),@ToAddress char(50),@CCAddress char(50) ,@YYYYMM char(12)
as
declare @strSql   char (400)
set @strSql = 'Insert into EmailAddress' + @YYYYMM + ' values(''+@CardNo+'',''+@ToAddress+'',''+@CCAddress+'',''+'1')'      --插入一条记录
exec (@strSql)                                                                       --也可以用insert into 语句
GO

//SQL SERVER的数据库连接
SqlConnection conn;
string dns =System.Configuration.ConfigurationSettings.AppSettings["ConStr"];//连接串
conn=new SqlConnection(dns);

SqlCommand cmd =conn.CreateCommand();
cmd.CommandType =CommandType.StoredProcedure;
cmd.CommandText="Proc_Address";

SqlParameter paraCardNo =cmd.Parameters.Add("@CardNo",SqlDbType.Char);
SqlParameter paraToAddress =cmd.Parameters.Add("@ToAddress",SqlDbType.Char);
SqlParameter paraCCAddress =cmd.Parameters.Add("@CCAddress",SqlDbType.Char);
SqlParameter paraYYYYMM =cmd.Parameters.Add("@YYYYMM",SqlDbType.Char);

//表示是输出参数
paraCardNo.Direction = ParameterDirection.Input;
paraToAddress.Direction = ParameterDirection.Input;
paraCCAddress.Direction = ParameterDirection.Input;
paraYYYYMM.Direction = ParameterDirection.Input;

//参数赋值
paraCardNo.Value = myDrv[0].ToString().Trim();
paraToAddress.Value = myDrv[1].ToString().Trim();
paraCCAddress.Value = myDrv[2].ToString().Trim();
paraYYYYMM.Value = ddlYear.Items[ddlYear.SelectedIndex].Value + ddlMonth.Items[ddlMonth.SelectedIndex].Value;

conn.Open();
cmd.ExecuteNonQuery();//写入SQL数据库
}
}
catch
{
Page.Response.Write("alert('第"+count.ToString()+"条数据出错!');";
objConn.Close();//关闭EXCEL的连接
}

objConn.Close();//关闭EXCEL的连接