sqlite

IList<UserInfo> aa=   DB.Select().Top("5").From(UserInfo.Schema).ExecuteTypedList<UserInfo>();

 

 

 

//返回 UserInfoCollection

UserInfoCollection users = new UserInfoCollection().Where(UserInfo.Columns.Password, SubSonic.Comparison.Equals, "admin").Load();

 

//返回单个对象

 UserInfo user = new UserInfo("password", "demo");

 

//返回单个对象

 UserInfo aa = DB.Select().Top("5").From(UserInfo.Schema).ExecuteSingle<UserInfo>();

 

 UserInfo aa = DB.Select().Top("5").From<UserInfo>().ExecuteSingle<UserInfo>();

 

 

 

//删除记录

 UserInfo.Delete(UserInfo.Columns.Username, "demo");

 

 

 

//更新

               DAL.TaxItem.Query().WHERE("xMCode=" + xMCode + "").AddUpdateSetting("ValueItem", ValueItem).Execute();

 

 

 

 

//插入记录

   UserInfo.Insert("aa", "bb", null, null, null, null, null, "abc", "abc");

 

Object q = DAL.S35.Query().WHERE("ProClass=" + ysgxmodel.Flag).GetSum(ysgxmodel.Zdmc);

 

 

 

//链接查询，From的表，==InnerJoin的第二张表                                                                     位置                                                                         位置

            IDataReader res = DB.Select("address" ,"trade_code", "Name","code" ).From<CompanyTrade>().InnerJoin(CompanyInformation.TradeCodeColumn, CompanyTrade.IdColumn).ExecuteReader();

            DataTable dt = new DataTable();

            dt.Load(res);

            this.dataGridView1.DataSource = dt;

subnoic直接执行sql语句

new InlineQuery().ExecuteReader(sql)

把有or的表达式单独定在InnerWhere里头

 

//tab为表名,如果存在返回true,否则返 回false

            int i  = Convert.ToInt16(db.ExecuteScalar("SELECT Count(*) AS RTab FROM MSysObjects WHERE (MSysObjects.Name) Like '"+tab+"'"));

            if (i == 0)

            {

                return false;

            }

            else

            {

                return true;

            }

        }

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Data.SQLite;

namespace DbConnect

{

    public class DBConnection

    {

 

        public static readonly string ConnectionString = @"Data Source=Tax.s3db;";

        SQLiteConnection con = new SQLiteConnection(ConnectionString);

        static  SQLiteCommand cmd = new SQLiteCommand();

 

        public string GetOleConnStr(string path)

        {

            return "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";

        }

        public object ExecuteNonQuery(string command)

        {

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))

            {

                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand(command, conn);

                object val = cmd.ExecuteNonQuery();

                return val;

            }

        }

 

        public object ExecuteScalar(string command)

        {

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))

            {

                conn.Open();

 

                SQLiteCommand cmd = new SQLiteCommand(command, conn);

                object val = cmd.ExecuteScalar();

                return val;

            }

        }

        public DataSet GetDataSet(string commandstring)

        {

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))

            {

                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand(commandstring, conn);

                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

                DataSet ds = new DataSet();

                da.Fill(ds);

                return ds;

            }

        }

 

        /// <summary>

        /// 打开一次连接然后执行完所有sql语句后关闭连接

        /// </summary>

        public object ExecuteNonQuery1(string cmdText)

        {

            cmd.Connection = con;

            cmd.CommandText = cmdText;

            object val = cmd.ExecuteNonQuery();

            return val;

        }

        /// <summary>

        /// 打开连接

        /// </summary>

        public void OpenConnection()

        {

            //如果关闭的话

            if (con.State == ConnectionState.Closed)

            {

                con.Open();

            }

        }

        /// <summary>

        /// 关闭连接

        /// </summary>

        public void CloseConnection()

        {

            //如果打开的话

            if (con.State == ConnectionState.Open)

            {

                con.Close();

            }

        }

 

        //

        public DataTable GetDataTable(string command)

        {

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))

            {

                conn.Open();

                SQLiteDataAdapter da = new SQLiteDataAdapter(command, conn);

                DataSet ds = new DataSet();

                da.Fill(ds, "temp");

                conn.Close();

                return ds.Tables["temp"];

            }

        }

 

    }