MySQL创建webservice_WebService实现数据库操作(示例代码)

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);

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;

}

解决跨域问题：

2.ajax调用：

var jsonOverlays = JSON.stringify(data.field).replace(/"/g,"‘");// 将双引号替换为单引号

$.ajax({

type:"POST",

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

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

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

}

});

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

2019-1-22测试无误：

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