Lint拼Sql 增删改查
===============Lint拼Sql=======
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using LogisticsHR.Linq;
using LogisticsHR.Model;
using System.Data.Linq.SqlClient;
using LogisticsHR.Utility;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Collections;
namespace LogisticsHR.BLL.Eval
{
public class NewsBLL
{
/// <summary>
/// Desc：添加一条新的News
/// Author：hbs
/// Date：2010-09-21
/// </summary>
/// <param name="news_Title">新闻标题</param>
/// <param name="news_Content">新闻内容</param>
/// <param name="news_Type">新闻类型</param>
/// <param name="isTop">是否置顶</param>
/// <returns>bool</returns>
public bool InsertNews(string news_Title, string news_Content, string news_Type, int isTop,string news_Link)
{
SqlConnection conn = new SqlConnection(ComDataContext.ConneStr);
try
{
string sql = @"Insert into News (
News_Title,
News_Content,
News_Type,
News_Top,
Create_User,
News_Link
) values (
@News_Title,
@News_Content,
@News_Type,
@News_Top,
@Create_User,
@News_Link)";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter p1 = new SqlParameter("News_Title", SqlDbType.VarChar, 100);
p1.Value = news_Title;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Content", SqlDbType.Text);
p1.Value = news_Content;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Type", SqlDbType.VarChar, 20);
p1.Value = news_Type;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Top", SqlDbType.SmallInt, 1);
p1.Value = isTop;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("Create_User", SqlDbType.VarChar, 20);
p1.Value = ManagementContext.Current.UserId;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Link", SqlDbType.VarChar, 20);
p1.Value = news_Link;
cmd.Parameters.Add(p1);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception ex)
{
LogHelper.ErrorFormat("NewsBLL -> InsertNews:{0}", ex.Message);
return false;
}
}
/// <summary>
/// Desc:显示Logo页面上的5条News
/// Author：hbs
/// Date：2010-09-21
/// </summary>
/// <returns>ds</returns>
public DataTable ShowTopNews()
{
ComDataContext context = new ComDataContext(ComDataContext.ConneStr);
string sql = "select Top 5 News_Title,News_Id,News_Link from News where News_type='Notice' and News_Top=1 order by Create_Date desc";
DataSet ds = new DataSet();
try
{
SqlCommand command = null;
SqlDataAdapter adapter = null;
command = new SqlCommand(sql, (SqlConnection)context.Connection);
adapter = new SqlDataAdapter(command);
adapter.Fill(ds, "dt");
}
catch (Exception ex)
{
LogHelper.ErrorFormat("NewsBLL -> ShowScoredSort:{0}", ex.Message);
return null;
}
return ds.Tables[0];
}
/// <summary>
/// Desc:根据ID获得新闻通知的详情
/// </summary>
/// Author：crj
/// Date：2010-09-21
/// <param name="newsId">新闻通知的ID</param>
/// <returns>read</returns>
public DataSet GetNewsDetailById(int newsId)
{
SqlConnection conn = new SqlConnection(ComDataContext.ConneStr);
string sql = "select News_Title,News_Content,News_Type,News_Top,News_Link from News where News_Id=@News_Id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@News_Id", newsId);
DataSet ds = new DataSet();
try
{
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
catch (Exception ex)
{
LogHelper.ErrorFormat("NewsBLL -> Search:{0}", ex.Message);
return null;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Desc:获取News
/// Author：hbs
/// Date：2010-09-21
/// </summary>
/// <returns>ds</returns>
public static DataTable Search(NewsModel model)
{
ComDataContext context = new ComDataContext(ComDataContext.ConneStr);
string sql = "select News_Title,News_Id , "+
" (select Scd_CName from SCode where Scd_Id=News.News_Type and Scd_type='NewsType') as News_Type" +
" from News ";
if (!string.IsNullOrEmpty(model.News_Title))
{
sql += "where News_Title like '" + model.News_Title.ToString() + "%'";
}
sql += " order by Create_Date desc";
DataSet ds = new DataSet();
try
{
SqlCommand command = null;
SqlDataAdapter adapter = null;
command = new SqlCommand(sql, (SqlConnection)context.Connection);
adapter = new SqlDataAdapter(command);
adapter.Fill(ds, "dt");
}
catch (Exception ex)
{
LogHelper.ErrorFormat("NewsBLL -> Search:{0}", ex.Message);
return null;
}
return ds.Tables[0];
}
/// <summary>
/// Desc：根据Id修改一条记录
/// Author：hbs
/// Date：2010-09-21
/// </summary>
/// <param name="news_Title">新闻标题</param>
/// <param name="news_Content">新闻内容</param>
/// <param name="news_Type">新闻类型</param>
/// <param name="isTop">是否置顶</param>
/// <returns>bool</returns>
public bool UpdateNewsById(string news_Title, string news_Content, string news_Type, int isTop, int news_id,string news_Link)
{
SqlConnection conn = new SqlConnection(ComDataContext.ConneStr);
try
{
string sql = @"update News set
News_Title=@News_Title,
News_Content=@News_Content,
News_Type=@News_Type,
News_Top=@News_Top,
Create_User=@Create_User,
Create_Date=@Create_Date,
News_Link=@News_Link
where News_Id=@News_Id";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter p1 = new SqlParameter("News_Title", SqlDbType.VarChar, 100);
p1.Value = news_Title;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Id", SqlDbType.Int);
p1.Value = news_id;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Content", SqlDbType.Text);
p1.Value = news_Content;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Type", SqlDbType.VarChar, 20);
p1.Value = news_Type;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Top", SqlDbType.SmallInt, 1);
p1.Value = isTop;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("Create_User", SqlDbType.VarChar, 20);
p1.Value = ManagementContext.Current.UserId;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("News_Link", SqlDbType.VarChar, 100);
p1.Value = news_Link;
cmd.Parameters.Add(p1);
p1 = new SqlParameter("Create_Date", SqlDbType.DateTime);
p1.Value = DateTime.Now;
cmd.Parameters.Add(p1);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception ex)
{
LogHelper.ErrorFormat("NewsBLL -> UpdateNewsById:{0}", ex.Message);
return false;
}
}
/// <summary>
/// Desc：根据Id删除一条记录
/// Author：hbs
/// Date：2010-09-21
/// </summary>
/// <param name="news_id"></param>
public static bool DeleteNewsById(int news_id)
{
SqlConnection conn = new SqlConnection(ComDataContext.ConneStr);
try
{
string sql = "delete from News where News_Id=@News_Id";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter p1 = new SqlParameter("news_id", SqlDbType.Int);
p1.Value = news_id;
cmd.Parameters.Add(p1);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception ex)
{
LogHelper.ErrorFormat("NewsBLL -> DeleteNewsById:{0}", ex.Message);
return false;
}
}
}
}
浙公网安备 33010602011771号