使用.NET（c#）操作SQLLITE_c#如何使用sqllate

// 创建一个数据库文件，保存在当前目录下HyData文件夹下
// CreateDB("HyData.db3");
private   void  CreateDB( string  dbName)
{
     string  databaseFileName  =  System.Environment.CurrentDirectory  +   @" /HyData/ "   +  dbName;

    SQLiteConnection.CreateFile(databaseFileName);
}

1.2  数据库连接字符串

// 执行Sql语句
// 创建一个表：  ExecuteSql("create table HyTest(TestID TEXT)");
// 插入些数据：  ExecuteSql("insert into HyTest(TestID) values('1001')");
private   void  ExecuteSql( string  sqlStr)
{
     using  (DbConnection conn  =   new  SQLiteConnection(connStr))
    {
        conn.Open();
        DbCommand comm  =  conn.CreateCommand();
        comm.CommandText  =  sqlStr;
        comm.CommandType  =  CommandType.Text;
        comm.ExecuteNonQuery();
    }
}

// 执行查询
// ExecQuery("select * from HyTest");
private   void  ExecQuery( string  sqlStr)
{
     using  (DbConnection conn  =   new  SQLiteConnection(connStr))
    {
        conn.Open();
        DbCommand comm  =  conn.CreateCommand();
        comm.CommandText  =  sqlStr;
        comm.CommandType  =  CommandType.Text;

         using  (IDataReader reader  =  comm.ExecuteReader())
        {
             while  (reader.Read())
            {
                MessageBox.Show(reader[ 0 ].ToString());
            }
        }
    }
}

// 执行查询返回DataSet
private  DataSet ExecDataSet( string  sqlStr)
{
     using  (SQLiteConnection conn  =   new  SQLiteConnection(connStr))
    {
        conn.Open();
        SQLiteCommand cmd  =  conn.CreateCommand();
        cmd.CommandText  =  sqlStr;
        cmd.CommandType  =  CommandType.Text;

        SQLiteDataAdapter da  =   new  SQLiteDataAdapter(cmd);
        DataSet ds  =   new  DataSet();
        da.Fill(ds);

         return  ds;
    }
}

本文示例项目源码：HySqlite.rar http://revit.5d6d.net/thread-799-1-1.html

2.Sqlite相关工具

2.1  Sqlite数据库可以到www.sqlite.org下载，非常小
或sqlite-shell-win32-x86-3070600.zip
http://revit.5d6d.net/thread-800-1-1.html

2.2  C#操作Sqlite的官方示例代码，一时忘了url
或http://revit.5d6d.net/thread-801-1-1.html包括
SQLite-1.0.66.0-source.zip
SQLite-1.0.66.0-binaries.zip
debug.rar

2.3  Sqlite两个界面工具

SQLiteExpertSetup.exe
http://revit.5d6d.net/thread-802-1-1.html这个比较好用，破解版
SQLite Database Browser.exe
http://revit.5d6d.net/thread-803-1-1.html这个据说用在手机上

2.4  小巧的界面工具SqliteSpy(感谢http://www.cnblogs.com/qq419524837/提供)

下载：SQLiteSpy或http://revit.5d6d.net/thread-808-1-1.html

//插入数据的另一种方法

//先创建表"CREATE TABLE user11(id int(4),name varchar(20),maxlevel int(4),east varchar(20))"

private void button5_Click(object sender, EventArgs e)
        {
            //string sql = "INSERT INTO user2 VALUES(1,'a','b',1.0)";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into user11(");
            strSql.Append("id,name,maxlevel,east)");//
            strSql.Append(" values (");
            strSql.Append("@ID,@name,@maxlevel,@east)");//

            //
            SQLiteParameter[] parameters = {
           new SQLiteParameter("@ID", TypeAffinity.Text),
           new SQLiteParameter("@name", TypeAffinity.Text),
                                            new SQLiteParameter("@maxlevel", TypeAffinity.Int64),
                                            new SQLiteParameter("@east", TypeAffinity.Double)
                                           };
            parameters[0].Value = 123;
            parameters[1].Value = "name";
            parameters[2].Value = 111;
            double ddd = 1.0;
            parameters[3].Value = ddd; //ddd.ToString();
            ExecuteSql(strSql.ToString(), parameters);

            //cmd.CommandText = sql;
            //cmd.Connection = conn;
            //cmd.ExecuteNonQuery();
            MessageBox.Show("添加数据成功");
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=e:\\test1.db;Version=3"))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            //if (cmdParms != null)  //用此方法出错：“输入字符串的格式不正确”（在执行cmd.ExecuteNonQuery();时）
            //{
            //    foreach (SQLiteParameter parm in cmdParms)
            //        cmd.Parameters.Add(parm);
            //}
            cmd.Parameters.AddWithValue("@ID", cmdParms[0].Value);
            cmd.Parameters.AddWithValue("@name", cmdParms[1].Value);
            cmd.Parameters.AddWithValue("@maxlevel", cmdParms[2].Value);
            cmd.Parameters.AddWithValue("@east", cmdParms[3].Value);
        }

sql="create table tablename (m_id integer identity(1,1) primary key,编号 varchar(10),日期 date, 时间 date)"
Conn.ExeCute(SQL)
怎样让tablename使用变量
tablename =Request.QueryString("tname")
sql="create table " & tablename & " (m_id integer identity(1,1) primary key,编号 varchar(10),日期 date, 时间 date)"

CREATE TABLE Persons(Id_P int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))

c#与SQL类型转换