silverlight连接SQLite数据库，增删改查

最新推荐文章于 2023-08-27 17:14:04 发布

FrogView

最新推荐文章于 2023-08-27 17:14:04 发布

阅读量321

点赞数

分类专栏： C#学习和进阶文章标签：数据库 silverlight sqlite

本文链接：https://blog.youkuaiyun.com/qq_32493189/article/details/53057021

版权

C#学习和进阶专栏收录该内容

27 篇文章

订阅专栏

1,在web程序中添加新建项-启用了silverlight的wcf服务，命名为ServiceSQLite

using System.Data;
using System.Collections.Generic;
using System.Data.SQLite;

namespace Projects_Manager.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class ServiceSQLite
    {
         [OperationContract]
        public List<ClassEmployee> getUser()
        {
            DataSet ds = DBSQLite.DBselect("select * from UserData");

            List<ClassEmployee> userList = new List<ClassEmployee>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                ClassEmployee user = new ClassEmployee();//userList是引用类型，所以这句不能放foreach外面
                user.ID =int.Parse( dr["ID"].ToString());
                user.Name = (string)dr["Name"];
                user.Group = (int)dr["Group"];
                user.Pwd = (string)dr["Pwd"];
                user.Limit = (int)dr["Limit"];
                user.ProLimit0 = dr["ProLimit0"] == DBNull.Value ? 1 : (int)dr["ProLimit0"];
                user.ProLimit1 = dr["ProLimit1"] == DBNull.Value ? 1 : (int)dr["ProLimit1"];
                user.ProLimit2 = dr["ProLimit2"] == DBNull.Value ? 1 : (int)dr["ProLimit2"];
                user.ProLimit3 = dr["ProLimit3"] == DBNull.Value ? 1 : (int)dr["ProLimit3"];
                user.ProLimit4 = dr["ProLimit4"] == DBNull.Value ? 1 : (int)dr["ProLimit4"];
                user.ProLimit5 = dr["ProLimit5"] == DBNull.Value ? 1 : (int)dr["ProLimit5"];
                userList.Add(user);
            }
            return userList;
        }

        [OperationContract]
        public void InsertUser(string Name, string Pwd, int Group, int Limit)
        {
            DBSQLite.InsertUser(Name, Pwd, Group, Limit);
        }

        [OperationContract]
        public void DeleteUser(int ID)
        {
            DBSQLite.DeleteUser(ID);
        }

        [OperationContract]
        public void UpdataPwd(int ID, string Pwd)
        {
            DBSQLite.UpdataPwd(ID, Pwd);
        }

         [DataContract]
        public class ClassEmployee
        {
            [DataMember]
            public int ID;
            [DataMember]
            public string Name;
            [DataMember]
            public string Pwd;
            [DataMember]
            public int Group;
            [DataMember]
            public int Limit;
            [DataMember]
            public int ProLimit0;
            [DataMember]
            public int ProLimit1;
            [DataMember]
            public int ProLimit2;
            [DataMember]
            public int ProLimit3;
            [DataMember]
            public int ProLimit4;
            [DataMember]
            public int ProLimit5;
        }

    }
}

2.添加一个类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SQLite;
using System.Data;

namespace Projects_Manager.Web
{
    public class DBSQLite
    {
     /// <summary>
       /// 数据库查找
       /// </summary>
       /// <param name="sql"></param>
       /// <returns></returns>
        static public DataSet DBselect(string sql)
        {
            string dbPath = AppDomain.CurrentDomain.BaseDirectory + "database.db";//指定数据库路径  

            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))//创建连接  
            {
                conn.Open();//打开连接  
                using (SQLiteTransaction tran = conn.BeginTransaction())//实例化一个事务  
                {
                    SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
                    SQLiteDataAdapter EmployeeAdapter = new SQLiteDataAdapter();
                    EmployeeAdapter.SelectCommand = cmdQ;
                    DataSet rs = new DataSet();
                    EmployeeAdapter.Fill(rs);

                    //SQLiteDataReader reader = cmdQ.ExecuteReader();
                    //tran.Commit();//提交 
                    //conn.Close();
                    return rs;
                }
            }
        }
        /// <summary>
        /// 数据库插入
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Pwd"></param>
        /// <param name="Group"></param>
        /// <param name="Limit"></param>
        static public void InsertUser(string Name, string Pwd, int Group, int Limit)
        {
            string dbPath = AppDomain.CurrentDomain.BaseDirectory + "database.db";//指定数据库路径 

            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))//创建连接  
            {
                conn.Open();//打开连接  
                using (SQLiteTransaction tran = conn.BeginTransaction())//实例化一个事务  
                {
                        SQLiteCommand cmd = new SQLiteCommand(conn);//实例化SQL命令  
                        cmd.Transaction = tran;
                        cmd.CommandText = "INSERT INTO [UserData] ([Name], [Pwd], [Group], [Limit]) VALUES (@Name,@Pwd,@Group,@Limit)";//设置带参SQL语句  
                        cmd.Parameters.AddRange(new[] {//添加参数  
                           new SQLiteParameter("@Name", Name),  
                           new SQLiteParameter("@Pwd", Pwd),  
                           new SQLiteParameter("@Group", Group),
                           new SQLiteParameter("@Limit", Limit)  
                       });
                        cmd.ExecuteNonQuery();//执行查询  
                        tran.Commit();//提交  
                }
            }
        }
        /// <summary>
        /// 数据库删除
        /// </summary>
        /// <param name="ID"></param>
        static public void DeleteUser(int ID)
        {
            string dbPath = AppDomain.CurrentDomain.BaseDirectory + "database.db";//指定数据库路径 

            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))//创建连接  
            {
                conn.Open();//打开连接  
                using (SQLiteTransaction tran = conn.BeginTransaction())//实例化一个事务  
                {
                    string sql = "DELETE FROM UserData WHERE ID = @ID";
                    SQLiteCommand command = new SQLiteCommand(sql, conn);
                    conn.Close();
                    tran.Commit();//提交  
                }
            }
        }
        /// <summary>
        /// 数据库修改
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Pwd"></param>
        static public void UpdataPwd(int ID, string Pwd)
        {
            string dbPath = AppDomain.CurrentDomain.BaseDirectory + "database.db";//指定数据库路径 

            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))//创建连接  
            {
                conn.Open();//打开连接  
                using (SQLiteTransaction tran = conn.BeginTransaction())//实例化一个事务  
                {
                    SQLiteCommand cmd = new SQLiteCommand(conn);//实例化SQL命令  
                    cmd.Transaction = tran;
                    cmd.CommandText = "UPDATE  [UserData] SET [Pwd]=@Pwd  WHERE [ID]=@ID";//设置带参SQL语句  
                    cmd.Parameters.AddRange(new[] {//添加参数  
                           new SQLiteParameter("@Pwd", Pwd),  
                           new SQLiteParameter("@ID", ID),  
                       });
                    cmd.ExecuteNonQuery();//执行查询  
                    tran.Commit();//提交  
                }
            }
        }
        /// <summary>
        /// 数据库修改
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Limit"></param>
        static public void UpdataLimit(int ID, int Limit)
        {
            string dbPath = AppDomain.CurrentDomain.BaseDirectory + "database.db";//指定数据库路径 

            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))//创建连接  
            {
                conn.Open();//打开连接  
                using (SQLiteTransaction tran = conn.BeginTransaction())//实例化一个事务  
                {
                    SQLiteCommand cmd = new SQLiteCommand(conn);//实例化SQL命令  
                    cmd.Transaction = tran;
                    cmd.CommandText = "UPDATE  [UserData] SET [Limit]=" + Limit + "  WHERE [ID]=" + ID;//设置带参SQL语句  
                    cmd.ExecuteNonQuery();//执行查询  
                    tran.Commit();//提交  
                }
            }
        }

    }
}

3.在客户端项目中添加服务引用，点击发现，添加

4.在客户端就可以进行相关的数据库操作

//得到数据中的信息
ServiceReference1.ServiceSQLiteClient myClient = new ServiceReference1.ServiceSQLiteClient();
myClient.getUserCompleted += new EventHandler<ServiceReference1.getUserCompletedEventArgs>(myGetUserCompleted);
myClient.getUserAsync();

void myGetUserCompleted(object sender, ServiceReference1.getUserCompletedEventArgs e)
        {
            System.Collections.ObjectModel.ObservableCollection<ServiceReference1.ServiceSQLiteClassEmployee> users = e.Result;
            for (int i = 0; i < users.Count; i++)
            {
                UserData.Add(new Users { Id = users[i].ID,Name=users[i].Name,Pwd=users[i].Pwd,Group=users[i].Group,Limit=users[i].Limit,
                                         ProLimit0 = users[i].ProLimit0,
                                         ProLimit1 = users[i].ProLimit1,
                                         ProLimit2 = users[i].ProLimit2,
                                         ProLimit3 = users[i].ProLimit3,
                                         ProLimit4 = users[i].ProLimit4,
                                         ProLimit5 = users[i].ProLimit5
                });
            }
        }

 //往数据库中写入数据
ServiceReference1.ServiceSQLiteClient myClient = new ServiceReference1.ServiceSQLiteClient();
            myClient.InsertProTestAsync(test.Pro_Id,test.Pro_Vision,test.Pro_TestValue, test.Pro_TestState);

3.传入大数据时需要进行web.config 配置

<configuration>
    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
    </system.web>

  <appSettings>
    <add key="DBappSetting" value="Data Source=(localdb)/Projects;Initial Catalog=SLRiaTest;Integrated Security=True;Connect Timeout=100;Encrypt=False;TrustServerCertificate=False"/>
  </appSettings>
  <connectionStrings>
    <add name="myConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\User\Documents\database.mdf;Integrated Security=True;Connect Timeout=30"/>
  </connectionStrings>

    <system.serviceModel>
        <behaviors>
            <serviceBehaviors>
                <behavior name="">
                    <serviceMetadata httpGetEnabled="true" httpsGetEnabled="true" />
                    <serviceDebug includeExceptionDetailInFaults="false" />
                  <dataContractSerializer maxItemsInObjectGraph="2147483647"/>
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <bindings>
            <customBinding>
                <binding name="Projects_Manager.Web.ServiceWCF.customBinding0">
                    <binaryMessageEncoding />
                    <httpTransport  maxReceivedMessageSize="2147483647"/>
                </binding>
                <binding name="Projects_Manager.Web.ServiceSQLite.customBinding0">
                    <binaryMessageEncoding />
                    <httpTransport  maxReceivedMessageSize="2147483647"/>
                </binding>
            </customBinding>
        <basicHttpBinding>
    <binding name="basicBinding" maxReceivedMessageSize="2147483647"></binding>
  </basicHttpBinding>
        </bindings>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="true"
            multipleSiteBindingsEnabled="true" />
        <services>
            <service name="Projects_Manager.Web.ServiceSQLite">
                <endpoint address="" binding="customBinding" bindingConfiguration="Projects_Manager.Web.ServiceSQLite.customBinding0"
                    contract="Projects_Manager.Web.ServiceSQLite" />
                <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
            </service>
        </services>
    </system.serviceModel>
</configuration>