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); }