// 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", "") + "\"}]}");
}
}
