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