using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using SqliteDotNet;
using System.Data;
using System.Data.SqlTypes;
namespace SqliteGUI
{
/// <summary>
/// Summary description for TableEditForm.
/// </summary>
public class TableEditForm : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
private SqliteDb database;
private SqliteVm vm = null;
private DataTable dataTable = null;
public TableEditForm()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if ((null != database) && (!Form1.IsReadOnly(database.GetName())))
{
WriteBack();
}
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.Dock = System.Windows.Forms.DockStyle.Fill;
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(0, 0);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(492, 473);
this.dataGrid1.TabIndex = 0;
//
// TableEditForm
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(492, 473);
this.Controls.Add(this.dataGrid1);
this.Name = "TableEditForm";
this.Text = "TableEditForm";
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
public void LoadTable(SqliteDb newDatabase, string tableName)
{
this.Name = tableName;
this.SetDatabase(newDatabase);
DataGridTableStyle tableStyle = new DataGridTableStyle();
tableStyle.MappingName = tableName;
if (tableName == "TestCase")
{
tableStyle.PreferredRowHeight *= 3;
}
else if (tableName == "ApiTestResults" || tableName == "HttpTestResults")
{
tableStyle.PreferredRowHeight *= 2;
}
int gridWidth = 0;
vm = new SqliteVm(database, true);
if (vm != null)
{
dataTable = new DataTable(tableName);
string sql = String.Format("select rowid, * from {0} ;", tableName);
int sqErr = vm.Execute(sql);
int nCols = vm.NumCols();
for (int i = 0; i<nCols; i++)
{
string colName = vm.ColumnName(i);
string colType = vm.ColumnType(colName);
DataGridTextBoxColumn colStyle = new DataGridTextBoxColumn();
colStyle.MappingName = colName;
colStyle.HeaderText = colName;
switch (colType.ToUpper())
{
case "INTEGER":
dataTable.Columns.Add(colName, typeof(int));
colStyle.Width = 60;
break;
case "REAL":
dataTable.Columns.Add(colName, typeof(double));
colStyle.Width = 60;
break;
case "TEXT":
dataTable.Columns.Add(colName, typeof(string));
colStyle.TextBox.Multiline = true;
if (colName == "ParamName" || colName == "ContentType" || colName == "TestType" || colName == "Prerequisite")
{
colStyle.Width = 100;
}
else if (colName == "ParamValue")
{
colStyle.Width = 275;
}
else
{
colStyle.Width = 200;
}
break;
case "BLOB":
DataColumn col = new DataColumn(colName, typeof(SqlBinary));
col.ReadOnly = true;
dataTable.Columns.Add(col);
colStyle.Width = 150;
break;
default:
colStyle = null;
break;
}
if (colStyle != null)
{
tableStyle.GridColumnStyles.Add(colStyle);
gridWidth += colStyle.Width;
}
}
//rowid should be readonly
dataTable.Columns[0].ReadOnly = true;
// update grid with new cols and width
dataGrid1.TableStyles.Add(tableStyle);
dataGrid1.Width = gridWidth;
while (sqErr == Sqlite.Row)
{
object [] cols = new object[nCols];
for (int i=0; i<nCols; i++)
{
string colName = vm.ColumnName(i);
string colType = vm.ColumnType(colName);
switch (colType.ToUpper())
{
case "INTEGER":
int colInt = vm.GetInt(colName);
cols[i] = colInt;
break;
case "REAL":
double colDbl = vm.GetDouble(colName);
cols[i] = colDbl;
break;
case "TEXT":
string colText = vm.GetString(colName);
cols[i] = colText;
break;
case "BLOB":
byte[] bytes = vm.GetBlob(colName).Read();
if (bytes != null)
{
SqlBinary binBlob = new SqlBinary(bytes);
cols[i] = binBlob;
}
else
{
cols[i] = null;
}
break;
default:
break;
}
}
dataTable.Rows.Add(cols);
sqErr = vm.NextRow();
}
}
vm.SqlFinalize();
vm = null;
dataTable.AcceptChanges();
dataGrid1.DataSource = dataTable;
this.Width = gridWidth+75;
}
public void WriteBack()
{
this.vm = new SqliteVm(this.database, true);
for (int i=0; i<dataTable.Rows.Count; i++)
{
int sqErr = Sqlite.Ok;
DataRow row = dataTable.Rows[i];
switch (row.RowState)
{
case DataRowState.Added:
{
string insertStmt = String.Format("INSERT INTO {0} (",dataTable.TableName);
for (int j=1; j<dataTable.Columns.Count; j++)
{
// ignore binary for now...
if (dataTable.Columns[j].DataType == typeof(SqlBinary))
{
continue;
}
if (j > 1) { insertStmt += ",";}
insertStmt += dataTable.Columns[j].ColumnName;
}
insertStmt += ") VALUES (";
for (int j=1; j<dataTable.Columns.Count; j++)
{
// ignore binary for now...
if (dataTable.Columns[j].DataType == typeof(SqlBinary))
{
continue;
}
if (j > 1) { insertStmt += ",";}
insertStmt += String.Format("\"{0}\"", row[j].ToString());
}
insertStmt += ");";
sqErr = vm.Execute(insertStmt);
}
break;
case DataRowState.Deleted:
{
// hack... rejectchanges to get the row back...
row.RejectChanges();
object obj = row[0];
if (obj != null)
{
string rowId = obj.ToString();
if (rowId != null && rowId.Length > 0)
{
string deleteStmt =
String.Format("DELETE FROM {0} WHERE rowid = {1};",
dataTable.TableName, rowId);
sqErr = vm.Execute(deleteStmt);
}
}
row.Delete();
}
break;
case DataRowState.Modified:
{
int nCols = dataTable.Columns.Count;
string updateStmt = String.Format("UPDATE {0} SET ", dataTable.TableName);
for (int j=1; j<nCols; j++)
{
// ignore binary for now...
if (dataTable.Columns[j].DataType == typeof(SqlBinary))
{
continue;
}
if (j > 1) { updateStmt += ",";}
updateStmt += String.Format("{0}=\"{1}\"",
dataTable.Columns[j].ColumnName,
row[j].ToString());
}
updateStmt += String.Format(" WHERE rowid = {0};", row[0].ToString());
sqErr = vm.Execute(updateStmt);
}
break;
case DataRowState.Unchanged:
{
// do nothing
;
;
}
break;
default:
break;
}
}
vm.SqlFinalize();
vm = null;
}
public void SetDatabase(SqliteDb newDatabase)
{
this.database = newDatabase;
}
}
}