// GET api/ExcuteOracle
public IEnumerable <string> Get()
{
try
{
OracleConnection conn = null;
OracleCommand cmd = null;
OracleDataAdapter adapter = null;
try
{
string connectOracle = ConfigurationManager.ConnectionStrings["ConnectOracle"].ConnectionString;
conn = new OracleConnection(connectOracle);
conn.Open();
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT 'OK' OK FROM DUAL";
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
adapter = new OracleDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
general gn = new general();
var data = gn.ConvertDatasetToListJson(ds);
if (data == null)
{
return new string[] { "ok 1", "1" }
}
;
var json = gn.ConvertListToJson(data);
var strResult = "{\"result\":\"OK\",\"data\":" + json + "}";
return(new string[] { "ok 2", json });
}
catch (Exception ex)
{
return(new string[] { "ERROR 3", ex.Message });
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (adapter != null)
{
adapter.Dispose();
}
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
return(new string[] { "value1", "value2" });
}
catch (Exception ex) { return(new string[] { "errror 4 ", ex.Message }); }
}
public string ExcuteReturnJson(Dictionary <string, string> dicConfig, List <OracleParameter> lstOrlPara)
{
OracleConnection conn = null;
OracleCommand cmd = null;
OracleDataAdapter adapter = null;
try
{
string connectOracle = ConfigurationManager.ConnectionStrings[dicConfig["connstr"]].ConnectionString;
conn = new OracleConnection(connectOracle);
conn.Open();
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = dicConfig["namesql"];
cmd.CommandType = dicConfig.ContainsKey("commandtype") && dicConfig["commandtype"].ToLower() == "text" ? CommandType.Text: CommandType.StoredProcedure;
cmd.BindByName = true;
if (lstOrlPara != null)
{
cmd.Parameters.AddRange(lstOrlPara.ToArray());
}
adapter = new OracleDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
general gn = new general();
var data = gn.ConvertDatasetToListJson(ds);
if (data == null)
{
return("{\"result\":\"OK\",\"data\":\"[{\"status\":[]\"}]}");
}
;
var json = gn.ConvertListToJson(data);
var strResult = "{\"result\":\"OK\",\"data\":" + json + "}";
return(strResult);
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (adapter != null)
{
adapter.Dispose();
}
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
public string ExecuteSqlDataset(Dictionary <string, string> dicConfig, Dictionary <string, string> dicParr)
{
SqlConnection Conn = null;
SqlCommand command = null;
SqlDataAdapter adapter = null;
try
{
// SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings[_dicConfig["connstr"]].ConnectionString);
// var conStr = Connection != null ? Connection.ConnectionString : "";
general gn = new general();
var dicApp = gn.ReadAppseting();
string Connection = dicApp[dicConfig["connstr"].ToString().ToLower()];// dicApp["connectsql"];
var conStr = Connection != null ? Connection : "";
if (conStr == "")
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Lỗi khi gọi ConnectionString\"}]}");
}
Conn = new SqlConnection(conStr);
command = new SqlCommand {
Connection = Conn
};
command.CommandText = dicConfig["namesql"];
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
if (dicParr != null)
{
List <SqlParameter> lstParr = new List <SqlParameter>();
foreach (var item in dicParr)
{
lstParr.Add(new SqlParameter("@" + item.Key, item.Value));
}
// trường hợp đặc biệt thêm mã hóa đăng nhập
// encode: true, passold: "PP01000111917", passnew: "PasswordEncrypt"
if (dicConfig.ContainsKey("encode") && Convert.ToBoolean(dicConfig["encode"]))
{
string passEncrypt = SLCommon.Security.EncryptString(dicConfig["passold"]);
lstParr.Add(new SqlParameter("@" + dicConfig["passnew"], passEncrypt));
}
command.Parameters.AddRange(lstParr.ToArray());
}
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
adapter = new SqlDataAdapter(command);
var ds = new DataSet();
adapter.Fill(ds);
var data = gn.ConvertDatasetToListJson(ds);
if (data == null)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Lỗi khi ConvertDatasetToListJson\"}]}");
}
var json = gn.ConvertListToJson(data);
var strResult = "{\"result\":\"OK\",\"data\":" + json + "}";
return(strResult);
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
finally
{
if (command != null)
{
command.Dispose();
command = null;
}
if (adapter != null)
{
//if(adapter.is)
adapter.Dispose();
adapter = null;
}
if (Conn.State != ConnectionState.Closed)
{
Conn.Close();
}
}
}
public string readKml()
{
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\"}]}");
}
var dt = new DataTable("dt");
dt.Columns.Add("name");
dt.Columns.Add("coordinates");
dt.Columns.Add("description");
foreach (DataRow row in ds.Tables["Placemark"].Rows)
{
var name = row["name"].ToString();
var description = row["description"].ToString();
var coor = ds1.Tables["Point"].Select("Placemark_id=" + row["Placemark_id"].ToString());
var coordinates = "";
if (coor != null && coor.Length > 0)
{
coordinates = coor[0]["coordinates"].ToString();
}
else
{
var coor1 = ds1.Tables["LineString"].Select("Placemark_id=" + row["Placemark_id"].ToString());
if (coor1 != null && coor1.Length > 0)
{
coordinates = coor1[0]["coordinates"].ToString();
}
}
dt.Rows.Add(name, coordinates, description);
}
var ds2 = new DataSet();
ds2.Tables.Add(dt);
var data = gn.ConvertDatasetToListJson(ds2);
if (data == null)
{
return("{\"result\":\"OK\",\"data\":[{\"status\":\"Lỗi khi ConvertDatasetToListJson\"}]}");
}
var json = gn.ConvertListToJson(data);
return("{\"result\":\"OK\",\"data\":" + json + "}");
}
catch (Exception ex)
{
return("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}");
}
}
public JsonResult ReadExcel()
{
try
{
Dictionary <string, string> dicAppSet = null;
if (dicAppSet == null || dicAppSet.Count == 0)
{
general gn = new general();
dicAppSet = gn.ReadAppseting();
if (dicAppSet == null || dicAppSet.Count == 0)
{
Json("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Đọc appsetting lỗi\"}]}");
}
}
var path = dicAppSet["path_saveonserver"];
string fileName = "";
for (int i = 0; i < Request.Files.Count; i++)
{
HttpPostedFileBase file = Request.Files[i]; //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(path))
{
Directory.CreateDirectory(path);
}
//To save file, use SaveAs method
file.SaveAs(path + fileName); //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]);
}
gnReadExcel ex = new gnReadExcel();
var dt = ex.readfileExcel(path + fileName, parr["select"] + " ", " " + parr["where"]);
var ds = new DataSet();
ds.Tables.Add(dt);
general gn = new general();
var data = gn.ConvertDatasetToListJson(ds);
if (data == null)
{
return(Json("{\"result\":\"ERROR\",\"data\":[{\"status\":\"Lỗi khi ConvertDatasetToListJson\"}]}"));
}
var json = gn.ConvertListToJson(data);
var strResult = Json("{\"result\":\"OK\",\"data\":" + json + "}");
System.IO.File.Delete(path + fileName);
return(strResult);
}
return(Json("{\"result\":\"OK\",\"data\":[{\"status\":\"Không nhận được file\"}]}"));
}
catch (Exception ex)
{
return(Json("{\"result\":\"ERROR\",\"data\":[{\"status\":\"" + ex.Message.Replace("\n", "") + "\"}]}"));
}
}
