效果:
数据库端:
前端展示:
实现原理:
1.在数据段建立两个存储过程
queryUserAnsawer(id)
exportUserAnsawer(id,filepath,re)根据指定的ID调用queryUserAnsawer,根据物理路径导出csv文件,re用来判断导出是否成功
2.使用aps.net的cs文件生成filepath,然后调用exportUserAnsawer导出文件
3.使用d3.js获取导出的csv文件,然后可视化显示。
源码及分析:
1.sql存储过程的建立(导出user的总分到CSV文件.sql)
Use 你的数据库名
go
--创建存储过程,根据userID返回每一题的分值
if exists (SELECT * FROM sysobjects WHERE name='queryUserAnsawer')
drop proc exportQueryUserAnsawer
go
create proc queryUserAnsawer
@id int
as
--这里可以自定义sql语句
select 题库.题编号 题编号, (选项一分值*A+选项二分值*B+选项三分值*C+选项四分值*D) 总分 from dbo.会员答案,题库 WHERE 会员编号=@id and 会员答案.题编号=题库.题编号
go
--调用存储过程exportQueryUserAnsawer
exec 你的数据库名..queryUserAnsawer 1
-- 创建函数,根据userID返回每一题的总分,并将其保存到自定义CSV的文件中
if exists (SELECT * FROM sysobjects WHERE name='exportUserAnsawer')
drop proc exportUserAnsawer
go
create proc exportUserAnsawer
(
@id int ,@filepath varchar(8000),--输入参数
@re int output --输出参数
)
as
declare @s varchar(8000)
--csv文件的表头,你也可以自定义表头,但是为了与前端d3.js访问,直接就data1,data2了set @s='echo data1,data2>"'+@filepath+'"'
exec master..xp_cmdshell @s,no_output
--导出csv文件到temp目录
set @s='bcp "exec 数据库名..queryUserAnsawer '+cast(@id as varchar(50))+'" queryout "'+'"%temp%\temp.csv"'+'" /c /t, /U"登录名" -P"密码" /S 服务器名'
exec master..xp_cmdshell @s ,no_output
--将temp目录下的csv文件与之前的csv文件的表头合并
set @s='more %temp%\temp.csv >>"'+@filepath+'"'
exec master..xp_cmdshell @s ,no_output
--删除temp目录下的csv文件
exec master..xp_cmdshell 'del %temp%\temp.csv' ,no_output
--返回执行结果
set @re=1 --如果可以执行这一句代表之前的语句没有报错
go
--调用存储过程exportUserAnsawer
--F:\Data\data.csv
declare @w int
exec 数据库名..exportUserAnsawer 5, 'F:\Data\data.csv' ,@w output
--PRINT '执行结果:'+CONVERT(varchar(20),@w)
select @w as '返回值'
2.在网站目录下的app_code创建一个用于导出文件的类(DB.cs)
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// DB 的摘要说明
/// </summary>
public class DB
{
public SqlConnection Con = new SqlConnection();
public SqlCommand Com = new SqlCommand();
public SqlDataAdapter Da = new SqlDataAdapter();
public DataSet Ds = new DataSet();
public DB()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
//定义一个用于返回数据库连接字符串的方法
public String GetConnectionString()
{
String ConStr;
// ConStr = System.Configuration.ConfigurationManager.AppSettings.Get("con").ToString();
// ConStr = System.Configuration.ConfigurationManager.ConnectionStrings["textconnectionstring"].ToString();
ConStr = System.Configuration.ConfigurationManager.ConnectionStrings["连接名"].ToString();
return ConStr;
}
//根据userID返回每一题的总分,并将其保存到自定义CSV的文件中
//调用存储过程exportUserAnsawer
public bool exportQueryUserAnsawer(int id, String filepath) {
int re = 0;
try
{
Con.ConnectionString = GetConnectionString();
//打开连接
Con.Open();
//调用存储过程
Com = new SqlCommand("exportUserAnsawer", Con);
//设置命令的类型为存储过程
Com.CommandType = CommandType.StoredProcedure;
// 设置参数
Com.Parameters.Add("@id", SqlDbType.Int);
Com.Parameters.Add("@filepath", SqlDbType.VarChar);
// 注意输出参数要设置大小,否则size默认为0
Com.Parameters.Add("@re", SqlDbType.Int, 10);
// 设置参数的类型为输出参数,默认情况下是输入,
Com.Parameters["@re"].Direction = ParameterDirection.Output;
// 为参数赋值
Com.Parameters["@id"].Value = id;
Com.Parameters["@filepath"].Value = filepath;
// 执行
Com.ExecuteNonQuery();
// 得到输出参数的值,把赋值给re,注意,这里得到的是object类型的,要进行相应的类型转换
re = (int)Com.Parameters["@re"].Value;
if (re == 1) return true;
else return false;
}
catch (Exception e){
// Console.WriteLine(e.ToString());
return false;
}
}
}
3.在后端(测试结果.cs)文件中,创建文件名,路径,用户ID。这些变量可以从其他页面的Session中获取,我这里就写死了(方便演示)。
测试结果.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
String filepath=null;
String userID = null;
String username = null;
protected void Page_Load(object sender, EventArgs e)
{
userID = ""+5;
username = "clientuser1";
//插入数据完成后导出csv文件
filepath = getFilePath(username, userID);
DB db2 = new DB();
if (db2.exportQueryUserAnsawer(Convert.ToInt16(userID), filepath))
{
Response.Write("<script>alert('保存文件成功');</script>");
}
else
{
Response.Write("<script>alert('⚠保存文件失败');</script>");
}
//转相对路径
filepath = urlconvertor(filepath);
//以上变量可从session中获取
//try
//{
// username = Session["ClientUsername"].ToString();
// userID = Session["ClientUserID"].ToString();
// filepath = Session["FliePath"].ToString();
// filepath = urlconvertor(filepath);
// // Response.Write(filepath + "<br/>");
//}
//catch
//{
// Response.Write("<script>alert('请先登录!');;window.location.href='登录.aspx';</script>");
// return;
//}
//设置以下属性是为了,实现与js数据交互
this.name.Text = username;
this.name.ToolTip = username;
this.path.ToolTip = filepath;
}
//退出登录
protected void Button1_Click(object sender, EventArgs e)
{
Session.Clear();
Response.Redirect("登录.aspx");
}
//生成文件物理路径 如下
//F:\Demo\Data\username_5_answer_2019-05-08_18-29-48-792.csv
protected String getFilePath(String username,String userID ) {
String year = DateTime.Now.Year.ToString();
String mouth = DateTime.Now.Month.ToString();
String day = DateTime.Now.Day.ToString();
String houre = DateTime.Now.Hour.ToString();
String minute = DateTime.Now.Minute.ToString();
String second = DateTime.Now.Second.ToString();
String millsecond = DateTime.Now.Millisecond.ToString();
if (Convert.ToInt16(mouth) < 10) mouth = "0" + mouth;
if (Convert.ToInt16(day) < 10) day = "0" + day;
String date = year + "-" + mouth + "-" + day + "_" + houre + "-" + minute + "-" + second + "-" + millsecond;
//Response.Write(date);
//插入完成后导出csv文件
filepath = "\\Data\\" + username + "_" + userID + "_answer_" + date + ".csv";
filepath = urlconvertorlocal(filepath);
return filepath;
}
//转绝对路径
private string urlconvertorlocal(string url)
{
string tmpRootDir = Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString());//获取程序根目录
string reurl = tmpRootDir + url.Replace(@"/", @"/"); //转换成绝对路径
return reurl;
}
//本地路径转换成URL相对路径
private string urlconvertor(string url)
{
string tmpRootDir = Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString())+"\\";//获取程序根目录
string reurl = url.Replace(tmpRootDir, ""); //转换成相对路径
reurl = reurl.Replace("\\", "/");
return reurl;
}
}
4.前端代码
测试结果.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="测试结果.aspx.cs" Inherits="_Default" %>
<!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>
<style type="text/css">
.hidden
{
display:none
}
.choice
{
display:block
}
</style>
<script type="text/javascript" src="js/d3.min.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="name" runat="server" Text="username"></asp:Label>
<%--为了c#与js交互--%>
<asp:Label ID="path" runat="server" Text="path" class="hidden" ></asp:Label>
<asp:Button ID="Button1" runat="server" Text="退出登录" οnclick="Button1_Click" />
<%--使用页面回发响应慢,使用JS控制--%>
<div id="radio">
<asp:RadioButton ID="RadioButton1" runat="server" Text="条形图" GroupName="g1"
Checked="true"
/>
<asp:RadioButton ID="RadioButton2" runat="server" Text="面积图" GroupName="g1"
/>
<asp:RadioButton ID="RadioButton3" runat="server" Text="柱状图" GroupName="g1"
/>
<asp:RadioButton ID="RadioButton4" runat="server" Text="饼状图" GroupName="g1"
/>
</div>
<asp:Panel ID="Pane_LineChart" runat="server" class="choice">
<link href="css/linechart.css" type="text/css" media="screen" rel="Stylesheet" />
<div id="linechart_container">
<div id="linechart">
</div>
</div>
<script type="text/javascript" src="js/linechart.js"></script>
</asp:Panel>
<asp:Panel ID="Panel_AreaChart" runat="server" class="hidden">
<link href="css/areachart.css" type="text/css" media="screen" rel="Stylesheet" />
<div id="areachart_container">
<div id="areachart">
</div>
</div>
<script type="text/javascript" src="js/areachart.js"></script>
</asp:Panel>
<asp:Panel ID="Panel_BarChart" runat="server" class="hidden">
<link href="css/barchart.css" type="text/css" media="screen" rel="Stylesheet" />
<div id="barchart">
</div>
<script type="text/javascript" src="js/barchart.js"></script>
</asp:Panel>
<asp:Panel ID="Panel_PieChart" runat="server" class="hidden">
<link href="css/piechart.css" type="text/css" media="screen" rel="Stylesheet" />
<div id="piechart">
</div>
<script type="text/javascript" src="js/piechart.js"></script>
</asp:Panel>
</div>
</form>
<script type="text/javascript" src="js/radioButton.js"></script>
</body>
</html>
测试结果.aspx
我上面只写了关键代码,如果你想查看整个demo,请在这里下载
GitHub:ASP.NET+d3.js实现Sqlserver数据库的可视化展示