/GrinGlobal.Client.Common/GrinGlobal.Client.Common/LocalDatabase.cs
C# | 382 lines | 342 code | 28 blank | 12 comment | 44 complexity | 849c331fc4b721cf1070fc84e545f0a5 MD5 | raw file
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data;
- using System.Data.SqlClient;
-
- namespace GRINGlobal.Client.Common
- {
- public class LocalDatabase
- {
- private string _connectionString;
- private Dictionary<string, bool> _tableExistsStatus;
-
- public LocalDatabase(string DatabaseName)
- {
- _tableExistsStatus = new Dictionary<string, bool>();
-
- System.ServiceProcess.ServiceController[] services = System.ServiceProcess.ServiceController.GetServices();
- foreach (System.ServiceProcess.ServiceController service in services)
- {
- if (service.ServiceName == "MSSQL$SQLEXPRESS" &&
- service.Status == System.ServiceProcess.ServiceControllerStatus.Running)
- {
- _connectionString = @"Data Source=localhost\SQLExpress; Integrated Security=True;";
- }
- else if (string.IsNullOrEmpty(_connectionString) && service.ServiceName == "MSSQLSERVER" &&
- service.Status == System.ServiceProcess.ServiceControllerStatus.Running)
- {
- _connectionString = @"Data Source=localhost; Integrated Security=True;";
- }
- }
-
- if (!string.IsNullOrEmpty(_connectionString) &&
- !string.IsNullOrEmpty(DatabaseName))
- {
- if (!DatabaseExists(DatabaseName))
- {
- CreateDatabase(DatabaseName);
- }
- _connectionString = _connectionString + "; Initial Catalog=" + DatabaseName;
- }
- }
-
- public string ConnectionString
- {
- get
- {
- return _connectionString;
- }
- set
- {
- _connectionString = value;
- }
- }
-
- public DataTable GetData(string SQLSelect, string[] SQLparms)
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlCommand dbCommand = new SqlCommand(SQLSelect, dbConnection);
- foreach (string parmEquation in SQLparms)
- {
- string[] parm = parmEquation.Split(new char[] { '=' }, 2);
- if (parm.Length == 2)
- {
- dbCommand.Parameters.AddWithValue(parm[0].Trim(), parm[1].TrimStart(' '));
- }
- }
- SqlDataAdapter dbDataAdapter = new SqlDataAdapter(dbCommand);
- dbDataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
- dbDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
- DataTable dt = new DataTable();
- try
- {
- dbDataAdapter.Fill(dt);
- foreach (DataColumn dc in dt.PrimaryKey)
- {
- dc.AllowDBNull = false;
- if (dc.DataType == typeof(int) ||
- dc.DataType == typeof(Int16) ||
- dc.DataType == typeof(Int32) ||
- dc.DataType == typeof(Int64))
- {
- dc.AutoIncrement = true;
- dc.AutoIncrementSeed = -1;
- dc.AutoIncrementStep = -1;
- }
- }
- }
- catch (Exception e)
- {
- //throw new Exception("Error retrieving local data in GrinGlobal.Client.Data library.", e);
- }
-
- return dt;
- }
-
- public bool SaveData(DataTable DataTable)
- {
- bool success = false;
- try
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlDataAdapter dbDataAdapter = new SqlDataAdapter("SELECT * FROM " + DataTable.TableName, dbConnection);
- SqlCommandBuilder dbCommandBuilder = new SqlCommandBuilder(dbDataAdapter);
-
- dbDataAdapter.Update(DataTable);
-
- dbCommandBuilder.Dispose();
- dbCommandBuilder = null;
- dbDataAdapter.Dispose();
- dbDataAdapter = null;
- dbConnection.Close();
- dbConnection.Dispose();
- dbConnection = null;
- success = true;
- }
- catch
- {
- success = false;
- }
- return success;
- }
-
- public void CreateTable(DataTable NewTable, bool CreateSchemaOnly, bool OverwriteTable)
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlCommand dbCommand = new SqlCommand("", dbConnection);
- SqlDataAdapter dbDataAdapter = new SqlDataAdapter("SELECT * FROM " + NewTable.TableName, dbConnection);
- SqlCommandBuilder dbCommandBuilder = new SqlCommandBuilder(dbDataAdapter);
-
- dbConnection.Open();
-
- if (TableExists(NewTable.TableName))
- {
- if (OverwriteTable)
- {
- dbCommand.CommandText = "DROP TABLE " + NewTable.TableName;
- dbCommand.ExecuteNonQuery();
- }
- else
- {
- return;
- }
- }
-
- // Build the SQL create table command...
- string createTableCommand = "CREATE TABLE " + NewTable.TableName + " (";
- foreach (DataColumn dc in NewTable.Columns)
- {
- createTableCommand += BuildColumnSQL(dc) + ",";
- }
- createTableCommand = createTableCommand.TrimEnd(',') + ")";
-
- // Add indexes for each column in the table...
- if (NewTable.TableName.EndsWith("_lookup"))
- {
- foreach (DataColumn dc in NewTable.Columns)
- {
- createTableCommand += "CREATE INDEX " + dc.ColumnName + "_index ON " + NewTable.TableName + " (" + dc.ColumnName + ");";
- }
- }
-
- dbCommand.CommandText = createTableCommand;
- dbCommand.ExecuteNonQuery();
-
- // Add the data if requested...
- if (!CreateSchemaOnly && NewTable.Rows.Count > 0)
- {
- foreach (DataRow dr in NewTable.Rows)
- {
- if(dr.RowState != DataRowState.Added) dr.SetAdded();
- }
- dbDataAdapter.Update(NewTable);
- }
-
- dbDataAdapter.Dispose();
- dbDataAdapter = null;
- dbCommand.Dispose();
- dbCommand = null;
- dbConnection.Close();
- dbConnection.Dispose();
- dbConnection = null;
- }
-
- private string BuildColumnSQL(DataColumn dc)
- {
- string columnSQL = dc.ColumnName + " ";
- string columnTypeName = dc.DataType.Name.ToString().ToUpper();
- switch (columnTypeName)
- {
- case "INT":
- case "INT32":
- case "INT64":
- columnSQL += "INT ";
- break;
- case "STRING":
- if (dc.ExtendedProperties.Contains("is_primary_key") && dc.ExtendedProperties["is_primary_key"].ToString() == "Y")
- {
- columnSQL += "NVARCHAR(255) ";
- }
- else
- {
- if (dc.ExtendedProperties.ContainsKey("table_field_data_type_string") &&
- dc.ExtendedProperties["table_field_data_type_string"].ToString().ToUpper().Trim() == "STRING" &&
- dc.ExtendedProperties.ContainsKey("gui_hint") &&
- dc.ExtendedProperties["gui_hint"].ToString().ToUpper().Trim() == "TEXT_CONTROL" &&
- dc.ExtendedProperties.ContainsKey("max_length"))
- {
- int columnLength;
- int.TryParse(dc.ExtendedProperties["max_length"].ToString(), out columnLength);
- if (columnLength > 0)
- {
- columnSQL += "NVARCHAR(" + dc.ExtendedProperties["max_length"].ToString() + ")";
- }
- else
- {
- columnSQL += "NVARCHAR(MAX)";
- }
- }
- else
- {
- // This is most likely a lookup column or code_value - so max number of characters is unknown...
- columnSQL += "NVARCHAR(440) "; // using 440 keeps the index size for each column less than the maximum of 900 bytes
- }
- }
- break;
- case "DATETIME":
- columnSQL += "DATETIME ";
- break;
- default:
- columnSQL += "NVARCHAR(255) ";
- break;
- }
- //if (dc.ExtendedProperties["is_autoincrement"].ToString() == "Y")
- //{
- // columnSQL += "IDENTITY ";
- //}
- if (dc.ExtendedProperties.Contains("is_primary_key") && dc.ExtendedProperties["is_primary_key"].ToString() == "Y")
- {
- columnSQL += "PRIMARY KEY ";
- }
- return columnSQL;
- }
-
- public bool DatabaseExists(string DatabaseName)
- {
- bool databaseExists = false;
- try
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlCommand dbCommand = new SqlCommand("SELECT count(1) from sysdatabases where name = '" + DatabaseName + "'", dbConnection);
- dbConnection.Open();
- if ((int)dbCommand.ExecuteScalar() == 1)
- {
- databaseExists = true;
- }
- else
- {
- databaseExists = false;
- }
- }
- catch
- {
- databaseExists = false;
- }
- return databaseExists;
- }
-
- public bool CreateDatabase(string DatabaseName)
- {
- bool success = false;
- try
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlCommand dbCommand = new SqlCommand("CREATE DATABASE " + DatabaseName, dbConnection);
- dbConnection.Open();
- dbCommand.ExecuteNonQuery();
- dbCommand.Dispose();
- dbCommand = null;
- dbConnection.Close();
- dbConnection.Dispose();
- dbConnection = null;
- success = true;
- }
- catch
- {
- success = false;
- }
-
- return success;
- }
-
- public bool TableExists(string TableName)
- {
- bool tableExists = false;
- // Check the cache first for table existence...
- if (_tableExistsStatus.ContainsKey(TableName))
- {
- tableExists = _tableExistsStatus[TableName];
- }
- // The cache above may have returned false (but another thread may be building
- // the table - so check again...
- if(!tableExists)
- {
- try
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlDataAdapter dbDataAdapter = new SqlDataAdapter("SELECT COUNT(*) FROM " + TableName, dbConnection);
- DataTable table = new DataTable();
- dbConnection.Open();
- dbDataAdapter.Fill(table);
- table.Dispose();
- table = null;
- dbDataAdapter.Dispose();
- dbDataAdapter = null;
- dbConnection.Close();
- dbConnection.Dispose();
- dbConnection = null;
-
- tableExists = true;
- }
- catch
- {
- tableExists = false;
- }
-
- if (_tableExistsStatus.ContainsKey(TableName))
- {
- _tableExistsStatus[TableName] = tableExists;
- }
- else
- {
- _tableExistsStatus.Add(TableName, tableExists);
- }
- }
- return tableExists;
- }
-
- public void Remove(string tableName)
- {
- if (TableExists(tableName))
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlCommand dbCommand = new SqlCommand("DROP TABLE " + tableName, dbConnection);
- dbConnection.Open();
- dbCommand.ExecuteNonQuery();
- dbConnection.Close();
- dbConnection.Dispose();
- dbConnection = null;
- if (_tableExistsStatus.ContainsKey(tableName)) _tableExistsStatus[tableName] = false;
- }
- }
-
- public bool MakeAccessibleToAllUsers()
- {
- bool success = false;
- try
- {
- SqlConnection dbConnection = new SqlConnection(_connectionString);
- SqlCommand dbCommand = new SqlCommand("sp_addsrvrolemember", dbConnection); // @loginame = N'BUILTIN\Users', @rolename = N'sysadmin'");
- dbCommand.CommandType = CommandType.StoredProcedure;
- dbCommand.Parameters.AddWithValue("@loginame", "BUILTIN\\Users");
- dbCommand.Parameters.AddWithValue("@rolename", "sysadmin");
- dbConnection.Open();
- dbCommand.ExecuteNonQuery();
- dbCommand.Dispose();
- dbCommand = null;
- dbConnection.Close();
- dbConnection.Dispose();
- dbConnection = null;
- success = true;
- }
- catch
- {
- success = false;
- }
-
- return success;
- }
- }
- }