i making web service, should read excel file, , return sql table values of excel sheet in appropriate columns. here code web service:
using system; using system.collections.generic; using system.linq; using system.web; using system.web.services; using system.data; using system.data.oledb; using system.data.common; using system.data.sql; using system.data.sqlclient; using system.io; namespace fileuploader { /// <summary> /// summary description service1 /// </summary> [webservice(namespace = "http://tempuri.org/")] [webservicebinding(conformsto = wsiprofiles.basicprofile1_1)] [system.componentmodel.toolboxitem(false)] // allow web service called script, using asp.net ajax, uncomment following line. // [system.web.script.services.scriptservice] public class service1 : system.web.services.webservice { [webmethod] public string simple() { return "hello world"; } [webmethod] private string cleaner(string query) { query = query.replace("'", ""); query = query.replace("\\", ""); query = query.replace("/", ""); query = query.replace("\"", ""); query = query.replace(".", ""); query = query.replace(";", ","); return query; } [webmethod] private string insertinsql(string query) { string constr = @"data source=.\sqlexpress;attachdbfilename=""c:\users\sagnik\documents\visual studio 2010\projects\fileuploader\fileuploader\app_data\database1.mdf"";integrated security=true;user instance=true"; sqlconnection con = new sqlconnection(constr); try { con.open(); } catch (exception e) { return e.tostring(); } sqlcommand cmd = new sqlcommand(query, con); cmd.connection = con; try { cmd.executenonquery(); } catch (exception e) { return e.tostring(); } con.close(); return "1"; } [webmethod] public string upload(string file) { //string path = path.getfullpath(file); system.data.datatable dt = null; string sourceconstr = @"provider=microsoft.ace.oledb.12.0;data source='" + file + "';extended properties= 'excel 8.0;hdr=no;imex=1'"; oledbconnection con = new oledbconnection(sourceconstr); try { con.open(); } catch(exception e) { return e.tostring(); } dt = con.getoledbschematable(oledbschemaguid.tables, null); if (dt == null) { return "dt null"; } string[] excelsheets = new string[dt.rows.count]; int sh = 0; foreach (datarow row in dt.rows) { if (row["table_name"].tostring().contains("$")) { excelsheets[sh] = row["table_name"].tostring(); sh++; } } int count = 0; (int z = 0; z < sh; z++) { if (!excelsheets[z].tostring().contains("print_area")) { count = 0; string query = "select top 1 * [" + excelsheets[z] + "];"; oledbcommand command = new oledbcommand(query, con); oledbdatareader odr = command.executereader(); string[] names = new string[50]; while (odr.read()) { count = odr.fieldcount; int = 0; while (i < count) { names[i] = cleaner(odr[i].tostring()).replace(" ", ""); += 1; } break; } int j = 0; string name = path.getfilenamewithoutextension(file); query = "create table " + cleaner(name) + "_" + cleaner(excelsheets[z]).replace(" ", "").replace("$", "") + " ( "; (int = 0; < count; i++) { if (names[i] != "") { query += (names[i] + " varchar(100), "); j += 1; } } if (query.contains("varchar(100)")) { count = j; query = query.substring(0, query.length - 2); query = query + ");"; string checker = insertinsql(query); if (checker != "1") { return checker; } odr.close(); string src = @"provider=microsoft.ace.oledb.12.0;data source='" + file + "';extended properties= 'excel 8.0;hdr=yes;imex=1'"; oledbconnection conn = new oledbconnection(src); conn.open(); string q = "select * [" + excelsheets[z] + "];"; oledbcommand cmd = new oledbcommand(q, conn); odr = cmd.executereader(); int check = 1; while (odr.read()) { query = "insert " + cleaner(name) + "_" + cleaner(excelsheets[z]).replace(" ", "").replace("$", "") + " ("; (int = 0; < count; i++) { query += (names[i] + ", "); } query = query.substring(0, query.length - 2); query += ") values ("; (int = 0; < count; i++) { query += ("'" + cleaner(odr[i].tostring()) + "', "); } query = query.substring(0, query.length - 2); query += ");"; if (check == 1) { insertinsql(query); } else { return "error"; } } conn.close(); } } } con.close(); return "copy successful...tables saved " + path.getfilenamewithoutextension(file); } } } and here test application, uses web service:
using system; using system.collections.generic; using system.linq; using system.web; using system.web.ui; using system.web.ui.webcontrols; using testuploader.testweb; namespace testuploader { public partial class _default : system.web.ui.page { protected void page_load(object sender, eventargs e) { } protected void button1_click(object sender, eventargs e) { if (fileupload1.filename == "") { return; } else if (system.io.path.getextension(fileupload1.filename).tostring() != ".xls" && system.io.path.getextension(fileupload1.filename).tostring() != ".xlsx") { response.write("<script type='text/javascript'>alert('please upload valid excel file');</script>"); return; } else { label1.text = "please wait...copying data"; service1 ser = new service1(); string path = savefile(fileupload1.postedfile);//system.io.path.getfullpath(fileupload1.filename); string s = ser.upload(path); label1.text = s; } } string savefile(httppostedfile file) { // specify path save uploaded file to. string savepath = "c:\\apps\\uploads\\"; // name of file upload. string filename = fileupload1.filename; // create path , file name check duplicates. string pathtocheck = savepath + filename; // create temporary file name use checking duplicates. string tempfilename = ""; // check see if file exists // same name file upload. if (system.io.file.exists(pathtocheck)) { int counter = 2; while (system.io.file.exists(pathtocheck)) { // if file name exists, // prefix filename number. tempfilename = system.io.path.getfilenamewithoutextension(filename) + counter.tostring() + system.io.path.getextension(filename); pathtocheck = savepath + tempfilename; counter++; } filename = tempfilename; // notify user file name changed. } else { // notify user file saved successfully. } // append name of file upload path. savepath += filename; // call saveas method save uploaded // file specified directory. fileupload1.saveas(savepath); return savepath; } } } this code works perfectly, uploads file web service, , database created within web-service. now, told when using web-service, can't keep uploading files, web-service may not have access. upload excel file data-row, work done client program, , want web-service user-friendly possible.
so, have 2 questions:
is there better way job without having upload file web service?
since table has created in database of client program, thinking of appending queries same string, , returning string client program, can run @ sql server manager. again, there better way of making more user-friendly?
thanks in advance!
Comments
Post a Comment