newsqlheleper

最新推荐文章于 2017-07-02 16:14:57 发布

goldfishsky

最新推荐文章于 2017-07-02 16:14:57 发布

阅读量453

点赞数

分类专栏： asp.net

本文链接：https://blog.csdn.net/goldfishsky/article/details/45688235

版权

asp.net 专栏收录该内容

59 篇文章 0 订阅

订阅专栏

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
onrowcommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="Title" HeaderText="标题" />
<asp:BoundField DataField="Author" HeaderText="权限" />
<asp:TemplateField HeaderText="操作">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Button ID="Button3" runat="server" CommandName="editline" CommandArgument='<%# bind("Id") %>'
Text="编辑" οnclick="Button3_Click" />
<asp:Button ID="Button4" runat="server" CommandName="deleteline" CommandArgument='<%# bind("Id") %>'
Text="删除" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="Button1" runat="server" οnclick="Button1_Click" Text="查询" />
<br />
<br />
<asp:Button ID="Button2" runat="server" οnclick="Button2_Click"
Text="tianjia" />
<br />
title<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
Author<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<br />

</div>
</form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BLL;
using Model;
using System.Data;
namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
userbll u = new userbll();
DataSet dt= u.GetList(0,"","Id");
GridView1.DataSource = dt;
GridView1.DataBind();
}

protected void Button2_Click(object sender, EventArgs e)
{
userbll u = new userbll();
Article amodel = new Article();
amodel.Title = TextBox1.Text;
amodel.Author = TextBox2.Text;
u.Add(amodel);
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "editline") {
string id = e.CommandArgument.ToString();
Response.Redirect("WebForm2.aspx?id=id" + id);
//userbll u = new userbll();
//Article model = new Article();
//u.Update(model);
}
if (e.CommandName == "deleteline")
{
int id = Convert.ToInt32(e.CommandArgument.ToString());
userbll u = new userbll();
Article model = new Article();
u.Delete(id);
}
}
protected void Button3_Click(object sender, EventArgs e)
{
}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
public class Article
{
public Article()
{ }
#region Model
public int Id { get; set; }
public string Title { get; set; }
public string Author { get; set; }

#endregion Model

}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
namespace BLL
{
/// <summary>
/// 业务逻辑类Article 的摘要说明。
/// </summary>
public class userbll
{
userdal dal = new userdal();
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Model.Article model)
{
dal.Add(model);
}

/// <summary>
/// 修改一条数据
/// </summary>
public void UpdateField(int Id, string strValue)
{
dal.UpdateField(Id, strValue);
}

/// <summary>
/// 更新一条数据
/// </summary>
public void Update(Model.Article model)
{
dal.Update(model);
}

/// <summary>
/// 删除一条数据
/// </summary>
public void Delete(int Id)
{

dal.Delete(Id);
}

/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.Article GetModel(int Id)
{

return dal.GetModel(Id);
}

/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(int Top, string strWhere, string filedOrder)
{
return dal.GetList(Top, strWhere, filedOrder);
}

}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
public class userdal
{

/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.Article model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Article(");
strSql.Append("Title,Author)");
strSql.Append(" values (");
strSql.Append("@Title,@Author)");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("Title",model.Title),
new SqlParameter("Author",model.Author)
};

return DbHelperOleDb.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
}

public void UpdateField(int Id, string strValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Article set " + strValue);
strSql.Append(" where Id=" + Id);
//DbHelperOleDb.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(Model.Article model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Article set ");
strSql.Append("Title=@Title,");
strSql.Append("Author=@Author");

strSql.Append(" where Id=@Id ");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("Title",model.Title),
new SqlParameter("Author",model.Author),
new SqlParameter("Id",model.Id)
};
return DbHelperOleDb.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
//DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
}

/// <summary>
/// 删除一条数据
/// </summary>
public int Delete(int Id)
{

StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Article ");
strSql.Append(" where Id=@Id ");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("Id",Id)
};
return DbHelperOleDb.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);

}

/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.Article GetModel(int Id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 Id,Title,Author from Article ");
strSql.Append(" where Id=@Id ");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("Id",Id)
};

Model.Article model = new Model.Article();
DataSet ds = DbHelperOleDb.SelectIntoTable(strSql.ToString(),CommandType.Text, parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
{
model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
}
model.Title = ds.Tables[0].Rows[0]["Title"].ToString();
model.Author = ds.Tables[0].Rows[0]["Author"].ToString();

return model;
}
else
{
return null;
}
}

/// <summary>
/// 获得前几行数据
/// </summary>
public DataSet GetList(int Top, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top.ToString());
}
strSql.Append(" Id,Title,Author");
strSql.Append(" FROM Article ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder);
return DbHelperOleDb.Query(strSql.ToString());
}

}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DAL
{
public class DbHelperOleDb
{
private static string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

public static string ConnString
{
get { return connString; }
}

public static int ExecuteNonQuery(string text, CommandType type, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connString))
{
SqlCommand cmd = PrepareCommand(con, null, text, type, parameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}

public static int ExecuteScalar(string text, CommandType type, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connString))
{
SqlCommand cmd = PrepareCommand(con, null, text, type, parameters);
int val = (int)cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}

public static int ExecuteNonQuery(SqlTransaction trans, string text, CommandType type, params SqlParameter[] parameters)
{
SqlCommand cmd = PrepareCommand(trans.Connection, trans, text, type, parameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

public static SqlDataReader ExecuteReader(string text, CommandType type, params SqlParameter[] parameters)
{
SqlConnection con = new SqlConnection(connString);
try
{
SqlCommand cmd = PrepareCommand(con, null, text, type, parameters);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch
{
con.Close();
throw;
}

}

private static SqlCommand PrepareCommand(SqlConnection con, SqlTransaction trans, string text, CommandType type, SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = type;
cmd.CommandText = text;

if (con.State != ConnectionState.Open)
{
con.Open();
}

if (null != trans)
{
cmd.Transaction = trans;
}
if (null != parameters)
{
foreach (SqlParameter parm in parameters)
{
cmd.Parameters.Add(parm);
}
}
return cmd;
}
public static DataSet SelectIntoTable(string text, CommandType type, params SqlParameter[] parameters)
{
SqlConnection con = new SqlConnection(connString);
SqlCommand cmd = PrepareCommand(con, null, text, type, parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
adapter.Fill(dt);
con.Close();
return dt;

//return dt;
}

/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}

}
}