// POST: api/ExcuteText public string Post(ValueText value) { if (value == null || value.connstr == null || value.connstr == "" || value.sqltext == null) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}"); } general gns = new general(); var dicAppSet = gns.ReadAppseting(); Db_Access ac = new Db_Access(); var check = ac.checkRequertLienTuc(HttpContext.Current, dicAppSet, value.sqltext, ""); if (!check) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Không cho phép request liên tục\"}]}"); } var dicConfig = new Dictionary <string, string>(); dicConfig.Add("connstr", value.connstr); dicConfig.Add("namesql", value.sqltext); dicConfig.Add("commandtype", "Text"); var json = ""; gnOracle orc = new gnOracle(); json = orc.ExcuteReturnJson(dicConfig, null); return(json); }
public DataTable forMartTable(DataTable dt, string insertTo, string conn) { try { var str = "SELECT * FROM " + insertTo + " WHERE ROWNUM =0 "; gnOracle or = new gnOracle(); var ds = or.ExcuteReturnDataSet(str, null, CommandType.Text, conn); if (ds == null) { return(null); } foreach (DataRow row in dt.Rows) { var newRow = ds.Tables[0].NewRow(); foreach (DataColumn col in dt.Columns) { newRow[col.ToString().ToUpper()] = row[col]; } ds.Tables[0].Rows.Add(newRow); } return(ds.Tables[0]); } catch (Exception ex) { return(null); } }
// GET api/Xml_ExportOracle/5 public HttpResponseMessage Get(string idrandom) { general gn = new general(); var dicAppSet = gn.ReadAppseting(); var lst = JObject.Parse(_dicPara_xml["StrColumnNames" + idrandom]).SelectToken("kq").ToString(); var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst); // lay du lieu gnOracle or = new gnOracle(); if (dicAppSet == null || dicAppSet.Count == 0) { return(null); } var dicPara = gn.ParaConvertDicPara(_dicPara_xml["StrPara" + idrandom]); var dicConfig = gn.ConfigConvertToDicConfig(_dicPara_xml["StrConfig" + idrandom]); if (dicPara == null && dicConfig == null) { return(null); } _dicPara_xml.Remove("StrPara" + idrandom); _dicPara_xml.Remove("StrConfig" + idrandom); _dicPara_xml.Remove("StrColumnNames" + idrandom); var dsParaOnSql = or.DecodeProceduePara_New(dicConfig, dicAppSet); var paraOk = or.dicParaToOraclePara_New(dsParaOnSql, dicPara); var ds = or.ExcuteReturnDataSet(dicConfig["namesql"], paraOk.ToList(), CommandType.StoredProcedure, dicAppSet[dicConfig["connstr"].ToLower()]); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return(null); } var ds1 = gn.convertDatasetToLower(ds); string filePath = dicAppSet["path_saveonserver"] + dicConfig["namefile"] + ".xml"; System.IO.StreamWriter xmlSW = new System.IO.StreamWriter(filePath); ds1.WriteXml(xmlSW, XmlWriteMode.WriteSchema); xmlSW.Close(); var response = new HttpResponseMessage(); FileStream fileStream = File.Open(filePath, FileMode.Open); response.Content = new StreamContent(fileStream); response.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment"); response.Content.Headers.ContentDisposition.FileName = dicConfig["namefile"] + ".xml"; response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream"); response.Content.Headers.ContentLength = fileStream.Length; return(response); //return System.Web.Mvc.Controller.(filePath, MimeMapping.GetMimeMapping(filePath)); }
// GET api/excel/5 public string Get(string id) { try { if (id == "") { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại userid\"}]}"); } var user = id; general gn = new general(); dicAppSet = gn.ReadAppseting(); var lst = JObject.Parse(_dicPara_exnom["StrColumnNames" + user]).SelectToken("kq").ToString(); var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst); if (dicAppSet == null || dicAppSet.Count == 0) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}"); } var lstPara = gn.ParaConvertListPara(_dicPara_exnom["StrPara" + user]); var dicConfig = gn.ConfigConvertToDicConfig(_dicPara_exnom["StrConfig" + user]); if (lstPara == null && dicConfig == null) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}"); } _dicPara_exnom.Remove("StrPara" + user); _dicPara_exnom.Remove("StrConfig" + user); _dicPara_exnom.Remove("StrColumnNames" + user); gnOracle orl = new gnOracle(); gnExcel exx = new gnExcel(); // var dtPara = orl.DecodeProceduePara(lstPara, dicConfigOk, dicAppSet); var dtPara = orl.DecodeProceduePara(lstPara, dicConfig, dicAppSet); var lstPrOrl = orl.dicParaToOraclePara(dtPara == null || dtPara.Tables.Count == 0 ? null : dtPara.Tables[0], lstPara); var ds = orl.ExcuteReturnDataSet(dicConfig["namesql"], (lstPrOrl == null ?null: lstPrOrl), CommandType.StoredProcedure, dicAppSet[dicConfig["connstr"].ToLower()]); if (ds == null || ds.Tables.Count == 0) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Không có dữ liệu xuất excel\"}]}"); } for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { ds.Tables[0].Columns[i].ColumnName = ds.Tables[0].Columns[i].ColumnName.ToLower(); } exx.ExporttoExcel(new List <string>(), ds.Tables[0], LstColumnNames, dicConfig.ContainsKey("namefile") ? dicConfig["namefile"] : "" + DateTime.Now.ToString("dd-MM-yyyy"), 1, false, true); return(""); } catch (Exception ex) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}"); } }
// GET api/ExcelExportOracle/5 public string Get(string idrandom) { general gn = new general(); var dicAppSet = gn.ReadAppseting(); var lst = JObject.Parse(_dicPara_exoracle["StrColumnNames" + idrandom]).SelectToken("kq").ToString(); var LstColumnNames = JsonConvert.DeserializeObject <List <ListColumnExport> >(lst); // lay du lieu gnOracle or = new gnOracle(); if (dicAppSet == null || dicAppSet.Count == 0) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}"); } var dicPara = gn.ParaConvertDicPara(_dicPara_exoracle["StrPara" + idrandom]); var dicConfig = gn.ConfigConvertToDicConfig(_dicPara_exoracle["StrConfig" + idrandom]); _dicPara_exoracle.Remove("StrPara" + idrandom); _dicPara_exoracle.Remove("StrConfig" + idrandom); _dicPara_exoracle.Remove("StrColumnNames" + idrandom); if (dicPara == null && dicConfig == null) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}"); } var dsParaOnSql = or.DecodeProceduePara_New(dicConfig, dicAppSet); var paraOk = or.dicParaToOraclePara_New(dsParaOnSql, dicPara); var ds = or.ExcuteReturnDataSet(dicConfig["namesql"], paraOk.ToList(), CommandType.StoredProcedure, dicAppSet[dicConfig["connstr"].ToLower()]); if (ds == null || ds.Tables.Count == 0) { return(""); } var ds1 = gn.convertDatasetToLower(ds); gnExcel exx = new gnExcel(); var idtable = 0; if (dicConfig.ContainsKey("exporttable")) { idtable = Convert.ToInt32(dicConfig["exporttable"]); } exx.ExporttoExcel(new List <string>(), ds1.Tables[idtable], LstColumnNames, dicConfig.ContainsKey("namefile") ? dicConfig["namefile"] : "" + DateTime.Now.ToString("dd-MM-yyyy"), 1, false, true); return(""); }
public string Get(string config, string para) { try { string id = JsonConvert.DeserializeObject <Dictionary <string, string> >(para)["id"]; if (id.Equals("id_user")) { if (dicAppSet == null || dicAppSet.Count == 0) { general gn = new general(); dicAppSet = gn.ReadAppseting(); if (dicAppSet == null || dicAppSet.Count == 0) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}"); } } if (config == null || para == null) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}"); } Db_Access ac = new Db_Access(); var check = ac.checkRequertLienTuc(HttpContext.Current, dicAppSet, config, para); if (!check) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Không cho phép request liên tục\"}]}"); } gnOracle orc = new gnOracle(); var json = orc.ExcuteStores(config, para, dicAppSet); return(json); } else { return(null); } } catch (Exception ex) { return(null); } }
// POST api/ExcuteOracle public string Post(ValueModelOracle value) { if (dicAppSet == null || dicAppSet.Count == 0) { general gn = new general(); dicAppSet = gn.ReadAppseting(); if (dicAppSet == null || dicAppSet.Count == 0) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}"); } } if (value == null || value.config == null) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Kiểm tra lại định dạng json đầu vào\"}]}"); } Db_Access ac = new Db_Access(); var check = ac.checkRequertLienTuc(HttpContext.Current, dicAppSet, value.config, value.para); if (!check) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Không cho phép request liên tục\"}]}"); } //Db_Access_Cookie cook = new Db_Access_Cookie(); //HttpCookie cookie = HttpContext.Current.Request.Cookies["Cookie"]; //var checkCook = cook.checkCookie(cookie, dicAppSet, value.config); //var json = ""; //if (checkCook == "ERROR") //{ // return "{\"result\":\"CookieError\",\"data\":[]}"; //} //else //{ gnOracle orc = new gnOracle(); var json = orc.ExcuteStores(value.config, value.para, dicAppSet); // } return(json); }
public string BulkCopyToOracle(DataTable dt, Dictionary <string, string> config) { try { string connectOracle = ConfigurationManager.ConnectionStrings[config["connstr"]].ConnectionString; gnOracle gn = new gnOracle(); using (var connection = new OracleConnection(connectOracle)) { connection.Open(); using (var bulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction)) { bulkCopy.DestinationTableName = config.ContainsKey("insertto")? config["insertto"]: dt.TableName; var table = gn.potentialFix(dt); bulkCopy.WriteToServer(table); } } return("{\"result\":\"OK\",\"data\":\"OK\"}"); } catch (Exception ex) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}"); } }
public string InportXmlToOrcale() { try { general gn = new general(); string fileName = ""; HttpPostedFileBase file = Request.Files[0]; //Uploaded file //Use the following properties to get file's name, size and MIMEType int fileSize = file.ContentLength; fileName = file.FileName; string mimeType = file.ContentType; System.IO.Stream fileContent = file.InputStream; if (!Directory.Exists(Server.MapPath("~/UploadFile/"))) { Directory.CreateDirectory(Server.MapPath("~/UploadFile/")); } //To save file, use SaveAs method string filePath = Server.MapPath("~/UploadFile/") + fileName; file.SaveAs(filePath); //File will be saved in application root var itemp = this.Request.Form; var parr = new Dictionary <string, string>(); foreach (var key in itemp.AllKeys) { parr.Add(key.ToLower(), itemp[key]); } // CHUYEN SANG DATASET var ds = new DataSet(); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(filePath); var xmlReader = new XmlNodeReader(xmlDoc); ds.ReadXml(xmlReader); var ds1 = gn.convertDatasetToLower(ds); if (ds1 == null || ds1.Tables.Count == 0) { return("{\"result\":\"OK\",\"data\":[{\"status\":\"Lỗi không đọc được file xml\"}]}"); } // add them 1 so cot DataColumn tenfile = new DataColumn("tenfile", typeof(System.String)); tenfile.DefaultValue = fileName; DataColumn ngayup = new DataColumn("ngayup", typeof(System.String)); ngayup.DefaultValue = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); DataColumn idrandom = new DataColumn("idrandom", typeof(System.String)); idrandom.DefaultValue = parr["idrandom"]; ds1.Tables[0].Columns.Add(tenfile); ds1.Tables[0].Columns.Add(ngayup); ds1.Tables[0].Columns.Add(idrandom); // check bang neu chua ton tai thi tao bang gnOracle or = new gnOracle(); general gns = new general(); var dicAppSet = gns.ReadAppseting(); var checkTonTai = "SELECT COUNT(1) TONTAI FROM USER_TABLES WHERE UPPER(TABLE_NAME) = UPPER('" + parr["insertto"] + "') "; var count = or.ExcuteReturnDataSet(checkTonTai, null, CommandType.Text, dicAppSet[parr["connstr"].ToLower()]); if (count != null && count.Tables.Count > 0 && count.Tables[0].Rows.Count > 0 && count.Tables[0].Rows[0]["TONTAI"].ToString() == "0") { // TAO COT CHINH var str = "CREATE TABLE " + parr["insertto"] + " ( "; foreach (var col in ds1.Tables[0].Columns) { str += " " + col.ToString().ToUpper() + " VARCHAR2(255),"; } str = str.Substring(0, str.Length - 1) + " ) "; or.ExcuteReturnDataSet(str, null, CommandType.Text, dicAppSet[parr["connstr"].ToLower()]); } // bulk copy to oracle gnOracle gnOr = new gnOracle(); string connectOracle = dicAppSet[parr["connstr"].ToLower()]; var dt2 = gnOr.forMartTable(ds1.Tables[0], parr["insertto"], connectOracle); using (var connection = new OracleConnection(connectOracle)) { connection.Open(); using (var bulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction)) { bulkCopy.DestinationTableName = parr["insertto"]; var table = gnOr.potentialFix(dt2); bulkCopy.WriteToServer(table); } } return("{\"result\":\"OK\",\"data\":[{\"idrandom\":\"" + parr["idrandom"] + "\"}]}"); } catch (Exception ex) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}"); } }
public string InportXmlToOrcale() { try { general gn = new general(); string fileName = ""; HttpPostedFileBase file = Request.Files[0]; //Uploaded file //Use the following properties to get file's name, size and MIMEType int fileSize = file.ContentLength; fileName = file.FileName; string mimeType = file.ContentType; System.IO.Stream fileContent = file.InputStream; if (!Directory.Exists(Server.MapPath("~/UploadFile/"))) { Directory.CreateDirectory(Server.MapPath("~/UploadFile/")); } //To save file, use SaveAs method string filePath = Server.MapPath("~/UploadFile/") + fileName; file.SaveAs(filePath); //File will be saved in application root var itemp = this.Request.Form; var parr = new Dictionary <string, string>(); foreach (var key in itemp.AllKeys) { parr.Add(key.ToLower(), itemp[key]); } var insertTo = parr["insertto"]; var connstr = parr["connstr"]; parr.Remove("insertto"); parr.Remove("connstr"); // CHUYEN SANG DATASET var ds = new DataSet(); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(filePath); var xmlReader = new XmlNodeReader(xmlDoc); ds.ReadXml(xmlReader); var ds1 = gn.convertDatasetToLower(ds); var dt = new DataTable("dt"); foreach (var val in parr) { dt.Columns.Add(val.Key); } foreach (DataRow row in ds1.Tables[0].Rows) { var dr = dt.NewRow(); foreach (var val in parr) { dr[val.Key] = row[val.Key]; } dt.Rows.Add(dr); } // bulk copy to oracle gnOracle gnOr = new gnOracle(); string connectOracle = ConfigurationManager.ConnectionStrings[connstr].ConnectionString; using (var connection = new OracleConnection(connectOracle)) { connection.Open(); using (var bulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction)) { bulkCopy.DestinationTableName = insertTo; var table = gnOr.potentialFix(dt); bulkCopy.WriteToServer(table); } } return("{\"result\":\"OK\",\"data\":[{\"status\":\"OK\"}]}"); } catch (Exception ex) { return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}"); } }