DevExpress实现增删查改(Oracle)
DBHelper
public class DBHelper
{
private static string connString = WebConfigurationManager.ConnectionStrings["oraclString"].ToString();
public DataTable GetDataTable(string sql)
{
using(OracleConnection conn = new OracleConnection(connString))
{
try
{
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception e)
{
return null;
}
finally
{
conn.Close();
}
}
}
public bool ExecuteSql(string sql)
{
using (OracleConnection conn = new OracleConnection(connString))
{
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
try
{
conn.Open();
int rows = cmd.ExecuteNonQuery();
return rows>0;
}
catch (System.Data.OracleClient.OracleException e)
{
conn.Close();
return false;
}
}
}
}
}
前台页面
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ProductType.aspx.cs" Inherits="TestDemo.ProductType" %>
<%@ Register Assembly="DevExpress.Web.ASPxGridView.v12.1, Version=12.1.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
Namespace="DevExpress.Web.ASPxGridView" TagPrefix="dx" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v12.1, Version=12.1.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dx" %>
<!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 style="margin: 50px;">
<dx:ASPxGridView ID="ASPxGridView1" runat="server" KeyFieldName="ID"
onrowdeleting="ASPxGridView1_RowDeleting"
onrowinserting="ASPxGridView1_RowInserting"
onrowupdating="ASPxGridView1_RowUpdating"
onrowvalidating="ASPxGridView1_RowValidating">
<Columns>
<dx:GridViewCommandColumn >
<EditButton Visible="true" />
<DeleteButton Visible="true" />
<HeaderTemplate>
<dx:ASPxButton ID="AddProductType" runat="server" Text="New"
onclick="AddProductType_Click">
</dx:ASPxButton>
</HeaderTemplate>
</dx:GridViewCommandColumn>
<dx:GridViewDataColumn FieldName="ID" Caption="Id" />
<dx:GridViewDataColumn FieldName="DESCRIPTION" Caption="Description" />
</Columns>
<SettingsBehavior AllowFocusedRow="True" ConfirmDelete="True" />
</dx:ASPxGridView>
<asp:Button ID="Button1" runat="server" Text="Test" onclick="Button1_Click" />
</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 DevExpress.Web.ASPxGridView;
namespace TestDemo
{
public partial class ProductType : System.Web.UI.Page
{
private DBHelper db = new DBHelper();
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
DataBind();
}
}
protected void AddProductType_Click(object sender, EventArgs e)
{
ASPxGridView1.AddNewRow();
}
protected void ASPxGridView1_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
{
int selectID = Convert.ToInt32(e.Keys[0].ToString());
string description = e.NewValues[1].ToString();
string sqlUpdate = string.Format("update web_producttypes set description='{0}' where id={1}",description,selectID);
if (db.ExecuteSql(sqlUpdate))
{
this.ASPxGridView1.CancelEdit();
e.Cancel = true;
DataBind();
}
}
protected void ASPxGridView1_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e)
{
int selectID = Convert.ToInt32(e.Keys[0].ToString());
string sqlDelete = string.Format("delete web_producttypes where id={0}", selectID);
if (db.ExecuteSql(sqlDelete))
{
e.Cancel = true;
DataBind();
}
}
public void DataBind()
{
string sqlQuery = "select id, description from web_producttypes";
this.ASPxGridView1.DataSource = db.GetDataTable(sqlQuery);
this.ASPxGridView1.DataBind();
}
protected void ASPxGridView1_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
{
int id =Convert.ToInt32(e.NewValues[0]);
string description = e.NewValues[1].ToString();
string sqlInsert=string.Format("insert into web_producttypes (id, description) values ({0},'{1}')",id,description);
if (db.ExecuteSql(sqlInsert))
{
this.ASPxGridView1.CancelEdit();
e.Cancel = true;
DataBind();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//string sqlDelete = "delete from web_producttypes where ID=5";
//db.ExecuteSql(sqlDelete);
}
protected void ASPxGridView1_RowValidating(object sender, DevExpress.Web.Data.ASPxDataValidationEventArgs e)
{
foreach(GridViewColumn column in this.ASPxGridView1.Columns)
{
GridViewDataColumn dataColumn = column as GridViewDataColumn;
if (dataColumn == null)
continue;
if(e.NewValues[dataColumn.FieldName]==null)
{
e.Errors[dataColumn] = "Value cant not be null";
}
if(e.Errors.Count>0)
{
e.RowError = "Please fill all fields";
}
if(e.NewValues["DESCRIPTION"]!=null&&e.NewValues["DESCRIPTION"].ToString().Length<2)
{
AddError(e.Errors, this.ASPxGridView1.Columns["DESCRIPTION"],"Description must be at least two characters long");
}
}
}
public void AddError(Dictionary<GridViewColumn,string> errors,GridViewColumn column,string errorText)
{
if (errors.ContainsKey(column))
return;
errors[column] = errorText;
}
}
}
Web.Config
<add name="oraclString" connectionString=" Data Source=IP/**;Persist Security Info=False;User ID=**;Password=******;" />
Oracle中字段名要大写，切记！
浙公网安备 33010602011771号