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 '返回值'

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;
        }

    }

}

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;
    }
    
}