如何返回一个包含数据的对象
我知道这可能很简单,我知道我发布的代码是错误的,但我越来越接近我的想法。如何返回一个包含数据的对象
我想从文件pageDAL.cs中获取下面的函数,以便返回对象页面,并为我传入的任何页面ID返回值。如果我不需要使用DataSet或DataTable,那很好。什么是最好的方法?
public page getPage(int _pageID)
{
DataTable dt = new DataTable;
using (SqlDataAdapter da = new SqlDataAdapter("select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from Page where PageID = " + _PageID, ConnectionStrings.StaceysCakes))
{
da.Fill(dt);
dt.AsEnumerable().Select(r => page)
{
page myPageOBJ = new page();
myPageObj.PageID = r.Field<int>("PageID"),
ParentID = r.Field<int>("ParentID"),
CategoryID = r.Field<int>("CategoryID"),
Name = r.Field<string>("Name"),
PageHTMLContent = r.Field<string>("PageHTMLContent"),
NavigationText = r.Field<string>("NavigationText"),
TopMenu = r.Field<bool>("TopMenu"),
SubMenu = r.Field<bool>("SubMenu"),
DisplayOrder = r.Field<int>("DisplayOrder"),
Active = r.Field<bool>("Active"),
});
}
return page;
}
page.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace sc2.Models.page
{
public class page
{
public int PageID { get; internal set; }
public int ParentID { get; internal set; }
public int CategoryID { get; internal set; }
public string Name { get; internal set; }
public string PageHTMLContent { get; internal set; }
public string NavigationText { get; internal set; }
public bool TopMenu { get; internal set; }
public bool SubMenu { get; internal set; }
public int DisplayOrder { get; internal set; }
public bool Active { get; internal set; }
public page()
{
}
}
}
pageBLL.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
//using sc2.Models;
namespace sc2.Models.page
{
public class pageBLL
{
pageDAL myPageDAL = new pageDAL();
public pageBLL(){
}
public page getPage()
{
page PageOBJ = new page();
return PageOBJ;
}
public page getPage(int _pageID)
{
return myPageDAL.getPage(_pageID);
}
public string save(page _page)
{
return myPageDAL.save(_page);
}
public List<page> Select()
{
return (myPageDAL.Select());
}
public List<page> Select(string _OrderBy)
{
return (myPageDAL.Select(_OrderBy));
}
public DataSet Get(int _PageID)
{
return (myPageDAL.Get(_PageID));
}
public void DeletePage(int _PageID)
{
myPageDAL.DeletePage(_PageID);
}
}
}
pageDLL.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
namespace sc2.Models.page
{
public class pageDAL
{
public pageDAL()
{
}
// List Order By
public List<page> Select(string _OrderBy)
{
string sqlStatement = _OrderBy;
return All(sqlStatement);
}
// Select List of Objects
public List<page> Select()
{
string sqlStatement = "DisplayOrder";
return All(sqlStatement);
}
// Return List of Objects
public List<page> All(string _sqlStatement)
{
var sqlResults = new DataTable();
string sqlStatement = "select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from page";
if (!String.IsNullOrEmpty(_sqlStatement))
{
sqlStatement += " Order By " + _sqlStatement;
}
using (SqlConnection conn = new SqlConnection(ConnectionStrings.StaceysCakes))
{
using (SqlCommand command = new SqlCommand(sqlStatement, conn))
{
var adapter = new SqlDataAdapter(command);
adapter.Fill(sqlResults);
}
}
return sqlResults.AsEnumerable().Select(r => new page()
{
PageID = r.Field<int>("PageID"),
ParentID = r.Field<int>("ParentID"),
CategoryID = r.Field<int>("CategoryID"),
Name = r.Field<string>("Name"),
PageHTMLContent = r.Field<string>("PageHTMLContent"),
NavigationText = r.Field<string>("NavigationText"),
TopMenu = r.Field<bool>("TopMenu"),
SubMenu = r.Field<bool>("SubMenu"),
DisplayOrder = r.Field<int>("DisplayOrder"),
Active = r.Field<bool>("Active"),
}).ToList();
}
public page getPage(int _pageID)
{
DataTable dt = new DataTable;
using (SqlDataAdapter da = new SqlDataAdapter("select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from Page where PageID = " + _PageID, ConnectionStrings.StaceysCakes))
{
da.Fill(dt);
dt.AsEnumerable().Select(r => page)
{
page myPageOBJ = new page();
myPageObj.PageID = r.Field<int>("PageID"),
ParentID = r.Field<int>("ParentID"),
CategoryID = r.Field<int>("CategoryID"),
Name = r.Field<string>("Name"),
PageHTMLContent = r.Field<string>("PageHTMLContent"),
NavigationText = r.Field<string>("NavigationText"),
TopMenu = r.Field<bool>("TopMenu"),
SubMenu = r.Field<bool>("SubMenu"),
DisplayOrder = r.Field<int>("DisplayOrder"),
Active = r.Field<bool>("Active"),
});
}
return page;
}
// (DataSet) get
public DataSet Get(int _PageID)
{
using (SqlDataAdapter da = new SqlDataAdapter("select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from Page where PageID = " + _PageID, ConnectionStrings.StaceysCakes))
{
DataSet ds = new DataSet();
da.Fill(ds, "Pages");
return (ds);
}
}
// Save
public string save(page _page)
{
string errorMessage = "";
if (_page.CategoryID == 0)
{
InsertPage(_page.Name, _page.PageHTMLContent, _page.NavigationText, _page.TopMenu, _page.SubMenu, _page.DisplayOrder, _page.Active);
}
else
{
UpdatePage(_page.CategoryID, _page.Name, _page.PageHTMLContent, _page.NavigationText, _page.TopMenu, _page.SubMenu, _page.DisplayOrder, _page.Active);
}
return errorMessage;
}
// Insert Page
public string InsertPage(string _Name, string _PageHTMLContent, string _NavigationText, bool _TopMenu, bool _SubMenu, int _DisplayOrder, bool _Active)
{
SqlConnection myConnection = new SqlConnection(ConnectionStrings.StaceysCakes);
string SqlStatement = "insert into Page (Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active) values ('"
+ _Name + "','"
+ _PageHTMLContent + "','"
+ _NavigationText + "','"
+ _TopMenu + "','"
+ _SubMenu + "',"
+ _DisplayOrder + ",'"
+ _Active + "');";
string errorMessage = "";
try
{
SqlCommand myCommand = new SqlCommand(SqlStatement);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception e)
{
errorMessage = "There was an error with the database insert.";
Trace.Write("Database unavailable with Message: ", e.Message);
Trace.Write("Stack Trace: ", e.StackTrace);
// Throw the exception higer for logging and notification
throw;
}
// Clean up any loose ends.
finally
{
myConnection.Close();
}
// Return the error message if there is one.
return errorMessage;
}
// Update Page
public string UpdatePage
(int _PageID,
string _Name,
string _PageHTMLContent,
string _NavigationText,
bool _TopMenu,
bool _SubMenu,
int _DisplayOrder,
bool _Active)
{
string SqlStatement = "UPDATE Page SET Name='" + _Name + "', PageHTMLContent='" + _PageHTMLContent + "', NavigationText='" + _NavigationText + "', TopMenu='" + _TopMenu + "', SubMenu='" + _SubMenu + "', DisplayOrder=" + _DisplayOrder + ", Active='" + _Active + "' where PageID = '" + _PageID + "'";
SqlConnection myConnection = new SqlConnection(ConnectionStrings.StaceysCakes);
string errorMessage = "";
try
{
SqlCommand myCommand = new SqlCommand(SqlStatement);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception e)
{
errorMessage = "There was an error with the database update.";
Trace.Write("Database unavailable with Message: ", e.Message);
Trace.Write("Stack Trace: ", e.StackTrace);
// Throw the exception higer for logging and notification
throw;
}
// Clean up any loose ends.
finally
{
myConnection.Close();
}
// Return the error message if there is one.
return errorMessage;
}
// Delete Page
public string DeletePage(int _PageID)
{
string SqlStatement = "Delete from Page where PageID = '" + _PageID + "'";
SqlConnection myConnection = new SqlConnection(ConnectionStrings.StaceysCakes);
string errorMessage = "";
try
{
SqlCommand myCommand = new SqlCommand(SqlStatement);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception e)
{
errorMessage = "There was an error with the database delete.";
Trace.Write("Database unavailable with Message: ", e.Message);
Trace.Write("Stack Trace: ", e.StackTrace);
// Throw the exception higer for logging and notification
throw;
}
// Clean up any loose ends.
finally
{
myConnection.Close();
}
// Return the error message if there is one.
return errorMessage;
}
}
}
看看SqlDataReader类。它比SqlDataAdapter和DataTable更简单,开销更少。
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
此外,寻找here
var results = new List<page>();
string queryString = "SELECT PageID,ParentID...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
page p= new page();
p.PageID = reader["PageID"];
//...
results.Add(p);
}
reader.Close();
}
return results;
不要你只需要在你的GETPAGE()方法返回myPageOBJ,不是页面?
您还需要使用“myPageObj。”将您的分配放在myPageOBJ成员之前。您可能还想查看对象初始值设定项,以使页面对象的设置更清晰。
您应该避免使用SQL Injection,并使用NHibernate或Fluent NHibernate等ORM来查找分页结果,例如:How can you do paging with NHibernate?。
需要一点时间才能熟悉NHibernate,但一旦做到这一点,就会使事情变得更容易和简单。
我在学习LINQ,Entity和(NHibernate和/或Fluent NHibernate)。我目前正在获得报酬以学习DotNet,然后在SharePoint之后,所以我会问我一些SharePoint问题,一旦我觉得我已经学会了足够多的DotNet以在不同的工作中工作......(以防他们使用任何这些不同的风格获取工作完成。) – 2010-08-11 14:18:14
我希望我能够学习.Net,我必须免费学习:) – 2010-08-11 14:20:34
对于看起来相当孤立的问题,这是很多代码。你认为你可以制作更简洁的样本吗? (关于我的意思,请阅读:http://www.yoda.arachsys.com/csharp/complete.html) – 2010-08-11 13:17:31
此代码是否可以编译? – 2010-08-11 13:21:07