//【1】连接字符串(方法一)
string connString = "Data Source=localhost;DataBase=LibraryDB;uid=sa;pwd=lwm110";
//【2】实例化Connection类并打开连接
SqlConnection Conn = new SqlConnection(connString);
Conn.Open();
//【3】显示连接后的信息
if (Conn.State == ConnectionState.Open)
{
MessageBox.Show("连接成功!" + Conn.DataSource + ' ' + Conn.Database + ' ' + Conn.ServerVersion + ' ' + Conn.ConnectionTimeout.ToString(), "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//【4】关闭连接
Conn.close();
--===================构造连接字符串(方法二)=======================
//实例化构造类
SqlConnectionStringBuilder objBuilder = new SqlConnectionStringBuilder();
//指定服务器
objBuilder.DataSource = "local";
//指定默认数据库
objBuilder.InitialCatalog = "TestDB";
//指定连接账号
objBuilder.UserID = "sa";
//指定密码
objBuilder.Password = "lwm110";
SqlConnection conn = new SqlConnection(objBuilder.ConnectionString);
--===================配置文件App.config中连接字符串(推荐)(方法三)=======================
【1】配置文件中添加以下信息
<connectionStrings>
<add name="connstring" connectionString="Data Source=local;DataBase=TestDB;UID=sa;PWD=lwm110"/>
</connectionStrings>
【2】在项目中添加引用-system.configuration
【3】在cs文件中“using System.Configuration;”
【4】 //将配置文件的连接信息赋值给连接字符串变量,["connstring"]为配置文件中name值
string connstring = ConfigurationManager.ConnectionStrings["connstring"].ToString();
/*===============传统的使用command类================*/
//【1】定义连接字符串
string connString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
//【2】实例化连接
SqlConnection conn = new SqlConnection(connString);
//【3】实例化一个command
SqlCommand cmd = new SqlCommand();
//【4】指定command类型为文本(默认)
cmd.CommandType = CommandType.Text;
//【5】指定使用哪个连接
cmd.Connection = conn;
//【6】指定执行哪个SQL语句
cmd.CommandText = "insert into student(SNO,Sname) values(95003,'JACK')";
/*===============简化的使用command类(推荐)================*/
//【1】定义连接字符串
string connString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
string sql = "insert into student(SNO,Sname) values(95003,'JACK')";
//【2】实例化连接
SqlConnection conn = new SqlConnection(connString);
//【3】实例化一个command
SqlCommand cmd = new SqlCommand(sql,conn);
/*===============command方法================*/
1、ExecuteScalar---->执行SQL语句,并返回查询结果第一行第一列,返回值Object类型
--示例
//【1】定义连接字符串
string connString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
string sql 01= "select max(age) from student";
string sql 02= "select min(age) from student";
string sql 03= "select avg(age) from student";
//【2】实例化连接
SqlConnection conn = new SqlConnection(connString);
//【3】实例化command并执行三次
SqlCommand cmd01 = new SqlCommand(sql01,conn);
SqlCommand cmd02 = new SqlCommand(sql02,conn);
SqlCommand cmd03 = new SqlCommand(sql03,conn);
//【4】执行
try
{
conn.Open();//打开连接
string str01=cmd01.ExecuteScalar().ToString();//执行Command
string str02=cmd02.ExecuteScalar().ToString();//执行Command
string str03=cmd03.ExecuteScalar().ToString();//执行Command
}
catch(Exception ex)
{
MessageBox.Show("执行出错,具体原因:"+ex.Message,"系统消息",MessageBoxButton.OK,MessageBoxIcon.Infomation);
}
finally
{
conn.Close();//关闭连接
}
2、ExecuteNonQuery--->执行SQL语句,返回受影响的行数,返回值Int类型
int result = cmd.ExecuteNonQuery();
3、ExecuteReader--->执行查询,返回一个DataReader类型
FieldCount-->记录中有多少个字段
HasRows-->表示DataReader是否为空
IsClosed-->DataReader是否关闭
-----【案例一】DataReader数据加载到List(一个结果集)-----
//【1】连接字符串
string connString = "Data Source=XH;DataBase=LibraryDB;UID=sa;PWD=lwm110";
string sql = "select SNO,SName,Sage,Sex,MobileNO from student";
//【2】实例化connection对象
SqlConnection conn = new SqlConnection(connString);
//【3】实例化command对象
SqlCommand cmd = new SqlCommand(sql,conn);
//执行command并接收返回结果
try
{
//打开连接
conn.Open();
//执行命令并接收返回新技术
objReader = cmd.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show("加载数据失败,具体原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//把DataReader中的数据存储在List<Student>
//【1】先判断有没有数据
if (!objReader.HasRows)
{
MessageBox.Show("没有读取到数据!", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
//【2】读取DataReader数据中的数据--游标
while (objReader.Read())
{
objList.Add(
new Student
{
SNO = Convert.ToInt32(objReader[0]),
SName = objReader["SName"].ToString(),
Age = Convert.ToInt32(objReader["Sage"]),//不能为NULL
Gender = objReader["Sex"].ToString(),
Mobile = objReader["MobileNO"].ToString(),
}
);
}
//关闭DataReader对象
objReader.Close();
conn.Close();
//初始化GridView,加载数据(对应自定义类中属性名)
dataGridView1.DataSource = null;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = objList;
}
-----【案例二】DataReader数据加载到List(二个结果集)-----
private SqlDataReader objReader;
private List<Student> objListStudent = new List<Student>();
private List<Book> objListBook = new List<Book>();
----------------------------------------------------------------------------------------------------------------------
//【1】连接字符串
string connString = "Data Source=XH;DataBase=LibraryDB;UID=sa;PWD=lwm110";
string sql = "select SNO,SName,Sage,sex,MobileNO from student;select BookID,BookName from Book";
//【2】实例化Connection对象
SqlConnection conn = new SqlConnection(connString);
//【3】实例化Command对象
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();//打开连接
objReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行并接收返回值
}
catch (Exception ex)
{
MessageBox.Show("加载数据失败,具体原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//读取数据并加载到List
if (!objReader.HasRows) MessageBox.Show("没有读取到数据!", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
else
{
//读取第一个结果集
while (objReader.Read())
{
objListStudent.Add(
new Student
{
SNO = Convert.ToInt32(objReader["SNO"]),
SName = objReader["SName"].ToString(),
Age = Convert.ToInt32(objReader["Sage"]),
Gender = objReader["Sex"].ToString(),
Mobile = objReader["MobileNO"].ToString(),
});
}
//读取下一个结果集
if (objReader.NextResult())
{
while (objReader.Read())
{
objListBook.Add(
new Book
{
BookID = Convert.ToInt32(objReader["BookID"]),
BookName = objReader["BookName"].ToString(),
});
}
}
//关闭DataReader
objReader.Close();
}
//加载到GridView中
dataGridView1.DataSource = null;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = objListStudent;
dataGridView2.DataSource = null;
dataGridView2.AutoGenerateColumns = false;
dataGridView2.DataSource = objListBook;
-----【案例三】DataReader数据加载到DataTable----
private SqlDataReader objReader;
private DataTable objTable=new DataTable();
--------------------------------------------------------------------------------------------------
string connString = "Data Source=XH;DataBase=LibraryDB;uid=sa;pwd=lwm110";
string sql = "select SNO,SName,Sage,sex,MobileNO from student";
//实例化Connection对象
SqlConnection conn = new SqlConnection(connString);
//实例化Command对象
SqlCommand cmd = new SqlCommand(sql, conn);
//执行并接收返回结果
try
{
conn.Open();//打开连接
objReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行cmd,接收返回结果
}
catch (Exception ex)
{
MessageBox.Show("获取数据失败,具体原因:"+ex.Message,"系统消息",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
if (!objReader.HasRows) MessageBox.Show("没有读取到数据!", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
else
{
objTable.Load(objReader);//加载到DataTable中
objReader.Close();//关闭DataReader
//初始化GridView,加载数据(对应表中字段名)
dataGridView1.DataSource = null;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = objTable;
}