Asp.net简单三层+Sqllite 增删改查

2018-06-23 23:13:46来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

  1. 新建项目à新建一个空白解决方案
  2. 在Model新建一个实体类
    1. using System;
    2. using System.Collections.Generic;
    3. using System.Linq;
    4. using System.Text;
    5. using System.Threading.Tasks;
    6.  
    7.  
    8. namespace factory.Model
    9. {
    10.     public class factorys
    11.     {
    12.     //ID INTEGER PRIMARY KEY AUTOINCREMENT
    13.     // NOT NULL
    14.     // DEFAULT 1,
    15.     //correspondent NVARCHAR( 100 ) COLLATE NOCASE,
    16.     //contactaddress NVARCHAR( 100 ) COLLATE NOCASE,
    17.     //contacts NVARCHAR( 50 ) COLLATE NOCASE,
    18.     //contactway NVARCHAR( 50 ) COLLATE NOCASE,
    19.     //contactposition NVARCHAR( 50 ),
    20.     //dutydepartment NVARCHAR( 50 ),
    21.     //dutyofficer NVARCHAR( 50 ) COLLATE NOCASE,
    22.     //note NVARCHAR( 2000 ) COLLATE NOCASE
    23.         private int _ID;
    24.  
    25.         public int ID
    26.         {
    27.             get { return _ID; }
    28.             set { _ID = value; }
    29.         }
    30.  
    31.         /// <summary>
    32.         /// 客户单位
    33.         /// </summary>
    34.         private string _correspondent;
    35.  
    36.         public string Correspondent
    37.         {
    38.             get { return _correspondent; }
    39.             set { _correspondent = value; }
    40.         }
    41.  
    42.         /// <summary>
    43.         /// 联系地址
    44.         /// </summary>
    45.         private string _contactaddress;
    46.  
    47.         public string Contactaddress
    48.         {
    49.             get { return _contactaddress; }
    50.             set { _contactaddress = value; }
    51.         }
    52.  
    53.         /// <summary>
    54.         /// 联系人
    55.         /// </summary>
    56.         private string _contacts;
    57.  
    58.         public string Contacts
    59.         {
    60.             get { return _contacts; }
    61.             set { _contacts = value; }
    62.         }
    63.  
    64.         /// <summary>
    65.         /// 联系方式
    66.         /// </summary>
    67.         private string _contactway;
    68.  
    69.         public string Contactway
    70.         {
    71.             get { return _contactway; }
    72.             set { _contactway = value; }
    73.         }
    74.  
    75.         /// <summary>
    76.         /// 联系人职务
    77.         /// </summary>
    78.         private string _contactposition;
    79.  
    80.         public string Contactposition
    81.         {
    82.             get { return _contactposition; }
    83.             set { _contactposition = value; }
    84.         }
    85.  
    86.         /// <summary>
    87.         /// 负责部门
    88.         /// </summary>
    89.         private string _dutydepartment;
    90.  
    91.         public string Dutydepartment
    92.         {
    93.             get { return _dutydepartment; }
    94.             set { _dutydepartment = value; }
    95.         }
    96.  
    97.         /// <summary>
    98.         /// 负责人
    99.         /// </summary>
    100.         private string _dutyofficer;
    101.  
    102.         public string Dutyofficer
    103.         {
    104.             get { return _dutyofficer; }
    105.             set { _dutyofficer = value; }
    106.         }
    107.  
    108.         /// <summary>
    109.         /// 备注
    110.         /// </summary>
    111.         private string _note;
    112.  
    113.         public string Note
    114.         {
    115.             get { return _note; }
    116.             set { _note = value; }
    117.         }
    118.  
    119.  
    120.     }
    121. }

     

  3. 右击解决方案名称à新建一个DAL与BLL ,Model类库,因为个人习惯建好每个类库时喜欢à右击类库属性à默认命名空间将factoryModel改为àfactory.Model所以应用命名空间时为
    1. using factory.Model;
  4. 如果不更改则为
    1. using factoryModel;
  5. 新建一个Windows窗体应用程序(UI层)
  6. 添加引用 :    因为用的是SQLite数据库所以要手动添加一个SQLite的dll引用文件,在解决方案下新建一个lib文件夹将SQLite的dll文件添加进去 DAL引用Model,与新建的lib文件下的SQLite.dll 及     BLL引用DAL,Model与Model UI引用DAL与Model
  7. DAL下的ado.net SqlliteHelper.cs
    1. using System;
    2. using System.Collections.Generic;
    3. using System.Linq;
    4. using System.Text;
    5. using System.Threading.Tasks;
    6. using System.Configuration;
    7. using System.Data.SQLite;
    8. using System.Data;
    9. namespace factory.DAL
    10. {
    11.     public class SqlliteHelper
    12.     {
    13.         //连接字符串
    14.         private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    15.  
    16.         //方法
    17.         /// <summary>
    18.         /// 增删改 都可以
    19.         /// </summary>
    20.         /// <param name="sql">sql语句</param>
    21.         /// <param name="ps">sql语句中的参数</param>
    22.         /// <returns>返回受影响的行数</returns>
    23.         public static int ExecuteNonQuery(string sql, params SQLiteParameter[] ps)
    24.         {
    25.             try
    26.             {
    27.                 using (SQLiteConnection con = new SQLiteConnection(str))
    28.                 {
    29.                     using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
    30.                     {
    31.                         if (ps != null)
    32.                         {
    33.                             cmd.Parameters.AddRange(ps);
    34.                         }
    35.                         con.Open();
    36.                         return cmd.ExecuteNonQuery();
    37.                     }
    38.                 }
    39.             }
    40.             catch (Exception ex)
    41.             {
    42.                 throw ex;
    43.             }
    44.  
    45.         }
    46.         /// <summary>
    47.         /// 查询首行首列
    48.         /// </summary>
    49.         /// <param name="sql">sql语句</param>
    50.         /// <param name="ps">参数</param>
    51.         /// <returns>首行首列object</returns>
    52.         public static object ExecuteScalar(string sql, params SQLiteParameter[] ps)
    53.         {
    54.             try
    55.             {
    56.                 using (SQLiteConnection con = new SQLiteConnection(str))
    57.                 {
    58.                     using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
    59.                     {
    60.                         con.Open();
    61.                         if (ps != null)
    62.                         {
    63.                             cmd.Parameters.AddRange(ps);
    64.                         }
    65.                         return cmd.ExecuteScalar();
    66.                     }
    67.                 }
    68.             }
    69.             catch (Exception ex)
    70.             {
    71.                 throw ex;
    72.             }
    73.  
    74.         }
    75.  
    76.         /// <summary>
    77.         /// 查询的
    78.         /// </summary>
    79.         /// <param name="sql"></param>
    80.         /// <param name="ps"></param>
    81.         /// <returns></returns>
    82.         public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] ps)
    83.         {
    84.             SQLiteConnection con = new SQLiteConnection(str);
    85.             try
    86.             {
    87.                 using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
    88.                 {
    89.                     if (ps != null)
    90.                     {
    91.                         cmd.Parameters.AddRange(ps);
    92.                     }
    93.                     try
    94.                     {
    95.                         con.Open();
    96.                         return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    97.                     }
    98.                     catch (Exception ex)
    99.                     {
    100.                         con.Close();
    101.                         con.Dispose();
    102.                         throw ex;
    103.                     }
    104.                 }
    105.             }
    106.             catch (Exception ex)
    107.             {
    108.  
    109.                 throw ex;
    110.             }
    111.  
    112.         }
    113.  
    114.         /// <summary>
    115.         /// 查询的是一个表
    116.         /// </summary>
    117.         /// <param name="sql">sql语句</param>
    118.         /// <param name="ps">sql语句中的参数</param>
    119.         /// <returns>一个表</returns>
    120.         public static DataTable ExecuteTable(string sql, params SQLiteParameter[] ps)
    121.         {
    122.             try
    123.             {
    124.                 DataTable dt = new DataTable();
    125.                 using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str))
    126.                 {
    127.                     if (ps != null)
    128.                     {
    129.                         sda.SelectCommand.Parameters.AddRange(ps);
    130.  
    131.                     }
    132.                     sda.Fill(dt);
    133.  
    134.                 }
    135.                 return dt;
    136.             }
    137.             catch (Exception ex)
    138.             {
    139.  
    140.                 throw ex;
    141.             }
    142.  
    143.         }
    144.  
    145.     }
    146. }
  8. App.config配置文件
    1. <?xml version="1.0" encoding="utf-8" ?>
    2. <configuration>
    3.   <startup useLegacyV2RuntimeActivationPolicy="true">
    4.  
    5.     <supportedRuntime version="v4.0"/>
    6.  
    7.   </startup>
    8.  
    9.   <connectionStrings>
    10.  
    11.     <add connectionString="Data Source=factory.db;Version=3;" name="conStr"/>
    12.   </connectionStrings>
    13. </configuration>
  9. SQLite数据库文件就放在UI的binàdebug目录下
  10. 最后说下参数是
  11. 完整图片
  12. 这是本人第一次写博客,主要还是以记录为主,如有其它不当之处还望指点
  13.  源码下载 
  14. SQLite第三方编辑工具(sqlitestudio)

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:2015年,年终项目总结

下一篇:asp.netDataTable导出excel方法(1)