I'm trying to create a handler that first of all creates a "mdf" file, which I later connect to. Then I can add tables from a type, that gets all its properties and values. So a class with 2 properties, example:
class Person { String Name { get; set; } float Age { get; set; }
will become:
"CREATE TABLE Person (Name text, Age float)"
and then I add a SQLDataAdapter that gets all it properties and creates functions for Fill, Update, Insert, Delete with one of the Properties as PrimaryKey for the Class/ObjectType.
I'm stuck and I have no idea if I'm thinking the right way, I can create a mdf file but then later when I delete the file data is still somehow present (or something's wrong).
If there's anyone who could give me a hint as to how to think or explain what I'm missing or need to think about.
DataAccess creates a dataset, whenever I add a Table I create a table in the Dataset with the same properties (columns), and I add a SqlDataAdapter for that specific type with methods to Fill/Update/Delete/Insert etc...
And DataAccess method Fill calls the adapter with the DataTable that corresponds to it, but it doesn't work.
Here's my code, if someone could try it out and give me some hints or tell me what I'm thinking wrong:
DataAccess Class:
public class DataAccess
{
protected String CONNECTION_STRING = "";
protected DataSet DBSet;
protected List<object> DBAdapters;
protected String DatabaseName;
public DataAccess(String dbName)
{
if (!File.Exists(Directory.GetCurrentDirectory() + "\\" + dbName + ".mdf"))
{
CreateDatabaseFile(Directory.GetCurrentDirectory() + "\\" + dbName + ".mdf");
}
CONNECTION_STRING = "Data Source=.\\SQLEXPRESS;Initial Catalog=tempdb; Integrated Security=true;User Instance=True;";
DatabaseName = dbName;
DBSet = new DataSet(dbName);
DBAdapters = new List<object>();
}
public DataTable Fill<T>()
{
for (int i = 0; i < DBAdapters.Count; i++)
{
try
{
if (((DatabaseAdapter<T>)DBAdapters[i]).ObjectType == typeof(T))
{
for (int t=0; t < DBSet.Tables.Count; t++)
{
if (DBSet.Tables[t].TableName == typeof(T).Name)
{
((DatabaseAdapter<T>)DBAdapters[i]).Fill(DBSet.Tables[t]);
return (DBSet.Tables[t]);
}
}
}
}
catch { }
}
throw new Exception("Not found");
}
public void Insert<T>(T obj)
{
object[] values;
for (int i = 0; i < DBAdapters.Count; i++)
{
try
{
MessageBox.Show(((DatabaseAdapter<T>)DBAdapters[i]).ObjectType + " == " + typeof(T));
if (((DatabaseAdapter<T>)DBAdapters[i]).ObjectType == typeof(T))
{
PropertyInfo[] pi = typeof(T).GetProperties();
values = new object[pi.Length];
for (int p = 0; p < pi.Length; p++)
{
values[p] = obj.GetType().GetProperty(pi[i].Name).GetValue(obj, null);
}
((DatabaseAdapter<T>)DBAdapters[i]).Insert(values);
}
}
catch { throw new Exception("Type not found"); }
}
throw new Exception("Not found");
}
public int NumOfRows<T>()
{
int count = 0;
if (TableExists(typeof(T).Name))
{
using (var connection = new SqlConnection(CONNECTION_STRING))
{
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM " + typeof(T).Name;
var reader = command.ExecuteReader();
while (reader.Read())
{
count++;
}
}
}
}
return count;
}
public bool TableExists(String tableName)
{
bool exists = false;
using (var connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i) == "TABLE_NAME")
{
if (reader.GetValue(i).ToString() == tableName)
{
exists = true;
}
}
}
}
}
}
}
return exists;
}
private bool Query(String query)
{
using (var connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = query;
try
{
command.ExecuteNonQuery();
}
catch { return false; }
}
}
return true;
}
public void CreateTable<T>(String primaryKey, bool deleteIfExists = true)
{
if (TableExists(typeof(T).Name))
{
MessageBox.Show("HEJ");
if (deleteIfExists)
{
Query("DROP TABLE " + typeof(T).Name);
}
else
{
return;
}
}
using (var connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using (var command = connection.CreateCommand())
{
String fields = "";
PropertyInfo[] pi = typeof(T).GetProperties();
for (int i = 0; i < pi.Length; i++)
{
fields += pi[i].Name + " " + GetDBType(pi[i].PropertyType).ToString() + ", ";
}
command.CommandText = String.Format("CREATE TABLE {0} ({1})", typeof(T).Name, fields.Substring(0, fields.Length - 2));
command.ExecuteNonQuery();
try
{
DBSet.Tables.Add(CreateDataTable<T>());
DBAdapters.Add(CreateDataAdapter<T>(primaryKey));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
connection.Close();
}
}
private void CreateDatabaseFile(String filename)
{
String dbName = System.IO.Path.GetFileNameWithoutExtension(filename);
using (var connection = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=tempdb; Integrated Security=true;User Instance=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
try
{
command.CommandText = "DROP DATABASE " + dbName;
command.ExecuteNonQuery();
}
catch {}
command.CommandText = "CREATE DATABASE " + dbName + " ON PRIMARY (NAME=" + dbName + ", FILENAME='" + filename + "')";
command.ExecuteNonQuery();
command.CommandText = "EXEC sp_detach_db '" + dbName + "', 'true'";
command.ExecuteNonQuery();
}
}
}
private DatabaseAdapter<T> CreateDataAdapter<T>(String primaryKey)
{
return new DatabaseAdapter<T>(typeof(T), CONNECTION_STRING, primaryKey, typeof(T).Name, DBSet.DataSetName);
}
private DataTable CreateDataTable<T>()
{
DataTable dt = new DataTable();
PropertyInfo[] properties = typeof(T).GetProperties();
if (properties.Length == 0) { throw new Exception("Type has no properties"); }
for (int i = 0; i < properties.Length; i++)
{
try
{
dt.Columns.Add(new DataColumn(properties[i].Name, properties[i].PropertyType, null, MappingType.Attribute));
}
catch { throw new Exception("Cannot add columns"); }
}
return dt;
}
public DataTable GetTable<T>()
{
for (int i = 0; i < DBSet.Tables.Count; i++)
{
if (DBSet.Tables[i].TableName == typeof(T).Name)
{
return DBSet.Tables[i];
}
}
throw new Exception("Table not found");
}
public static SqlDbType GetDBType(Type t)
{
if (t == typeof(bool))
{
return SqlDbType.Bit;
}
else if (t == typeof(String))
{
return SqlDbType.Text;
}
else if (t == typeof(Int16))
{
return SqlDbType.SmallInt;
}
else if (t == typeof(Int32))
{
return SqlDbType.Int;
}
else if (t == typeof(Int64))
{
return SqlDbType.BigInt;
}
else if (t == typeof(byte))
{
return SqlDbType.TinyInt;
}
else if (t == typeof(long))
{
return SqlDbType.Money;
}
else if (t == typeof(char))
{
return SqlDbType.Char;
}
else if (t == typeof(Image))
{
return SqlDbType.VarBinary;
}
else if (t == typeof(Byte[]))
{
return SqlDbType.VarBinary;
}
else if (t == typeof(sbyte[]))
{
return SqlDbType.VarBinary;
}
else if (t == typeof(DateTime))
{
return SqlDbType.DateTime;
}
else if (t == typeof(float) || t == typeof(double))
{
return SqlDbType.Float;
}
else if (t == typeof(Guid))
{
return SqlDbType.UniqueIdentifier;
}
else if (t == typeof(Decimal))
{
return SqlDbType.Decimal;
}
return SqlDbType.Text;
}
}
my own Adapter class:
public class DatabaseAdapter<T> : global::System.ComponentModel.Component
{
#region FIELDS
private bool _clearBeforeFill = true;
private global::System.Data.SqlServerCe.SqlCeDataAdapter _adapter;
private global::System.Data.SqlServerCe.SqlCeConnection _connection;
private global::System.Data.SqlServerCe.SqlCeTransaction _transaction;
private global::System.Data.SqlServerCe.SqlCeCommand[] _commandCollection;
private string ConnectionString;
private string TableName;
private string PrimaryKey;
private string DataSetTable;
private Type SourceType;
private PropertyInfo[] classProperties;
#endregion
public DatabaseAdapter(Type sourceType, String connectionString, String primaryKey, String tableName, String dataSetTable)
{
ConnectionString = connectionString;
TableName = tableName;
DataSetTable = dataSetTable;
SourceType = sourceType;
PrimaryKey = primaryKey;
classProperties = sourceType.GetProperties();
Init();
}
public void Init()
{
#region INITIALIZE CONNECTION
this._connection = new System.Data.SqlServerCe.SqlCeConnection();
this._connection.ConnectionString = ConnectionString;
#endregion
#region INITIALIZE ADAPTER
this._adapter = new System.Data.SqlServerCe.SqlCeDataAdapter();
global::System.Data.Common.DataTableMapping tableMapping = new global::System.Data.Common.DataTableMapping();
#endregion
#region SET TABLE SOURCE
tableMapping.SourceTable = TableName;
tableMapping.DataSetTable = DataSetTable;
#endregion
#region SET PRIMARY KEY / GET PRIMARY KEY TYPE
SqlDbType primaryType = SqlDbType.Text;
for (int i = 0; i < classProperties.Length; i++)
{
try
{
tableMapping.ColumnMappings.Add(classProperties[i].Name, classProperties[i].Name);
if (PrimaryKey == classProperties[i].Name)
{
primaryType = DataAccess.GetDBType(classProperties[i].PropertyType);
}
}
catch { }
}
this._adapter.TableMappings.Add(tableMapping);
#endregion
#region ADD DELETE COMMAND
this._adapter.DeleteCommand = new System.Data.SqlServerCe.SqlCeCommand();
this._adapter.DeleteCommand.Connection = this.Connection;
this._adapter.DeleteCommand.CommandText = "DELETE FROM [" + TableName + "] WHERE (([" + PrimaryKey + "] = @p1))";
this._adapter.DeleteCommand.Parameters.Add(new global::System.Data.SqlServerCe.SqlCeParameter("@p1", primaryType, 0, global::System.Data.ParameterDirection.Input, false, 0, 0, PrimaryKey, global::System.Data.DataRowVersion.Original, null));
#endregion
#region ADD INSERT / UPDATE COMMANDS
String ifields = "";
String ivalues = "";
String ufields = "";
int count = 1;
this._adapter.InsertCommand = new System.Data.SqlServerCe.SqlCeCommand();
this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandType = CommandType.Text;
this._adapter.UpdateCommand = new System.Data.SqlServerCe.SqlCeCommand();
this._adapter.UpdateCommand.Connection = this.Connection;
this._adapter.UpdateCommand.CommandType = CommandType.Text;
for (int i = 0; i < classProperties.Length; i++)
{
try
{
ifields += "[" + classProperties[i].Name + "], ";
ivalues += "@p" + count + ", ";
ufields += "[" + classProperties[i].Name + "] = @p" + count + ", ";
this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlServerCe.SqlCeParameter("@p" + count, DataAccess.GetDBType(classProperties[i].PropertyType), 0, global::System.Data.ParameterDirection.Input, true, 0, 0, classProperties[i].Name, global::System.Data.DataRowVersion.Current, null));
count++;
}
catch { }
}
ifields = ifields.Substring(0, ifields.Length - 1);
ivalues = ivalues.Substring(0, ivalues.Length - 1);
ufields = ufields.Substring(0, ufields.Length - 1);
this._adapter.InsertCommand.CommandText = "INSERT INTO [" + TableName + "] (" + ifields + ") VALUES (" + ivalues + ")";
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlServerCe.SqlCeParameter("@p" + count, primaryType, 0, global::System.Data.ParameterDirection.Input, true, 0, 0, PrimaryKey, global::System.Data.DataRowVersion.Original, null));
this._adapter.UpdateCommand.CommandText = "UPDATE [" + TableName + "] SET " + ufields + " WHERE (([" + PrimaryKey + "] = @p" + count + "))";
#endregion
#region INITIALIZE COMMAND COLLECTION / SET SELECT COMMAND
this._commandCollection = new global::System.Data.SqlServerCe.SqlCeCommand[1];
this._commandCollection[0] = new global::System.Data.SqlServerCe.SqlCeCommand();
this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandText = "SELECT " + ifields + " FROM [" + TableName + "]";
this._commandCollection[0].CommandType = global::System.Data.CommandType.Text;
#endregion
}
#region FILL / INSERT / UPDATE / DELETE
public virtual int Fill(DataTable dataTable)
{
this.Adapter.SelectCommand = this.CommandCollection[0];
if (this.ClearBeforeFill)
{
dataTable.Clear();
}
return this.Adapter.Fill(dataTable);
}
public virtual int Update(object[] parameters)
{
if (parameters.Length == this.Adapter.UpdateCommand.Parameters.Count)
{
if (parameters.Length == classProperties.Length)
{
if (CheckParameters(parameters))
{
for (int i = 0; i < parameters.Length; i++)
{
this.Adapter.UpdateCommand.Parameters[i].Value = parameters[i];
}
global::System.Data.ConnectionState previousConnection = this.Adapter.UpdateCommand.Connection.State;
if (((this.Adapter.UpdateCommand.Connection.State & global::System.Data.ConnectionState.Open) != global::System.Data.ConnectionState.Open))
{
this.Adapter.UpdateCommand.Connection.Open();
}
try
{
int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
return returnValue;
}
finally
{
if (previousConnection == global::System.Data.ConnectionState.Closed)
{
this.Adapter.UpdateCommand.Connection.Close();
}
}
}
}
}
return 0;
}
public virtual int Insert(object[] parameters)
{
if (parameters.Length == this.Adapter.InsertCommand.Parameters.Count)
{
if (parameters.Length == classProperties.Length)
{
if (CheckParameters(parameters))
{
for (int i = 0; i < parameters.Length; i++)
{
this.Adapter.InsertCommand.Parameters[i].Value = parameters[i];
}
global::System.Data.ConnectionState previousConnection = this.Adapter.InsertCommand.Connection.State;
if (((this.Adapter.InsertCommand.Connection.State & global::System.Data.ConnectionState.Open) != global::System.Data.ConnectionState.Open))
{
this.Adapter.InsertCommand.Connection.Open();
}
try
{
int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
return returnValue;
}
finally
{
if (previousConnection == global::System.Data.ConnectionState.Closed)
{
this.Adapter.InsertCommand.Connection.Close();
}
}
}
}
}
return 0;
}
public virtual int Delete(object value)
{
bool valid = false;
for (int i = 0; i < classProperties.Length; i++)
{
if (PrimaryKey == classProperties[i].Name)
{
if (classProperties[i].PropertyType == value.GetType())
{
valid = true;
}
}
}
if (valid)
{
this.Adapter.DeleteCommand.Parameters[0].Value = value;
global::System.Data.ConnectionState previousConnection = this.Adapter.InsertCommand.Connection.State;
if (((this.Adapter.DeleteCommand.Connection.State & global::System.Data.ConnectionState.Open) != global::System.Data.ConnectionState.Open))
{
this.Adapter.DeleteCommand.Connection.Open();
}
try
{
int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();
return returnValue;
}
finally
{
if ((previousConnection == global::System.Data.ConnectionState.Closed))
{
this.Adapter.DeleteCommand.Connection.Close();
}
}
}
return 0;
}
private bool CheckParameters(object[] parameters)
{
for (int i = 0; i < parameters.Length; i++)
{
if (parameters[i].GetType() != classProperties[i].PropertyType)
{
return false;
}
}
return true;
}
#endregion
#region MISC
#endregion
#region PROPERTIES
protected internal global::System.Data.SqlServerCe.SqlCeDataAdapter Adapter
{
get
{
return this._adapter;
}
}
internal global::System.Data.SqlServerCe.SqlCeConnection Connection
{
get
{
return this._connection;
}
set
{
this._connection = value;
if ((this.Adapter.InsertCommand != null))
{
this.Adapter.InsertCommand.Connection = value;
}
if ((this.Adapter.DeleteCommand != null))
{
this.Adapter.DeleteCommand.Connection = value;
}
if ((this.Adapter.UpdateCommand != null))
{
this.Adapter.UpdateCommand.Connection = value;
}
for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1))
{
if ((this.CommandCollection[i] != null))
{
((global::System.Data.SqlServerCe.SqlCeCommand)(this.CommandCollection[i])).Connection = value;
}
}
}
}
internal global::System.Data.SqlServerCe.SqlCeTransaction Transaction
{
get
{
return this._transaction;
}
set
{
this._transaction = value;
for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1))
{
this.CommandCollection[i].Transaction = this._transaction;
}
if (((this.Adapter != null)
&& (this.Adapter.DeleteCommand != null)))
{
this.Adapter.DeleteCommand.Transaction = this._transaction;
}
if (((this.Adapter != null)
&& (this.Adapter.InsertCommand != null)))
{
this.Adapter.InsertCommand.Transaction = this._transaction;
}
if (((this.Adapter != null)
&& (this.Adapter.UpdateCommand != null)))
{
this.Adapter.UpdateCommand.Transaction = this._transaction;
}
}
}
protected global::System.Data.SqlServerCe.SqlCeCommand[] CommandCollection
{
get
{
return this._commandCollection;
}
}
public bool ClearBeforeFill
{
get { return _clearBeforeFill; }
set { _clearBeforeFill = value; }
}
public Type ObjectType
{
get { return SourceType; }
}
#endregion
}
