就是c#如何链接sql server 数据库,昨天搞了一天,好像没有连上,网上的资料也搜到不少,自己也看了,知道一步一步的会连接上,由于是处手,请教大侠一下:
1、连接数据库的有没有固定的代码,就是标准版的。有的话发个链接;
2、我看过好多,要连接数据库都要写一写代码,而且大部分都是一样的,如下: String strcon;
String strcom;
SqlConnection Conn = new SqlConnection();
SqlCommand Comm = new SqlCommand();
SqlDataReader DR;
strcon = "Data Source=.;uid=sa;pwd=123456;database=temp";
Conn.ConnectionString = strcon;
strcom = "select * from temp"; Conn.Open();
Comm.Connection = Conn;
Comm.CommandText = strcom;
DR = Comm.ExecuteReader();
if (DR.Read())
等等。
为什么不直接搞个类每次调用?
里面有很多学问,大家帮忙解释一下,本人刚入手,想快点上手,什么不懂的请大家赐教。
1、连接数据库的有没有固定的代码,就是标准版的。有的话发个链接;
2、我看过好多,要连接数据库都要写一写代码,而且大部分都是一样的,如下: String strcon;
String strcom;
SqlConnection Conn = new SqlConnection();
SqlCommand Comm = new SqlCommand();
SqlDataReader DR;
strcon = "Data Source=.;uid=sa;pwd=123456;database=temp";
Conn.ConnectionString = strcon;
strcom = "select * from temp"; Conn.Open();
Comm.Connection = Conn;
Comm.CommandText = strcom;
DR = Comm.ExecuteReader();
if (DR.Read())
等等。
为什么不直接搞个类每次调用?
里面有很多学问,大家帮忙解释一下,本人刚入手,想快点上手,什么不懂的请大家赐教。
<appSettings>
<add key="SqlConnect" value="workstation id=Test;user id=用户名;pwd=密码;data source=192.168.1.10;persist security info=True;initial catalog=数据库"/>
</appSettings>
<system.web>
</configuration>
http://topic.csdn.net/u/20090818/16/8d2bf3a0-f0b8-452c-a2d4-43cdbc3b4db4.html
这个是不是要改?还是搞不定。在详细点啊。
http://www.cnblogs.com/josephshi/archive/2008/01/15/1039303.html
用的话,就配置好 数据库的连接字符串和执行的SQL语句就可以了。其实熟悉的话,自己写或者完善这个sql helper类最好了。 而且可以加上 事务处理,防SQL注入攻击等功能不同的企业,也有自己编写的不同的DB ACCESS组件。
VS2008里的 LINQ TO SQL, DATASET XSD都间接实现了数据访问层的实现
你的代码:
String strcon;
String strcom;
SqlConnection Conn = new SqlConnection();
SqlCommand Comm = new SqlCommand();
SqlDataReader DR;
strcon = "Data Source=.;uid=sa;pwd=123456;database=temp";
Conn.ConnectionString = strcon;
strcom = "select * from temp"; Conn.Open();
Comm.Connection = Conn;
Comm.CommandText = strcom;
DR = Comm.ExecuteReader();
if (DR.Read())
可能存在的问题:
连接字符串这样改试试strcon="Server=.;uid=sa;pwd=123456;database=temp";如果还不行,你考虑把Server后面的"."改成机器名.个别机器,不知道是设置问题还是怎么回事,必须输入机器名,才行.
另外,在进行以上调试时,切记把你的SqlServer的安全验证方式设成"混合验证",而不是"Windows验证"
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnType" value="sql"/>
</appSettings>
<dataConfiguration defaultDatabase="Connection String" />
<connectionStrings>
<add name="ConnectionString" connectionString="Database=AccpDB;Server=(local);uid=sa;pwd=sa;"
/>
</connectionStrings>
</configuration>然后在用的地方调用或者写一个类专门存放连接数据库的
{
private static SqlConnection conn; public static SqlConnection Conn
{
get
{
String connStr = "Data Source=.;uid=sa;pwd=123456;database=temp"; if (conn == null)
{
conn = new SqlConnection(connStr);
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
return conn;
}
} public static SqlDataReader ExecuteSelect(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Conn);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static SqlDataReader ExecuteSelect(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static DataTable GetDataTable(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.AddRange(values);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
return ds.Tables[0];
} public static DataTable GetDataTable(string sql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
return ds.Tables[0];
} public static int ExecuteUpdate(string sql,params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
return result;
}
}
SqlConnection conn = new Sqlconnection("server=192.168.0.1;uid=sa;pwd=sa;database=northwind"); //建立连接,字符串根据情况改
SqlCommand cmd = new SqlCommand("select * from products",conn); //创建查询命令对象
conn.open();//打开数据库链接
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);//填充数据集
这下lz明白了吧。。够详细了
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;namespace ConsoleApplication9
{
class Program
{
static void Main(string[] args)
{
Test3 te = new Test3();
te.SqlTest();
Console.Read();
} class Test3
{
//创建连接字符串
string connString = "Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;pwd=sa"; //连接数据库的方法
public void SqlTest()
{
//创建Connection对象
SqlConnection conn = new SqlConnection(connString);
//编写SQL语句
string sql = "select * from student";
//创建Command对象
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Console.WriteLine("ID->" + Convert.ToInt16(dr[0]));
Console.WriteLine("NAME->" + Convert.ToString(dr[1]));
}
dr.Close();
conn.Close();
}
}
}
}//创建一个控制台应用程序..直接复制就行
//把数据库脚本在sql server2005 执行一下就OK
数据库代码 use master
goif exists (select * from sysdatabases where name = 'Test')
drop database Testcreate database Test
on
(
name='Test',
filename='E:\Test.mdf',
size=10mb,
maxsize=100mb,
filegrowth=10%
)
go
use test
create table student
(
Sid int identity(1,1) not null,
Sname varchar(20) not null
)create table course
(
Cid int identity(1,1) not null,
Cname varchar(20) not null,
Tearcher varchar(20) not null
)create table grade
(
WrittenExam int not null,
LibExam int not null,
Gid int not null
)
alter table student add constraint PK_student_Sid primary key (Sid)
alter table course add constraint pk_course_Cid primary key (Cid)
alter table course add constraint uk_course_Cname unique(Cname)
alter table grade add constraint fk_grade_Gid foreign key(Gid) references student(Sid)
insert into student (Sname) values ('小田')
insert into student (Sname) values ('小西')
insert into student (Sname) values ('小泽')
insert into course (Cname,Tearcher) values ('java','小翟')
insert into course (Cname,Tearcher) values ('.net','小徐')
insert into course (Cname,Tearcher) values ('英语','小花')
insert into grade (WrittenExam,LibExam,Gid) values (60,98,1)
insert into grade (WrittenExam,LibExam,Gid) values (70,80,2)
insert into grade (WrittenExam,LibExam,Gid) values (80,70,3)另外还有更新删除修改..可以去看我的博客..
http://beta.hi.csdn.net/link.php?url=http://blog.csdn.net%2Ft6786780
算是一条龙服务了..
给分...
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="conn1" connectionString="server=.;database=数据库名;uid=sa;pwd=sa"/>
</connectionStrings>
</configuration>/// <summary>
/// 数据库连接类
/// </summary>
public class DBHelper
{
/// <summary>
/// 连接池对象
/// </summary>
private SqlConnection conn;
public SqlConnection Conn
{
get
{
if (conn == null)
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn1"].ConnectionString);
return conn;
}
}
/// <summary>
/// 打开连接池
/// </summary>
public void OpenConn()
{
if (Conn.State == ConnectionState.Closed)
Conn.Open();
}
/// <summary>
/// 关闭连接池
/// </summary>
public void CloseConn()
{
if (Conn.State == ConnectionState.Open)
Conn.Close();
} /// <summary>
/// 返回受影响的行数(适合于增、删、改操作)
/// </summary>
/// <param name="sql">执行的SQL语句</param>
/// <param name="type">执行的命令类型</param>
public bool ExcuteNonQuery(string sql, CommandType type)
{
int result = 0;
try
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.CommandType = type;
result = cmd.ExecuteNonQuery();
}
catch
{ }
finally
{
CloseConn();
}
if (result > 0)
return true;
else
return false;
} /// <summary>
/// 返回首行首列的值(适用于查询记录数等)
/// </summary>
/// <param name="sql">执行的SQL语句</param>
/// <param name="type">执行的命令类型</param>
/// <returns></returns>
public string ExcuteScaler(string sql, CommandType type, SqlParameter[] paras)
{
string result = string.Empty;
try
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, Conn);
if (paras != null && paras.Length > 0)
cmd.Parameters.AddRange(paras); //指定命令类型
cmd.CommandType = type;
object obj = cmd.ExecuteScalar();
if (obj != null)
{
result = obj.ToString();
}
}
catch { }
finally
{
CloseConn();
}
return result;
}
/// <summary>
/// 返回DataReader(适用于少量数据查询)注意:在调用该方法后,必须关闭连接池
/// </summary>
/// <param name="sql">执行的SQL语句</param>
/// <param name="type">执行的命令类型</param>
/// <returns></returns>
public SqlDataReader ExcuteDataReader(string sql, CommandType type)
{
SqlDataReader read = null;
try
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.CommandType = type;
read = cmd.ExecuteReader();
}
catch { }
finally
{
//CloseConn();
}
return read;
}
/// <summary>
/// 返回DataSet(适用于较大数据量查询)
/// </summary>
/// <param name="sql">执行的SQL语句</param>
/// <param name="type">执行的命令类型</param>
/// <returns></returns>
public DataSet ExcuteDataSet(string sql, CommandType type)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.CommandType = type;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch { }
return ds;
}
SqlConnectionStringBuilder stringconntion = new SqlConnectionStringBuilder();
stringconntion.DataSource = @".\sqlexpress";
stringconntion.InitialCatalog = "student";//数据库;;
stringconntion.IntegratedSecurity = true;
SqlConnection mycommed = new SqlConnection(stringconntion.ConnectionString);