DBHeper:
创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于成都网站制作、网站建设、外贸网站建设、华容网络推广、成都微信小程序、华容网络营销、华容企业策划、华容品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联公司为所有大学生创业者提供华容建站搭建服务，24小时服务热线：18982081108，官方网址：www.cdcxhl.com
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace NewsDAL
{
public static class DBHeper
{
private static SqlConnection connection;
///
/// 连接数据库
///
public static SqlConnection Connection
{
get {
string connectionstring=ConfigurationManager.ConnectionStrings["conn"].ConnectionString.ToString();
if(connection==null)
{
connection = new SqlConnection(connectionstring);
connection.Open();
}
else if (connection.State== System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if(connection.State==System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return DBHeper.connection;
}
}
//关闭数据库连接的方法
public static void CloesConnection()
{
try {
if(connection.State!=ConnectionState.Closed)
{
connection.Close();
}
}
catch(Exception e){
}
}
///
/// 根据 SQL语句 查询所影响的行数
///
///
///
public static int ExecutCommand(string sql) {
try
{
SqlCommand cmd = new SqlCommand(sql,Connection);
int result = cmd.ExecuteNonQuery();
return result;
}catch(Exception e){
return 0;
}
}
///
/// 根据 SQL语句、预编译数组 查询所影响的行数
///
/// 参数 SQL 语句
/// 参数 预编译数组
///
public static int ExecutCommand(string sql,params SqlParameter[] values) {
try
{
SqlCommand cmd = new SqlCommand(sql,Connection);
cmd.Parameters.Add(values);
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception e)
{
return 0;
}
}
///
/// 根据 SQL 语句查询得到的条数,执行查询，返回第一行第一列的值
///
/// 参数 SQL 语句
///
///
public static int GetScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql,Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
///
/// 根据 SQL语句、预编译数组 查询得到的条数,执行查询，返回第一行第一列的值
///
/// 参数 SQL 语句
/// 参数 预编译数组
///
public static int GetScalar(string sql,params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql,Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());//cmd.ExecuteScalar()返回的是一个Ojbect类型的
return result;
}
///
/// 根据 SQL语句 查询数据
///
/// 参数 接受一个 SQL语句
///
///
public static DataTable GetDataSet(string sql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql,Connection);
SqlDataAdapter sda =new SqlDataAdapter(cmd);//sqlDataAdapter用于填充DataSet
sda.Fill(ds);//向DataTable中添加数据
return ds.Tables[0];//获得表的集合
}
///
/// 根据 SQL语句、预编译数组 查询数据
///
/// 参数 接受一个 SQL语句
/// 参数 接受一个 预编译数组
///
///
public static DataTable GetDataSet(string sql,params SqlParameter[] values) {
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql,Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
return ds.Tables[0];
}
}
}
Service:
using System;
using System.Collections.Generic;
using System.Text;
using Newsentity;
using System.Data.SqlClient;
using System.Data;
using NewsDAL;
namespace NewsDAL
{
public class newsService
{
#region
///
/// 得到所有信息
///
///
///
#endregion
public static List
{
string sql = "SELECT * FROM NEWS";
DataTable tables = DBHeper.GetDataSet(sql);
List
if (tables.Rows.Count > 0)
{
for (int i = 0; i < tables.Rows.Count; i++)
{
news n = new news();
n.Nid = Convert.ToInt32(tables.Rows[i]["nid"]);
n.Sname = Convert.ToString(tables.Rows[i]["sname"]);
n.Spass =Convert.ToString(tables.Rows[i]["spass"]);
n.Stype=Convert.ToString(tables.Rows[i]["stype"]);
n.Ncontent = Convert.ToString(tables.Rows[i]["ncontent"]);
list.Add(n);
}
return list;
}
else
{
return null;
}
}
//根据类型查询新闻
public static List
{
string sql=string.Format("SELECT * FROM NEWS WHERE STYPE='{0}'",type);
DataTable tables = DBHeper.GetDataSet(sql);
List
if (tables.Rows.Count > 0)
{
for (int i = 0; i < tables.Rows.Count; i++)
{
news n = new news();
n.Sname = Convert.ToString(tables.Rows[i]["sname"]);
n.Spass = Convert.ToString(tables.Rows[i]["spass"]);
n.Stype = Convert.ToString(tables.Rows[i]["stype"]);
n.Ncontent = Convert.ToString(tables.Rows[i]["ncontent"]);
list.Add(n);
}
return list;
}
else
{
return null;
}
}
//验证用户登陆
public static news GetNewstLogin(string name, string pass)
{
string sql = string.Format("SELECT * FROM NEWS WHERE SNAME='{0}' and SPASS='{1}'", name, pass);
DataTable tables = DBHeper.GetDataSet(sql);
if (tables.Rows.Count != 0)
{
news n = new news();
n.Sname = Convert.ToString(tables.Rows[0]["sname"]);
n.Spass = Convert.ToString(tables.Rows[0]["spass"]);
n.Stype = Convert.ToString(tables.Rows[0]["stype"]);
n.Ncontent = Convert.ToString(tables.Rows[0]["ncontent"]);
return n;
}
else
{
return null;
}
}
//删除记录
public static int DelectNews(int id)
{
string sql = string.Format("DELETE FROM NEWS WHERE NID in ('{0}')", id);
int result = DBHeper.ExecutCommand(sql);
return result;
}
//删除记录
public static int DelectNews(string id)
{
string sql = string.Format("DELETE FROM NEWS WHERE NID in ({0})", id);
int result = DBHeper.ExecutCommand(sql);
return result;
}
//根据id查询详细信息
public static List
{
string sql = string.Format("SELECT * FROM NEWS WHERE NID={0}", id);
DataTable tables = DBHeper.GetDataSet(sql);
List
if (tables.Rows.Count != 0)
{
news n = new news();
n.Sname = Convert.ToString(tables.Rows[0]["sname"]);
n.Spass = Convert.ToString(tables.Rows[0]["spass"]);
n.Stype = Convert.ToString(tables.Rows[0]["stype"]);
n.Ncontent = Convert.ToString(tables.Rows[0]["ncontent"]);
list.Add(n);
return list;
}
else
{
return null;
}
}
//添加信息
public static int AddNews(news n)
{
string sql = string.Format("insert into news(ncontent,spass,sname,stype) values('{0}','{1}','{2}','{3}')",n.Ncontent,n.Spass,n.Sname,n.Stype);
int result = DBHeper.ExecutCommand(sql);
if (result > 0)
{
return result;
}
else
{
return 0;
}
}
//修改信息
public static int UpdateNews(news n)
{
string sql = string.Format("UPDATE NEWS SET NCONTENT='{0}',SNAME='{1}',SPASS='{2}',STYPE='{3}' WHERE NID='{4}'",n.Ncontent,n.Sname,n.Spass,n.Stype,n.Nid);
int result = DBHeper.ExecutCommand(sql);
if (result > 0)
{
return result;
}
else
{
return 0;
}
}
}
}
配置文件