我自横刀向天笑,去留肝胆两昆仑。这篇文章主要讲述ADO.NET 数据连接查询相关的知识,希望能为你提供帮助。
static stringsqlcon = "server=.;
database=;
Integrated Security=true;
";
/// <
summary>
/// 新增数据
/// <
/summary>
public static void Add()
【ADO.NET 数据连接查询】//string sqlcon = "server=.;
database=;
Integrated Security=true;
";
SqlConnection conn=new SqlConnection(sqlcon);
string sqlStr="insert into Boook(b_id,b_title,b_author,b_money)values(1,ASP.NET,莫言,100)";
SqlCommand cmd=new SqlCommand(sqlStr,conn);
conn.Open();
int i = cmd.ExecuteNonQuery();
conn.Close();
if (i>
0)
Console.Write("新增成功!");
else
Console.Write("新增失败!");
/// <
summary>
/// 删除数据
/// <
/summary>
public static void Delete()
//string sqlcon = "server=.;
database=;
Integrated Security=true;
";
SqlConnection conn = new SqlConnection(sqlcon);
string sqlStr = "delete from book where b_id=1";
SqlCommand cmd = new SqlCommand(sqlStr,conn);
conn.Open();
int i = cmd.ExecuteNonQuery();
conn.Close();
if (i >
0)
Console.Write("删除成功!");
else
Console.Write("删除失败!");
/// <
summary>
/// 数据更新,软删除
/// <
/summary>
private static void Update()
//string sqlcon = "server=.;
database=;
Integrated Security=true;
";
SqlConnection conn = new SqlConnection(sqlcon);
string sqlStr = "update Boook set b_id =2 where b_id=1";
SqlCommand cmd = new SqlCommand(sqlStr, conn);
conn.Open();
int i = cmd.ExecuteNonQuery();
conn.Close();
if (i >
0)
Console.Write("更新成功!");
else
Console.Write("更新失败!");
/// <
summary>
/// 读取单个值
/// <
/summary>
private static void selectSingle()
SqlConnection conn = new SqlConnection(sqlcon);
string sqlStr = "select * from Book";
SqlCommand cmd = new SqlCommand(sqlStr, conn);
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
Console.Write(obj.ToString());
/// <
summary>
/// dateReader读取数据,逐行读取,通过下表访问列
/// <
/summary>
private static void dateReader()
SqlConnection conn = new SqlConnection(sqlcon);
string sqlStr = "select * from Book";
SqlCommand cmd = new SqlCommand(sqlStr, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
while (dr.Read())//如果读到下一行数据就返回True,且本身就属于那一行数据
Console.Write(dr[0].ToString() + _ + dr[1].ToString()+_+dr["ID"].ToString());
else
Console.Write("无数据");
dr.Close();
conn.Close();
//使用适配器填充数据集SqlDataAdapter不需要手动开关,它能够自己开关
public static void QueryListAdapter()
SqlConnection con = new SqlConnection(sqlcon);
string sqlStr = "select*from book";
SqlDataAdapter da = new SqlDataAdapter(sqlStr,con);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
//循环数据表中的每一行
for (int i = 0;
i <
dt.Rows.Count;
i++)
DataRow dr = dt.Rows[i];
//将表中的一行拿出来给行对象
Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString());
//使用适配器填充数据集SqlDataAdapter不需要手动开关,它能够自己开关
public static void QueryListAdapter2()
SqlConnection con = new SqlConnection(sqlcon);
string sqlStr = "select*from book";
SqlDataAdapter da = new SqlDataAdapter(sqlStr, con);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
//循环数据表中的每一行
for (int i = 0;
i <
dt.Rows.Count;
i++)
DataRow dr = dt.Rows[i];
//将表中的一行拿出来给行对象
Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString());
//调用存储过程查询数据
public static void QuerListByProc()
SqlConnection conn = new SqlConnection(sqlcon);
SqlCommand cmd=new SqlCommand("usp_GetBookMyCateId",conn);
//无参数的存储过程
SqlParameter sp2 = new SqlParameter();
sp2.ParameterName = "@cateId";
sp2.SqlDbType = SqlDbType.Int;
sp2.Value = https://www.songbingjia.com/android/2;
cmd.Parameters.Add(sp2);
//有两个参数的存储过程
SqlParameter sp = new SqlParameter("@cateId", 2);
cmd.Parameters.Add(sp);
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt);
foreach(DataRow dr in dt.Rows)
Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString());
//调用多个参数的存储过程查询
private static void QueryListByProc2()
SqlConnection conn = new SqlConnection(sqlcon);
SqlCommand cmd = new SqlCommand("proGetPageData", conn);
cmd.CommandType = CommandType.StoredProcedure;
//尽量不要使用两个参数的存储过程,类型是枚举类型,另外一个两个参数的函数值混淆
//SqlParameter par = new SqlParameter("@Id", DbType.Int32);
//SqlParameter par = new SqlParameter("@id", 11);
//赋值多个参数
SqlParameter[] paras =
new SqlParameter("@pageIndex",SqlDbType.Int,4),//这里的4是代表整型的长度
new SqlParameter("@pageSize",SqlDbType.Int,4)
;
//cmd.Parameters.AddRange(paras);
paras[0].Value = https://www.songbingjia.com/android/1;
//搜索第一页
paras[1].Value = https://www.songbingjia.com/android/2;
//赋值的
cmd.Parameters.AddRange(paras);
//为command对象添加pameters数组
conn.Open();
//SqlDataReader dr = cmd.ExecuteReader();
//while (dr.Read())
//
//Console.Write("id=" + dr[0].ToString());
//
//dr.Close();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString());
conn.Close();
//调用带输出参数的存数过程
private static void QuerListProc3()
SqlConnection conn = new SqlConnection(sqlcon);
SqlCommand cmd = new SqlCommand("proGetData2", conn);
SqlParameter[] paras =
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@rowCount",SqlDbType.Int)
;
paras[0].Value = https://www.songbingjia.com/android/1;
paras[1].Value = https://www.songbingjia.com/android/2;
paras[2].Direction = ParameterDirection.Output;
paras[3].Direction = ParameterDirection.Output;
//设置参数的输出方向
cmd.Parameters.AddRange(paras);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString());
int pageCount=Convert.ToInt32(cmd.Parameters[2].Value);
int rowCount=Convert.ToInt32(cmd.Parameters[3].Value);
//获取输出参数
Console.WriteLine("pageCount=" + pageCount + ",rowCount=" + rowCount);
推荐阅读
- shell 编程之循环语句echo用法
- 移动的彩虹
- #yyds干货盘点#Hystrix
- PS讲义
- #yyds干货盘点# JavaSE系列Java类与对象,万物皆对象
- 弹出消息对话框类
- 自定义验证控件CustomValidator的使用
- wordpress-如何在主页上显示一个类别的帖子(仅一个类别新闻)而不是全部
- WordPress如何在视图中显示skill1_logo