|
|
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace ClassGeneratorEvents
{
/// <summary>
/// The class that handles the sql server database objects.
/// </summary>
public class SqlServerHandler
{
private string serverInstance;
private string serverUsername;
private string serverPassword;
private Server currentServer;
/// <summary>
/// The constructor that initializes the sql server handler class.
/// </summary>
/// <param name="serverInstance">The server instance.</param>
/// <param name="serverUsername">The username of the server.</param>
/// <param name="serverPassword">The password of the server.</param>
/// <param name="serverLoginSecure">The login secure of the server.</param>
public SqlServerHandler(string serverInstance, string serverUsername, string serverPassword)
{
this.serverInstance = serverInstance;
this.serverUsername = serverUsername;
this.serverPassword = serverPassword;
}
/// <summary>
/// The method that connects to the database.
/// </summary>
/// <returns>Returns whether database is connected or not.</returns>
public bool Connect()
{
ServerConnection serverConnection = new ServerConnection(serverInstance, serverUsername, serverPassword);
currentServer = new Server(serverConnection);
serverConnection.Connect();
return serverConnection.IsOpen;
}
/// <summary>
/// The method that gets the list of databases in the server.
/// </summary>
/// <returns>Returns the list of databases.</returns>
public List<string> GetDatabases()
{
List<string> dbList = new List<string>();
if (currentServer != null)
{
foreach (Database db in currentServer.Databases)
{
dbList.Add(db.Name);
}
}
return dbList;
}
/// <summary>
/// The method that gets the list of tables of the specified database.
/// </summary>
/// <param name="databaseName">The database name from which the tables are retrieved.</param>
/// <returns>Returns the list of tables.</returns>
public List<string> GetTables(string databaseName)
{
List<string> tablesList = new List<string>();
if (currentServer != null)
{
Database database = currentServer.Databases[databaseName];
if (database != null)
{
foreach (Table table in database.Tables)
{
if (table.Schema == database.DefaultSchema)
tablesList.Add(table.Name);
}
}
}
return tablesList;
}
/// <summary>
/// The method that gets the primary key value of the specified table of the specified database.
/// </summary>
/// <param name="databaseName">The database name from which the table of the primary key is retrieved.</param>
/// <param name="tableName">The table name for which the primary key is retrieved.</param>
/// <returns>Returns the primary key value.</returns>
public string GetPrimaryKey(string databaseName, string tableName)
{
string primaryKey = "";
if (currentServer != null)
{
Database database = currentServer.Databases[databaseName];
if (database != null)
{
Table table = database.Tables[tableName];
if (table != null)
{
foreach (Column column in table.Columns)
{
if (column.InPrimaryKey)
{
primaryKey = column.Name;
break;
}
}
}
}
}
return primaryKey;
}
/// <summary>
/// The method that gets the list of columns of the specified table of the specified database.
/// </summary>
/// <param name="databaseName">The database name from which the table of the columns are retrieved.</param>
/// <param name="tableName">The table from which the columns are retrieved.</param>
/// <returns>Returns the list of columns of the specified sql table.</returns>
public ColumnInformationCollection GetTableColumns(string databaseName, string tableName)
{
ColumnInformationCollection columnInfoCollection = new ColumnInformationCollection();
Table table = currentServer.Databases[databaseName].Tables[tableName];
if (table != null)
{
foreach (Column column in table.Columns)
{
string colTypeCS = ConvertToStringForCS(column.DataType.SqlDataType);
string colTypeVB = ConvertToStringForVB(column.DataType.SqlDataType);
string sqlColName = column.Name;
string colName = CommonFunctions.CreateGoodName(column.Name);
columnInfoCollection.Add(new ColumnInformation(colTypeCS, colTypeVB, sqlColName, colName));
}
}
return columnInfoCollection;
}
/// <summary>
/// The method that converts the sql type to cs type.
/// </summary>
/// <param name="sqlDataType">The sql type of a column.</param>
/// <returns>Returns the cs type of the specified sql type.</returns>
private string ConvertToStringForCS(SqlDataType sqlDataType)
{
string dataTypeString = "object";
switch (sqlDataType)
{
case SqlDataType.TinyInt:
case SqlDataType.SmallInt:
case SqlDataType.Int:
case SqlDataType.BigInt:
dataTypeString = "int";
break;
case SqlDataType.Binary:
case SqlDataType.Bit:
dataTypeString = "bool";
break;
case SqlDataType.SmallDateTime:
case SqlDataType.DateTime:
case SqlDataType.Timestamp:
dataTypeString = "DateTime";
break;
case SqlDataType.Float:
case SqlDataType.SmallMoney:
case SqlDataType.Money:
dataTypeString = "float";
break;
case SqlDataType.Decimal:
case SqlDataType.Numeric:
case SqlDataType.Real:
dataTypeString = "double";
break;
case SqlDataType.NChar:
case SqlDataType.Char:
case SqlDataType.NText:
case SqlDataType.NVarChar:
case SqlDataType.NVarCharMax:
case SqlDataType.Text:
case SqlDataType.VarChar:
case SqlDataType.VarCharMax:
dataTypeString = "string";
break;
case SqlDataType.Image:
dataTypeString = "byte[]";
break;
case SqlDataType.None:
case SqlDataType.SysName:
case SqlDataType.UniqueIdentifier:
case SqlDataType.UserDefinedDataType:
case SqlDataType.UserDefinedType:
case SqlDataType.Variant:
case SqlDataType.Xml:
default:
dataTypeString = "object";
break;
}
return dataTypeString;
}
/// <summary>
/// The method that converts the sql type to vb type.
/// </summary>
/// <param name="sqlDataType">The sql type of a column.</param>
/// <returns>Returns the vb type of the specified sql type.</returns>
private string ConvertToStringForVB(SqlDataType sqlDataType)
{
string dataTypeString = "Object";
switch (sqlDataType)
{
case SqlDataType.TinyInt:
case SqlDataType.SmallInt:
case SqlDataType.Int:
case SqlDataType.BigInt:
dataTypeString = "Integer";
break;
case SqlDataType.Binary:
case SqlDataType.Bit:
dataTypeString = "Boolean";
break;
case SqlDataType.SmallDateTime:
case SqlDataType.DateTime:
case SqlDataType.Timestamp:
dataTypeString = "DateTime";
break;
case SqlDataType.Float:
case SqlDataType.SmallMoney:
case SqlDataType.Money:
dataTypeString = "Single";
break;
case SqlDataType.Decimal:
case SqlDataType.Numeric:
case SqlDataType.Real:
dataTypeString = "Double";
break;
case SqlDataType.NChar:
case SqlDataType.Char:
case SqlDataType.NText:
case SqlDataType.NVarChar:
case SqlDataType.NVarCharMax:
case SqlDataType.Text:
case SqlDataType.VarChar:
case SqlDataType.VarCharMax:
dataTypeString = "String";
break;
case SqlDataType.None:
case SqlDataType.Image:
case SqlDataType.SysName:
case SqlDataType.UniqueIdentifier:
case SqlDataType.UserDefinedDataType:
case SqlDataType.UserDefinedType:
case SqlDataType.Variant:
case SqlDataType.Xml:
default:
dataTypeString = "Object";
break;
}
return dataTypeString;
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip