111,097
社区成员




sql = "select * from v_" + from.Name.Substring(1, from.Name.Length - 1) + " order by " + from.Name.Substring(1, from.Name.Length - 1) + "ID";
sqlItem= "select * from v_" + from.Name.Substring(1, from.Name.Length - 1) + "Item" + " order by " + from.Name.Substring(1, from.Name.Length - 1) + "ItemID";
DataGridView dv = from.Controls.Find("dataGridView", true)[0] as DataGridView;
DataGridView dvi = from.Controls.Find("dataGridViewItem", true)[0] as DataGridView;
dv.AutoGenerateColumns = false;
dvi.AutoGenerateColumns = false;
DataSet ds = new DataSet();
SqlDataAdapter masterda = new SqlDataAdapter(sql, mydate.Conn);
masterda.Fill(ds, "v_" + from.Name.Substring(1, from.Name.Length - 1));
SqlDataAdapter detailsda = new SqlDataAdapter(sqlItem, mydate.Conn);
detailsda.Fill(ds, "v_" + from.Name.Substring(1, from.Name.Length - 1)+"Item");
DataRelation relation = new DataRelation("ZC",
ds.Tables["v_" + from.Name.Substring(1, from.Name.Length - 1)].Columns[from.Name.Substring(1, from.Name.Length - 1)+"ID"],
ds.Tables["v_" + from.Name.Substring(1, from.Name.Length - 1) + "Item"].Columns[from.Name.Substring(1, from.Name.Length - 1)+"ID"]);
ds.Relations.Add(relation);
dv.DataSource = ds;
dv.DataMember = "v_" + from.Name.Substring(1, from.Name.Length - 1);//设置绑定的主表名称
dvi.DataSource = dv;//设置从表数据源
dvi.DataMember = "ZC";//设置绑定的主从表关系名称
/// <summary>
/// BLL业务类
/// </summary>
public class ContextHelper
{
public static DataTable GetOrderInfo(string orderid = null)
{
//定义参数组
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@OrderID", SqlDbType.NVarChar, 30),
}
paras[0].Value = orderid;
return DBHelper.GetDataTable("GetOrderInfo", CommandType.StoredProcedure, paragroup);
}
}
用法:该方法采用了一个可选参数,也就是说,调用时不指定orderid变量,它为null,查询结果为所有。指定时,只为当前订单号该单张单据信息。
6、实际UI窗体调用,假设为MainForm:
public partial class MainForm : Form
{
private DataTable dtOrderInfo {get;set;}
//构造函数
public MainForm()
{
InitializeComponent();
}
public MainForm_Load()
{
//初始化Dgv数据
InitsData();
}
private void InitsData()
{
dtOrderInfo = ContextHelper.GetOrderInfo(); //缺省方法参数
//清空
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
//数据源加载
dataGridView1.DataSource = dtOrderInfo;
}
}
这是一个可扩展性强的架构性思路,同时,要对dataGridView或TreeList数据呈现的结果,只需要去调整维护BLL或 Proc存储过程实现方法上的逻辑即可。
也就是说,它的可读性强,易维护性也灵活高效。
不要把复杂的业务层逻辑,写在UI层上。当回头检视代码时,会非常吃力,麻烦。
/// <summary>
/// 数据访问类
/// </summary>
public class DBHelper
{
//访问数据库连接
public static string ConnContext { get; set; } = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ToString();
//定义连接对象
private static SqlConnection _conn = null;
//初始化数据库连接
private static void InitConnection()
{
if (_conn == null)
_conn = new SqlConnection(ConnContext);
if (_conn.State == ConnectionState.Closed)
_conn.Open();
if (_conn.State == ConnectionState.Broken)
{
_conn.Close();
_conn.Open();
}
}
/// <summary>
/// SqlDataAdapter执行查询方法,支持存储过程
/// </summary>
/// <param name="cmdText">StoredProcedure,512/TableDirect,4/Text,1(默认)</param>
/// <param name="cmdType">sql对象</param>
/// <param name="para">参数组</param>
/// <returns>DataTable数据集</returns>
public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] para)
{
_conn = new SqlConnection(ConnContext);
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmdText, _conn);
if (para != null)
sda.SelectCommand.Parameters.AddRange(para);
sda.SelectCommand.CommandType = cmdType;
sda.Fill(dt);
sda.SelectCommand.Parameters.Clear();
return dt;
}
}
4、在SqlServer DB数据库中编写一个存储过程:
/*
获取订单详情信息
*/
if exists(select 1 from sysobjects where name='GetOrderInfo')
Drop Proc GetOrderInfo
Go
Create Proc GetOrderInfo @OrderID nvarchar(50)
as
begin
select a.OrderID
,a.OrderDate
,a.CustID
,b.OrderItem
,b.ProductID
,b.Qty
,b.Price
,b.Remark
from t_Order a
inner join t_OrderItem b on a.OrderID = b. OrderID
where (0 =(case when @OrderID !='' then 1 else 0 end) or @OrderID = a.CusID)
end
[接下贴]
<!--配置数据库连接字符串-->
<connectionStrings>
<add name="ConnString"
connectionString="Data source=['计算机名'或IP地址];initial catalog=[数据库名];user id=sa;password=['密码'];MultipleActiveResultSets=True;App=EntityFramework"
providerName="System.Data.SqlClient"/>
</connectionStrings>
注意:connectionString 为DAL访问类的关键字名,其它中括号都按改自己实际的参数即可。
比如我的:<add name="ConnString"
connectionString="Data Source=morliz-xx;Initial Catalog=CusProReport;User ID=sa;Password=xxxxxx;connect Timeout=15;Pooling=true;max pool size=300"
providerName="System.Data.SqlClient"/>
2、添加EntityFramework包,建议用v6.4.4(6.0.0也可以),并在项目中引用程序集:System.Configuration
[接下贴]create table t_Order
(
OrderID varchar(50) primary key,
OrderDate datetime,
CustID varchar(50),--客户信息ID
Remark nvarchar(150)
)
GO
create table t_OrderItem
(
OrderItem varchar(50) primary key,
OrderID varchar(50),
ProductID varchar(50),--产品信息ID
Qty float,
Price float,
Remark nvarchar(150)
)
这是两个表的数据结构,没什么特别要求,只要实现数据主从表数据联动就可以了sql = "select * from v_Order order by OrderID";
sqlItem= "select * from v_OrderItem order by OrderItemID";
DataGridView dv = from.Controls.Find("dataGridView", true)[0] as DataGridView;
DataGridView dvi = from.Controls.Find("dataGridViewItem", true)[0] as DataGridView;
dv.AutoGenerateColumns = false;
dvi.AutoGenerateColumns = false;
DataSet ds = new DataSet();
SqlDataAdapter masterda = new SqlDataAdapter(sql, mydate.Conn);
masterda.Fill(ds, "v_Order");
SqlDataAdapter detailsda = new SqlDataAdapter(sqlItem, mydate.Conn);
detailsda.Fill(ds, "v_OrderItem");
DataRelation relation = new DataRelation("ZC",
ds.Tables["v_Order" ].Columns["OrderID"],
ds.Tables["v_OrderItem"].Columns["OrderID"]);
ds.Relations.Add(relation);
dv.DataSource = ds;
dv.DataMember = "v_Order";//设置绑定的主表名称
dvi.DataSource = dv;//设置从表数据源
dvi.DataMember = "ZC";//设置绑定的主从表关系名称