110,566
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MyBookShopDAL
{
public abstract class DBHelper
{
private static readonly string connString =
ConfigurationManager.ConnectionStrings["S2_MyBookShopConnectionString"].ConnectionString;
public static DataSet ExecuteDataSet(string sql)
{
SqlDataAdapter sda = new SqlDataAdapter(sql,connString);
DataSet ds = new DataSet();
sda.Fill(ds,"MySchoolShop");
return ds;
}
public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(sql,connection);
command.CommandType = type;
if (ps != null)
command.Parameters.AddRange(ps);
connection.Open();
int i = command.ExecuteNonQuery();
connection.Close();
return i;
}
public static SqlDataReader ExecuteDataReader(string sql, CommandType type,params SqlParameter[] ps)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = type;
if (ps != null)
command.Parameters.AddRange(ps);
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
public static object ExecuteSalcue(string sql, CommandType type, params SqlParameter[] ps)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = type;
if (ps != null)
command.Parameters.AddRange(ps);
connection.Open();
object obj = command.ExecuteScalar();
connection.Close();
return obj;
}
}
}
SqlConnection con = new SqlConnection("连接字符串");
// 打开数据库连接
con.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM 表名 WHERE ID=@ID", con);
// 添加参数
cmd.Parameters.Add(new SqlParameter("@ID", "值"));
// 如果是insert语句,执行ExecuteNonQuery()方法,此方法返回受影响的行数
cmd.ExecuteNonQuery();
// 如果是执行查询返回单个值,执行ExecuteScalar()方法,此方法返回第一行第一列值,返回的是object
cmd.ExecuteScalar();
// 如果执行查询返回多个值
SqlDataReader dr = cmd.ExecuteReader();
// 两种方法读取值
// 1.
DataTable dt = new DataTable();
// 将值读取到DataTable中
dt.Load(dr);
// 2.
while (dr.Read())
{
object a = dr[0];
}
// 关闭sqlDataReader
dr.Close();
// 关闭连接
con.Close();
using System.Data;
using System.Data.SqlClient;
public DataTable RetrieveRowsWithDataTable()
{
using ( SqlConnection conn = new SqlConnection(connectionString) )
{
conn.Open();
SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter( cmd );
DataTable dataTable = new DataTable("Products");
adapter .Fill(dataTable);
return dataTable;
}
}