C#基础之ADO.Net笔记5
——杨中科老.Net师视频笔记
第一个mdf项目
在vs2008中新建一个ADO_net解决方案，并在此方案在新建一个名为"第一个mdf"的console项目。并在项目中添加一个"基于服务的数据库"。如下图：
文件结构：
Login.cs(program.cs)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace 第一个mdf
{
class Login
{
static void Main(string[] args)
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug")
|| dataDir.EndsWith(@"bin\Release"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
/**----------测试连接数据库-----------------------
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\第一个mdf\Database1.mdf;Integrated Security=True;User Instance=True"))
{ //用using进行资源的释放，当出了{}，花括号内部的资源自动被释放
//功能同try..catch...fially一样。
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into MyTable1(Name) values('abc')";
cmd.ExecuteNonQuery(); //执行一个非查询语句
Console.WriteLine("插入成功");
}
}
Console.WriteLine("打开数据库连接成功!");
* ==============================================================
*/
/*-----------一个简单的登录程序------------*/
/*
Console.WriteLine("请输入用户名:");
string username = Console.ReadLine();
Console.WriteLine("请输入密码:");
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\第一个mdf\Database1.mdf;Integrated Security=True;User Instance=True"))
{ //用using进行资源的释放，当出了{}，花括号内部的资源自动被释放
//功能同try..catch...fially一样。
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_User where UserName='" + username + "'";
//Console.WriteLine(username);
using (SqlDataReader reader=cmd.ExecuteReader())
{
if (reader.Read()) //到表中查用户输入的用户名对应的信息
{
//用户名存在
string dbpassword = reader.GetString(reader.GetOrdinal("Password")).Trim();
if (password == dbpassword)
{
Console.WriteLine("登录成功");
}
else
{
Console.WriteLine("密码错误,登录失败");
}
}
else //Read返回为false,说明没有找到用户输入的用户名
{
Console.WriteLine("用户名错误");
}
}
}
}
*/
//=====================允许用户往表里插入数据============================
//---------执行非查询语句、只返回一列数据的情况---------------------
/*
Console.WriteLine("请输入用户名:");
string username = Console.ReadLine();
Console.WriteLine("请输入密码:");
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\第一个mdf\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//在建立数据库表时，表名以T_开头，字段以F_开头
cmd.CommandText = "insert into T_User(UserName,PassWord) OUTPUT inserted.ID values('"+username+"','"+password+"')";
cmd.ExecuteNonQuery();
Console.WriteLine("新插入的主键的值："+cmd.ExecuteScalar()); //返回第1行第1列的数据
Console.WriteLine("插入成功");
cmd.CommandText = "select count(*) from T_User";
Console.WriteLine(cmd.ExecuteScalar()); //返回第1行第1列的数据
int i = Convert.ToInt32(cmd.ExecuteScalar()); //将返回的Objects类开转换成int型
Console.WriteLine(i);
}
}
*/
//-----------------执行返回结果集------------------------------
/*
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\第一个mdf\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_User";
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//Console.WriteLine(reader.GetString(1)); //取出当前行的第1列的数据
//Console.WriteLine("根据用户名得到："+reader.GetString(reader.GetOrdinal("UserName")));
//其中reader.GetOrdinal("UserName")是根据用户名得到这一列的序号
int id = reader.GetInt32(reader.GetOrdinal("Id"));
string username = reader.GetString(reader.GetOrdinal("UserName"));
string password = reader.GetString(reader.GetOrdinal("password"));
Console.WriteLine("id:"+id+" 用户名："+username+" 密码："+password);
}
}
}
}
*/
//--------------------带有sql注入漏洞-------------------------------
//密码输入 aaa' or 1='1 即可验证成功
/*
Console.WriteLine("请输入用户名:");
string username = Console.ReadLine();
Console.WriteLine("请输入密码:");
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\第一个mdf\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from T_User where UserName='" + username + "'and Password='" + password + "'";
int i = Convert.ToInt32(cmd.ExecuteScalar());
if (i > 0)
{
Console.WriteLine("登录成功！");
}
else
{
Console.WriteLine("用户名或者密码错误！");
}
}
}
*/
//--------------------消除注入漏洞--------------
//利用参数化查询
Console.WriteLine("请输入用户名:");
string username = Console.ReadLine();
Console.WriteLine("请输入密码:");
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\第一个mdf\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from T_User where UserName=@UN and Password=@p"; //使用占位符的形式
cmd.Parameters.Add(new SqlParameter("UN",username));
cmd.Parameters.Add(new SqlParameter("P",password));
int i = Convert.ToInt32(cmd.ExecuteScalar());
if (i > 0)
{
Console.WriteLine("登录成功！");
}
else
{
Console.WriteLine("用户名或者密码错误！");
}
}
}
Console.WriteLine("OK");
Console.ReadKey();
}
}
}
/*
insert into T_User(UserName,Password)
OUTPUT inserted.Id --输出主键的值，固定的格式
values('admin','888888')
*/
//Close:关闭以后还能打开。Dispose:直接销毁，不能再次使用。
//usingd在出了作用域以后调用Dispose,SqlConnection、FileStream等的Dispose内部都会做这样的判断：
// 判断有没有Close，如果没有Close,就先Close再Dispose。
//================================================================================
/**
查询所有表 select Sysobjects.Name from SysObjects
查询所有类型 select SysTypes.Name from SysTypes
查询所有列 select SysColumns.Name from SysColumns
查询列长度 select SysColumns.length from SysColumns
查询库 select * from master.dbo.sysdatabases order by dbid
获取用户库 ： select * from sysobjects where xtype='u'
根据库获取表 ： select * from 库名.dbo.sysobjects
根据表ID获取表字段 ：
select * from syscolumns where id=表ID
或者
select * from 库名.dbo.syscolumns where id=表ID
-----------------list the databases:----------------
EXEC sp_databases
EXEC sp_helpdb
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
EXEC sp_msForEachDB 'PRINT ''?'''
----------------------------------------------------
SELECT name, collation_name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
======================================================================
C#中Trim()、TrimStart()、TrimEnd()的用法：
这三个方法用于删除字符串头尾出现的某些字符。Trim()删除字符串头部及尾部出现的空格，
删除的过程为从外到内，直到碰到一个非空格的字符为止，所以不管前后有多少个连续的空格都会被删除掉。
TrimStart()只删除字符串的头部的空格。TrimEnd()只删除字符串尾部的空格。
如果这三个函数带上字符型数组的参数，则是删除字符型数组中出现的任意字符。如Trim("abcd".ToCharArray())
就是删除字符串头部及尾部出现的a或b或c或d字符，删除的过程直到碰到一个既不是a也不是b也不是c也不是d的字符才结束。
这里最容易引起的误会就是以为删除的是"abcd"字符串。如下例：
string s = " from dual union all ";
s = s.Trim().TrimEnd("union all".ToCharArray());
可能有人以为上面s的最终结果是"from dual"，但真正的结果是"from d"。需要注意的是这种写法执行的删除对象是字符
数组中出现的任意字符，而不是这些字符连在一起组成的字符串！
一般TRIM函数用法：
Trim() 功能删除字符串首部和尾部的空格。 语法Trim ( string ) 参数string：string类型，指定要删除
首部和尾部空格的字符串返回值String。函数执行成功时返回删除了string字符串首部和尾部空格的字符串，发生错误时返回
空字符串（""）。如果任何参数的值为NULL，Trim()函数返回NULL。
SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白。这个函数在不同
的资料库中有不同的名称:MySQL: TRIM(), RTRIM(), LTRIM() Oracle: RTRIM(), LTRIM() SQL Server: RTRIM(), LTRIM()
各种 trim 函数的语法如下： TRIM([[位置] [要移除的字串] FROM ] 字串): [位置] 的可能值为 LEADING (起头),
TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。
如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。 LTRIM(字串): 将所有字串起头的空白移除。
RTRIM(字串): 将所有字串结尾的空白移除。
*/
登录练习1
在方案中添加一个WinForm项目，取名"登录练习1"。
文件结构：
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace 登录练习1
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\")
|| dataDir.EndsWith(@"bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace 登录练习1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void IncErrorTimes() //输入错误次数加1
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\登录练习1\MyDB.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand updateCmd = conn.CreateCommand())
{
updateCmd.CommandText = "update T_Users Set ErrorTimes=ErrorTimes+1 where username=@username";
updateCmd.Parameters.Add(new SqlParameter("username", txtUserName.Text));
updateCmd.ExecuteNonQuery();
}
}
}
private void ResetErrorTimes() //重置错误次数
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\登录练习1\MyDB.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand updateCmd = conn.CreateCommand())
{
updateCmd.CommandText = "update T_Users Set ErrorTimes=0 where username=@username";
updateCmd.Parameters.Add(new SqlParameter("username", txtUserName.Text));
updateCmd.ExecuteNonQuery();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\登录练习1\MyDB.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_Users where UserName=@UserName";
cmd.Parameters.Add(new SqlParameter("UserName",txtUserName.Text));
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
int errorTimes = reader.GetInt32(reader.GetOrdinal("ErrorTimes"));
if (errorTimes > 3)
{
MessageBox.Show("登陆错误次数过多,禁止登陆");
return;
}
string dbpassword = reader.GetString(reader.GetOrdinal("Password"));
if (dbpassword == txtPassword.Text)
{
ResetErrorTimes(); //重置错误次数
MessageBox.Show("登陆成功");
}
else
{
/*
//在同一个连接中,如果SqlDataReader没有关闭,那么是不能执行update之类的语句
using (SqlCommand updateCmd = conn.CreateCommand())
{
updateCmd.CommandText = "update T_Users Set ErrorTimes=ErrorTimes+1 where username=@username";
updateCmd.Parameters.Add(new SqlParameter("username",txtUserName.Text));
updateCmd.ExecuteNonQuery();
}
*/
IncErrorTimes(); //输入错误次数加1
MessageBox.Show("登陆失败");
}
}
else
{
MessageBox.Show("用户名不存在!");
}
}
}
}
}
}
}
数据的导入导出
文件结构：
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
namespace 数据的导入导出
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
/*
if (ofdImport.ShowDialog() == DialogResult.OK)
{
using (FileStream fileStream = File.OpenRead(ofdImport.FileName)) //打开文件
{
using (StreamReader streamReader = new StreamReader(fileStream)) //创建StreamReader
{
string line = null;
while ((line = streamReader.ReadLine()) != null) //读取一行
{
string[] strs = line.Split('|');
string name = strs[0];
int age = Convert.ToInt32(strs[1]);
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\数据的导入导出\Import_DB.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into T_Persons(Name,Age) values(@Name,@Age)"; //插入数据
cmd.Parameters.Add(new SqlParameter("Name",name));
cmd.Parameters.Add(new SqlParameter("Age",age));
cmd.ExecuteNonQuery();
}
}
}
}
}
MessageBox.Show("导入成功");
}
*/
//------------优化以上代码------------------
if (ofdImport.ShowDialog() != DialogResult.OK) //如果用户没有点确定
{
return;
}
using (FileStream fileStream = File.OpenRead(ofdImport.FileName)) //打开文件
{
using (StreamReader streamReader = new StreamReader(fileStream)) //创建StreamReader
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\数据的导入导出\Import_DB.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open(); //创建连接
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into T_Persons(Name,Age) values(@Name,@Age)"; //插入数据
string line = null;
while ((line = streamReader.ReadLine()) != null) //读取一行
{
string[] strs = line.Split('|');
string name = strs[0];
int age = Convert.ToInt32(strs[1]);
cmd.Parameters.Clear(); //清除上次执行的参数,因为参数不能重复的添加
cmd.Parameters.Add(new SqlParameter("Name", name));
cmd.Parameters.Add(new SqlParameter("Age", age));
cmd.ExecuteNonQuery();
}
}
}
}
}
MessageBox.Show("导入成功");
}
}
}
省市选择
文件结构：
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace 省市选择
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//---------------测试往comboBox里添加对象----------------------
/*
Person p1 = new Person();
p1.Name = "tom";
p1.Age = 30;
MessageBox.Show(p1.ToString());
//默认的ToString()方法就是返回这个类的类名
//要使用comboBox显示名字,有两种方法:
//1.重写类的ToString()方法
//2.给comboBox控件添加DisplayMember属性
Person p2 = new Person();
p2.Name = "jim";
p2.Age = 20;
cmbProvince.Items.Add(p1);
cmbProvince.Items.Add(p2);
return;
*/
/**
采用从配置文件读取 连接字符串 要添加 System.Configuration 类的引用,步骤:在引用上点右键->添加引用...
*/
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//读取配置文件中的连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from province";
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
ProvinceItem item = new ProvinceItem();
item.Id = dataReader.GetInt32(dataReader.GetOrdinal("proID"));
item.Name = dataReader.GetString(dataReader.GetOrdinal("proName"));
cmbProvince.Items.Add(item); //将从数据库查出的item加到comboBox中
}
}
}
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
ProvinceItem item = (ProvinceItem)cmbProvince.SelectedItem;
int proID = item.Id;
/*
object obj1 = item;
ProvinceItem p2 = obj1;//把p2指向obj1指向的对象
ProvinceItem p2 = (ProvinceItem)obj1;
*/
cmbCity.Items.Clear();
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//读取连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from city where proID=@proID";
cmd.Parameters.Add(new SqlParameter("proID",proID));
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
string cityName = dataReader.GetString(dataReader.GetOrdinal("cityName"));
cmbCity.Items.Add(cityName); //将从数据库查出的item加到comboBox中
}
}
}
}
}
}
//------------------测试用例的类--------------------
/*
class Person
{
public string Name { get; set; }
public int Age { get; set; }
public override string ToString()
{
return Name;
}
}
*/
class ProvinceItem
{
public string Name {get;set;}
public int Id { get; set; }
}
}
手机号码归属地查询
文件结构：
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace 手机号码归属地查询
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\")
|| dataDir.EndsWith(@"bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Configuration;
using System.Data.SqlClient;
namespace 手机号码归属地查询
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnImport_Click(object sender, EventArgs e)
{
FolderBrowserDialog dlg = new FolderBrowserDialog();//new一个文件夹选择对话框
if (dlg.ShowDialog() != DialogResult.OK)
{
return;
}
string path = dlg.SelectedPath;
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = "delete from T_Numbers"; //清除表中的数据
cmd.ExecuteNonQuery();
}
}
string[] files=Directory.GetFiles(path,"*.txt",SearchOption.TopDirectoryOnly);//扫描此路径下的所有txt文件
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into T_Numbers(StartNo,EndNo,Name) values(@StartNo,@EndNo,@Name)";
foreach(string file in files)
{
string operatorsName=Path.GetFileNameWithoutExtension(file);
//MessageBox.Show(operatorsName);
string[] lines = File.ReadAllLines(file,Encoding.Default);
//解决乱码问题,加Encoding.Default使用系统默认编码(ASCII),ReadAllLines默认的UTF8
//不用StreamReader,因为文件很小，一次性加载也不占多少内存
foreach(string line in lines)
{
//MessageBox.Show(line);
//数据导入
string[] strs = line.Split('-');
string startNo = strs[0];
string endNo = strs[1];
string city = strs[2];
//MessageBox.Show(city);
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("StartNo",startNo));
cmd.Parameters.Add(new SqlParameter("EndNo",endNo));
cmd.Parameters.Add(new SqlParameter("Name", operatorsName+city));
cmd.ExecuteNonQuery(); //执行插入数据
}
}
}
}
MessageBox.Show("导入成功");
}
private void btnSearch_Click(object sender, EventArgs e)
{
//提示select * from T_Numbers where StartNo<=@No and EndNo>=@No
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_Numbers where StartNo<=@No and EndNo>=@No";
cmd.Parameters.Add(new SqlParameter("No",txtPhone.Text));
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("Name"));
MessageBox.Show("手机归属地:" + name);
}
else
{
MessageBox.Show("找不运营商信息");
}
}
}
}
}
}
}
尝试封装
文件结构：
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace 尝试封装
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\")
|| dataDir.EndsWith(@"bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
From1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using 尝试封装.testDataSetTableAdapters;
namespace 尝试封装
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//插入数据
SQLHelp.ExecuteNonQuery("insert into T_Persons(Name,Age) values(@Name,@Age)",new SqlParameter("Name","tom"),new SqlParameter("Age",30));
MessageBox.Show("插入数据成功");
}
private void button2_Click(object sender, EventArgs e)
{
object i = SQLHelp.ExecuteScalar("select count(*) from T_Persons");
MessageBox.Show(Convert.ToString(i));
}
private void button3_Click(object sender, EventArgs e)
{
SqlDataReader reader = SQLHelp.ExecuteReader("select * from T_Persons");
while (reader.Read()) //运行到这里报错，因为跟数据库的连接已关闭，利用DataSet可以解决这类问题
{
string name = reader.GetString(reader.GetOrdinal("Name"));
MessageBox.Show(name);
}
}
private void button4_Click(object sender, EventArgs e)
{
//注意只有在小数据量的时候才往DataSet里放，因为DataSet要占内存，大数据量的时候还是要用DataReader
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//连接字符串
DataSet dataset = new DataSet(); //定义一个DataSet
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_persons";
SqlDataAdapter adapter = new SqlDataAdapter(cmd); //执行select语句
adapter.Fill(dataset); //将执行结果得到的数据填充到dataset中
}
}
DataTable table = dataset.Tables[0];//取dataset的表中的第0条数据
for (int i = 0; i < table.Rows.Count; i++) //遍历每一行
{
DataRow row = table.Rows[i];
string name = Convert.ToString(row["Name"]);
MessageBox.Show(name);
}
}
private void button5_Click(object sender, EventArgs e)
{
DataTable dt = SQLHelp.ExcutetDataTable("select * from T_Persons");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string name=Convert.ToString(row["Name"]);
MessageBox.Show(name);
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
DataTable dt = SQLHelp.ExcutetDataTable("select * from T_Users where UserName=@UserName",
new SqlParameter("UserName",txtUserName.Text));
if (dt.Rows.Count <= 0)
{
MessageBox.Show("用户名不存在！");
}
else
{
DataRow row = dt.Rows[0];
int errorTimes = Convert.ToInt32(row["ErrorTimes"]);
if (errorTimes >= 3)
{
MessageBox.Show("登录次数过多！");
return;
}
string dbPassword = Convert.ToString(row["Password"]);
if (dbPassword == txtPassword.Text)
{
SQLHelp.ExecuteNonQuery("update T_Users set ErrorTimes=0 where UserName=@UserName",
new SqlParameter("UserName", txtUserName.Text));
MessageBox.Show("登录成功！");
}
else
{
SQLHelp.ExecuteNonQuery("update T_Users set ErrorTimes=ErrorTimes+1 where UserName=@UserName",
new SqlParameter("UserName",txtUserName.Text));
MessageBox.Show("密码错误！");
}
}
}
private void button6_Click(object sender, EventArgs e)
{
SQLHelp.ExcutetDataTable("select * from T_Users where Id=@Id",new SqlParameter("Id",(object)0));
//注：如果直接用0，SqlParameter会匹配成SqlParameter(string parameterName, SqlDbType dbType)这个函数，
//而SqlDbType 是一人枚举类型，0对应BigInt类型，所以不用 object强制转换，会出错。
}
private void button7_Click(object sender, EventArgs e) //修改DataSet
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//连接字符串
DataSet dataset = new DataSet(); //定义一个DataSet
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_persons";
SqlDataAdapter adapter = new SqlDataAdapter(cmd); //执行select语句
adapter.Fill(dataset); //将执行结果得到的数据填充到dataset中
DataTable table = dataset.Tables[0];
DataRow row = table.Rows[0];
row["Name"] = "jackson"; //修改Dataset中的数据
table.Rows.RemoveAt(1); //删除一行
DataRow dr=table.NewRow(); //新加一行
SqlCommandBuilder builder=new SqlCommandBuilder(adapter); //自动生成更新语句
adapter.Update(dataset); //更新DataSet,同步DataSet中的数据到数据库
}
}
}
private void button8_Click(object sender, EventArgs e) //测试强类型DataSet
{
T_UsersTableAdapter adapter = new T_UsersTableAdapter();
testDataSet.T_UsersDataTable data = adapter.GetData(); //获取数据
for (int i = 0; i < data.Count; i++)
{
testDataSet.T_UsersRow userRow = data[i];
MessageBox.Show(userRow.UserName);
}
}
}
}
可空数据类型
文件结构：
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace 可空数据类型
{
class Program
{
static void Main(string[] args)
{
string s1 = null;
//int i1=null;
int? i2 = 0;
int? i3 = null;//int? →可空的int,解决数据库和C#对于int是否可以为null的不同所设置的
if (i3 == null)
{
Console.WriteLine("i3为空");
}
else
{
i3++;
int i4 = (int)i3; //将可空的数据赋给不可空的,会报错,加(int)i3以保证i3一定不为空
Console.WriteLine("i3不为空,i3++={0}",i3);
}
if (i3.HasValue)
{
int i4 = i3.Value;
Console.WriteLine("i3不为空");
}
else
{
Console.WriteLine("i3为空");
}
int i6 = 10;
int? i5 = i6; //将不可空的赋给可空的,不会报错
}
}
}
强类型DataSet1
文件结构：
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace 强类型DataSet1
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\") //注意这个路径下面不要少加一个"\"
|| dataDir.EndsWith(@"bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="强类型DataSet1.Properties.Settings.DB1ConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB1.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using 强类型DataSet1.DB1DataSetTableAdapters;
namespace 强类型DataSet1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e) //强类型DataSet 测试1
{
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter();
DB1DataSet.T_PersonsDataTable personsTable = adapter.GetData();
//这里 DB1DataSet.T_PersonsDataTable 要写完整,因为T_PersonsDataTable是DB1DataSet的内部类
/*
DB1DataSet.T_PersonsRow p = personsTable[0];
if (p.IsNameNull())
{
MessageBox.Show("名字为空");
}
else
{
MessageBox.Show(p.Name);//如果不判断Name列的值为空，则会报错
}
*/
for (int i = 0; i < personsTable.Count; i++)
{
DB1DataSet.T_PersonsRow person = personsTable[i];
string msg = string.Format("姓名:{0},年龄:{1}",person.Name,person.Age);
MessageBox.Show(msg);
}
personsTable[0].Name = "aaa";
adapter.Update(personsTable); //更新数据
//插入数据
adapter.Insert("john",50);
//--------------这有个问题:更新和插入的数据没提交到数据库,引起数据库的变化--------------
}
private void button2_Click(object sender, EventArgs e) //测试DataSet添加自定义SQL语句的查询
{
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter();
adapter.GetOlder(); //调用自定义的SQL语句查询方法(得到年龄大于20的记录)
adapter.GetDataByAge(30); //调用包含参数的自定义查询方法
adapter.DeleteByAge(20); //删除年龄大于20的记录
int? count = adapter.GetPersonCount(); //得到记录总数
}
}
}
强类型登录
文件结构：
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace 强类型登录
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\") //注意这个路径下面不要少加一个"\"
|| dataDir.EndsWith(@"bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="强类型登录.Properties.Settings.DLConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
<!--connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DL.mdf;Integrated Security=True;User Instance=True"-->
<!--connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Workspace\C_Sharp\ADO_net\强类型登录\DL.mdf;Integrated Security=True;User Instance=True"-->
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using 强类型登录.DataSetUsersTableAdapters;
using System.Diagnostics;
namespace 强类型登录
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnLogin_Click(object sender, EventArgs e)
{
T_UsersTableAdapter adapter = new T_UsersTableAdapter();
DataSetUsers.T_UsersDataTable users = adapter.GetDataByUserName(txtUserName.Text);
if (users.Count <= 0)
{
MessageBox.Show("用户名错误");
}
else
{
DataSetUsers.T_UsersRow user=users[0];
if (user.ErrorTimes > 3)
{
MessageBox.Show("错误次数过多");
return;
}
if (user.Password == txtPassword.Text)
{
MessageBox.Show("登录成功");
//QueriesTableAdapter queryadapter = new QueriesTableAdapter();
//DataSetUsers user1 = new DataSetUsers();
//adapter.Fill(user1.T_Users);
//queryadapter.UpdateUserQuery(user.Id);
adapter.ResetErrorTimes(user.Id);
//DataSetUsers.acceptchanges();
}
else
{
adapter.IncErrorTimesById(user.Id);
//DataSetUsers.acceptchanges();
MessageBox.Show("密码错误");
}
}
}
private void button1_Click(object sender, EventArgs e)
{
T_UsersTableAdapter adapter = new T_UsersTableAdapter();
DataSetUsers.T_UsersDataTable users = adapter.GetDataByUserName(txtUserName.Text);//利用适配器根据用户名字获得相对应记录
if (users.Count <= 0)
{
MessageBox.Show("用户名错误");
}
else
{
DataSetUsers.T_UsersRow user = users[0]; //取得第1条记录
MessageBox.Show(user.ErrorTimes.ToString());//第1个MessageBox
MessageBox.Show(adapter.Connection.State.ToString());//查看连接状态
adapter.IncErrorTimesById(user.Id); //错误次数加1,直接改变数据库当中的值，DataSet中的值没有改变
MessageBox.Show(adapter.Connection.State.ToString());//查看连接状态
MessageBox.Show(user.ErrorTimes.ToString());//与第1个MessageBox打印出来的数据相同，因为DataSet中的值还没有改变
}
}
private void button2_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch(); //新建一个秒表类
sw.Start(); //启动秒表
T_UsersTableAdapter adapter = new T_UsersTableAdapter();
/*
//-----------每次执行插入都打开和关闭连接的情况-----------------
for (int i = 0; i < 3000; i++)
{
adapter.Insert(i.ToString(),i.ToString(),0);//插入数据
}
*/
//---------批量操作的情况，操作之前打一连接，操作之后再关闭连接-----------
//速度要快很多
adapter.Connection.Open(); //打开连接
for (int i = 0; i < 3000; i++)
{
adapter.Insert(i.ToString(), i.ToString(), 0);//插入数据
}
adapter.Connection.Close(); //关闭连接
sw.Stop(); //停止秒表
MessageBox.Show(sw.Elapsed.ToString()); //秒表从开始到停止的间隔时间
}
}
}
