public static Dictionary <string, KeyColumn> GetKeysDictionary(string tableName)
{
Dictionary <string, KeyColumn> collection = new Dictionary <string, KeyColumn>();
string connectionString = Session.ConnectionString;
string sql = string.Format("select * from information_schema.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE '%PK_%' AND TABLE_NAME = '{0}'", tableName);
SqlDataReader dataReader = SqlHelperSqlServer.ExecuteReader(connectionString, CommandType.Text, sql);
Dictionary <string, string> fields = GetFieldsEasy(tableName);
try
{
int sequence = 1;
while (dataReader.Read())
{
//TODO: Per ora le chiavi le ipotizzo solo GUID
KeyColumn column = new KeyColumn();
column.Name = dataReader["COLUMN_NAME"].ToString();
column.Unique = true;
column.Type = fields[column.Name];
column.NetType = GetDatatableSqlServer.GetSystemType(column.Type, false);
column.Sequence = sequence;
collection.Add(column.Name, column);
sequence++;
}
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
if (!dataReader.IsClosed)
{
dataReader.Close();
}
dataReader.Dispose();
}
return(collection);
}
public static string GetStored(SqlConnection connection, string tableName, string type)
{
string storedFunctions = string.Empty;
SqlConnection conn = new SqlConnection(Session.ConnectionString);
ArrayList storedProcedures = GetReader(conn, tableName);
DataTable tableParameters = GetDatatableSqlServer.GetDatatableSchema();
foreach (string storedProcedure in storedProcedures)
{
SqlCommand cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter parameter in cmd.Parameters)
{
if (!parameter.ParameterName.Equals("@RETURN_VALUE"))
{
DataRow row = tableParameters.NewRow();
row["Name"] = parameter.ParameterName.Replace("@", string.Empty);
row["Type"] = GetDatatableSqlServer.GetSystemType(parameter.SqlDbType.ToString().ToLower(), parameter.IsNullable);
row["DbType"] = parameter.SqlDbType;
row["Length"] = parameter.Size;
row["Nullable"] = parameter.IsNullable;
row["Table"] = storedProcedure;
tableParameters.Rows.Add(row);
}
}
storedFunctions = string.Concat(storedFunctions, BuildStoredProcedureList(tableParameters, tableName, type), "\n\n\t\t");
conn.Close();
tableParameters.Clear();
}
return(storedFunctions);
}