C#Web API操作Sqliteデータベース

Web API操作Sqliteデータベース

操作アシスタント

APIインタフェース構成

APIプロファイル

カスタムルーティング

操作アシスタント

Using System.Data.SQLite

SQLiteConnection m_dbConnection;
public SQLiteHelper() {
	//       sqlite     db  
    string connectionstr = string.Format(@"Data Source=" +System.Web.Hosting.HostingEnvironment.MapPath($"/sqlite") + @"/UserWechatLogin.db;Version=3");
    m_dbConnection = new SQLiteConnection(connectionstr);
    if (m_dbConnection.State == ConnectionState.Closed)
        m_dbConnection.Open();
}
// 
public DataTable SelectALL(string sql)
{
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
    SQLiteDataAdapter sdp = new SQLiteDataAdapter(command);
    DataTable ds = new DataTable();
    sdp.Fill(ds);
    sdp.Dispose();
    return ds;
}
// / / 
public bool insert_update_delete_Data(string sql) {
    try
    {
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command = new SQLiteCommand(sql, m_dbConnection);
        command.ExecuteNonQuery();
        return true;
    }
    catch {
        return false;
    }
}

APIインタフェース構成

[RoutePrefix("WeChatLoginAPI/UserInfo")]
public class LoginInfoController : ApiController
{
    SQLiteHelper sQLiteHelper = new SQLiteHelper();
    [Route("Insert_Data")]
    [HttpGet]
    public bool Insert_Datas(string Name,string IMG) {
        DataTable UserInfoDT = sQLiteHelper.SelectALL("SELECT * FROM UserInfo");
        for (int i=0;i< UserInfoDT.Rows.Count; i++) {
            if (UserInfoDT.Rows[i]["LoginName"].ToString() == Name)
                return true;
        }
        bool c1 = sQLiteHelper.insert_update_delete_Data("INSERT INTO UserInfo(LoginName,LoginIMG) VALUES('" + Name + "','" + IMG + "')");
        if (c1)
            return true;
        else
            return false;
    }
    [Route("SELECT_Data")]
    [HttpGet]
    public bool SELECT_Data()
    {
        DataTable c0 = sQLiteHelper.SelectALL("SELECT * FROM UserInfo");
        return true;
    }
}

APIプロファイル

<system.webServer>
  <httpProtocol>
    <customHeaders>
      <add name="Access-Control-Allow-Methods" value="GET, POST, Options, PUT, DELETE" />
      <add name="Access-Control-Allow-Headers" value="x-requsted-with,content-type" />
      <add name="Access-Control-Allow-Origin" value="*" />
    </customHeaders>
  </httpProtocol>
</system.webServer>

カスタムルーティング

config.Routes.MapHttpRoute(
    name: "TestApi",
    routeTemplate: "testapi/{controller}/{ordertype}/{id}",
    defaults: new { ordertype = "aa", id = RouteParameter.Optional }
);