1、sqlite3辅助类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;
//SQLite辅助类
namespace Utils
{
public class SqlLiteHelper
{
public static string ConnSqlLiteDbPath = string.Empty;
public static string ConnString
{
get
{
return string.Format(@"Data Source={0}", ConnSqlLiteDbPath);
}
}
// 取datatable
public static DataTable GetDataTable(out string sError,string sSQL)
{
DataTable dt = null;
sError = string.Empty;
try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = sSQL;
cmd.Connection = conn;
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
}
catch (Exception ex)
{
sError = ex.Message;
MessageBox.Show(sError, "数据库连接异常");
}
return dt;
}
// 取某个单一的元素
public static object GetSingle(out string sError, string sSQL)
{
DataTable dt = GetDataTable(out sError, sSQL);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
return null;
}
// 取最大的ID
public static Int32 GetMaxID(out string sError, string sKeyField,string sTableName)
{
DataTable dt = GetDataTable(out sError, "select ifnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]");
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
return 0;
}
// 执行insert,update,delete 动作，也可以使用事务
public static bool UpdateData(out string sError, string sSQL,bool bUseTransaction=false)
{
int iResult = 0;
sError = string.Empty;
if (!bUseTransaction)
{
try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
SQLiteCommand comm = new SQLiteCommand(conn);
comm.CommandText = sSQL;
iResult = comm.ExecuteNonQuery();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
}
}
else // 使用事务
{
DbTransaction trans =null;
try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
trans = conn.BeginTransaction();
SQLiteCommand comm = new SQLiteCommand(conn);
comm.CommandText = sSQL;
iResult = comm.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
trans.Rollback();
}
}
return iResult >0;
}
}
}
数据源sqlite3数据库Data目录，添加文件Store.db数据库；生成操作：内容；是否输出到目录：总是
主窗口添加DataGrid控件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Utils;
using System.IO;
using System.Data;
namespace WpfApplication1
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
InitDB();
}
private void InitDB()
{
string sql = "select * from Products";
DataTable dt = GetDataTable(@"Data\Store.db", sql);
this.dataGrid1.ItemsSource = dt.DefaultView;
}
private void button1_Click(object sender, RoutedEventArgs e)
{
}
private DataTable GetDataTable(string sqldbpath, string sql)
{
SqlLiteHelper.ConnSqlLiteDbPath = sqldbpath;
string sError = "";
return SqlLiteHelper.GetDataTable(out sError, sql);
}
}
}
XAML代码
<Window x:Class="WpfApplication1.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid>
<Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="34,21,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" />
<DataGrid AutoGenerateColumns="False" Height="210" HorizontalAlignment="Left" Margin="0,89,0,0" Name="dataGrid1" VerticalAlignment="Top" Width="437" Grid.Column="5" Grid.Row="5">
<DataGrid.Columns >
<DataGridTextColumn Header="产品ID" Binding="{Binding Path=ProductID}"/>
<DataGridTextColumn Header="分类ID" Binding="{Binding Path=CategoryID}"/>
<DataGridTextColumn Header="ModelName" Binding="{Binding Path=ModelName}" />
<DataGridTextColumn Header="ModelNumber" Binding="{Binding Path=ModelNumber}"></DataGridTextColumn>
<DataGridTextColumn Header="ProductImage" Binding="{Binding Path=ProductImage}"/>
<DataGridTextColumn Header="单价" Binding="{Binding Path=UnitCost}"/>
<DataGridTextColumn Header="描述" Binding="{Binding Path=Description}" />
</DataGrid.Columns>
</DataGrid>
</Grid>
</Window>
结果：