ajax调用mysql数据_ajax调用WebService实现数据库操作

末蓝、 2022-10-26 14:22 186阅读 0赞

首先说下测试环境和思路：

前端收集数据转换成json格式传输到后端，处理并存入数据库

1.数据库操作：

\[WebMethod\]public string InsertPoint(stringdata)

\{//解决跨域问题//Context.Response.AddHeader("Access-Control-Allow-Origin", "\*");

string database = "Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=password1633";//string data = HttpContext.Current.Request\["data"\];

try\{if (data.Equals("") || data == null)

\{return "data is null";

\}else\{

OverlaysPoint opoint= JsonConvert.DeserializeObject(data);// 将接收的参数data实例化为一个对象(方便操作)，注意该类的变量名要和json的key值对应/\*foreach (Data\_Opoint dtpoint in opoint.overlays)

sql = "insert into Map\_Overlays(wf\_pictureid,wf\_picturename,wf\_railid,wf\_name,wf\_location,wf\_createtime,wf\_pid,wf\_lng,wf\_lat) values('" + "123456789" + "','" + opoint.picturename + "','" + opoint.id + "','" + opoint.name + "','" + opoint.location + "','" + opoint.createime + "','" + dtpoint.pid + "','" + dtpoint.lng.ToString() + "','" + dtpoint.lat.ToString() + "')";

ExecuteUpdate(sql, database);

\}\*/

string\[\] xyArray = opoint.position.Split(new string\[\] \{ "(", ",", ")"\}, StringSplitOptions.RemoveEmptyEntries); //处理json字符串

//执行sql语句string sql = "insert into map\_img\_points(miid,pointtype,pointid, pointcode, positionx, positiony, pointinfo) values ('地图id', '" + opoint.point\_type + "', '" + opoint.point\_bm + "','" + opoint.point\_code + "','" + xyArray\[0\] + "','" + xyArray\[1\] + "', '" + opoint.point\_info + "')";

executeUpdate(sql, database);return "success";

\}catch(NullReferenceException e)

\{returne.StackTrace;

Data Source ：  一个点默认为本地数据库，其他数据库填写相应ip ；

Initial Catelog：是目标数据库名称

我们接收一个json数据，将其value填入一个实例化的类对象，通过这个类的实例化对象来使用它

OverlaysPoint opoint = JsonConvert.DeserializeObject(data);

根据json创建的OverPoint类：

public classOverlaysPoint

\{public stringpoint\_type;public stringpoint\_bm;public stringpoint\_code;public stringposition;public stringpoint\_info;public stringcon\_bussiness;public stringcon\_entity;public stringcon\_fields;public stringshowpic;public stringshowtext;public stringshowdata;public stringcreate\_time;public stringdata\_refresh\_rate;

executeQuery和executeUpdate方法(可重用)：

//对数据库操作//用于查询

private DataTable executeQuery(string sqlStr, stringsqlCon)

SqlConnection con= newSqlConnection(@sqlCon);

con.Open();

SqlCommand cmd= newSqlCommand();

cmd.Connection=con;

cmd.CommandType=CommandType.Text;

cmd.CommandText=sqlStr;

DataTable dt= new DataTable(); //实例化数据表

SqlDataAdapter msda;

msda= new SqlDataAdapter(cmd); //实例化适配器

msda.Fill(dt); //保存数据

con.Close();returndt;

\}//用于增删改;

private int executeUpdate(string sqlStr, stringsqlCon)

SqlConnection con= newSqlConnection(@sqlCon);

con.Open();

SqlCommand cmd= newSqlCommand();

cmd.Connection=con;

cmd.CommandType=CommandType.Text;

cmd.CommandText=sqlStr;int iud = 0;

iud=cmd.ExecuteNonQuery();

con.Close();returniud;

要使用JsonConvert需要引入外部依赖包

右键项目解决方案-添加引用-

2.ajax调用：

var jsonOverlays = JSON.stringify(data.field).replace(/\\"/g,"'");// 将json数据的双引号替换为单引号

$.ajax(\{

type:"POST",

contentType:"application/json; charset=utf-8",

url:"http://192.168.21.1:7777/WebService1.asmx/InsertPoint",

data:"\{\\"data\\":\\"" + jsonOverlays + "\\"\}", //这是post请求要传递的参数

dataType: 'json', //预期返回类型

success: function(result)\{ //回调函数

layer.msg('ajax调用成功' +result.d);

console.log(result.d);

\},

error: function (data) \{//200的响应有可能被认为error, responseText中没有Message部分

alert('error');

\},

complete: function (data) \{

;//after success ot error

\});

注意：1.双引号替换为单引号  2.构造data的参数

ps：报错：

No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:63342' is therefore not allowed access.

解决跨域问题：

![89044c469d895b31eedd7482d7715804.png][]

2019-1-22测试无误：

![3ce5ce2408a229b9459065dbb793d48f.png][]

如果出错，注意查看浏览器端控制台报错(ajax出错)和vs输出窗口(c\#方法出错)

[89044c469d895b31eedd7482d7715804.png]: /images/20221024/d19e4cc419994c178ad7f9507a905ecb.png
[3ce5ce2408a229b9459065dbb793d48f.png]: /images/20221024/3250fd5d988941cdb9743995e729f63f.png