|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
/// <summary>
///SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
static string connStr = ConfigurationManager.ConnectionStrings["JSDB"].ConnectionString;
public SqlHelper()
{
}
public static int ExecuteNonquery(string cmdText,params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(ps);
return cmd.ExecuteNonQuery();
}
}
}
public static object ExecuteScalar(string cmdText, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(ps);
return cmd.ExecuteScalar();
}
}
}
public static DataSet GetDsByAdapter(string cmdText, params SqlParameter[] ps)
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(ps);
using (SqlDataAdapter apt = new SqlDataAdapter(cmd))
{
apt.Fill(ds);
}
}
}
return ds;
}
public static SqlDataReader GetReader(string cmdText, params SqlParameter[] ps)
{
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// using (SqlCommand cmd = new SqlCommand())
// { conn.Open();
// cmd.Connection = conn;
// cmd.CommandText = cmdText;
// cmd.Parameters.AddRange(ps);
// return cmd.ExecuteReader(CommandBehavior.CloseConnection);
// }
//}
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand();
conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.Parameters.AddRange(ps);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
==============调用 /*
string sql = "insert into mytest (id,name) values(1,'abc')";
SqlHelper.ExecuteNonquery(sql);
*/
/*
string sql = "select * from mytest";
object obj = SqlHelper.ExecuteScalar(sql);
*/
/*
string sql = "select * from mytest";
DataSet obj = SqlHelper.GetDsByAdapter(sql);
*/
string sql = "select * from mytest";
SqlDataReader reader = SqlHelper.GetReader(sql);
Dictionary<string, string> dic = new Dictionary<string, string>();
while (reader.Read())
{
dic.Add(reader["Id"].ToString(),reader["Name"].ToString());
} |
|
ExecuteNonquery 用于 非查询操作，用于查询的操作，没有结果；如果sql中有查询的语句有非查询的语句，此操作返回所有insert update delete这些操作的影响行数；
ExecuteScalar 返回首行手列内容；不是说就不能执行 insert等操作，没问题，但是得不到反应的结果，数据能插入数据库没问题；多个操作，返回第一个有结果集操作的首行收列（比如 insert ...output或者select等） Adapter和reader返回 查询的结果，不是说不能执行 非查询操作，没问题数据照样执行，但是得不到任何反应； public class Comunicate
{
static string connStr = ConfigurationManager.AppSettings["connStr"].ToString();
public static void ExecuteNonquery()
{
//execurenonquery update delete insert 返回受这3中操作的影响的行数，【通过输出参数output可以输出一些内容】
// string sql = "insert into person (name ,age) values ('张三',25)";//插入一条数据，返回受影响行数1
// string sql = "select * from person";//-1
// string sql = "select * from person where name = 'shit'";//-1
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
// string sql = "InsertOutAge";//得到的还是受影响的行数1，没有得到age
//string sql = "InsertOutAgeTwo";
//string sql = "InserTsELECT";
string sql = "InserTsELECT3";//添加1行删除1行 ，影响结果是2
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql;
//存储过程的
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@name","李四");
//SqlParameter p = new SqlParameter();
//p.Direction = ParameterDirection.Output;
//p.ParameterName = "@age";
//p.SqlDbType = SqlDbType.Int;
//p.Size = 4;
//cmd.Parameters.Add(p);
int result = cmd.ExecuteNonQuery();//影响行数1
// object re = p.Value;//输出参数 92
}
}
}
public static void ExeceteScalar()
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
//string sql = "select * from person";//返回首航受列内容
string sql = "insert into person(age) values(30);select * from person;select * from person where id>6";//多个操作返回多张表时，一第一个为准，返回首行首列内容
cmd.Connection = conn;
conn.Open();
cmd.CommandText = sql;
object resltu = cmd.ExecuteScalar();
}
}
}
public static void GetDataTable()
{
//可以执行insert操作，但是此方法只是用来得到 select的内容 // string sql = "insert into person (name,age) values ('阿杜',25);select * from person";//执行了insert 把select的fill到datatable
string sql = "insert into person (name,age) values ('阿杜',26)";//能狗插入数据成功
using (SqlDataAdapter apt = new SqlDataAdapter(sql,connStr))
{
DataTable dt = new DataTable();
apt.Fill(dt);
}
}
} |