摘要:本文主要向大家介绍了SQLServer数据库之Silverlight将Excel导入到SQLserver数据库,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了SQLServer数据库之Silverlight将Excel导入到SQLserver数据库,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
最近纠结于读取Excel模板数据,将数据导入SQLServer的Silverlight实现,本文将实现代码贴出,作为一个简单的例子,方便各位:
1.先设计前台界面新建Silverlight5.0应用程序,出现MainPage.xaml,代码如下所示:
<UserControl x:Class="Excel导入SQLServer数据库.MainPage"
xmlns="//schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="//schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="//schemas.microsoft.com/expression/blend/2008"
xmlns:mc="//schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
Width="400" Height="117">
View Code
其效果图,如下所示:
其后台MainPage.xaml.cs代码,如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.IO;
namespace Excel导入SQLServer数据库
{
public partial class MainPage : UserControl
{
//在此先定义一个List;
List
public MainPage()
{
InitializeComponent();
}
///
///
///
private void UploadButton_Click(object sender, RoutedEventArgs e)
{
try
{
if (filesToUpload == null)
{
return;
}
foreach (FileInfo file in filesToUpload)
{
//Define the Url object for the Handler
UriBuilder handlerUrl = new UriBuilder("//localhost:5952/UploadFileHandler.ashx");//自己的端口
//Set the QueryString
handlerUrl.Query = "InputFile=" + file.Name;
FileStream FsInputFile = file.OpenRead();
//Define the WebClient for Uploading the Data
WebClient webClient = new WebClient();
//Now make an async class for writing the file to the server
//Here I am using Lambda Expression
webClient.OpenWriteCompleted += (s, evt) =>
{
UploadFileData(FsInputFile, evt.Result);
evt.Result.Close();
FsInputFile.Close();
MessageBox.Show("上传成功!");
listBox1.ItemsSource = "";
};
webClient.OpenWriteAsync(handlerUrl.Uri);
}
}
catch (System.Exception)
{
throw;
}
}
private void UploadFileData(Stream inputFile, Stream resultFile)
{
byte[] fileData = new byte[4096];
int fileDataToRead;
while ((fileDataToRead = inputFile.Read(fileData, 0, fileData.Length)) != 0)
{
resultFile.Write(fileData, 0, fileDataToRead);
}
}
///
///
///
private void OpenButton_Click(object sender, RoutedEventArgs e)
{
try
{
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Multiselect = false;
//这里只写了Excel有关格式,可以根据需要添加其他格式
fileDialog.Filter = "Excel Files(*.xls,*.xlsx)|*.xls";
bool? result = fileDialog.ShowDialog();
if (result != null)
{
if (result == true)
{
filesToUpload = fileDialog.Files.ToList();
listBox1.ItemsSource = filesToUpload;
}
else
return;
}
}
catch (System.Exception)
{
throw;
}
}
}
}
View Code
注意:将其中的端口号跟自己的程序相对应,如下图:
接着,在.web目录下,新建FilesServer文件夹,和UploadFileHandler.ashx的一般处理程序,如下图:
其中,UploadFileHandler.ashx.cs中内容如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace Excel导入SQLServer数据库.Web
{
///
public class UploadFileHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
try
{
string filename = context.Request.QueryString["InputFile"].ToString();
using (FileStream fileStream = File.Create(context.Server.MapPath("~/FilesServer/" + filename)))
{
byte[] bufferData = new byte[4096];
int bytesToBeRead;
while ((bytesToBeRead = context.Request.InputStream.Read(bufferData, 0, bufferData.Length)) != 0)
{
fileStream.Write(bufferData, 0, bytesToBeRead);
}
fileStream.Close();
}
//===========用于对上传的EXCEL文件插入到SQL数据库中===============
string strPath = context.Server.MapPath("~/FilesServer/" + filename);
//string mystring = "Provider = Microsoft.Jet.OleDb.4.0 ; Data Source = ‘" + strPath + "‘;Extended Properties=Excel 8.0";//之前版本链接格式
string mystring = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = ‘" + strPath + "‘;Extended Properties=‘Excel 12.0;HDR=Yes;IMEX=1;‘";//office2010链接格式
OleDbConnection cnnxls = new OleDbConnection(mystring);
if (cnnxls.State == ConnectionState.Closed)
{
cnnxls.Open();
}
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
string ConnStr = "Data Source=WIN-FKM3JDGK01I\\MYSQLR2;Initial Catalog=CDDB;Persist Security Info=True;User ID=sa;Password=123456";
SqlConnection MyConn = new SqlConnection(ConnStr);
MyConn.Open();
//读取Excel中的数据
string xuehao = myDs.Tables[0].Rows[0][0].ToString();
string xingming = myDs.Tables[0].Rows[0][1].ToString();
string strSQL = "insert into CDDB.dbo.Student(XUEHAO,NAME) values (‘" + xuehao + "‘,‘" + xingming + "‘)";
SqlCommand myComm1 = new SqlCommand(strSQL, MyConn);
myComm1.ExecuteNonQuery();
MyConn.Close();
cnnxls.Close();
}
catch (Exception)
{
throw;
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
View Code
注意事项,参考下图:
如此,可以将Excel中的数据写入SQLserver数据库中,经测试,可行,附上代码,仅供参考!
本文由职坐标整理并发布,希望对同学们学习SQL Server有所帮助,更多内容请关注职坐标数据库SQL Server数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号