using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// ExcuteScalar 一般和聚合函数一起使用
namespace _06ExcuteScalar
{
class Program
{
static void Main(string[] args)
{
object o; // 定义在外面,比较规范
string connStr = @"server=.;database=MySecondDB;uid=sa;pwd=520;";
string sql = "select pwd from loginPrimaryTest;";
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
o = cmd.ExecuteScalar();
}
}
}
}
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------------
// 2 ExecuteScalar的参数化使用
(1) 配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings>
<add name="sql" connectionString="server=.;database=MySecondDB;uid=sa;pwd=520;"/>
</connectionStrings>
</configuration>
//==================================
(2) Program.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace _11SQLHelper
{
class Program
{
static void Main(string[] args)
{
// 对数据库的增删改查
// 创建数据库
//string createtable = "create table Test0806(id int not null, name nvarchar(10), pwd varchar(10))";
//int res = JKLibrary.SQLHelper.ExecuteNonQuery(createtable);
//Console.WriteLine(res);
// 插入数据
#region 无参数,增加数据
//string sql = "insert into Test0806(id, name, pwd) values (1, N'赵晓虎','TigerZhao');";
//int res = JKLibrary.SQLHelper.ExecuteNonQuery(sql);
//Console.WriteLine(res);
#endregion
#region 参数化增加
//string sql = "insert into Test0806(id, name, pwd) values (@id, @name, @pwd);";
//SqlParameter[] ps = {
// new SqlParameter("@id", (object)2),
// new SqlParameter("@name", "赵剑宇"),
// new SqlParameter("@pwd", "happyZhao")
// };
//int res = JKLibrary.SQLHelper.ExecuteNonQuery(sql, ps);
//Console.WriteLine(res);
#endregion
// 修改
#region 参数化修改
//string sql = "update Test0806 set name=@name where id=@id;";
//SqlParameter[] ps = {
// new SqlParameter("@name", "赵晓"),
// new SqlParameter("@id", (object)2)
// };
//int res = JKLibrary.SQLHelper.ExecuteNonQuery(sql, ps);
#endregion
// 删除
//string sql = "delete from Test0806 where name=@name;";
//SqlParameter pName = new SqlParameter("@name", "赵晓");
//int count = JKLibrary.SQLHelper.ExecuteNonQuery(sql, pName);
//Console.WriteLine(count);
// 查询姓赵的有多少人?
#region Scalar
string sql = "select count(*) from Test0806 where name like @name;";
SqlParameter pName = new SqlParameter("@name", "赵%");
int count = (int)JKLibrary.SQLHelper.ExecuteScalar(sql, pName);
Console.WriteLine(count);
#endregion
Console.ReadKey();
}
}
}
//======================================================
(3) SQLHelper.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace JKLibrary
{
// 提供执行方法
public class SQLHelper
{
// 连接字符串
static string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(ps);
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}// ExecuteNonQuery End
// ExecuteScalar 方法
public static object ExecuteScalar(string sql, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(ps);
conn.Open();
return cmd.ExecuteScalar();
}
}
}//
}
}